1 - Extract, Transform and Load.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Alura Challenge Data Science - Week 1 - Transforming Data with PySpark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install pyspark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importando Bibliotecas\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql import functions as F\n",
    "from pyspark.sql.types import IntegerType, DoubleType, FloatType, StringType"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Iniciando sessão spark**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder \\\n",
    "    .master('local[*]') \\\n",
    "    .appName(\"Challenge Data Science 2\") \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://10.0.1.45:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.3.1</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>Challenge Data Science 2</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7fb34d54c220>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **ETL PROCESS (EXTRACT, TRANSFORM, LOAD)**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Extract**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import zipfile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extraindo o arquivo zipado\n",
    "zipfile.ZipFile('data/semana-1.zip', 'r').extractall('data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- anuncio: struct (nullable = true)\n",
      " |    |-- andar: long (nullable = true)\n",
      " |    |-- area_total: array (nullable = true)\n",
      " |    |    |-- element: string (containsNull = true)\n",
      " |    |-- area_util: array (nullable = true)\n",
      " |    |    |-- element: string (containsNull = true)\n",
      " |    |-- banheiros: array (nullable = true)\n",
      " |    |    |-- element: long (containsNull = true)\n",
      " |    |-- caracteristicas: array (nullable = true)\n",
      " |    |    |-- element: string (containsNull = true)\n",
      " |    |-- endereco: struct (nullable = true)\n",
      " |    |    |-- bairro: string (nullable = true)\n",
      " |    |    |-- cep: string (nullable = true)\n",
      " |    |    |-- cidade: string (nullable = true)\n",
      " |    |    |-- estado: string (nullable = true)\n",
      " |    |    |-- latitude: double (nullable = true)\n",
      " |    |    |-- longitude: double (nullable = true)\n",
      " |    |    |-- pais: string (nullable = true)\n",
      " |    |    |-- rua: string (nullable = true)\n",
      " |    |    |-- zona: string (nullable = true)\n",
      " |    |-- id: string (nullable = true)\n",
      " |    |-- quartos: array (nullable = true)\n",
      " |    |    |-- element: long (containsNull = true)\n",
      " |    |-- suites: array (nullable = true)\n",
      " |    |    |-- element: long (containsNull = true)\n",
      " |    |-- tipo_anuncio: string (nullable = true)\n",
      " |    |-- tipo_unidade: string (nullable = true)\n",
      " |    |-- tipo_uso: string (nullable = true)\n",
      " |    |-- vaga: array (nullable = true)\n",
      " |    |    |-- element: long (containsNull = true)\n",
      " |    |-- valores: array (nullable = true)\n",
      " |    |    |-- element: struct (containsNull = true)\n",
      " |    |    |    |-- condominio: string (nullable = true)\n",
      " |    |    |    |-- iptu: string (nullable = true)\n",
      " |    |    |    |-- tipo: string (nullable = true)\n",
      " |    |    |    |-- valor: string (nullable = true)\n",
      " |-- imagens: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- id: string (nullable = true)\n",
      " |    |    |-- url: string (nullable = true)\n",
      " |-- usuario: struct (nullable = true)\n",
      " |    |-- id: string (nullable = true)\n",
      " |    |-- nome: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Lendo o arquivo zipado e Criando o DataFrame Spark\n",
    "path = 'data/dataset_bruto.json'\n",
    "imoveis_df = spark.read.option(\"multiLine\",\"true\").json(path)\n",
    "imoveis_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "O DataFrame possui 89083 colunas\n"
     ]
    }
   ],
   "source": [
    "print('O DataFrame possui {} colunas'.format(imoveis_df.count()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Transform**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### *Dealing with array and dictionary columns*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For our analysis only the information inside the column `\"anuncio\"` are relevant, therefore we will just select it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+----------+---------+---------+--------------------+--------------------+--------------------+-------+------+------------+------------+-----------+----+--------------------+\n",
      "|andar|area_total|area_util|banheiros|     caracteristicas|            endereco|                  id|quartos|suites|tipo_anuncio|tipo_unidade|   tipo_uso|vaga|             valores|\n",
      "+-----+----------+---------+---------+--------------------+--------------------+--------------------+-------+------+------------+------------+-----------+----+--------------------+\n",
      "|    0|        []|     [16]|      [0]|                  []|{Centro, 20061003...|47d553e0-79f2-4a4...|    [0]|   [0]|       Usado|      Outros|  Comercial| [1]|[{260, 107, Venda...|\n",
      "|    0|        []|     [14]|      [0]|                  []|{Centro, 20051040...|b6ffbae1-17f6-487...|    [0]|    []|       Usado|      Outros|  Comercial| [0]|[{260, 107, Venda...|\n",
      "|    0|    [1026]|   [1026]|      [0]|                  []|{Maria da Graça, ...|1fb030a5-9e3e-4a1...|    [0]|    []|       Usado|      Outros|  Comercial| [0]|[{null, 1613, Ven...|\n",
      "|    0|     [120]|    [120]|      [0]|[Portão eletrônic...|{Campo Grande, 23...|ac6cb348-69d6-45a...|    [0]|    []|       Usado|      Outros|Residencial|  []|[{80, null, Venda...|\n",
      "|    0|       [3]|      [3]|      [0]|                  []|{São Cristóvão, 2...|e032b908-ef42-4d4...|    [0]|    []|       Usado|      Outros|Residencial|  []|[{0, 0, Venda, 50...|\n",
      "|    0|      [20]|     [15]|      [1]|                  []|{Centro, 20231047...|df9cf0b8-b972-433...|    [0]|    []|       Usado|      Outros|  Comercial| [1]|[{223, 87, Venda,...|\n",
      "|    3|      [43]|     [43]|      [1]|[Academia, Churra...|{Paciência, 23585...|d2e3a3aa-09b5-45a...|    [2]|    []|       Usado| Apartamento|Residencial| [1]|[{245, null, Vend...|\n",
      "|    2|      [42]|     [42]|      [1]|[Churrasqueira, P...|{Paciência, 23585...|085bab2c-87ad-452...|    [2]|    []|       Usado| Apartamento|Residencial| [1]|[{0, 0, Venda, 15...|\n",
      "|    0|        []|     [12]|      [0]|[Portaria 24h, El...|{Centro, 20231047...|096ad966-dc36-47b...|    [0]|    []|       Usado|      Outros|  Comercial| [1]|[{250, null, Vend...|\n",
      "|    1|      [41]|     [41]|      [1]|[Portaria 24h, Co...|{Guaratiba, 23036...|18d22cbe-1b86-476...|    [2]|    []|       Usado| Apartamento|Residencial| [1]|[{0, 0, Venda, 20...|\n",
      "|    3|      [43]|     [43]|      [1]|[Churrasqueira, P...|{Cosmos, 23066271...|bed8a354-9317-442...|    [2]|    []|       Usado| Apartamento|Residencial| [0]|[{285, null, Vend...|\n",
      "|    0|      [10]|      [4]|      [0]|[Elevador, Portar...|{Centro, 20080021...|b7d0fbd3-0b28-420...|    [0]|    []|       Usado|      Outros|  Comercial| [0]|[{200, 86, Venda,...|\n",
      "|    0|      [12]|     [12]|      [1]|      [Portaria 24h]|{Copacabana, 2202...|c6571c11-166e-4a4...|    [0]|    []|       Usado|      Outros|  Comercial|  []|[{470, null, Vend...|\n",
      "|    0|      [17]|     [17]|      [0]|          [Elevador]|{Centro, 20070021...|46d5b560-205f-45f...|    [0]|    []|       Usado|      Outros|  Comercial| [1]|[{294, 98, Venda,...|\n",
      "|    1|      [32]|     [32]|       []|[Elevador, Condom...|{Centro, 20090020...|db197997-00e6-4d6...|     []|    []|       Usado|      Outros|  Comercial| [1]|[{684, null, Vend...|\n",
      "|    0|        []|     [10]|      [0]|[Elevador, Condom...|{Saúde, 20080040,...|835d4338-4aa9-4e4...|    [0]|    []|       Usado|      Outros|  Comercial|  []|[{0, 120, Venda, ...|\n",
      "|    2|      [43]|     [43]|      [1]|[Academia, Churra...|{Guaratiba, 23036...|12a13315-d67f-48f...|    [2]|    []|       Usado| Apartamento|Residencial| [1]|[{245, null, Vend...|\n",
      "|    3|      [43]|     [43]|      [1]|[Churrasqueira, P...|{Cosmos, 23066271...|a2e6d7a5-0ff0-484...|    [2]|   [0]|       Usado| Apartamento|Residencial| [0]|[{285, null, Vend...|\n",
      "|    3|      [43]|     [43]|      [1]|[Churrasqueira, P...|{Paciência, 23585...|a6e3173b-c950-4db...|    [2]|    []|       Usado| Apartamento|Residencial| [1]|[{250, null, Vend...|\n",
      "|    3|      [43]|     [43]|      [1]|[Churrasqueira, P...|{Guaratiba, 23036...|2e6e5dfb-206c-496...|    [2]|   [0]|       Usado| Apartamento|Residencial| [1]|[{245, null, Vend...|\n",
      "+-----+----------+---------+---------+--------------------+--------------------+--------------------+-------+------+------------+------------+-----------+----+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "anuncio_df = imoveis_df.select(\"anuncio.*\")\n",
    "anuncio_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- andar: long (nullable = true)\n",
      " |-- area_total: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- area_util: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- banheiros: array (nullable = true)\n",
      " |    |-- element: long (containsNull = true)\n",
      " |-- caracteristicas: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- endereco: struct (nullable = true)\n",
      " |    |-- bairro: string (nullable = true)\n",
      " |    |-- cep: string (nullable = true)\n",
      " |    |-- cidade: string (nullable = true)\n",
      " |    |-- estado: string (nullable = true)\n",
      " |    |-- latitude: double (nullable = true)\n",
      " |    |-- longitude: double (nullable = true)\n",
      " |    |-- pais: string (nullable = true)\n",
      " |    |-- rua: string (nullable = true)\n",
      " |    |-- zona: string (nullable = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- quartos: array (nullable = true)\n",
      " |    |-- element: long (containsNull = true)\n",
      " |-- suites: array (nullable = true)\n",
      " |    |-- element: long (containsNull = true)\n",
      " |-- tipo_anuncio: string (nullable = true)\n",
      " |-- tipo_unidade: string (nullable = true)\n",
      " |-- tipo_uso: string (nullable = true)\n",
      " |-- vaga: array (nullable = true)\n",
      " |    |-- element: long (containsNull = true)\n",
      " |-- valores: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- condominio: string (nullable = true)\n",
      " |    |    |-- iptu: string (nullable = true)\n",
      " |    |    |-- tipo: string (nullable = true)\n",
      " |    |    |-- valor: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "anuncio_df.printSchema()   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Transforming **`array`** columns into **`string`** columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+----------+---------+---------+--------------------+--------------------+--------------------+-------+------+------------+------------+-----------+----+--------------------+\n",
      "|andar|area_total|area_util|banheiros|     caracteristicas|            endereco|                  id|quartos|suites|tipo_anuncio|tipo_unidade|   tipo_uso|vaga|             valores|\n",
      "+-----+----------+---------+---------+--------------------+--------------------+--------------------+-------+------+------------+------------+-----------+----+--------------------+\n",
      "|    0|      null|       16|        0|                  []|{Centro, 20061003...|47d553e0-79f2-4a4...|      0|     0|       Usado|      Outros|  Comercial|   1|[{260, 107, Venda...|\n",
      "|    0|      null|       14|        0|                  []|{Centro, 20051040...|b6ffbae1-17f6-487...|      0|  null|       Usado|      Outros|  Comercial|   0|[{260, 107, Venda...|\n",
      "|    0|      1026|     1026|        0|                  []|{Maria da Graça, ...|1fb030a5-9e3e-4a1...|      0|  null|       Usado|      Outros|  Comercial|   0|[{null, 1613, Ven...|\n",
      "|    0|       120|      120|        0|[Portão eletrônic...|{Campo Grande, 23...|ac6cb348-69d6-45a...|      0|  null|       Usado|      Outros|Residencial|null|[{80, null, Venda...|\n",
      "|    0|         3|        3|        0|                  []|{São Cristóvão, 2...|e032b908-ef42-4d4...|      0|  null|       Usado|      Outros|Residencial|null|[{0, 0, Venda, 50...|\n",
      "|    0|        20|       15|        1|                  []|{Centro, 20231047...|df9cf0b8-b972-433...|      0|  null|       Usado|      Outros|  Comercial|   1|[{223, 87, Venda,...|\n",
      "|    3|        43|       43|        1|[Academia, Churra...|{Paciência, 23585...|d2e3a3aa-09b5-45a...|      2|  null|       Usado| Apartamento|Residencial|   1|[{245, null, Vend...|\n",
      "|    2|        42|       42|        1|[Churrasqueira, P...|{Paciência, 23585...|085bab2c-87ad-452...|      2|  null|       Usado| Apartamento|Residencial|   1|[{0, 0, Venda, 15...|\n",
      "|    0|      null|       12|        0|[Portaria 24h, El...|{Centro, 20231047...|096ad966-dc36-47b...|      0|  null|       Usado|      Outros|  Comercial|   1|[{250, null, Vend...|\n",
      "|    1|        41|       41|        1|[Portaria 24h, Co...|{Guaratiba, 23036...|18d22cbe-1b86-476...|      2|  null|       Usado| Apartamento|Residencial|   1|[{0, 0, Venda, 20...|\n",
      "|    3|        43|       43|        1|[Churrasqueira, P...|{Cosmos, 23066271...|bed8a354-9317-442...|      2|  null|       Usado| Apartamento|Residencial|   0|[{285, null, Vend...|\n",
      "|    0|        10|        4|        0|[Elevador, Portar...|{Centro, 20080021...|b7d0fbd3-0b28-420...|      0|  null|       Usado|      Outros|  Comercial|   0|[{200, 86, Venda,...|\n",
      "|    0|        12|       12|        1|      [Portaria 24h]|{Copacabana, 2202...|c6571c11-166e-4a4...|      0|  null|       Usado|      Outros|  Comercial|null|[{470, null, Vend...|\n",
      "|    0|        17|       17|        0|          [Elevador]|{Centro, 20070021...|46d5b560-205f-45f...|      0|  null|       Usado|      Outros|  Comercial|   1|[{294, 98, Venda,...|\n",
      "|    1|        32|       32|     null|[Elevador, Condom...|{Centro, 20090020...|db197997-00e6-4d6...|   null|  null|       Usado|      Outros|  Comercial|   1|[{684, null, Vend...|\n",
      "|    0|      null|       10|        0|[Elevador, Condom...|{Saúde, 20080040,...|835d4338-4aa9-4e4...|      0|  null|       Usado|      Outros|  Comercial|null|[{0, 120, Venda, ...|\n",
      "|    2|        43|       43|        1|[Academia, Churra...|{Guaratiba, 23036...|12a13315-d67f-48f...|      2|  null|       Usado| Apartamento|Residencial|   1|[{245, null, Vend...|\n",
      "|    3|        43|       43|        1|[Churrasqueira, P...|{Cosmos, 23066271...|a2e6d7a5-0ff0-484...|      2|     0|       Usado| Apartamento|Residencial|   0|[{285, null, Vend...|\n",
      "|    3|        43|       43|        1|[Churrasqueira, P...|{Paciência, 23585...|a6e3173b-c950-4db...|      2|  null|       Usado| Apartamento|Residencial|   1|[{250, null, Vend...|\n",
      "|    3|        43|       43|        1|[Churrasqueira, P...|{Guaratiba, 23036...|2e6e5dfb-206c-496...|      2|     0|       Usado| Apartamento|Residencial|   1|[{245, null, Vend...|\n",
      "+-----+----------+---------+---------+--------------------+--------------------+--------------------+-------+------+------------+------------+-----------+----+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "anuncio_df = anuncio_df\\\n",
    "            .withColumn(\"quartos\",anuncio_df.quartos[0])\\\n",
    "            .withColumn(\"suites\",anuncio_df.suites[0])\\\n",
    "            .withColumn(\"banheiros\",anuncio_df.banheiros[0])\\\n",
    "            .withColumn(\"area_total\",anuncio_df.area_total[0])\\\n",
    "            .withColumn(\"area_util\",anuncio_df.area_util[0])\\\n",
    "            .withColumn(\"vaga\",anuncio_df.vaga[0])\n",
    "\n",
    "anuncio_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extracting the **bairro and zona** from the **`endereço`** column. And extracting the **condominio, iptu, tipo e valor** from the **`valores`** column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>andar</th>\n",
       "      <th>area_total</th>\n",
       "      <th>area_util</th>\n",
       "      <th>banheiros</th>\n",
       "      <th>caracteristicas</th>\n",
       "      <th>id</th>\n",
       "      <th>quartos</th>\n",
       "      <th>suites</th>\n",
       "      <th>tipo_anuncio</th>\n",
       "      <th>tipo_unidade</th>\n",
       "      <th>tipo_uso</th>\n",
       "      <th>vaga</th>\n",
       "      <th>bairro</th>\n",
       "      <th>zona</th>\n",
       "      <th>condominio</th>\n",
       "      <th>iptu</th>\n",
       "      <th>tipo</th>\n",
       "      <th>valor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>47d553e0-79f2-4a46-9390-5a3c962740c2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Usado</td>\n",
       "      <td>Outros</td>\n",
       "      <td>Comercial</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Centro</td>\n",
       "      <td>Zona Central</td>\n",
       "      <td>260</td>\n",
       "      <td>107</td>\n",
       "      <td>Venda</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>b6ffbae1-17f6-4870-9950-e998ac1c8d6a</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Usado</td>\n",
       "      <td>Outros</td>\n",
       "      <td>Comercial</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Centro</td>\n",
       "      <td>Zona Central</td>\n",
       "      <td>260</td>\n",
       "      <td>107</td>\n",
       "      <td>Venda</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>1fb030a5-9e3e-4a1d-93cd-cd2d8a215e11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Usado</td>\n",
       "      <td>Outros</td>\n",
       "      <td>Comercial</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Maria da Graça</td>\n",
       "      <td>Zona Norte</td>\n",
       "      <td>None</td>\n",
       "      <td>1613</td>\n",
       "      <td>Venda</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>120</td>\n",
       "      <td>120</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[Portão eletrônico, Condomínio fechado]</td>\n",
       "      <td>ac6cb348-69d6-45af-9589-dc34099370d8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Usado</td>\n",
       "      <td>Outros</td>\n",
       "      <td>Residencial</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Campo Grande</td>\n",
       "      <td>Zona Oeste</td>\n",
       "      <td>80</td>\n",
       "      <td>None</td>\n",
       "      <td>Venda</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>e032b908-ef42-4d4a-8125-eba4792bacbe</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Usado</td>\n",
       "      <td>Outros</td>\n",
       "      <td>Residencial</td>\n",
       "      <td>NaN</td>\n",
       "      <td>São Cristóvão</td>\n",
       "      <td>Zona Norte</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Venda</td>\n",
       "      <td>5000</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89078</th>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>219</td>\n",
       "      <td>4.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>10f8eb1a-89f8-4ba8-aae0-078a3661353f</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Lançamento</td>\n",
       "      <td>Outros</td>\n",
       "      <td>Residencial</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Barra da Tijuca</td>\n",
       "      <td>Zona Oeste</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Venda</td>\n",
       "      <td>3900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89079</th>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>200</td>\n",
       "      <td>3.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>dca4c35b-ae00-486e-aeea-60b03957dfae</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Lançamento</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>Residencial</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Leblon</td>\n",
       "      <td>Zona Sul</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Venda</td>\n",
       "      <td>4220000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89080</th>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>130</td>\n",
       "      <td>3.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>c2cee5ec-1d04-4792-a9d0-7bf50824223d</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Lançamento</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>Residencial</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Lagoa</td>\n",
       "      <td>Zona Sul</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Venda</td>\n",
       "      <td>4311297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89081</th>\n",
       "      <td>0</td>\n",
       "      <td>268</td>\n",
       "      <td>268</td>\n",
       "      <td>5.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>7f700a8e-b171-46ea-993b-461162f7afba</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Lançamento</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>Residencial</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Engenho de Dentro</td>\n",
       "      <td>Zona Norte</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Venda</td>\n",
       "      <td>4520008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89082</th>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>214</td>\n",
       "      <td>5.0</td>\n",
       "      <td>[]</td>\n",
       "      <td>043041dc-025e-4a11-8f1d-8bba3c991324</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Lançamento</td>\n",
       "      <td>Apartamento</td>\n",
       "      <td>Residencial</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Lagoa</td>\n",
       "      <td>Zona Sul</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Venda</td>\n",
       "      <td>4636000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>89083 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       andar area_total area_util  banheiros  \\\n",
       "0          0       None        16        0.0   \n",
       "1          0       None        14        0.0   \n",
       "2          0       1026      1026        0.0   \n",
       "3          0        120       120        0.0   \n",
       "4          0          3         3        0.0   \n",
       "...      ...        ...       ...        ...   \n",
       "89078      0       None       219        4.0   \n",
       "89079      0       None       200        3.0   \n",
       "89080      0       None       130        3.0   \n",
       "89081      0        268       268        5.0   \n",
       "89082      0       None       214        5.0   \n",
       "\n",
       "                               caracteristicas  \\\n",
       "0                                           []   \n",
       "1                                           []   \n",
       "2                                           []   \n",
       "3      [Portão eletrônico, Condomínio fechado]   \n",
       "4                                           []   \n",
       "...                                        ...   \n",
       "89078                                       []   \n",
       "89079                                       []   \n",
       "89080                                       []   \n",
       "89081                                       []   \n",
       "89082                                       []   \n",
       "\n",
       "                                         id  quartos  suites tipo_anuncio  \\\n",
       "0      47d553e0-79f2-4a46-9390-5a3c962740c2      0.0     0.0        Usado   \n",
       "1      b6ffbae1-17f6-4870-9950-e998ac1c8d6a      0.0     NaN        Usado   \n",
       "2      1fb030a5-9e3e-4a1d-93cd-cd2d8a215e11      0.0     NaN        Usado   \n",
       "3      ac6cb348-69d6-45af-9589-dc34099370d8      0.0     NaN        Usado   \n",
       "4      e032b908-ef42-4d4a-8125-eba4792bacbe      0.0     NaN        Usado   \n",
       "...                                     ...      ...     ...          ...   \n",
       "89078  10f8eb1a-89f8-4ba8-aae0-078a3661353f      4.0     4.0   Lançamento   \n",
       "89079  dca4c35b-ae00-486e-aeea-60b03957dfae      3.0     3.0   Lançamento   \n",
       "89080  c2cee5ec-1d04-4792-a9d0-7bf50824223d      2.0     2.0   Lançamento   \n",
       "89081  7f700a8e-b171-46ea-993b-461162f7afba      4.0     4.0   Lançamento   \n",
       "89082  043041dc-025e-4a11-8f1d-8bba3c991324      4.0     4.0   Lançamento   \n",
       "\n",
       "      tipo_unidade     tipo_uso  vaga             bairro          zona  \\\n",
       "0           Outros    Comercial   1.0             Centro  Zona Central   \n",
       "1           Outros    Comercial   0.0             Centro  Zona Central   \n",
       "2           Outros    Comercial   0.0     Maria da Graça    Zona Norte   \n",
       "3           Outros  Residencial   NaN       Campo Grande    Zona Oeste   \n",
       "4           Outros  Residencial   NaN      São Cristóvão    Zona Norte   \n",
       "...            ...          ...   ...                ...           ...   \n",
       "89078       Outros  Residencial   1.0    Barra da Tijuca    Zona Oeste   \n",
       "89079  Apartamento  Residencial   1.0             Leblon      Zona Sul   \n",
       "89080  Apartamento  Residencial   1.0              Lagoa      Zona Sul   \n",
       "89081  Apartamento  Residencial   4.0  Engenho de Dentro    Zona Norte   \n",
       "89082  Apartamento  Residencial   2.0              Lagoa      Zona Sul   \n",
       "\n",
       "      condominio  iptu   tipo    valor  \n",
       "0            260   107  Venda    10000  \n",
       "1            260   107  Venda    10000  \n",
       "2           None  1613  Venda    10000  \n",
       "3             80  None  Venda    10000  \n",
       "4              0     0  Venda     5000  \n",
       "...          ...   ...    ...      ...  \n",
       "89078          0     0  Venda  3900000  \n",
       "89079          0     0  Venda  4220000  \n",
       "89080          0     0  Venda  4311297  \n",
       "89081          0     0  Venda  4520008  \n",
       "89082          0     0  Venda  4636000  \n",
       "\n",
       "[89083 rows x 18 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df = anuncio_df\\\n",
    "                    .withColumn('bairro', anuncio_df['endereco']['bairro'])\\\n",
    "                    .withColumn('zona', anuncio_df['endereco']['zona'])\\\n",
    "                    .withColumn('condominio',anuncio_df['valores']['condominio'][0])\\\n",
    "                    .withColumn('iptu',anuncio_df['valores']['iptu'][0])\\\n",
    "                    .withColumn('tipo',anuncio_df['valores']['tipo'][0])\\\n",
    "                    .withColumn('valor',anuncio_df['valores']['valor'][0])\\\n",
    "                    .drop('endereco', 'valores')\n",
    "\n",
    "final_df.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- andar: long (nullable = true)\n",
      " |-- area_total: string (nullable = true)\n",
      " |-- area_util: string (nullable = true)\n",
      " |-- banheiros: long (nullable = true)\n",
      " |-- caracteristicas: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- quartos: long (nullable = true)\n",
      " |-- suites: long (nullable = true)\n",
      " |-- tipo_anuncio: string (nullable = true)\n",
      " |-- tipo_unidade: string (nullable = true)\n",
      " |-- tipo_uso: string (nullable = true)\n",
      " |-- vaga: long (nullable = true)\n",
      " |-- bairro: string (nullable = true)\n",
      " |-- zona: string (nullable = true)\n",
      " |-- condominio: string (nullable = true)\n",
      " |-- iptu: string (nullable = true)\n",
      " |-- tipo: string (nullable = true)\n",
      " |-- valor: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "final_df.printSchema()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### *Changing columns type*\n",
    "\n",
    "Some column types are not consistent. Therefore, we need to tweak them for later use.\n",
    "\n",
    "string --> float:\n",
    "\n",
    "- area_total\n",
    "- area_util\n",
    "- condominio\n",
    "- iptu\n",
    "- valor\n",
    "\n",
    "array --> string:\n",
    "\n",
    "- caracteristicas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- andar: long (nullable = true)\n",
      " |-- area_total: float (nullable = true)\n",
      " |-- area_util: float (nullable = true)\n",
      " |-- banheiros: long (nullable = true)\n",
      " |-- caracteristicas: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- quartos: long (nullable = true)\n",
      " |-- suites: long (nullable = true)\n",
      " |-- tipo_anuncio: string (nullable = true)\n",
      " |-- tipo_unidade: string (nullable = true)\n",
      " |-- tipo_uso: string (nullable = true)\n",
      " |-- vaga: long (nullable = true)\n",
      " |-- bairro: string (nullable = true)\n",
      " |-- zona: string (nullable = true)\n",
      " |-- condominio: float (nullable = true)\n",
      " |-- iptu: float (nullable = true)\n",
      " |-- tipo: string (nullable = true)\n",
      " |-- valor: float (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "final_df = final_df\\\n",
    "            .withColumn('area_total', final_df['area_total'].cast(FloatType()))\\\n",
    "            .withColumn('area_util', final_df['area_util'].cast(FloatType()))\\\n",
    "            .withColumn('condominio', final_df['condominio'].cast(FloatType()))\\\n",
    "            .withColumn('iptu', final_df['iptu'].cast(FloatType()))\\\n",
    "            .withColumn('valor', final_df['valor'].cast(FloatType()))\n",
    "\n",
    "final_df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### *Filtering the DF*\n",
    "\n",
    "We are going to filter the DF by:\n",
    "\n",
    "- tipo_uso = **`Residencial`**;\n",
    "- tipo_unidade = **`Apartamento`**;\n",
    "- tipo_anuncio = **`Usado`**.\n",
    "- tipo = **`Venda`**\n",
    "\n",
    "But first lets understand the impact of these filter by creating a frequency table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-----+\n",
      "|   tipo_uso|count|\n",
      "+-----------+-----+\n",
      "|Residencial|84541|\n",
      "|  Comercial| 4542|\n",
      "+-----------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "final_df\\\n",
    "    .select('tipo_uso')\\\n",
    "    .groupBy('tipo_uso')\\\n",
    "    .count()\\\n",
    "    .orderBy('tipo_uso', 'count', ascending=False)\\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------------+-----+\n",
      "|   tipo_uso|tipo_unidade|count|\n",
      "+-----------+------------+-----+\n",
      "|Residencial| Apartamento|66797|\n",
      "|Residencial|        Casa|10227|\n",
      "|Residencial|      Outros| 7517|\n",
      "|  Comercial| Apartamento|    4|\n",
      "|  Comercial|        Casa|   92|\n",
      "|  Comercial|      Outros| 4446|\n",
      "+-----------+------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "final_df\\\n",
    "    .select('tipo_uso', 'tipo_unidade')\\\n",
    "    .groupBy('tipo_uso', 'tipo_unidade')\\\n",
    "    .count()\\\n",
    "    .orderBy('tipo_uso', 'tipo_unidade', 'count', ascending=[False, True, False])\\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------------+------------+-----+\n",
      "|   tipo_uso|tipo_unidade|tipo_anuncio|count|\n",
      "+-----------+------------+------------+-----+\n",
      "|Residencial| Apartamento|       Usado|66562|\n",
      "|Residencial| Apartamento|  Lançamento|  235|\n",
      "|Residencial|        Casa|       Usado|10224|\n",
      "|Residencial|        Casa|  Lançamento|    3|\n",
      "|Residencial|      Outros|       Usado| 7502|\n",
      "|Residencial|      Outros|  Lançamento|   15|\n",
      "|  Comercial| Apartamento|       Usado|    4|\n",
      "|  Comercial|        Casa|       Usado|   92|\n",
      "|  Comercial|      Outros|       Usado| 4443|\n",
      "|  Comercial|      Outros|  Lançamento|    3|\n",
      "+-----------+------------+------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "final_df\\\n",
    "    .select('tipo_uso', 'tipo_unidade', 'tipo_anuncio')\\\n",
    "    .groupBy('tipo_uso', 'tipo_unidade', 'tipo_anuncio')\\\n",
    "    .count()\\\n",
    "    .orderBy('tipo_uso', 'tipo_unidade', 'tipo_anuncio', 'count', ascending=[False, True, False, False])\\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------------+------------+-------+-----+\n",
      "|   tipo_uso|tipo_unidade|tipo_anuncio|   tipo|count|\n",
      "+-----------+------------+------------+-------+-----+\n",
      "|Residencial| Apartamento|       Usado|  Venda|66348|\n",
      "|Residencial| Apartamento|       Usado|Aluguel|  214|\n",
      "|Residencial| Apartamento|  Lançamento|  Venda|  235|\n",
      "|Residencial|        Casa|       Usado|  Venda|10161|\n",
      "|Residencial|        Casa|       Usado|Aluguel|   63|\n",
      "|Residencial|        Casa|  Lançamento|  Venda|    3|\n",
      "|Residencial|      Outros|       Usado|  Venda| 7402|\n",
      "|Residencial|      Outros|       Usado|Aluguel|  100|\n",
      "|Residencial|      Outros|  Lançamento|  Venda|   15|\n",
      "|  Comercial| Apartamento|       Usado|  Venda|    4|\n",
      "|  Comercial|        Casa|       Usado|  Venda|   87|\n",
      "|  Comercial|        Casa|       Usado|Aluguel|    5|\n",
      "|  Comercial|      Outros|       Usado|  Venda| 4014|\n",
      "|  Comercial|      Outros|       Usado|Aluguel|  429|\n",
      "|  Comercial|      Outros|  Lançamento|  Venda|    3|\n",
      "+-----------+------------+------------+-------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "final_df\\\n",
    "    .select('tipo_uso', 'tipo_unidade', 'tipo_anuncio', 'tipo')\\\n",
    "    .groupBy('tipo_uso', 'tipo_unidade', 'tipo_anuncio', 'tipo')\\\n",
    "    .count()\\\n",
    "    .orderBy('tipo_uso', 'tipo_unidade', 'tipo_anuncio', 'tipo', 'count', ascending=[False, True, False, False ,False])\\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_final_df = final_df\\\n",
    "                        .filter((final_df['tipo_uso'] == 'Residencial') &\n",
    "                                (final_df['tipo_unidade'] == 'Apartamento') & \n",
    "                                (final_df['tipo_anuncio'] == 'Usado') &\n",
    "                                (final_df['tipo'] == 'Venda'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------------+------------+-----+\n",
      "|   tipo_uso|tipo_unidade|tipo_anuncio|count|\n",
      "+-----------+------------+------------+-----+\n",
      "|Residencial| Apartamento|       Usado|66348|\n",
      "+-----------+------------+------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "filtered_final_df\\\n",
    "    .select('tipo_uso', 'tipo_unidade', 'tipo_anuncio', 'tipo')\\\n",
    "    .groupBy('tipo_uso', 'tipo_unidade', 'tipo_anuncio', 'tipo')\\\n",
    "    .count()\\\n",
    "    .orderBy('tipo_uso', 'tipo_unidade', 'tipo_anuncio', 'count', ascending=[False, True, False, False])\\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+----------+---------+---------+--------------------+--------------------+-------+------+------------+------------+-----------+----+--------------------+----------+----------+----+-----+-------+\n",
      "|andar|area_total|area_util|banheiros|     caracteristicas|                  id|quartos|suites|tipo_anuncio|tipo_unidade|   tipo_uso|vaga|              bairro|      zona|condominio|iptu| tipo|  valor|\n",
      "+-----+----------+---------+---------+--------------------+--------------------+-------+------+------------+------------+-----------+----+--------------------+----------+----------+----+-----+-------+\n",
      "|    3|      43.0|     43.0|        1|[Academia, Churra...|d2e3a3aa-09b5-45a...|      2|  null|       Usado| Apartamento|Residencial|   1|           Paciência|Zona Oeste|     245.0|null|Venda|15000.0|\n",
      "|    2|      42.0|     42.0|        1|[Churrasqueira, P...|085bab2c-87ad-452...|      2|  null|       Usado| Apartamento|Residencial|   1|           Paciência|Zona Oeste|       0.0| 0.0|Venda|15000.0|\n",
      "|    1|      41.0|     41.0|        1|[Portaria 24h, Co...|18d22cbe-1b86-476...|      2|  null|       Usado| Apartamento|Residencial|   1|           Guaratiba|Zona Oeste|       0.0| 0.0|Venda|20000.0|\n",
      "|    3|      43.0|     43.0|        1|[Churrasqueira, P...|bed8a354-9317-442...|      2|  null|       Usado| Apartamento|Residencial|   0|              Cosmos|Zona Oeste|     285.0|null|Venda|20000.0|\n",
      "|    2|      43.0|     43.0|        1|[Academia, Churra...|12a13315-d67f-48f...|      2|  null|       Usado| Apartamento|Residencial|   1|           Guaratiba|Zona Oeste|     245.0|null|Venda|15000.0|\n",
      "|    3|      43.0|     43.0|        1|[Churrasqueira, P...|a2e6d7a5-0ff0-484...|      2|     0|       Usado| Apartamento|Residencial|   0|              Cosmos|Zona Oeste|     285.0|null|Venda|20000.0|\n",
      "|    3|      43.0|     43.0|        1|[Churrasqueira, P...|a6e3173b-c950-4db...|      2|  null|       Usado| Apartamento|Residencial|   1|           Paciência|Zona Oeste|     250.0|null|Venda|15000.0|\n",
      "|    3|      43.0|     43.0|        1|[Churrasqueira, P...|2e6e5dfb-206c-496...|      2|     0|       Usado| Apartamento|Residencial|   1|           Guaratiba|Zona Oeste|     245.0|null|Venda|15000.0|\n",
      "|    3|      43.0|     43.0|        1|[Churrasqueira, P...|99f8d0f9-95a4-461...|      2|     0|       Usado| Apartamento|Residencial|   1|           Paciência|Zona Oeste|     245.0|null|Venda|15000.0|\n",
      "|    4|      43.0|     43.0|        1|[Churrasqueira, P...|b3f44c1a-2d50-4d5...|      2|  null|       Usado| Apartamento|Residencial|   1|           Guaratiba|Zona Oeste|     240.0|null|Venda|17999.0|\n",
      "|    0|      60.0|     60.0|        1|[Condomínio fechado]|dc99d9e1-4c63-41f...|      2|  null|       Usado| Apartamento|Residencial|   1|  Pedra de Guaratiba|Zona Oeste|       0.0| 0.0|Venda|15000.0|\n",
      "|    1|      43.0|     43.0|        1|[Churrasqueira, P...|aa3606d8-8bcd-45e...|      2|  null|       Usado| Apartamento|Residencial|   1|              Cosmos|Zona Oeste|     240.0| 0.0|Venda|19999.0|\n",
      "|    0|      43.0|     43.0|        1|[Churrasqueira, P...|df80b0d5-677c-4be...|      2|     0|       Usado| Apartamento|Residencial|   0|        Campo Grande|Zona Oeste|     245.0|null|Venda|19999.0|\n",
      "|    2|      43.0|     43.0|        1|[Academia, Churra...|3d6c0218-9b7d-474...|      2|  null|       Usado| Apartamento|Residencial|   2|          Santa Cruz|Zona Oeste|     290.0|null|Venda|25000.0|\n",
      "|    3|      43.0|     43.0|        1|[Salão de festas,...|304266cc-62d1-4ce...|      2|  null|       Usado| Apartamento|Residencial|null|           Guaratiba|Zona Oeste|     285.0|null|Venda|29999.0|\n",
      "|    4|      55.0|     55.0|        2|[Academia, Churra...|111472a2-afa1-4a7...|      2|     1|       Usado| Apartamento|Residencial|   1|Recreio dos Bande...|Zona Oeste|       0.0| 0.0|Venda|30000.0|\n",
      "|    0|      45.0|     45.0|        1|[Playground, Pisc...|275c1589-6537-4bf...|      2|     0|       Usado| Apartamento|Residencial|   1|              Cosmos|Zona Oeste|      null|null|Venda|30000.0|\n",
      "|    5|      42.0|     42.0|        1|[Condomínio fecha...|ab8f367f-7509-45c...|      2|     0|       Usado| Apartamento|Residencial|   0|            Realengo|Zona Oeste|     280.0| 0.0|Venda|25000.0|\n",
      "|    1|      43.0|     43.0|        1|[Churrasqueira, P...|e0a3d370-66dd-4f5...|      2|  null|       Usado| Apartamento|Residencial|   1|              Cosmos|Zona Oeste|     280.0|null|Venda|27999.0|\n",
      "|    3|      null|     55.0|        1|[Academia, Churra...|09c8ba2d-dbf1-487...|      2|     1|       Usado| Apartamento|Residencial|   1|     Todos os Santos|Zona Norte|       0.0| 0.0|Venda|26000.0|\n",
      "+-----+----------+---------+---------+--------------------+--------------------+-------+------+------------+------------+-----------+----+--------------------+----------+----------+----+-----+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "filtered_final_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- andar: long (nullable = true)\n",
      " |-- area_total: float (nullable = true)\n",
      " |-- area_util: float (nullable = true)\n",
      " |-- banheiros: long (nullable = true)\n",
      " |-- caracteristicas: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- quartos: long (nullable = true)\n",
      " |-- suites: long (nullable = true)\n",
      " |-- tipo_anuncio: string (nullable = true)\n",
      " |-- tipo_unidade: string (nullable = true)\n",
      " |-- tipo_uso: string (nullable = true)\n",
      " |-- vaga: long (nullable = true)\n",
      " |-- bairro: string (nullable = true)\n",
      " |-- zona: string (nullable = true)\n",
      " |-- condominio: float (nullable = true)\n",
      " |-- iptu: float (nullable = true)\n",
      " |-- tipo: string (nullable = true)\n",
      " |-- valor: float (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "filtered_final_df.printSchema()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### *Changing columns name*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_columns = {\n",
    "        'andar' : 'floor',\n",
    "        'area_total' : 'total_area',\n",
    "        'area_util' : 'useful_area',\n",
    "        'banheiros': 'toilets',\n",
    "        'caracteristicas' : 'features',\n",
    "        'quartos' : 'bedrooms',\n",
    "        'suites' : 'suites',\n",
    "        'tipo_anuncio' : 'ad_type',\n",
    "        'tipo_unidade' : 'unit_type',\n",
    "        'tipo_uso' : 'use_type',\n",
    "        'vaga' : 'car_slot',\n",
    "        'bairro' : 'district',\n",
    "        'zona' : 'zone',\n",
    "        'condomino' : 'condominium',\n",
    "        'iptu' : 'iptu',\n",
    "        'tipo' : 'type',\n",
    "        'valor' : 'value'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "for key, value in dict_columns.items():\n",
    "    filtered_final_df = filtered_final_df.withColumnRenamed(key, value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+----------+-----------+-------+--------------------+--------------------+--------+------+-------+-----------+-----------+--------+---------+----------+----------+----+-----+-------+\n",
      "|floor|total_area|useful_area|toilets|            features|                  id|bedrooms|suites|ad_type|  unit_type|   use_type|car_slot| district|      zone|condominio|iptu| type|  value|\n",
      "+-----+----------+-----------+-------+--------------------+--------------------+--------+------+-------+-----------+-----------+--------+---------+----------+----------+----+-----+-------+\n",
      "|    3|      43.0|       43.0|      1|[Academia, Churra...|d2e3a3aa-09b5-45a...|       2|  null|  Usado|Apartamento|Residencial|       1|Paciência|Zona Oeste|     245.0|null|Venda|15000.0|\n",
      "|    2|      42.0|       42.0|      1|[Churrasqueira, P...|085bab2c-87ad-452...|       2|  null|  Usado|Apartamento|Residencial|       1|Paciência|Zona Oeste|       0.0| 0.0|Venda|15000.0|\n",
      "+-----+----------+-----------+-------+--------------------+--------------------+--------+------+-------+-----------+-----------+--------+---------+----------+----------+----+-----+-------+\n",
      "only showing top 2 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "filtered_final_df.show(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Load**\n",
    "\n",
    "Saving the **`filtered_final_df`** in csv and in parquet. Then comparing reading performance for both."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Parquet*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_final_df.write.parquet(\n",
    "    path = 'data/parquet',\n",
    "    mode = 'overwrite'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we need to transforma the features column DataType, which is an array, to String to be able to save de DataFrame as csv\n",
    "filtered_final_df_csv = filtered_final_df.withColumn('features', filtered_final_df['features'].cast(StringType()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_final_df_csv.write.csv(\n",
    "    path='data/csv',\n",
    "    mode='overwrite',\n",
    "    sep=';',\n",
    "    header=True\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comparing performance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4.71 ms, sys: 7.62 ms, total: 12.3 ms\n",
      "Wall time: 3.09 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "path = 'data/csv/part-00000-4340dfbc-acf6-4ad3-8cc2-6ec617ebd94a-c000.csv'\n",
    "data = spark.read.csv(path, sep = ';', inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.14 ms, sys: 3.56 ms, total: 6.7 ms\n",
      "Wall time: 418 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "path = 'data/parquet/part-00000-8bc019cf-aa0b-4e9e-98c7-a3ac5f08fe01-c000.snappy.parquet'\n",
    "data = spark.read.parquet(path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can exist a difference in performance between parquet and csv files. Parquet have a better aggregation performance than csv files."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.9.12 (main, Apr  4 2022, 05:22:27) [MSC v.1916 64 bit (AMD64)]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "d8ec4a234a3048a765173e7b64119a8d1a8e4ce4548f9a2d0fe97ce15d6f4926"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}