web_scraping_tutorial.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "web_scraping_tutorial.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/virtualmarioe/Web_scraping_tutorial/blob/main/web_scraping_tutorial.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KxcSk_hsPqdT"
      },
      "source": [
        "<p><img alt=\"Web scraping tutorial\" height=\"45px\" src=\"https://aiconica.net/previews/spider-web-icon-1027.png\" align=\"left\" hspace=\"10px\" vspace=\"0px\"></p>\n",
        "\n",
        "<h1>Web scraping tutorial</h1>\n",
        "\n",
        "This notebook presents an introduction to Web scraping. Web scraping is the process of extracting data from websites or other online sources and copying the data into an structured form (e.g., a database) enabling further retrieval and analysis.\n",
        "\n",
        "For this particular tutorial, we are going to extract demografic information (e.g., country, state and population) of Colombian towns from <a href = \"https://es.wikipedia.org/wiki/Municipios_de_Colombia\"> Wikipedia</a>.\n",
        "\n",
        "The tutorial is written in Python and will use two different methods, of the many available, for pulling the data, <a href = \"https://www.crummy.com/software/BeautifulSoup/bs4/doc/\">Beautiful Soup</a> and <a href = \"https://pandas.pydata.org/docs/\"> Pandas</a>.\n",
        "\n",
        "The tutorial is divided into the following 4 sections:\n",
        "\n",
        " - **Section 1: Method Beautiful Soup**\n",
        " - **Section 2: Method Pandas**\n",
        " - **Section 3: Structuring and cleaning the data**\n",
        " - **Section 4: Data saving**\n",
        "\n",
        " ____"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jwCLxEHbxhcf"
      },
      "source": [
        "<h2> Setup </h2>\n",
        "\n",
        "First, we will import all the required libraries."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7fr5Sk7lvgc0"
      },
      "source": [
        "# Importing libraries\n",
        "\n",
        "import requests\n",
        "import urllib.request\n",
        "import time\n",
        "from bs4 import BeautifulSoup\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "from urllib.request import urlopen\n",
        "import re\n",
        "import seaborn as sns"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jy1pEDeBdrGu"
      },
      "source": [
        "<h3> Section 1: Method Beautiful Soup </h3>\n",
        "\n",
        "The data we are interested in is distributed across multiple Wikipedia pages and tables. Therefore, we first need to read and parse the main table containing the list with all the towns and a link per town where the actual demographic information is located.\n",
        "\n",
        "We will go through the following steps:\n",
        "\n",
        "  - 1.1. Building the main table and parsing its content\n",
        "  - 1.2. Extracting all data contained in tables\n",
        "  - 1.3. Building lists to hold the extracted data\n",
        "  - 1.4. Structuring the extracted data\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2KwEa7V5Nuop"
      },
      "source": [
        "**1.1. Building the main table and parsing its content**\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vXkW71aaPif9"
      },
      "source": [
        "# 1. Building the URL and parsing it with Beautiful Soup\n",
        "wiki_es = 'https://es.wikipedia.org'\n",
        "mun_col = '/wiki/Municipios_de_Colombia'\n",
        "url = wiki_es + mun_col\n",
        "html = urlopen(url) \n",
        "soup = BeautifulSoup(html, 'html.parser')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RSyF-YsBiJ-S"
      },
      "source": [
        "**1.2. Extracting all data contained in tables**\n",
        "\n",
        "Extracting all data contained in the webpage's sections labeled with the tag `'table'`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HnqvTUoowtxz"
      },
      "source": [
        "# 2. Finding all data with tag 'table'\n",
        "tables = soup.find_all('table')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KH-tHt0f8Afw"
      },
      "source": [
        "**1.3. Building lists to hold the extracted data**\n",
        "\n",
        "To extract the links contained in the tables it is necessary to cycle across all rows, labeled witht the tag `'tr'`, and cells, labeled with the tag `'td'`. Finally, at each cell the link of interest, labeled with the tag `'href'`, will be appended to `links_anex` list, which will be used to build the final URL for calling the webpages we are interested in."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yjEJyRely8FK"
      },
      "source": [
        "# 3. Building lists to hold the extracted data\n",
        "\n",
        "# Preallocating variables for each lists\n",
        "departamentos = []\n",
        "numero_de_municipios = []\n",
        "links_anex = []\n",
        "\n",
        "# Cycling through the table rows\n",
        "for table in tables:\n",
        "    rows = table.find_all('tr')\n",
        "    \n",
        "    for row in rows:\n",
        "        cells = row.find_all('td')\n",
        "        \n",
        "        # The main page contains multiple tables.\n",
        "        # Finding the table with more that 2 cells, which\n",
        "        # the one we are interested.\n",
        "        if len(cells) > 2:\n",
        "          \n",
        "            # Building a list with the state names\n",
        "            departamento = cells[0]\n",
        "            departamentos.append(departamento.text.strip())\n",
        "            \n",
        "            # Building a list with the town names\n",
        "            municipio = cells[1]\n",
        "            numero_de_municipios.append(municipio.text.strip())\n",
        "\n",
        "            # Building a list with the state's link\n",
        "            link = cells[1]\n",
        "            links_anex.append(municipio.contents[0]['href'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5Gd-4WpojLSe"
      },
      "source": [
        "**1.4. Structuring the extracted data**\n",
        "\n",
        "In order to store the extracted data in a format and a structure that can be used for further analysis, we will put all the data in a pandas `DataFrame`. To do this we will create a pandas `series` from each list created in point 3. and then contenating all series into a single df."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "u0vOu8Vz9e2l"
      },
      "source": [
        "# Building DataFrame with name of States and number of towns\n",
        "\n",
        "# Creating pandas series from scraped list created in 1.3\n",
        "deptos_serie = pd.Series(departamentos,name='Departamento')\n",
        "num_mun_serie = pd.Series(numero_de_municipios,name='# Municipios')\n",
        "links_serie = pd.Series(links_anex, name='Link')\n",
        "\n",
        "# Building all series into a single df\n",
        "df_municipios_info = pd.concat([deptos_serie,num_mun_serie,links_serie],axis=1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UZGeK8dJWcB9"
      },
      "source": [
        "Let's check how the current `DataFrame` looks kike."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 243
        },
        "id": "NTzczWslWoAy",
        "outputId": "1ae86995-fac3-429a-c0da-f8be8bf17201"
      },
      "source": [
        "# Checking df dimenssions and head\n",
        "print('The dimenssions of the df_municipios_info are: ' +\n",
        "      str(df_municipios_info.shape))\n",
        "print('Here are the first 5 rows:')\n",
        "df_municipios_info.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "The dimenssions of the df_municipios_info are: (33, 3)\n",
            "Here are the first 5 raws:\n"
          ]
        },
        {
          "output_type": "execute_result",
          "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>Departamento</th>\n",
              "      <th># Municipios</th>\n",
              "      <th>Link</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Amazonas</td>\n",
              "      <td>2</td>\n",
              "      <td>/wiki/Anexo:Municipios_de_Amazonas_(Colombia)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Antioquia</td>\n",
              "      <td>125</td>\n",
              "      <td>/wiki/Anexo:Municipios_de_Antioquia</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Arauca</td>\n",
              "      <td>7</td>\n",
              "      <td>/wiki/Anexo:Municipios_de_Arauca</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Atlántico</td>\n",
              "      <td>23</td>\n",
              "      <td>/wiki/Anexo:Municipios_de_Atl%C3%A1ntico</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Bolívar</td>\n",
              "      <td>46</td>\n",
              "      <td>/wiki/Anexo:Municipios_de_Bol%C3%ADvar_(Colombia)</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  Departamento # Municipios                                               Link\n",
              "0     Amazonas            2      /wiki/Anexo:Municipios_de_Amazonas_(Colombia)\n",
              "1    Antioquia          125                /wiki/Anexo:Municipios_de_Antioquia\n",
              "2       Arauca            7                   /wiki/Anexo:Municipios_de_Arauca\n",
              "3    Atlántico           23           /wiki/Anexo:Municipios_de_Atl%C3%A1ntico\n",
              "4      Bolívar           46  /wiki/Anexo:Municipios_de_Bol%C3%ADvar_(Colombia)"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tcEmwWLtW9fR"
      },
      "source": [
        "So, now we have a `df` with the following information for each of the 33 states. The state's name, number of towns and the URL where the info for all State's town can be pulled."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Nxgy4MMAxNbp"
      },
      "source": [
        "<h3> Section 2: Method Pandas</h3>\n",
        "\n",
        "We will use `Pandas` to pull the demographic data of each town across all states.\n",
        "\n",
        "For this extraction we will use the function <a href = \"https://pandas.pydata.org/docs/user_guide/io.html#io-read-html\">`pd.read_html()`</a>, which takes a HTML URL and parse its content into a list of `DataFrames`. We will pass the URL with the function `get` from the library <a href = \"https://docs.python-requests.org/en/latest/\">Request</a>."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DFnA2J960sOB"
      },
      "source": [
        "# Looping through all the list of states to scrap available population data\n",
        "\n",
        "# Preallocation of list for all df with town info\n",
        "df_list_municipios = []\n",
        "df_habitantes_info = []\n",
        "df_habitantes_info_all = []\n",
        "\n",
        "\n",
        "# Loop for data collection\n",
        "for muni_link in enumerate((df_municipios_info.iloc[:]['Link']).tolist()):\n",
        "    \n",
        "    curr_link = muni_link[1]\n",
        "\n",
        "    # Current town's name\n",
        "    dept_name = df_municipios_info.iloc[muni_link[0]]['Departamento']\n",
        "    \n",
        "    curr_r = requests.get(wiki_es + curr_link)\n",
        "\n",
        "    # Scraping the data from the current URL ising Pandas.\n",
        "    curr_list_dfs = pd.read_html(curr_r.text)\n",
        "\n",
        "    # Loop for selecting and extracting data for each town\n",
        "    for df_idx in enumerate(curr_list_dfs):\n",
        "\n",
        "        # Checking for field town name. This can be either 'Name' or 'Municipio'\n",
        "        # Thus we will make them homogeneous by using 'Municipio' in all.\n",
        "        if True in curr_list_dfs[df_idx[0]].columns.astype(str).str.contains(\n",
        "            pat = 'Nombre'):\n",
        "\n",
        "            # Changing 'Nombre' to 'Municipio'\n",
        "            df_habitantes_info = pd.DataFrame(list(\n",
        "                curr_list_dfs[df_idx[0]]['Nombre']), columns=['Municipio'])\n",
        "\n",
        "            # Adding State's name as first column\n",
        "            df_habitantes_info['Departamento'] = dept_name\n",
        "       \n",
        "            # Poplation information can be stored in columns called either \n",
        "            # 'Habitantes' or 'Población'. Thus, we need to make them homogeneous.\n",
        "            # Checking if the current df has a column called 'Habitantes'\n",
        "            if True in curr_list_dfs[df_idx[0]].columns.astype(\n",
        "                str).str.contains(pat = 'Habitantes'):\n",
        "                \n",
        "                # Getting the index of the column named 'Habitantes'\n",
        "                col_idx = list(curr_list_dfs[df_idx[0]].columns.astype(\n",
        "                    str).str.contains(pat = 'Habitantes')).index(True)\n",
        "                \n",
        "                col_name = curr_list_dfs[df_idx[0]].columns[col_idx]\n",
        "                \n",
        "                df_habitantes_info['Habitantes'] = (\n",
        "                    curr_list_dfs[df_idx[0]][col_name])\n",
        "\n",
        "            # Checking if the current df has a column called 'Población'\n",
        "            elif True in curr_list_dfs[df_idx[0]].columns.astype(\n",
        "                str).str.contains(pat = 'Población'):\n",
        "                \n",
        "                # Getting the index of the column named 'Población'\n",
        "                col_idx = list(curr_list_dfs[df_idx[0]].columns.astype(\n",
        "                    str).str.contains(pat = 'Población')).index(True)\n",
        "                \n",
        "                col_name = curr_list_dfs[df_idx[0]].columns[col_idx]\n",
        "                \n",
        "                df_habitantes_info['Habitantes'] = (\n",
        "                    curr_list_dfs[df_idx[0]][col_name])\n",
        "        \n",
        "        # Special case: The demographic info of Bogota is subdivided, \n",
        "        # therefore it needs to be agregated.\n",
        "        elif True in curr_list_dfs[df_idx[0]].columns.astype(\n",
        "            str).str.contains(pat = 'Localidad') & True in (\n",
        "                curr_list_dfs[df_idx[0]].columns.astype(str).str.contains(\n",
        "                    pat = 'Población')):\n",
        "            \n",
        "            col_idx = list(curr_list_dfs[df_idx[0]].columns.astype(\n",
        "                str).str.contains(pat = 'Localidad')).index(True)\n",
        "            \n",
        "            col_name = curr_list_dfs[df_idx[0]].columns[col_idx]\n",
        "            \n",
        "            df_habitantes_info = pd.DataFrame(list(\n",
        "                curr_list_dfs[df_idx[0]][col_name]), columns=['Municipio'])\n",
        "\n",
        "            # Adding State's name as first column\n",
        "            df_habitantes_info['Departamento'] = dept_name\n",
        "\n",
        "            # Checking if the \"Poblacion\" info exist in current df\n",
        "            if True in curr_list_dfs[df_idx[0]].columns.astype(\n",
        "                str).str.contains(pat = 'Población'):\n",
        "\n",
        "                col_idx = list(curr_list_dfs[df_idx[0]].columns.astype(\n",
        "                    str).str.contains(pat = 'Población')).index(True)\n",
        "                \n",
        "                col_name = curr_list_dfs[df_idx[0]].columns[col_idx]\n",
        "                \n",
        "                df_habitantes_info['Habitantes'] = (\n",
        "                    curr_list_dfs[df_idx[0]][col_name])\n",
        "                \n",
        "        # Appending current df to the list with all dfs      \n",
        "        df_habitantes_info_all.append(df_habitantes_info)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2YNsfScHoVPt"
      },
      "source": [
        "<h3>Section 3: Structuring and cleaning the data</h3>\n",
        "\n",
        "There are some <a href = \"https://pandas.pydata.org/docs/user_guide/io.html#io-html-gotchas\">issues</a> when parsing HTML tables with pandas. In our case the function generates some non-numeric characters in the population column. Therefore in order to be able to analyse the data further we first need to make the numeric variables homogeneous. This can be done by finding and replacing the desire characters in the population column using regular expressions `regex`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9Gjezo5xwyX6"
      },
      "source": [
        "# Formating the final df 'all_data'\n",
        "all_data = pd.concat(df_habitantes_info_all)\n",
        "all_data = all_data.reset_index()\n",
        "all_data.shape\n",
        "\n",
        "# Removing non-numeric characters\n",
        "all_data.Habitantes = all_data.Habitantes.replace(u'\\xa0', '', regex=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XYMGOHvJbVNv"
      },
      "source": [
        "After organising the the data into the the final DataFrame `all_data`, we can check the `df` before saving it."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 243
        },
        "id": "ET7hmB8sbaAJ",
        "outputId": "1dc98fa7-39db-4196-e71d-f7de9dbed014"
      },
      "source": [
        "# Checking df dimenssions and head\n",
        "print('The dimenssions of all_data are: ' +\n",
        "      str(all_data.shape))\n",
        "print('Here are the first 5 rows of the final df (all_data):')\n",
        "all_data.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "The dimenssions of all_data are: (1726, 4)\n",
            "Here are the first 5 rows of the final df (all_data):\n"
          ]
        },
        {
          "output_type": "execute_result",
          "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>index</th>\n",
              "      <th>Municipio</th>\n",
              "      <th>Departamento</th>\n",
              "      <th>Habitantes</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>El Encanto</td>\n",
              "      <td>Amazonas</td>\n",
              "      <td>4884</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>La Chorrera</td>\n",
              "      <td>Amazonas</td>\n",
              "      <td>3933</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>La Pedrera</td>\n",
              "      <td>Amazonas</td>\n",
              "      <td>5125</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>La Victoria</td>\n",
              "      <td>Amazonas</td>\n",
              "      <td>1115</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>Leticia</td>\n",
              "      <td>Amazonas</td>\n",
              "      <td>41639</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   index    Municipio Departamento Habitantes\n",
              "0      0   El Encanto     Amazonas       4884\n",
              "1      1  La Chorrera     Amazonas       3933\n",
              "2      2   La Pedrera     Amazonas       5125\n",
              "3      3  La Victoria     Amazonas       1115\n",
              "4      4      Leticia     Amazonas      41639"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 224
        },
        "id": "PZL7NsXIiLsH",
        "outputId": "9763d6cd-33ff-4215-fcc6-5c8b1134cd0b"
      },
      "source": [
        "# Checking df's tail\n",
        "print('Here are the last 5 rows:')\n",
        "all_data.tail()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Here are the last 5 rows:\n"
          ]
        },
        {
          "output_type": "execute_result",
          "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>index</th>\n",
              "      <th>Municipio</th>\n",
              "      <th>Departamento</th>\n",
              "      <th>Habitantes</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1721</th>\n",
              "      <td>27</td>\n",
              "      <td>Antonio Nariño y Rafael Uribe (norte)</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1722</th>\n",
              "      <td>28</td>\n",
              "      <td>Ciudad Bolívar (Oriente), Rafael Uribe (sur) y...</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1723</th>\n",
              "      <td>29</td>\n",
              "      <td>Ciudad Bolívar (centro-sur)</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1724</th>\n",
              "      <td>30</td>\n",
              "      <td>Ciudad Bolívar (noroccidente)</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1725</th>\n",
              "      <td>31</td>\n",
              "      <td>Sumapaz</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      index  ... Habitantes\n",
              "1721     27  ...        NaN\n",
              "1722     28  ...        NaN\n",
              "1723     29  ...        NaN\n",
              "1724     30  ...        NaN\n",
              "1725     31  ...        NaN\n",
              "\n",
              "[5 rows x 4 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kGZ09U4odynF"
      },
      "source": [
        "The final df contains, for each of the country's 1726 towns, the town's name, the state to which the town belongs to and the town's population. There are however some cells with invalid or no information that will need to be cleaned, so let's do that with pandas `dropna` function and creating a new, clean, DataFrame without NaNs."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 224
        },
        "id": "h6AQ8m8Xm8LZ",
        "outputId": "a195a1fb-d7e1-4f0c-976e-e649aac58b60"
      },
      "source": [
        "# Droping NaN's\n",
        "all_data_clean = all_data.dropna()\n",
        "print('Here are the last 5 rows of the clean df:')\n",
        "all_data_clean.tail()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Here are the last 5 rows of the clean df:\n"
          ]
        },
        {
          "output_type": "execute_result",
          "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>index</th>\n",
              "      <th>Municipio</th>\n",
              "      <th>Departamento</th>\n",
              "      <th>Habitantes</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1689</th>\n",
              "      <td>15</td>\n",
              "      <td>Puente Aranda</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>258287</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1690</th>\n",
              "      <td>16</td>\n",
              "      <td>La Candelaria</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>24088</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1691</th>\n",
              "      <td>17</td>\n",
              "      <td>Rafael Uribe Uribe</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>374246</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1692</th>\n",
              "      <td>18</td>\n",
              "      <td>Ciudad Bolívar</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>733859</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1693</th>\n",
              "      <td>19</td>\n",
              "      <td>Sumapaz</td>\n",
              "      <td>Bogotá</td>\n",
              "      <td>6531</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      index           Municipio Departamento Habitantes\n",
              "1689     15       Puente Aranda       Bogotá     258287\n",
              "1690     16       La Candelaria       Bogotá      24088\n",
              "1691     17  Rafael Uribe Uribe       Bogotá     374246\n",
              "1692     18      Ciudad Bolívar       Bogotá     733859\n",
              "1693     19             Sumapaz       Bogotá       6531"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sSzZtlr70YHA"
      },
      "source": [
        "Now we have the cleaned data that can be used for further analysis. So, let's save it!\n",
        "_______"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JhZ0NVh1ogXe"
      },
      "source": [
        "<h3>Section 4: Data saving</h3>\n",
        "\n",
        "\n",
        "In Google colab, the data can be save it in google drive. In order to access google dirve, we first need to mount the drive in the colab interface, which can be done by using the method `dirve.mount`. This will prompt a dialog box where you need to authorize the access to **your** google drive. Alternatively, a .csv file can be access in the temporal folder of Colab and downloaded manually."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wHLxTiI1qRRq",
        "outputId": "f615bfb6-eede-46a5-908d-b7ec1d4d54f2"
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('drive')\n",
        "# df.to_csv('data.csv')\n",
        "# !cp data.csv \"drive/My Drive/\""
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w2H3GvaVqv9R"
      },
      "source": [
        "# Actual saving\n",
        "all_data_clean.to_csv('habitantes_municipios_colombia_2021.csv')\n",
        "!cp 'habitantes_municipios_colombia_2021.csv' \"drive/My Drive/\""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}