Trivago.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.max_rows', 1000)\n",
    "pd.set_option('display.max_colwidth', -1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(r\"trivago_data_analysis_case_study_part1.csv\")"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Describe the relationship between the average displayed position and the clicked displayed position\n",
    "\n",
    "clicked displayed position: It denotes at which impression position the Ad was clicked \n",
    "\n",
    "average displayed position or avg_impressed_position : on an average at which position the Ad is impressed or displayed. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>item_id</th>\n",
       "      <th>impressions</th>\n",
       "      <th>clicks</th>\n",
       "      <th>clicked_displayed_positions</th>\n",
       "      <th>avg_impressed_position</th>\n",
       "      <th>num_users</th>\n",
       "      <th>num_sessions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5040</td>\n",
       "      <td>40</td>\n",
       "      <td>5</td>\n",
       "      <td>20;9;12;10;12</td>\n",
       "      <td>11.83</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5053</td>\n",
       "      <td>83</td>\n",
       "      <td>3</td>\n",
       "      <td>2;3;0</td>\n",
       "      <td>7.88</td>\n",
       "      <td>43</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5094</td>\n",
       "      <td>54</td>\n",
       "      <td>6</td>\n",
       "      <td>0;0;0;2;1;2</td>\n",
       "      <td>4.72</td>\n",
       "      <td>30</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5101</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>5;3</td>\n",
       "      <td>4.97</td>\n",
       "      <td>17</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5105</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>9.08</td>\n",
       "      <td>8</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item_id  impressions  clicks clicked_displayed_positions  \\\n",
       "0     5040           40       5               20;9;12;10;12   \n",
       "1     5053           83       3                       2;3;0   \n",
       "2     5094           54       6                 0;0;0;2;1;2   \n",
       "3     5101           30       2                         5;3   \n",
       "4     5105           24       1                          18   \n",
       "\n",
       "   avg_impressed_position  num_users  num_sessions  \n",
       "0                   11.83         22            23  \n",
       "1                    7.88         43            52  \n",
       "2                    4.72         30            36  \n",
       "3                    4.97         17            20  \n",
       "4                    9.08          8            11  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the resources section, you will find trivago_data_analysis_case_study_part_1.csv \n",
    "\n",
    "with information about items (hotels). \n",
    "\n",
    "Each row displays for each item, it's id, \n",
    "\n",
    "number of times it was displayed (impressions), \n",
    "\n",
    "number of click-outs (clicks), \n",
    "\n",
    "set of clicked displayed positions (0=first position), \n",
    "\n",
    "average impressed position, \n",
    "\n",
    "number of users and sessions with at least one impression. \n",
    "\n",
    "Only clicks and impressions from the first page results are considered."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_unique(df):\n",
    "    cols = df.columns\n",
    "    for i in cols:\n",
    "        print(\"{}              {}\".format(i,len(df[i].unique())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "item_id              130056\n",
      "impressions              4254\n",
      "clicks              673\n",
      "clicked_displayed_positions              54937\n",
      "avg_impressed_position              2102\n",
      "num_users              3112\n",
      "num_sessions              3371\n"
     ]
    }
   ],
   "source": [
    "get_unique(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['CTR'] = (data['clicks']/data['impressions'])*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11.45570598949831"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['CTR'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_cdp = dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "for _ in data['clicked_displayed_positions']:\n",
    "    for i in _.split(';'):\n",
    "        try:\n",
    "            dict_cdp[i] = dict_cdp[i]+1\n",
    "        except:\n",
    "            dict_cdp[i] = 1\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series(dict_cdp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "s.index.name = 'Positions'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>Positions</th>\n",
       "      <th>Clicks_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>13241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>22</td>\n",
       "      <td>13453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>21</td>\n",
       "      <td>14597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td>15138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>24</td>\n",
       "      <td>15824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>19</td>\n",
       "      <td>16142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>18</td>\n",
       "      <td>17636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>17</td>\n",
       "      <td>19110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>16</td>\n",
       "      <td>20244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>15</td>\n",
       "      <td>22707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>23678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>13</td>\n",
       "      <td>25783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>29396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>11</td>\n",
       "      <td>31631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "      <td>35135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>39757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8</td>\n",
       "      <td>43564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>7</td>\n",
       "      <td>49122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>6</td>\n",
       "      <td>57308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5</td>\n",
       "      <td>66362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4</td>\n",
       "      <td>78928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>97319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>126804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>-11</td>\n",
       "      <td>134287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>189941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>480310</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Positions  Clicks_Count\n",
       "22        23         13241\n",
       "23        22         13453\n",
       "18        21         14597\n",
       "0         20         15138\n",
       "17        24         15824\n",
       "20        19         16142\n",
       "9         18         17636\n",
       "25        17         19110\n",
       "24        16         20244\n",
       "21        15         22707\n",
       "13        14         23678\n",
       "15        13         25783\n",
       "2         12         29396\n",
       "16        11         31631\n",
       "3         10         35135\n",
       "1          9         39757\n",
       "12         8         43564\n",
       "19         7         49122\n",
       "10         6         57308\n",
       "8          5         66362\n",
       "14         4         78928\n",
       "5          3         97319\n",
       "4          2        126804\n",
       "11       -11        134287\n",
       "7          1        189941\n",
       "6          0        480310"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(s).reset_index()\n",
    "df.columns = ['Positions', 'Clicks_Count']\n",
    "df.sort_values(by='Clicks_Count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "838708.5"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Clicks_Count'].sum()/2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "931342\n",
      "at position 4\n"
     ]
    }
   ],
   "source": [
    "counter = 0\n",
    "cnt = 0\n",
    "for i in df.sort_values(by='Clicks_Count', ascending = False)['Clicks_Count']:\n",
    "    if counter < 838708.5:\n",
    "        counter = counter + i\n",
    "        cnt = cnt+1\n",
    "    else:\n",
    "        break\n",
    "        \n",
    "print(counter)\n",
    "print(\"at position {}\".format(cnt))\n",
    "        \n",
    "    "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Question 4:  Instead of going with average to get average displayed position using mode to get the frequency is the best approach suitable for this kinda of occurrence analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\5029874\\AppData\\Local\\Continuum\\anaconda3\\envs\\trainingenv\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3020: DtypeWarning: Columns (0) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "data2 = pd.read_csv(r\"trivago_data_analysis_case_study_part2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>user_id</th>\n",
       "      <th>session_id</th>\n",
       "      <th>clicked_item_id</th>\n",
       "      <th>displayed_position</th>\n",
       "      <th>page_num</th>\n",
       "      <th>sort_order</th>\n",
       "      <th>search_type</th>\n",
       "      <th>path_id</th>\n",
       "      <th>arrival_days</th>\n",
       "      <th>departure_days</th>\n",
       "      <th>traffic_type</th>\n",
       "      <th>impressed_item_ids</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6216730024127027591</td>\n",
       "      <td>3.755630e+18</td>\n",
       "      <td>3390592.0</td>\n",
       "      <td>-11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2113.0</td>\n",
       "      <td>39693.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>128992;127920;132177;1107164;19389;3217646;123...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3540322805835307689</td>\n",
       "      <td>4.581411e+18</td>\n",
       "      <td>2393900.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2113.0</td>\n",
       "      <td>35009.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5169438;997421;76994;1376364;67654;68977;23939...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5439210126639293380</td>\n",
       "      <td>7.260244e+18</td>\n",
       "      <td>8285.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2116.0</td>\n",
       "      <td>34390.0</td>\n",
       "      <td>301.0</td>\n",
       "      <td>302.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8285;8284;78562;8243;1288434;61564;60293;15334...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3482625725900275142</td>\n",
       "      <td>3.013037e+18</td>\n",
       "      <td>148308.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2113.0</td>\n",
       "      <td>41579.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2196706;46431;1262783;148312;1478421;6171546;1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6185636196804664137</td>\n",
       "      <td>1.463579e+18</td>\n",
       "      <td>4527530.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2116.0</td>\n",
       "      <td>85724.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4527530;149339;945921;1236133;1259540;1700445;...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               user_id    session_id  clicked_item_id  displayed_position  \\\n",
       "0  6216730024127027591  3.755630e+18        3390592.0               -11.0   \n",
       "1  3540322805835307689  4.581411e+18        2393900.0                 6.0   \n",
       "2  5439210126639293380  7.260244e+18           8285.0                 0.0   \n",
       "3  3482625725900275142  3.013037e+18         148308.0                 6.0   \n",
       "4  6185636196804664137  1.463579e+18        4527530.0                 0.0   \n",
       "\n",
       "   page_num  sort_order  search_type  path_id  arrival_days  departure_days  \\\n",
       "0       0.0        12.0       2113.0  39693.0           3.0             4.0   \n",
       "1       0.0        21.0       2113.0  35009.0          21.0            22.0   \n",
       "2       0.0        12.0       2116.0  34390.0         301.0           302.0   \n",
       "3       0.0        41.0       2113.0  41579.0          39.0            42.0   \n",
       "4       0.0        12.0       2116.0  85724.0          48.0            49.0   \n",
       "\n",
       "   traffic_type                                impressed_item_ids   \n",
       "0           2.0  128992;127920;132177;1107164;19389;3217646;123...  \n",
       "1           2.0  5169438;997421;76994;1376364;67654;68977;23939...  \n",
       "2           2.0  8285;8284;78562;8243;1288434;61564;60293;15334...  \n",
       "3           3.0  2196706;46431;1262783;148312;1478421;6171546;1...  \n",
       "4           2.0  4527530;149339;945921;1236133;1259540;1700445;...  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "user_id              763390\n",
      "session_id              1032518\n",
      "clicked_item_id              145284\n",
      "displayed_position              27\n",
      "page_num              22\n",
      "sort_order              12\n",
      "search_type              7\n",
      "path_id              15138\n",
      "arrival_days              519\n",
      "departure_days              520\n",
      "traffic_type              4\n",
      "impressed_item_ids               1396315\n"
     ]
    }
   ],
   "source": [
    "get_unique(data2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user_id                1903796\n",
       "session_id             1903795\n",
       "clicked_item_id        1903795\n",
       "displayed_position     1903795\n",
       "page_num               1903795\n",
       "sort_order             1903795\n",
       "search_type            1903795\n",
       "path_id                1903795\n",
       "arrival_days           1903795\n",
       "departure_days         1903795\n",
       "traffic_type           1903795\n",
       "impressed_item_ids     1903795\n",
       "dtype: int64"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "data2['diff'] = data2['departure_days'] - data2['arrival_days']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                    user_id  counts\n",
      "371803  4509866267912745159  549   \n",
      "264909  3212529366726084995  373   \n",
      "746017  9051529085815567256  332   \n",
      "728846  8844433817516149751  272   \n",
      "519250  6297403635493453063  219   \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "          session_id  counts\n",
      "561939  5.009065e+18  337   \n",
      "958275  8.560570e+18  148   \n",
      "319119  2.839951e+18  109   \n",
      "801311  7.154740e+18  92    \n",
      "358443  3.191225e+18  88    \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "        clicked_item_id  counts\n",
      "101242  2834772.0        2869  \n",
      "64896   1321090.0        2828  \n",
      "3297    12624.0          2575  \n",
      "8079    32940.0          2324  \n",
      "69630   1455251.0        2297  \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "   displayed_position  counts\n",
      "1  0.0                 495014\n",
      "2  1.0                 203090\n",
      "0 -11.0                152437\n",
      "3  2.0                 138789\n",
      "4  3.0                 108490\n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "   page_num   counts\n",
      "0  0.0       1677391\n",
      "1  1.0       124109 \n",
      "2  2.0       44578  \n",
      "3  3.0       21226  \n",
      "4  4.0       11857  \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "   sort_order   counts\n",
      "1  12.0        1162269\n",
      "8  312.0       283516 \n",
      "7  212.0       229803 \n",
      "9  412.0       94343  \n",
      "4  41.0        54259  \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "   search_type  counts\n",
      "2  2113.0       928598\n",
      "5  2116.0       375583\n",
      "1  2111.0       230316\n",
      "4  2115.0       194310\n",
      "3  2114.0       174986\n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "      path_id  counts\n",
      "3101  38715.0  138706\n",
      "5807  46814.0  41379 \n",
      "4463  41579.0  36498 \n",
      "3275  38961.0  31634 \n",
      "1298  31965.0  30928 \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "   arrival_days  counts\n",
      "1  0.0           65863 \n",
      "4  3.0           63180 \n",
      "2  1.0           60910 \n",
      "5  4.0           54871 \n",
      "6  5.0           50279 \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "   departure_days  counts\n",
      "5  4.0             59506 \n",
      "2  1.0             52780 \n",
      "0 -1000000.0       52530 \n",
      "6  5.0             51982 \n",
      "7  6.0             48879 \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "   traffic_type   counts\n",
      "1  2.0           1496629\n",
      "2  3.0           301763 \n",
      "0  1.0           105403 \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "                                                                                                                                                                              impressed_item_ids   \\\n",
      "304166  1455251;40899;4995818;40887;15854;40928;2282460;40923;2805646;40917;15119;1332489;2292434;40921;40894;1234586;40935;40883;2727758;4775026;40880;15122;1105676;15114;40910                   \n",
      "753699  3439776                                                                                                                                                                                     \n",
      "568114  2092330;3390442;3439776                                                                                                                                                                     \n",
      "303842  1455251;40887;4995818;40899;40928;15854;40923;2282460;2805646;40917;40921;15119;40909;1332489;1234586;4775026;40883;40894;40935;2292434;2065710;914555;150636;1833933;2727758               \n",
      "252529  1330971;149339;624396;693916;927159;945921;1109455;1247036;1259540;2173240;2588352;3056364;4527530;3812986;909353;97917;900401;97940;153032;2396300;1929999;3492384;920327;1363627;449196   \n",
      "\n",
      "        counts  \n",
      "304166  141     \n",
      "753699  80      \n",
      "568114  78      \n",
      "303842  76      \n",
      "252529  56      \n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n",
      "     diff  counts\n",
      "404  1.0   862488\n",
      "405  2.0   331240\n",
      "406  3.0   209445\n",
      "410  7.0   136688\n",
      "407  4.0   121350\n",
      "\n",
      "------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for i in data2.columns:\n",
    "    print(data2[[i]].groupby([i]).size().reset_index(name='counts').sort_values(by='counts',ascending=False).head(5))\n",
    "    print()\n",
    "    print('------------------------------------------------------------------')\n",
    "    print()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 2.,  3.,  1., nan])"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.traffic_type.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "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>displayed_position</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>search_type</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2100.0</th>\n",
       "      <td>2.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2111.0</th>\n",
       "      <td>5.819748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2113.0</th>\n",
       "      <td>4.890490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2114.0</th>\n",
       "      <td>3.805842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2115.0</th>\n",
       "      <td>5.346740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2116.0</th>\n",
       "      <td>1.372424</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             displayed_position\n",
       "search_type                    \n",
       "2100.0       2.500000          \n",
       "2111.0       5.819748          \n",
       "2113.0       4.890490          \n",
       "2114.0       3.805842          \n",
       "2115.0       5.346740          \n",
       "2116.0       1.372424          "
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2[['search_type','displayed_position']].groupby(['search_type']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "mode_df.to_csv(\"mode_elements.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}