Machine Learning - Hands on Lab - Session #2 - Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Machine Learning - Hands on Lab - Session #1\n",
    "\n",
    "* **Lecturer:** Jonathan DEKHTIAR\n",
    "* **Date:** 2017-03-13\n",
    "<br/><br/>\n",
    "* **Contact:** [contact@jonathandekhtiar.eu](mailto:contact@jonathandekhtiar.eu)\n",
    "* **Twitter:** [@born2data](https://twitter.com/born2data)\n",
    "* **LinkedIn:** [JonathanDEKHTIAR](https://fr.linkedin.com/in/jonathandekhtiar)\n",
    "* **Personal Website:** [JonathanDEKHTIAR](http://www.jonathandekhtiar.eu)\n",
    "* **RSS Feed:** [FeedCrunch.io](https://www.feedcrunch.io/@dataradar/)\n",
    "* **Tech. Blog:** [born2data.com](http://www.born2data.com/)\n",
    "* **Github:** [DEKHTIARJonathan](https://github.com/DEKHTIARJonathan)\n",
    "<br/><br/>\n",
    "\n",
    "```\n",
    "*************************************************************************\n",
    "**\n",
    "** 2017 March 13\n",
    "**\n",
    "** In place of a legal notice, here is a blessing:\n",
    "**\n",
    "**    May you do good and not evil.\n",
    "**    May you find forgiveness for yourself and forgive others.\n",
    "**    May you share freely, never taking more than you give.\n",
    "**\n",
    "*************************************************************************\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Let's clean the data buddy !\n",
    "\n",
    "With that general overview out of the way, let's start cleaning the Airbnb data. \n",
    "\n",
    "In relation to the datasets provided for the Airbnb Kaggle competition, we will focus our cleaning efforts on two files:\n",
    "\n",
    "* **train_users_2.csv** \n",
    "* **test_users.csv** \n",
    "\n",
    "For now, *sessions.csv* will be left aside.\n",
    "\n",
    "## 1. Loading the Python libraries\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 2. Loading in the Data\n",
    "\n",
    "The first step is to load the data from the CSV files using Python. \n",
    "To do this we will use the Pandas library and load the data from two files **train_users_2.csv** and **test_users.csv**. \n",
    "\n",
    "After loading, we will combine them into one dataset so that any cleaning (and later any other changes) will be done to all the data at once."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date_account_created</th>\n",
       "      <th>timestamp_first_active</th>\n",
       "      <th>date_first_booking</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>signup_method</th>\n",
       "      <th>signup_flow</th>\n",
       "      <th>language</th>\n",
       "      <th>affiliate_channel</th>\n",
       "      <th>affiliate_provider</th>\n",
       "      <th>first_affiliate_tracked</th>\n",
       "      <th>signup_app</th>\n",
       "      <th>first_device_type</th>\n",
       "      <th>first_browser</th>\n",
       "      <th>country_destination</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97279</th>\n",
       "      <td>xzlqc2fczu</td>\n",
       "      <td>2013-08-07</td>\n",
       "      <td>20130807053513</td>\n",
       "      <td>2013-08-12</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>basic</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Web</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4115</th>\n",
       "      <td>k73c8hcxz3</td>\n",
       "      <td>2011-04-09</td>\n",
       "      <td>20110409013949</td>\n",
       "      <td>2011-05-05</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>29.0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2</td>\n",
       "      <td>en</td>\n",
       "      <td>other</td>\n",
       "      <td>craigslist</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Web</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115501</th>\n",
       "      <td>b3blqt4vqc</td>\n",
       "      <td>2013-10-09</td>\n",
       "      <td>20131009155411</td>\n",
       "      <td>2013-11-07</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>48.0</td>\n",
       "      <td>basic</td>\n",
       "      <td>25</td>\n",
       "      <td>en</td>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>untracked</td>\n",
       "      <td>iOS</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86055</th>\n",
       "      <td>hfi0yii255</td>\n",
       "      <td>2013-06-24</td>\n",
       "      <td>20130624033933</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>49.0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Web</td>\n",
       "      <td>iPad</td>\n",
       "      <td>Mobile Safari</td>\n",
       "      <td>NDF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144105</th>\n",
       "      <td>aqsjjomf53</td>\n",
       "      <td>2014-01-20</td>\n",
       "      <td>20140120221144</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>24.0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Web</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>NDF</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                id date_account_created  timestamp_first_active  \\\n",
       "97279   xzlqc2fczu           2013-08-07          20130807053513   \n",
       "4115    k73c8hcxz3           2011-04-09          20110409013949   \n",
       "115501  b3blqt4vqc           2013-10-09          20131009155411   \n",
       "86055   hfi0yii255           2013-06-24          20130624033933   \n",
       "144105  aqsjjomf53           2014-01-20          20140120221144   \n",
       "\n",
       "       date_first_booking     gender   age signup_method  signup_flow  \\\n",
       "97279          2013-08-12     FEMALE   NaN         basic            0   \n",
       "4115           2011-05-05  -unknown-  29.0      facebook            2   \n",
       "115501         2013-11-07  -unknown-  48.0         basic           25   \n",
       "86055                 NaN     FEMALE  49.0      facebook            0   \n",
       "144105                NaN       MALE  24.0      facebook            0   \n",
       "\n",
       "       language affiliate_channel affiliate_provider first_affiliate_tracked  \\\n",
       "97279        en            direct             direct               untracked   \n",
       "4115         en             other         craigslist               untracked   \n",
       "115501       en            direct             direct               untracked   \n",
       "86055        en            direct             direct               untracked   \n",
       "144105       en            direct             direct               untracked   \n",
       "\n",
       "       signup_app first_device_type  first_browser country_destination  \n",
       "97279         Web   Windows Desktop         Chrome                  US  \n",
       "4115          Web   Windows Desktop         Chrome                  CA  \n",
       "115501        iOS            iPhone      -unknown-                  US  \n",
       "86055         Web              iPad  Mobile Safari                 NDF  \n",
       "144105        Web       Mac Desktop         Chrome                 NDF  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train = pd.read_csv(\"data/train_users_2.csv\")\n",
    "df_train.sample(n=5) # Only display a few lines and not the whole dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date_account_created</th>\n",
       "      <th>timestamp_first_active</th>\n",
       "      <th>date_first_booking</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>signup_method</th>\n",
       "      <th>signup_flow</th>\n",
       "      <th>language</th>\n",
       "      <th>affiliate_channel</th>\n",
       "      <th>affiliate_provider</th>\n",
       "      <th>first_affiliate_tracked</th>\n",
       "      <th>signup_app</th>\n",
       "      <th>first_device_type</th>\n",
       "      <th>first_browser</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>31345</th>\n",
       "      <td>pdlmlrigjc</td>\n",
       "      <td>2014-08-14</td>\n",
       "      <td>20140814154442</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>27.0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>0</td>\n",
       "      <td>es</td>\n",
       "      <td>sem-brand</td>\n",
       "      <td>google</td>\n",
       "      <td>omg</td>\n",
       "      <td>Web</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>Chrome</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47210</th>\n",
       "      <td>mwkj5y3bge</td>\n",
       "      <td>2014-09-07</td>\n",
       "      <td>20140907011254</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>basic</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Moweb</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>Mobile Safari</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34548</th>\n",
       "      <td>dtmczaohp0</td>\n",
       "      <td>2014-08-19</td>\n",
       "      <td>20140819055848</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>28.0</td>\n",
       "      <td>basic</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Moweb</td>\n",
       "      <td>Android Phone</td>\n",
       "      <td>Android Browser</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5802</th>\n",
       "      <td>5xtwbkhiz3</td>\n",
       "      <td>2014-07-11</td>\n",
       "      <td>20140711031536</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>basic</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>linked</td>\n",
       "      <td>Moweb</td>\n",
       "      <td>Android Phone</td>\n",
       "      <td>Chrome Mobile</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>337</th>\n",
       "      <td>tbnsmr9il1</td>\n",
       "      <td>2014-07-01</td>\n",
       "      <td>20140701163704</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MALE</td>\n",
       "      <td>34.0</td>\n",
       "      <td>basic</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>linked</td>\n",
       "      <td>Web</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>Chrome</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id date_account_created  timestamp_first_active  \\\n",
       "31345  pdlmlrigjc           2014-08-14          20140814154442   \n",
       "47210  mwkj5y3bge           2014-09-07          20140907011254   \n",
       "34548  dtmczaohp0           2014-08-19          20140819055848   \n",
       "5802   5xtwbkhiz3           2014-07-11          20140711031536   \n",
       "337    tbnsmr9il1           2014-07-01          20140701163704   \n",
       "\n",
       "       date_first_booking     gender   age signup_method  signup_flow  \\\n",
       "31345                 NaN     FEMALE  27.0      facebook            0   \n",
       "47210                 NaN  -unknown-   NaN         basic            0   \n",
       "34548                 NaN       MALE  28.0         basic            0   \n",
       "5802                  NaN  -unknown-   NaN         basic            0   \n",
       "337                   NaN       MALE  34.0         basic            0   \n",
       "\n",
       "      language affiliate_channel affiliate_provider first_affiliate_tracked  \\\n",
       "31345       es         sem-brand             google                     omg   \n",
       "47210       en            direct             direct               untracked   \n",
       "34548       en            direct             direct               untracked   \n",
       "5802        en            direct             direct                  linked   \n",
       "337         en            direct             direct                  linked   \n",
       "\n",
       "      signup_app first_device_type    first_browser  \n",
       "31345        Web   Windows Desktop           Chrome  \n",
       "47210      Moweb            iPhone    Mobile Safari  \n",
       "34548      Moweb     Android Phone  Android Browser  \n",
       "5802       Moweb     Android Phone    Chrome Mobile  \n",
       "337          Web       Mac Desktop           Chrome  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test = pd.read_csv(\"data/test_users.csv\")\n",
    "df_test.sample(n=5) # Only display a few lines and not the whole dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>affiliate_channel</th>\n",
       "      <th>affiliate_provider</th>\n",
       "      <th>age</th>\n",
       "      <th>country_destination</th>\n",
       "      <th>date_account_created</th>\n",
       "      <th>date_first_booking</th>\n",
       "      <th>first_affiliate_tracked</th>\n",
       "      <th>first_browser</th>\n",
       "      <th>first_device_type</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>language</th>\n",
       "      <th>signup_app</th>\n",
       "      <th>signup_flow</th>\n",
       "      <th>signup_method</th>\n",
       "      <th>timestamp_first_active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2010-06-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>gxn3p5htnn</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>20090319043255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>seo</td>\n",
       "      <td>google</td>\n",
       "      <td>38.0</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2011-05-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>MALE</td>\n",
       "      <td>820tgsjxq7</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>20090523174809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>56.0</td>\n",
       "      <td>US</td>\n",
       "      <td>2010-09-28</td>\n",
       "      <td>2010-08-02</td>\n",
       "      <td>untracked</td>\n",
       "      <td>IE</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>4ft3gnwmtx</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>3</td>\n",
       "      <td>basic</td>\n",
       "      <td>20090609231247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>42.0</td>\n",
       "      <td>other</td>\n",
       "      <td>2011-12-05</td>\n",
       "      <td>2012-09-08</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Firefox</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>bjjt8pjhuk</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>20091031060129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>41.0</td>\n",
       "      <td>US</td>\n",
       "      <td>2010-09-14</td>\n",
       "      <td>2010-02-18</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>87mebub9p4</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>20091208061105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  affiliate_channel affiliate_provider   age country_destination  \\\n",
       "0            direct             direct   NaN                 NDF   \n",
       "1               seo             google  38.0                 NDF   \n",
       "2            direct             direct  56.0                  US   \n",
       "3            direct             direct  42.0               other   \n",
       "4            direct             direct  41.0                  US   \n",
       "\n",
       "  date_account_created date_first_booking first_affiliate_tracked  \\\n",
       "0           2010-06-28                NaN               untracked   \n",
       "1           2011-05-25                NaN               untracked   \n",
       "2           2010-09-28         2010-08-02               untracked   \n",
       "3           2011-12-05         2012-09-08               untracked   \n",
       "4           2010-09-14         2010-02-18               untracked   \n",
       "\n",
       "  first_browser first_device_type     gender          id language signup_app  \\\n",
       "0        Chrome       Mac Desktop  -unknown-  gxn3p5htnn       en        Web   \n",
       "1        Chrome       Mac Desktop       MALE  820tgsjxq7       en        Web   \n",
       "2            IE   Windows Desktop     FEMALE  4ft3gnwmtx       en        Web   \n",
       "3       Firefox       Mac Desktop     FEMALE  bjjt8pjhuk       en        Web   \n",
       "4        Chrome       Mac Desktop  -unknown-  87mebub9p4       en        Web   \n",
       "\n",
       "   signup_flow signup_method  timestamp_first_active  \n",
       "0            0      facebook          20090319043255  \n",
       "1            0      facebook          20090523174809  \n",
       "2            3         basic          20090609231247  \n",
       "3            0      facebook          20091031060129  \n",
       "4            0         basic          20091208061105  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Combine into one dataset\n",
    "df_all = pd.concat((df_train, df_test), axis=0, ignore_index=True)\n",
    "df_all.head(n=5) # Only display a few lines and not the whole dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 3. Cleaning the timestamp\n",
    "\n",
    "Once the data has been loaded and combined, the first cleaning step we will undertake is fixing the format of the dates.\n",
    "\n",
    "Fixing date format is essential in order to **simplify** date manipulation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>affiliate_channel</th>\n",
       "      <th>affiliate_provider</th>\n",
       "      <th>age</th>\n",
       "      <th>country_destination</th>\n",
       "      <th>date_account_created</th>\n",
       "      <th>date_first_booking</th>\n",
       "      <th>first_affiliate_tracked</th>\n",
       "      <th>first_browser</th>\n",
       "      <th>first_device_type</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>language</th>\n",
       "      <th>signup_app</th>\n",
       "      <th>signup_flow</th>\n",
       "      <th>signup_method</th>\n",
       "      <th>timestamp_first_active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2010-06-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>gxn3p5htnn</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2009-03-19 04:32:55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>seo</td>\n",
       "      <td>google</td>\n",
       "      <td>38.0</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2011-05-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>MALE</td>\n",
       "      <td>820tgsjxq7</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2009-05-23 17:48:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>56.0</td>\n",
       "      <td>US</td>\n",
       "      <td>2010-09-28</td>\n",
       "      <td>2010-08-02</td>\n",
       "      <td>untracked</td>\n",
       "      <td>IE</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>4ft3gnwmtx</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>3</td>\n",
       "      <td>basic</td>\n",
       "      <td>2009-06-09 23:12:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>42.0</td>\n",
       "      <td>other</td>\n",
       "      <td>2011-12-05</td>\n",
       "      <td>2012-09-08</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Firefox</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>bjjt8pjhuk</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2009-10-31 06:01:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>41.0</td>\n",
       "      <td>US</td>\n",
       "      <td>2010-09-14</td>\n",
       "      <td>2010-02-18</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>87mebub9p4</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2009-12-08 06:11:05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  affiliate_channel affiliate_provider   age country_destination  \\\n",
       "0            direct             direct   NaN                 NDF   \n",
       "1               seo             google  38.0                 NDF   \n",
       "2            direct             direct  56.0                  US   \n",
       "3            direct             direct  42.0               other   \n",
       "4            direct             direct  41.0                  US   \n",
       "\n",
       "  date_account_created date_first_booking first_affiliate_tracked  \\\n",
       "0           2010-06-28                NaN               untracked   \n",
       "1           2011-05-25                NaN               untracked   \n",
       "2           2010-09-28         2010-08-02               untracked   \n",
       "3           2011-12-05         2012-09-08               untracked   \n",
       "4           2010-09-14         2010-02-18               untracked   \n",
       "\n",
       "  first_browser first_device_type     gender          id language signup_app  \\\n",
       "0        Chrome       Mac Desktop  -unknown-  gxn3p5htnn       en        Web   \n",
       "1        Chrome       Mac Desktop       MALE  820tgsjxq7       en        Web   \n",
       "2            IE   Windows Desktop     FEMALE  4ft3gnwmtx       en        Web   \n",
       "3       Firefox       Mac Desktop     FEMALE  bjjt8pjhuk       en        Web   \n",
       "4        Chrome       Mac Desktop  -unknown-  87mebub9p4       en        Web   \n",
       "\n",
       "   signup_flow signup_method timestamp_first_active  \n",
       "0            0      facebook    2009-03-19 04:32:55  \n",
       "1            0      facebook    2009-05-23 17:48:09  \n",
       "2            3         basic    2009-06-09 23:12:47  \n",
       "3            0      facebook    2009-10-31 06:01:29  \n",
       "4            0         basic    2009-12-08 06:11:05  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all['date_account_created'] = pd.to_datetime(df_all['date_account_created'], format='%Y-%m-%d')\n",
    "df_all['timestamp_first_active'] = pd.to_datetime(df_all['timestamp_first_active'], format='%Y%m%d%H%M%S')\n",
    "\n",
    "df_all.head(n=5) # Only display a few lines and not the whole dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 4. Remove booking date field\n",
    "\n",
    "For every test data the column \"date_first_booking\" is missing, so we can't rely on this one for our prediction model.\n",
    "\n",
    "**=> We must delete the column**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Remove date_first_booking column\n",
    "df_all.drop('date_first_booking', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 5. Clean the Age column\n",
    "\n",
    "There are several age values that are clearly incorrect (unreasonably high or too low). \n",
    "\n",
    "In this step, we replace these incorrect values with \"NaN\", which literally stands for *Not a Number*, but implies we do not know the age value. In other words we are changing the incorrect values into missing values. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Remove outliers function\n",
    "def remove_outlier(x, min_val=15, max_val=90):\n",
    "    if np.isnan(x):\n",
    "        return np.nan\n",
    "    elif np.logical_or(x<=min_val, x>=max_val):\n",
    "        return np.nan\n",
    "    else:\n",
    "        return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>affiliate_channel</th>\n",
       "      <th>affiliate_provider</th>\n",
       "      <th>age</th>\n",
       "      <th>country_destination</th>\n",
       "      <th>date_account_created</th>\n",
       "      <th>first_affiliate_tracked</th>\n",
       "      <th>first_browser</th>\n",
       "      <th>first_device_type</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>language</th>\n",
       "      <th>signup_app</th>\n",
       "      <th>signup_flow</th>\n",
       "      <th>signup_method</th>\n",
       "      <th>timestamp_first_active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>208153</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>25.0</td>\n",
       "      <td>US</td>\n",
       "      <td>2014-06-21</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Firefox</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>MALE</td>\n",
       "      <td>28iy0ly0bj</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2014-06-21 09:31:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59291</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>24.0</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2013-02-06</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>MALE</td>\n",
       "      <td>291erqfv17</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2013-02-06 18:10:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152654</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>41.0</td>\n",
       "      <td>FR</td>\n",
       "      <td>2014-02-13</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>7jlh8y96b9</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-02-13 22:40:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70675</th>\n",
       "      <td>api</td>\n",
       "      <td>other</td>\n",
       "      <td>27.0</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2013-04-13</td>\n",
       "      <td>untracked</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>scnvq6fye4</td>\n",
       "      <td>en</td>\n",
       "      <td>iOS</td>\n",
       "      <td>12</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2013-04-13 01:13:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2543</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>US</td>\n",
       "      <td>2010-12-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>Other/Unknown</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>4wixfhqtzf</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2010-12-02 02:02:42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       affiliate_channel affiliate_provider   age country_destination  \\\n",
       "208153            direct             direct  25.0                  US   \n",
       "59291             direct             direct  24.0                 NDF   \n",
       "152654            direct             direct  41.0                  FR   \n",
       "70675                api              other  27.0                 NDF   \n",
       "2543              direct             direct  -1.0                  US   \n",
       "\n",
       "       date_account_created first_affiliate_tracked first_browser  \\\n",
       "208153           2014-06-21               untracked       Firefox   \n",
       "59291            2013-02-06               untracked        Chrome   \n",
       "152654           2014-02-13               untracked        Chrome   \n",
       "70675            2013-04-13               untracked     -unknown-   \n",
       "2543             2010-12-02                     NaN     -unknown-   \n",
       "\n",
       "       first_device_type  gender          id language signup_app  signup_flow  \\\n",
       "208153       Mac Desktop    MALE  28iy0ly0bj       en        Web            0   \n",
       "59291        Mac Desktop    MALE  291erqfv17       en        Web            0   \n",
       "152654       Mac Desktop  FEMALE  7jlh8y96b9       en        Web            0   \n",
       "70675             iPhone  FEMALE  scnvq6fye4       en        iOS           12   \n",
       "2543       Other/Unknown  FEMALE  4wixfhqtzf       en        Web            0   \n",
       "\n",
       "       signup_method timestamp_first_active  \n",
       "208153      facebook    2014-06-21 09:31:56  \n",
       "59291       facebook    2013-02-06 18:10:38  \n",
       "152654         basic    2014-02-13 22:40:42  \n",
       "70675       facebook    2013-04-13 01:13:09  \n",
       "2543           basic    2010-12-02 02:02:42  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fixing age column\n",
    "df_all['age'] = df_all['age'].apply(lambda x: remove_outlier(x, min_val=15, max_val=90))\n",
    "df_all['age'].fillna(-1, inplace=True)\n",
    "\n",
    "df_all.sample(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "As mentioned earlier, there are several more complicated ways to fill in the missing values in the age column. \n",
    "\n",
    "We are selecting this simple method for two main reasons:\n",
    "\n",
    "* **Clarity**: this workshop is going to be long enough without adding the complication of a complex methodology for imputing missing ages.\n",
    "\n",
    "* **Questionable results**: in my testing during the actual competition, I did test several more complex imputation methodologies. However, none of the methods I tested actually produced a better end result than the methodology outlined above.\n",
    "\n",
    "\n",
    "## 6. Converting Age Column to Integers\n",
    "\n",
    "Values inside the age columns are **floats**, it is better to have them as **integers**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>affiliate_channel</th>\n",
       "      <th>affiliate_provider</th>\n",
       "      <th>age</th>\n",
       "      <th>country_destination</th>\n",
       "      <th>date_account_created</th>\n",
       "      <th>first_affiliate_tracked</th>\n",
       "      <th>first_browser</th>\n",
       "      <th>first_device_type</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>language</th>\n",
       "      <th>signup_app</th>\n",
       "      <th>signup_flow</th>\n",
       "      <th>signup_method</th>\n",
       "      <th>timestamp_first_active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>98714</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2013-08-12</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Safari</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>pqq5j5hidw</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2013-08-12 19:10:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235645</th>\n",
       "      <td>sem-brand</td>\n",
       "      <td>google</td>\n",
       "      <td>45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2014-08-01</td>\n",
       "      <td>omg</td>\n",
       "      <td>IE</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>a8je0n2g3j</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-08-01 19:09:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209380</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2014-06-23</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome Mobile</td>\n",
       "      <td>Android Phone</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>mobm7ehrm0</td>\n",
       "      <td>en</td>\n",
       "      <td>Moweb</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-06-23 21:47:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273254</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2014-09-27</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome Mobile</td>\n",
       "      <td>Android Phone</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>9e5qum4w5i</td>\n",
       "      <td>en</td>\n",
       "      <td>Android</td>\n",
       "      <td>23</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-09-27 05:33:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85847</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2013-06-23</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Firefox</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>scdz1guxv7</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2013-06-23 05:41:27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       affiliate_channel affiliate_provider  age country_destination  \\\n",
       "98714             direct             direct   -1                 NDF   \n",
       "235645         sem-brand             google   45                 NaN   \n",
       "209380            direct             direct   -1                 NDF   \n",
       "273254            direct             direct   -1                 NaN   \n",
       "85847             direct             direct   -1                 NDF   \n",
       "\n",
       "       date_account_created first_affiliate_tracked  first_browser  \\\n",
       "98714            2013-08-12               untracked         Safari   \n",
       "235645           2014-08-01                     omg             IE   \n",
       "209380           2014-06-23               untracked  Chrome Mobile   \n",
       "273254           2014-09-27               untracked  Chrome Mobile   \n",
       "85847            2013-06-23               untracked        Firefox   \n",
       "\n",
       "       first_device_type     gender          id language signup_app  \\\n",
       "98714        Mac Desktop  -unknown-  pqq5j5hidw       en        Web   \n",
       "235645   Windows Desktop     FEMALE  a8je0n2g3j       en        Web   \n",
       "209380     Android Phone  -unknown-  mobm7ehrm0       en      Moweb   \n",
       "273254     Android Phone  -unknown-  9e5qum4w5i       en    Android   \n",
       "85847        Mac Desktop  -unknown-  scdz1guxv7       en        Web   \n",
       "\n",
       "        signup_flow signup_method timestamp_first_active  \n",
       "98714             0         basic    2013-08-12 19:10:56  \n",
       "235645            0         basic    2014-08-01 19:09:13  \n",
       "209380            0         basic    2014-06-23 21:47:54  \n",
       "273254           23         basic    2014-09-27 05:33:48  \n",
       "85847             0         basic    2013-06-23 05:41:27  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.age = df_all.age.astype(int)\n",
    "df_all.sample(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 7. Identify and fill additional columns with missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "country_destination => 62096 NaN Values\n",
      "first_affiliate_tracked => 6085 NaN Values\n"
     ]
    }
   ],
   "source": [
    "def check_NaN_Values_in_df(df):\n",
    "    # searching for NaN values is all the columns\n",
    "    for col in df:\n",
    "        nan_count = df[col].isnull().sum()\n",
    "\n",
    "        if nan_count != 0:\n",
    "            print (col + \" => \"+  str(nan_count) + \" NaN Values\")\n",
    "            \n",
    "check_NaN_Values_in_df(df_all) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "It is **absolutely normal** that <i>country_destination</i> contains NaN values, it is the output field. NaN Values are test data.\n",
    "\n",
    "However, **we need to fix** the column <i>first_affiliate_tracked</i> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "country_destination => 62096 NaN Values\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>affiliate_channel</th>\n",
       "      <th>affiliate_provider</th>\n",
       "      <th>age</th>\n",
       "      <th>country_destination</th>\n",
       "      <th>date_account_created</th>\n",
       "      <th>first_affiliate_tracked</th>\n",
       "      <th>first_browser</th>\n",
       "      <th>first_device_type</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>language</th>\n",
       "      <th>signup_app</th>\n",
       "      <th>signup_flow</th>\n",
       "      <th>signup_method</th>\n",
       "      <th>timestamp_first_active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>140602</th>\n",
       "      <td>other</td>\n",
       "      <td>padmapper</td>\n",
       "      <td>31</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2014-01-11</td>\n",
       "      <td>tracked-other</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>z4c7t8p8jn</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2014-01-11 19:37:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48957</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2012-11-18</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Mobile Safari</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>mus21jee9f</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2012-11-18 01:02:14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117129</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>37</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2013-10-15</td>\n",
       "      <td>linked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>MALE</td>\n",
       "      <td>utg1gr6non</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2013-10-15 23:06:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142229</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2014-01-15</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Firefox</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>lhtalzjjye</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-01-15 20:47:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60939</th>\n",
       "      <td>sem-non-brand</td>\n",
       "      <td>vast</td>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2013-02-18</td>\n",
       "      <td>omg</td>\n",
       "      <td>Firefox</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>p54kgtkzkf</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2013-02-18 00:51:29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       affiliate_channel affiliate_provider  age country_destination  \\\n",
       "140602             other          padmapper   31                 NDF   \n",
       "48957             direct             direct   -1                 NDF   \n",
       "117129            direct             direct   37                 NDF   \n",
       "142229            direct             direct   -1                 NDF   \n",
       "60939      sem-non-brand               vast   -1                 NDF   \n",
       "\n",
       "       date_account_created first_affiliate_tracked  first_browser  \\\n",
       "140602           2014-01-11           tracked-other         Chrome   \n",
       "48957            2012-11-18               untracked  Mobile Safari   \n",
       "117129           2013-10-15                  linked         Chrome   \n",
       "142229           2014-01-15               untracked        Firefox   \n",
       "60939            2013-02-18                     omg        Firefox   \n",
       "\n",
       "       first_device_type     gender          id language signup_app  \\\n",
       "140602       Mac Desktop     FEMALE  z4c7t8p8jn       en        Web   \n",
       "48957             iPhone  -unknown-  mus21jee9f       en        Web   \n",
       "117129   Windows Desktop       MALE  utg1gr6non       en        Web   \n",
       "142229   Windows Desktop  -unknown-  lhtalzjjye       en        Web   \n",
       "60939        Mac Desktop  -unknown-  p54kgtkzkf       en        Web   \n",
       "\n",
       "        signup_flow signup_method timestamp_first_active  \n",
       "140602            0      facebook    2014-01-11 19:37:22  \n",
       "48957             0         basic    2012-11-18 01:02:14  \n",
       "117129            0         basic    2013-10-15 23:06:15  \n",
       "142229            0         basic    2014-01-15 20:47:28  \n",
       "60939             0         basic    2013-02-18 00:51:29  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all['first_affiliate_tracked'].fillna(-1, inplace=True)\n",
    "\n",
    "# We check there is no more NaN except in country_destination column\n",
    "check_NaN_Values_in_df(df_all) \n",
    "\n",
    "df_all.sample(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 8. Removing Users which registred before Feb. 2013"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>affiliate_channel</th>\n",
       "      <th>affiliate_provider</th>\n",
       "      <th>age</th>\n",
       "      <th>country_destination</th>\n",
       "      <th>date_account_created</th>\n",
       "      <th>first_affiliate_tracked</th>\n",
       "      <th>first_browser</th>\n",
       "      <th>first_device_type</th>\n",
       "      <th>gender</th>\n",
       "      <th>id</th>\n",
       "      <th>language</th>\n",
       "      <th>signup_app</th>\n",
       "      <th>signup_flow</th>\n",
       "      <th>signup_method</th>\n",
       "      <th>timestamp_first_active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>132982</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-12-16</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Firefox</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>qc6w4nzs6m</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2013-12-16 23:03:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156708</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2014-02-25</td>\n",
       "      <td>-1</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>Other/Unknown</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>vjm5e8lzo6</td>\n",
       "      <td>en</td>\n",
       "      <td>iOS</td>\n",
       "      <td>25</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-02-25 08:10:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244192</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2014-08-13</td>\n",
       "      <td>untracked</td>\n",
       "      <td>IE</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>MALE</td>\n",
       "      <td>2813vaojmt</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-08-13 19:27:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202547</th>\n",
       "      <td>seo</td>\n",
       "      <td>facebook</td>\n",
       "      <td>-1</td>\n",
       "      <td>other</td>\n",
       "      <td>2014-06-10</td>\n",
       "      <td>untracked</td>\n",
       "      <td>IE</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>0hjij5mpba</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-06-10 18:43:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143304</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>GB</td>\n",
       "      <td>2014-01-18</td>\n",
       "      <td>linked</td>\n",
       "      <td>Safari</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>dxvop5t8lb</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-01-18 05:31:38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       affiliate_channel affiliate_provider  age country_destination  \\\n",
       "132982            direct             direct   -1                  US   \n",
       "156708            direct             direct   -1                 NDF   \n",
       "244192            direct             direct   38                 NaN   \n",
       "202547               seo           facebook   -1               other   \n",
       "143304            direct             direct   -1                  GB   \n",
       "\n",
       "       date_account_created first_affiliate_tracked first_browser  \\\n",
       "132982           2013-12-16               untracked       Firefox   \n",
       "156708           2014-02-25                      -1     -unknown-   \n",
       "244192           2014-08-13               untracked            IE   \n",
       "202547           2014-06-10               untracked            IE   \n",
       "143304           2014-01-18                  linked        Safari   \n",
       "\n",
       "       first_device_type     gender          id language signup_app  \\\n",
       "132982   Windows Desktop  -unknown-  qc6w4nzs6m       en        Web   \n",
       "156708     Other/Unknown  -unknown-  vjm5e8lzo6       en        iOS   \n",
       "244192   Windows Desktop       MALE  2813vaojmt       en        Web   \n",
       "202547   Windows Desktop  -unknown-  0hjij5mpba       en        Web   \n",
       "143304       Mac Desktop  -unknown-  dxvop5t8lb       en        Web   \n",
       "\n",
       "        signup_flow signup_method timestamp_first_active  \n",
       "132982            0         basic    2013-12-16 23:03:56  \n",
       "156708           25         basic    2014-02-25 08:10:05  \n",
       "244192            0         basic    2014-08-13 19:27:22  \n",
       "202547            0         basic    2014-06-10 18:43:44  \n",
       "143304            0         basic    2014-01-18 05:31:38  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all = df_all[df_all['date_account_created'] > '2013-02-01']\n",
    "df_all.sample(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 9. Saving the DataFrame to csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# We create the output directory if necessary\n",
    "if not os.path.exists(\"output\"):\n",
    "    os.makedirs(\"output\")\n",
    "    \n",
    "# We export to csv\n",
    "df_all.to_csv(\"output/cleaned.csv\", sep=',', index=False)"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}