2. Ploting information in Maps.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sabadell cultural events in a map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Open Data Sabadell also we can find the agenda of events that take place in the city, organized by categories (science and tecnology, art, music, traditions, etc.) and can be donwloded easilly from [here.](http://opendata.sabadell.cat/ca/inici/fitxes-cataleg?option=com_iasmetadadesarticles&cod=OD,CT-03-&title=Cultura%20i%20oci) \n",
    "\n",
    "In our case, we already downloaded the agendas in XML format related to science and tecnology, art and music. If you take a look to these files you can observe that every event have different fields. We are going to use location (longitude and latitude) and some description fields to plot them in a map filtered by a date.  \n",
    "\n",
    "Try to understand what following code do, try to modify data value (**data = \"1/2/2019 18:00\"**), modify data showed in the map and modify indicator maps color (**for type,color in [(musica,'blue'),(art,'red'),(ciencia,'green')]:**)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import folium\n",
    "import xml.etree.ElementTree as ET\n",
    "from datetime import datetime as dt\n",
    "\n",
    "ciencia = ET.parse('DadesSabadell/cienciaitecnologia2019.xml')\n",
    "art = ET.parse('DadesSabadell/artipatrimoni2019.xml')\n",
    "musica = ET.parse('DadesSabadell/musica2019.xml')\n",
    "\n",
    "map = folium.Map(location=[41.54329, 2.10942],zoom_start=13)\n",
    "\n",
    "data = \"1/2/2019 18:00\"\n",
    "data_input = dt.strptime(data, \"%d/%m/%Y %H:%M\")\n",
    "\n",
    "for type,color in [(musica,'blue'),(art,'red'),(ciencia,'green')]:\n",
    "    \n",
    "    root = type.getroot()\n",
    "\n",
    "    for activitat in root.findall('activitat'):\n",
    "    \n",
    "        data = activitat.find('dataInici').text\n",
    "        data_event = dt.strptime(data,\"%d/%m/%Y %H:%M\")\n",
    "    \n",
    "        if activitat.find('.//lon') != None and data_input < data_event:\n",
    "            \n",
    "            titol= activitat.find('titol').text\n",
    "            data = activitat.find('dataInici').text\n",
    "            lat = float(activitat.find('.//lat').text)\n",
    "            lon = float(activitat.find('.//lon').text)\n",
    "            info = titol + ' '+ data\n",
    "            folium.Marker([lat, lon], popup=folium.Popup(info),icon=folium.Icon(color=color)).add_to(map)\n",
    "    \n",
    "map.save(\"DadesSabadell/agenda.html\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can find your map creation in the following link:\n",
    "\n",
    "[Link to generated Map... ](DadesSabadell/agenda.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Barcelona bike path under construction in a map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a geolocated information file format that most of map aplications can undestand directelly. Try to execute this example using a geojson file and take a look how this file looks [like.](DadesBarcelona/CARRIL_BICI_CONSTRUCCIO.geojson)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "map = folium.Map(location=[41.3851, 2.1734],zoom_start=13)\n",
    "folium.GeoJson(\"DadesBarcelona/CARRIL_BICI_CONSTRUCCIO.geojson\",name='geojson').add_to(map)\n",
    "\n",
    "map.save(\"DadesBarcelona/carrilbici.html\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can find your map creation in the following link:\n",
    "\n",
    "[Link to generated Map... ](DadesBarcelona/carrilbici.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Poblation per district in Sabadell  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this example we are going to use infomartion related to Sabadell census from 2015 (population per distric, gender and year of birth). This information can be dowloaded from [here](http://opendata.sabadell.cat/ca/?option=com_content&view=article&id=4729&Itemid=209) but you can find the file used in **DadesSabadell/PadroDistricteSexeAnyNaixement2015.csv**. \n",
    "\n",
    "As other examples, we have to modify and group this information to obtain data of our interest: women average age, men average age, number of women, number of men and total population per district.\n",
    "\n",
    "Take a look that how we transform this information to obtain desired table:"
   ]
  },
  {
   "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>Districte</th>\n",
       "      <th>Sexe</th>\n",
       "      <th>Any_Naixement</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1906</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1911</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1912</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1913</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1914</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1915</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1916</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1917</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1918</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1919</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1920</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1921</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1922</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1923</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1924</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1925</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1926</td>\n",
       "      <td>134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1927</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1928</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1929</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1930</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1931</td>\n",
       "      <td>223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1932</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1933</td>\n",
       "      <td>248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1934</td>\n",
       "      <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1935</td>\n",
       "      <td>212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1936</td>\n",
       "      <td>249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1937</td>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1938</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1939</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1380</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1985</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1381</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1986</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1382</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1987</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1988</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1384</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1989</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1385</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1990</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1386</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1991</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1992</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1388</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1993</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1389</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1994</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1390</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1995</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1391</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1996</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1392</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1997</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1393</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1998</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1394</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>1999</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1395</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2000</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1396</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2001</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1397</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2002</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1398</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2003</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1399</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2004</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1400</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2005</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1401</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2006</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1402</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2007</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1403</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2008</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1404</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2009</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1405</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2010</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1406</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2011</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1407</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2012</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1408</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2013</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1409</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>2014</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1410 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Districte Sexe  Any_Naixement  Total\n",
       "0             1    D           1906      1\n",
       "1             1    D           1911      1\n",
       "2             1    D           1912      3\n",
       "3             1    D           1913      3\n",
       "4             1    D           1914      7\n",
       "5             1    D           1915      5\n",
       "6             1    D           1916     10\n",
       "7             1    D           1917     13\n",
       "8             1    D           1918     27\n",
       "9             1    D           1919     39\n",
       "10            1    D           1920     45\n",
       "11            1    D           1921     68\n",
       "12            1    D           1922     87\n",
       "13            1    D           1923     98\n",
       "14            1    D           1924    106\n",
       "15            1    D           1925    132\n",
       "16            1    D           1926    134\n",
       "17            1    D           1927    165\n",
       "18            1    D           1928    183\n",
       "19            1    D           1929    210\n",
       "20            1    D           1930    195\n",
       "21            1    D           1931    223\n",
       "22            1    D           1932    222\n",
       "23            1    D           1933    248\n",
       "24            1    D           1934    225\n",
       "25            1    D           1935    212\n",
       "26            1    D           1936    249\n",
       "27            1    D           1937    207\n",
       "28            1    D           1938    185\n",
       "29            1    D           1939    117\n",
       "...         ...  ...            ...    ...\n",
       "1380          7    H           1985     57\n",
       "1381          7    H           1986     55\n",
       "1382          7    H           1987     53\n",
       "1383          7    H           1988     50\n",
       "1384          7    H           1989     47\n",
       "1385          7    H           1990     42\n",
       "1386          7    H           1991     52\n",
       "1387          7    H           1992     38\n",
       "1388          7    H           1993     45\n",
       "1389          7    H           1994     57\n",
       "1390          7    H           1995     50\n",
       "1391          7    H           1996     52\n",
       "1392          7    H           1997     44\n",
       "1393          7    H           1998     45\n",
       "1394          7    H           1999     39\n",
       "1395          7    H           2000     42\n",
       "1396          7    H           2001     51\n",
       "1397          7    H           2002     42\n",
       "1398          7    H           2003     43\n",
       "1399          7    H           2004     37\n",
       "1400          7    H           2005     43\n",
       "1401          7    H           2006     49\n",
       "1402          7    H           2007     67\n",
       "1403          7    H           2008     58\n",
       "1404          7    H           2009     84\n",
       "1405          7    H           2010     66\n",
       "1406          7    H           2011     72\n",
       "1407          7    H           2012     69\n",
       "1408          7    H           2013     62\n",
       "1409          7    H           2014     66\n",
       "\n",
       "[1410 rows x 4 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import folium\n",
    "import geojson\n",
    "import pandas as pd\n",
    "import seaborn\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_csv('DadesSabadell/PadroDistricteSexeAnyNaixement2015.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Districte</th>\n",
       "      <th>Sexe</th>\n",
       "      <th>Total</th>\n",
       "      <th>Edat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>1</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>3</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>3</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>7</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>5</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>10</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>13</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>27</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>39</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>45</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>68</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>87</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>98</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>106</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>132</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>134</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>165</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>183</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>210</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>195</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>223</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>222</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>248</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>225</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>212</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>249</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>207</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>185</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>117</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1380</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>57</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1381</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>55</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1382</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>53</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>50</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1384</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>47</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1385</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>42</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1386</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>52</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>38</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1388</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>45</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1389</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>57</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1390</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>50</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1391</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>52</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1392</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>44</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1393</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>45</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1394</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>39</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1395</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>42</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1396</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>51</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1397</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>42</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1398</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>43</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1399</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>37</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1400</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>43</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1401</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>49</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1402</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>67</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1403</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>58</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1404</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>84</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1405</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>66</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1406</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>72</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1407</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>69</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1408</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>62</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1409</th>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>66</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1410 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Districte Sexe  Total  Edat\n",
       "0             1    D      1   112\n",
       "1             1    D      1   107\n",
       "2             1    D      3   106\n",
       "3             1    D      3   105\n",
       "4             1    D      7   104\n",
       "5             1    D      5   103\n",
       "6             1    D     10   102\n",
       "7             1    D     13   101\n",
       "8             1    D     27   100\n",
       "9             1    D     39    99\n",
       "10            1    D     45    98\n",
       "11            1    D     68    97\n",
       "12            1    D     87    96\n",
       "13            1    D     98    95\n",
       "14            1    D    106    94\n",
       "15            1    D    132    93\n",
       "16            1    D    134    92\n",
       "17            1    D    165    91\n",
       "18            1    D    183    90\n",
       "19            1    D    210    89\n",
       "20            1    D    195    88\n",
       "21            1    D    223    87\n",
       "22            1    D    222    86\n",
       "23            1    D    248    85\n",
       "24            1    D    225    84\n",
       "25            1    D    212    83\n",
       "26            1    D    249    82\n",
       "27            1    D    207    81\n",
       "28            1    D    185    80\n",
       "29            1    D    117    79\n",
       "...         ...  ...    ...   ...\n",
       "1380          7    H     57    33\n",
       "1381          7    H     55    32\n",
       "1382          7    H     53    31\n",
       "1383          7    H     50    30\n",
       "1384          7    H     47    29\n",
       "1385          7    H     42    28\n",
       "1386          7    H     52    27\n",
       "1387          7    H     38    26\n",
       "1388          7    H     45    25\n",
       "1389          7    H     57    24\n",
       "1390          7    H     50    23\n",
       "1391          7    H     52    22\n",
       "1392          7    H     44    21\n",
       "1393          7    H     45    20\n",
       "1394          7    H     39    19\n",
       "1395          7    H     42    18\n",
       "1396          7    H     51    17\n",
       "1397          7    H     42    16\n",
       "1398          7    H     43    15\n",
       "1399          7    H     37    14\n",
       "1400          7    H     43    13\n",
       "1401          7    H     49    12\n",
       "1402          7    H     67    11\n",
       "1403          7    H     58    10\n",
       "1404          7    H     84     9\n",
       "1405          7    H     66     8\n",
       "1406          7    H     72     7\n",
       "1407          7    H     69     6\n",
       "1408          7    H     62     5\n",
       "1409          7    H     66     4\n",
       "\n",
       "[1410 rows x 4 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Edat'] = 2018 - df['Any_Naixement']\n",
    "df = df.drop(['Any_Naixement'], axis=1)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Edat</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sexe</th>\n",
       "      <th>D</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Districte</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55.537736</td>\n",
       "      <td>54.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>54.509804</td>\n",
       "      <td>54.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>55.000000</td>\n",
       "      <td>54.568627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>54.539216</td>\n",
       "      <td>52.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>54.069307</td>\n",
       "      <td>52.551020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>54.500000</td>\n",
       "      <td>54.049505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>53.030303</td>\n",
       "      <td>50.500000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Edat           \n",
       "Sexe               D          H\n",
       "Districte                      \n",
       "1          55.537736  54.000000\n",
       "2          54.509804  54.000000\n",
       "3          55.000000  54.568627\n",
       "4          54.539216  52.500000\n",
       "5          54.069307  52.551020\n",
       "6          54.500000  54.049505\n",
       "7          53.030303  50.500000"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avg_age_df = pd.pivot_table(df,columns=[\"Sexe\"], index=[\"Districte\"], values=[\"Edat\"])\n",
    "avg_age_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sexe</th>\n",
       "      <th>D</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Districte</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27762</td>\n",
       "      <td>25050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12561</td>\n",
       "      <td>11719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17661</td>\n",
       "      <td>17081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19627</td>\n",
       "      <td>18918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9958</td>\n",
       "      <td>9590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>14803</td>\n",
       "      <td>14366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4312</td>\n",
       "      <td>4394</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Total       \n",
       "Sexe           D      H\n",
       "Districte              \n",
       "1          27762  25050\n",
       "2          12561  11719\n",
       "3          17661  17081\n",
       "4          19627  18918\n",
       "5           9958   9590\n",
       "6          14803  14366\n",
       "7           4312   4394"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_df = habitants_Df = pd.pivot_table(df,columns=[\"Sexe\"], index=[\"Districte\"], values=[\"Total\"],aggfunc=np.sum)\n",
    "count_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>Edat D</th>\n",
       "      <th>Edat H</th>\n",
       "      <th>Total D</th>\n",
       "      <th>Total H</th>\n",
       "      <th>Districte</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Districte</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>1</th>\n",
       "      <td>55.537736</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>27762</td>\n",
       "      <td>25050</td>\n",
       "      <td>1</td>\n",
       "      <td>52812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>54.509804</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>12561</td>\n",
       "      <td>11719</td>\n",
       "      <td>2</td>\n",
       "      <td>24280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>55.000000</td>\n",
       "      <td>54.568627</td>\n",
       "      <td>17661</td>\n",
       "      <td>17081</td>\n",
       "      <td>3</td>\n",
       "      <td>34742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>54.539216</td>\n",
       "      <td>52.500000</td>\n",
       "      <td>19627</td>\n",
       "      <td>18918</td>\n",
       "      <td>4</td>\n",
       "      <td>38545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>54.069307</td>\n",
       "      <td>52.551020</td>\n",
       "      <td>9958</td>\n",
       "      <td>9590</td>\n",
       "      <td>5</td>\n",
       "      <td>19548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>54.500000</td>\n",
       "      <td>54.049505</td>\n",
       "      <td>14803</td>\n",
       "      <td>14366</td>\n",
       "      <td>6</td>\n",
       "      <td>29169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>53.030303</td>\n",
       "      <td>50.500000</td>\n",
       "      <td>4312</td>\n",
       "      <td>4394</td>\n",
       "      <td>7</td>\n",
       "      <td>8706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Edat D     Edat H  Total D  Total H Districte  Total\n",
       "Districte                                                         \n",
       "1          55.537736  54.000000    27762    25050         1  52812\n",
       "2          54.509804  54.000000    12561    11719         2  24280\n",
       "3          55.000000  54.568627    17661    17081         3  34742\n",
       "4          54.539216  52.500000    19627    18918         4  38545\n",
       "5          54.069307  52.551020     9958     9590         5  19548\n",
       "6          54.500000  54.049505    14803    14366         6  29169\n",
       "7          53.030303  50.500000     4312     4394         7   8706"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_df = pd.concat([avg_age_df, count_df], axis=1)\n",
    "total_df['Districte'] = total_df.index.astype(str)\n",
    "\n",
    "total_df.columns=[' '.join(col).strip() for col in total_df.columns.values]\n",
    "total_df['Total'] = total_df['Total H'] + total_df['Total D']\n",
    "\n",
    "total_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to use the obtained table to plot this information in a map. The Open Data Sabadell Web Page also give us geographic information about districts that you can be downloaded [here]() or get from folder **/DadesSabadell/districte.geojson**. This file allow us to draw districts contourns and use different intensity colours to represent values obtained in previous table. This kind of representations in maps are know as chropleths.\n",
    "\n",
    "You can try to change represented values changing **'Total'** in **columns = ['Districte', 'Total']** for per exemple **'Edat D'**,**'Edat H'**,**'Total D'** or **'Total H'**.\n",
    "\n",
    "You can also change graph color **'YlOrRd'** and use **'BuPu'**, **'GnBu'**, **'OrRd'**, **'PuBu'**, **'PuBuGn'**, **'PuRd'**, **'RdPu'**, **'YlGn'** or **'YlGnBu'**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "district_geo = 'DadesSabadell/districtes.geojson'\n",
    "\n",
    "map1 = folium.Map(location=[41.54329, 2.10942],zoom_start=13)\n",
    "map1.choropleth(geo_data=district_geo, \n",
    "              data = total_df,\n",
    "              columns = ['Districte', 'Total'],\n",
    "              key_on = 'feature.properties.description',\n",
    "              fill_color='YlOrRd', \n",
    "              fill_opacity= 0.7,\n",
    "              legend_name = 'Poblacio per districte')\n",
    "\n",
    "\n",
    "map1.save('DadesSabadell/mapapoblacio.html')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can find your map creation in the following link:\n",
    "\n",
    "[Link to generated Map... ](DadesSabadell/mapapoblacio.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15rc1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}