Big-Data_Apache-Spark-Data-Wrangling.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Big Data - Apache Spark Data Wrangling\n",
        "Caderno de Desenvolvimento <br>\n",
        "Curadoria de [Rafael Barbosa](https://www.linkedin.com/in/barbosa89/)\n",
        "\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ne7Ay0HKaGSv"
      },
      "source": [
        "## 1\\. Apache Spark"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OQxOVktrzG9I"
      },
      "source": [
        "Instalar e configurar um cluster Apache Spark na máquina virtual do Google Colab."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "OTuyeWWXhel7"
      },
      "outputs": [],
      "source": [
        "###  Download do Spark, versão 3.0.0.\n",
        "\n",
        "%%capture\n",
        "!wget -q https://archive.apache.org/dist/spark/spark-3.0.0/spark-3.0.0-bin-hadoop2.7.tgz\n",
        "!tar xf spark-3.0.0-bin-hadoop2.7.tgz && rm spark-3.0.0-bin-hadoop2.7.tgz"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "PKpvjAnYhfbY"
      },
      "outputs": [],
      "source": [
        "### Download e instalação do Java, versão 8.\n",
        "\n",
        "%%capture\n",
        "!apt-get remove openjdk*\n",
        "!apt-get update --fix-missing\n",
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "c4E_FVN1h5Ml",
        "outputId": "4ee70ee6-aeca-41a6-b9c0-c821daa8a875"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m204.7/204.7 MB\u001b[0m \u001b[31m2.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m198.6/198.6 kB\u001b[0m \u001b[31m21.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n"
          ]
        }
      ],
      "source": [
        "### Instala o PySpark na mesmaa versão da aplicação Spark\n",
        "\n",
        "!pip install -q pyspark==3.0.0"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "mr1bG8AfiYXD"
      },
      "outputs": [],
      "source": [
        "### Etapa de configuração: preencher as variáveis de ambiente `JAVA_HOME` e `SPARK_HOME` com o seus respectivos caminhos de instalação.\n",
        "\n",
        "import os\n",
        "\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-3.0.0-bin-hadoop2.7\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "3gz9lWyYi7Gb"
      },
      "outputs": [],
      "source": [
        "#### Conecta PySpark, Python, Spark, Java.\n",
        "\n",
        "!pip install -q findspark==1.4.2"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "id": "xs9_ALGajXmj"
      },
      "outputs": [],
      "source": [
        "### Inicia o ambiente de execução Python\n",
        "\n",
        "import findspark\n",
        "\n",
        "findspark.init()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EvJTE2FpaGeU"
      },
      "source": [
        "## 2\\. Data Wrangling"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ISYXZbomlb1x"
      },
      "source": [
        "A base de dados presente neste [link](https://www.kaggle.com/datasets/bank-of-england/a-millennium-of-macroeconomic-data) contem dados macroeconômicos sobre o Reino Unido desde o século 13."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gw9qU6sLipWv"
      },
      "source": [
        "**2.1\\. Data**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1hnomN0NmgFY"
      },
      "source": [
        "Download dos dados utilizando a máquina virutal do Google Colab."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "id": "D7kgFvnoklGX"
      },
      "outputs": [],
      "source": [
        "!wget -q \"https://raw.githubusercontent.com/cluster-apps-on-docker/spark-standalone-cluster-on-docker/master/build/workspace/data/uk-macroeconomic-data.csv\" -O \"uk-macroeconomic-data.csv\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b-I1dUa0iyE1"
      },
      "source": [
        "**2.2. Wrangling**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RONWLjqgkr5B"
      },
      "source": [
        "Processe os dados para que a base de dados final apresente os valores da taxa de desemprego (`Unemployment rate`) e população (`Population (GB+NI)`) estejam ordenados por ano decrescente:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mr-NTxnalqIm"
      },
      "source": [
        "```csv\n",
        "year,population,unemployment_rate\n",
        "...,...,...\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "snONpcdtlpQu"
      },
      "source": [
        "Para isso, utilize:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nHY3jlCrkuN8"
      },
      "source": [
        " - Pandas"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 68,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 677
        },
        "id": "2BUN93hGkyMZ",
        "outputId": "42698655-006f-47ac-a790-d5a904b701fb"
      },
      "outputs": [
        {
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "summary": "{\n  \"name\": \"data_pd\",\n  \"rows\": 162,\n  \"fields\": [\n    {\n      \"column\": \"year\",\n      \"properties\": {\n        \"dtype\": \"object\",\n        \"num_unique_values\": 162,\n        \"samples\": [\n          \"1858\",\n          \"1907\",\n          \"1885\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"population\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 162,\n        \"samples\": [\n          \"23914\",\n          \"40593\",\n          \"32353\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"unemployment_rate\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 144,\n        \"samples\": [\n          \"6.06\",\n          \"8.10\",\n          \"11.10\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}",
              "type": "dataframe",
              "variable_name": "data_pd"
            },
            "text/html": [
              "\n",
              "  <div id=\"df-f700c9ef-1c41-4391-a1e2-348a21540efc\" class=\"colab-df-container\">\n",
              "    <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>year</th>\n",
              "      <th>population</th>\n",
              "      <th>unemployment_rate</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2016</td>\n",
              "      <td>65573</td>\n",
              "      <td>4.90</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2015</td>\n",
              "      <td>65110</td>\n",
              "      <td>5.38</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2014</td>\n",
              "      <td>64597</td>\n",
              "      <td>6.18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2013</td>\n",
              "      <td>64106</td>\n",
              "      <td>7.61</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2012</td>\n",
              "      <td>63705</td>\n",
              "      <td>7.97</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2011</td>\n",
              "      <td>63285</td>\n",
              "      <td>8.11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2010</td>\n",
              "      <td>62759</td>\n",
              "      <td>7.87</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2009</td>\n",
              "      <td>62260</td>\n",
              "      <td>7.61</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>2008</td>\n",
              "      <td>61824</td>\n",
              "      <td>5.69</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2007</td>\n",
              "      <td>61319</td>\n",
              "      <td>5.33</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>2006</td>\n",
              "      <td>60827</td>\n",
              "      <td>5.42</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>2005</td>\n",
              "      <td>60413</td>\n",
              "      <td>4.83</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>2004</td>\n",
              "      <td>59950</td>\n",
              "      <td>4.75</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>2003</td>\n",
              "      <td>59637</td>\n",
              "      <td>5.01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>2002</td>\n",
              "      <td>59366</td>\n",
              "      <td>5.19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>2001</td>\n",
              "      <td>59113</td>\n",
              "      <td>5.10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>2000</td>\n",
              "      <td>58886</td>\n",
              "      <td>5.46</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>1999</td>\n",
              "      <td>58684</td>\n",
              "      <td>5.98</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>1998</td>\n",
              "      <td>58475</td>\n",
              "      <td>6.26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>1997</td>\n",
              "      <td>58314</td>\n",
              "      <td>6.97</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-f700c9ef-1c41-4391-a1e2-348a21540efc')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-f700c9ef-1c41-4391-a1e2-348a21540efc button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-f700c9ef-1c41-4391-a1e2-348a21540efc');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-4d570e7d-5b73-4d38-9a8d-14abe3e25c2b\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-4d570e7d-5b73-4d38-9a8d-14abe3e25c2b')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-4d570e7d-5b73-4d38-9a8d-14abe3e25c2b button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "text/plain": [
              "    year population unemployment_rate\n",
              "0   2016      65573              4.90\n",
              "1   2015      65110              5.38\n",
              "2   2014      64597              6.18\n",
              "3   2013      64106              7.61\n",
              "4   2012      63705              7.97\n",
              "5   2011      63285              8.11\n",
              "6   2010      62759              7.87\n",
              "7   2009      62260              7.61\n",
              "8   2008      61824              5.69\n",
              "9   2007      61319              5.33\n",
              "10  2006      60827              5.42\n",
              "11  2005      60413              4.83\n",
              "12  2004      59950              4.75\n",
              "13  2003      59637              5.01\n",
              "14  2002      59366              5.19\n",
              "15  2001      59113              5.10\n",
              "16  2000      58886              5.46\n",
              "17  1999      58684              5.98\n",
              "18  1998      58475              6.26\n",
              "19  1997      58314              6.97"
            ]
          },
          "execution_count": 68,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "import pandas as pd\n",
        "\n",
        "# Carrega o CSV\n",
        "data_pd = pd.read_csv(\"uk-macroeconomic-data.csv\")\n",
        "\n",
        "# Seleciona e renomeia as colunas de interesse\n",
        "data_pd = data_pd[[\"Description\", \"Population (GB+NI)\", \"Unemployment rate\"]]\n",
        "data_pd = data_pd.rename(columns={\"Description\": \"year\", \"Population (GB+NI)\": \"population\", \"Unemployment rate\": \"unemployment_rate\"})\n",
        "\n",
        "# Filtra a linha a ser descartada\n",
        "data_pd_description = data_pd[data_pd['year'] == 'Units']\n",
        "\n",
        "# Exclui a linha do DataFrame original\n",
        "data_pd = data_pd[~data_pd['year'].isin(data_pd_description['year'])]\n",
        "\n",
        "# Remove valores nulos e ordena por ano decrescente\n",
        "data_pd = data_pd.dropna().sort_values(by='year', ascending=False)\n",
        "\n",
        "data_pd.reset_index(drop=True, inplace=True)\n",
        "data_pd.head(20)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yswaBr3JkxHq"
      },
      "source": [
        " - PySpark"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 62,
      "metadata": {
        "id": "OUO5AeXwrqKi"
      },
      "outputs": [],
      "source": [
        "### Cria aplicação Spark\n",
        "\n",
        "from pyspark.sql import SparkSession\n",
        "\n",
        "spark = SparkSession.builder.master(\"local[*]\").appName(\"pyspark-notebook\").getOrCreate()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 63,
      "metadata": {
        "id": "_afhSYPQuQmz"
      },
      "outputs": [],
      "source": [
        "### Carrega CSV no dataframe Spark\n",
        "\n",
        "data = spark.read.csv(path=\"uk-macroeconomic-data.csv\", sep=\",\", header=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 64,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GmQoR-hfk1Mv",
        "outputId": "4a73b2d5-19b1-4aa7-8904-bec1422598bd"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-----+----------+-----------------+\n",
            "| year|population|unemployment_rate|\n",
            "+-----+----------+-----------------+\n",
            "|Units|      000s|                %|\n",
            "| 1209|      null|             null|\n",
            "| 1210|      null|             null|\n",
            "| 1211|      null|             null|\n",
            "| 1212|      null|             null|\n",
            "| 1213|      null|             null|\n",
            "| 1214|      null|             null|\n",
            "| 1215|      null|             null|\n",
            "| 1216|      null|             null|\n",
            "| 1217|      null|             null|\n",
            "+-----+----------+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "### Seleciona as colunas de interesse e as renomeia\n",
        "\n",
        "data = data.select([\"Description\", \"Population (GB+NI)\", \"Unemployment rate\"])\n",
        "\n",
        "data = data.\\\n",
        "  withColumnRenamed(\"Description\", 'year').\\\n",
        "  withColumnRenamed(\"Population (GB+NI)\", \"population\").\\\n",
        "  withColumnRenamed(\"Unemployment rate\", \"unemployment_rate\")\n",
        "data.show(n=10)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 65,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QCacLzi-uXwb",
        "outputId": "dfe7dec3-1dd2-4f73-dc77-1953e2087bcb"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-----+----------+-----------------+\n",
            "| year|population|unemployment_rate|\n",
            "+-----+----------+-----------------+\n",
            "|Units|      000s|                %|\n",
            "+-----+----------+-----------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "### Seleciona e carrega linha a ser descartada em um novo dataframe\n",
        "\n",
        "data_description = data.filter(data['year'] == 'Units')\n",
        "data_description.show(n=10)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 66,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Vc6ggyXuuzMj",
        "outputId": "4f48367f-2bd7-4f29-fb30-c1a60891944b"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+----+----------+-----------------+\n",
            "|year|population|unemployment_rate|\n",
            "+----+----------+-----------------+\n",
            "|1209|      null|             null|\n",
            "|1210|      null|             null|\n",
            "|1211|      null|             null|\n",
            "|1212|      null|             null|\n",
            "|1213|      null|             null|\n",
            "|1214|      null|             null|\n",
            "|1215|      null|             null|\n",
            "|1216|      null|             null|\n",
            "|1217|      null|             null|\n",
            "|1218|      null|             null|\n",
            "+----+----------+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "### Utiliza o \"join\" \"left_anti\" para excluir do dataframe orignial a linha guardada no dataframe \"descripition\"\n",
        "### O `broadcast` \"marca\" um `DataFrame` como \"pequeno\" e força o Spark a trafega-lo pela rede\n",
        "\n",
        "from pyspark.sql.functions import broadcast\n",
        "\n",
        "data = data.join(other=broadcast(data_description), on=['year'], how='left_anti')\n",
        "data.show(n=10)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 67,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0FQQrqEEvhGW",
        "outputId": "b2535623-23c6-4562-888c-56509b16acc0"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+----+----------+-----------------+\n",
            "|year|population|unemployment_rate|\n",
            "+----+----------+-----------------+\n",
            "|2016|     65573|             4.90|\n",
            "|2015|     65110|             5.38|\n",
            "|2014|     64597|             6.18|\n",
            "|2013|     64106|             7.61|\n",
            "|2012|     63705|             7.97|\n",
            "|2011|     63285|             8.11|\n",
            "|2010|     62759|             7.87|\n",
            "|2009|     62260|             7.61|\n",
            "|2008|     61824|             5.69|\n",
            "|2007|     61319|             5.33|\n",
            "|2006|     60827|             5.42|\n",
            "|2005|     60413|             4.83|\n",
            "|2004|     59950|             4.75|\n",
            "|2003|     59637|             5.01|\n",
            "|2002|     59366|             5.19|\n",
            "|2001|     59113|             5.10|\n",
            "|2000|     58886|             5.46|\n",
            "|1999|     58684|             5.98|\n",
            "|1998|     58475|             6.26|\n",
            "|1997|     58314|             6.97|\n",
            "+----+----------+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "### Remove linhas com valor nulo e mostra o resultados final\n",
        "\n",
        "data = data.dropna()\n",
        "data.orderBy(\"year\", ascending=False).show()"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [
        "JG1HL69-j0lH",
        "s_Dj-BgThLg5",
        "jeEzLmuRhDQy",
        "PoTeRwlihDQ0",
        "QpMlxqRyhDQ1",
        "ov7Fg-mWhDQ1",
        "DvHPNAoPgdiG",
        "3SMp5GwjglJm",
        "e3ieQfXTgqd3",
        "L0pKWNr5fm9z",
        "DMhCpOU_gupo",
        "l3h0WP2Eg2ht",
        "rWG4Vk6XaWtf",
        "wkibgd8rB8y3",
        "IjPaJtnYE0MI",
        "2lpj1rszc--m",
        "HnWg_mhLd5gC",
        "RhqmXiHQo-is",
        "SvT9xrajLL7J",
        "91eSyA2cLLqv"
      ],
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}