04x-join_and_aggregate_makedataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Innovating Journalism\n",
    "## Practical Python exercise Joining and Merging\n",
    "## EXTRA: How did we prepare the dataset?\n",
    "\n",
    "*Damian Trilling and Penny Sheets*\n",
    "\n",
    "This notebook outlines the retrieval and preprocessing steps we did to construct the files for our examples.\n",
    "Thus, this notebook contains the steps\n",
    "\n",
    "- (1) Retrieval\n",
    "- (2) Preprocessing,\n",
    "\n",
    "while the other notebook contains the steps\n",
    "\n",
    "- (3) Enrichment\n",
    "- (4) Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Population data\n",
    "\n",
    "Go to\n",
    "https://opendata.cbs.nl/statline/portal.html?_la=en&_catalog=CBS&tableId=37259eng&_theme=1066\n",
    "\n",
    "Select the following options:\n",
    "- Topics: Net migration *and* live born children (2/25)\n",
    "- Sex: only total (1/3)\n",
    "- Regions: all provinces (12/1225)\n",
    "\n",
    "This should give you 1392 rows and result in the file\n",
    "\n",
    "`37259eng_UntypedDataSet_20112018_132903.csv`\n",
    "\n",
    "Also download the metadata for later reference.\n",
    "\n",
    "### Economic data\n",
    "\n",
    "Go to\n",
    "https://opendata.cbs.nl/statline/portal.html?_la=en&_catalog=CBS&tableId=82800ENG&_theme=1064\n",
    "\n",
    "Select the following options:\n",
    "- Topics: GDP (volume change) (1/2)\n",
    "- Economic sectors: A-U all economic activities (1/15)\n",
    "- Regions: all provinces (12/77)\n",
    "\n",
    "This should give you 264 rows and result in the file\n",
    "\n",
    "`82800ENG_UntypedDataSet_20112018_133529.csv`\n",
    "\n",
    "Also download the metadata for later reference.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocessing the data\n",
    "### Population data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "population = pd.read_csv('37259eng_UntypedDataSet_20112018_132903.csv', delimiter=';')"
   ]
  },
  {
   "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></th>\n",
       "      <th>ID</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Regions</th>\n",
       "      <th>Periods</th>\n",
       "      <th>LiveBornChildren_2</th>\n",
       "      <th>NetMigrationExcludingAdministrative_19</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>290</td>\n",
       "      <td>T001038</td>\n",
       "      <td>PV20</td>\n",
       "      <td>1960JJ00</td>\n",
       "      <td>8868.0</td>\n",
       "      <td>-1748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>291</td>\n",
       "      <td>T001038</td>\n",
       "      <td>PV20</td>\n",
       "      <td>1961JJ00</td>\n",
       "      <td>9062.0</td>\n",
       "      <td>-1087.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>292</td>\n",
       "      <td>T001038</td>\n",
       "      <td>PV20</td>\n",
       "      <td>1962JJ00</td>\n",
       "      <td>9165.0</td>\n",
       "      <td>-623.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>293</td>\n",
       "      <td>T001038</td>\n",
       "      <td>PV20</td>\n",
       "      <td>1963JJ00</td>\n",
       "      <td>9518.0</td>\n",
       "      <td>547.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>294</td>\n",
       "      <td>T001038</td>\n",
       "      <td>PV20</td>\n",
       "      <td>1964JJ00</td>\n",
       "      <td>9671.0</td>\n",
       "      <td>305.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID      Sex Regions   Periods  LiveBornChildren_2  \\\n",
       "0  290  T001038  PV20    1960JJ00              8868.0   \n",
       "1  291  T001038  PV20    1961JJ00              9062.0   \n",
       "2  292  T001038  PV20    1962JJ00              9165.0   \n",
       "3  293  T001038  PV20    1963JJ00              9518.0   \n",
       "4  294  T001038  PV20    1964JJ00              9671.0   \n",
       "\n",
       "   NetMigrationExcludingAdministrative_19  \n",
       "0                                 -1748.0  \n",
       "1                                 -1087.0  \n",
       "2                                  -623.0  \n",
       "3                                   547.0  \n",
       "4                                   305.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We first remove all columns that are not necessary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "T001038    696\n",
       "Name: Sex, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population['Sex'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "population.drop(['Sex','ID'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Regions</th>\n",
       "      <th>Periods</th>\n",
       "      <th>LiveBornChildren_2</th>\n",
       "      <th>NetMigrationExcludingAdministrative_19</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1960JJ00</td>\n",
       "      <td>8868.0</td>\n",
       "      <td>-1748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1961JJ00</td>\n",
       "      <td>9062.0</td>\n",
       "      <td>-1087.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1962JJ00</td>\n",
       "      <td>9165.0</td>\n",
       "      <td>-623.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1963JJ00</td>\n",
       "      <td>9518.0</td>\n",
       "      <td>547.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1964JJ00</td>\n",
       "      <td>9671.0</td>\n",
       "      <td>305.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1965JJ00</td>\n",
       "      <td>9632.0</td>\n",
       "      <td>-148.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1966JJ00</td>\n",
       "      <td>9558.0</td>\n",
       "      <td>667.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1967JJ00</td>\n",
       "      <td>9707.0</td>\n",
       "      <td>-1617.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1968JJ00</td>\n",
       "      <td>9366.0</td>\n",
       "      <td>-2358.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1969JJ00</td>\n",
       "      <td>10088.0</td>\n",
       "      <td>-1952.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1970JJ00</td>\n",
       "      <td>9774.0</td>\n",
       "      <td>196.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1971JJ00</td>\n",
       "      <td>9219.0</td>\n",
       "      <td>-203.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1972JJ00</td>\n",
       "      <td>8895.0</td>\n",
       "      <td>-190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1973JJ00</td>\n",
       "      <td>7776.0</td>\n",
       "      <td>-660.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1974JJ00</td>\n",
       "      <td>7341.0</td>\n",
       "      <td>925.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1975JJ00</td>\n",
       "      <td>6952.0</td>\n",
       "      <td>1750.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1976JJ00</td>\n",
       "      <td>6862.0</td>\n",
       "      <td>2387.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1977JJ00</td>\n",
       "      <td>6624.0</td>\n",
       "      <td>1781.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1978JJ00</td>\n",
       "      <td>6716.0</td>\n",
       "      <td>1949.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1979JJ00</td>\n",
       "      <td>6644.0</td>\n",
       "      <td>823.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1980JJ00</td>\n",
       "      <td>6898.0</td>\n",
       "      <td>1345.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1981JJ00</td>\n",
       "      <td>6730.0</td>\n",
       "      <td>569.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1982JJ00</td>\n",
       "      <td>6648.0</td>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1983JJ00</td>\n",
       "      <td>6449.0</td>\n",
       "      <td>-612.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1984JJ00</td>\n",
       "      <td>6366.0</td>\n",
       "      <td>-1625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1985JJ00</td>\n",
       "      <td>6537.0</td>\n",
       "      <td>-2135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1986JJ00</td>\n",
       "      <td>6551.0</td>\n",
       "      <td>-2756.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1987JJ00</td>\n",
       "      <td>6515.0</td>\n",
       "      <td>-2720.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1988JJ00</td>\n",
       "      <td>6298.0</td>\n",
       "      <td>-2679.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>PV20</td>\n",
       "      <td>1989JJ00</td>\n",
       "      <td>6284.0</td>\n",
       "      <td>-2204.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>666</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1988JJ00</td>\n",
       "      <td>12979.0</td>\n",
       "      <td>505.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>667</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1989JJ00</td>\n",
       "      <td>12875.0</td>\n",
       "      <td>1027.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>668</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1990JJ00</td>\n",
       "      <td>13690.0</td>\n",
       "      <td>1906.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>669</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1991JJ00</td>\n",
       "      <td>13479.0</td>\n",
       "      <td>2191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>670</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1992JJ00</td>\n",
       "      <td>13029.0</td>\n",
       "      <td>1434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1993JJ00</td>\n",
       "      <td>12899.0</td>\n",
       "      <td>2810.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>672</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1994JJ00</td>\n",
       "      <td>12700.0</td>\n",
       "      <td>1919.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>673</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1995JJ00</td>\n",
       "      <td>12474.0</td>\n",
       "      <td>2086.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1996JJ00</td>\n",
       "      <td>12100.0</td>\n",
       "      <td>1476.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>675</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1997JJ00</td>\n",
       "      <td>11984.0</td>\n",
       "      <td>982.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>676</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1998JJ00</td>\n",
       "      <td>12445.0</td>\n",
       "      <td>411.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>677</th>\n",
       "      <td>PV31</td>\n",
       "      <td>1999JJ00</td>\n",
       "      <td>12213.0</td>\n",
       "      <td>1584.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>678</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2000JJ00</td>\n",
       "      <td>12235.0</td>\n",
       "      <td>615.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2001JJ00</td>\n",
       "      <td>11884.0</td>\n",
       "      <td>642.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2002JJ00</td>\n",
       "      <td>11484.0</td>\n",
       "      <td>-311.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>681</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2003JJ00</td>\n",
       "      <td>11190.0</td>\n",
       "      <td>-1579.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>682</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2004JJ00</td>\n",
       "      <td>10753.0</td>\n",
       "      <td>-1338.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2005JJ00</td>\n",
       "      <td>10051.0</td>\n",
       "      <td>-2831.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2006JJ00</td>\n",
       "      <td>9857.0</td>\n",
       "      <td>-2389.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>685</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2007JJ00</td>\n",
       "      <td>9458.0</td>\n",
       "      <td>-2099.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>686</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2008JJ00</td>\n",
       "      <td>9401.0</td>\n",
       "      <td>986.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2009JJ00</td>\n",
       "      <td>9741.0</td>\n",
       "      <td>1264.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2010JJ00</td>\n",
       "      <td>9620.0</td>\n",
       "      <td>2288.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>689</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2011JJ00</td>\n",
       "      <td>9288.0</td>\n",
       "      <td>2666.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>690</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2012JJ00</td>\n",
       "      <td>9109.0</td>\n",
       "      <td>1900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>691</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2013JJ00</td>\n",
       "      <td>9011.0</td>\n",
       "      <td>1255.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>692</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2014JJ00</td>\n",
       "      <td>8841.0</td>\n",
       "      <td>1176.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>693</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2015JJ00</td>\n",
       "      <td>8739.0</td>\n",
       "      <td>2627.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2016JJ00</td>\n",
       "      <td>8994.0</td>\n",
       "      <td>4882.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>695</th>\n",
       "      <td>PV31</td>\n",
       "      <td>2017JJ00</td>\n",
       "      <td>8821.0</td>\n",
       "      <td>3955.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>696 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Regions   Periods  LiveBornChildren_2  \\\n",
       "0    PV20    1960JJ00              8868.0   \n",
       "1    PV20    1961JJ00              9062.0   \n",
       "2    PV20    1962JJ00              9165.0   \n",
       "3    PV20    1963JJ00              9518.0   \n",
       "4    PV20    1964JJ00              9671.0   \n",
       "5    PV20    1965JJ00              9632.0   \n",
       "6    PV20    1966JJ00              9558.0   \n",
       "7    PV20    1967JJ00              9707.0   \n",
       "8    PV20    1968JJ00              9366.0   \n",
       "9    PV20    1969JJ00             10088.0   \n",
       "10   PV20    1970JJ00              9774.0   \n",
       "11   PV20    1971JJ00              9219.0   \n",
       "12   PV20    1972JJ00              8895.0   \n",
       "13   PV20    1973JJ00              7776.0   \n",
       "14   PV20    1974JJ00              7341.0   \n",
       "15   PV20    1975JJ00              6952.0   \n",
       "16   PV20    1976JJ00              6862.0   \n",
       "17   PV20    1977JJ00              6624.0   \n",
       "18   PV20    1978JJ00              6716.0   \n",
       "19   PV20    1979JJ00              6644.0   \n",
       "20   PV20    1980JJ00              6898.0   \n",
       "21   PV20    1981JJ00              6730.0   \n",
       "22   PV20    1982JJ00              6648.0   \n",
       "23   PV20    1983JJ00              6449.0   \n",
       "24   PV20    1984JJ00              6366.0   \n",
       "25   PV20    1985JJ00              6537.0   \n",
       "26   PV20    1986JJ00              6551.0   \n",
       "27   PV20    1987JJ00              6515.0   \n",
       "28   PV20    1988JJ00              6298.0   \n",
       "29   PV20    1989JJ00              6284.0   \n",
       "..      ...       ...                 ...   \n",
       "666  PV31    1988JJ00             12979.0   \n",
       "667  PV31    1989JJ00             12875.0   \n",
       "668  PV31    1990JJ00             13690.0   \n",
       "669  PV31    1991JJ00             13479.0   \n",
       "670  PV31    1992JJ00             13029.0   \n",
       "671  PV31    1993JJ00             12899.0   \n",
       "672  PV31    1994JJ00             12700.0   \n",
       "673  PV31    1995JJ00             12474.0   \n",
       "674  PV31    1996JJ00             12100.0   \n",
       "675  PV31    1997JJ00             11984.0   \n",
       "676  PV31    1998JJ00             12445.0   \n",
       "677  PV31    1999JJ00             12213.0   \n",
       "678  PV31    2000JJ00             12235.0   \n",
       "679  PV31    2001JJ00             11884.0   \n",
       "680  PV31    2002JJ00             11484.0   \n",
       "681  PV31    2003JJ00             11190.0   \n",
       "682  PV31    2004JJ00             10753.0   \n",
       "683  PV31    2005JJ00             10051.0   \n",
       "684  PV31    2006JJ00              9857.0   \n",
       "685  PV31    2007JJ00              9458.0   \n",
       "686  PV31    2008JJ00              9401.0   \n",
       "687  PV31    2009JJ00              9741.0   \n",
       "688  PV31    2010JJ00              9620.0   \n",
       "689  PV31    2011JJ00              9288.0   \n",
       "690  PV31    2012JJ00              9109.0   \n",
       "691  PV31    2013JJ00              9011.0   \n",
       "692  PV31    2014JJ00              8841.0   \n",
       "693  PV31    2015JJ00              8739.0   \n",
       "694  PV31    2016JJ00              8994.0   \n",
       "695  PV31    2017JJ00              8821.0   \n",
       "\n",
       "     NetMigrationExcludingAdministrative_19  \n",
       "0                                   -1748.0  \n",
       "1                                   -1087.0  \n",
       "2                                    -623.0  \n",
       "3                                     547.0  \n",
       "4                                     305.0  \n",
       "5                                    -148.0  \n",
       "6                                     667.0  \n",
       "7                                   -1617.0  \n",
       "8                                   -2358.0  \n",
       "9                                   -1952.0  \n",
       "10                                    196.0  \n",
       "11                                   -203.0  \n",
       "12                                   -190.0  \n",
       "13                                   -660.0  \n",
       "14                                    925.0  \n",
       "15                                   1750.0  \n",
       "16                                   2387.0  \n",
       "17                                   1781.0  \n",
       "18                                   1949.0  \n",
       "19                                    823.0  \n",
       "20                                   1345.0  \n",
       "21                                    569.0  \n",
       "22                                    102.0  \n",
       "23                                   -612.0  \n",
       "24                                  -1625.0  \n",
       "25                                  -2135.0  \n",
       "26                                  -2756.0  \n",
       "27                                  -2720.0  \n",
       "28                                  -2679.0  \n",
       "29                                  -2204.0  \n",
       "..                                      ...  \n",
       "666                                   505.0  \n",
       "667                                  1027.0  \n",
       "668                                  1906.0  \n",
       "669                                  2191.0  \n",
       "670                                  1434.0  \n",
       "671                                  2810.0  \n",
       "672                                  1919.0  \n",
       "673                                  2086.0  \n",
       "674                                  1476.0  \n",
       "675                                   982.0  \n",
       "676                                   411.0  \n",
       "677                                  1584.0  \n",
       "678                                   615.0  \n",
       "679                                   642.0  \n",
       "680                                  -311.0  \n",
       "681                                 -1579.0  \n",
       "682                                 -1338.0  \n",
       "683                                 -2831.0  \n",
       "684                                 -2389.0  \n",
       "685                                 -2099.0  \n",
       "686                                   986.0  \n",
       "687                                  1264.0  \n",
       "688                                  2288.0  \n",
       "689                                  2666.0  \n",
       "690                                  1900.0  \n",
       "691                                  1255.0  \n",
       "692                                  1176.0  \n",
       "693                                  2627.0  \n",
       "694                                  4882.0  \n",
       "695                                  3955.0  \n",
       "\n",
       "[696 rows x 4 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The values of the column Regions contain weird spaces at the end:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'PV20  '"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population.iloc[0,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to remove them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "population['Regions'] = population['Regions'].map(lambda x: x.strip())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By having a look at the metadata (using CTRL-F for looking for PV20), we can find out what the province codes actually mean. Let's recode that by using a dict to map the keys to more meaningful values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "provinces = {\"PV20\":\"Groningen\",\n",
    "\"PV21\":\"Friesland\",\n",
    "\"PV22\":\"Drenthe\",\n",
    "\"PV23\":\"Overijssel\",\n",
    "\"PV24\":\"Flevoland\",\n",
    "\"PV25\":\"Gelderland\",\n",
    "\"PV26\":\"Utrecht\",\n",
    "\"PV27\":\"Noord-Holland\",\n",
    "\"PV28\":\"Zuid-Holland\",\n",
    "\"PV29\":\"Zeeland\",\n",
    "\"PV30\":\"Noord-Brabant\",\n",
    "\"PV31\":\"Limburg\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "population['Regions'] = population['Regions'].map(provinces)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Regions</th>\n",
       "      <th>Periods</th>\n",
       "      <th>LiveBornChildren_2</th>\n",
       "      <th>NetMigrationExcludingAdministrative_19</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1960</td>\n",
       "      <td>8868.0</td>\n",
       "      <td>-1748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1961</td>\n",
       "      <td>9062.0</td>\n",
       "      <td>-1087.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1962</td>\n",
       "      <td>9165.0</td>\n",
       "      <td>-623.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1963</td>\n",
       "      <td>9518.0</td>\n",
       "      <td>547.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1964</td>\n",
       "      <td>9671.0</td>\n",
       "      <td>305.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Regions  Periods  LiveBornChildren_2  \\\n",
       "0  Groningen     1960              8868.0   \n",
       "1  Groningen     1961              9062.0   \n",
       "2  Groningen     1962              9165.0   \n",
       "3  Groningen     1963              9518.0   \n",
       "4  Groningen     1964              9671.0   \n",
       "\n",
       "   NetMigrationExcludingAdministrative_19  \n",
       "0                                 -1748.0  \n",
       "1                                 -1087.0  \n",
       "2                                  -623.0  \n",
       "3                                   547.0  \n",
       "4                                   305.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's also represent the Period in a better way. It's a string now, and only the first four digits are meaningful. Let's convert these to an integer. Alternatively, we could opt to convert it to a date (a so-called datetime object)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "population['Periods'] = population['Periods'].map(lambda x: int(x[:4]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Regions</th>\n",
       "      <th>Periods</th>\n",
       "      <th>LiveBornChildren_2</th>\n",
       "      <th>NetMigrationExcludingAdministrative_19</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1960</td>\n",
       "      <td>8868.0</td>\n",
       "      <td>-1748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1961</td>\n",
       "      <td>9062.0</td>\n",
       "      <td>-1087.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1962</td>\n",
       "      <td>9165.0</td>\n",
       "      <td>-623.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1963</td>\n",
       "      <td>9518.0</td>\n",
       "      <td>547.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1964</td>\n",
       "      <td>9671.0</td>\n",
       "      <td>305.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Regions  Periods  LiveBornChildren_2  \\\n",
       "0  Groningen     1960              8868.0   \n",
       "1  Groningen     1961              9062.0   \n",
       "2  Groningen     1962              9165.0   \n",
       "3  Groningen     1963              9518.0   \n",
       "4  Groningen     1964              9671.0   \n",
       "\n",
       "   NetMigrationExcludingAdministrative_19  \n",
       "0                                 -1748.0  \n",
       "1                                 -1087.0  \n",
       "2                                  -623.0  \n",
       "3                                   547.0  \n",
       "4                                   305.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's save this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "population.to_csv('population.csv')\n",
    "population.to_json('population.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Economic data\n",
    "\n",
    "We just do exactly the same for our economic dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "economy = pd.read_csv('82800ENG_UntypedDataSet_20112018_133529.csv', delimiter=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>ID</th>\n",
       "      <th>EconomicSectorsSIC2008</th>\n",
       "      <th>Regions</th>\n",
       "      <th>Periods</th>\n",
       "      <th>GDPVolumeChanges_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>132</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>1996JJ00</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>133</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>1997JJ00</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>134</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>1998JJ00</td>\n",
       "      <td>-0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>135</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>1999JJ00</td>\n",
       "      <td>-0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>136</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2000JJ00</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>137</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2001JJ00</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>138</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2002JJ00</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>139</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2003JJ00</td>\n",
       "      <td>2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>140</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2004JJ00</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>141</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2005JJ00</td>\n",
       "      <td>-0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>142</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2006JJ00</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>143</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2007JJ00</td>\n",
       "      <td>-2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>144</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2008JJ00</td>\n",
       "      <td>8.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>145</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2009JJ00</td>\n",
       "      <td>-4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>146</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2010JJ00</td>\n",
       "      <td>10.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>147</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2011JJ00</td>\n",
       "      <td>-2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>148</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2012JJ00</td>\n",
       "      <td>-0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>149</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2013JJ00</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>150</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2014JJ00</td>\n",
       "      <td>-7.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>151</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2015JJ00</td>\n",
       "      <td>-8.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>152</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2016JJ00</td>\n",
       "      <td>-1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>153</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV20</td>\n",
       "      <td>2017JJ00</td>\n",
       "      <td>-0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>154</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV21</td>\n",
       "      <td>1996JJ00</td>\n",
       "      <td>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>155</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV21</td>\n",
       "      <td>1997JJ00</td>\n",
       "      <td>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>156</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV21</td>\n",
       "      <td>1998JJ00</td>\n",
       "      <td>6.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>157</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV21</td>\n",
       "      <td>1999JJ00</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>158</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV21</td>\n",
       "      <td>2000JJ00</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>159</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV21</td>\n",
       "      <td>2001JJ00</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>160</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV21</td>\n",
       "      <td>2002JJ00</td>\n",
       "      <td>-1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>161</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV21</td>\n",
       "      <td>2003JJ00</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>366</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV30</td>\n",
       "      <td>2010JJ00</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>367</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV30</td>\n",
       "      <td>2011JJ00</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>236</th>\n",
       "      <td>368</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV30</td>\n",
       "      <td>2012JJ00</td>\n",
       "      <td>-0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>369</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV30</td>\n",
       "      <td>2013JJ00</td>\n",
       "      <td>-0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>370</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV30</td>\n",
       "      <td>2014JJ00</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>371</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV30</td>\n",
       "      <td>2015JJ00</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240</th>\n",
       "      <td>372</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV30</td>\n",
       "      <td>2016JJ00</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>373</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV30</td>\n",
       "      <td>2017JJ00</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>374</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>1996JJ00</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>375</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>1997JJ00</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>376</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>1998JJ00</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>377</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>1999JJ00</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>378</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2000JJ00</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>379</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2001JJ00</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>380</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2002JJ00</td>\n",
       "      <td>0.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>381</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2003JJ00</td>\n",
       "      <td>-0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>382</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2004JJ00</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>383</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2005JJ00</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>384</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2006JJ00</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>385</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2007JJ00</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>386</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2008JJ00</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>387</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2009JJ00</td>\n",
       "      <td>-5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>388</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2010JJ00</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>389</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2011JJ00</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>390</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2012JJ00</td>\n",
       "      <td>-1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>391</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2013JJ00</td>\n",
       "      <td>-0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260</th>\n",
       "      <td>392</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2014JJ00</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>393</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2015JJ00</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>394</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2016JJ00</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>395</td>\n",
       "      <td>T001081</td>\n",
       "      <td>PV31</td>\n",
       "      <td>2017JJ00</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>264 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID EconomicSectorsSIC2008   Regions   Periods  GDPVolumeChanges_1\n",
       "0    132             T001081     PV20      1996JJ00                 9.3\n",
       "1    133             T001081     PV20      1997JJ00                -2.0\n",
       "2    134             T001081     PV20      1998JJ00                -0.9\n",
       "3    135             T001081     PV20      1999JJ00                -0.7\n",
       "4    136             T001081     PV20      2000JJ00                 1.5\n",
       "5    137             T001081     PV20      2001JJ00                 3.9\n",
       "6    138             T001081     PV20      2002JJ00                 2.1\n",
       "7    139             T001081     PV20      2003JJ00                 2.3\n",
       "8    140             T001081     PV20      2004JJ00                 2.2\n",
       "9    141             T001081     PV20      2005JJ00                -0.7\n",
       "10   142             T001081     PV20      2006JJ00                 1.1\n",
       "11   143             T001081     PV20      2007JJ00                -2.1\n",
       "12   144             T001081     PV20      2008JJ00                 8.7\n",
       "13   145             T001081     PV20      2009JJ00                -4.2\n",
       "14   146             T001081     PV20      2010JJ00                10.4\n",
       "15   147             T001081     PV20      2011JJ00                -2.9\n",
       "16   148             T001081     PV20      2012JJ00                -0.5\n",
       "17   149             T001081     PV20      2013JJ00                 5.7\n",
       "18   150             T001081     PV20      2014JJ00                -7.6\n",
       "19   151             T001081     PV20      2015JJ00                -8.3\n",
       "20   152             T001081     PV20      2016JJ00                -1.8\n",
       "21   153             T001081     PV20      2017JJ00                -0.6\n",
       "22   154             T001081     PV21      1996JJ00                 2.6\n",
       "23   155             T001081     PV21      1997JJ00                 2.6\n",
       "24   156             T001081     PV21      1998JJ00                 6.8\n",
       "25   157             T001081     PV21      1999JJ00                 3.0\n",
       "26   158             T001081     PV21      2000JJ00                 3.4\n",
       "27   159             T001081     PV21      2001JJ00                 2.5\n",
       "28   160             T001081     PV21      2002JJ00                -1.9\n",
       "29   161             T001081     PV21      2003JJ00                 1.1\n",
       "..   ...                    ...       ...       ...                 ...\n",
       "234  366             T001081     PV30      2010JJ00                 3.3\n",
       "235  367             T001081     PV30      2011JJ00                 3.5\n",
       "236  368             T001081     PV30      2012JJ00                -0.6\n",
       "237  369             T001081     PV30      2013JJ00                -0.9\n",
       "238  370             T001081     PV30      2014JJ00                 1.9\n",
       "239  371             T001081     PV30      2015JJ00                 3.4\n",
       "240  372             T001081     PV30      2016JJ00                 2.5\n",
       "241  373             T001081     PV30      2017JJ00                 3.7\n",
       "242  374             T001081     PV31      1996JJ00                 2.5\n",
       "243  375             T001081     PV31      1997JJ00                 5.8\n",
       "244  376             T001081     PV31      1998JJ00                 4.9\n",
       "245  377             T001081     PV31      1999JJ00                 5.5\n",
       "246  378             T001081     PV31      2000JJ00                 2.7\n",
       "247  379             T001081     PV31      2001JJ00                 1.6\n",
       "248  380             T001081     PV31      2002JJ00                 0.4\n",
       "249  381             T001081     PV31      2003JJ00                -0.8\n",
       "250  382             T001081     PV31      2004JJ00                 2.8\n",
       "251  383             T001081     PV31      2005JJ00                 0.3\n",
       "252  384             T001081     PV31      2006JJ00                 2.9\n",
       "253  385             T001081     PV31      2007JJ00                 4.5\n",
       "254  386             T001081     PV31      2008JJ00                 0.3\n",
       "255  387             T001081     PV31      2009JJ00                -5.3\n",
       "256  388             T001081     PV31      2010JJ00                 2.2\n",
       "257  389             T001081     PV31      2011JJ00                 2.2\n",
       "258  390             T001081     PV31      2012JJ00                -1.5\n",
       "259  391             T001081     PV31      2013JJ00                -0.7\n",
       "260  392             T001081     PV31      2014JJ00                 0.2\n",
       "261  393             T001081     PV31      2015JJ00                 2.7\n",
       "262  394             T001081     PV31      2016JJ00                 2.2\n",
       "263  395             T001081     PV31      2017JJ00                 3.2\n",
       "\n",
       "[264 rows x 5 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "economy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "T001081       264\n",
       "Name: EconomicSectorsSIC2008, dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We only downloaded the total, so we can safely delete:\n",
    "economy['EconomicSectorsSIC2008'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "economy.drop(['EconomicSectorsSIC2008','ID'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "economy['Regions'] = economy['Regions'].map(lambda x: x.strip())\n",
    "economy['Regions'] = economy['Regions'].map(provinces)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "economy['Periods'] = economy['Periods'].map(lambda x: int(x[:4]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>Regions</th>\n",
       "      <th>Periods</th>\n",
       "      <th>GDPVolumeChanges_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1996</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1997</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1998</td>\n",
       "      <td>-0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>1999</td>\n",
       "      <td>-0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Groningen</td>\n",
       "      <td>2000</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Regions  Periods  GDPVolumeChanges_1\n",
       "0  Groningen     1996                 9.3\n",
       "1  Groningen     1997                -2.0\n",
       "2  Groningen     1998                -0.9\n",
       "3  Groningen     1999                -0.7\n",
       "4  Groningen     2000                 1.5"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "economy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "economy.to_csv('economy.csv')\n",
    "economy.to_json('economy.json')"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}