project_data_barcelona.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c192abbb",
   "metadata": {},
   "source": [
    "# Extracting data from the Barcelona City Hall open data website\n",
    "______\n",
    "\n",
    "Here we are going to extract data from the [Barcelona City Hall Open Data Service](https://opendata-ajuntament.barcelona.cat/data/ca/dataset/divter/resource/ed515bb8-502b-4dff-96dc-769f72767919#additional-info) (this data is open and accessible to any user). Specifically, we are going to collect data on **socioeconomic indicators of the territorial division of the city of Barcelona**.\n",
    "\n",
    "This information will be stored in a dataframe. \n",
    "\n",
    "As we will see, the data obtained are URLs that we will later use to download pdf files.\n",
    "\n",
    "$~$\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80c20a77",
   "metadata": {},
   "source": [
    "libraries we will use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cae6e388",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import os\n",
    "import pprint\n",
    "import pdfplumber\n",
    "\n",
    "from bs4 import BeautifulSoup\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c4a1c83",
   "metadata": {},
   "source": [
    "____"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7fd621f",
   "metadata": {},
   "source": [
    "## Scraping the city hall website\n",
    "\n",
    "It is true that the city council's website allows us open access to the data, but we could automate this.\n",
    "\n",
    "<img src=\"./images/web_image2.png\" width=\"500\" height=\"340\"/>\n",
    "\n",
    "\n",
    "$~$\n",
    "\n",
    "as we can see in the image above, the first thing we would have to do is download one or one of the csv files where we will find a series of URLs that we will then use to download the pdf files.\n",
    "\n",
    "$~$\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d3759642",
   "metadata": {},
   "outputs": [],
   "source": [
    "#url we want to scrape\n",
    "url = \"https://opendata-ajuntament.barcelona.cat/data/ca/dataset/divter\"\n",
    "\n",
    "#request the url \n",
    "web = requests.get(url)\n",
    "#init soup \n",
    "soup = BeautifulSoup(web.content,\"html.parser\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1c57f12b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# find all the links in the page (div class=\"resource-actions-small\">)\n",
    "links = soup.find_all(\"div\", class_=\"resource-actions-small\")\n",
    "\n",
    "\n",
    "links = [link.find(\"a\") for link in links]\n",
    "\n",
    "# get the links\n",
    "links = [link.get(\"href\") for link in links]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b1d6615",
   "metadata": {},
   "source": [
    "in the links list we have each one of the links that will allow us to download the csv files. let's create a dataframe with this information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3d6b0cd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a dataframe with the links\n",
    "links = pd.DataFrame(links, columns=[\"link\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "370b4baa",
   "metadata": {},
   "outputs": [],
   "source": [
    "#save the dataframe in a csv file as a backup \n",
    "links.to_csv(\"links.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dbd82f2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# read links.csv\n",
    "links = pd.read_csv(\"links.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5d1b5cad",
   "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>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://opendata-ajuntament.barcelona.cat/data...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://opendata-ajuntament.barcelona.cat/data...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://opendata-ajuntament.barcelona.cat/data...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://opendata-ajuntament.barcelona.cat/data...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://opendata-ajuntament.barcelona.cat/data...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                link\n",
       "0  https://opendata-ajuntament.barcelona.cat/data...\n",
       "1  https://opendata-ajuntament.barcelona.cat/data...\n",
       "2  https://opendata-ajuntament.barcelona.cat/data...\n",
       "3  https://opendata-ajuntament.barcelona.cat/data...\n",
       "4  https://opendata-ajuntament.barcelona.cat/data..."
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "links.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91f33346",
   "metadata": {},
   "source": [
    "_____"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a114201",
   "metadata": {},
   "source": [
    "## Download and save files with a url\n",
    "\n",
    "We can use the dataframe \"links\" with the urls to download each of the .csv files. Note that each of the files refers to a specific year. The next cell will create a folder \"csv_folder\" where we will save the .cvs files.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ddf7ebfd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019_divter.csv\n",
      "2018_divter.csv\n",
      "2017_divter.csv\n",
      "2016_divter.csv\n",
      "2015_divter.csv\n",
      "2014_divter.csv\n",
      "2013_divter.csv\n",
      "2012_divter.csv\n",
      "2011_divter.csv\n",
      "2010_divter.csv\n",
      "2009_divter.csv\n",
      "2008_divter.csv\n"
     ]
    }
   ],
   "source": [
    "#access the links, save the csv files in a folder called \"csv_files\"\n",
    "#create a folder called \"csv_files\" if it doesn't exist\n",
    "if not os.path.exists(\"csv_files\"):\n",
    "      os.mkdir(\"csv_files\")\n",
    "\n",
    "#loop through the links\n",
    "for link in links[\"link\"]:\n",
    "      file_name = link.split(\"/\")[-1]\n",
    "      file_name = file_name.split(\"?\")[0]\n",
    "      file_name = file_name.split(\".\")[0]\n",
    "      file_name = file_name + \".csv\"\n",
    "      print(file_name)\n",
    "      with open(os.path.join(\"csv_files\", file_name), \"wb\") as f:\n",
    "         f.write(requests.get(link).content)\n",
    "\n",
    "\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d42308b",
   "metadata": {},
   "source": [
    "We open one of the files we have downloaded to copy the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "id": "3fa60961",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "the columns names are:  Index(['CODI_DIVISIO_TERRITORIAL', 'NOM_DIVISIO_TERRITORIAL',\n",
      "       'CATEGORIA_DIVISIO', 'CODI_DIVISIO_TERRITORIAL_PARE',\n",
      "       'URL_FITXA_DIVISIO_TERRITORIAL'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "#open one of the csv files \n",
    "df = pd.read_csv(\"csv_files/2019_divter.csv\")\n",
    "\n",
    "#check the columns names\n",
    "print(\"the columns names are: \", df.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4aef60ac",
   "metadata": {},
   "source": [
    "\n",
    "let's create a new column any that will be used to know what year the information is from "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "id": "440501d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a list with the columns names and add the column \"Any\"\n",
    "columns = list(df.columns)\n",
    "columns.append(\"Any\")\n",
    "\n",
    "#create a new dataframe with the columns names\n",
    "df_new = pd.DataFrame(columns=columns)\n",
    "\n",
    "#put the column \"any\" in the first position\n",
    "df_new = df_new[[\"Any\"] + df_new.columns[:-1].tolist()]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "id": "097ce35a",
   "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>Any</th>\n",
       "      <th>CODI_DIVISIO_TERRITORIAL</th>\n",
       "      <th>NOM_DIVISIO_TERRITORIAL</th>\n",
       "      <th>CATEGORIA_DIVISIO</th>\n",
       "      <th>CODI_DIVISIO_TERRITORIAL_PARE</th>\n",
       "      <th>URL_FITXA_DIVISIO_TERRITORIAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Any, CODI_DIVISIO_TERRITORIAL, NOM_DIVISIO_TERRITORIAL, CATEGORIA_DIVISIO, CODI_DIVISIO_TERRITORIAL_PARE, URL_FITXA_DIVISIO_TERRITORIAL]\n",
       "Index: []"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb9cc2e3",
   "metadata": {},
   "source": [
    "this empty dataframe will be used to fill it with the information that we are going to obtain from each of the csv files. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "id": "cfb8e1f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#loop through the csv files\n",
    "for file in os.listdir(\"csv_files\"):\n",
    "      #read the csv file\n",
    "      df = pd.read_csv(os.path.join(\"csv_files\", file))\n",
    "      #get the year from the file name \"\"\n",
    "      year = file.split(\"_\")[0]\n",
    "      #add the year to the dataframe in the column \"Any\"\n",
    "      df[\"Any\"] = year\n",
    "      #concatenate the dataframes\n",
    "      df_new = pd.concat([df_new, df], axis=0)\n",
    "      "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "id": "5448aa15",
   "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>Any</th>\n",
       "      <th>CODI_DIVISIO_TERRITORIAL</th>\n",
       "      <th>NOM_DIVISIO_TERRITORIAL</th>\n",
       "      <th>CATEGORIA_DIVISIO</th>\n",
       "      <th>CODI_DIVISIO_TERRITORIAL_PARE</th>\n",
       "      <th>URL_FITXA_DIVISIO_TERRITORIAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>B-01</td>\n",
       "      <td>el Raval</td>\n",
       "      <td>Barri</td>\n",
       "      <td>D-01</td>\n",
       "      <td>http://www.bcn.cat/estadistica/catala/dades/in...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>B-02</td>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>Barri</td>\n",
       "      <td>D-01</td>\n",
       "      <td>http://www.bcn.cat/estadistica/catala/dades/in...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>B-03</td>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>Barri</td>\n",
       "      <td>D-01</td>\n",
       "      <td>http://www.bcn.cat/estadistica/catala/dades/in...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>B-04</td>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>Barri</td>\n",
       "      <td>D-01</td>\n",
       "      <td>http://www.bcn.cat/estadistica/catala/dades/in...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>B-05</td>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>Barri</td>\n",
       "      <td>D-02</td>\n",
       "      <td>http://www.bcn.cat/estadistica/catala/dades/in...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Any CODI_DIVISIO_TERRITORIAL                NOM_DIVISIO_TERRITORIAL  \\\n",
       "0  2017                     B-01                               el Raval   \n",
       "1  2017                     B-02                         el Barri Gòtic   \n",
       "2  2017                     B-03                         la Barceloneta   \n",
       "3  2017                     B-04  Sant Pere, Santa Caterina i la Ribera   \n",
       "4  2017                     B-05                          el Fort Pienc   \n",
       "\n",
       "  CATEGORIA_DIVISIO CODI_DIVISIO_TERRITORIAL_PARE  \\\n",
       "0             Barri                          D-01   \n",
       "1             Barri                          D-01   \n",
       "2             Barri                          D-01   \n",
       "3             Barri                          D-01   \n",
       "4             Barri                          D-02   \n",
       "\n",
       "                       URL_FITXA_DIVISIO_TERRITORIAL  \n",
       "0  http://www.bcn.cat/estadistica/catala/dades/in...  \n",
       "1  http://www.bcn.cat/estadistica/catala/dades/in...  \n",
       "2  http://www.bcn.cat/estadistica/catala/dades/in...  \n",
       "3  http://www.bcn.cat/estadistica/catala/dades/in...  \n",
       "4  http://www.bcn.cat/estadistica/catala/dades/in...  "
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#show the first 5 rows of the dataframe\n",
    "df_new.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ea5a69d",
   "metadata": {},
   "source": [
    "Now we have a dataframe with the columns:\n",
    "+ Any = is the year of information\n",
    "+ CODI_DIVISIO_TERRITORIAL: is the code of the territorial division managed by the city council of Barcelona for each of the neighborhoods.\n",
    "+ NOM_DIVISIO_TERRITORIAL = this is the name of each of the barcelona neighborhoods.\n",
    "+ CATEGORY_DIVISION = here there can be either (Barri) Neighborhood or District (Districte), \n",
    "+ CODI_DIVISIO_TERRITORIAL_PARE = this code is internal to the City Council of Barcelona.\n",
    "\n",
    "+ URL_FITXA_DIVISIO_TERRITORIAL = this is the url from where we can download the pdf with the specific information of the neighborhood and the year.\n",
    "\n",
    "Knowing this, we are only interested in the rows where we have url address, the following cells are useful for this purpose"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "337d9994",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there are null values in the URL_FITXA_DIVISIO_TERRITORIAL:  True\n",
      "the number of null values in the URL_FITXA_DIVISIO_TERRITORIAL:  120\n",
      "there are empty values in the URL_FITXA_DIVISIO_TERRITORIAL:  False\n",
      "the number of empty values in the URL_FITXA_DIVISIO_TERRITORIAL:  0\n"
     ]
    }
   ],
   "source": [
    "#show if there are null values in the URL_FITXA_DIVISIO_TERRITORIAL\n",
    "print(\"there are null values in the URL_FITXA_DIVISIO_TERRITORIAL: \", df_new[\"URL_FITXA_DIVISIO_TERRITORIAL\"].isnull().values.any())\n",
    "print(\"the number of null values in the URL_FITXA_DIVISIO_TERRITORIAL: \", df_new[\"URL_FITXA_DIVISIO_TERRITORIAL\"].isnull().sum())\n",
    "\n",
    "#show if there are empty values in the URL_FITXA_DIVISIO_TERRITORIAL\n",
    "print(\"there are empty values in the URL_FITXA_DIVISIO_TERRITORIAL: \", df_new[\"URL_FITXA_DIVISIO_TERRITORIAL\"].eq(\"\").values.any())\n",
    "print(\"the number of empty values in the URL_FITXA_DIVISIO_TERRITORIAL: \", df_new[\"URL_FITXA_DIVISIO_TERRITORIAL\"].eq(\"\").sum())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c1fd7df4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop the rows with null values in the URL_FITXA_DIVISIO_TERRITORIAL\n",
    "df_new = df_new.dropna(subset=[\"URL_FITXA_DIVISIO_TERRITORIAL\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2394e442",
   "metadata": {},
   "outputs": [],
   "source": [
    "#save the dataframe in a csv file\n",
    "df_new.to_csv(\"project_data_barcelona.csv\", index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "62c8a6a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#read the csv file\n",
    "df_new = pd.read_csv(\"project_data_barcelona.csv\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a0f3298",
   "metadata": {},
   "source": [
    "_____"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51dced3b",
   "metadata": {},
   "source": [
    "## Downloading pdf files\n",
    "\n",
    "Before we have obtained the URLs that will be used to download the .pdf files, now we are going to download them and save them in a folder that we are going to create and call pdf_files.\n",
    "\n",
    "$~$\n",
    "\n",
    "One of the problems with the city council's website is that the URLs they offer are corrupted. We have previously noticed this. The correct URLs are the ones we have called \"url_base\". The first thing we are going to do is to change the corrupted URLs for the correct ones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a1299749",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    803\n",
       "True      73\n",
       "Name: URL_FITXA_DIVISIO_TERRITORIAL, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we previous know that the proper url to access the pdf files is the url in the column \"URL_FITXA_DIVISIO_TERRITORIAL\" have the following structure:\n",
    "\n",
    "url_base = \"https://ajuntament.barcelona.cat/estadistica/catala/Estadistiques_per_territori/Barris/Fitxes\"\n",
    "\n",
    "#we also previous know that the wrong url to access the pdf files is the url in the column \"URL_FITXA_DIVISIO_TERRITORIAL\" have the following structure:\n",
    "url_wrong =\"http://www.bcn.cat/estadistica/catala/dades/inf/barris\"\n",
    "\n",
    "#find the values in the column \"URL_FITXA_DIVISIO_TERRITORIAL\" that have the url_base\n",
    "df_new[\"URL_FITXA_DIVISIO_TERRITORIAL\"].str.contains(url_base).value_counts()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "bb07254d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# for the values that have the url_wrong, we replace the url_wrong with the url_base\n",
    "df_new.loc[df_new[\"URL_FITXA_DIVISIO_TERRITORIAL\"].str.contains(url_wrong), \"URL_FITXA_DIVISIO_TERRITORIAL\"] = df_new.loc[df_new[\"URL_FITXA_DIVISIO_TERRITORIAL\"].str.contains(\n",
    "    url_wrong), \"URL_FITXA_DIVISIO_TERRITORIAL\"].str.replace(url_wrong, url_base)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "31af0fe0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#save the dataframe in a csv file for backup.\n",
    "df_new.to_csv(\"project_data_barcelona_url_clean.csv\", index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e635fcd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#read the csv file\n",
    "df = pd.read_csv(\"project_data_barcelona_url_clean.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e4580a3",
   "metadata": {},
   "source": [
    "_____"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "175779eb",
   "metadata": {},
   "source": [
    "$~$\n",
    "\n",
    "Now, with the correct URl we can download the information. The next cell is used to create a folder \"pdf_files\", and download the files and save them in that folder. In addition we are going to call each of the files by the year to which the information refers and the name of the neighborhood, that is to say, it will have the following structure:\n",
    "\n",
    "*year_neighborhood name.pdf*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84c05a71",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a folder to save the pdf files called \"pdf_files\"\n",
    "#check if the folder exists and if not create it\n",
    "import time\n",
    "import urllib.request\n",
    "errors = []\n",
    "if not os.path.exists(\"pdf_files\"):\n",
    "    os.mkdir(\"pdf_files\")\n",
    "\n",
    "#loop through the dataframe and download the pdf files\n",
    "for i in range(len(df)):\n",
    "    #get the url of the pdf file\n",
    "    url = df[\"URL_FITXA_DIVISIO_TERRITORIAL\"][i]\n",
    "    #get the name of the file Any+NOM_DIVISIO+.pdf\n",
    "    file_name = str(df[\"Any\"][i]) + \"_\" + df[\"NOM_DIVISIO_TERRITORIAL\"][i] + \".pdf\"\n",
    "    #create the path to save the file\n",
    "    path = os.path.join(\"pdf_files\", file_name)\n",
    "    #try to download the file in files and save them in the folder pdf_files if the download fails print the error, the url and the file name. save the error with the name of the file in a list\n",
    "    try:\n",
    "        urllib.request.urlretrieve(url, path)\n",
    "        #print the name of the file downloaded\n",
    "\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        print(url)\n",
    "        print(file_name)\n",
    "        errors.append(file_name)\n",
    "    #wait 1 second to avoid the error 429\n",
    "    # time.sleep(1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ac20f61",
   "metadata": {},
   "source": [
    "\n",
    "We keep a list with the names of the files we have downloaded and also a list with the names of the files we have not been able to download."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "id": "3236933a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of files downloaded: 803\n"
     ]
    }
   ],
   "source": [
    "#save the name of the files in a list with the path. here are the files that we already have downloaded\n",
    "files = [os.path.join(\"pdf_files\", file) for file in os.listdir(\"pdf_files\")]\n",
    "\n",
    "#print the number of files downloaded\n",
    "print(\"Number of files downloaded: {}\".format(len(files)))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "id": "f0c17c68",
   "metadata": {},
   "outputs": [],
   "source": [
    "#print the number of files with errors\n",
    "print(\"Number of files with errors: {}\".format(len(errors)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04bbc39f",
   "metadata": {},
   "source": [
    "_________"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce81654b",
   "metadata": {},
   "source": [
    "## Extracting information from pdf files\n",
    "\n",
    "we already have the pdf files now what we are going to do is to extract the information from the files. \n",
    "\n",
    "To do this, the first thing we are going to do is to define a dictionary with the following keys\n",
    "\n",
    "+ Barri = is the name of the neighborhood\n",
    "+ Any = the year the information refers to\n",
    "+ Població = is the population of the neighborhood for that year.\n",
    "+ Superfície_(km2) = is the area in km2 of the neighborhood\n",
    "+ Densitat_(hab/km2) = is the density of inhabitants per square kilometer. \n",
    "+ Dones = Total number of women living in the neighborhood.\n",
    "+ Homes = Total number of men living in the neighborhood.\n",
    "+ edat_0_14 = percentage of the total population of the neighborhood aged 0 to 14 years old\n",
    "+ edat_15_24 = percentage of the total population of the neighborhood aged 15 to 24 years old\n",
    "+ edat_25_64 = percentage of the total population of the neighborhood aged 25 to 64 years old\n",
    "+ edat_65_i_mes = percentage of the total population of the neighborhood over the age of 65\n",
    "+ naixement_Barcelona = population of the neighborhood (in percentage) born in Barcelona\n",
    "+ population of the neighborhood (in percentage) born outside of Barcelona but in the region of catalonia \n",
    "+ naixement_Resta_Espanya = population of the neighborhood (in percentage) born in \n",
    "other parts of spain\n",
    "+ naixement_Estranger = foreign-born population of the neighborhood (in percentage)\n",
    "+ nacionalitat_Espanyols = percentage of the neighborhood's population with Spanish nationality\n",
    "+ nacionalitat_Estrangers = percentage of the neighborhood's population with foreign nationality\n",
    "+ Titulats_superiors = Percentage of the population with higher education in the neighborhood\n",
    "+ Taxa_natalitat= neighborhood birth rate per 1000 inhabitants\n",
    "+ mes_de_65_anys_viu_sola = percentage of the neighborhood population over 65 years old living alone\n",
    "+ Index_de_sobreenvelliment =  index population >75 years/ population >65 years*100\n",
    "+ Turismes = tourists in the neighborhood (persons) per 1,000 inhabitants\n",
    "+ Motos = motorcycles (individuals) / 1,000 inhab.\n",
    "+ Ciclomotors = Cyclomotors (physical persons) / 1,000 inhab.\n",
    "+ aturats_registrats = Number of registered unemployed\n",
    "+ Renda_familiar = Family income available per inhabitant\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "id": "2495faf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a empty dataframe with the columns we want.\n",
    "# the columns are: \"Any\",\"Barri\",\"Població\",\"Superfície_(km2)\",\n",
    "# \"Densitat_(hab/km2)\",\"per_sexe_Dones\",\"per_sexe_Homes\",\"per_edat_(en %)_0-14\",\n",
    "# \"per_edat_(en %)_15-24\", \"per_edat_(en %)_25-64\", \"per_edat_(en %)_65 i mes\"\n",
    "# \"lloc de naixement_(en %)_Barcelona\",\"\"lloc de naixement_(en %)_Resta Catalunya\"\n",
    "#\"lloc de naixement_(en %)_Resta Espanya\", \"lloc de naixement_(en %)_Estranger\"\n",
    "# per_nacionalitat_(en %)_Espanyols\", \"per_nacionalitat_(en %)_Estrangers\"\n",
    "#\"%_Titulats superiors\", \"Taxa natalitat(1.000 hab)\",\"més_de_65_anys_que_viu_sola_(%)\"\n",
    "#\"Índex_de_sobreenvelliment\", \"Turismes_(persones físiques)_1.000 hab\"\n",
    "#\"Motos_(persones_físiques)1.000_hab\", \"Ciclomotors_(persones físiques)1.000_hab\"\n",
    "#\"Nombre_d'aturats_registrats\", \"Renda_familiar_disponible_per_habitant_(2017)\"\n",
    "\n",
    "\n",
    "columns = [\"Barri\",\"Any\",\"Població\", \"Superfície_(km2)\", \"Densitat_(hab/km2)\", \"Dones\", \"Homes\", \"edat_0_14\",\n",
    "           \"edat_15_24\", \"edat_25_64\", \"edat_65_i_mes\", \"naixement_Barcelona\",\n",
    "           \"naixement_Resta_Catalunya\", \"naixement_Resta_Espanya\", \"naixement_Estranger\",\n",
    "           \"nacionalitat_Espanyols\", \"nacionalitat_Estrangers\", \"Titulats_superiors\",\n",
    "           \"Taxa_natalitat\", \"mes_de_65_anys_viu_sola\", \"Index_de_sobreenvelliment\",\n",
    "           \"Turismes\", \"Motos\",\n",
    "           \"Ciclomotors\", \"aturats_registrats\", \"Renda_familiar\"]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "448ccf68",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a dictionary with the columns as keys and the values as empty lists\n",
    "data_structure = {}\n",
    "for column in columns:\n",
    "    data_structure[column] = None\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "415cef11",
   "metadata": {},
   "source": [
    "Now we are going to create a series of regular expressions that will allow us to extract the information from the pdf files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "b45f57dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a regex to get the data from the pdf files.\n",
    "\n",
    "#patter to find the year(any) it is a  interger number after the string \"SOCIOECONÒMICS \"\n",
    "any = re.compile(r\"SOCIOECONÒMICS\\s+(\\d+)\")\n",
    "\n",
    "# pattert to find the poblation it is a number with 1 or more digits and a comma or a dot and 1 or more digits after the comma or dot after the word \"Població\"\n",
    "poblacio = re.compile(r'Població\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Superfície (km2) \"\n",
    "superficie = re.compile(r'Superfície\\s\\(km2\\)\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Densitat (hab/km2) \"\n",
    "densitat = re.compile(r'Densitat\\s\\(hab/km2\\)\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Dones\" or a interger after the word \"Dones\"\n",
    "dones = re.compile(r'Dones\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Homes\", or a interger after the word \"Homes\"\n",
    "homes = re.compile(r'Homes\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after 0-14\n",
    "edat_0_14 = re.compile(r'[0].[1][4]\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "\n",
    "\n",
    "# pattert to find a number with 1 or more digits after 15\"-\"24, or a interger after 15\"-\"24\" \"\n",
    "edat_15_24 = re.compile(r'[1][5].[2][4]\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"25-64\"\n",
    "edat_25_64 = re.compile(r'[2][5].[6][4]\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"65 i mes \" o 65 i mes\"\n",
    "edat_65_i_mes = re.compile(r'[6][5]\\s[i]\\s[m][e][s]\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Barcelona\"\n",
    "naixement_Barcelona = re.compile(r'Barcelona\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Resta Catalunya\"\n",
    "naixement_Resta_Catalunya = re.compile(r'Resta\\sCatalunya\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Resta Espanya\"\n",
    "naixement_Resta_Espanya = re.compile(r'ta\\sEspanya\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Estranger\"\n",
    "naixement_Estranger = re.compile(r'Estranger\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Espanyols\"\n",
    "nacionalitat_Espanyols = re.compile(r'Espanyols\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Estrangers\"\n",
    "nacionalitat_Estrangers = re.compile(r'Estrangers\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"CFGS (1)\"\n",
    "Titulats_superiors = re.compile(r'CFGS\\s\\(1\\)\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Taxa natalitat / 1.000 hab.\"\n",
    "Taxa_natalitat = re.compile(r'Taxa natalitat / 1.000 hab.\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Població de més de 65 anys que viu sola (%) (2)\"\n",
    "mes_de_65_anys_viu_sola = re.compile(r'Població de més de 65 anys que viu sola \\(%\\) \\(2\\)\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word Index de sobreenvelliment (?)\"\n",
    "Index_de_sobreenvelliment = re.compile(r\"Índex de sobreenvelliment\\s+\\(\\d+\\)\\s+(\\d+[\\.,]\\d+|\\d+)\")\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Turismes (persones físiques) / 1.000 hab (4)\"\n",
    "Turismes = re.compile(r\"Turismes \\(persones físiques\\)\\s\\/\\s1\\.000\\shab\\s\\(\\d\\)\\s+(\\d+[\\.,]\\d+|\\d+)\")\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Motos (persones físiques) / 1.000 hab (4)\"\n",
    "Motos = re.compile(r\"(Motos\\s\\(persones\\sfísiques\\)\\s\\/\\s1\\.000\\shab\\s\\(\\d\\)\\s|Motos\\s\\(persones\\sfísiques\\)\\s\\/\\s1\\.000\\shab\\s)+(\\d+[\\.,]\\d+|\\d+)\")\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"Ciclomotors (persones físiques) / 1.000 hab (4)\"\n",
    "\n",
    "Ciclomotors = re.compile(r'Ciclomotors \\(persones físiques\\)\\s\\/\\s1\\.000\\shab\\s\\(\\d\\)\\s+(\\d+[\\.,]\\d+|\\d+)')\n",
    "\n",
    "# pattert to find any interger after the word \"(5)\"\n",
    "aturats_registrats = re.compile(r\"Nombre d'aturats registrats\\s\\(\\d\\)\\s+(\\d+[\\.,]\\d+|\\d+)\")\n",
    "\n",
    "# pattert to find a number with 1 or more digits after the word \"(2017)\"\n",
    "Renda_familiar = re.compile(r\"Renda\\sfamiliar\\sdisponible\\sper\\shabitant\\s\\(\\d{4}\\)\\s+(\\d+[\\.,]\\d+|\\d+)\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e371635",
   "metadata": {},
   "source": [
    "once the regular expressions have been defined, we will extract the information from the pdfs and save what we are interested in "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "id": "bc9f5960",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pdf_files/2015_el Putxet i el Farró.pdf this .pdf file no have the same pages as the others\n"
     ]
    }
   ],
   "source": [
    "# here we are going to create a list of all the patterns. This will be useful later\n",
    "# also we are going to use pdfplumber to extract the text from the pdf\n",
    "values = []\n",
    "\n",
    "\n",
    "for file in files:\n",
    "\n",
    "\n",
    "    data = data_structure\n",
    "    data[\"Barri\"] = file[:-4][15:]\n",
    "    # print(file[:-4][15:])\n",
    "\n",
    "    with pdfplumber.open(file) as pdf:\n",
    "        # check if the pdf has 2 pages\n",
    "        if len(pdf.pages) == 2:\n",
    "        \n",
    "            page = pdf.pages[1]\n",
    "            text = page.extract_text()\n",
    "            for i in text.split('\\n'):\n",
    "                \n",
    "                if any.search(i):\n",
    "                    # finding the \"Any\" pattern that matches the text and adding it to the dictionary\n",
    "                    data['Any'] = any.search(i).group(1)\n",
    "                    # print(any.search(i).group(1))\n",
    "                    continue\n",
    "\n",
    "                    # find the \"Població\" pattern that matches the text and adding it to the dictionary\n",
    "                if poblacio.search(i):\n",
    "                    data[\"Població\"] = poblacio.search(i).group(1)\n",
    "                    # print(poblacio.search(i).group(1))\n",
    "                \n",
    "                    continue\n",
    "\n",
    "                    # find the \"superfície\" pattern that matches the text and adding it to the dictionary\n",
    "                if superficie.search(i):\n",
    "                \n",
    "                    data['Superfície_(km2)'] = superficie.search(i).group(1)\n",
    "                    # print(superficie.search(i).group(1))\n",
    "\n",
    "                    continue\n",
    "\n",
    "                    # find the \"densitat\" pattern that matches the text and adding it to the dictionary\n",
    "                if densitat.search(i):\n",
    "                    \n",
    "                    data[\"Densitat_(hab/km2)\"] = densitat.search(i).group(1)\n",
    "                    # print(densitat.search(i).group(1))\n",
    "\n",
    "                    continue\n",
    "\n",
    "                    # find the Dones pattern that matches the text and adding it to the dictionary\n",
    "                if dones.search(i):\n",
    "                    \n",
    "                    data[\"Dones\"]= dones.search(i).group(1)\n",
    "                    # print(dones.search(i).group(1))\n",
    "\n",
    "                    continue\n",
    "\n",
    "                    # find the Homes pattern that matches the text and adding it to the dictionary\n",
    "                if homes.search(i):\n",
    "                        # print(homes.search(i).group(1))\n",
    "                        data[\"Homes\"] = homes.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the edat_0_14 pattern that matches the text and adding it to the dictionary\n",
    "                if edat_0_14.search(i):\n",
    "                        # print(edat_0_14.search(i).group(1))\n",
    "                        data[\"edat_0_14\"] = edat_0_14.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                # find the edat_15_64 pattern that matches the text and adding it to the dictionary\n",
    "                if edat_15_24.search(i):\n",
    "                        # print(edat_15_24.search(i).group(1))\n",
    "                        data[\"edat_15_24\"] = edat_15_24.search(i).group(1)\n",
    "                        continue\n",
    "                        #find the edat_25_64 pattern that matches the text and adding it to the dictionary\n",
    "                if edat_25_64.search(i):\n",
    "                        # print(edat_25_64.search(i).group(1))\n",
    "                        data[\"edat_25_64\"] = edat_25_64.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the pattern  edat_65_i_mes that matches the text and adding it to the dictionary\n",
    "                if edat_65_i_mes.search(i):\n",
    "                        # print(edat_65_i_mes.search(i).group(1))\n",
    "                        data [\"edat_65_i_mes\"]= edat_65_i_mes.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "\n",
    "                    # find the naixement_Barcelona pattern that matches the text and adding it to the dictionary\n",
    "                if naixement_Barcelona.search(i):\n",
    "                        # print(naixement_Barcelona.search(i).group(1))\n",
    "                        data[\"naixement_Barcelona\"] = naixement_Barcelona.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the naixement_Resta_Catalunya pattern that matches the text and adding it to the dictionary\n",
    "                if naixement_Resta_Catalunya.search(i):\n",
    "                        # print(naixement_Resta_Catalunya.search(i).group(1))\n",
    "                        data[\"naixement_Resta_Catalunya\"]= naixement_Resta_Catalunya.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the naixement_Resta_Espanya pattern that matches the text and adding it to the dictionary\n",
    "                if naixement_Resta_Espanya.search(i):\n",
    "                        # print(naixement_Resta_Espanya.search(i).group(1))\n",
    "                        data[\"naixement_Resta_Espanya\"]= naixement_Resta_Espanya.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the naixement_Extranger pattern that matches the text and adding it to the dictionary\n",
    "                if naixement_Estranger.search(i):\n",
    "                        # print(naixement_Estranger.search(i).group(1))\n",
    "                        data[\"naixement_Estranger\"] = naixement_Estranger.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the nacionalitat_Espanyols pattern that matches the text and adding it to the dictionary\n",
    "                if nacionalitat_Espanyols.search(i):\n",
    "                        # print(nacionalitat_Espanyols.search(i).group(1))\n",
    "                        data[\"nacionalitat_Espanyols\"] = nacionalitat_Espanyols.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the nacionalitat_Estrangers\n",
    "\n",
    "                if nacionalitat_Estrangers.search(i):\n",
    "                        # print(nacionalitat_Estrangers.search(i).group(1))\n",
    "                        data[\"nacionalitat_Estrangers\"] = nacionalitat_Estrangers.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the Titulats_superiors pattern that matches the text and adding it to the dictionary\n",
    "                if Titulats_superiors.search(i):\n",
    "                        # print(Titulats_superiors.search(i).group(1))\n",
    "                        data[\"Titulats_superiors\"] = Titulats_superiors.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the Taxa_natalitat pattern that matches the text and adding it to the dictionary\n",
    "                if Taxa_natalitat.search(i):\n",
    "                        # print(Taxa_natalitat.search(i).group(1))\n",
    "                        data[\"Taxa_natalitat\"] = Taxa_natalitat.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the mes_de_65_anys_viu_sola pattern that matches the text and adding it to the dictionary\n",
    "                if mes_de_65_anys_viu_sola.search(i):\n",
    "                        # print(mes_de_65_anys_viu_sola.search(i).group(1))\n",
    "                        data[\"mes_de_65_anys_viu_sola\"] = mes_de_65_anys_viu_sola.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the Index_de_sobreenvelliment and get the number\n",
    "                if Index_de_sobreenvelliment.search(i):\n",
    "                        # print(Index_de_sobreenvelliment.search(i).group(1))\n",
    "                        data[\"Index_de_sobreenvelliment\"] = Index_de_sobreenvelliment.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the Turisme pattern that matches the text and adding it to the dictionary\n",
    "                if Turismes.search(i):\n",
    "                        # print(Turismes.search(i).group(1))\n",
    "                        data[\"Turismes\"] = Turismes.search(i).group(1)\n",
    "                        continue\n",
    "\n",
    "                    # find the Motos pattern that matches the text and adding it to the dictionary\n",
    "\n",
    "                if Motos.search(i):\n",
    "                    # print(Motos.search(i).group(1))\n",
    "                    data[\"Motos\"] = Motos.search(i).group(2)\n",
    "                    continue\n",
    "\n",
    "                    #find the Ciclomotors pattern that matches the text and adding it to the dictionary\n",
    "\n",
    "                if Ciclomotors.search(i):\n",
    "                    # print(Ciclomotors.search(i).group(1))\n",
    "                    data[\"Ciclomotors\"] = Ciclomotors.search(i).group(1)\n",
    "                    continue\n",
    "                \n",
    "                \n",
    "                    # find the aturats_registrats pattern that matches the text and adding it to the dictionary\n",
    "                if aturats_registrats.search(i):\n",
    "                    # print(aturats_registrats.search(i).group(1))\n",
    "                    data[\"aturats_registrats\"] = aturats_registrats.search(i).group(1)\n",
    "                    continue   \n",
    "\n",
    "                    #find the renta familiar pattern that matches the text and adding it to the dictionary\n",
    "                if Renda_familiar.search(i):\n",
    "                    # print(Renda_familiar.search(i).group(1))\n",
    "                    data[\"Renda_familiar\"] = Renda_familiar.search(i).group(1)\n",
    "                    continue\n",
    "        else:\n",
    "            print(file + \" this .pdf file no have the same pages as the others\")     \n",
    "    values.append(data.copy())\n",
    "#    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0094722c",
   "metadata": {},
   "source": [
    "we create a dataframe with the extracted information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "id": "b91afefc",
   "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>Barri</th>\n",
       "      <th>Any</th>\n",
       "      <th>Població</th>\n",
       "      <th>Superfície_(km2)</th>\n",
       "      <th>Densitat_(hab/km2)</th>\n",
       "      <th>Dones</th>\n",
       "      <th>Homes</th>\n",
       "      <th>edat_0_14</th>\n",
       "      <th>edat_15_24</th>\n",
       "      <th>edat_25_64</th>\n",
       "      <th>...</th>\n",
       "      <th>nacionalitat_Estrangers</th>\n",
       "      <th>Titulats_superiors</th>\n",
       "      <th>Taxa_natalitat</th>\n",
       "      <th>mes_de_65_anys_viu_sola</th>\n",
       "      <th>Index_de_sobreenvelliment</th>\n",
       "      <th>Turismes</th>\n",
       "      <th>Motos</th>\n",
       "      <th>Ciclomotors</th>\n",
       "      <th>aturats_registrats</th>\n",
       "      <th>Renda_familiar</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>2012</td>\n",
       "      <td>22.873</td>\n",
       "      <td>1,1</td>\n",
       "      <td>20.527</td>\n",
       "      <td>11.479</td>\n",
       "      <td>11.394</td>\n",
       "      <td>9,7</td>\n",
       "      <td>8,4</td>\n",
       "      <td>66,0</td>\n",
       "      <td>...</td>\n",
       "      <td>38,5</td>\n",
       "      <td>33,7</td>\n",
       "      <td>9,3</td>\n",
       "      <td>30,4</td>\n",
       "      <td>54,1</td>\n",
       "      <td>237,8</td>\n",
       "      <td>97,9</td>\n",
       "      <td>24,5</td>\n",
       "      <td>613</td>\n",
       "      <td>89,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>el Clot</td>\n",
       "      <td>2012</td>\n",
       "      <td>27.201</td>\n",
       "      <td>0,7</td>\n",
       "      <td>39.085</td>\n",
       "      <td>14.079</td>\n",
       "      <td>13.122</td>\n",
       "      <td>13,1</td>\n",
       "      <td>9,2</td>\n",
       "      <td>60,4</td>\n",
       "      <td>...</td>\n",
       "      <td>14,4</td>\n",
       "      <td>21,6</td>\n",
       "      <td>9,3</td>\n",
       "      <td>30,4</td>\n",
       "      <td>54,1</td>\n",
       "      <td>237,8</td>\n",
       "      <td>95,5</td>\n",
       "      <td>24,5</td>\n",
       "      <td>613</td>\n",
       "      <td>81,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>el Putxet i el Farró</td>\n",
       "      <td>2016</td>\n",
       "      <td>27.201</td>\n",
       "      <td>0,8</td>\n",
       "      <td>34.996</td>\n",
       "      <td>16.176</td>\n",
       "      <td>13.441</td>\n",
       "      <td>14,3</td>\n",
       "      <td>10,0</td>\n",
       "      <td>54,7</td>\n",
       "      <td>...</td>\n",
       "      <td>13,0</td>\n",
       "      <td>48,9</td>\n",
       "      <td>9,3</td>\n",
       "      <td>30,4</td>\n",
       "      <td>54,1</td>\n",
       "      <td>237,8</td>\n",
       "      <td>213,7</td>\n",
       "      <td>24,5</td>\n",
       "      <td>613</td>\n",
       "      <td>141,0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>la Trinitat Nova</td>\n",
       "      <td>2016</td>\n",
       "      <td>27.201</td>\n",
       "      <td>0,6</td>\n",
       "      <td>12.982</td>\n",
       "      <td>3.813</td>\n",
       "      <td>3.458</td>\n",
       "      <td>14,6</td>\n",
       "      <td>10,2</td>\n",
       "      <td>56,5</td>\n",
       "      <td>...</td>\n",
       "      <td>18,7</td>\n",
       "      <td>7,5</td>\n",
       "      <td>9,3</td>\n",
       "      <td>30,4</td>\n",
       "      <td>54,1</td>\n",
       "      <td>237,8</td>\n",
       "      <td>62,5</td>\n",
       "      <td>24,5</td>\n",
       "      <td>613</td>\n",
       "      <td>35,6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Torre Baró</td>\n",
       "      <td>2018</td>\n",
       "      <td>2.931</td>\n",
       "      <td>1,7</td>\n",
       "      <td>1.653</td>\n",
       "      <td>1.457</td>\n",
       "      <td>1.474</td>\n",
       "      <td>17,9</td>\n",
       "      <td>12,5</td>\n",
       "      <td>56,7</td>\n",
       "      <td>...</td>\n",
       "      <td>18,7</td>\n",
       "      <td>8,2</td>\n",
       "      <td>9,7</td>\n",
       "      <td>21,8</td>\n",
       "      <td>46,2</td>\n",
       "      <td>290,7</td>\n",
       "      <td>77,8</td>\n",
       "      <td>24,5</td>\n",
       "      <td>169</td>\n",
       "      <td>46,5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>la Vila de Gràcia</td>\n",
       "      <td>2013</td>\n",
       "      <td>52.586</td>\n",
       "      <td>1,3</td>\n",
       "      <td>39.657</td>\n",
       "      <td>28.233</td>\n",
       "      <td>24.353</td>\n",
       "      <td>10,2</td>\n",
       "      <td>7,5</td>\n",
       "      <td>62,7</td>\n",
       "      <td>...</td>\n",
       "      <td>17,6</td>\n",
       "      <td>10,3</td>\n",
       "      <td>8,5</td>\n",
       "      <td>19,6</td>\n",
       "      <td>45,9</td>\n",
       "      <td>379,9</td>\n",
       "      <td>128,5</td>\n",
       "      <td>28,1</td>\n",
       "      <td>93</td>\n",
       "      <td>146,6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>el Poblenou</td>\n",
       "      <td>2014</td>\n",
       "      <td>52.586</td>\n",
       "      <td>1,5</td>\n",
       "      <td>21.634</td>\n",
       "      <td>17.182</td>\n",
       "      <td>16.243</td>\n",
       "      <td>15,9</td>\n",
       "      <td>7,2</td>\n",
       "      <td>60,0</td>\n",
       "      <td>...</td>\n",
       "      <td>16,6</td>\n",
       "      <td>29,3</td>\n",
       "      <td>8,5</td>\n",
       "      <td>19,6</td>\n",
       "      <td>45,9</td>\n",
       "      <td>379,9</td>\n",
       "      <td>94,2</td>\n",
       "      <td>28,1</td>\n",
       "      <td>93</td>\n",
       "      <td>89,6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>800</th>\n",
       "      <td>Vilapicina i la Torre Llobeta</td>\n",
       "      <td>2013</td>\n",
       "      <td>25.527</td>\n",
       "      <td>0,6</td>\n",
       "      <td>45.021</td>\n",
       "      <td>13.798</td>\n",
       "      <td>11.729</td>\n",
       "      <td>11,9</td>\n",
       "      <td>8,6</td>\n",
       "      <td>55,2</td>\n",
       "      <td>...</td>\n",
       "      <td>12,2</td>\n",
       "      <td>18,7</td>\n",
       "      <td>8,5</td>\n",
       "      <td>19,6</td>\n",
       "      <td>45,9</td>\n",
       "      <td>379,9</td>\n",
       "      <td>88,5</td>\n",
       "      <td>28,1</td>\n",
       "      <td>93</td>\n",
       "      <td>71,7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>801</th>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>2017</td>\n",
       "      <td>25.527</td>\n",
       "      <td>1,3</td>\n",
       "      <td>11.335</td>\n",
       "      <td>7.489</td>\n",
       "      <td>7.404</td>\n",
       "      <td>8,2</td>\n",
       "      <td>8,9</td>\n",
       "      <td>65,0</td>\n",
       "      <td>...</td>\n",
       "      <td>36,0</td>\n",
       "      <td>28,6</td>\n",
       "      <td>8,5</td>\n",
       "      <td>19,6</td>\n",
       "      <td>45,9</td>\n",
       "      <td>379,9</td>\n",
       "      <td>110,3</td>\n",
       "      <td>28,1</td>\n",
       "      <td>93</td>\n",
       "      <td>84,8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>802</th>\n",
       "      <td>Porta</td>\n",
       "      <td>2018</td>\n",
       "      <td>26.557</td>\n",
       "      <td>0,8</td>\n",
       "      <td>30.433</td>\n",
       "      <td>14.214</td>\n",
       "      <td>12.343</td>\n",
       "      <td>12,3</td>\n",
       "      <td>8,9</td>\n",
       "      <td>55,2</td>\n",
       "      <td>...</td>\n",
       "      <td>20,2</td>\n",
       "      <td>17,9</td>\n",
       "      <td>7,6</td>\n",
       "      <td>25,2</td>\n",
       "      <td>57,0</td>\n",
       "      <td>244,1</td>\n",
       "      <td>76,6</td>\n",
       "      <td>28,1</td>\n",
       "      <td>1.196</td>\n",
       "      <td>64,4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>803 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Barri   Any Població Superfície_(km2)  \\\n",
       "0    Sant Pere, Santa Caterina i la Ribera  2012   22.873              1,1   \n",
       "1                                  el Clot  2012   27.201              0,7   \n",
       "2                    el Putxet i el Farró  2016   27.201              0,8   \n",
       "3                         la Trinitat Nova  2016   27.201              0,6   \n",
       "4                              Torre Baró  2018    2.931              1,7   \n",
       "..                                     ...   ...      ...              ...   \n",
       "798                     la Vila de Gràcia  2013   52.586              1,3   \n",
       "799                            el Poblenou  2014   52.586              1,5   \n",
       "800          Vilapicina i la Torre Llobeta  2013   25.527              0,6   \n",
       "801                         la Barceloneta  2017   25.527              1,3   \n",
       "802                                  Porta  2018   26.557              0,8   \n",
       "\n",
       "    Densitat_(hab/km2)   Dones   Homes edat_0_14 edat_15_24 edat_25_64  ...  \\\n",
       "0               20.527  11.479  11.394       9,7        8,4       66,0  ...   \n",
       "1               39.085  14.079  13.122      13,1        9,2       60,4  ...   \n",
       "2               34.996  16.176  13.441      14,3       10,0       54,7  ...   \n",
       "3               12.982   3.813   3.458      14,6       10,2       56,5  ...   \n",
       "4                1.653   1.457   1.474      17,9       12,5       56,7  ...   \n",
       "..                 ...     ...     ...       ...        ...        ...  ...   \n",
       "798             39.657  28.233  24.353      10,2        7,5       62,7  ...   \n",
       "799             21.634  17.182  16.243      15,9        7,2       60,0  ...   \n",
       "800             45.021  13.798  11.729      11,9        8,6       55,2  ...   \n",
       "801             11.335   7.489   7.404       8,2        8,9       65,0  ...   \n",
       "802             30.433  14.214  12.343      12,3        8,9       55,2  ...   \n",
       "\n",
       "    nacionalitat_Estrangers Titulats_superiors Taxa_natalitat  \\\n",
       "0                      38,5               33,7            9,3   \n",
       "1                      14,4               21,6            9,3   \n",
       "2                      13,0               48,9            9,3   \n",
       "3                      18,7                7,5            9,3   \n",
       "4                      18,7                8,2            9,7   \n",
       "..                      ...                ...            ...   \n",
       "798                    17,6               10,3            8,5   \n",
       "799                    16,6               29,3            8,5   \n",
       "800                    12,2               18,7            8,5   \n",
       "801                    36,0               28,6            8,5   \n",
       "802                    20,2               17,9            7,6   \n",
       "\n",
       "    mes_de_65_anys_viu_sola Index_de_sobreenvelliment Turismes  Motos  \\\n",
       "0                      30,4                      54,1    237,8   97,9   \n",
       "1                      30,4                      54,1    237,8   95,5   \n",
       "2                      30,4                      54,1    237,8  213,7   \n",
       "3                      30,4                      54,1    237,8   62,5   \n",
       "4                      21,8                      46,2    290,7   77,8   \n",
       "..                      ...                       ...      ...    ...   \n",
       "798                    19,6                      45,9    379,9  128,5   \n",
       "799                    19,6                      45,9    379,9   94,2   \n",
       "800                    19,6                      45,9    379,9   88,5   \n",
       "801                    19,6                      45,9    379,9  110,3   \n",
       "802                    25,2                      57,0    244,1   76,6   \n",
       "\n",
       "    Ciclomotors aturats_registrats Renda_familiar  \n",
       "0          24,5                613           89,1  \n",
       "1          24,5                613           81,1  \n",
       "2          24,5                613          141,0  \n",
       "3          24,5                613           35,6  \n",
       "4          24,5                169           46,5  \n",
       "..          ...                ...            ...  \n",
       "798        28,1                 93          146,6  \n",
       "799        28,1                 93           89,6  \n",
       "800        28,1                 93           71,7  \n",
       "801        28,1                 93           84,8  \n",
       "802        28,1              1.196           64,4  \n",
       "\n",
       "[803 rows x 26 columns]"
      ]
     },
     "execution_count": 261,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_barris = pd.DataFrame(values)\n",
    "data_barris"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "id": "3eaef42d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 803 entries, 0 to 802\n",
      "Data columns (total 26 columns):\n",
      " #   Column                     Non-Null Count  Dtype \n",
      "---  ------                     --------------  ----- \n",
      " 0   Barri                      803 non-null    object\n",
      " 1   Any                        803 non-null    object\n",
      " 2   Població                   803 non-null    object\n",
      " 3   Superfície_(km2)           803 non-null    object\n",
      " 4   Densitat_(hab/km2)         803 non-null    object\n",
      " 5   Dones                      803 non-null    object\n",
      " 6   Homes                      803 non-null    object\n",
      " 7   edat_0_14                  803 non-null    object\n",
      " 8   edat_15_24                 803 non-null    object\n",
      " 9   edat_25_64                 803 non-null    object\n",
      " 10  edat_65_i_mes              803 non-null    object\n",
      " 11  naixement_Barcelona        803 non-null    object\n",
      " 12  naixement_Resta_Catalunya  803 non-null    object\n",
      " 13  naixement_Resta_Espanya    803 non-null    object\n",
      " 14  naixement_Estranger        803 non-null    object\n",
      " 15  nacionalitat_Espanyols     803 non-null    object\n",
      " 16  nacionalitat_Estrangers    803 non-null    object\n",
      " 17  Titulats_superiors         803 non-null    object\n",
      " 18  Taxa_natalitat             803 non-null    object\n",
      " 19  mes_de_65_anys_viu_sola    803 non-null    object\n",
      " 20  Index_de_sobreenvelliment  803 non-null    object\n",
      " 21  Turismes                   803 non-null    object\n",
      " 22  Motos                      803 non-null    object\n",
      " 23  Ciclomotors                803 non-null    object\n",
      " 24  aturats_registrats         803 non-null    object\n",
      " 25  Renda_familiar             803 non-null    object\n",
      "dtypes: object(26)\n",
      "memory usage: 163.2+ KB\n"
     ]
    }
   ],
   "source": [
    "#show the info of the dataframe\n",
    "data_barris.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea25337d",
   "metadata": {},
   "source": [
    "now we are going to clean up the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "id": "0c0129db",
   "metadata": {},
   "outputs": [],
   "source": [
    "#replace \",\" with \".\" in the dataframe\n",
    "data_barris = data_barris.replace(',','.', regex=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "id": "83235668",
   "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>Barri</th>\n",
       "      <th>Any</th>\n",
       "      <th>Població</th>\n",
       "      <th>Superfície_(km2)</th>\n",
       "      <th>Densitat_(hab/km2)</th>\n",
       "      <th>Dones</th>\n",
       "      <th>Homes</th>\n",
       "      <th>edat_0_14</th>\n",
       "      <th>edat_15_24</th>\n",
       "      <th>edat_25_64</th>\n",
       "      <th>...</th>\n",
       "      <th>nacionalitat_Estrangers</th>\n",
       "      <th>Titulats_superiors</th>\n",
       "      <th>Taxa_natalitat</th>\n",
       "      <th>mes_de_65_anys_viu_sola</th>\n",
       "      <th>Index_de_sobreenvelliment</th>\n",
       "      <th>Turismes</th>\n",
       "      <th>Motos</th>\n",
       "      <th>Ciclomotors</th>\n",
       "      <th>aturats_registrats</th>\n",
       "      <th>Renda_familiar</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sant Pere. Santa Caterina i la Ribera</td>\n",
       "      <td>2012</td>\n",
       "      <td>22.873</td>\n",
       "      <td>1.1</td>\n",
       "      <td>20.527</td>\n",
       "      <td>11.479</td>\n",
       "      <td>11.394</td>\n",
       "      <td>9.7</td>\n",
       "      <td>8.4</td>\n",
       "      <td>66.0</td>\n",
       "      <td>...</td>\n",
       "      <td>38.5</td>\n",
       "      <td>33.7</td>\n",
       "      <td>9.3</td>\n",
       "      <td>30.4</td>\n",
       "      <td>54.1</td>\n",
       "      <td>237.8</td>\n",
       "      <td>97.9</td>\n",
       "      <td>24.5</td>\n",
       "      <td>613</td>\n",
       "      <td>89.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>el Clot</td>\n",
       "      <td>2012</td>\n",
       "      <td>27.201</td>\n",
       "      <td>0.7</td>\n",
       "      <td>39.085</td>\n",
       "      <td>14.079</td>\n",
       "      <td>13.122</td>\n",
       "      <td>13.1</td>\n",
       "      <td>9.2</td>\n",
       "      <td>60.4</td>\n",
       "      <td>...</td>\n",
       "      <td>14.4</td>\n",
       "      <td>21.6</td>\n",
       "      <td>9.3</td>\n",
       "      <td>30.4</td>\n",
       "      <td>54.1</td>\n",
       "      <td>237.8</td>\n",
       "      <td>95.5</td>\n",
       "      <td>24.5</td>\n",
       "      <td>613</td>\n",
       "      <td>81.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>el Putxet i el Farró</td>\n",
       "      <td>2016</td>\n",
       "      <td>27.201</td>\n",
       "      <td>0.8</td>\n",
       "      <td>34.996</td>\n",
       "      <td>16.176</td>\n",
       "      <td>13.441</td>\n",
       "      <td>14.3</td>\n",
       "      <td>10.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>48.9</td>\n",
       "      <td>9.3</td>\n",
       "      <td>30.4</td>\n",
       "      <td>54.1</td>\n",
       "      <td>237.8</td>\n",
       "      <td>213.7</td>\n",
       "      <td>24.5</td>\n",
       "      <td>613</td>\n",
       "      <td>141.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>la Trinitat Nova</td>\n",
       "      <td>2016</td>\n",
       "      <td>27.201</td>\n",
       "      <td>0.6</td>\n",
       "      <td>12.982</td>\n",
       "      <td>3.813</td>\n",
       "      <td>3.458</td>\n",
       "      <td>14.6</td>\n",
       "      <td>10.2</td>\n",
       "      <td>56.5</td>\n",
       "      <td>...</td>\n",
       "      <td>18.7</td>\n",
       "      <td>7.5</td>\n",
       "      <td>9.3</td>\n",
       "      <td>30.4</td>\n",
       "      <td>54.1</td>\n",
       "      <td>237.8</td>\n",
       "      <td>62.5</td>\n",
       "      <td>24.5</td>\n",
       "      <td>613</td>\n",
       "      <td>35.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Torre Baró</td>\n",
       "      <td>2018</td>\n",
       "      <td>2.931</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.653</td>\n",
       "      <td>1.457</td>\n",
       "      <td>1.474</td>\n",
       "      <td>17.9</td>\n",
       "      <td>12.5</td>\n",
       "      <td>56.7</td>\n",
       "      <td>...</td>\n",
       "      <td>18.7</td>\n",
       "      <td>8.2</td>\n",
       "      <td>9.7</td>\n",
       "      <td>21.8</td>\n",
       "      <td>46.2</td>\n",
       "      <td>290.7</td>\n",
       "      <td>77.8</td>\n",
       "      <td>24.5</td>\n",
       "      <td>169</td>\n",
       "      <td>46.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>la Vila de Gràcia</td>\n",
       "      <td>2013</td>\n",
       "      <td>52.586</td>\n",
       "      <td>1.3</td>\n",
       "      <td>39.657</td>\n",
       "      <td>28.233</td>\n",
       "      <td>24.353</td>\n",
       "      <td>10.2</td>\n",
       "      <td>7.5</td>\n",
       "      <td>62.7</td>\n",
       "      <td>...</td>\n",
       "      <td>17.6</td>\n",
       "      <td>10.3</td>\n",
       "      <td>8.5</td>\n",
       "      <td>19.6</td>\n",
       "      <td>45.9</td>\n",
       "      <td>379.9</td>\n",
       "      <td>128.5</td>\n",
       "      <td>28.1</td>\n",
       "      <td>93</td>\n",
       "      <td>146.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>el Poblenou</td>\n",
       "      <td>2014</td>\n",
       "      <td>52.586</td>\n",
       "      <td>1.5</td>\n",
       "      <td>21.634</td>\n",
       "      <td>17.182</td>\n",
       "      <td>16.243</td>\n",
       "      <td>15.9</td>\n",
       "      <td>7.2</td>\n",
       "      <td>60.0</td>\n",
       "      <td>...</td>\n",
       "      <td>16.6</td>\n",
       "      <td>29.3</td>\n",
       "      <td>8.5</td>\n",
       "      <td>19.6</td>\n",
       "      <td>45.9</td>\n",
       "      <td>379.9</td>\n",
       "      <td>94.2</td>\n",
       "      <td>28.1</td>\n",
       "      <td>93</td>\n",
       "      <td>89.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>800</th>\n",
       "      <td>Vilapicina i la Torre Llobeta</td>\n",
       "      <td>2013</td>\n",
       "      <td>25.527</td>\n",
       "      <td>0.6</td>\n",
       "      <td>45.021</td>\n",
       "      <td>13.798</td>\n",
       "      <td>11.729</td>\n",
       "      <td>11.9</td>\n",
       "      <td>8.6</td>\n",
       "      <td>55.2</td>\n",
       "      <td>...</td>\n",
       "      <td>12.2</td>\n",
       "      <td>18.7</td>\n",
       "      <td>8.5</td>\n",
       "      <td>19.6</td>\n",
       "      <td>45.9</td>\n",
       "      <td>379.9</td>\n",
       "      <td>88.5</td>\n",
       "      <td>28.1</td>\n",
       "      <td>93</td>\n",
       "      <td>71.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>801</th>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>2017</td>\n",
       "      <td>25.527</td>\n",
       "      <td>1.3</td>\n",
       "      <td>11.335</td>\n",
       "      <td>7.489</td>\n",
       "      <td>7.404</td>\n",
       "      <td>8.2</td>\n",
       "      <td>8.9</td>\n",
       "      <td>65.0</td>\n",
       "      <td>...</td>\n",
       "      <td>36.0</td>\n",
       "      <td>28.6</td>\n",
       "      <td>8.5</td>\n",
       "      <td>19.6</td>\n",
       "      <td>45.9</td>\n",
       "      <td>379.9</td>\n",
       "      <td>110.3</td>\n",
       "      <td>28.1</td>\n",
       "      <td>93</td>\n",
       "      <td>84.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>802</th>\n",
       "      <td>Porta</td>\n",
       "      <td>2018</td>\n",
       "      <td>26.557</td>\n",
       "      <td>0.8</td>\n",
       "      <td>30.433</td>\n",
       "      <td>14.214</td>\n",
       "      <td>12.343</td>\n",
       "      <td>12.3</td>\n",
       "      <td>8.9</td>\n",
       "      <td>55.2</td>\n",
       "      <td>...</td>\n",
       "      <td>20.2</td>\n",
       "      <td>17.9</td>\n",
       "      <td>7.6</td>\n",
       "      <td>25.2</td>\n",
       "      <td>57.0</td>\n",
       "      <td>244.1</td>\n",
       "      <td>76.6</td>\n",
       "      <td>28.1</td>\n",
       "      <td>1.196</td>\n",
       "      <td>64.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>803 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Barri   Any Població Superfície_(km2)  \\\n",
       "0    Sant Pere. Santa Caterina i la Ribera  2012   22.873              1.1   \n",
       "1                                  el Clot  2012   27.201              0.7   \n",
       "2                    el Putxet i el Farró  2016   27.201              0.8   \n",
       "3                         la Trinitat Nova  2016   27.201              0.6   \n",
       "4                              Torre Baró  2018    2.931              1.7   \n",
       "..                                     ...   ...      ...              ...   \n",
       "798                     la Vila de Gràcia  2013   52.586              1.3   \n",
       "799                            el Poblenou  2014   52.586              1.5   \n",
       "800          Vilapicina i la Torre Llobeta  2013   25.527              0.6   \n",
       "801                         la Barceloneta  2017   25.527              1.3   \n",
       "802                                  Porta  2018   26.557              0.8   \n",
       "\n",
       "    Densitat_(hab/km2)   Dones   Homes edat_0_14 edat_15_24 edat_25_64  ...  \\\n",
       "0               20.527  11.479  11.394       9.7        8.4       66.0  ...   \n",
       "1               39.085  14.079  13.122      13.1        9.2       60.4  ...   \n",
       "2               34.996  16.176  13.441      14.3       10.0       54.7  ...   \n",
       "3               12.982   3.813   3.458      14.6       10.2       56.5  ...   \n",
       "4                1.653   1.457   1.474      17.9       12.5       56.7  ...   \n",
       "..                 ...     ...     ...       ...        ...        ...  ...   \n",
       "798             39.657  28.233  24.353      10.2        7.5       62.7  ...   \n",
       "799             21.634  17.182  16.243      15.9        7.2       60.0  ...   \n",
       "800             45.021  13.798  11.729      11.9        8.6       55.2  ...   \n",
       "801             11.335   7.489   7.404       8.2        8.9       65.0  ...   \n",
       "802             30.433  14.214  12.343      12.3        8.9       55.2  ...   \n",
       "\n",
       "    nacionalitat_Estrangers Titulats_superiors Taxa_natalitat  \\\n",
       "0                      38.5               33.7            9.3   \n",
       "1                      14.4               21.6            9.3   \n",
       "2                      13.0               48.9            9.3   \n",
       "3                      18.7                7.5            9.3   \n",
       "4                      18.7                8.2            9.7   \n",
       "..                      ...                ...            ...   \n",
       "798                    17.6               10.3            8.5   \n",
       "799                    16.6               29.3            8.5   \n",
       "800                    12.2               18.7            8.5   \n",
       "801                    36.0               28.6            8.5   \n",
       "802                    20.2               17.9            7.6   \n",
       "\n",
       "    mes_de_65_anys_viu_sola Index_de_sobreenvelliment Turismes  Motos  \\\n",
       "0                      30.4                      54.1    237.8   97.9   \n",
       "1                      30.4                      54.1    237.8   95.5   \n",
       "2                      30.4                      54.1    237.8  213.7   \n",
       "3                      30.4                      54.1    237.8   62.5   \n",
       "4                      21.8                      46.2    290.7   77.8   \n",
       "..                      ...                       ...      ...    ...   \n",
       "798                    19.6                      45.9    379.9  128.5   \n",
       "799                    19.6                      45.9    379.9   94.2   \n",
       "800                    19.6                      45.9    379.9   88.5   \n",
       "801                    19.6                      45.9    379.9  110.3   \n",
       "802                    25.2                      57.0    244.1   76.6   \n",
       "\n",
       "    Ciclomotors aturats_registrats Renda_familiar  \n",
       "0          24.5                613           89.1  \n",
       "1          24.5                613           81.1  \n",
       "2          24.5                613          141.0  \n",
       "3          24.5                613           35.6  \n",
       "4          24.5                169           46.5  \n",
       "..          ...                ...            ...  \n",
       "798        28.1                 93          146.6  \n",
       "799        28.1                 93           89.6  \n",
       "800        28.1                 93           71.7  \n",
       "801        28.1                 93           84.8  \n",
       "802        28.1              1.196           64.4  \n",
       "\n",
       "[803 rows x 26 columns]"
      ]
     },
     "execution_count": 267,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_barris"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "id": "a098f872",
   "metadata": {},
   "outputs": [],
   "source": [
    "#change the type of the columns to int \n",
    "to_change = [\"Any\"]\n",
    "data_barris[to_change] = data_barris[to_change].astype(int)\n",
    "\n",
    "#change Població: extract \".\" and transform to int\n",
    "data_barris[\"Població\"] = data_barris[\"Població\"].str.replace(\".\",\"\")\n",
    "data_barris[\"Població\"] = data_barris[\"Població\"].astype(int)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "id": "9c7b4a13",
   "metadata": {},
   "outputs": [],
   "source": [
    "# safe the dataframe to a csv file as a backup\n",
    "data_barris.to_csv(\"data_barris.csv\", index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "id": "50830303",
   "metadata": {},
   "outputs": [],
   "source": [
    "#read the csv file\n",
    "data_barris = pd.read_csv(\"data_barris.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.8 64-bit",
   "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.10.8"
  },
  "vscode": {
   "interpreter": {
    "hash": "b0fa6594d8f4cbf19f97940f81e996739fb7646882a419484c72d19e05852a7e"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}