main.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-1-b74041b8537f>:7: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.\n",
      "  df_stolen = pd.read_csv(csv_raw, skiprows=6, sep='\";\"', names=['Desc', '2010', '2011', '2012', '2013', '2014', '2015',\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                Desc    2011    2012    2013    2014    2015    2016    2017  \\\n",
      "0    Aantal (x 1000)    1198    1035    1008    1051     983     928     957   \n",
      "1  Waarde (x € 1000)  879240  769159  797264  886605  899020  936829  976034   \n",
      "\n",
      "      2018  Growth ratio  \n",
      "0     1011          0.06  \n",
      "1  1220291          0.25  \n",
      "(2, 10)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-1-42f1a593a19d>:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  pop_percentage ['Desc'][1] = 'Pct_Population_AMS'\n",
      "<ipython-input-1-42f1a593a19d>:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  pop_percentage ['Desc'][5] = 'Pct_Population_NL'\n",
      "<ipython-input-1-ccd29760d882>:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  population['Desc'][1] = 'Pop_Increase_AMS'\n",
      "<ipython-input-1-ccd29760d882>:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  population['Desc'][5] = 'Pop_Increase_NL'\n"
     ]
    }
   ],
   "source": [
    "%run ./confiscated.ipynb\n",
    "%run ./stolen.ipynb\n",
    "%run ./Purchased.ipynb\n",
    "%run ./Population.ipynb"
   ]
  },
  {
   "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></th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>Desc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Desc</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>Total amount bikes NL</th>\n",
       "      <td>19032000</td>\n",
       "      <td>19960000</td>\n",
       "      <td>20917000</td>\n",
       "      <td>21928000</td>\n",
       "      <td>Total amount bikes NL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <td>687037</td>\n",
       "      <td>732057</td>\n",
       "      <td>778739</td>\n",
       "      <td>828274</td>\n",
       "      <td>Total amount bikes AMS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased bikes NL</th>\n",
       "      <td>928000</td>\n",
       "      <td>957000</td>\n",
       "      <td>1011000</td>\n",
       "      <td>1072000</td>\n",
       "      <td>Purchased bikes NL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <td>45020</td>\n",
       "      <td>46682</td>\n",
       "      <td>49535</td>\n",
       "      <td>52726</td>\n",
       "      <td>Purchased bikes AMS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fetched bikes NL</th>\n",
       "      <td>15000</td>\n",
       "      <td>15000</td>\n",
       "      <td>15000</td>\n",
       "      <td>15000</td>\n",
       "      <td>Fetched bikes NL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>Fetched bikes AMS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased - Fetched bikes NL</th>\n",
       "      <td>913000</td>\n",
       "      <td>942000</td>\n",
       "      <td>996000</td>\n",
       "      <td>1057000</td>\n",
       "      <td>Purchased - Fetched bikes NL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <td>39020</td>\n",
       "      <td>40682</td>\n",
       "      <td>43535</td>\n",
       "      <td>46726</td>\n",
       "      <td>Purchased - Fetched bikes AMS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   2016      2017      2018      2019  \\\n",
       "Desc                                                                    \n",
       "Total amount bikes NL          19032000  19960000  20917000  21928000   \n",
       "Total amount bikes AMS           687037    732057    778739    828274   \n",
       "Purchased bikes NL               928000    957000   1011000   1072000   \n",
       "Purchased bikes AMS               45020     46682     49535     52726   \n",
       "Fetched bikes NL                  15000     15000     15000     15000   \n",
       "Fetched bikes AMS                  6000      6000      6000      6000   \n",
       "Purchased - Fetched bikes NL     913000    942000    996000   1057000   \n",
       "Purchased - Fetched bikes AMS     39020     40682     43535     46726   \n",
       "\n",
       "                                                        Desc  \n",
       "Desc                                                          \n",
       "Total amount bikes NL                  Total amount bikes NL  \n",
       "Total amount bikes AMS                Total amount bikes AMS  \n",
       "Purchased bikes NL                        Purchased bikes NL  \n",
       "Purchased bikes AMS                      Purchased bikes AMS  \n",
       "Fetched bikes NL                            Fetched bikes NL  \n",
       "Fetched bikes AMS                          Fetched bikes AMS  \n",
       "Purchased - Fetched bikes NL    Purchased - Fetched bikes NL  \n",
       "Purchased - Fetched bikes AMS  Purchased - Fetched bikes AMS  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_purchased['Desc'] = total_purchased.index\n",
    "total_purchased"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "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>Desc</th>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <th>Stolen bikes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>687037</td>\n",
       "      <td>45020</td>\n",
       "      <td>6000</td>\n",
       "      <td>39020</td>\n",
       "      <td>21769</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>732057</td>\n",
       "      <td>46682</td>\n",
       "      <td>6000</td>\n",
       "      <td>40682</td>\n",
       "      <td>27043</td>\n",
       "      <td>1.1</td>\n",
       "      <td>78835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>778739</td>\n",
       "      <td>49535</td>\n",
       "      <td>6000</td>\n",
       "      <td>43535</td>\n",
       "      <td>26169</td>\n",
       "      <td>1</td>\n",
       "      <td>68190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>828274</td>\n",
       "      <td>52726</td>\n",
       "      <td>6000</td>\n",
       "      <td>46726</td>\n",
       "      <td>32020</td>\n",
       "      <td>1.1</td>\n",
       "      <td>66690</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Desc Total amount bikes AMS Purchased bikes AMS Fetched bikes AMS  \\\n",
       "2016                 687037               45020              6000   \n",
       "2017                 732057               46682              6000   \n",
       "2018                 778739               49535              6000   \n",
       "2019                 828274               52726              6000   \n",
       "\n",
       "Desc Purchased - Fetched bikes AMS Conf.bikes left market Pct_Population_AMS  \\\n",
       "2016                         39020                  21769                1.2   \n",
       "2017                         40682                  27043                1.1   \n",
       "2018                         43535                  26169                  1   \n",
       "2019                         46726                  32020                1.1   \n",
       "\n",
       "Desc Stolen bikes  \n",
       "2016        96675  \n",
       "2017        78835  \n",
       "2018        68190  \n",
       "2019        66690  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A bit of fiddling to keep only relevant columns.\n",
    "cons_table = pd.concat([total_purchased, total_conf, pop_percentage, total_stolen], ignore_index=True, sort=False)\n",
    "cons_table = cons_table.transpose()\n",
    "cons_table = cons_table.drop([0, 2, 4, 6, 8, 11], axis=1).transpose().set_index('Desc').transpose()\n",
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "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>Desc</th>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <th>Stolen bikes</th>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>687037</td>\n",
       "      <td>45020</td>\n",
       "      <td>6000</td>\n",
       "      <td>39020</td>\n",
       "      <td>21769</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96675</td>\n",
       "      <td>17251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>732057</td>\n",
       "      <td>46682</td>\n",
       "      <td>6000</td>\n",
       "      <td>40682</td>\n",
       "      <td>27043</td>\n",
       "      <td>1.1</td>\n",
       "      <td>78835</td>\n",
       "      <td>13639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>778739</td>\n",
       "      <td>49535</td>\n",
       "      <td>6000</td>\n",
       "      <td>43535</td>\n",
       "      <td>26169</td>\n",
       "      <td>1</td>\n",
       "      <td>68190</td>\n",
       "      <td>17366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>828274</td>\n",
       "      <td>52726</td>\n",
       "      <td>6000</td>\n",
       "      <td>46726</td>\n",
       "      <td>32020</td>\n",
       "      <td>1.1</td>\n",
       "      <td>66690</td>\n",
       "      <td>14706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Desc Total amount bikes AMS Purchased bikes AMS Fetched bikes AMS  \\\n",
       "2016                 687037               45020              6000   \n",
       "2017                 732057               46682              6000   \n",
       "2018                 778739               49535              6000   \n",
       "2019                 828274               52726              6000   \n",
       "\n",
       "Desc Purchased - Fetched bikes AMS Conf.bikes left market Pct_Population_AMS  \\\n",
       "2016                         39020                  21769                1.2   \n",
       "2017                         40682                  27043                1.1   \n",
       "2018                         43535                  26169                  1   \n",
       "2019                         46726                  32020                1.1   \n",
       "\n",
       "Desc Stolen bikes Net increase bikes, abs  \n",
       "2016        96675                   17251  \n",
       "2017        78835                   13639  \n",
       "2018        68190                   17366  \n",
       "2019        66690                   14706  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding calculation of net increase in bikes in absolute numbers\n",
    "cons_table['Net increase bikes, abs'] = cons_table['Purchased - Fetched bikes AMS']-cons_table['Conf.bikes left market']\n",
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Desc</th>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <th>Stolen bikes</th>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <th>Net increase bikes, %</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>687037</td>\n",
       "      <td>45020</td>\n",
       "      <td>6000</td>\n",
       "      <td>39020</td>\n",
       "      <td>21769</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96675</td>\n",
       "      <td>17251</td>\n",
       "      <td>2.51093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>732057</td>\n",
       "      <td>46682</td>\n",
       "      <td>6000</td>\n",
       "      <td>40682</td>\n",
       "      <td>27043</td>\n",
       "      <td>1.1</td>\n",
       "      <td>78835</td>\n",
       "      <td>13639</td>\n",
       "      <td>1.86311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>778739</td>\n",
       "      <td>49535</td>\n",
       "      <td>6000</td>\n",
       "      <td>43535</td>\n",
       "      <td>26169</td>\n",
       "      <td>1</td>\n",
       "      <td>68190</td>\n",
       "      <td>17366</td>\n",
       "      <td>2.23002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>828274</td>\n",
       "      <td>52726</td>\n",
       "      <td>6000</td>\n",
       "      <td>46726</td>\n",
       "      <td>32020</td>\n",
       "      <td>1.1</td>\n",
       "      <td>66690</td>\n",
       "      <td>14706</td>\n",
       "      <td>1.7755</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Desc Total amount bikes AMS Purchased bikes AMS Fetched bikes AMS  \\\n",
       "2016                 687037               45020              6000   \n",
       "2017                 732057               46682              6000   \n",
       "2018                 778739               49535              6000   \n",
       "2019                 828274               52726              6000   \n",
       "\n",
       "Desc Purchased - Fetched bikes AMS Conf.bikes left market Pct_Population_AMS  \\\n",
       "2016                         39020                  21769                1.2   \n",
       "2017                         40682                  27043                1.1   \n",
       "2018                         43535                  26169                  1   \n",
       "2019                         46726                  32020                1.1   \n",
       "\n",
       "Desc Stolen bikes Net increase bikes, abs Net increase bikes, %  \n",
       "2016        96675                   17251               2.51093  \n",
       "2017        78835                   13639               1.86311  \n",
       "2018        68190                   17366               2.23002  \n",
       "2019        66690                   14706                1.7755  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding calculation of net increase in bikes in persentage\n",
    "cons_table['Net increase bikes, %'] = cons_table['Net increase bikes, abs']/cons_table['Total amount bikes AMS']*100\n",
    "cons_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Amendments of calculation of total amount of bikes per year.***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[847000, 0, 0, 0, 0]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q2015 = 847000\n",
    "amendment = [q2015, 0, 0, 0, 0]\n",
    "amendment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "cons_table = cons_table.transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Desc</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <td>687037</td>\n",
       "      <td>732057</td>\n",
       "      <td>778739</td>\n",
       "      <td>828274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <td>45020</td>\n",
       "      <td>46682</td>\n",
       "      <td>49535</td>\n",
       "      <td>52726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <td>39020</td>\n",
       "      <td>40682</td>\n",
       "      <td>43535</td>\n",
       "      <td>46726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <td>21769</td>\n",
       "      <td>27043</td>\n",
       "      <td>26169</td>\n",
       "      <td>32020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <td>1.2</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stolen bikes</th>\n",
       "      <td>96675</td>\n",
       "      <td>78835</td>\n",
       "      <td>68190</td>\n",
       "      <td>66690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <td>17251</td>\n",
       "      <td>13639</td>\n",
       "      <td>17366</td>\n",
       "      <td>14706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Net increase bikes, %</th>\n",
       "      <td>2.51093</td>\n",
       "      <td>1.86311</td>\n",
       "      <td>2.23002</td>\n",
       "      <td>1.7755</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  2016     2017     2018    2019\n",
       "Desc                                                            \n",
       "Total amount bikes AMS          687037   732057   778739  828274\n",
       "Purchased bikes AMS              45020    46682    49535   52726\n",
       "Fetched bikes AMS                 6000     6000     6000    6000\n",
       "Purchased - Fetched bikes AMS    39020    40682    43535   46726\n",
       "Conf.bikes left market           21769    27043    26169   32020\n",
       "Pct_Population_AMS                 1.2      1.1        1     1.1\n",
       "Stolen bikes                     96675    78835    68190   66690\n",
       "Net increase bikes, abs          17251    13639    17366   14706\n",
       "Net increase bikes, %          2.51093  1.86311  2.23002  1.7755"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-9-82065c977c74>:1: DeprecationWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.\n",
      "  cons_table['2015'] = pd.Series()\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>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2015</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Desc</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>Total amount bikes AMS</th>\n",
       "      <td>687037</td>\n",
       "      <td>732057</td>\n",
       "      <td>778739</td>\n",
       "      <td>828274</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <td>45020</td>\n",
       "      <td>46682</td>\n",
       "      <td>49535</td>\n",
       "      <td>52726</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <td>39020</td>\n",
       "      <td>40682</td>\n",
       "      <td>43535</td>\n",
       "      <td>46726</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <td>21769</td>\n",
       "      <td>27043</td>\n",
       "      <td>26169</td>\n",
       "      <td>32020</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <td>1.2</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stolen bikes</th>\n",
       "      <td>96675</td>\n",
       "      <td>78835</td>\n",
       "      <td>68190</td>\n",
       "      <td>66690</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <td>17251</td>\n",
       "      <td>13639</td>\n",
       "      <td>17366</td>\n",
       "      <td>14706</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Net increase bikes, %</th>\n",
       "      <td>2.51093</td>\n",
       "      <td>1.86311</td>\n",
       "      <td>2.23002</td>\n",
       "      <td>1.7755</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  2016     2017     2018    2019  2015\n",
       "Desc                                                                  \n",
       "Total amount bikes AMS          687037   732057   778739  828274   NaN\n",
       "Purchased bikes AMS              45020    46682    49535   52726   NaN\n",
       "Fetched bikes AMS                 6000     6000     6000    6000   NaN\n",
       "Purchased - Fetched bikes AMS    39020    40682    43535   46726   NaN\n",
       "Conf.bikes left market           21769    27043    26169   32020   NaN\n",
       "Pct_Population_AMS                 1.2      1.1        1     1.1   NaN\n",
       "Stolen bikes                     96675    78835    68190   66690   NaN\n",
       "Net increase bikes, abs          17251    13639    17366   14706   NaN\n",
       "Net increase bikes, %          2.51093  1.86311  2.23002  1.7755   NaN"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table['2015'] = pd.Series()\n",
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Desc</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>Total amount bikes AMS</th>\n",
       "      <td>NaN</td>\n",
       "      <td>687037</td>\n",
       "      <td>732057</td>\n",
       "      <td>778739</td>\n",
       "      <td>828274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <td>NaN</td>\n",
       "      <td>45020</td>\n",
       "      <td>46682</td>\n",
       "      <td>49535</td>\n",
       "      <td>52726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "      <td>6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <td>NaN</td>\n",
       "      <td>39020</td>\n",
       "      <td>40682</td>\n",
       "      <td>43535</td>\n",
       "      <td>46726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <td>NaN</td>\n",
       "      <td>21769</td>\n",
       "      <td>27043</td>\n",
       "      <td>26169</td>\n",
       "      <td>32020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.2</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stolen bikes</th>\n",
       "      <td>NaN</td>\n",
       "      <td>96675</td>\n",
       "      <td>78835</td>\n",
       "      <td>68190</td>\n",
       "      <td>66690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <td>NaN</td>\n",
       "      <td>17251</td>\n",
       "      <td>13639</td>\n",
       "      <td>17366</td>\n",
       "      <td>14706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Net increase bikes, %</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.51093</td>\n",
       "      <td>1.86311</td>\n",
       "      <td>2.23002</td>\n",
       "      <td>1.7755</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               2015     2016     2017     2018    2019\n",
       "Desc                                                                  \n",
       "Total amount bikes AMS          NaN   687037   732057   778739  828274\n",
       "Purchased bikes AMS             NaN    45020    46682    49535   52726\n",
       "Fetched bikes AMS               NaN     6000     6000     6000    6000\n",
       "Purchased - Fetched bikes AMS   NaN    39020    40682    43535   46726\n",
       "Conf.bikes left market          NaN    21769    27043    26169   32020\n",
       "Pct_Population_AMS              NaN      1.2      1.1        1     1.1\n",
       "Stolen bikes                    NaN    96675    78835    68190   66690\n",
       "Net increase bikes, abs         NaN    17251    13639    17366   14706\n",
       "Net increase bikes, %           NaN  2.51093  1.86311  2.23002  1.7755"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table = cons_table[['2015', '2016', '2017', '2018', '2019']]\n",
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cons_table = cons_table.transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Desc</th>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <th>Stolen bikes</th>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <th>Net increase bikes, %</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>687037</td>\n",
       "      <td>45020</td>\n",
       "      <td>6000</td>\n",
       "      <td>39020</td>\n",
       "      <td>21769</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96675</td>\n",
       "      <td>17251</td>\n",
       "      <td>2.51093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>732057</td>\n",
       "      <td>46682</td>\n",
       "      <td>6000</td>\n",
       "      <td>40682</td>\n",
       "      <td>27043</td>\n",
       "      <td>1.1</td>\n",
       "      <td>78835</td>\n",
       "      <td>13639</td>\n",
       "      <td>1.86311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>778739</td>\n",
       "      <td>49535</td>\n",
       "      <td>6000</td>\n",
       "      <td>43535</td>\n",
       "      <td>26169</td>\n",
       "      <td>1</td>\n",
       "      <td>68190</td>\n",
       "      <td>17366</td>\n",
       "      <td>2.23002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>828274</td>\n",
       "      <td>52726</td>\n",
       "      <td>6000</td>\n",
       "      <td>46726</td>\n",
       "      <td>32020</td>\n",
       "      <td>1.1</td>\n",
       "      <td>66690</td>\n",
       "      <td>14706</td>\n",
       "      <td>1.7755</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Desc Total amount bikes AMS Purchased bikes AMS Fetched bikes AMS  \\\n",
       "2015                    NaN                 NaN               NaN   \n",
       "2016                 687037               45020              6000   \n",
       "2017                 732057               46682              6000   \n",
       "2018                 778739               49535              6000   \n",
       "2019                 828274               52726              6000   \n",
       "\n",
       "Desc Purchased - Fetched bikes AMS Conf.bikes left market Pct_Population_AMS  \\\n",
       "2015                           NaN                    NaN                NaN   \n",
       "2016                         39020                  21769                1.2   \n",
       "2017                         40682                  27043                1.1   \n",
       "2018                         43535                  26169                  1   \n",
       "2019                         46726                  32020                1.1   \n",
       "\n",
       "Desc Stolen bikes Net increase bikes, abs Net increase bikes, %  \n",
       "2015          NaN                     NaN                   NaN  \n",
       "2016        96675                   17251               2.51093  \n",
       "2017        78835                   13639               1.86311  \n",
       "2018        68190                   17366               2.23002  \n",
       "2019        66690                   14706                1.7755  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cons_table['Amount bikes AMS'] = amendment\n",
    "cons_table = cons_table.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Desc</th>\n",
       "      <th>index</th>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <th>Stolen bikes</th>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <th>Net increase bikes, %</th>\n",
       "      <th>Amount bikes AMS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>847000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>687037</td>\n",
       "      <td>45020</td>\n",
       "      <td>6000</td>\n",
       "      <td>39020</td>\n",
       "      <td>21769</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96675</td>\n",
       "      <td>17251</td>\n",
       "      <td>2.51093</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>732057</td>\n",
       "      <td>46682</td>\n",
       "      <td>6000</td>\n",
       "      <td>40682</td>\n",
       "      <td>27043</td>\n",
       "      <td>1.1</td>\n",
       "      <td>78835</td>\n",
       "      <td>13639</td>\n",
       "      <td>1.86311</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018</td>\n",
       "      <td>778739</td>\n",
       "      <td>49535</td>\n",
       "      <td>6000</td>\n",
       "      <td>43535</td>\n",
       "      <td>26169</td>\n",
       "      <td>1</td>\n",
       "      <td>68190</td>\n",
       "      <td>17366</td>\n",
       "      <td>2.23002</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019</td>\n",
       "      <td>828274</td>\n",
       "      <td>52726</td>\n",
       "      <td>6000</td>\n",
       "      <td>46726</td>\n",
       "      <td>32020</td>\n",
       "      <td>1.1</td>\n",
       "      <td>66690</td>\n",
       "      <td>14706</td>\n",
       "      <td>1.7755</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Desc index Total amount bikes AMS Purchased bikes AMS Fetched bikes AMS  \\\n",
       "0     2015                    NaN                 NaN               NaN   \n",
       "1     2016                 687037               45020              6000   \n",
       "2     2017                 732057               46682              6000   \n",
       "3     2018                 778739               49535              6000   \n",
       "4     2019                 828274               52726              6000   \n",
       "\n",
       "Desc Purchased - Fetched bikes AMS Conf.bikes left market Pct_Population_AMS  \\\n",
       "0                              NaN                    NaN                NaN   \n",
       "1                            39020                  21769                1.2   \n",
       "2                            40682                  27043                1.1   \n",
       "3                            43535                  26169                  1   \n",
       "4                            46726                  32020                1.1   \n",
       "\n",
       "Desc Stolen bikes Net increase bikes, abs Net increase bikes, %  \\\n",
       "0             NaN                     NaN                   NaN   \n",
       "1           96675                   17251               2.51093   \n",
       "2           78835                   13639               1.86311   \n",
       "3           68190                   17366               2.23002   \n",
       "4           66690                   14706                1.7755   \n",
       "\n",
       "Desc  Amount bikes AMS  \n",
       "0               847000  \n",
       "1                    0  \n",
       "2                    0  \n",
       "3                    0  \n",
       "4                    0  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for i in range(1, len(cons_table)):\n",
    "    cons_table.loc[i, 'Amount bikes AMS'] = (cons_table.loc[i-1, 'Amount bikes AMS'] +\n",
    "                                             cons_table.loc[i, 'Net increase bikes, abs'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Desc</th>\n",
       "      <th>index</th>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <th>Stolen bikes</th>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <th>Net increase bikes, %</th>\n",
       "      <th>Amount bikes AMS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>847000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>687037</td>\n",
       "      <td>45020</td>\n",
       "      <td>6000</td>\n",
       "      <td>39020</td>\n",
       "      <td>21769</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96675</td>\n",
       "      <td>17251</td>\n",
       "      <td>2.51093</td>\n",
       "      <td>864251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>732057</td>\n",
       "      <td>46682</td>\n",
       "      <td>6000</td>\n",
       "      <td>40682</td>\n",
       "      <td>27043</td>\n",
       "      <td>1.1</td>\n",
       "      <td>78835</td>\n",
       "      <td>13639</td>\n",
       "      <td>1.86311</td>\n",
       "      <td>877890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018</td>\n",
       "      <td>778739</td>\n",
       "      <td>49535</td>\n",
       "      <td>6000</td>\n",
       "      <td>43535</td>\n",
       "      <td>26169</td>\n",
       "      <td>1</td>\n",
       "      <td>68190</td>\n",
       "      <td>17366</td>\n",
       "      <td>2.23002</td>\n",
       "      <td>895256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019</td>\n",
       "      <td>828274</td>\n",
       "      <td>52726</td>\n",
       "      <td>6000</td>\n",
       "      <td>46726</td>\n",
       "      <td>32020</td>\n",
       "      <td>1.1</td>\n",
       "      <td>66690</td>\n",
       "      <td>14706</td>\n",
       "      <td>1.7755</td>\n",
       "      <td>909962</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Desc index Total amount bikes AMS Purchased bikes AMS Fetched bikes AMS  \\\n",
       "0     2015                    NaN                 NaN               NaN   \n",
       "1     2016                 687037               45020              6000   \n",
       "2     2017                 732057               46682              6000   \n",
       "3     2018                 778739               49535              6000   \n",
       "4     2019                 828274               52726              6000   \n",
       "\n",
       "Desc Purchased - Fetched bikes AMS Conf.bikes left market Pct_Population_AMS  \\\n",
       "0                              NaN                    NaN                NaN   \n",
       "1                            39020                  21769                1.2   \n",
       "2                            40682                  27043                1.1   \n",
       "3                            43535                  26169                  1   \n",
       "4                            46726                  32020                1.1   \n",
       "\n",
       "Desc Stolen bikes Net increase bikes, abs Net increase bikes, %  \\\n",
       "0             NaN                     NaN                   NaN   \n",
       "1           96675                   17251               2.51093   \n",
       "2           78835                   13639               1.86311   \n",
       "3           68190                   17366               2.23002   \n",
       "4           66690                   14706                1.7755   \n",
       "\n",
       "Desc  Amount bikes AMS  \n",
       "0               847000  \n",
       "1               864251  \n",
       "2               877890  \n",
       "3               895256  \n",
       "4               909962  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding calculation of net increase in bikes in persentage\n",
    "cons_table['Net increase bikes, %2'] = cons_table['Net increase bikes, abs']/cons_table['Amount bikes AMS']*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Desc</th>\n",
       "      <th>index</th>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <th>Stolen bikes</th>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <th>Net increase bikes, %</th>\n",
       "      <th>Amount bikes AMS</th>\n",
       "      <th>Net increase bikes, %2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>847000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>687037</td>\n",
       "      <td>45020</td>\n",
       "      <td>6000</td>\n",
       "      <td>39020</td>\n",
       "      <td>21769</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96675</td>\n",
       "      <td>17251</td>\n",
       "      <td>2.51093</td>\n",
       "      <td>864251</td>\n",
       "      <td>1.99606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>732057</td>\n",
       "      <td>46682</td>\n",
       "      <td>6000</td>\n",
       "      <td>40682</td>\n",
       "      <td>27043</td>\n",
       "      <td>1.1</td>\n",
       "      <td>78835</td>\n",
       "      <td>13639</td>\n",
       "      <td>1.86311</td>\n",
       "      <td>877890</td>\n",
       "      <td>1.55361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018</td>\n",
       "      <td>778739</td>\n",
       "      <td>49535</td>\n",
       "      <td>6000</td>\n",
       "      <td>43535</td>\n",
       "      <td>26169</td>\n",
       "      <td>1</td>\n",
       "      <td>68190</td>\n",
       "      <td>17366</td>\n",
       "      <td>2.23002</td>\n",
       "      <td>895256</td>\n",
       "      <td>1.93978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019</td>\n",
       "      <td>828274</td>\n",
       "      <td>52726</td>\n",
       "      <td>6000</td>\n",
       "      <td>46726</td>\n",
       "      <td>32020</td>\n",
       "      <td>1.1</td>\n",
       "      <td>66690</td>\n",
       "      <td>14706</td>\n",
       "      <td>1.7755</td>\n",
       "      <td>909962</td>\n",
       "      <td>1.61611</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Desc index Total amount bikes AMS Purchased bikes AMS Fetched bikes AMS  \\\n",
       "0     2015                    NaN                 NaN               NaN   \n",
       "1     2016                 687037               45020              6000   \n",
       "2     2017                 732057               46682              6000   \n",
       "3     2018                 778739               49535              6000   \n",
       "4     2019                 828274               52726              6000   \n",
       "\n",
       "Desc Purchased - Fetched bikes AMS Conf.bikes left market Pct_Population_AMS  \\\n",
       "0                              NaN                    NaN                NaN   \n",
       "1                            39020                  21769                1.2   \n",
       "2                            40682                  27043                1.1   \n",
       "3                            43535                  26169                  1   \n",
       "4                            46726                  32020                1.1   \n",
       "\n",
       "Desc Stolen bikes Net increase bikes, abs Net increase bikes, %  \\\n",
       "0             NaN                     NaN                   NaN   \n",
       "1           96675                   17251               2.51093   \n",
       "2           78835                   13639               1.86311   \n",
       "3           68190                   17366               2.23002   \n",
       "4           66690                   14706                1.7755   \n",
       "\n",
       "Desc  Amount bikes AMS Net increase bikes, %2  \n",
       "0               847000                    NaN  \n",
       "1               864251                1.99606  \n",
       "2               877890                1.55361  \n",
       "3               895256                1.93978  \n",
       "4               909962                1.61611  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "cons_table = cons_table.drop([0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Desc</th>\n",
       "      <th>index</th>\n",
       "      <th>Total amount bikes AMS</th>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <th>Stolen bikes</th>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <th>Net increase bikes, %</th>\n",
       "      <th>Amount bikes AMS</th>\n",
       "      <th>Net increase bikes, %2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>687037</td>\n",
       "      <td>45020</td>\n",
       "      <td>6000</td>\n",
       "      <td>39020</td>\n",
       "      <td>21769</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96675</td>\n",
       "      <td>17251</td>\n",
       "      <td>2.51093</td>\n",
       "      <td>864251</td>\n",
       "      <td>1.99606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>732057</td>\n",
       "      <td>46682</td>\n",
       "      <td>6000</td>\n",
       "      <td>40682</td>\n",
       "      <td>27043</td>\n",
       "      <td>1.1</td>\n",
       "      <td>78835</td>\n",
       "      <td>13639</td>\n",
       "      <td>1.86311</td>\n",
       "      <td>877890</td>\n",
       "      <td>1.55361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018</td>\n",
       "      <td>778739</td>\n",
       "      <td>49535</td>\n",
       "      <td>6000</td>\n",
       "      <td>43535</td>\n",
       "      <td>26169</td>\n",
       "      <td>1</td>\n",
       "      <td>68190</td>\n",
       "      <td>17366</td>\n",
       "      <td>2.23002</td>\n",
       "      <td>895256</td>\n",
       "      <td>1.93978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019</td>\n",
       "      <td>828274</td>\n",
       "      <td>52726</td>\n",
       "      <td>6000</td>\n",
       "      <td>46726</td>\n",
       "      <td>32020</td>\n",
       "      <td>1.1</td>\n",
       "      <td>66690</td>\n",
       "      <td>14706</td>\n",
       "      <td>1.7755</td>\n",
       "      <td>909962</td>\n",
       "      <td>1.61611</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Desc index Total amount bikes AMS Purchased bikes AMS Fetched bikes AMS  \\\n",
       "1     2016                 687037               45020              6000   \n",
       "2     2017                 732057               46682              6000   \n",
       "3     2018                 778739               49535              6000   \n",
       "4     2019                 828274               52726              6000   \n",
       "\n",
       "Desc Purchased - Fetched bikes AMS Conf.bikes left market Pct_Population_AMS  \\\n",
       "1                            39020                  21769                1.2   \n",
       "2                            40682                  27043                1.1   \n",
       "3                            43535                  26169                  1   \n",
       "4                            46726                  32020                1.1   \n",
       "\n",
       "Desc Stolen bikes Net increase bikes, abs Net increase bikes, %  \\\n",
       "1           96675                   17251               2.51093   \n",
       "2           78835                   13639               1.86311   \n",
       "3           68190                   17366               2.23002   \n",
       "4           66690                   14706                1.7755   \n",
       "\n",
       "Desc  Amount bikes AMS Net increase bikes, %2  \n",
       "1               864251                1.99606  \n",
       "2               877890                1.55361  \n",
       "3               895256                1.93978  \n",
       "4               909962                1.61611  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "cons_table = cons_table.drop(['Total amount bikes AMS', 'Net increase bikes, %'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Desc</th>\n",
       "      <th>index</th>\n",
       "      <th>Purchased bikes AMS</th>\n",
       "      <th>Fetched bikes AMS</th>\n",
       "      <th>Purchased - Fetched bikes AMS</th>\n",
       "      <th>Conf.bikes left market</th>\n",
       "      <th>Pct_Population_AMS</th>\n",
       "      <th>Stolen bikes</th>\n",
       "      <th>Net increase bikes, abs</th>\n",
       "      <th>Amount bikes AMS</th>\n",
       "      <th>Net increase bikes, %2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>45020</td>\n",
       "      <td>6000</td>\n",
       "      <td>39020</td>\n",
       "      <td>21769</td>\n",
       "      <td>1.2</td>\n",
       "      <td>96675</td>\n",
       "      <td>17251</td>\n",
       "      <td>864251</td>\n",
       "      <td>1.99606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>46682</td>\n",
       "      <td>6000</td>\n",
       "      <td>40682</td>\n",
       "      <td>27043</td>\n",
       "      <td>1.1</td>\n",
       "      <td>78835</td>\n",
       "      <td>13639</td>\n",
       "      <td>877890</td>\n",
       "      <td>1.55361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018</td>\n",
       "      <td>49535</td>\n",
       "      <td>6000</td>\n",
       "      <td>43535</td>\n",
       "      <td>26169</td>\n",
       "      <td>1</td>\n",
       "      <td>68190</td>\n",
       "      <td>17366</td>\n",
       "      <td>895256</td>\n",
       "      <td>1.93978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019</td>\n",
       "      <td>52726</td>\n",
       "      <td>6000</td>\n",
       "      <td>46726</td>\n",
       "      <td>32020</td>\n",
       "      <td>1.1</td>\n",
       "      <td>66690</td>\n",
       "      <td>14706</td>\n",
       "      <td>909962</td>\n",
       "      <td>1.61611</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Desc index Purchased bikes AMS Fetched bikes AMS  \\\n",
       "1     2016               45020              6000   \n",
       "2     2017               46682              6000   \n",
       "3     2018               49535              6000   \n",
       "4     2019               52726              6000   \n",
       "\n",
       "Desc Purchased - Fetched bikes AMS Conf.bikes left market Pct_Population_AMS  \\\n",
       "1                            39020                  21769                1.2   \n",
       "2                            40682                  27043                1.1   \n",
       "3                            43535                  26169                  1   \n",
       "4                            46726                  32020                1.1   \n",
       "\n",
       "Desc Stolen bikes Net increase bikes, abs  Amount bikes AMS  \\\n",
       "1           96675                   17251            864251   \n",
       "2           78835                   13639            877890   \n",
       "3           68190                   17366            895256   \n",
       "4           66690                   14706            909962   \n",
       "\n",
       "Desc Net increase bikes, %2  \n",
       "1                   1.99606  \n",
       "2                   1.55361  \n",
       "3                   1.93978  \n",
       "4                   1.61611  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# excel = cons_table.transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cons_table.to_excel('output.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Desc\n",
       "index                            float64\n",
       "Purchased bikes AMS              float64\n",
       "Fetched bikes AMS                float64\n",
       "Purchased - Fetched bikes AMS    float64\n",
       "Conf.bikes left market           float64\n",
       "Pct_Population_AMS               float64\n",
       "Stolen bikes                     float64\n",
       "Net increase bikes, abs          float64\n",
       "Amount bikes AMS                 float64\n",
       "Net increase bikes, %2           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cons_table = cons_table.astype('float64')\n",
    "cons_table.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.10266558310476716"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr_coef = cons_table['Pct_Population_AMS'].corr(cons_table['Net increase bikes, %2'])\n",
    "corr_coef\n",
    "# There is a positive correlation between increase in bikes and in people but this correlation is not strong"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9016381366325774"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr_coef_p = cons_table['Purchased bikes AMS'].corr(cons_table['Conf.bikes left market'])\n",
    "corr_coef_p\n",
    "# There is a positive and strong correlation between confiscated bikes (left the market, i.e. demolished) and purchased bikes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "nan"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr_coef_f = cons_table['Purchased bikes AMS'].corr(cons_table['Fetched bikes AMS'])\n",
    "corr_coef_f"
   ]
  },
  {
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}