lab-db/ADM Lab 3 - Database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1 - Pandas and Data Engineering\n",
    "\n",
    "As in the previous two labs we work with the data provided by the [Open Data portal of the City of Rome](http://dati.comune.roma.it/). We use the dataset related to registered hosting structures, that is, hotels, hostels, B&B, etc., that were active during January 2019. For information on how to import the data from these datasets into a data structure that will support further studies, check out the [ADM Introductory Laboratory](../lab-intro/ADM%20Lab%201%20-%20Intro.ipynb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2019 = pd.read_csv(\"../data/opendata_suar_gennaio.csv\",\n",
    "                          sep=',', delimiter = None,\n",
    "                          header='infer', names=None,\n",
    "                          index_col = None, usecols = None,\n",
    "                          encoding = \"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Insegna', 'Classificazione', 'Indirizzo', 'Municipio', 'Tipologia',\n",
       "       'Singole', 'Doppie', 'Triple', 'Quadruple', 'Quintuple', 'Sestuple',\n",
       "       'Unitaâ Abitative', 'Posti Letto Unitaâ Abitative', 'Sito Web',\n",
       "       'Email', 'Telefono', 'Fax', 'Cellulare', 'Contatto Facebook',\n",
       "       'Contatto Twitter', 'Contatto Instagram', 'Contatto Altro Social',\n",
       "       'Unnamed: 22', 'Unnamed: 23', 'Unnamed: 24'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2019.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Insegna</th>\n",
       "      <th>Classificazione</th>\n",
       "      <th>Indirizzo</th>\n",
       "      <th>Municipio</th>\n",
       "      <th>Tipologia</th>\n",
       "      <th>Singole</th>\n",
       "      <th>Doppie</th>\n",
       "      <th>Triple</th>\n",
       "      <th>Quadruple</th>\n",
       "      <th>Quintuple</th>\n",
       "      <th>...</th>\n",
       "      <th>Telefono</th>\n",
       "      <th>Fax</th>\n",
       "      <th>Cellulare</th>\n",
       "      <th>Contatto Facebook</th>\n",
       "      <th>Contatto Twitter</th>\n",
       "      <th>Contatto Instagram</th>\n",
       "      <th>Contatto Altro Social</th>\n",
       "      <th>Unnamed: 22</th>\n",
       "      <th>Unnamed: 23</th>\n",
       "      <th>Unnamed: 24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Casa e Appartamento per Vacanze \"Trastevere_ho...</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>VIA FEDERICO ROSAZZA</td>\n",
       "      <td>52</td>\n",
       "      <td>Mun. XII</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Casa e Appartamento per Vacanze \"VIVES 63\"</td>\n",
       "      <td>Categoria 1</td>\n",
       "      <td>VIA DI MONTE VERDE</td>\n",
       "      <td>63</td>\n",
       "      <td>Mun. XII</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE \"LA CASA DI FA...</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>VIA AUGUSTO AUBRY</td>\n",
       "      <td>1</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>casa e appartamento per vacanze Valentina's house</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>VIALE GIULIO CESARE</td>\n",
       "      <td>51/A</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OLD CITY TESTACCIO CASA E APPARTAMENTO PER VAC...</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>VIA ORAZIO ANTINORI</td>\n",
       "      <td>2</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Insegna Classificazione  \\\n",
       "0  Casa e Appartamento per Vacanze \"Trastevere_ho...     Categoria 2   \n",
       "1         Casa e Appartamento per Vacanze \"VIVES 63\"     Categoria 1   \n",
       "2  CASA E APPARTAMENTO PER VACANZE \"LA CASA DI FA...     Categoria 2   \n",
       "3  casa e appartamento per vacanze Valentina's house     Categoria 2   \n",
       "4  OLD CITY TESTACCIO CASA E APPARTAMENTO PER VAC...     Categoria 2   \n",
       "\n",
       "              Indirizzo Municipio  Tipologia  \\\n",
       "0  VIA FEDERICO ROSAZZA        52  Mun. XII    \n",
       "1    VIA DI MONTE VERDE        63  Mun. XII    \n",
       "2     VIA AUGUSTO AUBRY         1    Mun. I    \n",
       "3   VIALE GIULIO CESARE      51/A    Mun. I    \n",
       "4   VIA ORAZIO ANTINORI         2    Mun. I    \n",
       "\n",
       "                                Singole Doppie Triple Quadruple  Quintuple  \\\n",
       "0  Casa Vacanze NON impr (Appartamento)      1    NaN       NaN        NaN   \n",
       "1  Casa Vacanze NON impr (Appartamento)    NaN      1       NaN        NaN   \n",
       "2  Casa Vacanze NON impr (Appartamento)      1    NaN       NaN        NaN   \n",
       "3  Casa Vacanze NON impr (Appartamento)    NaN      2       NaN        NaN   \n",
       "4  Casa Vacanze NON impr (Appartamento)      1      1       NaN        NaN   \n",
       "\n",
       "      ...      Telefono  Fax  Cellulare  Contatto Facebook Contatto Twitter  \\\n",
       "0     ...           NaN  NaN        NaN                NaN              NaN   \n",
       "1     ...           NaN  NaN        NaN                NaN              NaN   \n",
       "2     ...           NaN  NaN        NaN                NaN              NaN   \n",
       "3     ...           NaN  NaN        NaN                NaN              NaN   \n",
       "4     ...           NaN  NaN        NaN                NaN              NaN   \n",
       "\n",
       "  Contatto Instagram Contatto Altro Social  Unnamed: 22  Unnamed: 23  \\\n",
       "0                NaN                   NaN          NaN          NaN   \n",
       "1                NaN                   NaN          NaN          NaN   \n",
       "2                NaN                   NaN          NaN          NaN   \n",
       "3                NaN                   NaN          NaN          NaN   \n",
       "4                NaN                   NaN          NaN          NaN   \n",
       "\n",
       "  Unnamed: 24  \n",
       "0         NaN  \n",
       "1         NaN  \n",
       "2         NaN  \n",
       "3         NaN  \n",
       "4         NaN  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2019.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the column Municipio again holds the street number and not the actual region of Rome. The type of the structure has been moved to the column Singole, and most probably all values are shifted one column towards left. We will try to fix the data and rename the columns with more simple names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2019['Address'] = dataset2019['Indirizzo'] + \" \" + dataset2019['Municipio']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2019.rename(columns={'Tipologia':'Region', \n",
    "                            'Singole':'Type',\n",
    "                            'Doppie':'One-Bed-Rooms',\n",
    "                            'Triple':'Two-Beds-Rooms', \n",
    "                            'Quadruple':'Three-Beds-Rooms', \n",
    "                            'Quintuple':'Four-Beds-Rooms', \n",
    "                            'Sestuple':'Five-Beds-Rooms',\n",
    "                            'Unitaâ Abitative':'Total-Beds'}, \n",
    "                   inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Insegna', 'Classificazione', 'Indirizzo', 'Municipio', 'Region',\n",
       "       'Type', 'One-Bed-Rooms', 'Two-Beds-Rooms', 'Three-Beds-Rooms',\n",
       "       'Four-Beds-Rooms', 'Five-Beds-Rooms', 'Total-Beds',\n",
       "       'Posti Letto Unitaâ Abitative', 'Sito Web', 'Email', 'Telefono',\n",
       "       'Fax', 'Cellulare', 'Contatto Facebook', 'Contatto Twitter',\n",
       "       'Contatto Instagram', 'Contatto Altro Social', 'Unnamed: 22',\n",
       "       'Unnamed: 23', 'Unnamed: 24', 'Address'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2019.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will drop the columns that do not contain any useful information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2019.drop(columns=['Indirizzo', 'Municipio', 'Posti Letto Unitaâ Abitative', \n",
    "                          'Sito Web', 'Email', 'Telefono', 'Fax', 'Cellulare', \n",
    "                          'Contatto Facebook', 'Contatto Twitter', 'Contatto Instagram', \n",
    "                          'Contatto Altro Social', 'Unnamed: 22', 'Unnamed: 23', 'Unnamed: 24'],\n",
    "                inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can rename the _Tipologia_ column into _Region_ so that to be clear about the content of the data."
   ]
  },
  {
   "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>Insegna</th>\n",
       "      <th>Classificazione</th>\n",
       "      <th>Region</th>\n",
       "      <th>Type</th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>Five-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "      <th>Address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Casa e Appartamento per Vacanze \"Trastevere_ho...</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>Mun. XII</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>VIA FEDERICO ROSAZZA 52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Casa e Appartamento per Vacanze \"VIVES 63\"</td>\n",
       "      <td>Categoria 1</td>\n",
       "      <td>Mun. XII</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>VIA DI MONTE VERDE 63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE \"LA CASA DI FA...</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>VIA AUGUSTO AUBRY 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>casa e appartamento per vacanze Valentina's house</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>VIALE GIULIO CESARE  51/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>OLD CITY TESTACCIO CASA E APPARTAMENTO PER VAC...</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>VIA ORAZIO ANTINORI 2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Insegna Classificazione  \\\n",
       "0  Casa e Appartamento per Vacanze \"Trastevere_ho...     Categoria 2   \n",
       "1         Casa e Appartamento per Vacanze \"VIVES 63\"     Categoria 1   \n",
       "2  CASA E APPARTAMENTO PER VACANZE \"LA CASA DI FA...     Categoria 2   \n",
       "3  casa e appartamento per vacanze Valentina's house     Categoria 2   \n",
       "4  OLD CITY TESTACCIO CASA E APPARTAMENTO PER VAC...     Categoria 2   \n",
       "\n",
       "      Region                                  Type One-Bed-Rooms  \\\n",
       "0  Mun. XII   Casa Vacanze NON impr (Appartamento)             1   \n",
       "1  Mun. XII   Casa Vacanze NON impr (Appartamento)           NaN   \n",
       "2    Mun. I   Casa Vacanze NON impr (Appartamento)             1   \n",
       "3    Mun. I   Casa Vacanze NON impr (Appartamento)           NaN   \n",
       "4    Mun. I   Casa Vacanze NON impr (Appartamento)             1   \n",
       "\n",
       "  Two-Beds-Rooms Three-Beds-Rooms  Four-Beds-Rooms  Five-Beds-Rooms  \\\n",
       "0            NaN              NaN              NaN              NaN   \n",
       "1              1              NaN              NaN              NaN   \n",
       "2            NaN              NaN              NaN              NaN   \n",
       "3              2              NaN              NaN              NaN   \n",
       "4              1              NaN              NaN              NaN   \n",
       "\n",
       "   Total-Beds                    Address  \n",
       "0         1.0    VIA FEDERICO ROSAZZA 52  \n",
       "1         2.0      VIA DI MONTE VERDE 63  \n",
       "2         1.0        VIA AUGUSTO AUBRY 1  \n",
       "3         4.0  VIALE GIULIO CESARE  51/A  \n",
       "4         3.0      VIA ORAZIO ANTINORI 2  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2019.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Like in the previous labs, we will convert the 5 columns (Doppie, Triple, Quadruple, Quintuple, Sestuple) into numbers. In the process we will delete the entries that have misplaced values. These can be indetified by the entries in the _Region_ column that contain wrong values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Mun. XII ', 'Mun. I ', 'Mun. VII ', 'Mun. VIII ', 'Mun. II ',\n",
       "       'Mun. XIII ', 'Mun. VI ', 'Mun. III ', 'Mun. XV ', 'Mun. V ',\n",
       "       'Mun. XIV ', 'Mun. IV ', 'Mun. IX ', 'Mun. X ', 'Mun. XI ', ' 1/D',\n",
       "       '16', '56', '71', '49', '70', '45', '76', '171', '44', '21',\n",
       "       'VIA DI COLLE PIZZUTO', '1', 'Categoria 2', '3'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2019.Region.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the above list, the following entries are not correct: \n",
    "' 1/D', '16', '56', '71', '49', '70', '45', '76', '171', '44', '21', 'VIA DI COLLE PIZZUTO', '1', 'Categoria 2', '3'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "dropRows = [' 1/D', '16', '56', '71', '49', '70', '45', '76', '171', '44', '21', \n",
    "            'VIA DI COLLE PIZZUTO', '1', 'Categoria 2', '3']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Pandas most of the times there exist multiple ways to carry out a certain task. Here we wish to delete all rows that much one of the above multiple values under the _Region_ column. Here is a post in [StackOverflow that explains various way to do this](https://stackoverflow.com/questions/11350770/select-by-partial-string-from-a-pandas-dataframe). "
   ]
  },
  {
   "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></th>\n",
       "      <th>Insegna</th>\n",
       "      <th>Classificazione</th>\n",
       "      <th>Region</th>\n",
       "      <th>Type</th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>Five-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "      <th>Address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1059</th>\n",
       "      <td>VIA DEL PORTICO D'OTTAVIA</td>\n",
       "      <td>IL CUORE DI ROMA</td>\n",
       "      <td>1/D</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Categoria 2 VIA DEL PORTICO D'OTTAVIA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1066</th>\n",
       "      <td>VIA ARENULA</td>\n",
       "      <td>ROMA ANTICA</td>\n",
       "      <td>16</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Categoria 2 VIA ARENULA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1350</th>\n",
       "      <td>MARIA</td>\n",
       "      <td>L'ATTICO AL VATICANO</td>\n",
       "      <td>56</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Categoria 3 VIA ANGELO EMO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1405</th>\n",
       "      <td>CASA BRUNETTI</td>\n",
       "      <td>PIAZZA DEL POPOLO</td>\n",
       "      <td>56</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Categoria 2 VIA ANGELO BRUNETTI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1492</th>\n",
       "      <td>VIA LEONINA</td>\n",
       "      <td>COLOSSEO</td>\n",
       "      <td>71</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Categoria 2 VIA LEONINA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1549</th>\n",
       "      <td>LA CASA DELL'ELEFANTE</td>\n",
       "      <td>CASA VACANZE</td>\n",
       "      <td>49</td>\n",
       "      <td>Mun. V</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Categoria 2 VIA DELLE ROSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2740</th>\n",
       "      <td>GIANICOLO</td>\n",
       "      <td>ARTIST'S APARTMENT</td>\n",
       "      <td>70</td>\n",
       "      <td>Mun. XII</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Categoria 1 VIALE DI VILLA PAMPHILI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4030</th>\n",
       "      <td>MARMELLATA SUITE</td>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE</td>\n",
       "      <td>45</td>\n",
       "      <td>Mun. VII</td>\n",
       "      <td>Casa Vacanze impr (Appartamento)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Categoria 1 VIA APPIA NUOVA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4567</th>\n",
       "      <td>WEMBACHER PALACE FONTANA DI TREVI KING</td>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE</td>\n",
       "      <td>76</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Categoria 2 VICOLO SCAVOLINO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7270</th>\n",
       "      <td>QUE SERA</td>\n",
       "      <td>SERA...</td>\n",
       "      <td>171</td>\n",
       "      <td>Mun. XIII</td>\n",
       "      <td>Bed &amp; Breakfast non imprenditoriale</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Unica VIA CARDINALE GARAMPI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8170</th>\n",
       "      <td>Audrey's Roman Holidays - Rome Suite &amp; Rooms</td>\n",
       "      <td>Guest House o Affittacamere</td>\n",
       "      <td>44</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Guest House o Affittacamere</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Categoria 2 VIA CAVOUR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8713</th>\n",
       "      <td>GUEST HOUSE O AFFITTACAMERE STRANAMORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21</td>\n",
       "      <td>Mun. VII</td>\n",
       "      <td>Guest House o Affittacamere</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Categoria 2 VIA DI COLLE PIZZUTO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8762</th>\n",
       "      <td>GUEST HOUSE O AFFITTACAMERE STRANOAMORE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VIA DI COLLE PIZZUTO</td>\n",
       "      <td>21</td>\n",
       "      <td>Mun. VII</td>\n",
       "      <td>Guest House o Affittacamere</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8954</th>\n",
       "      <td>GUEST HOUSE O AFFITTACAMERE</td>\n",
       "      <td>PANNONIA SMART HOUSE DI VALERIA GARACCI</td>\n",
       "      <td>1</td>\n",
       "      <td>Mun. VII</td>\n",
       "      <td>Guest House o Affittacamere</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Categoria 3 LARGO PANNONIA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9712</th>\n",
       "      <td>LA FONTANA NEL CORTILE</td>\n",
       "      <td>GUEST HOUSE</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>VIA ETRURIA</td>\n",
       "      <td>44</td>\n",
       "      <td>Mun. VII</td>\n",
       "      <td>Guest House o Affittacamere</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AFFITTACAMERE  ROMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11573</th>\n",
       "      <td>Le Grand Hotel - St Regis Grand</td>\n",
       "      <td>Rome</td>\n",
       "      <td>3</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Albergo</td>\n",
       "      <td>0</td>\n",
       "      <td>157</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>5 Stelle VIA VITTORIO EMANUELE ORLANDO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Insegna  \\\n",
       "1059                      VIA DEL PORTICO D'OTTAVIA   \n",
       "1066                                    VIA ARENULA   \n",
       "1350                                          MARIA   \n",
       "1405                                  CASA BRUNETTI   \n",
       "1492                                    VIA LEONINA   \n",
       "1549                          LA CASA DELL'ELEFANTE   \n",
       "2740                                      GIANICOLO   \n",
       "4030                               MARMELLATA SUITE   \n",
       "4567         WEMBACHER PALACE FONTANA DI TREVI KING   \n",
       "7270                                       QUE SERA   \n",
       "8170   Audrey's Roman Holidays - Rome Suite & Rooms   \n",
       "8713        GUEST HOUSE O AFFITTACAMERE STRANAMORE    \n",
       "8762        GUEST HOUSE O AFFITTACAMERE STRANOAMORE   \n",
       "8954                    GUEST HOUSE O AFFITTACAMERE   \n",
       "9712                         LA FONTANA NEL CORTILE   \n",
       "11573               Le Grand Hotel - St Regis Grand   \n",
       "\n",
       "                                Classificazione                Region  \\\n",
       "1059                           IL CUORE DI ROMA                   1/D   \n",
       "1066                                ROMA ANTICA                    16   \n",
       "1350                       L'ATTICO AL VATICANO                    56   \n",
       "1405                          PIAZZA DEL POPOLO                    56   \n",
       "1492                                   COLOSSEO                    71   \n",
       "1549                               CASA VACANZE                    49   \n",
       "2740                         ARTIST'S APARTMENT                    70   \n",
       "4030            CASA E APPARTAMENTO PER VACANZE                    45   \n",
       "4567           CASA E APPARTAMENTO PER VACANZE                     76   \n",
       "7270                                    SERA...                   171   \n",
       "8170                Guest House o Affittacamere                    44   \n",
       "8713                                        NaN                    21   \n",
       "8762                                        NaN  VIA DI COLLE PIZZUTO   \n",
       "8954    PANNONIA SMART HOUSE DI VALERIA GARACCI                     1   \n",
       "9712                                GUEST HOUSE           Categoria 2   \n",
       "11573                                      Rome                     3   \n",
       "\n",
       "              Type                         One-Bed-Rooms  \\\n",
       "1059       Mun. I   Casa Vacanze NON impr (Appartamento)   \n",
       "1066       Mun. I   Casa Vacanze NON impr (Appartamento)   \n",
       "1350       Mun. I   Casa Vacanze NON impr (Appartamento)   \n",
       "1405       Mun. I   Casa Vacanze NON impr (Appartamento)   \n",
       "1492       Mun. I   Casa Vacanze NON impr (Appartamento)   \n",
       "1549       Mun. V   Casa Vacanze NON impr (Appartamento)   \n",
       "2740     Mun. XII   Casa Vacanze NON impr (Appartamento)   \n",
       "4030     Mun. VII       Casa Vacanze impr (Appartamento)   \n",
       "4567       Mun. I       Casa Vacanze impr (Appartamento)   \n",
       "7270    Mun. XIII    Bed & Breakfast non imprenditoriale   \n",
       "8170       Mun. I            Guest House o Affittacamere   \n",
       "8713     Mun. VII            Guest House o Affittacamere   \n",
       "8762            21                             Mun. VII    \n",
       "8954     Mun. VII            Guest House o Affittacamere   \n",
       "9712   VIA ETRURIA                                    44   \n",
       "11573      Mun. I                                Albergo   \n",
       "\n",
       "                    Two-Beds-Rooms             Three-Beds-Rooms  \\\n",
       "1059                             1                          NaN   \n",
       "1066                             1                          NaN   \n",
       "1350                             0                            1   \n",
       "1405                             0                            0   \n",
       "1492                             1                            0   \n",
       "1549                             0                            1   \n",
       "2740                             0                            1   \n",
       "4030                             1                            1   \n",
       "4567                           NaN                            1   \n",
       "7270                             0                            2   \n",
       "8170                             0                            2   \n",
       "8713                             4                          NaN   \n",
       "8762   Guest House o Affittacamere                            4   \n",
       "8954                             0                            2   \n",
       "9712                     Mun. VII   Guest House o Affittacamere   \n",
       "11573                            0                          157   \n",
       "\n",
       "       Four-Beds-Rooms  Five-Beds-Rooms  Total-Beds  \\\n",
       "1059               NaN              NaN         1.0   \n",
       "1066               NaN              NaN         1.0   \n",
       "1350               0.0              0.0         1.0   \n",
       "1405               0.0              0.0         0.0   \n",
       "1492               0.0              0.0         1.0   \n",
       "1549               0.0              0.0         1.0   \n",
       "2740               0.0              0.0         1.0   \n",
       "4030               NaN              NaN         2.0   \n",
       "4567               NaN              NaN         1.0   \n",
       "7270               0.0              0.0         2.0   \n",
       "8170               0.0              1.0         3.0   \n",
       "8713               NaN              NaN         4.0   \n",
       "8762               2.0              NaN         NaN   \n",
       "8954               1.0              0.0         3.0   \n",
       "9712               NaN              3.0         NaN   \n",
       "11573              4.0              0.0       161.0   \n",
       "\n",
       "                                      Address  \n",
       "1059    Categoria 2 VIA DEL PORTICO D'OTTAVIA  \n",
       "1066                  Categoria 2 VIA ARENULA  \n",
       "1350               Categoria 3 VIA ANGELO EMO  \n",
       "1405          Categoria 2 VIA ANGELO BRUNETTI  \n",
       "1492                  Categoria 2 VIA LEONINA  \n",
       "1549               Categoria 2 VIA DELLE ROSE  \n",
       "2740      Categoria 1 VIALE DI VILLA PAMPHILI  \n",
       "4030              Categoria 1 VIA APPIA NUOVA  \n",
       "4567             Categoria 2 VICOLO SCAVOLINO  \n",
       "7270              Unica VIA CARDINALE GARAMPI  \n",
       "8170                   Categoria 2 VIA CAVOUR  \n",
       "8713         Categoria 2 VIA DI COLLE PIZZUTO  \n",
       "8762                                      NaN  \n",
       "8954               Categoria 3 LARGO PANNONIA  \n",
       "9712                      AFFITTACAMERE  ROMA  \n",
       "11573  5 Stelle VIA VITTORIO EMANUELE ORLANDO  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2019[dataset2019['Region'].str.contains('|'.join(dropRows))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All the above records need to be deleted. Once again there are multiple ways to do it. [A post in SlackOverflow presents some of them](https://stackoverflow.com/questions/13851535/delete-rows-from-a-pandas-dataframe-based-on-a-conditional-expression-involving)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2019.drop(dataset2019[dataset2019['Region'].str.contains('|'.join(dropRows))].index, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that all valid rows have a training single-space character, that is a ' ', at the end of each region. This needs to be fixed. So 'Mun. I ' needs to become 'Mun. I'. In other words [we need to strip the trailing space from each row](http://www.datasciencemadesimple.com/strip-space-column-pandas-dataframe-leading-trailing-2/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2019['Region'] = dataset2019['Region'].str.strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check out the resulting dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Mun. XII', 'Mun. I', 'Mun. VII', 'Mun. VIII', 'Mun. II',\n",
       "       'Mun. XIII', 'Mun. VI', 'Mun. III', 'Mun. XV', 'Mun. V',\n",
       "       'Mun. XIV', 'Mun. IV', 'Mun. IX', 'Mun. X', 'Mun. XI'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2019.Region.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we are in position to convert the 5 columns (Doppie, Triple, Quadruple, Quintuple, Sestuple) into numbers. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['One-Bed-Rooms', 'Two-Beds-Rooms', 'Three-Beds-Rooms', \n",
    "        'Four-Beds-Rooms', 'Five-Beds-Rooms', 'Total-Beds']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in cols:\n",
    "    dataset2019[col] = pd.to_numeric(dataset2019[col], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Room\t\t\tTotal\t\tMax Value\tIndex of Max\tName of Max\n",
      "One-Bed-Rooms   \t 16360.0\t    415.0\t       11237\tERGIFE  PALACE\n",
      "Two-Beds-Rooms  \t 54641.0\t    639.0\t       11090\tSHERATON ROMA HTEL\n",
      "Three-Beds-Rooms\t 10165.0\t    172.0\t       11337\tROUGE ET NOIR\n",
      "Four-Beds-Rooms \t  2338.0\t    108.0\t       11884\tQUIRINALE\n",
      "Five-Beds-Rooms \t   216.0\t      9.0\t       11662\tMAJESTIC\n",
      "Total-Beds      \t177196.0\t   1279.0\t       11090\tSHERATON ROMA HTEL\n"
     ]
    }
   ],
   "source": [
    "print(\"Room\\t\\t\\tTotal\\t\\tMax Value\\tIndex of Max\\tName of Max\")\n",
    "for col in cols:\n",
    "    print(\"{0:16}\\t{1:8}\\t{2:9}\\t{3:12}\\t{4}\".format(col, dataset2019[col].sum(), \n",
    "                                  dataset2019[col].max(), dataset2019[col].idxmax(),\n",
    "                                  dataset2019.loc[dataset2019[col].idxmax()].Insegna))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can repeat the statistics we did during the 2nd laboratory by grouping the rows based on the region."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "region_set2019 = dataset2019.groupby(['Region']).sum()"
   ]
  },
  {
   "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></th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>Five-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mun. I</th>\n",
       "      <td>8560.0</td>\n",
       "      <td>29234.0</td>\n",
       "      <td>6535.0</td>\n",
       "      <td>1679.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>98098.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. II</th>\n",
       "      <td>1459.0</td>\n",
       "      <td>3801.0</td>\n",
       "      <td>599.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>12464.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. III</th>\n",
       "      <td>323.0</td>\n",
       "      <td>691.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2385.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. IV</th>\n",
       "      <td>303.0</td>\n",
       "      <td>646.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2853.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. IX</th>\n",
       "      <td>298.0</td>\n",
       "      <td>2722.0</td>\n",
       "      <td>291.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7241.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. V</th>\n",
       "      <td>270.0</td>\n",
       "      <td>1070.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VI</th>\n",
       "      <td>200.0</td>\n",
       "      <td>926.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2894.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VII</th>\n",
       "      <td>687.0</td>\n",
       "      <td>2178.0</td>\n",
       "      <td>301.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6665.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VIII</th>\n",
       "      <td>258.0</td>\n",
       "      <td>1056.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3203.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. X</th>\n",
       "      <td>339.0</td>\n",
       "      <td>671.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2606.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XI</th>\n",
       "      <td>269.0</td>\n",
       "      <td>2241.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5305.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XII</th>\n",
       "      <td>689.0</td>\n",
       "      <td>2264.0</td>\n",
       "      <td>362.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7030.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XIII</th>\n",
       "      <td>1661.0</td>\n",
       "      <td>4741.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>119.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>14841.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XIV</th>\n",
       "      <td>696.0</td>\n",
       "      <td>1521.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5365.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XV</th>\n",
       "      <td>348.0</td>\n",
       "      <td>879.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3011.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           One-Bed-Rooms  Two-Beds-Rooms  Three-Beds-Rooms  Four-Beds-Rooms  \\\n",
       "Region                                                                        \n",
       "Mun. I            8560.0         29234.0            6535.0           1679.0   \n",
       "Mun. II           1459.0          3801.0             599.0            133.0   \n",
       "Mun. III           323.0           691.0             117.0             17.0   \n",
       "Mun. IV            303.0           646.0             315.0             19.0   \n",
       "Mun. IX            298.0          2722.0             291.0             41.0   \n",
       "Mun. V             270.0          1070.0             114.0             33.0   \n",
       "Mun. VI            200.0           926.0             115.0             76.0   \n",
       "Mun. VII           687.0          2178.0             301.0             33.0   \n",
       "Mun. VIII          258.0          1056.0             175.0             28.0   \n",
       "Mun. X             339.0           671.0              40.0             21.0   \n",
       "Mun. XI            269.0          2241.0              77.0              6.0   \n",
       "Mun. XII           689.0          2264.0             362.0             56.0   \n",
       "Mun. XIII         1661.0          4741.0             680.0            119.0   \n",
       "Mun. XIV           696.0          1521.0             276.0             42.0   \n",
       "Mun. XV            348.0           879.0             168.0             35.0   \n",
       "\n",
       "           Five-Beds-Rooms  Total-Beds  \n",
       "Region                                  \n",
       "Mun. I               174.0     98098.0  \n",
       "Mun. II                7.0     12464.0  \n",
       "Mun. III               2.0      2385.0  \n",
       "Mun. IV                5.0      2853.0  \n",
       "Mun. IX                2.0      7241.0  \n",
       "Mun. V                 0.0      3235.0  \n",
       "Mun. VI                0.0      2894.0  \n",
       "Mun. VII               1.0      6665.0  \n",
       "Mun. VIII              0.0      3203.0  \n",
       "Mun. X                 0.0      2606.0  \n",
       "Mun. XI                0.0      5305.0  \n",
       "Mun. XII              11.0      7030.0  \n",
       "Mun. XIII              8.0     14841.0  \n",
       "Mun. XIV               5.0      5365.0  \n",
       "Mun. XV                1.0      3011.0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "region_set2019"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2018\n",
    "We are not interested in checking out the annual progress on the hosting strucutres of Rome. Here we will use the dataset on [Strutture ricettive for January, 2018](https://dati.comune.roma.it/catalog/dataset/d198/resource/69b5af35-2088-42a2-bbf9-be8c60408eaf)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2018 = pd.read_csv('../data/comune.romadip_inntecopen_datadati-nuovo-portaleturismo-okstrutture-ricettivedopogennaio-2018str.csv', sep=',', delimiter=None, header='infer',\n",
    "                          names=None, index_col=None, \n",
    "                          usecols=None, encoding = \"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['INSEGNA', 'CLASSIFICAZIONE', 'INDIRIZZO', 'MUNICIPIO', 'TIPOLOGIA',\n",
       "       'SINGOLE', 'DOPPIE', 'TRIPLE', 'QUADRUPLE', 'QUINTUPLE', 'SESTUPLE',\n",
       "       'UNITA_ABITATIVE', 'POSTI_LETTO_UNITA_ABITATIVE', 'Unnamed: 13',\n",
       "       'Unnamed: 14', 'Unnamed: 15', 'Unnamed: 16'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2018.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that column names are in uppercase leters. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>INSEGNA</th>\n",
       "      <th>CLASSIFICAZIONE</th>\n",
       "      <th>INDIRIZZO</th>\n",
       "      <th>MUNICIPIO</th>\n",
       "      <th>TIPOLOGIA</th>\n",
       "      <th>SINGOLE</th>\n",
       "      <th>DOPPIE</th>\n",
       "      <th>TRIPLE</th>\n",
       "      <th>QUADRUPLE</th>\n",
       "      <th>QUINTUPLE</th>\n",
       "      <th>SESTUPLE</th>\n",
       "      <th>UNITA_ABITATIVE</th>\n",
       "      <th>POSTI_LETTO_UNITA_ABITATIVE</th>\n",
       "      <th>Unnamed: 13</th>\n",
       "      <th>Unnamed: 14</th>\n",
       "      <th>Unnamed: 15</th>\n",
       "      <th>Unnamed: 16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE 19</td>\n",
       "      <td>Categoria 1</td>\n",
       "      <td>VIA STATONIA</td>\n",
       "      <td>19</td>\n",
       "      <td>Mun. VII</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE ARGENTINA EXCL...</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>VIA DI S. ELENA</td>\n",
       "      <td>138</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE GIOIA</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>VIA DELLE CAVE DI PIETRALATA</td>\n",
       "      <td>64</td>\n",
       "      <td>Mun. IV</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MAMI'S HOME TRASTEVERE</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>PIAZZA DI S. APOLLONIA</td>\n",
       "      <td>3</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE ROMA BAROCCA S...</td>\n",
       "      <td>Categoria 1</td>\n",
       "      <td>VIA FRANCESCO CRISPI</td>\n",
       "      <td>20</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             INSEGNA CLASSIFICAZIONE  \\\n",
       "0                 CASA E APPARTAMENTO PER VACANZE 19     Categoria 1   \n",
       "1  CASA E APPARTAMENTO PER VACANZE ARGENTINA EXCL...     Categoria 2   \n",
       "2              CASA E APPARTAMENTO PER VACANZE GIOIA     Categoria 2   \n",
       "3                             MAMI'S HOME TRASTEVERE     Categoria 2   \n",
       "4  CASA E APPARTAMENTO PER VACANZE ROMA BAROCCA S...     Categoria 1   \n",
       "\n",
       "                      INDIRIZZO MUNICIPIO TIPOLOGIA  \\\n",
       "0                  VIA STATONIA        19  Mun. VII   \n",
       "1               VIA DI S. ELENA       138    Mun. I   \n",
       "2  VIA DELLE CAVE DI PIETRALATA        64   Mun. IV   \n",
       "3        PIAZZA DI S. APOLLONIA         3    Mun. I   \n",
       "4          VIA FRANCESCO CRISPI        20    Mun. I   \n",
       "\n",
       "                                SINGOLE DOPPIE TRIPLE QUADRUPLE  QUINTUPLE  \\\n",
       "0  Casa Vacanze NON impr (Appartamento)    NaN      1         1        NaN   \n",
       "1  Casa Vacanze NON impr (Appartamento)    NaN    NaN       NaN        NaN   \n",
       "2  Casa Vacanze NON impr (Appartamento)    NaN      1       NaN        NaN   \n",
       "3  Casa Vacanze NON impr (Appartamento)    NaN      3       NaN        NaN   \n",
       "4  Casa Vacanze NON impr (Appartamento)    NaN      1       NaN        NaN   \n",
       "\n",
       "   SESTUPLE  UNITA_ABITATIVE  POSTI_LETTO_UNITA_ABITATIVE  Unnamed: 13  \\\n",
       "0       NaN              5.0                          0.0          0.0   \n",
       "1       NaN              0.0                          1.0          1.0   \n",
       "2       NaN              2.0                          0.0          0.0   \n",
       "3       NaN              6.0                          0.0          0.0   \n",
       "4       NaN              2.0                          0.0          0.0   \n",
       "\n",
       "   Unnamed: 14  Unnamed: 15  Unnamed: 16  \n",
       "0          NaN          NaN          NaN  \n",
       "1          NaN          NaN          NaN  \n",
       "2          NaN          NaN          NaN  \n",
       "3          NaN          NaN          NaN  \n",
       "4          NaN          NaN          NaN  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2018.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also notice that the column Municipio again holds the street number and not the actual region of Rome. The type of the structure has been moved to the column Singole, and most probably all values are shifted one column towards left. Once again we will try to fix the data and remove the columns that do not provide any useful information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2018['Address'] = dataset2018['INDIRIZZO'] + \" \" + dataset2018['MUNICIPIO']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2018.rename(columns={'TIPOLOGIA':'Region', \n",
    "                            'SINGOLE':'Type',\n",
    "                            'DOPPIE':'One-Bed-Rooms',\n",
    "                            'TRIPLE':'Two-Beds-Rooms', \n",
    "                            'QUADRUPLE':'Three-Beds-Rooms', \n",
    "                            'QUINTUPLE':'Four-Beds-Rooms', \n",
    "                            'SESTUPLE':'Five-Beds-Rooms',\n",
    "                            'UNITA_ABITATIVE':'Total-Beds'}, \n",
    "                   inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['INSEGNA', 'CLASSIFICAZIONE', 'INDIRIZZO', 'MUNICIPIO', 'Region',\n",
       "       'Type', 'One-Bed-Rooms', 'Two-Beds-Rooms', 'Three-Beds-Rooms',\n",
       "       'Four-Beds-Rooms', 'Five-Beds-Rooms', 'Total-Beds',\n",
       "       'POSTI_LETTO_UNITA_ABITATIVE', 'Unnamed: 13', 'Unnamed: 14',\n",
       "       'Unnamed: 15', 'Unnamed: 16', 'Address'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2018.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2018.drop(columns=['INDIRIZZO', 'MUNICIPIO', 'POSTI_LETTO_UNITA_ABITATIVE', \n",
    "                          'Unnamed: 13', 'Unnamed: 14', 'Unnamed: 15', 'Unnamed: 16'], \n",
    "                 inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Mun. VII', 'Mun. I', 'Mun. IV', 'Mun. V', 'Mun. XII', 'Mun. X',\n",
       "       'Mun. II', 'Mun. XIII', 'Mun. III', 'Mun. VI', 'Mun. VIII',\n",
       "       'Mun. XI', 'Mun. XV', 'Mun. XIV', 'Mun. IX', '16', ' 1/D', '56',\n",
       "       '71', '49', '10', '70', '76', '171', '21', 'VIA DI COLLE PIZZUTO',\n",
       "       '1', 'Categoria 2', '3'], dtype=object)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2018.Region.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "dropRows = ['16', ' 1/D', '56', '71', '49', '10', '70', '76', '171', '21', \n",
    "            'VIA DI COLLE PIZZUTO', '1', 'Categoria 2', '3']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset2018.drop(dataset2018[dataset2018['Region'].str.contains('|'.join(dropRows))].index, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>INSEGNA</th>\n",
       "      <th>CLASSIFICAZIONE</th>\n",
       "      <th>Region</th>\n",
       "      <th>Type</th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>Five-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "      <th>Address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE 19</td>\n",
       "      <td>Categoria 1</td>\n",
       "      <td>Mun. VII</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>VIA STATONIA 19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE ARGENTINA EXCL...</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>VIA DI S. ELENA 138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE GIOIA</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>Mun. IV</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>VIA DELLE CAVE DI PIETRALATA 64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MAMI'S HOME TRASTEVERE</td>\n",
       "      <td>Categoria 2</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>PIAZZA DI S. APOLLONIA 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CASA E APPARTAMENTO PER VACANZE ROMA BAROCCA S...</td>\n",
       "      <td>Categoria 1</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>Casa Vacanze NON impr (Appartamento)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>VIA FRANCESCO CRISPI 20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             INSEGNA CLASSIFICAZIONE  \\\n",
       "0                 CASA E APPARTAMENTO PER VACANZE 19     Categoria 1   \n",
       "1  CASA E APPARTAMENTO PER VACANZE ARGENTINA EXCL...     Categoria 2   \n",
       "2              CASA E APPARTAMENTO PER VACANZE GIOIA     Categoria 2   \n",
       "3                             MAMI'S HOME TRASTEVERE     Categoria 2   \n",
       "4  CASA E APPARTAMENTO PER VACANZE ROMA BAROCCA S...     Categoria 1   \n",
       "\n",
       "     Region                                  Type One-Bed-Rooms  \\\n",
       "0  Mun. VII  Casa Vacanze NON impr (Appartamento)           NaN   \n",
       "1    Mun. I  Casa Vacanze NON impr (Appartamento)           NaN   \n",
       "2   Mun. IV  Casa Vacanze NON impr (Appartamento)           NaN   \n",
       "3    Mun. I  Casa Vacanze NON impr (Appartamento)           NaN   \n",
       "4    Mun. I  Casa Vacanze NON impr (Appartamento)           NaN   \n",
       "\n",
       "  Two-Beds-Rooms Three-Beds-Rooms  Four-Beds-Rooms  Five-Beds-Rooms  \\\n",
       "0              1                1              NaN              NaN   \n",
       "1            NaN              NaN              NaN              NaN   \n",
       "2              1              NaN              NaN              NaN   \n",
       "3              3              NaN              NaN              NaN   \n",
       "4              1              NaN              NaN              NaN   \n",
       "\n",
       "   Total-Beds                          Address  \n",
       "0         5.0                  VIA STATONIA 19  \n",
       "1         0.0              VIA DI S. ELENA 138  \n",
       "2         2.0  VIA DELLE CAVE DI PIETRALATA 64  \n",
       "3         6.0         PIAZZA DI S. APOLLONIA 3  \n",
       "4         2.0          VIA FRANCESCO CRISPI 20  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2018.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in cols:\n",
    "    dataset2018[col] = pd.to_numeric(dataset2018[col], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Room\t\t\tTotal\t\tMax Value\tIndex of Max\tName of Max\n",
      "One-Bed-Rooms   \t 15860.0\t    101.0\t       10994\tERGIFE PALACE\n",
      "Two-Beds-Rooms  \t 53461.0\t    703.0\t       10994\tERGIFE PALACE\n",
      "Three-Beds-Rooms\t  9907.0\t    172.0\t       10861\tROUGE ET NOIR\n",
      "Four-Beds-Rooms \t  2198.0\t    108.0\t       11568\tQUIRINALE\n",
      "Five-Beds-Rooms \t   194.0\t      9.0\t       11374\tMAJESTIC\n",
      "Total-Beds      \t173579.0\t   1507.0\t       10994\tERGIFE PALACE\n"
     ]
    }
   ],
   "source": [
    "print(\"Room\\t\\t\\tTotal\\t\\tMax Value\\tIndex of Max\\tName of Max\")\n",
    "for col in cols:\n",
    "    print(\"{0:16}\\t{1:8}\\t{2:9}\\t{3:12}\\t{4}\".format(col, dataset2018[col].sum(), \n",
    "                                  dataset2018[col].max(), dataset2018[col].idxmax(),\n",
    "                                  dataset2018.loc[dataset2018[col].idxmax()].INSEGNA))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "region_set2018 = dataset2018.groupby(['Region']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>One-Bed-Rooms</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>Five-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mun. I</th>\n",
       "      <td>8441.0</td>\n",
       "      <td>28147.0</td>\n",
       "      <td>6279.0</td>\n",
       "      <td>1579.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>94934.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. II</th>\n",
       "      <td>1453.0</td>\n",
       "      <td>3698.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12289.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. III</th>\n",
       "      <td>311.0</td>\n",
       "      <td>685.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2355.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. IV</th>\n",
       "      <td>298.0</td>\n",
       "      <td>613.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2823.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. IX</th>\n",
       "      <td>381.0</td>\n",
       "      <td>2626.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7169.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. V</th>\n",
       "      <td>273.0</td>\n",
       "      <td>1049.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3206.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VI</th>\n",
       "      <td>197.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2850.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VII</th>\n",
       "      <td>620.0</td>\n",
       "      <td>2091.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6447.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VIII</th>\n",
       "      <td>260.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. X</th>\n",
       "      <td>332.0</td>\n",
       "      <td>635.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2505.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XI</th>\n",
       "      <td>223.0</td>\n",
       "      <td>2224.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5226.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XII</th>\n",
       "      <td>719.0</td>\n",
       "      <td>2268.0</td>\n",
       "      <td>365.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7085.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XIII</th>\n",
       "      <td>1328.0</td>\n",
       "      <td>5128.0</td>\n",
       "      <td>681.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15325.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XIV</th>\n",
       "      <td>689.0</td>\n",
       "      <td>1520.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5343.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XV</th>\n",
       "      <td>335.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2886.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           One-Bed-Rooms  Two-Beds-Rooms  Three-Beds-Rooms  Four-Beds-Rooms  \\\n",
       "Region                                                                        \n",
       "Mun. I            8441.0         28147.0            6279.0           1579.0   \n",
       "Mun. II           1453.0          3698.0             600.0            130.0   \n",
       "Mun. III           311.0           685.0             115.0             14.0   \n",
       "Mun. IV            298.0           613.0             323.0             18.0   \n",
       "Mun. IX            381.0          2626.0             300.0             40.0   \n",
       "Mun. V             273.0          1049.0             115.0             31.0   \n",
       "Mun. VI            197.0           912.0             112.0             75.0   \n",
       "Mun. VII           620.0          2091.0             300.0             24.0   \n",
       "Mun. VIII          260.0          1000.0             177.0             34.0   \n",
       "Mun. X             332.0           635.0              36.0             16.0   \n",
       "Mun. XI            223.0          2224.0              76.0              6.0   \n",
       "Mun. XII           719.0          2268.0             365.0             57.0   \n",
       "Mun. XIII         1328.0          5128.0             681.0            112.0   \n",
       "Mun. XIV           689.0          1520.0             270.0             42.0   \n",
       "Mun. XV            335.0           865.0             158.0             20.0   \n",
       "\n",
       "           Five-Beds-Rooms  Total-Beds  \n",
       "Region                                  \n",
       "Mun. I               156.0     94934.0  \n",
       "Mun. II                4.0     12289.0  \n",
       "Mun. III               1.0      2355.0  \n",
       "Mun. IV                5.0      2823.0  \n",
       "Mun. IX                2.0      7169.0  \n",
       "Mun. V                 0.0      3206.0  \n",
       "Mun. VI                0.0      2850.0  \n",
       "Mun. VII               1.0      6447.0  \n",
       "Mun. VIII              0.0      3136.0  \n",
       "Mun. X                 0.0      2505.0  \n",
       "Mun. XI                0.0      5226.0  \n",
       "Mun. XII              11.0      7085.0  \n",
       "Mun. XIII              8.0     15325.0  \n",
       "Mun. XIV               5.0      5343.0  \n",
       "Mun. XV                1.0      2886.0  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "region_set2018"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Compare 2019 with 2018\n",
    "Here we need to compare the two tables. We start by creating a new DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "bothyears = pd.DataFrame( region_set2018['Total-Beds'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "bothyears = bothyears.rename(columns={'Total-Beds' : '2018'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "bothyears['2019'] = region_set2019['Total-Beds']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>2018</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mun. I</th>\n",
       "      <td>94934.0</td>\n",
       "      <td>98098.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. II</th>\n",
       "      <td>12289.0</td>\n",
       "      <td>12464.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. III</th>\n",
       "      <td>2355.0</td>\n",
       "      <td>2385.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. IV</th>\n",
       "      <td>2823.0</td>\n",
       "      <td>2853.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. IX</th>\n",
       "      <td>7169.0</td>\n",
       "      <td>7241.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. V</th>\n",
       "      <td>3206.0</td>\n",
       "      <td>3235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VI</th>\n",
       "      <td>2850.0</td>\n",
       "      <td>2894.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VII</th>\n",
       "      <td>6447.0</td>\n",
       "      <td>6665.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VIII</th>\n",
       "      <td>3136.0</td>\n",
       "      <td>3203.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. X</th>\n",
       "      <td>2505.0</td>\n",
       "      <td>2606.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XI</th>\n",
       "      <td>5226.0</td>\n",
       "      <td>5305.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XII</th>\n",
       "      <td>7085.0</td>\n",
       "      <td>7030.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XIII</th>\n",
       "      <td>15325.0</td>\n",
       "      <td>14841.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XIV</th>\n",
       "      <td>5343.0</td>\n",
       "      <td>5365.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XV</th>\n",
       "      <td>2886.0</td>\n",
       "      <td>3011.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              2018     2019\n",
       "Region                     \n",
       "Mun. I     94934.0  98098.0\n",
       "Mun. II    12289.0  12464.0\n",
       "Mun. III    2355.0   2385.0\n",
       "Mun. IV     2823.0   2853.0\n",
       "Mun. IX     7169.0   7241.0\n",
       "Mun. V      3206.0   3235.0\n",
       "Mun. VI     2850.0   2894.0\n",
       "Mun. VII    6447.0   6665.0\n",
       "Mun. VIII   3136.0   3203.0\n",
       "Mun. X      2505.0   2606.0\n",
       "Mun. XI     5226.0   5305.0\n",
       "Mun. XII    7085.0   7030.0\n",
       "Mun. XIII  15325.0  14841.0\n",
       "Mun. XIV    5343.0   5365.0\n",
       "Mun. XV     2886.0   3011.0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bothyears"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7fd8bae22550>"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAY0AAAEsCAYAAAA/5++aAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4wLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvqOYd8AAAH7xJREFUeJzt3X+8VXWd7/HXWw6Gv1A0VAIV5kaF6aRI4jxsau5ghDU3TM1fTZJDch+T/bDGuVKPex9mjV28dxyNxrxXg0Sbh0TmBBXFNcrJfqCglD8wR0ZRDgkSmGZEiX3uH/sLbo/7HJZ7re/eZ7Pfz8fjPNj7u9f+rHUW55z3Xp/v2msrIjAzMytir3ZvgJmZdQ6HhpmZFebQMDOzwhwaZmZWmEPDzMwKc2iYmVlhDg0zMyvMoWFmZoU5NMzMrLCedm9A1V796lfH2LFj270ZZmYd5Z577vlVRIzc3XJ7XGiMHTuWVatWtXszzMw6iqTHiyzn9pSZmRW229CQNF/SU5IeqBs7WNLtkh5J/45I45I0V9JaSfdJmlj3nBlp+UckzagbP0HS/ek5cyVpoHWYmVn7FDnSuBGY1mdsNrA8IsYDy9N9gFOB8elrFnAd1AIAuAyYDJwIXFYXAtcBF9Y9b9pu1mFmZm2y2zmNiPihpLF9hqcDf5FuLwDuAC5N4zdF7XrrKyQdJGlUWvb2iNgKIOl2YJqkO4DhEbEijd8EnAZ8Z4B1mJlV4vnnn6e3t5ft27e3e1NaZtiwYYwZM4ahQ4c29fxmJ8IPi4gn0+2NwGHp9mhgfd1yvWlsoPHeBuMDreNlJM2idmTDkUce+Uq/FzPrUr29vRxwwAGMHTuW1Bnfo0UEW7Zsobe3l3HjxjVVo/REeDqqyPpJTrtbR0RcHxGTImLSyJG7PWPMzAyA7du3c8ghh3RFYABI4pBDDil1ZNVsaGxKbSfSv0+l8Q3AEXXLjUljA42PaTA+0DrMzCrTLYGxU9nvt9nQWALsPANqBrC4bvz8dBbVScAzqcW0DJgqaUSaAJ8KLEuPPSvppHTW1Pl9ajVah5mZtclu5zQk3UJtQvrVknqpnQU1B1gkaSbwOHBWWnwp8E5gLbANuAAgIrZK+iywMi33mZ2T4sCHqJ2htQ+1CfDvpPH+1lGdTx/Yz/gzla/KzAa/sbO/XWm9dXPetdtl1q9fz/nnn8+mTZuQxKxZs/jYxz7G1q1bOfvss1m3bh1jx45l0aJFjBgxgl/84hdccMEF3HvvvVxxxRVccsklu2pdffXVfOlLX0ISxx57LF/+8pcZNmxYpd9TkbOnzu3noSkNlg3gon7qzAfmNxhfBRzTYHxLo3WYme1Jenp6uOqqq5g4cSK/+c1vOOGEE3j729/OjTfeyJQpU5g9ezZz5sxhzpw5XHnllRx88MHMnTuXb3zjGy+ps2HDBubOncuaNWvYZ599OOuss1i4cCEf+MAHKt1evyPczKyNRo0axcSJtfdBH3DAAUyYMIENGzawePFiZsyodehnzJixKyQOPfRQ3vzmNzc8ZXbHjh387ne/Y8eOHWzbto3XvOY1lW+vQ8PMbJBYt24dq1evZvLkyWzatIlRo0YBcPjhh7Np06YBnzt69GguueQSjjzySEaNGsWBBx7I1KlTK99Gh4aZ2SDw3HPPccYZZ3DNNdcwfPjwlzwmabdnPT399NMsXryYxx57jF/+8pf89re/5Stf+Url2+nQMDNrs+eff54zzjiD973vfZx++ukAHHbYYTz5ZO39zU8++SSHHnrogDW+973vMW7cOEaOHMnQoUM5/fTT+clPflL5tjo0zMzaKCKYOXMmEyZM4BOf+MSu8Xe/+90sWLAAgAULFjB9+vQB6xx55JGsWLGCbdu2EREsX76cCRMmVL69e9znaTTS32l066o9E83MOlyRU2Sr9uMf/5ibb76ZY489luOOOw6Az33uc8yePZuzzjqLefPmcdRRR7Fo0SIANm7cyKRJk3j22WfZa6+9uOaaa1izZg2TJ0/mzDPPZOLEifT09HD88ccza9asyre3K0LDzGywestb3kLt3Qovt3z58peNHX744fT29jZYGi6//HIuv/zySrevL7enzMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzApzaJiZWWE+5dbMbKf+Pi6h6Xq7/5iFKi+N/vnPf54bbriBiODCCy/k4osvrvb7wUcaZmZttfPS6GvWrGHFihVce+21rFmzhjlz5jBlyhQeeeQRpkyZwpw5cwB2XRq9PiwAHnjgAW644Qbuvvtufv7zn/Otb32LtWvXVr69Dg0zszaq6tLoDz30EJMnT2bfffelp6eHt73tbdx2222Vb69Dw8xskChzafRjjjmGO++8ky1btrBt2zaWLl3K+vXrK99Gz2mYmQ0CZS+NPmHCBC699FKmTp3Kfvvtx3HHHceQIUMq304faZiZtVkVl0YHmDlzJvfccw8//OEPGTFiBK973esq31aHhplZG1V1aXSAp556CoAnnniC2267jfPOO6/y7XV7ysxspwKnyFatqkujDx8+nDPOOIMtW7YwdOhQrr32Wg466KDKt9ehYWbWRlVeGv3OO++sdNsacXvKzMwKc2iYmVlhDg0z62r9tYb2VGW/X4eGmXWtYcOGsWXLlq4Jjohgy5YtDBs2rOkangg3s641ZswYent72bx5c7s3pWWGDRvGmDFjmn6+Q8PMutbQoUMZN25cuzejo7g9ZWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlZYqdCQ9HFJD0p6QNItkoZJGifpLklrJX1V0t5p2Vel+2vT42Pr6nwyjT8s6R1149PS2FpJs8tsq5mZldd0aEgaDXwUmBQRxwBDgHOAK4GrI+K1wNPAzPSUmcDTafzqtBySjk7PeyMwDfiipCGShgDXAqcCRwPnpmXNzKxNyraneoB9JPUA+wJPAn8J3JoeXwCclm5PT/dJj09R7aOopgMLI+L3EfEYsBY4MX2tjYhHI+IPwMK0rJmZtUnToRERG4B/BJ6gFhbPAPcAv46IHWmxXmB0uj0aWJ+euyMtf0j9eJ/n9DduZmZtUqY9NYLaK/9xwGuA/ai1l1pO0ixJqySt6qbLAZiZtVqZ9tQpwGMRsTkingduA04GDkrtKoAxwIZ0ewNwBEB6/EBgS/14n+f0N/4yEXF9REyKiEkjR44s8S2ZmdlAyoTGE8BJkvZNcxNTgDXAD4Az0zIzgMXp9pJ0n/T496N2acklwDnp7KpxwHjgbmAlMD6djbU3tcnyJSW218zMSmr6goURcZekW4F7gR3AauB64NvAQkn/kMbmpafMA26WtBbYSi0EiIgHJS2iFjg7gIsi4gUASR8GllE7M2t+RDzY7PaamVl5pa5yGxGXAZf1GX6U2plPfZfdDry3nzpXAFc0GF8KLC2zjWZmVh2/I9zMzApzaJiZWWEODTMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzApzaJiZWWEODTMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzApzaJiZWWEODTMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzApzaJiZWWEODTMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzAorFRqSDpJ0q6RfSHpI0p9JOljS7ZIeSf+OSMtK0lxJayXdJ2liXZ0ZaflHJM2oGz9B0v3pOXMlqcz2mplZOWWPND4PfDci3gC8CXgImA0sj4jxwPJ0H+BUYHz6mgVcByDpYOAyYDJwInDZzqBJy1xY97xpJbfXzMxKaDo0JB0IvBWYBxARf4iIXwPTgQVpsQXAaen2dOCmqFkBHCRpFPAO4PaI2BoRTwO3A9PSY8MjYkVEBHBTXS0zM2uDMkca44DNwJclrZb0JUn7AYdFxJNpmY3AYen2aGB93fN709hA470Nxs3MrE3KhEYPMBG4LiKOB37Li60oANIRQpRYRyGSZklaJWnV5s2bc6/OzKxrlQmNXqA3Iu5K92+lFiKbUmuJ9O9T6fENwBF1zx+TxgYaH9Ng/GUi4vqImBQRk0aOHFniWzIzs4E0HRoRsRFYL+n1aWgKsAZYAuw8A2oGsDjdXgKcn86iOgl4JrWxlgFTJY1IE+BTgWXpsWclnZTOmjq/rpaZmbVBT8nnfwT4F0l7A48CF1ALokWSZgKPA2elZZcC7wTWAtvSskTEVkmfBVam5T4TEVvT7Q8BNwL7AN9JX2Zm1ialQiMifgZMavDQlAbLBnBRP3XmA/MbjK8CjimzjWZmVh2/I9zMzApzaJiZWWEODTMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzApzaJiZWWEODTMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzApzaJiZWWEODTMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzApzaJiZWWEODTMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzApzaJiZWWGlQ0PSEEmrJX0r3R8n6S5JayV9VdLeafxV6f7a9PjYuhqfTOMPS3pH3fi0NLZW0uyy22pmZuVUcaTxMeChuvtXAldHxGuBp4GZaXwm8HQavzoth6SjgXOANwLTgC+mIBoCXAucChwNnJuWNTOzNikVGpLGAO8CvpTuC/hL4Na0yALgtHR7erpPenxKWn46sDAifh8RjwFrgRPT19qIeDQi/gAsTMuamVmblD3SuAb4b8Af0/1DgF9HxI50vxcYnW6PBtYDpMefScvvGu/znP7GX0bSLEmrJK3avHlzyW/JzMz603RoSPor4KmIuKfC7WlKRFwfEZMiYtLIkSPbvTlmZnusnhLPPRl4t6R3AsOA4cDngYMk9aSjiTHAhrT8BuAIoFdSD3AgsKVufKf65/Q3bmZmbdD0kUZEfDIixkTEWGoT2d+PiPcBPwDOTIvNABan20vSfdLj34+ISOPnpLOrxgHjgbuBlcD4dDbW3mkdS5rdXjMzK6/MkUZ/LgUWSvoHYDUwL43PA26WtBbYSi0EiIgHJS0C1gA7gIsi4gUASR8GlgFDgPkR8WCG7TUzs4IqCY2IuAO4I91+lNqZT32X2Q68t5/nXwFc0WB8KbC0im00M7Py/I5wMzMrzKFhZmaFOTTMzKwwh4aZmRXm0DAzs8IcGmZmVphDw8zMCnNomJlZYQ4NMzMrzKFhZmaFOTTMzKwwh4aZmRXm0DAzs8IcGmZmVphDw8zMCnNomJlZYQ4NMzMrzKFhZmaFOTTMzKwwh4aZmRXm0DAzs8IcGmZmVphDw8zMCnNomJlZYQ4NMzMrzKFhZmaFOTTMzKwwh4aZmRXm0DAzs8IcGmZmVphDw8zMCnNomJlZYQ4NMzMrrOnQkHSEpB9IWiPpQUkfS+MHS7pd0iPp3xFpXJLmSlor6T5JE+tqzUjLPyJpRt34CZLuT8+ZK0llvlkzMyunzJHGDuDvIuJo4CTgIklHA7OB5RExHlie7gOcCoxPX7OA66AWMsBlwGTgROCynUGTlrmw7nnTSmyvmZmV1HRoRMSTEXFvuv0b4CFgNDAdWJAWWwCclm5PB26KmhXAQZJGAe8Abo+IrRHxNHA7MC09NjwiVkREADfV1TIzszaoZE5D0ljgeOAu4LCIeDI9tBE4LN0eDayve1pvGhtovLfBeKP1z5K0StKqzZs3l/pezMysf6VDQ9L+wNeBiyPi2frH0hFClF3H7kTE9RExKSImjRw5MvfqzMy6VqnQkDSUWmD8S0TcloY3pdYS6d+n0vgG4Ii6p49JYwONj2kwbmZmbVLm7CkB84CHIuKf6h5aAuw8A2oGsLhu/Px0FtVJwDOpjbUMmCppRJoAnwosS489K+mktK7z62qZmVkb9JR47snA+4H7Jf0sjX0KmAMskjQTeBw4Kz22FHgnsBbYBlwAEBFbJX0WWJmW+0xEbE23PwTcCOwDfCd9mZlZmzQdGhHxI6C/901MabB8ABf1U2s+ML/B+CrgmGa30czMquV3hJuZWWEODTMzK8yhYWZmhTk0zMyssDJnT5mZ7XHGzv52w/F1c97V4i0ZnBwaZmZFfPrABmPPtH472sztKTMzK8yhYWZmhTk0zMysMIeGmZkV5onwkhqdabFu2HmNF+7CSTMz27P4SMPMzApzaJiZWWEODTMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PCHBpmZlaYQ8PMzApzaJiZWWG+jIhVxpdUsVbyhyW1h0PDrFUafYgPOECr5g9LysqhYZZB46OuNmyIWcU8p2FmZoX5SMPMsvJc14v2hH3hIw0zMyvMoWFmZoU5NMzMrDCHhpmZFeaJ8C7T7xuiBvFk3Cva5lewvbnq5tSJ22z5tXKC3aExSPmPg7WSf96sKLenzMyssEEfGpKmSXpY0lpJs9u9PWZm3WxQh4akIcC1wKnA0cC5ko5u71aZmXWvQR0awInA2oh4NCL+ACwEprd5m8zMupYiot3b0C9JZwLTIuKD6f77gckR8eE+y80CZqW7rwceLriKVwO/qmhzW1E3Z+1Oq5uzdqfVzVm70+rmrL2n1z0qIkbubqE94uypiLgeuP6VPk/SqoiYVPX25Kqbs3an1c1Zu9Pq5qzdaXVz1nbdmsHentoAHFF3f0waMzOzNhjsobESGC9pnKS9gXOAJW3eJjOzrjWo21MRsUPSh4FlwBBgfkQ8WOEqXnFLq811c9butLo5a3da3Zy1O61uztquyyCfCDczs8FlsLenzMxsEHFomJlZYQ4NMzMrbFBPhFv1JL05Ila2eJ09EbGjxPPfC3wzIrZXuFkdSdLpAz0eEbc1WXeviPhjP48dFBG/bqZuJ8q4jw+PiI3NbdWAdVv6O90VE+GSvgn0+41GxLubrPuF3dT9aDN1U+1PDPR4RPxTk3VXA/tTuyTLLRGxppk6Dep+E/hwRDzeZ/wU4JqIOKZE7X8FTqZ2Ft0twLKIeKHM9qa6Wf7/cv3fpdpfHrh0/E2Tde8F/jYi7uoz/kHgUxHxJ03W7cTfkVz7eCPwALWf4a9XFcS5fqf70y1HGv+Yqe6qTHUBDshRNCKOl/R6au95uVXS89R+iBdGxLoSpRcCP5A0D/hfwEjgGuAoYEbJbX6PpOHAe4CPAPMkLab2C/JvJUrn+v/L8n8HEBEXZCr9UeB6SXcDl1L7f/si0Au8tUTdTvwdybWPRwOnUPvd+5ykFdR+9xZHxO+aLZrxd7qhrjjSsP5JehO1H7azgI0RcXKJWgdSC4wpwFDgCuCGqPiHTNIhwJnAh4CDI+KI3Tylvzod1/bKfBTTA3wauAh4DpgZEf+v2XqpZse1vXLu47p17E3t6t3nAP8ZWB4R7ytbN9Wu7He6kW450sgiV9sr1Z470ONlDuvr1rEXcChwGLAf8FTJkkdTuzLx3cCkVLcHeL5k3V0kjQBOB84GDgZuLVHuPOBaSR3R9kqyHcVQC+JzgeuAtwNnp+sXbS1Rc5WkftteQFNtr1Qj1+9Izn0MQET8QdIa4CHgBGBCFXUz/E6/fB0+0miepLcN9HiZ1omkAVs6EbGgRO0/p/bH4TTgfmqtpdsiounP8UxtqeOBiyLip5L2Ay4H3glcXOYVq6T9qbWmzk3rWJK2+Y6yRzF1ba9zgOOA0m2vnP93uUj6HrAd+EhEPJb++FwEXAxcmS4K2kzdt1D7TJxGba+PR0RviW3uxP18BLWftXOp/VHf2Ub6Rcm6lf9O97suh0Z3kbQeeJzaD9WiiKjklYikjwNz+75Sl3Qs8MWI+PMStX8FfJfaNi+LiMqOXPqsZ9C3vTJO3r8nIv61wfjhwFVlWic52l6pbpbWV64jGEk/oTav8TVqL0zuaaZOg7pZfqf7XV+3h4akWc2+isop4xlfR/U9w2mwk7RPmYnCgusYwYvtmfHArRHx8SZrZTnbK9XuxFfX51Cb3/oqtbbXfcDfl2x75TzjK8s+lvRW4M4Mc3z/KSL+o5/HKp83cmhI/zUi/m+7t6OvXK2vjGGUbX4nl05re6W6HTV5n6vtlWpnaX1lPILJdZSYJTz7XV+3h0a3yRhG2eZ3cum0tleqle0oJoecba9UJ8cZX512BJNt3qjh+ropNCS9CjgDGEvdmWMR8ZkM6xqUbS97Uae1vepqZjmK6UQ5Wl+ddgSTnp9l3qiRbrv21GJgOrAD+G3dVw7KVNfqqPb58E3JFRiS9pf0fklLgTXUTj/+LHBk2cAAiIhnI2JBRJwKHAOsBuamCdHKldnHOaXW118Dp0TEp4DJwM+AlSV/Ln5E7TTYTcB/UGtbXhYR7y35qn2VpMl9B9MRzL0l6sJLT5feSO106YNL1myo296nMSYiprViRYNxnmQPNRjDeR21ttcXydv2qvI9KwOuKlPdsq6tb32lV/FfkPQ14CrKfQhRjvesZHnXfd280Slp3ui/UzviWCmp1LxRw/V1WXvqeuALEXF/xXVb1vZK63PraxDL2fbKOXlvNZkn73PMwWSdN3pZ3W76OUvvwHwt8Bjwe2qvoCIi/rRk3e8CzwD3ALsmJSPiqjJ1B1hfljO+coVRVXU9J5V/8t77OPt7VrKcftxK3daeOjVT3Za1vSBr6ytXG6Kquot5MZx/X1HN/gzWlswRmSfvu34fNwqMNL4RKBMYLW0j5dJtRxpHNhqPiCdK1s3S9kq1W9r6GswkPRAlLrFuu+d9nE+r20i5dNuRxrepvblGwDBgHPAw8MaSdd8CfEBSpW2vJMsrv1xhlDnkfiLp2E6ekxqsLZk6Hb+P0/oG3X7OdQTTal0VGhFxbP19SROpveGqrFxtL8jX+srVhsjZ3sgVzl3fkqmzJ+xjGPz7uSVyhGdXhUZfEXFvo/OmmylVQY3+ZHnlR74wyjm/0/FzUjlPxa7oD0TH72PIt58H4xHMblQenl0VGnrph6vsBUwEfllB6VxtL8j3yi9XGOWqC/nCeY9oyVDNH4iO2sfQ8v3cUUcwWc6y7LKJ8Mvq7u6g9iasr0fFF3/b2faKiA9WUOuoRuNR8kq1GU8/zlI31b6fBuEcEaXCeU85FbsKnbaPU+2O2899VXEE06rw7KojjYi4vEXrqartBfle+eVqQ2Sb3+nAOalsLZlcfyA6cB9Dpv3cgUcwLZk36orQkLRkoMej5GW7M7a9IF/rK1cYtezQtQPmpHK26lryB6ID9jHk288tm7yvqI3UknmjrggN4M+A9dQuJX0X1fcl6z9TeAe1P/Rfr6Jwxld+ucIo2/xOB85J5TwVO9er607bx5BvP3faEUzOFym7dEtoHE7tLfvnAudR+wG+JSIerKJ4q9peaV2VvPLLFUYZQw4yhXOHtmRy/YHotH0M+fZzpx3B5HyRsktXTYTDrpQ/F/jfwOUR8c8lamVte6V1NHrld0hEvKNs7Qbrur/vL/dgrptTFduc6woEqXa2ieVWqernIuOVHnKdIJHlXfe5Tprpq1uONHaGxbuoBcZYYC7Q8B2ar0DuthdkeuWXqw2Ro24Hz0nlbMlU+uq6g/cx5NvPnXYE05IjgK4IDUk3UfuwmqXUji4eqKh01rYXZG195ZqHyVG3I+ekMrdkqv4D0ZH7GLLu51x/hHO1kXK+SNmlK9pTkv7Ii5/QV/8N7/zPGl7BOipre6V62VtfnULSEF4M5z8lQzi3SoUtmUrfT7En7WOorL2Y6z0rLWkjVfl+sXpdcaQREdk+1jZT2wsyvfLLFUY5Qy4iXqD2GRLfrQvnOyQN6jmpnC2Zql9dd+o+TuvIsp878AjmpSup9v1iu3RFaOSSse0F+VpfudoQWdsbHTonla0l01cVfyA6dB9Di/ZzhX+Es7SRMs8bvbiebmhP5dKKtldaT5VnfGVpQ+Rsb/QJ54VVhXMnt2SqPqvO+/jlWnXmYlVtpJZdJsmhMXg1eOW3BJgfERsqrF/ZPEyuup02J9WilkylfyA6bR+nernbiy35I5zW1TGnpTs0Bqlcr/xS7SxhlDvkcsixzZI2M0BLJiL+rdnanSjjz1tH7ucMR4ktPWnGoTFI5Xrll7ENkS3kcunElkynnVWX+cVPrlZrRx3BtDo8HRpdJmMYtWR+p0od2pLpqFfXHTrv12n7uKXzRg4NswwytmQ6dmI5h0ztxY48gknryDJP+ZJ1ODTMqtWqVl0r/kAMZq3Yz51yBNPK+USHhlnFcrdkOvGEgxxy7ucOO4Jp6XyiQ8Osg3TiCQedpgOPYFo6n+jQMOsgnXjCQafptCOYVnNomJm1wJ5ylOjQMDNrgT3lKNGhYWZmhWW7ZLiZme15HBpmZlaYQ8PMzApzaJgVJOkFST+T9ICkb0o6qEStz0g6pcrtM2sFT4SbFSTpuYjYP91eAPx7RFzR5s0yaykfaZg156fA6J13JP29pJWS7pN0ed34/5D0sKQfSbpF0iVp/EZJZ6bbUyStlnS/pPnpDWBIWifpckn3psfe0OLv0exlHBpmr1C6htAUau/mRdJUYDxwInAccIKkt0p6M3AG8CbgVGBSg1rDgBuBs9Mnt/UAf1u3yK8iYiJwHXBJru/JrCiHhllx+0j6GbAROAy4PY1PTV+rgXuBN1ALkZOBxRGxPSJ+A3yzQc3XA49FxL+n+wuAt9Y9flv69x5ql50wayuHhllxv4uI44CjqL2L96I0LuB/RsRx6eu1ETGvonX+Pv37ArWjELO2cmiYvUIRsQ34KPB3knqAZcDfSNo5ST5a0qHAj4H/ImlYeuyvGpR7GBgr6bXp/vuBQfXJcGb1/MrFrAkRsVrSfcC5EXGzpAnATyUBPAf8dUSsTJ/Wdh+wCbgfeKZPne2SLgC+lgJoJfB/Wvm9mL0SPuXWLCNJ+0fEc5L2BX4IzIqIe9u9XWbN8pGGWV7XSzoaGAYscGBYp/ORhpmZFeaJcDMzK8yhYWZmhTk0zMysMIeGmZkV5tAwM7PC/j80SHyOZ/hlRQAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bothyears.plot.bar()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2 - Document Databases\n",
    "\n",
    "In this part we will store the clean data in a database so that we can use it without the need to go through all these steps. We will use the [MongoDB](https://www.mongodb.com/what-is-mongodb) installed on our workstation and also through the Database-as-a-service provider [mLab](https://mlab.com/). \n",
    "\n",
    "The contents of the dataset will be stored into a document database where each row becomes a separate document. \n",
    "\n",
    "Documents are encoded as JSON objects.\n",
    "\n",
    "We will use the [JSON encoder and decoder](https://docs.python.org/3/library/json.html) a standard python package as defined in [W3C School on JSON](https://www.w3schools.com/js/js_json_intro.asp). Two main methods are provided:\n",
    "- *dumps* to write/convert/unmarsal a python object into a JSON object.\n",
    "- *loads* to load/convert/marshal a JSON object into a Python Dictionary object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = {'first_name':'Ioannis', 'positions': 'DIAG'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'first_name': 'Ioannis', 'positions': 'DIAG'}"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"first_name\": \"Ioannis\", \"positions\": \"DIAG\"}'"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json.dumps(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "txt = '{\"first_name\": \"Ioannis\", \"positions\": \"DIAG\", \"nationality\":\"Greek\" }'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "dd = json.loads(txt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'first_name': 'Ioannis', 'nationality': 'Greek', 'positions': 'DIAG'}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "dd['positions'] = ['DIAG','Data Science']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"first_name\": \"Ioannis\", \"positions\": [\"DIAG\", \"Data Science\"], \"nationality\": \"Greek\"}'"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json.dumps(dd)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "JSON supports multiple objects encoded as list of objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "lst = [{'first_name': 'Ioannis', 'nationality': 'Greek', 'positions': ['DIAG','Data Science', 'BioInformatics']},\n",
    "      {'first_name': 'Aris', 'nationality': 'Greek', 'positions': ['DIAG','Data Science']}]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[{\"first_name\": \"Ioannis\", \"nationality\": \"Greek\", \"positions\": [\"DIAG\", \"Data Science\", \"BioInformatics\"]}, {\"first_name\": \"Aris\", \"nationality\": \"Greek\", \"positions\": [\"DIAG\", \"Data Science\"]}]'"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json.dumps(lst)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PANDAS uses the [JSON encoder and decoder](https://docs.python.org/3/library/json.html) a standard python package as defined in [W3C School on JSON](https://www.w3schools.com/js/js_json_intro.asp). Two main methods are provided:\n",
    "- *dumps* to write/convert/unmarsal a PANDAS object into a JSON object.\n",
    "- *loads* to load/convert/marshal a JSON object into a Python Dictionary object.\n",
    "\n",
    "Requesting a single object is straight forward:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/lib/python3/dist-packages/ipykernel_launcher.py:1: FutureWarning: pandas.json is deprecated and will be removed in a future version.\n",
      "You can access dumps as pandas.io.json.dumps\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'{\"Insegna\":\"Casa e Appartamento per Vacanze \\\\\"Trastevere_house\\\\\"\",\"Classificazione\":\"Categoria 2\",\"Region\":\"Mun. XII\",\"Type\":\"Casa Vacanze NON impr (Appartamento)\",\"One-Bed-Rooms\":1.0,\"Two-Beds-Rooms\":null,\"Three-Beds-Rooms\":null,\"Four-Beds-Rooms\":null,\"Five-Beds-Rooms\":null,\"Total-Beds\":1.0,\"Address\":\"VIA FEDERICO ROSAZZA 52\"}'"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json.dumps(dataset2019.loc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However for multiple objects, pandas is not using a list of dictionaries. Insted it is creating a dictionary of lists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"Insegna\":{\"0\":\"Casa e Appartamento per Vacanze \\\\\"Trastevere_house\\\\\"\",\"1\":\"Casa e Appartamento per Vacanze \\\\\"VIVES 63\\\\\"\"},\"Classificazione\":{\"0\":\"Categoria 2\",\"1\":\"Categoria 1\"},\"Region\":{\"0\":\"Mun. XII\",\"1\":\"Mun. XII\"},\"Type\":{\"0\":\"Casa Vacanze NON impr (Appartamento)\",\"1\":\"Casa Vacanze NON impr (Appartamento)\"},\"One-Bed-Rooms\":{\"0\":1.0,\"1\":null},\"Two-Beds-Rooms\":{\"0\":null,\"1\":1.0},\"Three-Beds-Rooms\":{\"0\":null,\"1\":null},\"Four-Beds-Rooms\":{\"0\":null,\"1\":null},\"Five-Beds-Rooms\":{\"0\":null,\"1\":null},\"Total-Beds\":{\"0\":1.0,\"1\":2.0},\"Address\":{\"0\":\"VIA FEDERICO ROSAZZA 52\",\"1\":\"VIA DI MONTE VERDE 63\"}}'"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json.dumps(dataset2019.loc[0:1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So when we convert the JSON object back to a python object, we get a different ordering."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Address': {'0': 'VIA FEDERICO ROSAZZA 52', '1': 'VIA DI MONTE VERDE 63'},\n",
       " 'Classificazione': {'0': 'Categoria 2', '1': 'Categoria 1'},\n",
       " 'Five-Beds-Rooms': {'0': None, '1': None},\n",
       " 'Four-Beds-Rooms': {'0': None, '1': None},\n",
       " 'Insegna': {'0': 'Casa e Appartamento per Vacanze \"Trastevere_house\"',\n",
       "  '1': 'Casa e Appartamento per Vacanze \"VIVES 63\"'},\n",
       " 'One-Bed-Rooms': {'0': 1.0, '1': None},\n",
       " 'Region': {'0': 'Mun. XII', '1': 'Mun. XII'},\n",
       " 'Three-Beds-Rooms': {'0': None, '1': None},\n",
       " 'Total-Beds': {'0': 1.0, '1': 2.0},\n",
       " 'Two-Beds-Rooms': {'0': None, '1': 1.0},\n",
       " 'Type': {'0': 'Casa Vacanze NON impr (Appartamento)',\n",
       "  '1': 'Casa Vacanze NON impr (Appartamento)'}}"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json.loads(pd.json.dumps(dataset2019.loc[0:1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PANDAS provides the _to_json_ method that allows to choose a different orientation for the records. [Detailed examples are explained in the manual](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_json.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[{\"Insegna\":\"Casa e Appartamento per Vacanze \\\\\"Trastevere_house\\\\\"\",\"Classificazione\":\"Categoria 2\",\"Region\":\"Mun. XII\",\"Type\":\"Casa Vacanze NON impr (Appartamento)\",\"One-Bed-Rooms\":1.0,\"Two-Beds-Rooms\":null,\"Three-Beds-Rooms\":null,\"Four-Beds-Rooms\":null,\"Five-Beds-Rooms\":null,\"Total-Beds\":1.0,\"Address\":\"VIA FEDERICO ROSAZZA 52\"},{\"Insegna\":\"Casa e Appartamento per Vacanze \\\\\"VIVES 63\\\\\"\",\"Classificazione\":\"Categoria 1\",\"Region\":\"Mun. XII\",\"Type\":\"Casa Vacanze NON impr (Appartamento)\",\"One-Bed-Rooms\":null,\"Two-Beds-Rooms\":1.0,\"Three-Beds-Rooms\":null,\"Four-Beds-Rooms\":null,\"Five-Beds-Rooms\":null,\"Total-Beds\":2.0,\"Address\":\"VIA DI MONTE VERDE 63\"}]'"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset2019.loc[0:1].to_json(orient=\"records\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the index is not included in the conversion. For example consider the grouped set:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[{\"One-Bed-Rooms\":8560.0,\"Two-Beds-Rooms\":29234.0,\"Three-Beds-Rooms\":6535.0,\"Four-Beds-Rooms\":1679.0,\"Five-Beds-Rooms\":174.0,\"Total-Beds\":98098.0},{\"One-Bed-Rooms\":1459.0,\"Two-Beds-Rooms\":3801.0,\"Three-Beds-Rooms\":599.0,\"Four-Beds-Rooms\":133.0,\"Five-Beds-Rooms\":7.0,\"Total-Beds\":12464.0},{\"One-Bed-Rooms\":323.0,\"Two-Beds-Rooms\":691.0,\"Three-Beds-Rooms\":117.0,\"Four-Beds-Rooms\":17.0,\"Five-Beds-Rooms\":2.0,\"Total-Beds\":2385.0},{\"One-Bed-Rooms\":303.0,\"Two-Beds-Rooms\":646.0,\"Three-Beds-Rooms\":315.0,\"Four-Beds-Rooms\":19.0,\"Five-Beds-Rooms\":5.0,\"Total-Beds\":2853.0},{\"One-Bed-Rooms\":298.0,\"Two-Beds-Rooms\":2722.0,\"Three-Beds-Rooms\":291.0,\"Four-Beds-Rooms\":41.0,\"Five-Beds-Rooms\":2.0,\"Total-Beds\":7241.0},{\"One-Bed-Rooms\":270.0,\"Two-Beds-Rooms\":1070.0,\"Three-Beds-Rooms\":114.0,\"Four-Beds-Rooms\":33.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":3235.0},{\"One-Bed-Rooms\":200.0,\"Two-Beds-Rooms\":926.0,\"Three-Beds-Rooms\":115.0,\"Four-Beds-Rooms\":76.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":2894.0},{\"One-Bed-Rooms\":687.0,\"Two-Beds-Rooms\":2178.0,\"Three-Beds-Rooms\":301.0,\"Four-Beds-Rooms\":33.0,\"Five-Beds-Rooms\":1.0,\"Total-Beds\":6665.0},{\"One-Bed-Rooms\":258.0,\"Two-Beds-Rooms\":1056.0,\"Three-Beds-Rooms\":175.0,\"Four-Beds-Rooms\":28.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":3203.0},{\"One-Bed-Rooms\":339.0,\"Two-Beds-Rooms\":671.0,\"Three-Beds-Rooms\":40.0,\"Four-Beds-Rooms\":21.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":2606.0},{\"One-Bed-Rooms\":269.0,\"Two-Beds-Rooms\":2241.0,\"Three-Beds-Rooms\":77.0,\"Four-Beds-Rooms\":6.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":5305.0},{\"One-Bed-Rooms\":689.0,\"Two-Beds-Rooms\":2264.0,\"Three-Beds-Rooms\":362.0,\"Four-Beds-Rooms\":56.0,\"Five-Beds-Rooms\":11.0,\"Total-Beds\":7030.0},{\"One-Bed-Rooms\":1661.0,\"Two-Beds-Rooms\":4741.0,\"Three-Beds-Rooms\":680.0,\"Four-Beds-Rooms\":119.0,\"Five-Beds-Rooms\":8.0,\"Total-Beds\":14841.0},{\"One-Bed-Rooms\":696.0,\"Two-Beds-Rooms\":1521.0,\"Three-Beds-Rooms\":276.0,\"Four-Beds-Rooms\":42.0,\"Five-Beds-Rooms\":5.0,\"Total-Beds\":5365.0},{\"One-Bed-Rooms\":348.0,\"Two-Beds-Rooms\":879.0,\"Three-Beds-Rooms\":168.0,\"Four-Beds-Rooms\":35.0,\"Five-Beds-Rooms\":1.0,\"Total-Beds\":3011.0}]'"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "region_set2019.to_json(orient=\"records\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to be careful when using pandas to convert a dataframe into a JSON object. The representation of our data into a document depends on the actual tasks we need to carry out. The structure of the documents usually affect the performance of our code.\n",
    "\n",
    "One way to include the index in the JSON object is to change the index of the DataFrame as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "json_set2019 = region_set2019.reset_index(level=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>Region</th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>Five-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mun. I</td>\n",
       "      <td>8560.0</td>\n",
       "      <td>29234.0</td>\n",
       "      <td>6535.0</td>\n",
       "      <td>1679.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>98098.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mun. II</td>\n",
       "      <td>1459.0</td>\n",
       "      <td>3801.0</td>\n",
       "      <td>599.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>12464.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mun. III</td>\n",
       "      <td>323.0</td>\n",
       "      <td>691.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2385.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mun. IV</td>\n",
       "      <td>303.0</td>\n",
       "      <td>646.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2853.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mun. IX</td>\n",
       "      <td>298.0</td>\n",
       "      <td>2722.0</td>\n",
       "      <td>291.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7241.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mun. V</td>\n",
       "      <td>270.0</td>\n",
       "      <td>1070.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Mun. VI</td>\n",
       "      <td>200.0</td>\n",
       "      <td>926.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2894.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Mun. VII</td>\n",
       "      <td>687.0</td>\n",
       "      <td>2178.0</td>\n",
       "      <td>301.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6665.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Mun. VIII</td>\n",
       "      <td>258.0</td>\n",
       "      <td>1056.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3203.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Mun. X</td>\n",
       "      <td>339.0</td>\n",
       "      <td>671.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2606.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Mun. XI</td>\n",
       "      <td>269.0</td>\n",
       "      <td>2241.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5305.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Mun. XII</td>\n",
       "      <td>689.0</td>\n",
       "      <td>2264.0</td>\n",
       "      <td>362.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7030.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Mun. XIII</td>\n",
       "      <td>1661.0</td>\n",
       "      <td>4741.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>119.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>14841.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Mun. XIV</td>\n",
       "      <td>696.0</td>\n",
       "      <td>1521.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5365.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Mun. XV</td>\n",
       "      <td>348.0</td>\n",
       "      <td>879.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3011.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Region  One-Bed-Rooms  Two-Beds-Rooms  Three-Beds-Rooms  \\\n",
       "0      Mun. I         8560.0         29234.0            6535.0   \n",
       "1     Mun. II         1459.0          3801.0             599.0   \n",
       "2    Mun. III          323.0           691.0             117.0   \n",
       "3     Mun. IV          303.0           646.0             315.0   \n",
       "4     Mun. IX          298.0          2722.0             291.0   \n",
       "5      Mun. V          270.0          1070.0             114.0   \n",
       "6     Mun. VI          200.0           926.0             115.0   \n",
       "7    Mun. VII          687.0          2178.0             301.0   \n",
       "8   Mun. VIII          258.0          1056.0             175.0   \n",
       "9      Mun. X          339.0           671.0              40.0   \n",
       "10    Mun. XI          269.0          2241.0              77.0   \n",
       "11   Mun. XII          689.0          2264.0             362.0   \n",
       "12  Mun. XIII         1661.0          4741.0             680.0   \n",
       "13   Mun. XIV          696.0          1521.0             276.0   \n",
       "14    Mun. XV          348.0           879.0             168.0   \n",
       "\n",
       "    Four-Beds-Rooms  Five-Beds-Rooms  Total-Beds  \n",
       "0            1679.0            174.0     98098.0  \n",
       "1             133.0              7.0     12464.0  \n",
       "2              17.0              2.0      2385.0  \n",
       "3              19.0              5.0      2853.0  \n",
       "4              41.0              2.0      7241.0  \n",
       "5              33.0              0.0      3235.0  \n",
       "6              76.0              0.0      2894.0  \n",
       "7              33.0              1.0      6665.0  \n",
       "8              28.0              0.0      3203.0  \n",
       "9              21.0              0.0      2606.0  \n",
       "10              6.0              0.0      5305.0  \n",
       "11             56.0             11.0      7030.0  \n",
       "12            119.0              8.0     14841.0  \n",
       "13             42.0              5.0      5365.0  \n",
       "14             35.0              1.0      3011.0  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_set2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[{\"Region\":\"Mun. I\",\"One-Bed-Rooms\":8560.0,\"Two-Beds-Rooms\":29234.0,\"Three-Beds-Rooms\":6535.0,\"Four-Beds-Rooms\":1679.0,\"Five-Beds-Rooms\":174.0,\"Total-Beds\":98098.0},{\"Region\":\"Mun. II\",\"One-Bed-Rooms\":1459.0,\"Two-Beds-Rooms\":3801.0,\"Three-Beds-Rooms\":599.0,\"Four-Beds-Rooms\":133.0,\"Five-Beds-Rooms\":7.0,\"Total-Beds\":12464.0},{\"Region\":\"Mun. III\",\"One-Bed-Rooms\":323.0,\"Two-Beds-Rooms\":691.0,\"Three-Beds-Rooms\":117.0,\"Four-Beds-Rooms\":17.0,\"Five-Beds-Rooms\":2.0,\"Total-Beds\":2385.0},{\"Region\":\"Mun. IV\",\"One-Bed-Rooms\":303.0,\"Two-Beds-Rooms\":646.0,\"Three-Beds-Rooms\":315.0,\"Four-Beds-Rooms\":19.0,\"Five-Beds-Rooms\":5.0,\"Total-Beds\":2853.0},{\"Region\":\"Mun. IX\",\"One-Bed-Rooms\":298.0,\"Two-Beds-Rooms\":2722.0,\"Three-Beds-Rooms\":291.0,\"Four-Beds-Rooms\":41.0,\"Five-Beds-Rooms\":2.0,\"Total-Beds\":7241.0},{\"Region\":\"Mun. V\",\"One-Bed-Rooms\":270.0,\"Two-Beds-Rooms\":1070.0,\"Three-Beds-Rooms\":114.0,\"Four-Beds-Rooms\":33.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":3235.0},{\"Region\":\"Mun. VI\",\"One-Bed-Rooms\":200.0,\"Two-Beds-Rooms\":926.0,\"Three-Beds-Rooms\":115.0,\"Four-Beds-Rooms\":76.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":2894.0},{\"Region\":\"Mun. VII\",\"One-Bed-Rooms\":687.0,\"Two-Beds-Rooms\":2178.0,\"Three-Beds-Rooms\":301.0,\"Four-Beds-Rooms\":33.0,\"Five-Beds-Rooms\":1.0,\"Total-Beds\":6665.0},{\"Region\":\"Mun. VIII\",\"One-Bed-Rooms\":258.0,\"Two-Beds-Rooms\":1056.0,\"Three-Beds-Rooms\":175.0,\"Four-Beds-Rooms\":28.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":3203.0},{\"Region\":\"Mun. X\",\"One-Bed-Rooms\":339.0,\"Two-Beds-Rooms\":671.0,\"Three-Beds-Rooms\":40.0,\"Four-Beds-Rooms\":21.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":2606.0},{\"Region\":\"Mun. XI\",\"One-Bed-Rooms\":269.0,\"Two-Beds-Rooms\":2241.0,\"Three-Beds-Rooms\":77.0,\"Four-Beds-Rooms\":6.0,\"Five-Beds-Rooms\":0.0,\"Total-Beds\":5305.0},{\"Region\":\"Mun. XII\",\"One-Bed-Rooms\":689.0,\"Two-Beds-Rooms\":2264.0,\"Three-Beds-Rooms\":362.0,\"Four-Beds-Rooms\":56.0,\"Five-Beds-Rooms\":11.0,\"Total-Beds\":7030.0},{\"Region\":\"Mun. XIII\",\"One-Bed-Rooms\":1661.0,\"Two-Beds-Rooms\":4741.0,\"Three-Beds-Rooms\":680.0,\"Four-Beds-Rooms\":119.0,\"Five-Beds-Rooms\":8.0,\"Total-Beds\":14841.0},{\"Region\":\"Mun. XIV\",\"One-Bed-Rooms\":696.0,\"Two-Beds-Rooms\":1521.0,\"Three-Beds-Rooms\":276.0,\"Four-Beds-Rooms\":42.0,\"Five-Beds-Rooms\":5.0,\"Total-Beds\":5365.0},{\"Region\":\"Mun. XV\",\"One-Bed-Rooms\":348.0,\"Two-Beds-Rooms\":879.0,\"Three-Beds-Rooms\":168.0,\"Four-Beds-Rooms\":35.0,\"Five-Beds-Rooms\":1.0,\"Total-Beds\":3011.0}]'"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_set2019.to_json(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "str = json_set2019.to_json(orient='records')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's examine the reverse path, that is, from the JSON object back to a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict = pd.json.loads(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Five-Beds-Rooms': 174.0,\n",
       "  'Four-Beds-Rooms': 1679.0,\n",
       "  'One-Bed-Rooms': 8560.0,\n",
       "  'Region': 'Mun. I',\n",
       "  'Three-Beds-Rooms': 6535.0,\n",
       "  'Total-Beds': 98098.0,\n",
       "  'Two-Beds-Rooms': 29234.0},\n",
       " {'Five-Beds-Rooms': 7.0,\n",
       "  'Four-Beds-Rooms': 133.0,\n",
       "  'One-Bed-Rooms': 1459.0,\n",
       "  'Region': 'Mun. II',\n",
       "  'Three-Beds-Rooms': 599.0,\n",
       "  'Total-Beds': 12464.0,\n",
       "  'Two-Beds-Rooms': 3801.0},\n",
       " {'Five-Beds-Rooms': 2.0,\n",
       "  'Four-Beds-Rooms': 17.0,\n",
       "  'One-Bed-Rooms': 323.0,\n",
       "  'Region': 'Mun. III',\n",
       "  'Three-Beds-Rooms': 117.0,\n",
       "  'Total-Beds': 2385.0,\n",
       "  'Two-Beds-Rooms': 691.0},\n",
       " {'Five-Beds-Rooms': 5.0,\n",
       "  'Four-Beds-Rooms': 19.0,\n",
       "  'One-Bed-Rooms': 303.0,\n",
       "  'Region': 'Mun. IV',\n",
       "  'Three-Beds-Rooms': 315.0,\n",
       "  'Total-Beds': 2853.0,\n",
       "  'Two-Beds-Rooms': 646.0},\n",
       " {'Five-Beds-Rooms': 2.0,\n",
       "  'Four-Beds-Rooms': 41.0,\n",
       "  'One-Bed-Rooms': 298.0,\n",
       "  'Region': 'Mun. IX',\n",
       "  'Three-Beds-Rooms': 291.0,\n",
       "  'Total-Beds': 7241.0,\n",
       "  'Two-Beds-Rooms': 2722.0},\n",
       " {'Five-Beds-Rooms': 0.0,\n",
       "  'Four-Beds-Rooms': 33.0,\n",
       "  'One-Bed-Rooms': 270.0,\n",
       "  'Region': 'Mun. V',\n",
       "  'Three-Beds-Rooms': 114.0,\n",
       "  'Total-Beds': 3235.0,\n",
       "  'Two-Beds-Rooms': 1070.0},\n",
       " {'Five-Beds-Rooms': 0.0,\n",
       "  'Four-Beds-Rooms': 76.0,\n",
       "  'One-Bed-Rooms': 200.0,\n",
       "  'Region': 'Mun. VI',\n",
       "  'Three-Beds-Rooms': 115.0,\n",
       "  'Total-Beds': 2894.0,\n",
       "  'Two-Beds-Rooms': 926.0},\n",
       " {'Five-Beds-Rooms': 1.0,\n",
       "  'Four-Beds-Rooms': 33.0,\n",
       "  'One-Bed-Rooms': 687.0,\n",
       "  'Region': 'Mun. VII',\n",
       "  'Three-Beds-Rooms': 301.0,\n",
       "  'Total-Beds': 6665.0,\n",
       "  'Two-Beds-Rooms': 2178.0},\n",
       " {'Five-Beds-Rooms': 0.0,\n",
       "  'Four-Beds-Rooms': 28.0,\n",
       "  'One-Bed-Rooms': 258.0,\n",
       "  'Region': 'Mun. VIII',\n",
       "  'Three-Beds-Rooms': 175.0,\n",
       "  'Total-Beds': 3203.0,\n",
       "  'Two-Beds-Rooms': 1056.0},\n",
       " {'Five-Beds-Rooms': 0.0,\n",
       "  'Four-Beds-Rooms': 21.0,\n",
       "  'One-Bed-Rooms': 339.0,\n",
       "  'Region': 'Mun. X',\n",
       "  'Three-Beds-Rooms': 40.0,\n",
       "  'Total-Beds': 2606.0,\n",
       "  'Two-Beds-Rooms': 671.0},\n",
       " {'Five-Beds-Rooms': 0.0,\n",
       "  'Four-Beds-Rooms': 6.0,\n",
       "  'One-Bed-Rooms': 269.0,\n",
       "  'Region': 'Mun. XI',\n",
       "  'Three-Beds-Rooms': 77.0,\n",
       "  'Total-Beds': 5305.0,\n",
       "  'Two-Beds-Rooms': 2241.0},\n",
       " {'Five-Beds-Rooms': 11.0,\n",
       "  'Four-Beds-Rooms': 56.0,\n",
       "  'One-Bed-Rooms': 689.0,\n",
       "  'Region': 'Mun. XII',\n",
       "  'Three-Beds-Rooms': 362.0,\n",
       "  'Total-Beds': 7030.0,\n",
       "  'Two-Beds-Rooms': 2264.0},\n",
       " {'Five-Beds-Rooms': 8.0,\n",
       "  'Four-Beds-Rooms': 119.0,\n",
       "  'One-Bed-Rooms': 1661.0,\n",
       "  'Region': 'Mun. XIII',\n",
       "  'Three-Beds-Rooms': 680.0,\n",
       "  'Total-Beds': 14841.0,\n",
       "  'Two-Beds-Rooms': 4741.0},\n",
       " {'Five-Beds-Rooms': 5.0,\n",
       "  'Four-Beds-Rooms': 42.0,\n",
       "  'One-Bed-Rooms': 696.0,\n",
       "  'Region': 'Mun. XIV',\n",
       "  'Three-Beds-Rooms': 276.0,\n",
       "  'Total-Beds': 5365.0,\n",
       "  'Two-Beds-Rooms': 1521.0},\n",
       " {'Five-Beds-Rooms': 1.0,\n",
       "  'Four-Beds-Rooms': 35.0,\n",
       "  'One-Bed-Rooms': 348.0,\n",
       "  'Region': 'Mun. XV',\n",
       "  'Three-Beds-Rooms': 168.0,\n",
       "  'Total-Beds': 3011.0,\n",
       "  'Two-Beds-Rooms': 879.0}]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Five-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Region</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>174.0</td>\n",
       "      <td>1679.0</td>\n",
       "      <td>8560.0</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>6535.0</td>\n",
       "      <td>98098.0</td>\n",
       "      <td>29234.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>1459.0</td>\n",
       "      <td>Mun. II</td>\n",
       "      <td>599.0</td>\n",
       "      <td>12464.0</td>\n",
       "      <td>3801.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>Mun. III</td>\n",
       "      <td>117.0</td>\n",
       "      <td>2385.0</td>\n",
       "      <td>691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>Mun. IV</td>\n",
       "      <td>315.0</td>\n",
       "      <td>2853.0</td>\n",
       "      <td>646.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>298.0</td>\n",
       "      <td>Mun. IX</td>\n",
       "      <td>291.0</td>\n",
       "      <td>7241.0</td>\n",
       "      <td>2722.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>Mun. V</td>\n",
       "      <td>114.0</td>\n",
       "      <td>3235.0</td>\n",
       "      <td>1070.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>Mun. VI</td>\n",
       "      <td>115.0</td>\n",
       "      <td>2894.0</td>\n",
       "      <td>926.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>687.0</td>\n",
       "      <td>Mun. VII</td>\n",
       "      <td>301.0</td>\n",
       "      <td>6665.0</td>\n",
       "      <td>2178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>258.0</td>\n",
       "      <td>Mun. VIII</td>\n",
       "      <td>175.0</td>\n",
       "      <td>3203.0</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>339.0</td>\n",
       "      <td>Mun. X</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2606.0</td>\n",
       "      <td>671.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>269.0</td>\n",
       "      <td>Mun. XI</td>\n",
       "      <td>77.0</td>\n",
       "      <td>5305.0</td>\n",
       "      <td>2241.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>Mun. XII</td>\n",
       "      <td>362.0</td>\n",
       "      <td>7030.0</td>\n",
       "      <td>2264.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8.0</td>\n",
       "      <td>119.0</td>\n",
       "      <td>1661.0</td>\n",
       "      <td>Mun. XIII</td>\n",
       "      <td>680.0</td>\n",
       "      <td>14841.0</td>\n",
       "      <td>4741.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>5.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>696.0</td>\n",
       "      <td>Mun. XIV</td>\n",
       "      <td>276.0</td>\n",
       "      <td>5365.0</td>\n",
       "      <td>1521.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>348.0</td>\n",
       "      <td>Mun. XV</td>\n",
       "      <td>168.0</td>\n",
       "      <td>3011.0</td>\n",
       "      <td>879.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Five-Beds-Rooms  Four-Beds-Rooms  One-Bed-Rooms     Region  \\\n",
       "0             174.0           1679.0         8560.0     Mun. I   \n",
       "1               7.0            133.0         1459.0    Mun. II   \n",
       "2               2.0             17.0          323.0   Mun. III   \n",
       "3               5.0             19.0          303.0    Mun. IV   \n",
       "4               2.0             41.0          298.0    Mun. IX   \n",
       "5               0.0             33.0          270.0     Mun. V   \n",
       "6               0.0             76.0          200.0    Mun. VI   \n",
       "7               1.0             33.0          687.0   Mun. VII   \n",
       "8               0.0             28.0          258.0  Mun. VIII   \n",
       "9               0.0             21.0          339.0     Mun. X   \n",
       "10              0.0              6.0          269.0    Mun. XI   \n",
       "11             11.0             56.0          689.0   Mun. XII   \n",
       "12              8.0            119.0         1661.0  Mun. XIII   \n",
       "13              5.0             42.0          696.0   Mun. XIV   \n",
       "14              1.0             35.0          348.0    Mun. XV   \n",
       "\n",
       "    Three-Beds-Rooms  Total-Beds  Two-Beds-Rooms  \n",
       "0             6535.0     98098.0         29234.0  \n",
       "1              599.0     12464.0          3801.0  \n",
       "2              117.0      2385.0           691.0  \n",
       "3              315.0      2853.0           646.0  \n",
       "4              291.0      7241.0          2722.0  \n",
       "5              114.0      3235.0          1070.0  \n",
       "6              115.0      2894.0           926.0  \n",
       "7              301.0      6665.0          2178.0  \n",
       "8              175.0      3203.0          1056.0  \n",
       "9               40.0      2606.0           671.0  \n",
       "10              77.0      5305.0          2241.0  \n",
       "11             362.0      7030.0          2264.0  \n",
       "12             680.0     14841.0          4741.0  \n",
       "13             276.0      5365.0          1521.0  \n",
       "14             168.0      3011.0           879.0  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>Five-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mun. I</th>\n",
       "      <td>174.0</td>\n",
       "      <td>1679.0</td>\n",
       "      <td>8560.0</td>\n",
       "      <td>6535.0</td>\n",
       "      <td>98098.0</td>\n",
       "      <td>29234.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. II</th>\n",
       "      <td>7.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>1459.0</td>\n",
       "      <td>599.0</td>\n",
       "      <td>12464.0</td>\n",
       "      <td>3801.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. III</th>\n",
       "      <td>2.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>323.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>2385.0</td>\n",
       "      <td>691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. IV</th>\n",
       "      <td>5.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>2853.0</td>\n",
       "      <td>646.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. IX</th>\n",
       "      <td>2.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>298.0</td>\n",
       "      <td>291.0</td>\n",
       "      <td>7241.0</td>\n",
       "      <td>2722.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. V</th>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>3235.0</td>\n",
       "      <td>1070.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VI</th>\n",
       "      <td>0.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>2894.0</td>\n",
       "      <td>926.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VII</th>\n",
       "      <td>1.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>687.0</td>\n",
       "      <td>301.0</td>\n",
       "      <td>6665.0</td>\n",
       "      <td>2178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. VIII</th>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>258.0</td>\n",
       "      <td>175.0</td>\n",
       "      <td>3203.0</td>\n",
       "      <td>1056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. X</th>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>339.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2606.0</td>\n",
       "      <td>671.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XI</th>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>269.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>5305.0</td>\n",
       "      <td>2241.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XII</th>\n",
       "      <td>11.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>362.0</td>\n",
       "      <td>7030.0</td>\n",
       "      <td>2264.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XIII</th>\n",
       "      <td>8.0</td>\n",
       "      <td>119.0</td>\n",
       "      <td>1661.0</td>\n",
       "      <td>680.0</td>\n",
       "      <td>14841.0</td>\n",
       "      <td>4741.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XIV</th>\n",
       "      <td>5.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>696.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>5365.0</td>\n",
       "      <td>1521.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. XV</th>\n",
       "      <td>1.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>348.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>3011.0</td>\n",
       "      <td>879.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Five-Beds-Rooms  Four-Beds-Rooms  One-Bed-Rooms  Three-Beds-Rooms  \\\n",
       "Region                                                                         \n",
       "Mun. I               174.0           1679.0         8560.0            6535.0   \n",
       "Mun. II                7.0            133.0         1459.0             599.0   \n",
       "Mun. III               2.0             17.0          323.0             117.0   \n",
       "Mun. IV                5.0             19.0          303.0             315.0   \n",
       "Mun. IX                2.0             41.0          298.0             291.0   \n",
       "Mun. V                 0.0             33.0          270.0             114.0   \n",
       "Mun. VI                0.0             76.0          200.0             115.0   \n",
       "Mun. VII               1.0             33.0          687.0             301.0   \n",
       "Mun. VIII              0.0             28.0          258.0             175.0   \n",
       "Mun. X                 0.0             21.0          339.0              40.0   \n",
       "Mun. XI                0.0              6.0          269.0              77.0   \n",
       "Mun. XII              11.0             56.0          689.0             362.0   \n",
       "Mun. XIII              8.0            119.0         1661.0             680.0   \n",
       "Mun. XIV               5.0             42.0          696.0             276.0   \n",
       "Mun. XV                1.0             35.0          348.0             168.0   \n",
       "\n",
       "           Total-Beds  Two-Beds-Rooms  \n",
       "Region                                 \n",
       "Mun. I        98098.0         29234.0  \n",
       "Mun. II       12464.0          3801.0  \n",
       "Mun. III       2385.0           691.0  \n",
       "Mun. IV        2853.0           646.0  \n",
       "Mun. IX        7241.0          2722.0  \n",
       "Mun. V         3235.0          1070.0  \n",
       "Mun. VI        2894.0           926.0  \n",
       "Mun. VII       6665.0          2178.0  \n",
       "Mun. VIII      3203.0          1056.0  \n",
       "Mun. X         2606.0           671.0  \n",
       "Mun. XI        5305.0          2241.0  \n",
       "Mun. XII       7030.0          2264.0  \n",
       "Mun. XIII     14841.0          4741.0  \n",
       "Mun. XIV       5365.0          1521.0  \n",
       "Mun. XV        3011.0           879.0  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(dict).set_index(keys='Region')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Local Mongo\n",
    "\n",
    "We will use the pymongo library to interact with MongoDB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pymongo import MongoClient"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we need to establish a connection with the MongoDB server available on our local machine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = MongoClient('localhost', 27017)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The second step is to create a new Database. pymongo is offering a very easy approach to create a new database. The interface is similar to that of the dictionary data type, where we use the name of the database similar to the way we use the key of an entry in the dictionary data type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = client[\"Hotels\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the database is not created yet. The actual operation is anticipated until the actual moment when it is absolutely necessary to take place."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'Hotels')"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A database is capable of holding one or more collections. We can create collections by introducing a name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "collection = db.Regions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, the operation of creating a collection is delayed until the very last moment that is needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Collection(Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'Hotels'), 'Regions')"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "collection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the collection is ready, we can insert our data.\n",
    "\n",
    "The method _insert_many_ is expecting a Dictionary like the one we created above under the **dict** variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertManyResult at 0x7fd8e63b5f48>"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "collection.insert_many(dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The _find_ method is used to access the entries of a collection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "entries = collection.find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('5d9cdaa295f9546674a26f02'), 'Region': 'Mun. I', 'One-Bed-Rooms': 8560.0, 'Two-Beds-Rooms': 29234.0, 'Three-Beds-Rooms': 6535.0, 'Four-Beds-Rooms': 1679.0, 'Five-Beds-Rooms': 174.0, 'Total-Beds': 98098.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f03'), 'Region': 'Mun. II', 'One-Bed-Rooms': 1459.0, 'Two-Beds-Rooms': 3801.0, 'Three-Beds-Rooms': 599.0, 'Four-Beds-Rooms': 133.0, 'Five-Beds-Rooms': 7.0, 'Total-Beds': 12464.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f04'), 'Region': 'Mun. III', 'One-Bed-Rooms': 323.0, 'Two-Beds-Rooms': 691.0, 'Three-Beds-Rooms': 117.0, 'Four-Beds-Rooms': 17.0, 'Five-Beds-Rooms': 2.0, 'Total-Beds': 2385.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f05'), 'Region': 'Mun. IV', 'One-Bed-Rooms': 303.0, 'Two-Beds-Rooms': 646.0, 'Three-Beds-Rooms': 315.0, 'Four-Beds-Rooms': 19.0, 'Five-Beds-Rooms': 5.0, 'Total-Beds': 2853.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f06'), 'Region': 'Mun. IX', 'One-Bed-Rooms': 298.0, 'Two-Beds-Rooms': 2722.0, 'Three-Beds-Rooms': 291.0, 'Four-Beds-Rooms': 41.0, 'Five-Beds-Rooms': 2.0, 'Total-Beds': 7241.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f07'), 'Region': 'Mun. V', 'One-Bed-Rooms': 270.0, 'Two-Beds-Rooms': 1070.0, 'Three-Beds-Rooms': 114.0, 'Four-Beds-Rooms': 33.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 3235.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f08'), 'Region': 'Mun. VI', 'One-Bed-Rooms': 200.0, 'Two-Beds-Rooms': 926.0, 'Three-Beds-Rooms': 115.0, 'Four-Beds-Rooms': 76.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 2894.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f09'), 'Region': 'Mun. VII', 'One-Bed-Rooms': 687.0, 'Two-Beds-Rooms': 2178.0, 'Three-Beds-Rooms': 301.0, 'Four-Beds-Rooms': 33.0, 'Five-Beds-Rooms': 1.0, 'Total-Beds': 6665.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0a'), 'Region': 'Mun. VIII', 'One-Bed-Rooms': 258.0, 'Two-Beds-Rooms': 1056.0, 'Three-Beds-Rooms': 175.0, 'Four-Beds-Rooms': 28.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 3203.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0b'), 'Region': 'Mun. X', 'One-Bed-Rooms': 339.0, 'Two-Beds-Rooms': 671.0, 'Three-Beds-Rooms': 40.0, 'Four-Beds-Rooms': 21.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 2606.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0c'), 'Region': 'Mun. XI', 'One-Bed-Rooms': 269.0, 'Two-Beds-Rooms': 2241.0, 'Three-Beds-Rooms': 77.0, 'Four-Beds-Rooms': 6.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 5305.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0d'), 'Region': 'Mun. XII', 'One-Bed-Rooms': 689.0, 'Two-Beds-Rooms': 2264.0, 'Three-Beds-Rooms': 362.0, 'Four-Beds-Rooms': 56.0, 'Five-Beds-Rooms': 11.0, 'Total-Beds': 7030.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0e'), 'Region': 'Mun. XIII', 'One-Bed-Rooms': 1661.0, 'Two-Beds-Rooms': 4741.0, 'Three-Beds-Rooms': 680.0, 'Four-Beds-Rooms': 119.0, 'Five-Beds-Rooms': 8.0, 'Total-Beds': 14841.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0f'), 'Region': 'Mun. XIV', 'One-Bed-Rooms': 696.0, 'Two-Beds-Rooms': 1521.0, 'Three-Beds-Rooms': 276.0, 'Four-Beds-Rooms': 42.0, 'Five-Beds-Rooms': 5.0, 'Total-Beds': 5365.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f10'), 'Region': 'Mun. XV', 'One-Bed-Rooms': 348.0, 'Two-Beds-Rooms': 879.0, 'Three-Beds-Rooms': 168.0, 'Four-Beds-Rooms': 35.0, 'Five-Beds-Rooms': 1.0, 'Total-Beds': 3011.0}\n"
     ]
    }
   ],
   "source": [
    "for item in entries:\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MongoDB provides a special syntax in order to narrow down the items retuned by the **find** method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('5d9cdaa295f9546674a26f02'), 'Region': 'Mun. I', 'One-Bed-Rooms': 8560.0, 'Two-Beds-Rooms': 29234.0, 'Three-Beds-Rooms': 6535.0, 'Four-Beds-Rooms': 1679.0, 'Five-Beds-Rooms': 174.0, 'Total-Beds': 98098.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f03'), 'Region': 'Mun. II', 'One-Bed-Rooms': 1459.0, 'Two-Beds-Rooms': 3801.0, 'Three-Beds-Rooms': 599.0, 'Four-Beds-Rooms': 133.0, 'Five-Beds-Rooms': 7.0, 'Total-Beds': 12464.0}\n"
     ]
    }
   ],
   "source": [
    "for item in collection.find({ \"$or\": [{\"Region\" : \"Mun. I\"}, {\"Region\" : \"Mun. II\"}] }):\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The results of the **find** method can be easily converted into a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_region_2019 = pd.DataFrame(list(collection.find({ \"$or\": [{\"Region\" : \"Mun. I\"}, {\"Region\" : \"Mun. II\"}] })))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>Five-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Region</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "      <th>_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>174.0</td>\n",
       "      <td>1679.0</td>\n",
       "      <td>8560.0</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>6535.0</td>\n",
       "      <td>98098.0</td>\n",
       "      <td>29234.0</td>\n",
       "      <td>5d9cdaa295f9546674a26f02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>1459.0</td>\n",
       "      <td>Mun. II</td>\n",
       "      <td>599.0</td>\n",
       "      <td>12464.0</td>\n",
       "      <td>3801.0</td>\n",
       "      <td>5d9cdaa295f9546674a26f03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Five-Beds-Rooms  Four-Beds-Rooms  One-Bed-Rooms   Region  Three-Beds-Rooms  \\\n",
       "0            174.0           1679.0         8560.0   Mun. I            6535.0   \n",
       "1              7.0            133.0         1459.0  Mun. II             599.0   \n",
       "\n",
       "   Total-Beds  Two-Beds-Rooms                       _id  \n",
       "0     98098.0         29234.0  5d9cdaa295f9546674a26f02  \n",
       "1     12464.0          3801.0  5d9cdaa295f9546674a26f03  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_region_2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_region_2019.drop(columns='_id', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>Five-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Region</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>174.0</td>\n",
       "      <td>1679.0</td>\n",
       "      <td>8560.0</td>\n",
       "      <td>Mun. I</td>\n",
       "      <td>6535.0</td>\n",
       "      <td>98098.0</td>\n",
       "      <td>29234.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>1459.0</td>\n",
       "      <td>Mun. II</td>\n",
       "      <td>599.0</td>\n",
       "      <td>12464.0</td>\n",
       "      <td>3801.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Five-Beds-Rooms  Four-Beds-Rooms  One-Bed-Rooms   Region  Three-Beds-Rooms  \\\n",
       "0            174.0           1679.0         8560.0   Mun. I            6535.0   \n",
       "1              7.0            133.0         1459.0  Mun. II             599.0   \n",
       "\n",
       "   Total-Beds  Two-Beds-Rooms  \n",
       "0     98098.0         29234.0  \n",
       "1     12464.0          3801.0  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_region_2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_region_2019.set_index(keys='Region', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>Five-Beds-Rooms</th>\n",
       "      <th>Four-Beds-Rooms</th>\n",
       "      <th>One-Bed-Rooms</th>\n",
       "      <th>Three-Beds-Rooms</th>\n",
       "      <th>Total-Beds</th>\n",
       "      <th>Two-Beds-Rooms</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mun. I</th>\n",
       "      <td>174.0</td>\n",
       "      <td>1679.0</td>\n",
       "      <td>8560.0</td>\n",
       "      <td>6535.0</td>\n",
       "      <td>98098.0</td>\n",
       "      <td>29234.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mun. II</th>\n",
       "      <td>7.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>1459.0</td>\n",
       "      <td>599.0</td>\n",
       "      <td>12464.0</td>\n",
       "      <td>3801.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Five-Beds-Rooms  Four-Beds-Rooms  One-Bed-Rooms  Three-Beds-Rooms  \\\n",
       "Region                                                                       \n",
       "Mun. I             174.0           1679.0         8560.0            6535.0   \n",
       "Mun. II              7.0            133.0         1459.0             599.0   \n",
       "\n",
       "         Total-Beds  Two-Beds-Rooms  \n",
       "Region                               \n",
       "Mun. I      98098.0         29234.0  \n",
       "Mun. II     12464.0          3801.0  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_region_2019"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The same query syntax is used to delete records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.DeleteResult at 0x7fd8bad9eb88>"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "collection.delete_many({ \"$or\": [{\"Region\" : \"Mun. I\"}, {\"Region\" : \"Mun. II\"}] })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('5d9cdaa295f9546674a26f04'), 'Region': 'Mun. III', 'One-Bed-Rooms': 323.0, 'Two-Beds-Rooms': 691.0, 'Three-Beds-Rooms': 117.0, 'Four-Beds-Rooms': 17.0, 'Five-Beds-Rooms': 2.0, 'Total-Beds': 2385.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f05'), 'Region': 'Mun. IV', 'One-Bed-Rooms': 303.0, 'Two-Beds-Rooms': 646.0, 'Three-Beds-Rooms': 315.0, 'Four-Beds-Rooms': 19.0, 'Five-Beds-Rooms': 5.0, 'Total-Beds': 2853.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f06'), 'Region': 'Mun. IX', 'One-Bed-Rooms': 298.0, 'Two-Beds-Rooms': 2722.0, 'Three-Beds-Rooms': 291.0, 'Four-Beds-Rooms': 41.0, 'Five-Beds-Rooms': 2.0, 'Total-Beds': 7241.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f07'), 'Region': 'Mun. V', 'One-Bed-Rooms': 270.0, 'Two-Beds-Rooms': 1070.0, 'Three-Beds-Rooms': 114.0, 'Four-Beds-Rooms': 33.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 3235.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f08'), 'Region': 'Mun. VI', 'One-Bed-Rooms': 200.0, 'Two-Beds-Rooms': 926.0, 'Three-Beds-Rooms': 115.0, 'Four-Beds-Rooms': 76.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 2894.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f09'), 'Region': 'Mun. VII', 'One-Bed-Rooms': 687.0, 'Two-Beds-Rooms': 2178.0, 'Three-Beds-Rooms': 301.0, 'Four-Beds-Rooms': 33.0, 'Five-Beds-Rooms': 1.0, 'Total-Beds': 6665.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0a'), 'Region': 'Mun. VIII', 'One-Bed-Rooms': 258.0, 'Two-Beds-Rooms': 1056.0, 'Three-Beds-Rooms': 175.0, 'Four-Beds-Rooms': 28.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 3203.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0b'), 'Region': 'Mun. X', 'One-Bed-Rooms': 339.0, 'Two-Beds-Rooms': 671.0, 'Three-Beds-Rooms': 40.0, 'Four-Beds-Rooms': 21.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 2606.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0c'), 'Region': 'Mun. XI', 'One-Bed-Rooms': 269.0, 'Two-Beds-Rooms': 2241.0, 'Three-Beds-Rooms': 77.0, 'Four-Beds-Rooms': 6.0, 'Five-Beds-Rooms': 0.0, 'Total-Beds': 5305.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0d'), 'Region': 'Mun. XII', 'One-Bed-Rooms': 689.0, 'Two-Beds-Rooms': 2264.0, 'Three-Beds-Rooms': 362.0, 'Four-Beds-Rooms': 56.0, 'Five-Beds-Rooms': 11.0, 'Total-Beds': 7030.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0e'), 'Region': 'Mun. XIII', 'One-Bed-Rooms': 1661.0, 'Two-Beds-Rooms': 4741.0, 'Three-Beds-Rooms': 680.0, 'Four-Beds-Rooms': 119.0, 'Five-Beds-Rooms': 8.0, 'Total-Beds': 14841.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f0f'), 'Region': 'Mun. XIV', 'One-Bed-Rooms': 696.0, 'Two-Beds-Rooms': 1521.0, 'Three-Beds-Rooms': 276.0, 'Four-Beds-Rooms': 42.0, 'Five-Beds-Rooms': 5.0, 'Total-Beds': 5365.0}\n",
      "{'_id': ObjectId('5d9cdaa295f9546674a26f10'), 'Region': 'Mun. XV', 'One-Bed-Rooms': 348.0, 'Two-Beds-Rooms': 879.0, 'Three-Beds-Rooms': 168.0, 'Four-Beds-Rooms': 35.0, 'Five-Beds-Rooms': 1.0, 'Total-Beds': 3011.0}\n"
     ]
    }
   ],
   "source": [
    "for item in collection.find():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The same syntax is used to update the records within the collection. An update can also introduce new keys."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.UpdateResult at 0x7fd8e636ca88>"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myquery = {\"Region\" : \"Mun. XV\"}\n",
    "newvalues = { \"$set\": { 'One-BXV-Rooms': 4000, 'One-Bed-Rooms': 339.0 } }\n",
    "\n",
    "collection.update_one(myquery, newvalues)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Five-Beds-Rooms': 1.0,\n",
       "  'Four-Beds-Rooms': 35.0,\n",
       "  'One-BXV-Rooms': 4000,\n",
       "  'One-Bed-Rooms': 339.0,\n",
       "  'Region': 'Mun. XV',\n",
       "  'Three-Beds-Rooms': 168.0,\n",
       "  'Total-Beds': 3011.0,\n",
       "  'Two-Beds-Rooms': 879.0,\n",
       "  '_id': ObjectId('5d9cdaa295f9546674a26f10')}]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(collection.find({\"Region\" : \"Mun. XV\"}))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also drop all the collection in one step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "collection.drop()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database-as-a-Service: mLAB\n",
    "\n",
    "We now wish to store the contents of the dataset into a document database where each row becomes a separate document. We will use the [MongoDB](https://www.mongodb.com/what-is-mongodb) and the Database-as-a-service provider [mLab](https://mlab.com/).\n",
    "\n",
    "Using the web interface provided by mLab create a new account under mLab.\n",
    "\n",
    "Now we need to create a new Database through the [mLab web UI wizard](https://mlab.com/create/wizard). \n",
    "\n",
    "1. Choose the SANDBOX plan type, which is free for storing up to 500MB of data and click on the button *Continue*.\n",
    "2. Choose the Region where the new database will be located (e.g., Europe) and click on the button *Continue*.\n",
    "3. Give a name to the database name (e.g., *adm2019*) and click on the button *Continue*.\n",
    "4. The final page displays the details of the new database. Click *Submit Order* to create the new database.\n",
    "\n",
    "Select the new database. Within the new page you will get a help window on the connection string of the new database.\n",
    "\n",
    "Go to **user tab** and create a new user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = MongoClient(\"ds331758.mlab.com\", 31758)\n",
    "db = connection[\"adm2019\"]\n",
    "db.authenticate(\"dbuser\", \"pass123\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "coll2018 = db.Regions2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "json_set2018 = region_set2018.reset_index(level=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "str2018 = json_set2018.to_json(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict2018 = json.loads(str2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertManyResult at 0x7fd8e63dcb08>"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "coll2018.insert_many(dict2018)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The rest of the operations are exaclty the same."
   ]
  }
 ],
 "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": 2
}