python_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.23.4\n",
      "53500\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>TIME</th>\n",
       "      <th>CITIES</th>\n",
       "      <th>INDIC_UR</th>\n",
       "      <th>Value</th>\n",
       "      <th>Flag and Footnotes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2008</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Population on the 1st of January, total</td>\n",
       "      <td>82217837</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Population on the 1st of January, male</td>\n",
       "      <td>40274292</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2008</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Population on the 1st of January, female</td>\n",
       "      <td>41943545</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Population on the 1st of January, 0-4 years, t...</td>\n",
       "      <td>3469044</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Population on the 1st of January, 0-4 years, male</td>\n",
       "      <td>1780414</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TIME   CITIES                                           INDIC_UR     Value  \\\n",
       "0  2008  Germany            Population on the 1st of January, total  82217837   \n",
       "1  2008  Germany             Population on the 1st of January, male  40274292   \n",
       "2  2008  Germany           Population on the 1st of January, female  41943545   \n",
       "3  2008  Germany  Population on the 1st of January, 0-4 years, t...   3469044   \n",
       "4  2008  Germany  Population on the 1st of January, 0-4 years, male   1780414   \n",
       "\n",
       "  Flag and Footnotes  \n",
       "0                NaN  \n",
       "1                NaN  \n",
       "2                NaN  \n",
       "3                NaN  \n",
       "4                NaN  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = pd.read_csv('urb_cpop1_1_Data.csv')\n",
    "print(pd.__version__) # I ran with 0.23.4\n",
    "print(len(data))\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>TIME</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>'s-Hertogenbosch:Population on the 1st of January, 0-4 years, male</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4121.0</td>\n",
       "      <td>4131.0</td>\n",
       "      <td>4181.0</td>\n",
       "      <td>4255.0</td>\n",
       "      <td>4295.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>'s-Hertogenbosch:Population on the 1st of January, 0-4 years, total</th>\n",
       "      <td>7885.0</td>\n",
       "      <td>7915.0</td>\n",
       "      <td>8127.0</td>\n",
       "      <td>8211.0</td>\n",
       "      <td>8292.0</td>\n",
       "      <td>8313.0</td>\n",
       "      <td>8290.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>'s-Hertogenbosch:Population on the 1st of January, female</th>\n",
       "      <td>69434.0</td>\n",
       "      <td>70060.0</td>\n",
       "      <td>70979.0</td>\n",
       "      <td>71586.0</td>\n",
       "      <td>72085.0</td>\n",
       "      <td>72541.0</td>\n",
       "      <td>72877.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>'s-Hertogenbosch:Population on the 1st of January, male</th>\n",
       "      <td>67047.0</td>\n",
       "      <td>67715.0</td>\n",
       "      <td>68628.0</td>\n",
       "      <td>69200.0</td>\n",
       "      <td>69808.0</td>\n",
       "      <td>70276.0</td>\n",
       "      <td>70856.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>'s-Hertogenbosch:Population on the 1st of January, total</th>\n",
       "      <td>136481.0</td>\n",
       "      <td>137775.0</td>\n",
       "      <td>139607.0</td>\n",
       "      <td>140786.0</td>\n",
       "      <td>141893.0</td>\n",
       "      <td>142817.0</td>\n",
       "      <td>143733.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "TIME                                                    2008      2009  \\\n",
       "key                                                                      \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...       NaN       NaN   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...    7885.0    7915.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...   69434.0   70060.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...   67047.0   67715.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...  136481.0  137775.0   \n",
       "\n",
       "TIME                                                    2010      2011  \\\n",
       "key                                                                      \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...    4121.0    4131.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...    8127.0    8211.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...   70979.0   71586.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...   68628.0   69200.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...  139607.0  140786.0   \n",
       "\n",
       "TIME                                                    2012      2013  \\\n",
       "key                                                                      \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...    4181.0    4255.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...    8292.0    8313.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...   72085.0   72541.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...   69808.0   70276.0   \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...  141893.0  142817.0   \n",
       "\n",
       "TIME                                                    2014  2015  2016  2017  \n",
       "key                                                                             \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...    4295.0   NaN   NaN   NaN  \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...    8290.0   NaN   NaN   NaN  \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...   72877.0   NaN   NaN   NaN  \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...   70856.0   NaN   NaN   NaN  \n",
       "'s-Hertogenbosch:Population on the 1st of Janua...  143733.0   NaN   NaN   NaN  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove rows that have no value\n",
    "filtered = data.drop(data[pd.isna(data.Value) | (data.Value == \":\")].index)\n",
    "filtered['key'] = filtered['CITIES'] + ':' + filtered['INDIC_UR']\n",
    "filtered['Value'] = pd.to_numeric(filtered['Value'])\n",
    "\n",
    "# pivot(...) will fail because there are some cities (e.g. Barcelona, Bilbao) that have multiple entries\n",
    "# for the same year\n",
    "cities = filtered.pivot_table(index='key', columns='TIME', values='Value', aggfunc=\"mean\")\n",
    "cities.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>TIME</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>United Kingdom:Population on the 1st of January, total</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64853393.0</td>\n",
       "      <td>65379044.0</td>\n",
       "      <td>65844142.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Portugal:Population on the 1st of January, total</th>\n",
       "      <td>10627250.0</td>\n",
       "      <td>10637713.0</td>\n",
       "      <td>10573479.0</td>\n",
       "      <td>10572721.0</td>\n",
       "      <td>10542398.0</td>\n",
       "      <td>10487289.0</td>\n",
       "      <td>10427301.0</td>\n",
       "      <td>10374822.0</td>\n",
       "      <td>10341330.0</td>\n",
       "      <td>10309573.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>London (greater city):Population on the 1st of January, total</th>\n",
       "      <td>7668300.0</td>\n",
       "      <td>7753600.0</td>\n",
       "      <td>8002000.0</td>\n",
       "      <td>8173941.0</td>\n",
       "      <td>8256400.0</td>\n",
       "      <td>8362500.0</td>\n",
       "      <td>8477600.0</td>\n",
       "      <td>8606201.0</td>\n",
       "      <td>8730803.0</td>\n",
       "      <td>8797330.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slovakia:Population on the 1st of January, total</th>\n",
       "      <td>5412254.0</td>\n",
       "      <td>5424925.0</td>\n",
       "      <td>5435273.0</td>\n",
       "      <td>5397036.0</td>\n",
       "      <td>5404322.0</td>\n",
       "      <td>5410836.0</td>\n",
       "      <td>5415949.0</td>\n",
       "      <td>5421349.0</td>\n",
       "      <td>5426252.0</td>\n",
       "      <td>5435343.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Greater Manchester:Population on the 1st of January, total</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2650800.0</td>\n",
       "      <td>2682528.0</td>\n",
       "      <td>2693800.0</td>\n",
       "      <td>2708600.0</td>\n",
       "      <td>2723900.0</td>\n",
       "      <td>2744508.0</td>\n",
       "      <td>2769152.0</td>\n",
       "      <td>2789822.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Midlands urban area:Population on the 1st of January, total</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2390000.0</td>\n",
       "      <td>2419500.0</td>\n",
       "      <td>2431200.0</td>\n",
       "      <td>2446600.0</td>\n",
       "      <td>2462300.0</td>\n",
       "      <td>2479550.0</td>\n",
       "      <td>2500093.0</td>\n",
       "      <td>2527245.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Latvia:Population on the 1st of January, total</th>\n",
       "      <td>2270894.0</td>\n",
       "      <td>2261294.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2070371.0</td>\n",
       "      <td>2044813.0</td>\n",
       "      <td>2023825.0</td>\n",
       "      <td>2001468.0</td>\n",
       "      <td>1986096.0</td>\n",
       "      <td>1968957.0</td>\n",
       "      <td>1950116.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisboa (greater city):Population on the 1st of January, total</th>\n",
       "      <td>1790389.0</td>\n",
       "      <td>1784236.0</td>\n",
       "      <td>1857112.0</td>\n",
       "      <td>1863069.0</td>\n",
       "      <td>1860256.0</td>\n",
       "      <td>1849472.0</td>\n",
       "      <td>1837852.0</td>\n",
       "      <td>1835785.0</td>\n",
       "      <td>1835894.0</td>\n",
       "      <td>1842352.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Birmingham:Population on the 1st of January, total</th>\n",
       "      <td>1019200.0</td>\n",
       "      <td>1028700.0</td>\n",
       "      <td>1055600.0</td>\n",
       "      <td>1073045.0</td>\n",
       "      <td>1079900.0</td>\n",
       "      <td>1088900.0</td>\n",
       "      <td>1096800.0</td>\n",
       "      <td>1106334.0</td>\n",
       "      <td>1117938.0</td>\n",
       "      <td>1132600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Greater Glasgow:Population on the 1st of January, total</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>986575.0</td>\n",
       "      <td>996545.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "TIME                                                      2008        2009  \\\n",
       "key                                                                          \n",
       "United Kingdom:Population on the 1st of January...         NaN         NaN   \n",
       "Portugal:Population on the 1st of January, total    10627250.0  10637713.0   \n",
       "London (greater city):Population on the 1st of ...   7668300.0   7753600.0   \n",
       "Slovakia:Population on the 1st of January, total     5412254.0   5424925.0   \n",
       "Greater Manchester:Population on the 1st of Jan...         NaN         NaN   \n",
       "West Midlands urban area:Population on the 1st ...         NaN         NaN   \n",
       "Latvia:Population on the 1st of January, total       2270894.0   2261294.0   \n",
       "Lisboa (greater city):Population on the 1st of ...   1790389.0   1784236.0   \n",
       "Birmingham:Population on the 1st of January, total   1019200.0   1028700.0   \n",
       "Greater Glasgow:Population on the 1st of Januar...         NaN         NaN   \n",
       "\n",
       "TIME                                                      2010        2011  \\\n",
       "key                                                                          \n",
       "United Kingdom:Population on the 1st of January...         NaN         NaN   \n",
       "Portugal:Population on the 1st of January, total    10573479.0  10572721.0   \n",
       "London (greater city):Population on the 1st of ...   8002000.0   8173941.0   \n",
       "Slovakia:Population on the 1st of January, total     5435273.0   5397036.0   \n",
       "Greater Manchester:Population on the 1st of Jan...   2650800.0   2682528.0   \n",
       "West Midlands urban area:Population on the 1st ...   2390000.0   2419500.0   \n",
       "Latvia:Population on the 1st of January, total             NaN   2070371.0   \n",
       "Lisboa (greater city):Population on the 1st of ...   1857112.0   1863069.0   \n",
       "Birmingham:Population on the 1st of January, total   1055600.0   1073045.0   \n",
       "Greater Glasgow:Population on the 1st of Januar...         NaN         NaN   \n",
       "\n",
       "TIME                                                      2012        2013  \\\n",
       "key                                                                          \n",
       "United Kingdom:Population on the 1st of January...         NaN         NaN   \n",
       "Portugal:Population on the 1st of January, total    10542398.0  10487289.0   \n",
       "London (greater city):Population on the 1st of ...   8256400.0   8362500.0   \n",
       "Slovakia:Population on the 1st of January, total     5404322.0   5410836.0   \n",
       "Greater Manchester:Population on the 1st of Jan...   2693800.0   2708600.0   \n",
       "West Midlands urban area:Population on the 1st ...   2431200.0   2446600.0   \n",
       "Latvia:Population on the 1st of January, total       2044813.0   2023825.0   \n",
       "Lisboa (greater city):Population on the 1st of ...   1860256.0   1849472.0   \n",
       "Birmingham:Population on the 1st of January, total   1079900.0   1088900.0   \n",
       "Greater Glasgow:Population on the 1st of Januar...         NaN         NaN   \n",
       "\n",
       "TIME                                                      2014        2015  \\\n",
       "key                                                                          \n",
       "United Kingdom:Population on the 1st of January...         NaN  64853393.0   \n",
       "Portugal:Population on the 1st of January, total    10427301.0  10374822.0   \n",
       "London (greater city):Population on the 1st of ...   8477600.0   8606201.0   \n",
       "Slovakia:Population on the 1st of January, total     5415949.0   5421349.0   \n",
       "Greater Manchester:Population on the 1st of Jan...   2723900.0   2744508.0   \n",
       "West Midlands urban area:Population on the 1st ...   2462300.0   2479550.0   \n",
       "Latvia:Population on the 1st of January, total       2001468.0   1986096.0   \n",
       "Lisboa (greater city):Population on the 1st of ...   1837852.0   1835785.0   \n",
       "Birmingham:Population on the 1st of January, total   1096800.0   1106334.0   \n",
       "Greater Glasgow:Population on the 1st of Januar...         NaN         NaN   \n",
       "\n",
       "TIME                                                      2016        2017  \n",
       "key                                                                         \n",
       "United Kingdom:Population on the 1st of January...  65379044.0  65844142.0  \n",
       "Portugal:Population on the 1st of January, total    10341330.0  10309573.0  \n",
       "London (greater city):Population on the 1st of ...   8730803.0   8797330.0  \n",
       "Slovakia:Population on the 1st of January, total     5426252.0   5435343.0  \n",
       "Greater Manchester:Population on the 1st of Jan...   2769152.0   2789822.0  \n",
       "West Midlands urban area:Population on the 1st ...   2500093.0   2527245.0  \n",
       "Latvia:Population on the 1st of January, total       1968957.0   1950116.0  \n",
       "Lisboa (greater city):Population on the 1st of ...   1835894.0   1842352.0  \n",
       "Birmingham:Population on the 1st of January, total   1117938.0   1132600.0  \n",
       "Greater Glasgow:Population on the 1st of Januar...    986575.0    996545.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show biggest cities in 2017\n",
    "\n",
    "# just 'total' would also give us \"Population on the 1st of January, 0-4 years, total\" items\n",
    "key_filter = 'January, total'\n",
    "\n",
    "cities.filter(like=key_filter,axis=0).sort_values(by=[2017], ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>TIME</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>growth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bournemouth:Population on the 1st of January, total</th>\n",
       "      <td>164600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176800.0</td>\n",
       "      <td>183491.0</td>\n",
       "      <td>185100.0</td>\n",
       "      <td>187700.0</td>\n",
       "      <td>190100.0</td>\n",
       "      <td>268124.5</td>\n",
       "      <td>271606.0</td>\n",
       "      <td>269698.0</td>\n",
       "      <td>53.623303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oulu:Population on the 1st of January, total</th>\n",
       "      <td>131585.0</td>\n",
       "      <td>133550.0</td>\n",
       "      <td>139133.0</td>\n",
       "      <td>141671.0</td>\n",
       "      <td>143909.0</td>\n",
       "      <td>190847.0</td>\n",
       "      <td>193798.0</td>\n",
       "      <td>196291.0</td>\n",
       "      <td>198525.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.687213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Derry &amp; Strabane Local Government District:Population on the 1st of January, total</th>\n",
       "      <td>109100.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>108400.0</td>\n",
       "      <td>107877.0</td>\n",
       "      <td>108400.0</td>\n",
       "      <td>108600.0</td>\n",
       "      <td>108900.0</td>\n",
       "      <td>149336.0</td>\n",
       "      <td>149808.0</td>\n",
       "      <td>150320.0</td>\n",
       "      <td>38.199262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southampton:Population on the 1st of January, total</th>\n",
       "      <td>234100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>231600.0</td>\n",
       "      <td>236882.0</td>\n",
       "      <td>237600.0</td>\n",
       "      <td>240800.0</td>\n",
       "      <td>243700.0</td>\n",
       "      <td>311890.0</td>\n",
       "      <td>316571.5</td>\n",
       "      <td>316379.0</td>\n",
       "      <td>36.688903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Blackpool:Population on the 1st of January, total</th>\n",
       "      <td>140600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>142700.0</td>\n",
       "      <td>142065.0</td>\n",
       "      <td>142000.0</td>\n",
       "      <td>141700.0</td>\n",
       "      <td>141000.0</td>\n",
       "      <td>194661.5</td>\n",
       "      <td>194388.5</td>\n",
       "      <td>195034.0</td>\n",
       "      <td>36.221794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Valencia:Population on the 1st of January, total</th>\n",
       "      <td>807200.0</td>\n",
       "      <td>814208.0</td>\n",
       "      <td>809267.0</td>\n",
       "      <td>792054.0</td>\n",
       "      <td>797028.0</td>\n",
       "      <td>792303.0</td>\n",
       "      <td>786424.0</td>\n",
       "      <td>1085048.5</td>\n",
       "      <td>1089284.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.601374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Granada:Population on the 1st of January, total</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>239154.0</td>\n",
       "      <td>241003.0</td>\n",
       "      <td>239017.0</td>\n",
       "      <td>237818.0</td>\n",
       "      <td>237540.0</td>\n",
       "      <td>317253.5</td>\n",
       "      <td>317160.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.617477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pamplona/Iru�a:Population on the 1st of January, total</th>\n",
       "      <td>197275.0</td>\n",
       "      <td>198491.0</td>\n",
       "      <td>197488.0</td>\n",
       "      <td>195943.0</td>\n",
       "      <td>197604.0</td>\n",
       "      <td>196955.0</td>\n",
       "      <td>196166.0</td>\n",
       "      <td>257629.0</td>\n",
       "      <td>257984.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.632747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Milano (greater city):Population on the 1st of January, total</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3154102.0</td>\n",
       "      <td>3854555.0</td>\n",
       "      <td>3875801.0</td>\n",
       "      <td>3925767.0</td>\n",
       "      <td>4038864.0</td>\n",
       "      <td>4061382.0</td>\n",
       "      <td>4074585.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.183679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stoke-on-trent:Population on the 1st of January, total</th>\n",
       "      <td>239300.0</td>\n",
       "      <td>238900.0</td>\n",
       "      <td>246600.0</td>\n",
       "      <td>249008.0</td>\n",
       "      <td>249300.0</td>\n",
       "      <td>250100.0</td>\n",
       "      <td>250600.0</td>\n",
       "      <td>314612.0</td>\n",
       "      <td>316315.0</td>\n",
       "      <td>318791.0</td>\n",
       "      <td>28.270479</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "TIME                                                    2008      2009  \\\n",
       "key                                                                      \n",
       "Bournemouth:Population on the 1st of January, t...  164600.0       NaN   \n",
       "Oulu:Population on the 1st of January, total        131585.0  133550.0   \n",
       "Derry & Strabane Local Government District:Popu...  109100.0  109600.0   \n",
       "Southampton:Population on the 1st of January, t...  234100.0       NaN   \n",
       "Blackpool:Population on the 1st of January, total   140600.0       NaN   \n",
       "Valencia:Population on the 1st of January, total    807200.0  814208.0   \n",
       "Granada:Population on the 1st of January, total          NaN       NaN   \n",
       "Pamplona/Iru�a:Population on the 1st of January...  197275.0  198491.0   \n",
       "Milano (greater city):Population on the 1st of ...       NaN       NaN   \n",
       "Stoke-on-trent:Population on the 1st of January...  239300.0  238900.0   \n",
       "\n",
       "TIME                                                     2010       2011  \\\n",
       "key                                                                        \n",
       "Bournemouth:Population on the 1st of January, t...   176800.0   183491.0   \n",
       "Oulu:Population on the 1st of January, total         139133.0   141671.0   \n",
       "Derry & Strabane Local Government District:Popu...   108400.0   107877.0   \n",
       "Southampton:Population on the 1st of January, t...   231600.0   236882.0   \n",
       "Blackpool:Population on the 1st of January, total    142700.0   142065.0   \n",
       "Valencia:Population on the 1st of January, total     809267.0   792054.0   \n",
       "Granada:Population on the 1st of January, total      239154.0   241003.0   \n",
       "Pamplona/Iru�a:Population on the 1st of January...   197488.0   195943.0   \n",
       "Milano (greater city):Population on the 1st of ...  3154102.0  3854555.0   \n",
       "Stoke-on-trent:Population on the 1st of January...   246600.0   249008.0   \n",
       "\n",
       "TIME                                                     2012       2013  \\\n",
       "key                                                                        \n",
       "Bournemouth:Population on the 1st of January, t...   185100.0   187700.0   \n",
       "Oulu:Population on the 1st of January, total         143909.0   190847.0   \n",
       "Derry & Strabane Local Government District:Popu...   108400.0   108600.0   \n",
       "Southampton:Population on the 1st of January, t...   237600.0   240800.0   \n",
       "Blackpool:Population on the 1st of January, total    142000.0   141700.0   \n",
       "Valencia:Population on the 1st of January, total     797028.0   792303.0   \n",
       "Granada:Population on the 1st of January, total      239017.0   237818.0   \n",
       "Pamplona/Iru�a:Population on the 1st of January...   197604.0   196955.0   \n",
       "Milano (greater city):Population on the 1st of ...  3875801.0  3925767.0   \n",
       "Stoke-on-trent:Population on the 1st of January...   249300.0   250100.0   \n",
       "\n",
       "TIME                                                     2014       2015  \\\n",
       "key                                                                        \n",
       "Bournemouth:Population on the 1st of January, t...   190100.0   268124.5   \n",
       "Oulu:Population on the 1st of January, total         193798.0   196291.0   \n",
       "Derry & Strabane Local Government District:Popu...   108900.0   149336.0   \n",
       "Southampton:Population on the 1st of January, t...   243700.0   311890.0   \n",
       "Blackpool:Population on the 1st of January, total    141000.0   194661.5   \n",
       "Valencia:Population on the 1st of January, total     786424.0  1085048.5   \n",
       "Granada:Population on the 1st of January, total      237540.0   317253.5   \n",
       "Pamplona/Iru�a:Population on the 1st of January...   196166.0   257629.0   \n",
       "Milano (greater city):Population on the 1st of ...  4038864.0  4061382.0   \n",
       "Stoke-on-trent:Population on the 1st of January...   250600.0   314612.0   \n",
       "\n",
       "TIME                                                     2016      2017  \\\n",
       "key                                                                       \n",
       "Bournemouth:Population on the 1st of January, t...   271606.0  269698.0   \n",
       "Oulu:Population on the 1st of January, total         198525.0       NaN   \n",
       "Derry & Strabane Local Government District:Popu...   149808.0  150320.0   \n",
       "Southampton:Population on the 1st of January, t...   316571.5  316379.0   \n",
       "Blackpool:Population on the 1st of January, total    194388.5  195034.0   \n",
       "Valencia:Population on the 1st of January, total    1089284.5       NaN   \n",
       "Granada:Population on the 1st of January, total      317160.0       NaN   \n",
       "Pamplona/Iru�a:Population on the 1st of January...   257984.0       NaN   \n",
       "Milano (greater city):Population on the 1st of ...  4074585.0       NaN   \n",
       "Stoke-on-trent:Population on the 1st of January...   316315.0  318791.0   \n",
       "\n",
       "TIME                                                   growth  \n",
       "key                                                            \n",
       "Bournemouth:Population on the 1st of January, t...  53.623303  \n",
       "Oulu:Population on the 1st of January, total        42.687213  \n",
       "Derry & Strabane Local Government District:Popu...  38.199262  \n",
       "Southampton:Population on the 1st of January, t...  36.688903  \n",
       "Blackpool:Population on the 1st of January, total   36.221794  \n",
       "Valencia:Population on the 1st of January, total    34.601374  \n",
       "Granada:Population on the 1st of January, total     32.617477  \n",
       "Pamplona/Iru�a:Population on the 1st of January...  30.632747  \n",
       "Milano (greater city):Population on the 1st of ...  29.183679  \n",
       "Stoke-on-trent:Population on the 1st of January...  28.270479  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add a growth % 2010 - 2016 column \n",
    "cities[\"growth\"] = (cities[2016] / cities[2010] - 1) * 100\n",
    "cities.filter(like=key_filter,axis=0).sort_values(by=[\"growth\"], ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}