notebook/2_Data_Loading_and_Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b014d9f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:06.900924Z",
     "start_time": "2021-10-27T12:48:06.889862Z"
    }
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    import google.colab\n",
    "    IN_COLAB = True\n",
    "except:\n",
    "    IN_COLAB = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12b45f30",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.423265Z",
     "start_time": "2021-10-27T12:48:06.903049Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6fbe4d7",
   "metadata": {},
   "source": [
    "# [Adult Data Set](https://archive.ics.uci.edu/ml/datasets/Adult)\n",
    "\n",
    "Predict whether income exceeds $50K/yr based on census data.\n",
    "Also known as \"Census Income\" dataset.\n",
    "\n",
    "**Attributes**\n",
    "\n",
    "- age\n",
    "- workclass\n",
    "  - Represents the employment status of an individual\n",
    "- fnlwgt\n",
    "  - Final weight which is the number of people the census believes the entry represents\n",
    "  - People with similar demographic characteristics should have similar weights\n",
    "    - This only applies within state.\n",
    "- education\n",
    "  - The highest level of education achieved by an individual\n",
    "- education-num\n",
    "  - The highest level of education achieved in numerical form\n",
    "- marital-status\n",
    "- relationship\n",
    "  - Represents what this individual is relative to others\n",
    "- race\n",
    "- sex\n",
    "- capital-gain\n",
    "- capital-loss\n",
    "- hours-per-week\n",
    "  - The hours an individual has reported to work per week\n",
    "- native-country"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5e227f2",
   "metadata": {},
   "source": [
    "# Data Loading"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e11eb5db",
   "metadata": {},
   "source": [
    "## Data Files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29817d4c",
   "metadata": {},
   "source": [
    "I have cut the [original data](https://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.data)\n",
    "into 4 seprate files.\n",
    "\n",
    "https://github.com/sesise0307/pydata2021-eda/tree/main/data\n",
    "\n",
    "![Data List](../image/data_list.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fe08a01",
   "metadata": {},
   "source": [
    "## Loading a file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59c4e7db",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.485156Z",
     "start_time": "2021-10-27T12:48:13.426359Z"
    }
   },
   "outputs": [],
   "source": [
    "if IN_COLAB:\n",
    "    data_file = 'https://raw.githubusercontent.com/sesise0307/pydata2021-eda/main/data/adult-0.data'\n",
    "else:\n",
    "    data_file = '../data/adult-0.data'\n",
    "\n",
    "df_0 = pd.read_csv(data_file)\n",
    "    \n",
    "df_0.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35d86afa",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.505249Z",
     "start_time": "2021-10-27T12:48:13.486606Z"
    }
   },
   "outputs": [],
   "source": [
    "df_0.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1edc2c5a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.509929Z",
     "start_time": "2021-10-27T12:48:13.507058Z"
    }
   },
   "outputs": [],
   "source": [
    "names = [\n",
    "    'age',\n",
    "    'workclass',\n",
    "    'fnlwgt',\n",
    "    'education',\n",
    "    'education_num',\n",
    "    'marital_status',\n",
    "    'occupation',\n",
    "    'relationship',\n",
    "    'race',\n",
    "    'sex',\n",
    "    'capital_gain',\n",
    "    'capital_loss',\n",
    "    'hours_per_week',\n",
    "    'native_country',\n",
    "    'income',\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fbf0d3c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.539046Z",
     "start_time": "2021-10-27T12:48:13.511013Z"
    }
   },
   "outputs": [],
   "source": [
    "df_0 = pd.read_csv(\n",
    "    data_file,\n",
    "    names=names,\n",
    "    skipinitialspace=True,  # Skip spaces after delimiter\n",
    ")\n",
    "\n",
    "df_0.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5313574c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.549358Z",
     "start_time": "2021-10-27T12:48:13.540068Z"
    }
   },
   "outputs": [],
   "source": [
    "df_0.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e401c6d7",
   "metadata": {},
   "source": [
    "## Merging many files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce15ad14",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.703907Z",
     "start_time": "2021-10-27T12:48:13.550556Z"
    }
   },
   "outputs": [],
   "source": [
    "df_list = list()\n",
    "\n",
    "for i in range(4):\n",
    "    print(f'Reading a csv file: adult-{i}.data')\n",
    "    \n",
    "    if IN_COLAB:\n",
    "        data_file = f'https://raw.githubusercontent.com/sesise0307/pydata2021-eda/main/data/adult-{i}.data'\n",
    "    else:\n",
    "        data_file = f'../data/adult-{i}.data'\n",
    "    \n",
    "    df_list.append(        \n",
    "        pd.read_csv(\n",
    "            data_file,\n",
    "            names=names,\n",
    "            skipinitialspace=True,\n",
    "        )\n",
    "    )\n",
    "\n",
    "df = pd.concat(df_list, ignore_index=True)\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acae2436",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.716317Z",
     "start_time": "2021-10-27T12:48:13.705442Z"
    }
   },
   "outputs": [],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36aea336",
   "metadata": {},
   "source": [
    "# Essential Check"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61e3d706",
   "metadata": {},
   "source": [
    "## info() and describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d905113",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.741462Z",
     "start_time": "2021-10-27T12:48:13.717373Z"
    }
   },
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc18fcbc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.770294Z",
     "start_time": "2021-10-27T12:48:13.743482Z"
    }
   },
   "outputs": [],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "266287cd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.811431Z",
     "start_time": "2021-10-27T12:48:13.771270Z"
    }
   },
   "outputs": [],
   "source": [
    "df.describe(exclude=np.number)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bf0353c",
   "metadata": {},
   "source": [
    "## Unique Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36c5c1c4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.818188Z",
     "start_time": "2021-10-27T12:48:13.812387Z"
    }
   },
   "outputs": [],
   "source": [
    "print('Unique workclass: ', df['workclass'].unique())\n",
    "print('The number of unique workclass: ', df['workclass'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d74eb39d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:13.878234Z",
     "start_time": "2021-10-27T12:48:13.819286Z"
    }
   },
   "outputs": [],
   "source": [
    "for column in df.columns:\n",
    "    n_unique = df[column].nunique()\n",
    "    \n",
    "    if df[column].nunique() < 50:\n",
    "        print(f'{column} ({n_unique}): {df[column].unique()}\\n')\n",
    "    else:\n",
    "        print(f'{column} ({n_unique})\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c38fdddc",
   "metadata": {},
   "source": [
    "> Note that if a variable contains only 1 unique value,\n",
    "> it means that the variable does not have any information."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a338eef",
   "metadata": {},
   "source": [
    "## Missing Values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84962926",
   "metadata": {},
   "source": [
    "### Visualization with missingno"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7de11df",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:18.838587Z",
     "start_time": "2021-10-27T12:48:13.879186Z"
    }
   },
   "outputs": [],
   "source": [
    "import missingno as msno\n",
    "\n",
    "msno.matrix(df);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2f9c036",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:19.427357Z",
     "start_time": "2021-10-27T12:48:18.840999Z"
    }
   },
   "outputs": [],
   "source": [
    "msno.bar(df);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3e94c65",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:19.437787Z",
     "start_time": "2021-10-27T12:48:19.428891Z"
    }
   },
   "outputs": [],
   "source": [
    "# Replace '?' to NaN\n",
    "df = df.replace('?', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e1f34a5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:19.773231Z",
     "start_time": "2021-10-27T12:48:19.438848Z"
    }
   },
   "outputs": [],
   "source": [
    "msno.matrix(df);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc8af197",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.271535Z",
     "start_time": "2021-10-27T12:48:19.774333Z"
    }
   },
   "outputs": [],
   "source": [
    "msno.bar(df);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34223451",
   "metadata": {},
   "source": [
    "### Manual computation of NaN ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb851ef9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.293156Z",
     "start_time": "2021-10-27T12:48:20.272694Z"
    }
   },
   "outputs": [],
   "source": [
    "df.isna().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60acfef9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.319793Z",
     "start_time": "2021-10-27T12:48:20.294149Z"
    }
   },
   "outputs": [],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf1c7c6f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.340817Z",
     "start_time": "2021-10-27T12:48:20.320897Z"
    }
   },
   "outputs": [],
   "source": [
    "df.isna().sum() / df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5bf8e8c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.362125Z",
     "start_time": "2021-10-27T12:48:20.341931Z"
    }
   },
   "outputs": [],
   "source": [
    "(\n",
    "    (df.isna().sum() / df.shape[0])\n",
    "    .sort_values(ascending=False)  # Sort by NaN Ratio\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66f0c43a",
   "metadata": {},
   "source": [
    "### Handling Missing Values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b942cb14",
   "metadata": {},
   "source": [
    "![Handling Missing Data](../image/handle_missing_data.png)\n",
    "\n",
    "Source: https://towardsdatascience.com/how-to-handle-missing-data-8646b18db0d4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dee491f0",
   "metadata": {},
   "source": [
    "#### Imputation with fillna()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37377b9a",
   "metadata": {},
   "source": [
    "##### workclass fillna() with mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77d8232e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.374096Z",
     "start_time": "2021-10-27T12:48:20.363327Z"
    }
   },
   "outputs": [],
   "source": [
    "workclass_mode = df['workclass'].mode()[0]\n",
    "workclass_mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96824b95",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.387135Z",
     "start_time": "2021-10-27T12:48:20.375243Z"
    }
   },
   "outputs": [],
   "source": [
    "df['workclass'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e38f41a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.401768Z",
     "start_time": "2021-10-27T12:48:20.388430Z"
    }
   },
   "outputs": [],
   "source": [
    "df['workclass'] = df['workclass'].fillna(workclass_mode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "132de6c2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.415352Z",
     "start_time": "2021-10-27T12:48:20.403737Z"
    }
   },
   "outputs": [],
   "source": [
    "df['workclass'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e63e39b",
   "metadata": {},
   "source": [
    "##### occupation fillna with \"Unknown\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b8250b9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.426914Z",
     "start_time": "2021-10-27T12:48:20.416758Z"
    }
   },
   "outputs": [],
   "source": [
    "df['occupation'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33475255",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.439165Z",
     "start_time": "2021-10-27T12:48:20.430631Z"
    }
   },
   "outputs": [],
   "source": [
    "df['occupation'] = df['occupation'].fillna('Unknown')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c68b77aa",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.453460Z",
     "start_time": "2021-10-27T12:48:20.440714Z"
    }
   },
   "outputs": [],
   "source": [
    "df['occupation'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c3b0a3c",
   "metadata": {},
   "source": [
    "#### Deletion with dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fefb4309",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.480479Z",
     "start_time": "2021-10-27T12:48:20.454652Z"
    }
   },
   "outputs": [],
   "source": [
    "# Drop rows with at least one NaN values\n",
    "df = df.dropna().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "841c1d2d",
   "metadata": {},
   "source": [
    "#### Verification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6ab4bd0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.908463Z",
     "start_time": "2021-10-27T12:48:20.482237Z"
    }
   },
   "outputs": [],
   "source": [
    "msno.bar(df);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d1d7f4c",
   "metadata": {},
   "source": [
    "## [Sidetable](https://github.com/chris1610/sidetable)\n",
    "\n",
    "Builds simple but useful summary tables of your pandas DataFrame.\n",
    "\n",
    "Usage is straightforward. Install and import sidetable. Then access it through the new .stb accessor on your DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "088d72ce",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.912070Z",
     "start_time": "2021-10-27T12:48:20.909682Z"
    }
   },
   "outputs": [],
   "source": [
    "if IN_COLAB:\n",
    "    !pip install sidetable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d96d3c8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.954363Z",
     "start_time": "2021-10-27T12:48:20.913152Z"
    }
   },
   "outputs": [],
   "source": [
    "import sidetable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd1c52e5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:20.978936Z",
     "start_time": "2021-10-27T12:48:20.956728Z"
    }
   },
   "outputs": [],
   "source": [
    "df.stb.freq(['race', 'sex'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6df6243",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.094831Z",
     "start_time": "2021-10-27T12:48:20.980255Z"
    }
   },
   "outputs": [],
   "source": [
    "df.stb.counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0fa04d46",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.569005Z",
     "start_time": "2021-10-27T12:48:21.096075Z"
    }
   },
   "outputs": [],
   "source": [
    "df.stb.missing(style=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf958bb8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.583263Z",
     "start_time": "2021-10-27T12:48:21.570416Z"
    }
   },
   "outputs": [],
   "source": [
    "(\n",
    "    df\n",
    "    .groupby(['race', 'sex'])\n",
    "    [['capital_gain']]\n",
    "    .mean()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fabfe356",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.618464Z",
     "start_time": "2021-10-27T12:48:21.584628Z"
    }
   },
   "outputs": [],
   "source": [
    "(\n",
    "    df\n",
    "    .groupby(['race', 'sex'])\n",
    "    [['capital_gain']]\n",
    "    .mean()\n",
    "    .stb\n",
    "    .subtotal()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a04438b",
   "metadata": {},
   "source": [
    "# (Primitive) Preprocessing & Feature Engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6b81643",
   "metadata": {},
   "source": [
    "## Age Grouping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d587a31",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.626005Z",
     "start_time": "2021-10-27T12:48:21.619642Z"
    }
   },
   "outputs": [],
   "source": [
    "df['age'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0421721e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.634951Z",
     "start_time": "2021-10-27T12:48:21.627116Z"
    }
   },
   "outputs": [],
   "source": [
    "df['age_group'] = pd.cut(\n",
    "    df['age'],\n",
    "    bins=range(10, 101, 10),\n",
    "    right=False,\n",
    "    labels=[f'{age_start}~{age_start + 9}'\n",
    "            for age_start in range(10, 100, 10)]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32570ba3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.648271Z",
     "start_time": "2021-10-27T12:48:21.636272Z"
    }
   },
   "outputs": [],
   "source": [
    "df['age_group'].cat.categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29cdcc51",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.664752Z",
     "start_time": "2021-10-27T12:48:21.649683Z"
    }
   },
   "outputs": [],
   "source": [
    "df[['age', 'age_group']].tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38c12228",
   "metadata": {},
   "source": [
    "## Cateogry and Category Ordering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8da45a3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.681908Z",
     "start_time": "2021-10-27T12:48:21.666500Z"
    }
   },
   "outputs": [],
   "source": [
    "(\n",
    "    df\n",
    "    .groupby('education')['education_num']\n",
    "    .unique()\n",
    "    .sort_values()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4252d6bf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.691762Z",
     "start_time": "2021-10-27T12:48:21.683277Z"
    }
   },
   "outputs": [],
   "source": [
    "education_order = (\n",
    "    df\n",
    "    .groupby('education')['education_num']\n",
    "    .unique()\n",
    "    .sort_values()\n",
    "    .index\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92e752bf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.703288Z",
     "start_time": "2021-10-27T12:48:21.694023Z"
    }
   },
   "outputs": [],
   "source": [
    "df['education'] = df['education'].astype(\n",
    "    pd.CategoricalDtype(categories=education_order,\n",
    "                        ordered=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4681315",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.795068Z",
     "start_time": "2021-10-27T12:48:21.704463Z"
    }
   },
   "outputs": [],
   "source": [
    "df['education'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e65d2d06",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.808757Z",
     "start_time": "2021-10-27T12:48:21.797594Z"
    }
   },
   "outputs": [],
   "source": [
    "df['education_num'] = df['education_num'].astype(\n",
    "    pd.CategoricalDtype(ordered=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a85f29f2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.821235Z",
     "start_time": "2021-10-27T12:48:21.810581Z"
    }
   },
   "outputs": [],
   "source": [
    "df['education_num'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e676a637",
   "metadata": {},
   "source": [
    "## Captial gain and loss"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "991cf59d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.841109Z",
     "start_time": "2021-10-27T12:48:21.823097Z"
    }
   },
   "outputs": [],
   "source": [
    "df[['capital_gain', 'capital_loss']].tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5fb15d4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.858910Z",
     "start_time": "2021-10-27T12:48:21.842793Z"
    }
   },
   "outputs": [],
   "source": [
    "(  # Check if capital gain and capital loss appear at the same time\n",
    "    df[['capital_gain', 'capital_loss']]\n",
    "    .astype(bool)\n",
    "#     .sum(axis='columns')\n",
    "#     .max()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9ff5a14",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.872966Z",
     "start_time": "2021-10-27T12:48:21.860960Z"
    }
   },
   "outputs": [],
   "source": [
    "df['capital_gain'] = df['capital_gain'] - df['capital_loss']\n",
    "df = df.drop(columns='capital_loss')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea69279e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.888435Z",
     "start_time": "2021-10-27T12:48:21.874573Z"
    }
   },
   "outputs": [],
   "source": [
    "df['capital_gain'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8735896f",
   "metadata": {},
   "source": [
    "## Income exceeds $50K/yr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "571a5b75",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.897916Z",
     "start_time": "2021-10-27T12:48:21.890070Z"
    }
   },
   "outputs": [],
   "source": [
    "df['income_exceed_50k'] = df['income'] == '>50K'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be592ded",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.914803Z",
     "start_time": "2021-10-27T12:48:21.899397Z"
    }
   },
   "outputs": [],
   "source": [
    "df[['income', 'income_exceed_50k']].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1688175",
   "metadata": {},
   "source": [
    "## Adding Fake Income (for fun)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53082544",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:21.949545Z",
     "start_time": "2021-10-27T12:48:21.937707Z"
    }
   },
   "outputs": [],
   "source": [
    "import random\n",
    "\n",
    "def get_fake_income(row):\n",
    "    exceed_50k = row['income_exceed_50k']\n",
    "        \n",
    "    weight_age = 1.2 if 40 <= row['age'] <= 55 else 1.0\n",
    "    weight_hours_per_week = 1.2 if 38 <= row['hours_per_week'] else 0.8\n",
    "    weight_race = 1.2 if row['race'] == 'White' else 0.9\n",
    "    weight_sex = 1.2 if row['sex'] == 'Male' else 0.9\n",
    "    \n",
    "    mu = 35000 * weight_age * weight_hours_per_week * weight_race * weight_sex\n",
    "    sigma = mu * random.uniform(0.5, 1.5)\n",
    "    \n",
    "    while True:  # Guarantee fake income is conform to exceed_50k\n",
    "        income = int(random.gauss(mu, sigma))\n",
    "        \n",
    "        if exceed_50k and 50000 < income:\n",
    "            return income\n",
    "        \n",
    "        elif not exceed_50k and 1000 < income <= 50000:\n",
    "            return income"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c246723b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:22.455736Z",
     "start_time": "2021-10-27T12:48:21.951143Z"
    }
   },
   "outputs": [],
   "source": [
    "random.seed(42)\n",
    "df['fake_income'] = df.apply(get_fake_income, axis='columns')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "272d993d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d2f161b",
   "metadata": {},
   "source": [
    "# Save the Preprocessed DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46454ea7",
   "metadata": {},
   "source": [
    "`pandas` supports various file types for both reading and saving your DataFrame.\n",
    "\n",
    "![Pandas I/O](../image/pandas_io.png)\n",
    "\n",
    "Source: https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a52ff13",
   "metadata": {},
   "source": [
    "> It seems that `feather` format is an ideal candidate to store the data between Jupyter sessions.\n",
    "> It shows high I/O speed, doesn’t take too much memory on the disk and doesn’t need any unpacking when loaded back into RAM.\n",
    ">\n",
    "> Source: [The Best Format to Save Pandas Data](https://towardsdatascience.com/the-best-format-to-save-pandas-data-414dca023e0d)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28244b1e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:22.549597Z",
     "start_time": "2021-10-27T12:48:22.457629Z"
    }
   },
   "outputs": [],
   "source": [
    "if IN_COLAB:\n",
    "    df.to_feather('adult_preprocessed.feather')\n",
    "else:\n",
    "    df.to_feather('../data/preprocessed/adult.feather')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95ccfc76",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:22.602226Z",
     "start_time": "2021-10-27T12:48:22.551999Z"
    }
   },
   "outputs": [],
   "source": [
    "if IN_COLAB:\n",
    "    df_feather = pd.read_feather('adult_preprocessed.feather')\n",
    "else:\n",
    "    df_feather = pd.read_feather('../data/preprocessed/adult.feather')\n",
    "\n",
    "df_feather.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93294f01",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-10-27T12:48:22.623013Z",
     "start_time": "2021-10-27T12:48:22.603791Z"
    }
   },
   "outputs": [],
   "source": [
    "df_feather.info()"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "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": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "303.825px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}