Machine Learning - Hands on Lab - Session #3 - Feature Engineering.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": [
    "## 1. Loading the Python libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "from datetime import datetime\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import sklearn as sk"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 2. Re-Import the Check NaN Function from Part 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "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\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 3. Loading in the Data from Session #2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>55773</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>-1</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-10-05</td>\n",
       "      <td>omg</td>\n",
       "      <td>Safari</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>e5uj93n1or</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2013-10-05 00:15:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37575</th>\n",
       "      <td>sem-non-brand</td>\n",
       "      <td>google</td>\n",
       "      <td>40</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2013-08-03</td>\n",
       "      <td>omg</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>bulf1euo01</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2013-08-03 02:18:24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112022</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>29</td>\n",
       "      <td>US</td>\n",
       "      <td>2014-04-02</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Safari</td>\n",
       "      <td>Mac Desktop</td>\n",
       "      <td>MALE</td>\n",
       "      <td>68wfudy756</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2014-04-02 02:14:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36398</th>\n",
       "      <td>direct</td>\n",
       "      <td>direct</td>\n",
       "      <td>36</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-07-29</td>\n",
       "      <td>untracked</td>\n",
       "      <td>Chrome</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>91nzc3szqc</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2013-07-29 20:01:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48670</th>\n",
       "      <td>sem-brand</td>\n",
       "      <td>google</td>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2013-09-13</td>\n",
       "      <td>omg</td>\n",
       "      <td>IE</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>-unknown-</td>\n",
       "      <td>te76y9uto9</td>\n",
       "      <td>en</td>\n",
       "      <td>Web</td>\n",
       "      <td>0</td>\n",
       "      <td>basic</td>\n",
       "      <td>2013-09-13 04:43:46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       affiliate_channel affiliate_provider  age country_destination  \\\n",
       "55773             direct             direct   -1                  US   \n",
       "37575      sem-non-brand             google   40                 NDF   \n",
       "112022            direct             direct   29                  US   \n",
       "36398             direct             direct   36                  US   \n",
       "48670          sem-brand             google   -1                 NDF   \n",
       "\n",
       "       date_account_created first_affiliate_tracked first_browser  \\\n",
       "55773            2013-10-05                     omg        Safari   \n",
       "37575            2013-08-03                     omg        Chrome   \n",
       "112022           2014-04-02               untracked        Safari   \n",
       "36398            2013-07-29               untracked        Chrome   \n",
       "48670            2013-09-13                     omg            IE   \n",
       "\n",
       "       first_device_type     gender          id language signup_app  \\\n",
       "55773        Mac Desktop     FEMALE  e5uj93n1or       en        Web   \n",
       "37575    Windows Desktop     FEMALE  bulf1euo01       en        Web   \n",
       "112022       Mac Desktop       MALE  68wfudy756       en        Web   \n",
       "36398    Windows Desktop     FEMALE  91nzc3szqc       en        Web   \n",
       "48670    Windows Desktop  -unknown-  te76y9uto9       en        Web   \n",
       "\n",
       "        signup_flow signup_method timestamp_first_active  \n",
       "55773             0         basic    2013-10-05 00:15:28  \n",
       "37575             0      facebook    2013-08-03 02:18:24  \n",
       "112022            0         basic    2014-04-02 02:14:02  \n",
       "36398             0         basic    2013-07-29 20:01:42  \n",
       "48670             0         basic    2013-09-13 04:43:46  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all = pd.read_csv(\n",
    "    \"output/cleaned.csv\", \n",
    "    dtype={\n",
    "        'country_destination': str\n",
    "    }\n",
    ")\n",
    "\n",
    "# We transform again the date column into datetime\n",
    "df_all['date_account_created'] = pd.to_datetime(df_all['date_account_created'], format='%Y-%m-%d %H:%M:%S')\n",
    "df_all['timestamp_first_active'] = pd.to_datetime(df_all['timestamp_first_active'], format='%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "# Check for NaN Values => We must find: country_destination => 62096 NaN Values\n",
    "check_NaN_Values_in_df(df_all) \n",
    "\n",
    "df_all.sample(n=5) # Only display a few lines and not the whole dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 3. Transforming Categorical Data\n",
    "\n",
    "Let's go for some **One Hot Encoding** - replacing the categorical fields in the dataset with multiple columns representing one value from each column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Home made One Hot Encoding function\n",
    "def convert_to_binary(df, column_to_convert):\n",
    "    categories = list(df[column_to_convert].drop_duplicates())\n",
    "\n",
    "    for category in categories:\n",
    "        cat_name = str(category).replace(\" \", \"_\").replace(\"(\", \"\").replace(\")\", \"\").replace(\"/\", \"_\").replace(\"-\", \"\").lower()\n",
    "        col_name = column_to_convert[:5] + '_' + cat_name[:10]\n",
    "        df[col_name] = 0\n",
    "        df.loc[(df[column_to_convert] == category), col_name] = 1\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true,
    "scrolled": 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>age</th>\n",
       "      <th>country_destination</th>\n",
       "      <th>date_account_created</th>\n",
       "      <th>id</th>\n",
       "      <th>timestamp_first_active</th>\n",
       "      <th>gende_other</th>\n",
       "      <th>gende_female</th>\n",
       "      <th>gende_male</th>\n",
       "      <th>gende_unknown</th>\n",
       "      <th>signu_basic</th>\n",
       "      <th>...</th>\n",
       "      <th>first_crazy_brow</th>\n",
       "      <th>first_stainless</th>\n",
       "      <th>first_coolnovo</th>\n",
       "      <th>first_opera_mini</th>\n",
       "      <th>first_googlebot</th>\n",
       "      <th>first_outlook_20</th>\n",
       "      <th>first_icedragon</th>\n",
       "      <th>first_ibrowse</th>\n",
       "      <th>first_nintendo_b</th>\n",
       "      <th>first_uc_browser</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>142404</th>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2014-06-07</td>\n",
       "      <td>deep65lgfb</td>\n",
       "      <td>2014-06-07 15:51:39</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106537</th>\n",
       "      <td>-1</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2014-03-19</td>\n",
       "      <td>54deuw2k8p</td>\n",
       "      <td>2014-03-19 07:51:15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152310</th>\n",
       "      <td>44</td>\n",
       "      <td>US</td>\n",
       "      <td>2014-06-26</td>\n",
       "      <td>ce5sk93xqk</td>\n",
       "      <td>2014-06-26 02:45:49</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136061</th>\n",
       "      <td>22</td>\n",
       "      <td>NDF</td>\n",
       "      <td>2014-05-25</td>\n",
       "      <td>hp5urn796c</td>\n",
       "      <td>2014-05-25 02:47:10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209674</th>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2014-09-18</td>\n",
       "      <td>4nmf77dlpb</td>\n",
       "      <td>2014-09-18 23:30:03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 140 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        age country_destination date_account_created          id  \\\n",
       "142404   -1                 NDF           2014-06-07  deep65lgfb   \n",
       "106537   -1                 NDF           2014-03-19  54deuw2k8p   \n",
       "152310   44                  US           2014-06-26  ce5sk93xqk   \n",
       "136061   22                 NDF           2014-05-25  hp5urn796c   \n",
       "209674   -1                 NaN           2014-09-18  4nmf77dlpb   \n",
       "\n",
       "       timestamp_first_active  gende_other  gende_female  gende_male  \\\n",
       "142404    2014-06-07 15:51:39            0             0           0   \n",
       "106537    2014-03-19 07:51:15            0             0           0   \n",
       "152310    2014-06-26 02:45:49            0             0           0   \n",
       "136061    2014-05-25 02:47:10            0             0           0   \n",
       "209674    2014-09-18 23:30:03            0             0           0   \n",
       "\n",
       "        gende_unknown  signu_basic        ...         first_crazy_brow  \\\n",
       "142404              1            1        ...                        0   \n",
       "106537              1            1        ...                        0   \n",
       "152310              1            1        ...                        0   \n",
       "136061              1            1        ...                        0   \n",
       "209674              1            1        ...                        0   \n",
       "\n",
       "        first_stainless  first_coolnovo  first_opera_mini  first_googlebot  \\\n",
       "142404                0               0                 0                0   \n",
       "106537                0               0                 0                0   \n",
       "152310                0               0                 0                0   \n",
       "136061                0               0                 0                0   \n",
       "209674                0               0                 0                0   \n",
       "\n",
       "        first_outlook_20  first_icedragon  first_ibrowse  first_nintendo_b  \\\n",
       "142404                 0                0              0                 0   \n",
       "106537                 0                0              0                 0   \n",
       "152310                 0                0              0                 0   \n",
       "136061                 0                0              0                 0   \n",
       "209674                 0                0              0                 0   \n",
       "\n",
       "        first_uc_browser  \n",
       "142404                 0  \n",
       "106537                 0  \n",
       "152310                 0  \n",
       "136061                 0  \n",
       "209674                 0  \n",
       "\n",
       "[5 rows x 140 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_to_convert = [\n",
    "    'gender', \n",
    "    'signup_method', \n",
    "    'signup_flow', \n",
    "    'language', \n",
    "    'affiliate_channel', \n",
    "    'affiliate_provider', \n",
    "    'first_affiliate_tracked', \n",
    "    'signup_app', \n",
    "    'first_device_type', \n",
    "    'first_browser'\n",
    "]\n",
    "\n",
    "# One Hot Encoding\n",
    "for column in columns_to_convert:\n",
    "    df_all = convert_to_binary(df=df_all, column_to_convert=column)\n",
    "    df_all.drop(column, axis=1, inplace=True)\n",
    "    \n",
    "df_all.sample(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe Shape: (216973, 147)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>id</th>\n",
       "      <th>gende_other</th>\n",
       "      <th>gende_female</th>\n",
       "      <th>gende_male</th>\n",
       "      <th>gende_unknown</th>\n",
       "      <th>signu_basic</th>\n",
       "      <th>signu_facebook</th>\n",
       "      <th>signu_google</th>\n",
       "      <th>signu_weibo</th>\n",
       "      <th>...</th>\n",
       "      <th>day_account_created</th>\n",
       "      <th>month_account_created</th>\n",
       "      <th>quarter_account_created</th>\n",
       "      <th>year_account_created</th>\n",
       "      <th>hour_first_active</th>\n",
       "      <th>day_first_active</th>\n",
       "      <th>month_first_active</th>\n",
       "      <th>quarter_first_active</th>\n",
       "      <th>year_first_active</th>\n",
       "      <th>created_less_active</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4754</th>\n",
       "      <td>24</td>\n",
       "      <td>ip1v9qfz6y</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2013</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2013</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117075</th>\n",
       "      <td>43</td>\n",
       "      <td>vy4yd3hwjd</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2014</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2014</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41724</th>\n",
       "      <td>36</td>\n",
       "      <td>254dbgjuad</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2013</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2013</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214395</th>\n",
       "      <td>25</td>\n",
       "      <td>iwb4jwqu8c</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>2014</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>2014</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131841</th>\n",
       "      <td>33</td>\n",
       "      <td>zcomr2n3i7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2014</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2014</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 147 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        age          id  gende_other  gende_female  gende_male  gende_unknown  \\\n",
       "4754     24  ip1v9qfz6y            0             1           0              0   \n",
       "117075   43  vy4yd3hwjd            0             0           1              0   \n",
       "41724    36  254dbgjuad            0             0           0              1   \n",
       "214395   25  iwb4jwqu8c            0             1           0              0   \n",
       "131841   33  zcomr2n3i7            0             0           1              0   \n",
       "\n",
       "        signu_basic  signu_facebook  signu_google  signu_weibo  \\\n",
       "4754              0               1             0            0   \n",
       "117075            1               0             0            0   \n",
       "41724             1               0             0            0   \n",
       "214395            1               0             0            0   \n",
       "131841            1               0             0            0   \n",
       "\n",
       "               ...           day_account_created  month_account_created  \\\n",
       "4754           ...                             0                      3   \n",
       "117075         ...                             0                      4   \n",
       "41724          ...                             6                      8   \n",
       "214395         ...                             4                      9   \n",
       "131841         ...                             4                      5   \n",
       "\n",
       "        quarter_account_created  year_account_created  hour_first_active  \\\n",
       "4754                          1                  2013                  1   \n",
       "117075                        2                  2014                 19   \n",
       "41724                         3                  2013                  5   \n",
       "214395                        3                  2014                 20   \n",
       "131841                        2                  2014                 21   \n",
       "\n",
       "        day_first_active  month_first_active  quarter_first_active  \\\n",
       "4754                   0                   3                     1   \n",
       "117075                 0                   4                     2   \n",
       "41724                  6                   8                     3   \n",
       "214395                 4                   9                     3   \n",
       "131841                 4                   5                     2   \n",
       "\n",
       "        year_first_active  created_less_active  \n",
       "4754                 2013                   -1  \n",
       "117075               2014                   -1  \n",
       "41724                2013                   -1  \n",
       "214395               2014                   -1  \n",
       "131841               2014                   -1  \n",
       "\n",
       "[5 rows x 147 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add new date related fields\n",
    "df_all['day_account_created'] = df_all['date_account_created'].dt.weekday\n",
    "df_all['month_account_created'] = df_all['date_account_created'].dt.month\n",
    "df_all['quarter_account_created'] = df_all['date_account_created'].dt.quarter\n",
    "df_all['year_account_created'] = df_all['date_account_created'].dt.year\n",
    "df_all['hour_first_active'] = df_all['timestamp_first_active'].dt.hour\n",
    "df_all['day_first_active'] = df_all['timestamp_first_active'].dt.weekday\n",
    "df_all['month_first_active'] = df_all['timestamp_first_active'].dt.month\n",
    "df_all['quarter_first_active'] = df_all['timestamp_first_active'].dt.quarter\n",
    "df_all['year_first_active'] = df_all['timestamp_first_active'].dt.year\n",
    "df_all['created_less_active'] = (df_all['date_account_created'] - df_all['timestamp_first_active']).dt.days\n",
    "\n",
    "# Drop unnecessary columns\n",
    "columns_to_drop = ['date_account_created', 'timestamp_first_active', 'date_first_booking', 'country_destination']\n",
    "for column in columns_to_drop:\n",
    "    if column in df_all.columns:\n",
    "        df_all.drop(column, axis=1, inplace=True)\n",
    "\n",
    "print (\"Dataframe Shape:\", df_all.shape)\n",
    "df_all.sample(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 5. Mid - Conclusion\n",
    "\n",
    "We had **14 Columns**, we now have **147 columns**. \n",
    "\n",
    "It seems like a lot, however we mostly just have **restructured** the information and only created **10 columns**\n",
    "\n",
    "\n",
    "## 6. Adding new data\n",
    "\n",
    "### 6.1. Understanding session.csv\n",
    "Exactly like we did with the training / testing data. We now investigate session data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DF Session Shape: (10567737, 6)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>action</th>\n",
       "      <th>action_type</th>\n",
       "      <th>action_detail</th>\n",
       "      <th>device_type</th>\n",
       "      <th>secs_elapsed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d1mm9tcy42</td>\n",
       "      <td>lookup</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>319.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d1mm9tcy42</td>\n",
       "      <td>search_results</td>\n",
       "      <td>click</td>\n",
       "      <td>view_search_results</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>67753.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d1mm9tcy42</td>\n",
       "      <td>lookup</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d1mm9tcy42</td>\n",
       "      <td>search_results</td>\n",
       "      <td>click</td>\n",
       "      <td>view_search_results</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>22141.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>d1mm9tcy42</td>\n",
       "      <td>lookup</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Windows Desktop</td>\n",
       "      <td>435.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      user_id          action action_type        action_detail  \\\n",
       "0  d1mm9tcy42          lookup         NaN                  NaN   \n",
       "1  d1mm9tcy42  search_results       click  view_search_results   \n",
       "2  d1mm9tcy42          lookup         NaN                  NaN   \n",
       "3  d1mm9tcy42  search_results       click  view_search_results   \n",
       "4  d1mm9tcy42          lookup         NaN                  NaN   \n",
       "\n",
       "       device_type  secs_elapsed  \n",
       "0  Windows Desktop         319.0  \n",
       "1  Windows Desktop       67753.0  \n",
       "2  Windows Desktop         301.0  \n",
       "3  Windows Desktop       22141.0  \n",
       "4  Windows Desktop         435.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sessions = pd.read_csv(\"data/sessions.csv\")\n",
    "print (\"DF Session Shape:\", df_sessions.shape)\n",
    "df_sessions.head(n=5) # Only display a few lines and not the whole dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### 6.2. Cleaning and Transforming the Data\n",
    "\n",
    "#### 6.2.1. Extract the primary and secondary devices for each user\n",
    "\n",
    "The first piece of information we are going to extract is the primary and secondary device for each user. \n",
    "\n",
    "How do we determine what is the user's primary and secondary devices are? We look at how much time they spent on each device."
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>primary_secs</th>\n",
       "      <th>prima_windows_de</th>\n",
       "      <th>prima_mac_deskto</th>\n",
       "      <th>prima_iphone</th>\n",
       "      <th>prima_ipad_table</th>\n",
       "      <th>prima_unknown</th>\n",
       "      <th>prima_android_ap</th>\n",
       "      <th>prima_linux_desk</th>\n",
       "      <th>prima_tablet</th>\n",
       "      <th>prima_chromebook</th>\n",
       "      <th>prima_android_ph</th>\n",
       "      <th>prima_ipodtouch</th>\n",
       "      <th>prima_blackberry</th>\n",
       "      <th>prima_windows_ph</th>\n",
       "      <th>prima_opera_phon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>95538</th>\n",
       "      <td>p66tzhxm53</td>\n",
       "      <td>205597.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41319</th>\n",
       "      <td>vazdmbkqe8</td>\n",
       "      <td>1290600.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1926</th>\n",
       "      <td>mvy42eiv5g</td>\n",
       "      <td>3125968.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163608</th>\n",
       "      <td>6w7z8r9k5v</td>\n",
       "      <td>574355.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151785</th>\n",
       "      <td>idzwwxu4iy</td>\n",
       "      <td>191851.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           user_id  primary_secs  prima_windows_de  prima_mac_deskto  \\\n",
       "95538   p66tzhxm53      205597.0                 1                 0   \n",
       "41319   vazdmbkqe8     1290600.0                 0                 0   \n",
       "1926    mvy42eiv5g     3125968.0                 1                 0   \n",
       "163608  6w7z8r9k5v      574355.0                 0                 1   \n",
       "151785  idzwwxu4iy      191851.0                 1                 0   \n",
       "\n",
       "        prima_iphone  prima_ipad_table  prima_unknown  prima_android_ap  \\\n",
       "95538              0                 0              0                 0   \n",
       "41319              0                 0              0                 1   \n",
       "1926               0                 0              0                 0   \n",
       "163608             0                 0              0                 0   \n",
       "151785             0                 0              0                 0   \n",
       "\n",
       "        prima_linux_desk  prima_tablet  prima_chromebook  prima_android_ph  \\\n",
       "95538                  0             0                 0                 0   \n",
       "41319                  0             0                 0                 0   \n",
       "1926                   0             0                 0                 0   \n",
       "163608                 0             0                 0                 0   \n",
       "151785                 0             0                 0                 0   \n",
       "\n",
       "        prima_ipodtouch  prima_blackberry  prima_windows_ph  prima_opera_phon  \n",
       "95538                 0                 0                 0                 0  \n",
       "41319                 0                 0                 0                 0  \n",
       "1926                  0                 0                 0                 0  \n",
       "163608                0                 0                 0                 0  \n",
       "151785                0                 0                 0                 0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Determine primary device\n",
    "sessions_device = df_sessions.loc[:, ['user_id', 'device_type', 'secs_elapsed']]\n",
    "aggregated_lvl1 = sessions_device.groupby(['user_id', 'device_type'], as_index=False, sort=False).aggregate(np.sum)\n",
    "idx = aggregated_lvl1.groupby(['user_id'], sort=False)['secs_elapsed'].transform(max) == aggregated_lvl1['secs_elapsed']\n",
    "df_primary = pd.DataFrame(aggregated_lvl1.loc[idx , ['user_id', 'device_type', 'secs_elapsed']])\n",
    "df_primary.rename(columns = {'device_type':'primary_device', 'secs_elapsed':'primary_secs'}, inplace=True)\n",
    "df_primary = convert_to_binary(df=df_primary, column_to_convert='primary_device')\n",
    "df_primary.drop('primary_device', axis=1, inplace=True)\n",
    "\n",
    "df_primary.sample(n=5)"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>secondary_secs</th>\n",
       "      <th>secon_unknown</th>\n",
       "      <th>secon_android_ph</th>\n",
       "      <th>secon_ipad_table</th>\n",
       "      <th>secon_android_ap</th>\n",
       "      <th>secon_mac_deskto</th>\n",
       "      <th>secon_iphone</th>\n",
       "      <th>secon_windows_de</th>\n",
       "      <th>secon_linux_desk</th>\n",
       "      <th>secon_tablet</th>\n",
       "      <th>secon_blackberry</th>\n",
       "      <th>secon_windows_ph</th>\n",
       "      <th>secon_chromebook</th>\n",
       "      <th>secon_opera_phon</th>\n",
       "      <th>secon_ipodtouch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30956</th>\n",
       "      <td>wlwif7w7y0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51574</th>\n",
       "      <td>7ir5ybbf36</td>\n",
       "      <td>372427.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92738</th>\n",
       "      <td>r3s2x12bta</td>\n",
       "      <td>90286.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95318</th>\n",
       "      <td>sex52f0sbh</td>\n",
       "      <td>157173.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131709</th>\n",
       "      <td>x2izlb17o3</td>\n",
       "      <td>263078.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           user_id  secondary_secs  secon_unknown  secon_android_ph  \\\n",
       "30956   wlwif7w7y0           227.0              1                 0   \n",
       "51574   7ir5ybbf36        372427.0              0                 0   \n",
       "92738   r3s2x12bta         90286.0              1                 0   \n",
       "95318   sex52f0sbh        157173.0              0                 0   \n",
       "131709  x2izlb17o3        263078.0              0                 0   \n",
       "\n",
       "        secon_ipad_table  secon_android_ap  secon_mac_deskto  secon_iphone  \\\n",
       "30956                  0                 0                 0             0   \n",
       "51574                  0                 0                 1             0   \n",
       "92738                  0                 0                 0             0   \n",
       "95318                  0                 0                 0             1   \n",
       "131709                 0                 0                 1             0   \n",
       "\n",
       "        secon_windows_de  secon_linux_desk  secon_tablet  secon_blackberry  \\\n",
       "30956                  0                 0             0                 0   \n",
       "51574                  0                 0             0                 0   \n",
       "92738                  0                 0             0                 0   \n",
       "95318                  0                 0             0                 0   \n",
       "131709                 0                 0             0                 0   \n",
       "\n",
       "        secon_windows_ph  secon_chromebook  secon_opera_phon  secon_ipodtouch  \n",
       "30956                  0                 0                 0                0  \n",
       "51574                  0                 0                 0                0  \n",
       "92738                  0                 0                 0                0  \n",
       "95318                  0                 0                 0                0  \n",
       "131709                 0                 0                 0                0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Determine Secondary device\n",
    "remaining = aggregated_lvl1.drop(aggregated_lvl1.index[idx])\n",
    "idx = remaining.groupby(['user_id'], sort=False)['secs_elapsed'].transform(max) == remaining['secs_elapsed']\n",
    "df_secondary = pd.DataFrame(remaining.loc[idx , ['user_id', 'device_type', 'secs_elapsed']])\n",
    "df_secondary.rename(columns = {'device_type':'secondary_device', 'secs_elapsed':'secondary_secs'}, inplace=True)\n",
    "df_secondary = convert_to_binary(df=df_secondary, column_to_convert='secondary_device')\n",
    "df_secondary.drop('secondary_device', axis=1, inplace=True)\n",
    "\n",
    "df_secondary.sample(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### 6.2.2. Determine Counts of Actions\n",
    "\n",
    "The next thing we are going to do is take counts of how many times each action was taken by each user. This is a two-step process. \n",
    "\n",
    "To handle the multiple action columns, we repeat these steps for each column individually, effectively creating three separate tables. \n",
    "\n",
    "Because we have now created tables where each row represents one user, we can now **join these three tables together** on the basis of the user id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Count occurrences of value in a column\n",
    "def convert_to_counts(df, id_col, column_to_convert):\n",
    "    id_list = df[id_col].drop_duplicates()\n",
    "    \n",
    "    df_counts = df.loc[:,[id_col, column_to_convert]]\n",
    "    df_counts['count'] = 1\n",
    "    df_counts = df_counts.groupby(by=[id_col, column_to_convert], as_index=False, sort=False).sum()\n",
    "    \n",
    "    new_df = df_counts.pivot(index=id_col, columns=column_to_convert, values='count')\n",
    "    new_df = new_df.fillna(0)\n",
    "    \n",
    "    # Rename Columns\n",
    "    categories = list(df[column_to_convert].drop_duplicates())\n",
    "    for category in categories:\n",
    "        cat_name = str(category).replace(\" \", \"_\").replace(\"(\", \"\").replace(\")\", \"\").replace(\"/\", \"_\").replace(\"-\", \"\").lower()\n",
    "        col_name = column_to_convert + '_' + cat_name\n",
    "        new_df.rename(columns = {category:col_name}, inplace=True)\n",
    "        \n",
    "    return new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Converting action column...\n",
      "Converting action_type column...\n",
      "Converting action_detail column...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>action_10</th>\n",
       "      <th>action_11</th>\n",
       "      <th>action_12</th>\n",
       "      <th>action_15</th>\n",
       "      <th>action_about_us</th>\n",
       "      <th>action_accept_decline</th>\n",
       "      <th>action_account</th>\n",
       "      <th>action_acculynk_bin_check_failed</th>\n",
       "      <th>action_acculynk_bin_check_success</th>\n",
       "      <th>action_acculynk_load_pin_pad</th>\n",
       "      <th>...</th>\n",
       "      <th>action_detail_view_resolutions</th>\n",
       "      <th>action_detail_view_search_results</th>\n",
       "      <th>action_detail_view_security_checks</th>\n",
       "      <th>action_detail_view_user_real_names</th>\n",
       "      <th>action_detail_wishlist</th>\n",
       "      <th>action_detail_wishlist_content_update</th>\n",
       "      <th>action_detail_wishlist_note</th>\n",
       "      <th>action_detail_your_listings</th>\n",
       "      <th>action_detail_your_reservations</th>\n",
       "      <th>action_detail_your_trips</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5rj93jxuae</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>t6nsyzlmnk</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vd7n1e4loq</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1fvf8hr8tn</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kk3rgd1e2g</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 527 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            action_10  action_11  action_12  action_15  action_about_us  \\\n",
       "user_id                                                                   \n",
       "5rj93jxuae        0.0        0.0        0.0        0.0              0.0   \n",
       "t6nsyzlmnk        0.0        0.0        0.0        0.0              0.0   \n",
       "vd7n1e4loq        0.0        0.0        0.0        0.0              0.0   \n",
       "1fvf8hr8tn        0.0        0.0        0.0        0.0              0.0   \n",
       "kk3rgd1e2g        0.0        0.0        0.0        0.0              0.0   \n",
       "\n",
       "            action_accept_decline  action_account  \\\n",
       "user_id                                             \n",
       "5rj93jxuae                    0.0             0.0   \n",
       "t6nsyzlmnk                    0.0             0.0   \n",
       "vd7n1e4loq                    0.0             0.0   \n",
       "1fvf8hr8tn                    0.0             0.0   \n",
       "kk3rgd1e2g                    0.0             0.0   \n",
       "\n",
       "            action_acculynk_bin_check_failed  \\\n",
       "user_id                                        \n",
       "5rj93jxuae                               0.0   \n",
       "t6nsyzlmnk                               0.0   \n",
       "vd7n1e4loq                               0.0   \n",
       "1fvf8hr8tn                               0.0   \n",
       "kk3rgd1e2g                               0.0   \n",
       "\n",
       "            action_acculynk_bin_check_success  action_acculynk_load_pin_pad  \\\n",
       "user_id                                                                       \n",
       "5rj93jxuae                                0.0                           0.0   \n",
       "t6nsyzlmnk                                0.0                           0.0   \n",
       "vd7n1e4loq                                0.0                           0.0   \n",
       "1fvf8hr8tn                                0.0                           0.0   \n",
       "kk3rgd1e2g                                0.0                           0.0   \n",
       "\n",
       "                      ...             action_detail_view_resolutions  \\\n",
       "user_id               ...                                              \n",
       "5rj93jxuae            ...                                        0.0   \n",
       "t6nsyzlmnk            ...                                        0.0   \n",
       "vd7n1e4loq            ...                                        0.0   \n",
       "1fvf8hr8tn            ...                                        0.0   \n",
       "kk3rgd1e2g            ...                                        0.0   \n",
       "\n",
       "            action_detail_view_search_results  \\\n",
       "user_id                                         \n",
       "5rj93jxuae                               30.0   \n",
       "t6nsyzlmnk                               10.0   \n",
       "vd7n1e4loq                               36.0   \n",
       "1fvf8hr8tn                                0.0   \n",
       "kk3rgd1e2g                               59.0   \n",
       "\n",
       "            action_detail_view_security_checks  \\\n",
       "user_id                                          \n",
       "5rj93jxuae                                 0.0   \n",
       "t6nsyzlmnk                                 0.0   \n",
       "vd7n1e4loq                                 0.0   \n",
       "1fvf8hr8tn                                 0.0   \n",
       "kk3rgd1e2g                                 0.0   \n",
       "\n",
       "            action_detail_view_user_real_names  action_detail_wishlist  \\\n",
       "user_id                                                                  \n",
       "5rj93jxuae                                 0.0                     0.0   \n",
       "t6nsyzlmnk                                 0.0                     0.0   \n",
       "vd7n1e4loq                                 0.0                     0.0   \n",
       "1fvf8hr8tn                                 0.0                     0.0   \n",
       "kk3rgd1e2g                                 0.0                     0.0   \n",
       "\n",
       "            action_detail_wishlist_content_update  \\\n",
       "user_id                                             \n",
       "5rj93jxuae                                   13.0   \n",
       "t6nsyzlmnk                                    0.0   \n",
       "vd7n1e4loq                                   32.0   \n",
       "1fvf8hr8tn                                    0.0   \n",
       "kk3rgd1e2g                                    1.0   \n",
       "\n",
       "            action_detail_wishlist_note  action_detail_your_listings  \\\n",
       "user_id                                                                \n",
       "5rj93jxuae                          0.0                          0.0   \n",
       "t6nsyzlmnk                          0.0                          0.0   \n",
       "vd7n1e4loq                          0.0                          0.0   \n",
       "1fvf8hr8tn                          0.0                          2.0   \n",
       "kk3rgd1e2g                          0.0                          0.0   \n",
       "\n",
       "            action_detail_your_reservations  action_detail_your_trips  \n",
       "user_id                                                                \n",
       "5rj93jxuae                              0.0                      24.0  \n",
       "t6nsyzlmnk                              0.0                       0.0  \n",
       "vd7n1e4loq                              0.0                       0.0  \n",
       "1fvf8hr8tn                              0.0                       0.0  \n",
       "kk3rgd1e2g                              0.0                       0.0  \n",
       "\n",
       "[5 rows x 527 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Aggregate and combine actions taken columns\n",
    "\n",
    "session_actions = df_sessions.loc[:,['user_id', 'action', 'action_type', 'action_detail']]\n",
    "columns_to_convert = ['action', 'action_type', 'action_detail']\n",
    "\n",
    "session_actions = session_actions.fillna('not provided')\n",
    "first = True\n",
    "\n",
    "for column in columns_to_convert:\n",
    "    print(\"Converting \" + column + \" column...\")\n",
    "    current_data = convert_to_counts(df=session_actions, id_col='user_id', column_to_convert=column)\n",
    "\n",
    "    # If first loop, current data becomes existing data, otherwise merge existing and current\n",
    "    if first:\n",
    "        first = False\n",
    "        actions_data = current_data\n",
    "    else:\n",
    "        actions_data = pd.concat([actions_data, current_data], axis=1, join='inner')\n",
    "        \n",
    "actions_data.sample(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### 6.2.3. Combine Data Sets\n",
    "\n",
    "The final steps are to combine the various datasets we have created into one large dataset. \n",
    "\n",
    "1. we combine the two device dataframes (df_primary and df_secondary) to create a device dataframe\n",
    "2. we combine the device dataframe with the actions dataframe to create a sessions dataframe with all the features we extracted from sessions.csv\n",
    "3. Finally, we combine the sessions dataframe with the training and testing data dataframe"
   ]
  },
  {
   "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>age</th>\n",
       "      <th>gende_other</th>\n",
       "      <th>gende_female</th>\n",
       "      <th>gende_male</th>\n",
       "      <th>gende_unknown</th>\n",
       "      <th>signu_basic</th>\n",
       "      <th>signu_facebook</th>\n",
       "      <th>signu_google</th>\n",
       "      <th>signu_weibo</th>\n",
       "      <th>signu_24</th>\n",
       "      <th>...</th>\n",
       "      <th>action_detail_view_resolutions</th>\n",
       "      <th>action_detail_view_search_results</th>\n",
       "      <th>action_detail_view_security_checks</th>\n",
       "      <th>action_detail_view_user_real_names</th>\n",
       "      <th>action_detail_wishlist</th>\n",
       "      <th>action_detail_wishlist_content_update</th>\n",
       "      <th>action_detail_wishlist_note</th>\n",
       "      <th>action_detail_your_listings</th>\n",
       "      <th>action_detail_your_reservations</th>\n",
       "      <th>action_detail_your_trips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>l4r31vw1qi</th>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>957x12vgva</th>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f1gn7yj9am</th>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90g6d74oid</th>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>o7x0v0sysa</th>\n",
       "      <td>37</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 703 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            age  gende_other  gende_female  gende_male  gende_unknown  \\\n",
       "l4r31vw1qi   -1            0             0           0              1   \n",
       "957x12vgva   39            0             1           0              0   \n",
       "f1gn7yj9am   27            0             1           0              0   \n",
       "90g6d74oid   31            0             1           0              0   \n",
       "o7x0v0sysa   37            0             0           1              0   \n",
       "\n",
       "            signu_basic  signu_facebook  signu_google  signu_weibo  signu_24  \\\n",
       "l4r31vw1qi            1               0             0            0         0   \n",
       "957x12vgva            0               1             0            0         0   \n",
       "f1gn7yj9am            0               1             0            0         0   \n",
       "90g6d74oid            1               0             0            0         0   \n",
       "o7x0v0sysa            1               0             0            0         0   \n",
       "\n",
       "                      ...             action_detail_view_resolutions  \\\n",
       "l4r31vw1qi            ...                                        0.0   \n",
       "957x12vgva            ...                                        0.0   \n",
       "f1gn7yj9am            ...                                        0.0   \n",
       "90g6d74oid            ...                                        0.0   \n",
       "o7x0v0sysa            ...                                        0.0   \n",
       "\n",
       "            action_detail_view_search_results  \\\n",
       "l4r31vw1qi                               65.0   \n",
       "957x12vgva                               13.0   \n",
       "f1gn7yj9am                               16.0   \n",
       "90g6d74oid                                5.0   \n",
       "o7x0v0sysa                               12.0   \n",
       "\n",
       "            action_detail_view_security_checks  \\\n",
       "l4r31vw1qi                                 0.0   \n",
       "957x12vgva                                 0.0   \n",
       "f1gn7yj9am                                 0.0   \n",
       "90g6d74oid                                 0.0   \n",
       "o7x0v0sysa                                 0.0   \n",
       "\n",
       "            action_detail_view_user_real_names  action_detail_wishlist  \\\n",
       "l4r31vw1qi                                 0.0                     0.0   \n",
       "957x12vgva                                 0.0                     0.0   \n",
       "f1gn7yj9am                                 0.0                     0.0   \n",
       "90g6d74oid                                 0.0                     0.0   \n",
       "o7x0v0sysa                                 0.0                     0.0   \n",
       "\n",
       "            action_detail_wishlist_content_update  \\\n",
       "l4r31vw1qi                                    0.0   \n",
       "957x12vgva                                    0.0   \n",
       "f1gn7yj9am                                    4.0   \n",
       "90g6d74oid                                    6.0   \n",
       "o7x0v0sysa                                    8.0   \n",
       "\n",
       "            action_detail_wishlist_note  action_detail_your_listings  \\\n",
       "l4r31vw1qi                          0.0                          0.0   \n",
       "957x12vgva                          0.0                          0.0   \n",
       "f1gn7yj9am                          0.0                          0.0   \n",
       "90g6d74oid                          0.0                          0.0   \n",
       "o7x0v0sysa                          0.0                          0.0   \n",
       "\n",
       "            action_detail_your_reservations  action_detail_your_trips  \n",
       "l4r31vw1qi                              0.0                       0.0  \n",
       "957x12vgva                              0.0                       0.0  \n",
       "f1gn7yj9am                              0.0                       0.0  \n",
       "90g6d74oid                              0.0                       0.0  \n",
       "o7x0v0sysa                              0.0                       0.0  \n",
       "\n",
       "[5 rows x 703 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge device datasets\n",
    "df_primary.set_index('user_id', inplace=True)\n",
    "df_secondary.set_index('user_id', inplace=True)\n",
    "device_data = pd.concat([df_primary, df_secondary], axis=1, join=\"outer\")\n",
    "\n",
    "# Merge device and actions datasets\n",
    "combined_results = pd.concat([device_data, actions_data], axis=1, join='outer')\n",
    "df_sessions = combined_results.fillna(0)\n",
    "\n",
    "# Merge user and session datasets\n",
    "df_all.set_index('id', inplace=True)\n",
    "df_all = pd.concat([df_all, df_sessions], axis=1, join='inner')\n",
    "\n",
    "df_all.sample(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>primary_secs</th>\n",
       "      <th>prima_windows_de</th>\n",
       "      <th>prima_mac_deskto</th>\n",
       "      <th>prima_iphone</th>\n",
       "      <th>prima_ipad_table</th>\n",
       "      <th>prima_unknown</th>\n",
       "      <th>prima_android_ap</th>\n",
       "      <th>prima_linux_desk</th>\n",
       "      <th>prima_tablet</th>\n",
       "      <th>prima_chromebook</th>\n",
       "      <th>...</th>\n",
       "      <th>action_detail_view_resolutions</th>\n",
       "      <th>action_detail_view_search_results</th>\n",
       "      <th>action_detail_view_security_checks</th>\n",
       "      <th>action_detail_view_user_real_names</th>\n",
       "      <th>action_detail_wishlist</th>\n",
       "      <th>action_detail_wishlist_content_update</th>\n",
       "      <th>action_detail_wishlist_note</th>\n",
       "      <th>action_detail_your_listings</th>\n",
       "      <th>action_detail_your_reservations</th>\n",
       "      <th>action_detail_your_trips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>fb002qrrlh</th>\n",
       "      <td>158970</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>07mba72zco</th>\n",
       "      <td>411779</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>z219kr5vlo</th>\n",
       "      <td>363604</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9sdva32ng8</th>\n",
       "      <td>534654</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4cqalco6ed</th>\n",
       "      <td>988704</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 557 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            primary_secs  prima_windows_de  prima_mac_deskto  prima_iphone  \\\n",
       "fb002qrrlh        158970                 0                 1             0   \n",
       "07mba72zco        411779                 0                 0             0   \n",
       "z219kr5vlo        363604                 1                 0             0   \n",
       "9sdva32ng8        534654                 1                 0             0   \n",
       "4cqalco6ed        988704                 0                 0             0   \n",
       "\n",
       "            prima_ipad_table  prima_unknown  prima_android_ap  \\\n",
       "fb002qrrlh                 0              0                 0   \n",
       "07mba72zco                 0              1                 0   \n",
       "z219kr5vlo                 0              0                 0   \n",
       "9sdva32ng8                 0              0                 0   \n",
       "4cqalco6ed                 1              0                 0   \n",
       "\n",
       "            prima_linux_desk  prima_tablet  prima_chromebook  \\\n",
       "fb002qrrlh                 0             0                 0   \n",
       "07mba72zco                 0             0                 0   \n",
       "z219kr5vlo                 0             0                 0   \n",
       "9sdva32ng8                 0             0                 0   \n",
       "4cqalco6ed                 0             0                 0   \n",
       "\n",
       "                      ...             action_detail_view_resolutions  \\\n",
       "fb002qrrlh            ...                                          0   \n",
       "07mba72zco            ...                                          0   \n",
       "z219kr5vlo            ...                                          0   \n",
       "9sdva32ng8            ...                                          0   \n",
       "4cqalco6ed            ...                                          0   \n",
       "\n",
       "            action_detail_view_search_results  \\\n",
       "fb002qrrlh                                  0   \n",
       "07mba72zco                                  3   \n",
       "z219kr5vlo                                  6   \n",
       "9sdva32ng8                                  4   \n",
       "4cqalco6ed                                 41   \n",
       "\n",
       "            action_detail_view_security_checks  \\\n",
       "fb002qrrlh                                   0   \n",
       "07mba72zco                                   0   \n",
       "z219kr5vlo                                   0   \n",
       "9sdva32ng8                                   0   \n",
       "4cqalco6ed                                   0   \n",
       "\n",
       "            action_detail_view_user_real_names  action_detail_wishlist  \\\n",
       "fb002qrrlh                                   0                       0   \n",
       "07mba72zco                                   0                       0   \n",
       "z219kr5vlo                                   0                       0   \n",
       "9sdva32ng8                                   0                       0   \n",
       "4cqalco6ed                                   0                       0   \n",
       "\n",
       "            action_detail_wishlist_content_update  \\\n",
       "fb002qrrlh                                      0   \n",
       "07mba72zco                                      0   \n",
       "z219kr5vlo                                      5   \n",
       "9sdva32ng8                                      5   \n",
       "4cqalco6ed                                      0   \n",
       "\n",
       "            action_detail_wishlist_note  action_detail_your_listings  \\\n",
       "fb002qrrlh                            0                            0   \n",
       "07mba72zco                            0                            0   \n",
       "z219kr5vlo                            0                            0   \n",
       "9sdva32ng8                            0                            0   \n",
       "4cqalco6ed                            0                            0   \n",
       "\n",
       "            action_detail_your_reservations  action_detail_your_trips  \n",
       "fb002qrrlh                                0                         0  \n",
       "07mba72zco                                0                         0  \n",
       "z219kr5vlo                                0                         0  \n",
       "9sdva32ng8                                0                         0  \n",
       "4cqalco6ed                                0                         0  \n",
       "\n",
       "[5 rows x 557 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sessions = df_sessions.astype(int)\n",
    "df_sessions.sample(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "#Just recheck we don't have new NaN Values before saving our data\n",
    "# We must find no NaN, the column \"country_destination\" has been deleted\n",
    "\n",
    "check_NaN_Values_in_df(df_all) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## 7. Saving the DataFrame to csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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/enriched.csv\", sep=',')"
   ]
  }
 ],
 "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
}