Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (1,62,191,208,212) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "original_data = pd.read_csv(\"data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function randomly fills in the missing values in a given series using the existing values in the series. It does so in such a way that the frequency of each value remains unchanged after the missing values are filled in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_random(series: pd.Series) -> pd.Series:\n",
    "    \"\"\"Randomly fill in data while maintaining the same frequencies.\"\"\"\n",
    "    notnull = len(series.dropna())\n",
    "    unique_values = series.dropna().unique()\n",
    "    counts = series.dropna().value_counts()\n",
    "    frequencies = []\n",
    "    \n",
    "    # Get the frequency of each unique value in the column.\n",
    "    for value in unique_values:\n",
    "        frequencies.append(counts[value] / notnull)\n",
    "        \n",
    "    # Fill in missing values while maintaining the same frequencies.\n",
    "    new_series = series.fillna(pd.Series(\n",
    "        np.random.choice(unique_values, p=frequencies, size=len(series))))\n",
    "    \n",
    "    return new_series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a copy of the data for cleaning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = original_data.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The columns in this data set containing boolean values use the strings \"t\" and \"f\" to denote true and false. These strings need to be converted to a bool type and the missing data in these columns needs to be filled in.\n",
    "\n",
    "Some of the columns contain only \"t\" and NaN. For these columns, it can be assumed that NaN means false. For a data set this large, it is unlikely that there would be no false values.\n",
    "\n",
    "Some of the columns contain \"t\", \"f\", and NaN. For these columns, it can be assumed that NaN means that there is no data. To fill in the data, missing values are randomly replaced with true and false according to their frequency. This means that the ratio of true to false values will remain the same after filling in the missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "TRUE_VAL = \"t\"\n",
    "FALSE_VAL = \"f\"\n",
    "BOOL_VALS = {TRUE_VAL, FALSE_VAL, np.NaN}\n",
    "\n",
    "bool_columns = set()\n",
    "true_columns = set()\n",
    "\n",
    "# Determine which columns are boolean.\n",
    "for column in data:\n",
    "    if not set(data[column].unique()) - BOOL_VALS:\n",
    "        # The column is boolean.\n",
    "        if FALSE_VAL in data[column].values:\n",
    "            # The column contains both \"t\" and \"f\".\n",
    "            bool_columns.add(column)\n",
    "        else:\n",
    "            # The column contains only \"t\".\n",
    "            true_columns.add(column)\n",
    "            \n",
    "# Fill in missing data.\n",
    "for column in bool_columns:\n",
    "    data[column] = fill_random(data[column])\n",
    "    \n",
    "# Replace strings with bool values.\n",
    "for column in true_columns | bool_columns:\n",
    "    data[column].replace({TRUE_VAL: True, FALSE_VAL: False, np.NaN: False}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fill in missing values in numerical columns using the mean value for the column. For columns that contain only integers, round the mean value before filling in the missing data. This ensures that a column for which non-integers doesn't make sense (e.g. number of adults) isn't filled in with decimal values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "int_columns = []\n",
    "float_columns = []\n",
    "\n",
    "# Determine which columns contain floats and which contain only integers.\n",
    "for column in data:\n",
    "    # Skip the column if it is not numeric.\n",
    "    if not np.issubdtype(data[column].dtype, np.number):\n",
    "        continue\n",
    "        \n",
    "    # Check if the column contains any floats.\n",
    "    try:\n",
    "        downcast_dtype = pd.to_numeric(data[column].dropna(), downcast=\"integer\").dtype\n",
    "    except ValueError:\n",
    "        continue\n",
    "        \n",
    "    if np.issubdtype(downcast_dtype, np.integer):\n",
    "        # The column contains only integers.\n",
    "        int_columns.append(column)\n",
    "    else:\n",
    "        # The column contains floats.\n",
    "        float_columns.append(column)\n",
    "\n",
    "# For integer columns, fill in missing data using the rounded mean value.\n",
    "for column in int_columns:\n",
    "    mean = data[column].mean()\n",
    "    try:\n",
    "        mean = round(mean)\n",
    "    except ValueError:\n",
    "        pass\n",
    "    data[column].fillna(mean, inplace=True)\n",
    "    \n",
    "# For float columns, fill in missing data using the mean value.\n",
    "for column in float_columns:\n",
    "    mean = data[column].mean()\n",
    "    data[column].fillna(mean, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fill in missing values for columns containing string values. The missing values are filled in randomly using existing values in such a way that the frequency of each value is preserved."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in data:\n",
    "    if data[column].dtype == object and data[column].isnull().values.any():\n",
    "        # The column contains string values and has missing values.\n",
    "        data[column] = fill_random(data[column])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove columns that still contain empty values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "appraise_improvement_value\n",
      "appraise_land_value\n",
      "basement\n",
      "basement_area\n",
      "bathroom_fixtures\n",
      "crime_index_property\n",
      "crime_index_violent\n",
      "effective_year_built\n",
      "electricity_available\n",
      "electricity_type\n",
      "fireplace_type\n",
      "floor_type\n",
      "foundation_type\n",
      "garage_area\n",
      "garage_spaces\n",
      "improvement_type\n",
      "improvement_value_sqft\n",
      "location_associated_aspects\n",
      "married_individuals\n",
      "mortgage_amount_assumed\n",
      "mortgage_deed_type\n",
      "pcp_annual\n",
      "pool_type\n",
      "prior_deed_type\n",
      "second_deed_type\n",
      "second_equity_in_property\n",
      "second_mortgage_amount_assumed\n",
      "second_mortgage_deed_type\n",
      "second_mortgate_due_date\n",
      "solar_suitable_roof_type\n",
      "third_equity_in_property\n",
      "third_mortgage_amount_assumed\n",
      "third_mortgage_deed_type\n",
      "tmax_jan\n",
      "tmax_jul\n",
      "tmean_annual\n",
      "tmean_jan\n",
      "tmean_jul\n",
      "tmin_jan\n",
      "tmin_jul\n",
      "total_appraised_value\n"
     ]
    }
   ],
   "source": [
    "for column in data:\n",
    "    if data[column].isnull().any():\n",
    "        del data[column]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write cleaned data to a CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv(\"clean_data.csv\")"
   ]
  }
 ],
 "metadata": {
  "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}