Notebook_Code_Along_Intro_To_Pandas_Healthcare For_All_Pipelining.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TAS5MqM9xy-N"
   },
   "source": [
    "## Health Care for All Case Study using Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "executionInfo": {
     "elapsed": 357,
     "status": "ok",
     "timestamp": 1623063461098,
     "user": {
      "displayName": "Ignacio Soteras",
      "photoUrl": "",
      "userId": "02050793736257155229"
     },
     "user_tz": -120
    },
    "id": "DVtwE6bRxy-V"
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "import seaborn as sns\n",
    "pd.options.display.max_rows = 50\n",
    "## Install xlrd package to load Excel files\n",
    "# conda install openpyxl\n",
    "## conda install xlrd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pipeline Controller"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hk_df=load_original_data() \n",
    "hk_df=lower_case_column_names(hk_df)\n",
    "hk_df=rename_columns(hk_df)\n",
    "hk_df=drop_columns(hk_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>state</th>\n",
       "      <th>gender</th>\n",
       "      <th>median_home_val</th>\n",
       "      <th>median_household_income</th>\n",
       "      <th>avg_fam_income</th>\n",
       "      <th>hvp1</th>\n",
       "      <th>per_capita_income</th>\n",
       "      <th>pobc1</th>\n",
       "      <th>pobc2</th>\n",
       "      <th>med_fam_income</th>\n",
       "      <th>avg_household_income</th>\n",
       "      <th>avggift</th>\n",
       "      <th>tcode</th>\n",
       "      <th>dob</th>\n",
       "      <th>domain</th>\n",
       "      <th>target_d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>44060</td>\n",
       "      <td>FL</td>\n",
       "      <td>M</td>\n",
       "      <td>AAA896</td>\n",
       "      <td>392</td>\n",
       "      <td>520.0</td>\n",
       "      <td>7</td>\n",
       "      <td>21975</td>\n",
       "      <td>6</td>\n",
       "      <td>16</td>\n",
       "      <td>430.0</td>\n",
       "      <td>466</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>1901</td>\n",
       "      <td>C2</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>96093</td>\n",
       "      <td>IL</td>\n",
       "      <td>M</td>\n",
       "      <td>537.00</td>\n",
       "      <td>365</td>\n",
       "      <td>473.0</td>\n",
       "      <td>0</td>\n",
       "      <td>19387</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "      <td>415.0</td>\n",
       "      <td>410</td>\n",
       "      <td>5.666667</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>T2</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>43333</td>\n",
       "      <td>FL</td>\n",
       "      <td>F</td>\n",
       "      <td>725.00</td>\n",
       "      <td>301</td>\n",
       "      <td>436.0</td>\n",
       "      <td>3</td>\n",
       "      <td>18837</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>340.0</td>\n",
       "      <td>361</td>\n",
       "      <td>4.111111</td>\n",
       "      <td>0</td>\n",
       "      <td>2501</td>\n",
       "      <td>C2</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21885</td>\n",
       "      <td>NC</td>\n",
       "      <td>M</td>\n",
       "      <td>AAA1095</td>\n",
       "      <td>401</td>\n",
       "      <td>413.0</td>\n",
       "      <td>7</td>\n",
       "      <td>14014</td>\n",
       "      <td>1</td>\n",
       "      <td>74</td>\n",
       "      <td>407.0</td>\n",
       "      <td>399</td>\n",
       "      <td>27.277778</td>\n",
       "      <td>0</td>\n",
       "      <td>2208</td>\n",
       "      <td>T2</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>190108</td>\n",
       "      <td>FL</td>\n",
       "      <td>F</td>\n",
       "      <td>995.00</td>\n",
       "      <td>252</td>\n",
       "      <td>348.0</td>\n",
       "      <td>0</td>\n",
       "      <td>17991</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>280.0</td>\n",
       "      <td>316</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>C2</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1001</th>\n",
       "      <td>41537</td>\n",
       "      <td>FL</td>\n",
       "      <td>F</td>\n",
       "      <td>742</td>\n",
       "      <td>396</td>\n",
       "      <td>470.0</td>\n",
       "      <td>0</td>\n",
       "      <td>15356</td>\n",
       "      <td>7</td>\n",
       "      <td>37</td>\n",
       "      <td>424.0</td>\n",
       "      <td>450</td>\n",
       "      <td>14.285714</td>\n",
       "      <td>2</td>\n",
       "      <td>6101</td>\n",
       "      <td>S2</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002</th>\n",
       "      <td>4881</td>\n",
       "      <td>FL</td>\n",
       "      <td>M</td>\n",
       "      <td>599</td>\n",
       "      <td>100</td>\n",
       "      <td>423.0</td>\n",
       "      <td>4</td>\n",
       "      <td>11670</td>\n",
       "      <td>10</td>\n",
       "      <td>27</td>\n",
       "      <td>406.0</td>\n",
       "      <td>200</td>\n",
       "      <td>9.230769</td>\n",
       "      <td>28</td>\n",
       "      <td>7401</td>\n",
       "      <td>C1</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1003</th>\n",
       "      <td>66358</td>\n",
       "      <td>ND</td>\n",
       "      <td>M</td>\n",
       "      <td>593</td>\n",
       "      <td>258</td>\n",
       "      <td>299.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10186</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "      <td>298.0</td>\n",
       "      <td>274</td>\n",
       "      <td>5.266667</td>\n",
       "      <td>1</td>\n",
       "      <td>3301</td>\n",
       "      <td>S2</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1004</th>\n",
       "      <td>2231</td>\n",
       "      <td>WI</td>\n",
       "      <td>male</td>\n",
       "      <td>564</td>\n",
       "      <td>217</td>\n",
       "      <td>397.0</td>\n",
       "      <td>0</td>\n",
       "      <td>12315</td>\n",
       "      <td>0</td>\n",
       "      <td>96</td>\n",
       "      <td>386.0</td>\n",
       "      <td>295</td>\n",
       "      <td>11.400000</td>\n",
       "      <td>1</td>\n",
       "      <td>7301</td>\n",
       "      <td>T2</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1005</th>\n",
       "      <td>68534</td>\n",
       "      <td>MI</td>\n",
       "      <td>M</td>\n",
       "      <td>651</td>\n",
       "      <td>431</td>\n",
       "      <td>505.0</td>\n",
       "      <td>0</td>\n",
       "      <td>14845</td>\n",
       "      <td>7</td>\n",
       "      <td>77</td>\n",
       "      <td>451.0</td>\n",
       "      <td>455</td>\n",
       "      <td>7.428571</td>\n",
       "      <td>1</td>\n",
       "      <td>1709</td>\n",
       "      <td>S2</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4028 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id state gender median_home_val  median_household_income  \\\n",
       "0      44060    FL      M          AAA896                      392   \n",
       "1      96093    IL      M          537.00                      365   \n",
       "2      43333    FL      F          725.00                      301   \n",
       "3      21885    NC      M         AAA1095                      401   \n",
       "4     190108    FL      F          995.00                      252   \n",
       "...      ...   ...    ...             ...                      ...   \n",
       "1001   41537    FL      F             742                      396   \n",
       "1002    4881    FL      M             599                      100   \n",
       "1003   66358    ND      M             593                      258   \n",
       "1004    2231    WI   male             564                      217   \n",
       "1005   68534    MI      M             651                      431   \n",
       "\n",
       "      avg_fam_income  hvp1 per_capita_income  pobc1  pobc2  med_fam_income  \\\n",
       "0              520.0     7             21975      6     16           430.0   \n",
       "1              473.0     0             19387      1     89           415.0   \n",
       "2              436.0     3             18837     11     17           340.0   \n",
       "3              413.0     7             14014      1     74           407.0   \n",
       "4              348.0     0             17991      5      6           280.0   \n",
       "...              ...   ...               ...    ...    ...             ...   \n",
       "1001           470.0     0             15356      7     37           424.0   \n",
       "1002           423.0     4             11670     10     27           406.0   \n",
       "1003           299.0     0             10186      1     70           298.0   \n",
       "1004           397.0     0             12315      0     96           386.0   \n",
       "1005           505.0     0             14845      7     77           451.0   \n",
       "\n",
       "      avg_household_income    avggift  tcode   dob domain  target_d  \n",
       "0                      466  28.000000      1  1901     C2     100.0  \n",
       "1                      410   5.666667      0     0     T2       7.0  \n",
       "2                      361   4.111111      0  2501     C2       5.0  \n",
       "3                      399  27.277778      0  2208     T2      38.0  \n",
       "4                      316   6.000000     28     0     C2       5.0  \n",
       "...                    ...        ...    ...   ...    ...       ...  \n",
       "1001                   450  14.285714      2  6101     S2      50.0  \n",
       "1002                   200   9.230769     28  7401     C1      10.0  \n",
       "1003                   274   5.266667      1  3301     S2       5.0  \n",
       "1004                   295  11.400000      1  7301     T2      14.0  \n",
       "1005                   455   7.428571      1  1709     S2       9.0  \n",
       "\n",
       "[4028 rows x 17 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hk_df=load_original_data() \n",
    "\n",
    "hk_df= (hk_df.pipe(lower_case_column_names)\n",
    " .pipe(rename_columns)\n",
    " #.pipe(drop_columns)\n",
    "\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_original_data():\n",
    "    file1 = pd.read_csv('Data/file1.csv')\n",
    "    file2 = pd.read_csv('Data/file2.txt', sep = '\\t')\n",
    "    file3 = pd.read_excel('Data/file3.xlsx')\n",
    "    file4 = pd.read_excel('Data/file4.xlsx')\n",
    "    return pd.concat([file1,file2,file3, file4], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "hk_df=load_original_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make the column names lower case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def lower_case_column_names(hk_df):\n",
    "    hk_df.columns=[i.lower() for i in hk_df.columns]\n",
    "    return hk_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>controln</th>\n",
       "      <th>state</th>\n",
       "      <th>gender</th>\n",
       "      <th>hv1</th>\n",
       "      <th>ic1</th>\n",
       "      <th>ic4</th>\n",
       "      <th>hvp1</th>\n",
       "      <th>ic5</th>\n",
       "      <th>pobc1</th>\n",
       "      <th>pobc2</th>\n",
       "      <th>ic2</th>\n",
       "      <th>ic3</th>\n",
       "      <th>avggift</th>\n",
       "      <th>tcode</th>\n",
       "      <th>dob</th>\n",
       "      <th>domain</th>\n",
       "      <th>target_d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>44060</td>\n",
       "      <td>FL</td>\n",
       "      <td>M</td>\n",
       "      <td>AAA896</td>\n",
       "      <td>392</td>\n",
       "      <td>520.0</td>\n",
       "      <td>7</td>\n",
       "      <td>21975</td>\n",
       "      <td>6</td>\n",
       "      <td>16</td>\n",
       "      <td>430.0</td>\n",
       "      <td>466</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>1901</td>\n",
       "      <td>C2</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>96093</td>\n",
       "      <td>IL</td>\n",
       "      <td>M</td>\n",
       "      <td>537.00</td>\n",
       "      <td>365</td>\n",
       "      <td>473.0</td>\n",
       "      <td>0</td>\n",
       "      <td>19387</td>\n",
       "      <td>1</td>\n",
       "      <td>89</td>\n",
       "      <td>415.0</td>\n",
       "      <td>410</td>\n",
       "      <td>5.666667</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>T2</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>43333</td>\n",
       "      <td>FL</td>\n",
       "      <td>F</td>\n",
       "      <td>725.00</td>\n",
       "      <td>301</td>\n",
       "      <td>436.0</td>\n",
       "      <td>3</td>\n",
       "      <td>18837</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>340.0</td>\n",
       "      <td>361</td>\n",
       "      <td>4.111111</td>\n",
       "      <td>0</td>\n",
       "      <td>2501</td>\n",
       "      <td>C2</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21885</td>\n",
       "      <td>NC</td>\n",
       "      <td>M</td>\n",
       "      <td>AAA1095</td>\n",
       "      <td>401</td>\n",
       "      <td>413.0</td>\n",
       "      <td>7</td>\n",
       "      <td>14014</td>\n",
       "      <td>1</td>\n",
       "      <td>74</td>\n",
       "      <td>407.0</td>\n",
       "      <td>399</td>\n",
       "      <td>27.277778</td>\n",
       "      <td>0</td>\n",
       "      <td>2208</td>\n",
       "      <td>T2</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>190108</td>\n",
       "      <td>FL</td>\n",
       "      <td>F</td>\n",
       "      <td>995.00</td>\n",
       "      <td>252</td>\n",
       "      <td>348.0</td>\n",
       "      <td>0</td>\n",
       "      <td>17991</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>280.0</td>\n",
       "      <td>316</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>C2</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1001</th>\n",
       "      <td>41537</td>\n",
       "      <td>FL</td>\n",
       "      <td>F</td>\n",
       "      <td>742</td>\n",
       "      <td>396</td>\n",
       "      <td>470.0</td>\n",
       "      <td>0</td>\n",
       "      <td>15356</td>\n",
       "      <td>7</td>\n",
       "      <td>37</td>\n",
       "      <td>424.0</td>\n",
       "      <td>450</td>\n",
       "      <td>14.285714</td>\n",
       "      <td>2</td>\n",
       "      <td>6101</td>\n",
       "      <td>S2</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002</th>\n",
       "      <td>4881</td>\n",
       "      <td>FL</td>\n",
       "      <td>M</td>\n",
       "      <td>599</td>\n",
       "      <td>100</td>\n",
       "      <td>423.0</td>\n",
       "      <td>4</td>\n",
       "      <td>11670</td>\n",
       "      <td>10</td>\n",
       "      <td>27</td>\n",
       "      <td>406.0</td>\n",
       "      <td>200</td>\n",
       "      <td>9.230769</td>\n",
       "      <td>28</td>\n",
       "      <td>7401</td>\n",
       "      <td>C1</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1003</th>\n",
       "      <td>66358</td>\n",
       "      <td>ND</td>\n",
       "      <td>M</td>\n",
       "      <td>593</td>\n",
       "      <td>258</td>\n",
       "      <td>299.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10186</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "      <td>298.0</td>\n",
       "      <td>274</td>\n",
       "      <td>5.266667</td>\n",
       "      <td>1</td>\n",
       "      <td>3301</td>\n",
       "      <td>S2</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1004</th>\n",
       "      <td>2231</td>\n",
       "      <td>WI</td>\n",
       "      <td>male</td>\n",
       "      <td>564</td>\n",
       "      <td>217</td>\n",
       "      <td>397.0</td>\n",
       "      <td>0</td>\n",
       "      <td>12315</td>\n",
       "      <td>0</td>\n",
       "      <td>96</td>\n",
       "      <td>386.0</td>\n",
       "      <td>295</td>\n",
       "      <td>11.400000</td>\n",
       "      <td>1</td>\n",
       "      <td>7301</td>\n",
       "      <td>T2</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1005</th>\n",
       "      <td>68534</td>\n",
       "      <td>MI</td>\n",
       "      <td>M</td>\n",
       "      <td>651</td>\n",
       "      <td>431</td>\n",
       "      <td>505.0</td>\n",
       "      <td>0</td>\n",
       "      <td>14845</td>\n",
       "      <td>7</td>\n",
       "      <td>77</td>\n",
       "      <td>451.0</td>\n",
       "      <td>455</td>\n",
       "      <td>7.428571</td>\n",
       "      <td>1</td>\n",
       "      <td>1709</td>\n",
       "      <td>S2</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4028 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      controln state gender      hv1  ic1    ic4  hvp1    ic5  pobc1  pobc2  \\\n",
       "0        44060    FL      M   AAA896  392  520.0     7  21975      6     16   \n",
       "1        96093    IL      M   537.00  365  473.0     0  19387      1     89   \n",
       "2        43333    FL      F   725.00  301  436.0     3  18837     11     17   \n",
       "3        21885    NC      M  AAA1095  401  413.0     7  14014      1     74   \n",
       "4       190108    FL      F   995.00  252  348.0     0  17991      5      6   \n",
       "...        ...   ...    ...      ...  ...    ...   ...    ...    ...    ...   \n",
       "1001     41537    FL      F      742  396  470.0     0  15356      7     37   \n",
       "1002      4881    FL      M      599  100  423.0     4  11670     10     27   \n",
       "1003     66358    ND      M      593  258  299.0     0  10186      1     70   \n",
       "1004      2231    WI   male      564  217  397.0     0  12315      0     96   \n",
       "1005     68534    MI      M      651  431  505.0     0  14845      7     77   \n",
       "\n",
       "        ic2  ic3    avggift  tcode   dob domain  target_d  \n",
       "0     430.0  466  28.000000      1  1901     C2     100.0  \n",
       "1     415.0  410   5.666667      0     0     T2       7.0  \n",
       "2     340.0  361   4.111111      0  2501     C2       5.0  \n",
       "3     407.0  399  27.277778      0  2208     T2      38.0  \n",
       "4     280.0  316   6.000000     28     0     C2       5.0  \n",
       "...     ...  ...        ...    ...   ...    ...       ...  \n",
       "1001  424.0  450  14.285714      2  6101     S2      50.0  \n",
       "1002  406.0  200   9.230769     28  7401     C1      10.0  \n",
       "1003  298.0  274   5.266667      1  3301     S2       5.0  \n",
       "1004  386.0  295  11.400000      1  7301     T2      14.0  \n",
       "1005  451.0  455   7.428571      1  1709     S2       9.0  \n",
       "\n",
       "[4028 rows x 17 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lower_case_column_names(hk_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "e1CinfMhxy-Z"
   },
   "source": [
    "#### Renaming columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rename_columns(hk_df):\n",
    "    hk_df.rename(columns={'controln':'id','hv1':'median_home_val', 'ic1':'median_household_income',\"ic2\":\"med_fam_income\", \"ic3\":\"avg_household_income\",\"ic4\": \"avg_fam_income\",\"ic5\":\"per_capita_income\"}, inplace=True )\n",
    "    return hk_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1EeQNdq4xy-a"
   },
   "source": [
    "#### Deleting columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def drop_columns(hk_df) :\n",
    "    hk_df.drop(columns=[\"id\",\"tcode\",'pobc1',\"dob\"], inplace=True)\n",
    "    return hk_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_columns(hk_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_gender(hk_df):\n",
    "    ## do your magic about gender cleanfing \n",
    "    hk_df[\"gender\"]\n",
    "    return hk_df\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TSiKcjoRxy-e"
   },
   "source": [
    "#### Removing duplicates\n",
    "\n",
    "We can remove duplicate rows with the method `drop_duplicates()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 427
    },
    "executionInfo": {
     "elapsed": 358,
     "status": "ok",
     "timestamp": 1623071156303,
     "user": {
      "displayName": "Ignacio Soteras",
      "photoUrl": "",
      "userId": "02050793736257155229"
     },
     "user_tz": -120
    },
    "id": "ZDYDHX-jxy-e",
    "outputId": "6be726f4-0cd6-4e8c-f774-fa7c5d19ec79"
   },
   "outputs": [],
   "source": [
    "hk_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing outliers "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import scoreatpercentile as pct\n",
    "def remove_outliers(df):\n",
    "    pct_75 = pct(df, 75)  # Calculate percentile 75 using scipy function scoreatpercentile\n",
    "    pct_25 = pct(df, 25)  # Calculate percentile 25 using scipy function scoreatpercentile\n",
    "    upper_bound = pct_75 + 1.5*iqr(df)  # iqr - > Scipy function to calculate the Interquartile Range\n",
    "    lower_bound = pct_25 - 1.5*iqr(df)\n",
    "    df = df[(df <= upper_bound) & (df >= lower_bound)]  # Filter out the outliers\n",
    "    return df\n",
    "\n",
    "#calling the function\n",
    "remove_outliers(hk_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "Intro to Pandas - \"Healthcare for All\" Code Along - Structure.ipynb",
   "provenance": [],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "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
}