2015-12-ip-anonymization-and-geolocation-google-analytics.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IP anonymization and its impact on visitor localization in Google Analytics\n",
    "\n",
    "\n",
    "### Tools used\n",
    "* [pandas](http://pandas.pydata.org/) and its [Google Analytics connector](http://pandas.pydata.org/pandas-docs/version/0.15.2/remote_data.html#remote-data-ga) to fetch and wrangle the data,\n",
    "* [bokeh](http://bokeh.pydata.org) to visualize it.\n",
    "\n",
    "### Data sources\n",
    "* The traffic data comes from Google Analytics and concerns [blog.liip.ch](http://blog.liip.ch). Two properties track pageviews on that 'tech' blog, on with IP anonymizing enabled, since september 2015."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploration 1: What's the impact of IP anonymizing on the user country dimension?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pandas.io.ga as ga\n",
    "import numpy as np\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PYTHON  3.4.3 |Anaconda 2.4.0 (x86_64)| (default, Oct 20 2015, 14:27:51) \n",
      "[GCC 4.2.1 (Apple Inc. build 5577)]\n",
      "PANDAS  0.17.0\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "print (\"PYTHON \", sys.version)\n",
    "print (\"PANDAS \", pd.__version__)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetching data\n",
    "\n",
    "Now that our tools are loaded, let us fetch the data from the two Google Analytics properties.\n",
    "\n",
    "The traffic data without anonymization since September 1st, 2015."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sources = {\n",
    "    'full' : {\n",
    "        'property_id': \"UA-424540-4\",\n",
    "        'profile_id': \"5334921\",\n",
    "    },\n",
    "    'anon' : {\n",
    "        'property_id': \"UA-424540-11\",\n",
    "        'profile_id': \"107030134\",\n",
    "    },\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "49376\n",
      "49161\n"
     ]
    }
   ],
   "source": [
    "source_data = {}\n",
    "\n",
    "for key in sources:    \n",
    "    source_data[key] = ga.read_ga(\n",
    "        property_id = sources[key]['property_id'],\n",
    "        profile_id = sources[key]['profile_id'],\n",
    "        metrics     = \"sessions\",\n",
    "        dimensions  = ['country','city'],\n",
    "        start_date  = \"2015-09-01\",\n",
    "        index_col   = ['country','city'],\n",
    "    )\n",
    "\n",
    "    print(source_data[key]['sessions'].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Less than 1% difference in volumes. Since the tracking is not forcefully simultaneous, that was expected. Let's just have a look at one of them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sessions</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>(not set)</th>\n",
       "      <th>(not set)</th>\n",
       "      <td>126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Afghanistan</th>\n",
       "      <th>(not set)</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Albania</th>\n",
       "      <th>(not set)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tirana</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <th>(not set)</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       sessions\n",
       "country     city               \n",
       "(not set)   (not set)       126\n",
       "Afghanistan (not set)         2\n",
       "Albania     (not set)         1\n",
       "            Tirana           12\n",
       "Algeria     (not set)        15"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "source_data['full'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us now join those two dataframes based on their country/city index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>full_ip_sessions</th>\n",
       "      <th>anon_ip_sessions</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>(not set)</th>\n",
       "      <th>(not set)</th>\n",
       "      <td>130</td>\n",
       "      <td>126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Afghanistan</th>\n",
       "      <th>(not set)</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Albania</th>\n",
       "      <th>(not set)</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tirana</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <th>(not set)</th>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       full_ip_sessions  anon_ip_sessions\n",
       "country     city                                         \n",
       "(not set)   (not set)               130               126\n",
       "Afghanistan (not set)                 2                 2\n",
       "Albania     (not set)                 1                 1\n",
       "            Tirana                   12                12\n",
       "Algeria     (not set)                14                15"
      ]
     },
     "execution_count": 222,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.concat(source_data, axis=1, join='outer')\n",
    "\n",
    "# rename homonymous columns\n",
    "data.columns=['full_ip_sessions', 'anon_ip_sessions']\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's list the countries where the biggest proportional losses & wins happen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# group by level 0 of the index (i.e. countries) and sum columns for groups\n",
    "country_data = data.groupby(level=0).sum()\n",
    "\n",
    "# compute delta and its proportion\n",
    "country_data['delta'] = country_data.anon_ip_sessions - country_data.full_ip_sessions\n",
    "country_data['dprop'] = country_data.delta / country_data.full_ip_sessions\n",
    "\n",
    "# sort by prop. delta, ascending\n",
    "country_data.sort_values(by='dprop', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Countries with proportionally large losses:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_ip_sessions</th>\n",
       "      <th>anon_ip_sessions</th>\n",
       "      <th>delta</th>\n",
       "      <th>dprop</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>387</td>\n",
       "      <td>356</td>\n",
       "      <td>-31</td>\n",
       "      <td>-0.080103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>China</th>\n",
       "      <td>341</td>\n",
       "      <td>326</td>\n",
       "      <td>-15</td>\n",
       "      <td>-0.043988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>443</td>\n",
       "      <td>429</td>\n",
       "      <td>-14</td>\n",
       "      <td>-0.031603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>7705</td>\n",
       "      <td>7465</td>\n",
       "      <td>-240</td>\n",
       "      <td>-0.031149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Denmark</th>\n",
       "      <td>242</td>\n",
       "      <td>238</td>\n",
       "      <td>-4</td>\n",
       "      <td>-0.016529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bulgaria</th>\n",
       "      <td>207</td>\n",
       "      <td>206</td>\n",
       "      <td>-1</td>\n",
       "      <td>-0.004831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finland</th>\n",
       "      <td>275</td>\n",
       "      <td>274</td>\n",
       "      <td>-1</td>\n",
       "      <td>-0.003636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mexico</th>\n",
       "      <td>332</td>\n",
       "      <td>332</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Taiwan</th>\n",
       "      <td>278</td>\n",
       "      <td>278</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Czech Republic</th>\n",
       "      <td>420</td>\n",
       "      <td>420</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                full_ip_sessions  anon_ip_sessions  delta     dprop\n",
       "country                                                            \n",
       "Austria                      387               356    -31 -0.080103\n",
       "China                        341               326    -15 -0.043988\n",
       "Japan                        443               429    -14 -0.031603\n",
       "United States               7705              7465   -240 -0.031149\n",
       "Denmark                      242               238     -4 -0.016529\n",
       "Bulgaria                     207               206     -1 -0.004831\n",
       "Finland                      275               274     -1 -0.003636\n",
       "Mexico                       332               332      0  0.000000\n",
       "Taiwan                       278               278      0  0.000000\n",
       "Czech Republic               420               420      0  0.000000"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_data.query('full_ip_sessions > 200').head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Countries with proportionally large gains:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_ip_sessions</th>\n",
       "      <th>anon_ip_sessions</th>\n",
       "      <th>delta</th>\n",
       "      <th>dprop</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Philippines</th>\n",
       "      <td>266</td>\n",
       "      <td>272</td>\n",
       "      <td>6</td>\n",
       "      <td>0.022556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lithuania</th>\n",
       "      <td>217</td>\n",
       "      <td>222</td>\n",
       "      <td>5</td>\n",
       "      <td>0.023041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>India</th>\n",
       "      <td>4142</td>\n",
       "      <td>4243</td>\n",
       "      <td>101</td>\n",
       "      <td>0.024384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indonesia</th>\n",
       "      <td>430</td>\n",
       "      <td>441</td>\n",
       "      <td>11</td>\n",
       "      <td>0.025581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Portugal</th>\n",
       "      <td>230</td>\n",
       "      <td>236</td>\n",
       "      <td>6</td>\n",
       "      <td>0.026087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Malaysia</th>\n",
       "      <td>233</td>\n",
       "      <td>242</td>\n",
       "      <td>9</td>\n",
       "      <td>0.038627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ukraine</th>\n",
       "      <td>1100</td>\n",
       "      <td>1144</td>\n",
       "      <td>44</td>\n",
       "      <td>0.040000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ireland</th>\n",
       "      <td>202</td>\n",
       "      <td>211</td>\n",
       "      <td>9</td>\n",
       "      <td>0.044554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweden</th>\n",
       "      <td>478</td>\n",
       "      <td>506</td>\n",
       "      <td>28</td>\n",
       "      <td>0.058577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Singapore</th>\n",
       "      <td>311</td>\n",
       "      <td>331</td>\n",
       "      <td>20</td>\n",
       "      <td>0.064309</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             full_ip_sessions  anon_ip_sessions  delta     dprop\n",
       "country                                                         \n",
       "Philippines               266               272      6  0.022556\n",
       "Lithuania                 217               222      5  0.023041\n",
       "India                    4142              4243    101  0.024384\n",
       "Indonesia                 430               441     11  0.025581\n",
       "Portugal                  230               236      6  0.026087\n",
       "Malaysia                  233               242      9  0.038627\n",
       "Ukraine                  1100              1144     44  0.040000\n",
       "Ireland                   202               211      9  0.044554\n",
       "Sweden                    478               506     28  0.058577\n",
       "Singapore                 311               331     20  0.064309"
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_data.query('full_ip_sessions > 200').tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's the proportion of the fluctuation?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.018429242692378105"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_data.delta.map(abs).sum()/country_data.full_ip_sessions.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The deltas are below 10%, either positive or negative. And there's globally less than 2% of country attribution mismatch.\n",
    "\n",
    "One can then say that __Country attribution is largely insensitive to IP anonymization__."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploration 2 : what does it mean locally in Switzerland?\n",
    "\n",
    "Let us dive one level deeper: at city level. We will focus on Switzerland since we have enough traffic from it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_ip_sessions</th>\n",
       "      <th>anon_ip_sessions</th>\n",
       "      <th>delta</th>\n",
       "      <th>dprop</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Switzerland</th>\n",
       "      <td>4141</td>\n",
       "      <td>4156</td>\n",
       "      <td>15</td>\n",
       "      <td>0.003622</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             full_ip_sessions  anon_ip_sessions  delta     dprop\n",
       "country                                                         \n",
       "Switzerland              4141              4156     15  0.003622"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_data.query('country == \"Switzerland\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Less than 1 percent loss at country level for Switzerland, rather stable. But what's happening at city level?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "full_ip_sessions    4141\n",
       "anon_ip_sessions    4156\n",
       "dtype: float64"
      ]
     },
     "execution_count": 234,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a clean subset\n",
    "swiss_data = data.query('country == \"Switzerland\"').copy()\n",
    "swiss_data.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "swiss_data['delta'] = swiss_data.anon_ip_sessions - swiss_data.full_ip_sessions\n",
    "swiss_data['dprop'] = swiss_data.delta / swiss_data.full_ip_sessions\n",
    "\n",
    "swiss_data.sort_values(by='dprop', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>full_ip_sessions</th>\n",
       "      <th>anon_ip_sessions</th>\n",
       "      <th>delta</th>\n",
       "      <th>dprop</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">Switzerland</th>\n",
       "      <th>Porrentruy</th>\n",
       "      <td>138</td>\n",
       "      <td>1</td>\n",
       "      <td>-137</td>\n",
       "      <td>-0.992754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ebikon</th>\n",
       "      <td>81</td>\n",
       "      <td>1</td>\n",
       "      <td>-80</td>\n",
       "      <td>-0.987654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Basel</th>\n",
       "      <td>162</td>\n",
       "      <td>111</td>\n",
       "      <td>-51</td>\n",
       "      <td>-0.314815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lugano</th>\n",
       "      <td>54</td>\n",
       "      <td>42</td>\n",
       "      <td>-12</td>\n",
       "      <td>-0.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lucerne</th>\n",
       "      <td>71</td>\n",
       "      <td>58</td>\n",
       "      <td>-13</td>\n",
       "      <td>-0.183099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lausanne</th>\n",
       "      <td>341</td>\n",
       "      <td>345</td>\n",
       "      <td>4</td>\n",
       "      <td>0.011730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zurich</th>\n",
       "      <td>1476</td>\n",
       "      <td>1504</td>\n",
       "      <td>28</td>\n",
       "      <td>0.018970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bern</th>\n",
       "      <td>206</td>\n",
       "      <td>232</td>\n",
       "      <td>26</td>\n",
       "      <td>0.126214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Saint Gallen</th>\n",
       "      <td>86</td>\n",
       "      <td>104</td>\n",
       "      <td>18</td>\n",
       "      <td>0.209302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Winterthur</th>\n",
       "      <td>77</td>\n",
       "      <td>97</td>\n",
       "      <td>20</td>\n",
       "      <td>0.259740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Geneva</th>\n",
       "      <td>118</td>\n",
       "      <td>189</td>\n",
       "      <td>71</td>\n",
       "      <td>0.601695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fribourg</th>\n",
       "      <td>105</td>\n",
       "      <td>300</td>\n",
       "      <td>195</td>\n",
       "      <td>1.857143</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          full_ip_sessions  anon_ip_sessions  delta     dprop\n",
       "country     city                                                             \n",
       "Switzerland Porrentruy                 138                 1   -137 -0.992754\n",
       "            Ebikon                      81                 1    -80 -0.987654\n",
       "            Basel                      162               111    -51 -0.314815\n",
       "            Lugano                      54                42    -12 -0.222222\n",
       "            Lucerne                     71                58    -13 -0.183099\n",
       "            Lausanne                   341               345      4  0.011730\n",
       "            Zurich                    1476              1504     28  0.018970\n",
       "            Bern                       206               232     26  0.126214\n",
       "            Saint Gallen                86               104     18  0.209302\n",
       "            Winterthur                  77                97     20  0.259740\n",
       "            Geneva                     118               189     71  0.601695\n",
       "            Fribourg                   105               300    195  1.857143"
      ]
     },
     "execution_count": 237,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "swiss_data.query('full_ip_sessions > 50')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2711905336875151"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# absolute sum of delta \n",
    "swiss_data.delta.map(abs).sum()/swiss_data.full_ip_sessions.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Quite some turmoil at city level!__ For example, Fribourg gains 185% of attributions while Basel loses 30%, Something wild going on in Porrentruy and Ebikon, ...\n",
    "\n",
    "Overall, we see __more than 25% mismatch in city attribution__ for Switzerland."
   ]
  }
 ],
 "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}