cleaning-data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "df = pd.read_csv(\"data/hits_data.csv\",delimiter=\";\", \n",
    "                 #index_col=\"row_num\"\n",
    "                )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(988681, 10)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_num</th>\n",
       "      <th>locale</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour_of_day</th>\n",
       "      <th>agent_id</th>\n",
       "      <th>entry_page</th>\n",
       "      <th>path_id_set</th>\n",
       "      <th>traffic_type</th>\n",
       "      <th>session_duration</th>\n",
       "      <th>hits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>988681</td>\n",
       "      <td>L6</td>\n",
       "      <td>Monday</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>2111</td>\n",
       "      <td>31672;0</td>\n",
       "      <td>6</td>\n",
       "      <td>7037</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>988680</td>\n",
       "      <td>L2</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>2113</td>\n",
       "      <td>31965;0</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>988679</td>\n",
       "      <td>L4</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>2100</td>\n",
       "      <td>0;78464</td>\n",
       "      <td>1</td>\n",
       "      <td>1892</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>988678</td>\n",
       "      <td>L3</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>19</td>\n",
       "      <td>8</td>\n",
       "      <td>2113</td>\n",
       "      <td>51462</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>988677</td>\n",
       "      <td>L2</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>2116</td>\n",
       "      <td>31931;0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_num locale day_of_week  hour_of_day  agent_id  entry_page path_id_set  \\\n",
       "0   988681     L6      Monday           17         1        2111     31672;0   \n",
       "1   988680     L2    Thursday           22        10        2113     31965;0   \n",
       "2   988679     L4    Saturday           21         2        2100     0;78464   \n",
       "3   988678     L3    Saturday           19         8        2113       51462   \n",
       "4   988677     L2     Tuesday            6        10        2116     31931;0   \n",
       "\n",
       "   traffic_type session_duration hits  \n",
       "0             6             7037   \\N  \n",
       "1             2               49   14  \n",
       "2             1             1892   14  \n",
       "3             6                0    1  \n",
       "4             1                2    3  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename({'session_durantion':'session_duration'}, axis=1, inplace=True)\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Spliting the dataset into test and train, based on inavailablity of hits data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = df[df['hits']!=\"\\\\N\"]\n",
    "test = df[df['hits']==\"\\\\N\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(619235, 10)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Keshav\\Anaconda3\\envs\\tf_py36\\lib\\site-packages\\pandas\\core\\frame.py:3140: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self[k1] = value[k2]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_num</th>\n",
       "      <th>locale</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour_of_day</th>\n",
       "      <th>agent_id</th>\n",
       "      <th>entry_page</th>\n",
       "      <th>path_id_set</th>\n",
       "      <th>traffic_type</th>\n",
       "      <th>session_duration</th>\n",
       "      <th>hits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>988680</td>\n",
       "      <td>L2</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>2113</td>\n",
       "      <td>31965;0</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>988679</td>\n",
       "      <td>L4</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>2100</td>\n",
       "      <td>0;78464</td>\n",
       "      <td>1</td>\n",
       "      <td>1892</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>988678</td>\n",
       "      <td>L3</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>19</td>\n",
       "      <td>8</td>\n",
       "      <td>2113</td>\n",
       "      <td>51462</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>988677</td>\n",
       "      <td>L2</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>2116</td>\n",
       "      <td>31931;0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>988676</td>\n",
       "      <td>L3</td>\n",
       "      <td>Monday</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>2100</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_num locale day_of_week  hour_of_day  agent_id  entry_page path_id_set  \\\n",
       "1   988680     L2    Thursday           22        10        2113     31965;0   \n",
       "2   988679     L4    Saturday           21         2        2100     0;78464   \n",
       "3   988678     L3    Saturday           19         8        2113       51462   \n",
       "4   988677     L2     Tuesday            6        10        2116     31931;0   \n",
       "5   988676     L3      Monday            1         8        2100           0   \n",
       "\n",
       "   traffic_type session_duration  hits  \n",
       "1             2               49    14  \n",
       "2             1             1892    14  \n",
       "3             6                0     1  \n",
       "4             1                2     3  \n",
       "5             1                0     2  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train[['hits']]  = train[['hits']].astype(int)\n",
    "print(train.shape)\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(369446, 10)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_num</th>\n",
       "      <th>locale</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour_of_day</th>\n",
       "      <th>agent_id</th>\n",
       "      <th>entry_page</th>\n",
       "      <th>path_id_set</th>\n",
       "      <th>traffic_type</th>\n",
       "      <th>session_duration</th>\n",
       "      <th>hits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>988681</td>\n",
       "      <td>L6</td>\n",
       "      <td>Monday</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>2111</td>\n",
       "      <td>31672;0</td>\n",
       "      <td>6</td>\n",
       "      <td>7037</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>988666</td>\n",
       "      <td>L3</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>2700</td>\n",
       "      <td>0;34387;84765</td>\n",
       "      <td>1</td>\n",
       "      <td>5189</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>988665</td>\n",
       "      <td>L4</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>2113</td>\n",
       "      <td>79148;0</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>988664</td>\n",
       "      <td>L3</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>19</td>\n",
       "      <td>6</td>\n",
       "      <td>2100</td>\n",
       "      <td>0;34602;34604</td>\n",
       "      <td>1</td>\n",
       "      <td>8041</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>988663</td>\n",
       "      <td>L3</td>\n",
       "      <td>Friday</td>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "      <td>2111</td>\n",
       "      <td>34287;0;60579</td>\n",
       "      <td>2</td>\n",
       "      <td>117</td>\n",
       "      <td>\\N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    row_num locale day_of_week  hour_of_day  agent_id  entry_page  \\\n",
       "0    988681     L6      Monday           17         1        2111   \n",
       "15   988666     L3    Thursday           16        10        2700   \n",
       "16   988665     L4      Sunday           16        10        2113   \n",
       "17   988664     L3     Tuesday           19         6        2100   \n",
       "18   988663     L3      Friday           20        10        2111   \n",
       "\n",
       "      path_id_set  traffic_type session_duration hits  \n",
       "0         31672;0             6             7037   \\N  \n",
       "15  0;34387;84765             1             5189   \\N  \n",
       "16        79148;0             4                5   \\N  \n",
       "17  0;34602;34604             1             8041   \\N  \n",
       "18  34287;0;60579             2              117   \\N  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(test.shape)\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "row_num                0\n",
       "locale                 0\n",
       "day_of_week            0\n",
       "hour_of_day            0\n",
       "agent_id               0\n",
       "entry_page             0\n",
       "path_id_set         3153\n",
       "traffic_type           0\n",
       "session_duration       0\n",
       "hits                   0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is seen that the path_id_set columns have missing data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the Unique Ids and check if they repeat in train and test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique Ids with train data: 28216\n"
     ]
    }
   ],
   "source": [
    "path_id_train= train.loc[train[\"path_id_set\"].notnull(),\"path_id_set\"].str.split(';').tolist()\n",
    "ids = {}\n",
    "ids_dummy = {}\n",
    "i=10000\n",
    "for path_split in path_id_train:\n",
    "    for split in path_split:\n",
    "        if split in ids:\n",
    "            ids[split]+=1\n",
    "        else:\n",
    "            ids[split]=1\n",
    "            ids_dummy[split] =i\n",
    "            i+=1\n",
    "print(\"Unique Ids with train data:\",len(ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique Ids with train and test data: 33215\n"
     ]
    }
   ],
   "source": [
    "path_id_test= test.loc[test[\"path_id_set\"].notnull(),\"path_id_set\"].str.split(';').tolist()\n",
    "for path_split in path_id_test:\n",
    "    for split in path_split:\n",
    "        if split in ids:\n",
    "            ids[split]+=1\n",
    "        else:\n",
    "            ids[split]=1\n",
    "            ids_dummy[split] =i\n",
    "            i+=1\n",
    "print(\"Unique Ids with train and test data:\",len(ids))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This shows that the path_id_set values might keep increasing and is not a constant set of categories. I had further analysed and found that only less than 1% of the dataset had more than 5 path_id_sets(and so be considered as outliers). So below I am converting the path_id_set into 6 different columns, with first 5 of oringinal values splitted. The 6th column will have the remaining number of path_ids as I did not want to waste the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>path_1</th>\n",
       "      <th>path_2</th>\n",
       "      <th>path_3</th>\n",
       "      <th>path_4</th>\n",
       "      <th>path_5</th>\n",
       "      <th>no_of_ids</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31965</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>78464</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51462</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31931</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  path_1 path_2 path_3 path_4 path_5  no_of_ids\n",
       "1  31965      0   None   None   None        2.0\n",
       "2      0  78464   None   None   None        2.0\n",
       "3  51462   None   None   None   None        1.0\n",
       "4  31931      0   None   None   None        2.0\n",
       "5      0   None   None   None   None        1.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = train[\"path_id_set\"].str.split(';', n=5,expand=True)\n",
    "path['no_of_ids']=train['path_id_set'].str.split(';')\n",
    "path['no_of_ids']=path['no_of_ids'].str.len()\n",
    "path.drop(axis=1, columns=5,inplace=True)\n",
    "path.columns = ['path_1','path_2','path_3','path_4','path_5','no_of_ids'] \n",
    "path.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The path values are mapped with values ranging from 10000-35000 so that the values will scale better. The scaling is done in the 'Trivago_ML_feature_engg.ipynb' file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>path_1</th>\n",
       "      <th>path_2</th>\n",
       "      <th>path_3</th>\n",
       "      <th>path_4</th>\n",
       "      <th>path_5</th>\n",
       "      <th>no_of_ids</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001.0</td>\n",
       "      <td>10002.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10003.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10004.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10001.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    path_1   path_2  path_3  path_4  path_5  no_of_ids\n",
       "1  10000.0  10001.0     NaN     NaN     NaN        2.0\n",
       "2  10001.0  10002.0     NaN     NaN     NaN        2.0\n",
       "3  10003.0      NaN     NaN     NaN     NaN        1.0\n",
       "4  10004.0  10001.0     NaN     NaN     NaN        2.0\n",
       "5  10001.0      NaN     NaN     NaN     NaN        1.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path['path_1'] = path['path_1'].map(ids_dummy)\n",
    "path['path_2'] = path['path_2'].map(ids_dummy)\n",
    "path['path_3'] = path['path_3'].map(ids_dummy)\n",
    "path['path_4'] = path['path_4'].map(ids_dummy)\n",
    "path['path_5'] = path['path_5'].map(ids_dummy)\n",
    "path.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Keshav\\Anaconda3\\envs\\tf_py36\\lib\\site-packages\\pandas\\core\\frame.py:3697: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  errors=errors)\n"
     ]
    }
   ],
   "source": [
    "train[['path_1','path_2','path_3','path_4','path_5','no_of_ids']] = path[['path_1','path_2','path_3','path_4','path_5','no_of_ids']]\n",
    "train.drop(axis=1, columns='path_id_set',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_num</th>\n",
       "      <th>locale</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour_of_day</th>\n",
       "      <th>agent_id</th>\n",
       "      <th>entry_page</th>\n",
       "      <th>traffic_type</th>\n",
       "      <th>session_duration</th>\n",
       "      <th>path_1</th>\n",
       "      <th>path_2</th>\n",
       "      <th>path_3</th>\n",
       "      <th>path_4</th>\n",
       "      <th>path_5</th>\n",
       "      <th>no_of_ids</th>\n",
       "      <th>hits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>988680</td>\n",
       "      <td>L2</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>2113</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>988679</td>\n",
       "      <td>L4</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>2100</td>\n",
       "      <td>1</td>\n",
       "      <td>1892</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10002.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>988678</td>\n",
       "      <td>L3</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>19</td>\n",
       "      <td>8</td>\n",
       "      <td>2113</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>10003.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>988677</td>\n",
       "      <td>L2</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>2116</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10004.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>988676</td>\n",
       "      <td>L3</td>\n",
       "      <td>Monday</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>2100</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_num locale day_of_week  hour_of_day  agent_id  entry_page  \\\n",
       "1   988680     L2    Thursday           22        10        2113   \n",
       "2   988679     L4    Saturday           21         2        2100   \n",
       "3   988678     L3    Saturday           19         8        2113   \n",
       "4   988677     L2     Tuesday            6        10        2116   \n",
       "5   988676     L3      Monday            1         8        2100   \n",
       "\n",
       "   traffic_type session_duration   path_1   path_2  path_3  path_4  path_5  \\\n",
       "1             2               49  10000.0  10001.0     NaN     NaN     NaN   \n",
       "2             1             1892  10001.0  10002.0     NaN     NaN     NaN   \n",
       "3             6                0  10003.0      NaN     NaN     NaN     NaN   \n",
       "4             1                2  10004.0  10001.0     NaN     NaN     NaN   \n",
       "5             1                0  10001.0      NaN     NaN     NaN     NaN   \n",
       "\n",
       "   no_of_ids  hits  \n",
       "1        2.0    14  \n",
       "2        2.0    14  \n",
       "3        1.0     1  \n",
       "4        2.0     3  \n",
       "5        1.0     2  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols=['row_num', 'locale', 'day_of_week', 'hour_of_day', 'agent_id',\n",
    "       'entry_page', 'traffic_type', 'session_duration',\n",
    "        'path_1', 'path_2', 'path_3', 'path_4', 'path_5', 'no_of_ids','hits']\n",
    "train = train[cols]\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_num</th>\n",
       "      <th>locale</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour_of_day</th>\n",
       "      <th>agent_id</th>\n",
       "      <th>entry_page</th>\n",
       "      <th>traffic_type</th>\n",
       "      <th>session_duration</th>\n",
       "      <th>path_1</th>\n",
       "      <th>path_2</th>\n",
       "      <th>path_3</th>\n",
       "      <th>path_4</th>\n",
       "      <th>path_5</th>\n",
       "      <th>no_of_ids</th>\n",
       "      <th>hits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>988680</td>\n",
       "      <td>L2</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>2113</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>988679</td>\n",
       "      <td>L4</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>2100</td>\n",
       "      <td>1</td>\n",
       "      <td>1892</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10002.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>988678</td>\n",
       "      <td>L3</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>19</td>\n",
       "      <td>8</td>\n",
       "      <td>2113</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>10003.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>988677</td>\n",
       "      <td>L2</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>2116</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10004.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>988676</td>\n",
       "      <td>L3</td>\n",
       "      <td>Monday</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>2100</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_num locale day_of_week  hour_of_day  agent_id  entry_page  \\\n",
       "1   988680     L2    Thursday           22        10        2113   \n",
       "2   988679     L4    Saturday           21         2        2100   \n",
       "3   988678     L3    Saturday           19         8        2113   \n",
       "4   988677     L2     Tuesday            6        10        2116   \n",
       "5   988676     L3      Monday            1         8        2100   \n",
       "\n",
       "   traffic_type session_duration   path_1   path_2  path_3  path_4  path_5  \\\n",
       "1             2               49  10000.0  10001.0     0.0     0.0     0.0   \n",
       "2             1             1892  10001.0  10002.0     0.0     0.0     0.0   \n",
       "3             6                0  10003.0      0.0     0.0     0.0     0.0   \n",
       "4             1                2  10004.0  10001.0     0.0     0.0     0.0   \n",
       "5             1                0  10001.0      0.0     0.0     0.0     0.0   \n",
       "\n",
       "   no_of_ids  hits  \n",
       "1        2.0    14  \n",
       "2        2.0    14  \n",
       "3        1.0     1  \n",
       "4        2.0     3  \n",
       "5        1.0     2  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#train.no_of_ids.fillna(0,inplace=True)\n",
    "train.fillna(0,inplace=True)\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "train[['path_1','path_2','path_3','path_4','path_5']]=train[['path_1','path_2','path_3','path_4','path_5']].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "row_num               int64\n",
       "locale               object\n",
       "day_of_week          object\n",
       "hour_of_day           int64\n",
       "agent_id              int64\n",
       "entry_page            int64\n",
       "traffic_type          int64\n",
       "session_duration     object\n",
       "path_1                int32\n",
       "path_2                int32\n",
       "path_3                int32\n",
       "path_4                int32\n",
       "path_5                int32\n",
       "no_of_ids           float64\n",
       "hits                  int32\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From above its seen that session duration is saved as an object inspite of being numbers. After analysing it I found there was some missing data as '\\N'. \n",
    "This was seen due to the crossing over of \"hour_of_day\" to the next day(the deduction was made by analysing the max \"session_duration\"<(24-\"hour_of_day\")x60x60). Thus whenever a persons usage started in one day and crossed over to next day in same session it became NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# hour_0_sess_N = (train[\"hour_of_day\"]==0) & (train[\"session_duration\"]=='\\\\N')\n",
    "# train.loc[hour_0_sess_N,\"session_duration\"] = train.loc[train['session_duration']!='\\\\N','session_duration'].astype('int').mean()\n",
    "hour_n0_sess_N = (train[\"session_duration\"]=='\\\\N')\n",
    "train.loc[hour_n0_sess_N,\"session_duration\"] = (24-train.loc[hour_n0_sess_N, \"hour_of_day\"])*3600\n",
    "train[['session_duration']]  = train[['session_duration']].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, what I have done is converted the remaining hours of the day to seconds and saved as the unknown 'session_duration' value. It can be even more, I cannot be sure. But this should work fine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "row_num               int64\n",
       "locale               object\n",
       "day_of_week          object\n",
       "hour_of_day           int64\n",
       "agent_id              int64\n",
       "entry_page            int64\n",
       "traffic_type          int64\n",
       "session_duration      int32\n",
       "path_1                int32\n",
       "path_2                int32\n",
       "path_3                int32\n",
       "path_4                int32\n",
       "path_5                int32\n",
       "no_of_ids           float64\n",
       "hits                  int32\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_num</th>\n",
       "      <th>locale</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour_of_day</th>\n",
       "      <th>agent_id</th>\n",
       "      <th>entry_page</th>\n",
       "      <th>traffic_type</th>\n",
       "      <th>session_duration</th>\n",
       "      <th>path_1</th>\n",
       "      <th>path_2</th>\n",
       "      <th>path_3</th>\n",
       "      <th>path_4</th>\n",
       "      <th>path_5</th>\n",
       "      <th>no_of_ids</th>\n",
       "      <th>hits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3562</th>\n",
       "      <td>985119</td>\n",
       "      <td>L3</td>\n",
       "      <td>Monday</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>8101</td>\n",
       "      <td>1</td>\n",
       "      <td>86400</td>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      row_num locale day_of_week  hour_of_day  agent_id  entry_page  \\\n",
       "3562   985119     L3      Monday            0         8        8101   \n",
       "\n",
       "      traffic_type  session_duration  path_1  path_2  path_3  path_4  path_5  \\\n",
       "3562             1             86400   10001       0       0       0       0   \n",
       "\n",
       "      no_of_ids  hits  \n",
       "3562        1.0     1  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train[(train[\"row_num\"]==985119)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "days_of_week = ['Monday', 'Tuesday','Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']\n",
    "locales = ['L1','L2', 'L3','L4', 'L5', 'L6']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Monday': 1, 'Tuesday': 2, 'Wednesday': 3, 'Thursday': 4, 'Friday': 5, 'Saturday': 6, 'Sunday': 7}\n",
      "{'L1': 1, 'L2': 2, 'L3': 3, 'L4': 4, 'L5': 5, 'L6': 6}\n"
     ]
    }
   ],
   "source": [
    "day_of_week_dict = {}\n",
    "locale_dict = {}\n",
    "for i,a in enumerate(locales):\n",
    "    locale_dict[a] = i+1\n",
    "for i,a in enumerate(days_of_week):\n",
    "    day_of_week_dict[a] =i+1\n",
    "print(day_of_week_dict)\n",
    "print(locale_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_num</th>\n",
       "      <th>locale</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour_of_day</th>\n",
       "      <th>agent_id</th>\n",
       "      <th>entry_page</th>\n",
       "      <th>traffic_type</th>\n",
       "      <th>session_duration</th>\n",
       "      <th>path_1</th>\n",
       "      <th>path_2</th>\n",
       "      <th>path_3</th>\n",
       "      <th>path_4</th>\n",
       "      <th>path_5</th>\n",
       "      <th>no_of_ids</th>\n",
       "      <th>hits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>988680</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>2113</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>10000</td>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>988679</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>2100</td>\n",
       "      <td>1</td>\n",
       "      <td>1892</td>\n",
       "      <td>10001</td>\n",
       "      <td>10002</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>988678</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "      <td>8</td>\n",
       "      <td>2113</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>10003</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>988677</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>2116</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10004</td>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>988676</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>2100</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_num  locale  day_of_week  hour_of_day  agent_id  entry_page  \\\n",
       "1   988680       2            4           22        10        2113   \n",
       "2   988679       4            6           21         2        2100   \n",
       "3   988678       3            6           19         8        2113   \n",
       "4   988677       2            2            6        10        2116   \n",
       "5   988676       3            1            1         8        2100   \n",
       "\n",
       "   traffic_type  session_duration  path_1  path_2  path_3  path_4  path_5  \\\n",
       "1             2                49   10000   10001       0       0       0   \n",
       "2             1              1892   10001   10002       0       0       0   \n",
       "3             6                 0   10003       0       0       0       0   \n",
       "4             1                 2   10004   10001       0       0       0   \n",
       "5             1                 0   10001       0       0       0       0   \n",
       "\n",
       "   no_of_ids  hits  \n",
       "1        2.0    14  \n",
       "2        2.0    14  \n",
       "3        1.0     1  \n",
       "4        2.0     3  \n",
       "5        1.0     2  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['day_of_week'] = train['day_of_week'].map(day_of_week_dict)\n",
    "train['locale'] = train['locale'].map(locale_dict)\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "agent = {10:1, 9:2, 1:3, 8:4, 2:5, 13:6, 7:7, 14:8}\n",
    "train['agent_id'] = train['agent_id'].map(agent)\n",
    "train.fillna(-1,inplace=True)\n",
    "train[['agent_id']]=train[['agent_id']].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "entry={2113: 1, 2116:2, 2100:3, 2111:4, 2114:5, 2700:6, 8001:7, 2115:8, 2226:9, 2316:10}\n",
    "train['entry_page'] = train['entry_page'].map(entry)\n",
    "train.fillna(-1,inplace=True)\n",
    "train[['entry_page']]=train[['entry_page']].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The day of week, locale, entry and agent values have been mapped with dummy values for now just for my convenience. It will be properly handled in the 'Trivago_ML_feature_engg.ipynb' file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_num</th>\n",
       "      <th>locale</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>hour_of_day</th>\n",
       "      <th>agent_id</th>\n",
       "      <th>entry_page</th>\n",
       "      <th>traffic_type</th>\n",
       "      <th>session_duration</th>\n",
       "      <th>path_1</th>\n",
       "      <th>path_2</th>\n",
       "      <th>path_3</th>\n",
       "      <th>path_4</th>\n",
       "      <th>path_5</th>\n",
       "      <th>no_of_ids</th>\n",
       "      <th>hits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>988680</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>49</td>\n",
       "      <td>10000</td>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>988679</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1892</td>\n",
       "      <td>10001</td>\n",
       "      <td>10002</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>988678</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>10003</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>988677</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10004</td>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>988676</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_num  locale  day_of_week  hour_of_day  agent_id  entry_page  \\\n",
       "1   988680       2            4           22         1           1   \n",
       "2   988679       4            6           21         5           3   \n",
       "3   988678       3            6           19         4           1   \n",
       "4   988677       2            2            6         1           2   \n",
       "5   988676       3            1            1         4           3   \n",
       "\n",
       "   traffic_type  session_duration  path_1  path_2  path_3  path_4  path_5  \\\n",
       "1             2                49   10000   10001       0       0       0   \n",
       "2             1              1892   10001   10002       0       0       0   \n",
       "3             6                 0   10003       0       0       0       0   \n",
       "4             1                 2   10004   10001       0       0       0   \n",
       "5             1                 0   10001       0       0       0       0   \n",
       "\n",
       "   no_of_ids  hits  \n",
       "1        2.0    14  \n",
       "2        2.0    14  \n",
       "3        1.0     1  \n",
       "4        2.0     3  \n",
       "5        1.0     2  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.to_csv(\"data/trivago_cleaned.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:tf_py36]",
   "language": "python",
   "name": "conda-env-tf_py36-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}