combine_and_clean_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import joblib\n",
    "import pickle\n",
    "pd.options.display.max_colwidth = 150"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "rawdir = 'data/raw'\n",
    "files = ['claims-2002-2006.xls', 'claims-2007-2009.xls', 'claims-2010-2013.xls',\n",
    "         'claims-2014.xls', 'claims-2015.xlsx', 'claims-2016.csv', 'claims-2017.csv']\n",
    "filelist = [f'{rawdir}/{file}' for file in files]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data/raw/claims-2002-2006.xls',\n",
       " 'data/raw/claims-2007-2009.xls',\n",
       " 'data/raw/claims-2010-2013.xls',\n",
       " 'data/raw/claims-2014.xls',\n",
       " 'data/raw/claims-2015.xlsx',\n",
       " 'data/raw/claims-2016.csv',\n",
       " 'data/raw/claims-2017.csv']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filelist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = {}\n",
    "for i, file in enumerate(filelist):\n",
    "    if 'xls' in file:\n",
    "        d[i] = pd.read_excel(file)\n",
    "    if 'csv' in file:\n",
    "        d[i] = pd.read_csv(file)\n",
    "        d[i] = d[i].iloc[:, 1:]\n",
    "    else:\n",
    "        assert \"Error\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat(d, axis=0, sort=False, ignore_index=True)\n",
    "\n",
    "### Rename columns\n",
    "\n",
    "col_names = ['claim_number', 'date_received', 'incident_date', 'airport_code', 'airport_name',\n",
    "             'airline', 'claim_type', 'claim_site', 'item', 'claim_amount', 'status',\n",
    "             'close_amount', 'disposition', 'item_category']\n",
    "\n",
    "col_mapper = dict(zip(df.columns, col_names))\n",
    "df.rename(columns=col_mapper, inplace=True)\n",
    "\n",
    "### Clean and drop columns\n",
    "\n",
    "# combine the Item and Item Category column into just Item Category\n",
    "df['item_category'].fillna(value=df['item'], inplace=True)\n",
    "df.item_category = df.item_category.astype('str')\n",
    "\n",
    "# drop item category (only doing item_category now) and airport_name\n",
    "df.drop(columns=['item', 'airport_name'], inplace=True)\n",
    "\n",
    "# Set DateTime columns\n",
    "df['incident_date'] = pd.to_datetime(df['incident_date'], errors='coerce')\n",
    "df['date_received'] = pd.to_datetime(df['date_received'], errors='coerce')\n",
    "df.dropna(subset=['date_received', 'incident_date'], inplace=True)\n",
    "\n",
    "# df['date_received'] = pd.to_datetime(df['date_received'])\n",
    "# df['incident_date'] = pd.to_datetime(df['incident_date'])\n",
    "\n",
    "# Limit to 3 outcomes of interest (and clean)\n",
    "df.disposition = df.disposition.str.replace('D eny|Den y|D en y|De ny',\n",
    "                           'Deny', regex=True)\n",
    "df.disposition = df.disposition.str.replace('Approv e in Full',\n",
    "                           'Approve in Full', regex=True)\n",
    "\n",
    "df = df[(df['disposition'] == \"Approve in Full\") |\n",
    "        (df['disposition'] == \"Settle\") |\n",
    "        (df['disposition'] == \"Deny\")]\n",
    "\n",
    "# Fix disrepancies in close_amount based on other information. Assuming disposition of Approve or Deny is correct.\n",
    "df.loc[(df.close_amount.isna()) & (df.disposition == 'Approve in Full'), 'close_amount'] = df.claim_amount\n",
    "df.loc[(df.close_amount.isna()) & (df.disposition == 'Deny'), 'close_amount'] = 0\n",
    "\n",
    "# Format close_amount column\n",
    "df.close_amount = df.close_amount.astype('str')\n",
    "df.close_amount = df.close_amount.str.replace('$','', regex=False)\n",
    "df = df[df.close_amount.str.contains('[^a-zA-Z]')]\n",
    "df.close_amount = df.close_amount.str.replace(',', '',regex=False)\n",
    "df.close_amount = df.close_amount.str.replace(' ', '',regex=False)\n",
    "df.close_amount = df.close_amount.str.replace('-', '0',regex=False)\n",
    "df.close_amount = df.close_amount.astype('float64')\n",
    "\n",
    "# Drop bad dates\n",
    "df = df[(df.date_received.dt.year >= 2002) & (df.date_received.dt.year <= 2017)]\n",
    "df = df[(df.incident_date.dt.year >= 2001) & (df.incident_date.dt.year <= 2017)]\n",
    "\n",
    "df = df.sort_values(by='date_received', ascending=False)\n",
    "\n",
    "# strip and clean a bit\n",
    "df.airline = df.airline.str.strip()\n",
    "df.airport_code = df.airport_code.str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 478,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleandir = 'data/clean'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# joblib.dump(df, f'{cleandir}/clean_df.joblib', compress=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## From this point forward, this is cleaning for 2002-2017 CLASSIFIER and not 2002-2009 Regressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/spencer/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:97: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n"
     ]
    }
   ],
   "source": [
    "df.drop(columns=['claim_number', 'status', 'claim_amount'], inplace=True)\n",
    "\n",
    "# clean 'claim_type'\n",
    "df.claim_type = df.claim_type.str.replace(' ','')\n",
    "df.claim_type = df.claim_type.str.replace('PassengerPropertyLoss', 'PropertyLoss')\n",
    "df = df[\n",
    "       (df.claim_type == 'PropertyLoss') | (df.claim_type == 'PropertyDamage') | \n",
    "       (df.claim_type == 'PersonalInjury') | (df.claim_type == 'EmployeeLoss(MPCECA)') | \n",
    "       (df.claim_type == 'PassengerTheft') | (df.claim_type == 'MotorVehicle')\n",
    "        ]\n",
    "# clean 'claim_site'\n",
    "df.claim_site = df.claim_site.str.replace('-', 'Other')\n",
    "df.claim_site = df.claim_site.str.replace('C heckpoint', 'Checkpoint')\n",
    "df.claim_site = df.claim_site.str.replace('Checked  Baggage', 'Checked Baggage')\n",
    "\n",
    "df = df[(df.claim_site != 'PreOtherCheck')]\n",
    "df = df[(df.claim_site != 'BusStation')]\n",
    "\n",
    "df.item_category = df.item_category.astype('str')\n",
    "\n",
    "# simplify categories with subcategories into only normal categories\n",
    "df.item_category = df.item_category.str.replace('\\s-[^;]*', '')\n",
    "df.item_category = df.item_category.str.replace('\\s\\([^;]*', '')\n",
    "\n",
    "# Drop weird misclassified beginners\n",
    "df = df[df.item_category.str.contains('^[^/]')]\n",
    "df = df[df.item_category.str.contains('^[^&]')]\n",
    "\n",
    "# Strip whitespace\n",
    "df.item_category = df.item_category.str.replace('\\s;',';')\n",
    "df.item_category = df.item_category.str.replace('[^.*]{1}$\\s','')\n",
    "df.item_category = df.item_category.str.replace('^;\\s','')\n",
    "\n",
    "# Fix one off typos\n",
    "df.item_category = df.item_category.str.replace('Jew$', 'Jewelry & Watches')\n",
    "df.item_category = df.item_category.str.replace('Home Décor', 'Home Decor')\n",
    "\n",
    "df.item_category = df.item_category.str.replace('Computer$', 'Computer & Accessories')\n",
    "df.item_category = df.item_category.str.replace('Computer peripheral$', 'Computer & Accessories')\n",
    "df.item_category = df.item_category.str.replace('Hunting & Fi$', 'Hunting & Fishing')\n",
    "df.item_category = df.item_category.str.replace('Jewelry$', 'Jewelry & Watches')\n",
    "df.item_category = df.item_category.str.replace('Jewelry & Watch$', 'Jewelry & Watches')\n",
    "df.item_category = df.item_category.str.replace('Othe$', 'Other')\n",
    "df.item_category = df.item_category.str.replace('Travel A$', 'Travel Accessories')\n",
    "df.item_category = df.item_category.str.replace('Travel Accessorie$', 'Travel Accessories')\n",
    "df.item_category = df.item_category.str.replace('Tools & Tool Boxes$', 'Tool Chests & Tool Boxes')\n",
    "df.item_category = df.item_category.str.replace('Tools$', 'Tools & Home Improvement Supplies')\n",
    "df.item_category = df.item_category.str.replace('Toys$', 'Toys & Games')\n",
    "df.item_category = df.item_category.str.replace('Personal Accesso$', 'Personal Accessories')\n",
    "df.item_category = df.item_category.str.replace('Personal Accessor$', 'Personal Accessories')\n",
    "df.item_category = df.item_category.str.replace('Personal Accessories$', 'Personal Accessories')\n",
    "df.item_category = df.item_category.str.replace('Personal E$', 'Personal Electronics')\n",
    "df.item_category = df.item_category.str.replace('Personal Ele$', 'Personal Electronics')\n",
    "df.item_category = df.item_category.str.replace('Personal Electronic$', 'Personal Electronics')\n",
    "df.item_category = df.item_category.str.replace('Medica$', 'Medical/Science')\n",
    "df.item_category = df.item_category.str.replace('Medical/S$', 'Medical/Science')\n",
    "df.item_category = df.item_category.str.replace('Medica$', 'Medical/Science')\n",
    "df.item_category = df.item_category.str.replace('Sporting$', 'Sporting Equipment & Supplies')\n",
    "df.item_category = df.item_category.str.replace('Cosmetics$', 'Cosmetics & Grooming')\n",
    "df.item_category = df.item_category.str.replace('Drapery and Curtain Rods, Venetian Blinds$','Drapes')\n",
    "df.item_category = df.item_category.str.replace('Food items$', 'Food & Drink')\n",
    "df.item_category = df.item_category.str.replace('H$', '')\n",
    "df.item_category = df.item_category.str.replace('Hunting & Fishing Items$', 'Hunting & Fishing')\n",
    "df.item_category = df.item_category.str.replace('Musical Instruments$', 'Musical Instruments & Accessories')\n",
    "df.item_category = df.item_category.str.replace('Automobile Parts$', 'Automobile Parts & Accessories')\n",
    "df.item_category = df.item_category.str.replace('Audio$', 'Audio/Video')\n",
    "\n",
    "df.item_category = df.item_category.str.replace('Computer;', 'Computer & Accessories;')\n",
    "df.item_category = df.item_category.str.replace('Computer peripherals;', 'Computer & Accessories;')\n",
    "df.item_category = df.item_category.str.replace('Hunting & Fi;', 'Hunting & Fishing;')\n",
    "df.item_category = df.item_category.str.replace('Jewelry;', 'Jewelry & Watches;')\n",
    "df.item_category = df.item_category.str.replace('Jewelry & Watch;', 'Jewelry & Watches;')\n",
    "df.item_category = df.item_category.str.replace('Othe;', 'Other;')\n",
    "df.item_category = df.item_category.str.replace('Travel A;', 'Travel Accessories;')\n",
    "df.item_category = df.item_category.str.replace('Travel Accessorie;', 'Travel Accessories;')\n",
    "df.item_category = df.item_category.str.replace('Tools & Tool Boxes;', 'Tool Chests & Tool Boxes;')\n",
    "df.item_category = df.item_category.str.replace('Tools;', 'Tools & Home Improvement Supplies;')\n",
    "df.item_category = df.item_category.str.replace('Toys;', 'Toys & Games;')\n",
    "df.item_category = df.item_category.str.replace('Personal Accesso;', 'Personal Accessories;')\n",
    "df.item_category = df.item_category.str.replace('Personal Accessor;', 'Personal Accessories;')\n",
    "df.item_category = df.item_category.str.replace('Personal E;', 'Personal Electronics;')\n",
    "df.item_category = df.item_category.str.replace('Personal Ele;', 'Personal Electronics;')\n",
    "df.item_category = df.item_category.str.replace('Personal Electronic;', 'Personal Electronics;')\n",
    "df.item_category = df.item_category.str.replace('Medica;', 'Medical/Science;')\n",
    "df.item_category = df.item_category.str.replace('Medical/S;', 'Medical/Science;')\n",
    "df.item_category = df.item_category.str.replace('Medica;', 'Medical/Science;')\n",
    "df.item_category = df.item_category.str.replace('Sporting;', 'Sporting Equipment & Supplies;')\n",
    "df.item_category = df.item_category.str.replace('Cosmetics;', 'Cosmetics & Grooming;')\n",
    "df.item_category = df.item_category.str.replace('Drapery and Curtain Rods, Venetian Blinds;','Drapes;')\n",
    "df.item_category = df.item_category.str.replace('Food items;', 'Food & Drink;')\n",
    "df.item_category = df.item_category.str.replace('H;', '')\n",
    "df.item_category = df.item_category.str.replace('Hunting & Fishing Items;', 'Hunting & Fishing;')\n",
    "df.item_category = df.item_category.str.replace('Musical Instruments;', 'Musical Instruments & Accessories;')\n",
    "df.item_category = df.item_category.str.replace('Automobile Parts;', 'Automobile Parts & Accessories;')\n",
    "df.item_category = df.item_category.str.replace('Audio;', 'Audio/Video;')\n",
    "\n",
    "\n",
    "\n",
    "# Drop about 2500 rows where there is a missing letter for item_type\n",
    "df = df[df.claim_type != 'PersonalInjury'][df.item_category.str.contains('^[^a-z]')]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleandir = './data/clean'\n",
    "df.to_pickle(f'{cleandir}/clean_for_classifier_df.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "combine_and_clean_data.ipynb  reports\n",
      "data\t\t\t      simple_random_forest.ipynb\n",
      "explore_raw_data.ipynb\t      simple_random_forest_w_req_amount.ipynb\n",
      "ideas.txt\t\t      tsa-wait-times-january-2006-december-2015.xls\n",
      "models\t\t\t      web_app\n",
      "pdf_to_csvs.ipynb\n"
     ]
    }
   ],
   "source": [
    "!ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 530,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.to_pickle(f'{cleandir}/clean_for_classifier_df.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 526,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data/clean/clean_for_classifier_df.joblib']"
      ]
     },
     "execution_count": 526,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# joblib.dump(df, f'{cleandir}/clean_for_classifier_df.joblib')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/spencer/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n",
      "/home/spencer/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  if __name__ == '__main__':\n"
     ]
    }
   ],
   "source": [
    "df = df.dropna()\n",
    "\n",
    "\n",
    "df['binary_disposition'] = df['disposition']\n",
    "df['binary_disposition'] = df['disposition'].where(df['disposition'] == 'Deny', other='Compensate')\n",
    "\n",
    "# Change some text to make it more human readable\n",
    "df.claim_site[df.claim_site == '-'] = 'Unknown'\n",
    "df.claim_type[df.claim_type == '-'] = 'Unknown'\n",
    "\n",
    "# Feature Engineering\n",
    "\n",
    "### Count of items claimed\n",
    "\n",
    "# TODO: Consider only using this for where claim_type is related to property.\n",
    "df['num_items_or_incidents_claimed'] = df['item_category'].str.split(pat=';').apply(lambda x: len(x))\n",
    "df['num_items_or_incidents_claimed'] = df['num_items_or_incidents_claimed'].where(df['claim_type'].str.contains('property', case=False) == True, other= 0)\n",
    "\n",
    "### Time calculation\n",
    "wait_period = df.date_received - df.incident_date\n",
    "df['days_waited_to_file_claim'] = wait_period.dt.days\n",
    "\n",
    "# Drop days where the 'date_received\" was reported before 'incident_date'\n",
    "df = df[df.days_waited_to_file_claim >= 0]\n",
    "\n",
    "df.reset_index(inplace=True,drop=True)\n",
    "df['bin_dispos_onehot'] = df['binary_disposition'].apply(lambda x: 1 if x == 'Compensate' else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df['Week/Year_inc_date'] = df['incident_date'].apply(lambda x: \"%d/%d\" % (x.week, x.year))\n",
    "# df['Week_inc_date'] = df['incident_date'].apply(lambda x: \"%d\" % (x.week))\n",
    "# df['Month_inc_date'] = df['incident_date'].apply(lambda x: \"%d\" % (x.month))\n",
    "# df['Year_inc_date'] = df['incident_date'].apply(lambda x: \"%d\" % (x.year))\n",
    "\n",
    "# df['Week_received'] = df['date_received'].apply(lambda x: \"%d\" % (x.week))\n",
    "df['Month_received'] = df['date_received'].apply(lambda x: \"%d\" % (x.month))\n",
    "# df['Year_received'] = df['date_received'].apply(lambda x: \"%d\" % (x.year))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pickle Lists for Web App"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "itemlist = list(sorted(df.item_category.str.replace(' ','').str.split(pat=';').apply(pd.Series).stack().unique()))\n",
    "itemlist = itemlist[2:]\n",
    "\n",
    "airportlist = list(sorted(df.airport_code.unique()))\n",
    "\n",
    "airlinelist = list(sorted(df.airline.unique()))\n",
    "airlinelist.remove('(OY)')\n",
    "airlinelist.remove('-')\n",
    "airlinelist.remove('Inc')\n",
    "airlinelist.remove('Not Provided')\n",
    "airlinelist.remove('vivaAerobus')\n",
    "\n",
    "claim_types = list(sorted(df.claim_type.unique()))\n",
    "\n",
    "claim_sites = list(sorted(df.claim_site.unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [],
   "source": [
    "featuredir = './web_app/featurelists'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['./web_app/featurelists/claim_sites.joblib']"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joblib.dump(itemlist, f'{featuredir}/item_category.joblib')\n",
    "joblib.dump(airportlist, f'{featuredir}/airports.joblib')\n",
    "joblib.dump(airlinelist, f'{featuredir}/airlines.joblib')\n",
    "joblib.dump(claim_types, f'{featuredir}/claim_types.joblib')\n",
    "joblib.dump(claim_sites, f'{featuredir}/claim_sites.joblib')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rf_full_month_rec.joblib\n"
     ]
    }
   ],
   "source": [
    "!ls web_app/stat_models/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}