DataScienceBasics/Pandas/data_cleaning_with_pandas_overview.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Overview-of-Pandas-Data-Cleaning\" data-toc-modified-id=\"Overview-of-Pandas-Data-Cleaning-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Overview of Pandas Data Cleaning</a></span><ul class=\"toc-item\"><li><span><a href=\"#Objectives\" data-toc-modified-id=\"Objectives-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Objectives</a></span></li></ul></li><li><span><a href=\"#Functions-&amp;-Methods-for-Data-Cleaning\" data-toc-modified-id=\"Functions-&amp;-Methods-for-Data-Cleaning-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Functions &amp; Methods for Data Cleaning</a></span><ul class=\"toc-item\"><li><span><a href=\"#Lambda-Functions\" data-toc-modified-id=\"Lambda-Functions-2.1\"><span class=\"toc-item-num\">2.1&nbsp;&nbsp;</span>Lambda Functions</a></span></li><li><span><a href=\"#Aggregation-Functions\" data-toc-modified-id=\"Aggregation-Functions-2.2\"><span class=\"toc-item-num\">2.2&nbsp;&nbsp;</span>Aggregation Functions</a></span></li></ul></li><li><span><a href=\"#Dealing-with-Missing-Data\" data-toc-modified-id=\"Dealing-with-Missing-Data-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Dealing with Missing Data</a></span></li><li><span><a href=\"#Combining-DataFrames\" data-toc-modified-id=\"Combining-DataFrames-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Combining DataFrames</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "heading_collapsed": true,
    "id": "A4754OXh8Bb7"
   },
   "source": [
    "# Overview of Pandas Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "heading_collapsed": true,
    "hidden": true,
    "id": "0T7_Mxmt8Bb9"
   },
   "source": [
    "## Objectives"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "hidden": true,
    "id": "0T7_Mxmt8Bb9"
   },
   "source": [
    "You will be able to:\n",
    "* Split DataFrame into subgroups using .groupby() and aggregation functions (.min(), .max(), .count(), .sum()) \n",
    "* Explain the different types of joins (outer, inner, left, right)\n",
    "* Explain strategies for missing data (categorical & numerical)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "heading_collapsed": true,
    "id": "A4754OXh8Bb7"
   },
   "source": [
    "# Functions & Methods for Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "heading_collapsed": true,
    "hidden": true,
    "id": "Ob_EeLHj8Bb9"
   },
   "source": [
    "## Lambda Functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "hidden": true,
    "id": "7ys0Gxkq8Bb-"
   },
   "source": [
    "* experiment and solve for individual cases first\n",
    "* generalize your solution\n",
    "* watch for edge cases & exceptions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "heading_collapsed": true,
    "hidden": true,
    "id": "39p_On-z8Bb-"
   },
   "source": [
    "## Aggregation Functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "hidden": true,
    "id": "8FMO4ojM8Bb_"
   },
   "source": [
    "* .min() -- returns the minimum value for each column by group\n",
    "* .max() -- returns the maximum value for each column by group\n",
    "* .mean() -- returns the average value for each column by group\n",
    "* .median() -- returns the median value for each column by group\n",
    "* .count() -- returns the count of each column by group\n",
    "\n",
    "**Being familiar and comfortable with DataFrame splitting using aggregation methods will be VERY IMPORTANT for correctly using pivot tables, stack/unstack, and multi-heirarchical indexing**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-05T22:06:35.820651Z",
     "start_time": "2020-02-05T22:06:34.722242Z"
    },
    "colab": {},
    "colab_type": "code",
    "hidden": true,
    "id": "wvIHb-zN8Bb_",
    "outputId": "47f8cf37-75bf-4147-c185-311332923cf7"
   },
   "outputs": [],
   "source": [
    "from sklearn.datasets import load_diabetes\n",
    "import pandas as pd \n",
    "\n",
    "data = load_diabetes()\n",
    "df = pd.DataFrame(data.data, columns=data.feature_names)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-05T22:06:35.835287Z",
     "start_time": "2020-02-05T22:06:35.822307Z"
    },
    "colab": {},
    "colab_type": "code",
    "hidden": true,
    "id": "jzgZ71JZ8BcC",
    "outputId": "15db9f04-5dd0-41b0-9e83-7ff5aa92386b"
   },
   "outputs": [],
   "source": [
    "df.groupby('sex').max()\n",
    "#df['sex']\n",
    "#df['sex'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-02-05T22:06:35.855372Z",
     "start_time": "2020-02-05T22:06:35.836493Z"
    },
    "colab": {},
    "colab_type": "code",
    "hidden": true,
    "id": "Z5djlUYR8BcE",
    "outputId": "7770149d-8899-4dc3-d678-5d630ab052ad",
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "r_list = ['bp', 's1']\n",
    "df2 = pd.DataFrame(df.groupby(['sex', 'bmi'])[r_list].mean())\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "heading_collapsed": true,
    "id": "pQP6bRmb8BcJ"
   },
   "source": [
    "# Dealing with Missing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "hidden": true,
    "id": "CJUS0FUk8BcJ"
   },
   "source": [
    "* How to detect missing data?\n",
    "    1. NaNs - .isna().sum()\n",
    "    2. Placeholder Values\n",
    "    \n",
    "        a. Numerical - 0/999, .value_counts()\n",
    "        \n",
    "        b. Categorical - .unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "hidden": true,
    "id": "3It_XfJp8BcK"
   },
   "source": [
    "* How to deal with missing data?\n",
    "    1. Remove - df.dropna()\n",
    "    \n",
    "    2. Replace/Impute - df['col'].fillna(df['col'].median()) OR common value for categorical data (df['col'].value_counts())\n",
    "       \n",
    "       a. Why median instead of mean?\n",
    "       \n",
    "    3. Keep - \n",
    "        a. categorical - label 'missing'/'NaN' can give useful info about dataset\n",
    "        \n",
    "        b. numerical - binning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "heading_collapsed": true,
    "id": "xcX5LaaG8BcG"
   },
   "source": [
    "# Combining DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "hidden": true,
    "id": "RkerYSut8BcH"
   },
   "source": [
    "* Outer Join - returns all records from both tables.\n",
    "\n",
    "* Inner Join - returns only the records with matching keys in both tables.\n",
    "\n",
    "* Left Join - returns all the records from the left table, as well as any records from the right table that have a matching key with a record from the left table.\n",
    "\n",
    "* Right Join - returns all the records from the right table, as well as any records from the left table that have a matching key with a record from the right table."
   ]
  }
 ],
 "metadata": {
  "colab": {
   "name": "M01S04.ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": false,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": false,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}