analisis/parte2_analisis_spark.ipynb

{
 "nbformat": 4,
 "nbformat_minor": 0,
 "metadata": {
  "colab": {
   "provenance": [],
   "toc_visible": true,
   "authorship_tag": "ABX9TyPjdhwOyS4ksxKoqli4gnNf"
  },
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "cells": [
  {
   "cell_type": "markdown",
   "source": "# Parte 2: Análisis de datos con Spark",
   "metadata": {
    "id": "W3YPi1vVIwqI"
   }
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## Instalación y configuración de Spark\n",
    "#### ********************************************************************************************"
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Instalación de Java\n",
    "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
    "\n",
    "# Descargar e instalar Spark\n",
    "!wget -q https://archive.apache.org/dist/spark/spark-3.1.2/spark-3.1.2-bin-hadoop2.7.tgz\n",
    "!tar xf spark-3.1.2-bin-hadoop2.7.tgz\n",
    "\n",
    "# Instalación de findspark\n",
    "!pip install -q findspark\n",
    "\n",
    "# Configuración de las variables de entorno\n",
    "import os\n",
    "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
    "os.environ[\"SPARK_HOME\"] = \"/content/spark-3.1.2-bin-hadoop2.7\"\n",
    "\n",
    "# Inicialización de findspark\n",
    "import findspark\n",
    "findspark.init()"
   ],
   "metadata": {
    "id": "2qgLHFFZV-wL",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721188938196,
     "user_tz": 180,
     "elapsed": 56160,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 1,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Importacion de librerias\n",
    "#### ********************************************************************************************"
   ],
   "metadata": {
    "id": "XJdenHfWI4nI"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "from pyspark.sql.functions import *\n",
    "from google.colab import drive\n",
    "drive.mount('/content/drive')\n",
    "\n",
    "# Crear una sesión de Spark\n",
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.master(\"local[*]\").getOrCreate()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "EtvLmHUkWAzT",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721188982961,
     "user_tz": 180,
     "elapsed": 33614,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "e7df14e2-8a21-4b7f-b234-17276bd75751"
   },
   "execution_count": 2,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Mounted at /content/drive\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Rutas Drive\n",
    "input_dir = '/content/drive/MyDrive/Big Data/Originales'\n",
    "output_dir = '/content/drive/MyDrive/Big Data/Spark'"
   ],
   "metadata": {
    "id": "kwHVBSF8dv6Z",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721188982961,
     "user_tz": 180,
     "elapsed": 18,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 3,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Bus Stops\n",
    "#### ********************************************************************************************"
   ],
   "metadata": {
    "id": "oR2QSJRxIX-x"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_bus_stops = input_dir + '/bus_stops.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "bus_stops = spark.read.csv(src_bus_stops, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "kzCp1FZjoTV0",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191817526,
     "user_tz": 180,
     "elapsed": 1368,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 65,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar esquema de los datos\n",
    "bus_stops.printSchema()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "-GlPVwbLGvn2",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191819623,
     "user_tz": 180,
     "elapsed": 10,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "541a245d-06b8-4db9-a8dd-245683971c26"
   },
   "execution_count": 66,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "root\n",
      " |-- Code: string (nullable = true)\n",
      " |-- Transport: string (nullable = true)\n",
      " |-- Longitude: double (nullable = true)\n",
      " |-- Latitude: double (nullable = true)\n",
      " |-- Bus.Stop: string (nullable = true)\n",
      " |-- District.Name: string (nullable = true)\n",
      " |-- Neighborhood.Name: string (nullable = true)\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar las primeras filas\n",
    "bus_stops.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "vptu0iigZnPq",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721087577385,
     "user_tz": 180,
     "elapsed": 14,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "affdb0a4-67ee-4b9c-fa52-4078921c66ec"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+------------+---------+---------+----------+-------------------+--------------------+\n",
      "|Code|   Transport|Longitude| Latitude|  Bus.Stop|      District.Name|   Neighborhood.Name|\n",
      "+----+------------+---------+---------+----------+-------------------+--------------------+\n",
      "|K014|Day bus stop| 2.171619|41.413744|BUS -192--|     Horta-Guinardó|         el Guinardó|\n",
      "|K014|Day bus stop| 2.134902|41.420222|BUS -124--|             Gràcia|Vallcarca i els P...|\n",
      "|K014|Day bus stop| 2.162913|41.423187|BUS -117--|     Horta-Guinardó|la Font d'en Fargues|\n",
      "|K014|Day bus stop| 2.163667|41.422899|BUS -117--|     Horta-Guinardó|la Font d'en Fargues|\n",
      "|K014|Day bus stop| 2.120212|41.397209|BUS -130--|Sarrià-Sant Gervasi|              Sarrià|\n",
      "+----+------------+---------+---------+----------+-------------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(bus_stops.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "uHkfvHU7cJsP",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721087577385,
     "user_tz": 180,
     "elapsed": 13,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "ca1ac07c-3142-485a-b8d9-cbd3c76fe2be"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de columnas: 7\n",
      "Nombres de las columnas: ['Code', 'Transport', 'Longitude', 'Latitude', 'Bus.Stop', 'District.Name', 'Neighborhood.Name']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = bus_stops.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "YOnvEzaa_t6k",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721087577385,
     "user_tz": 180,
     "elapsed": 12,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "9a62e400-3905-44cb-c041-d333a4d3a652"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Nombres de las columnas: ['Code', 'Transport', 'Longitude', 'Latitude', 'Bus.Stop', 'District.Name', 'Neighborhood.Name']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos\n",
    "bus_stops.describe().show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "hrjfDYCfZ0Z7",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721087578327,
     "user_tz": 180,
     "elapsed": 952,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "d96284ec-1ffc-47f7-a4be-e0f26367a92c"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+----+----------------+--------------------+-------------------+-------------+-------------------+-----------------+\n",
      "|summary|Code|       Transport|           Longitude|           Latitude|     Bus.Stop|      District.Name|Neighborhood.Name|\n",
      "+-------+----+----------------+--------------------+-------------------+-------------+-------------------+-----------------+\n",
      "|  count|3162|            3162|                3162|               3162|         3162|               3146|             3146|\n",
      "|   mean|null|            null|   2.159823969639465|  41.40500162333972|         null|               null|             null|\n",
      "| stddev|null|            null|0.027898271557498862|0.02715006452368344|         null|               null|             null|\n",
      "|    min|K014|Airport bus stop|            2.055835|          41.321643|AEROBUS -A1--|       Ciutat Vella|    Baró de Viver|\n",
      "|    max|K017|  Night bus stop|            2.221753|          41.467592| NITBUS -N9--|Sarrià-Sant Gervasi|  les Tres Torres|\n",
      "+-------+----+----------------+--------------------+-------------------+-------------+-------------------+-----------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = bus_stops.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "HR3ePYuhiY7u",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721087578728,
     "user_tz": 180,
     "elapsed": 403,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "cc3180df-cc9a-405e-ce7e-8679ba14712b"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de registros: 3162\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "bus_stops = bus_stops.withColumnRenamed(\"Bus.Stop\", \"Bus_Stop\") \\\n",
    "                     .withColumnRenamed(\"District.Name\", \"District_Name\") \\\n",
    "                     .withColumnRenamed(\"Neighborhood.Name\", \"Neighborhood_Name\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "bus_stops.select([count(when(col(c).isNull(), c)).alias(c) for c in bus_stops.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "SCj86ErHc8b2",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191829802,
     "user_tz": 180,
     "elapsed": 487,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "56658425-3f20-46fc-999e-7be0f2f61e15"
   },
   "execution_count": 67,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+---------+---------+--------+--------+-------------+-----------------+\n",
      "|Code|Transport|Longitude|Latitude|Bus_Stop|District_Name|Neighborhood_Name|\n",
      "+----+---------+---------+--------+--------+-------------+-----------------+\n",
      "|   0|        0|        0|       0|       0|           16|               16|\n",
      "+----+---------+---------+--------+--------+-------------+-----------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "#Si Neighborhood_Name o District_Name son nulos rellenar con texto vacío\n",
    "bus_stops = bus_stops.na.fill({\"District_Name\": \"\", \"Neighborhood_Name\": \"\"})"
   ],
   "metadata": {
    "id": "7ylMfSSoeG-E",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191846330,
     "user_tz": 180,
     "elapsed": 449,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 68,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar duplicados\n",
    "print(\"Registros duplicados en bus_stops:\")\n",
    "print(bus_stops.count() - bus_stops.dropDuplicates().count())"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "D-s_FcoNeOZ6",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191851239,
     "user_tz": 180,
     "elapsed": 2001,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "78f2f6ba-24ac-4f25-89b4-f2045de63f9f"
   },
   "execution_count": 69,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Registros duplicados en bus_stops:\n",
      "0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar claves primarias únicas\n",
    "bus_stops.groupBy(\"Longitude\", \"Latitude\", \"Bus_Stop\").count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "xW7FB3RUezk5",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191859099,
     "user_tz": 180,
     "elapsed": 1855,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "07d461b4-53f8-48ad-b8e8-3c6e3b4fa5fd"
   },
   "execution_count": 70,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+---------+--------+--------+-----+\n",
      "|Longitude|Latitude|Bus_Stop|count|\n",
      "+---------+--------+--------+-----+\n",
      "+---------+--------+--------+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "bus_stops.write.csv(output_dir + \"/cleaned_bus_stops.csv\", header=True)"
   ],
   "metadata": {
    "id": "SduLtaMlek5m",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191861752,
     "user_tz": 180,
     "elapsed": 623,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 71,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Population\n",
    "#### ********************************************************************************************"
   ],
   "metadata": {
    "id": "G7RsBdQ4Z_Na"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_population = input_dir + '/population.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "population = spark.read.csv(src_population, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "-Rb7XrDKojH0",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191922331,
     "user_tz": 180,
     "elapsed": 2226,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 72,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar esquema de los datos\n",
    "population.printSchema()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "IwpZ1RkvZZED",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191924867,
     "user_tz": 180,
     "elapsed": 548,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "caf637d3-6af3-4a9a-a1c6-88210c2f4ec3"
   },
   "execution_count": 73,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "root\n",
      " |-- Year: integer (nullable = true)\n",
      " |-- District.Code: integer (nullable = true)\n",
      " |-- District.Name: string (nullable = true)\n",
      " |-- Neighborhood.Code: integer (nullable = true)\n",
      " |-- Neighborhood.Name: string (nullable = true)\n",
      " |-- Gender: string (nullable = true)\n",
      " |-- Age: string (nullable = true)\n",
      " |-- Number: integer (nullable = true)\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar las primeras filas\n",
    "population.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "EilfX1hybjC2",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721158268878,
     "user_tz": 180,
     "elapsed": 635,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "9cfd4039-00f6-4d74-bbe9-92ce83907483"
   },
   "execution_count": 10,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+--------------------+------+---+------+\n",
      "|Year|District.Code|District.Name|Neighborhood.Code|   Neighborhood.Name|Gender|Age|Number|\n",
      "+----+-------------+-------------+-----------------+--------------------+------+---+------+\n",
      "|2017|            1| Ciutat Vella|                1|            el Raval|  Male|0-4|   224|\n",
      "|2017|            1| Ciutat Vella|                2|      el Barri Gòtic|  Male|0-4|    50|\n",
      "|2017|            1| Ciutat Vella|                3|      la Barceloneta|  Male|0-4|    43|\n",
      "|2017|            1| Ciutat Vella|                4|Sant Pere, Santa ...|  Male|0-4|    95|\n",
      "|2017|            2|     Eixample|                5|       el Fort Pienc|  Male|0-4|   124|\n",
      "+----+-------------+-------------+-----------------+--------------------+------+---+------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(population.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "yWDkV3T1_Yfw",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721158268879,
     "user_tz": 180,
     "elapsed": 5,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "40b12cef-2954-40f8-de28-32ecdf59ca6b"
   },
   "execution_count": 11,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de columnas: 8\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = population.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "5-cskrYJ_e0E",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721158268879,
     "user_tz": 180,
     "elapsed": 4,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "b7353934-3f17-408c-c659-2119a23cb6f1"
   },
   "execution_count": 12,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Nombres de las columnas: ['Year', 'District.Code', 'District.Name', 'Neighborhood.Code', 'Neighborhood.Name', 'Gender', 'Age', 'Number']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos\n",
    "population.describe().show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "KC4jDsBRcggg",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721158275659,
     "user_tz": 180,
     "elapsed": 6783,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "4332b12d-898a-4da3-d3b8-359e52a328a0"
   },
   "execution_count": 13,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+------------------+------------------+-------------------+------------------+-----------------+------+-----+-----------------+\n",
      "|summary|              Year|     District.Code|      District.Name| Neighborhood.Code|Neighborhood.Name|Gender|  Age|           Number|\n",
      "+-------+------------------+------------------+-------------------+------------------+-----------------+------+-----+-----------------+\n",
      "|  count|             70080|             70080|              70080|             70080|            70080| 70080|70080|            70080|\n",
      "|   mean|            2015.0|6.2465753424657535|               null|              37.0|             null|  null| null|114.8447203196347|\n",
      "| stddev|1.4142236524678082|2.7883557847683456|               null|21.071457844839458|             null|  null| null|96.54038907224755|\n",
      "|    min|              2013|                 1|       Ciutat Vella|                 1|    Baró de Viver|Female|  0-4|                0|\n",
      "|    max|              2017|                10|Sarrià-Sant Gervasi|                73|  les Tres Torres|  Male| >=95|              777|\n",
      "+-------+------------------+------------------+-------------------+------------------+-----------------+------+-----+-----------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = population.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "UXVz7OYkigL1",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721158275660,
     "user_tz": 180,
     "elapsed": 26,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "e330d9f8-5380-4258-9857-10b3db291351"
   },
   "execution_count": 14,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de registros: 70080\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "population = population.withColumnRenamed(\"District.Code\", \"District_Code\") \\\n",
    "                       .withColumnRenamed(\"District.Name\", \"District_Name\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood.Code\", \"Neighborhood_Code\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood.Name\", \"Neighborhood_Name\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "population.select([count(when(col(c).isNull(), c)).alias(c) for c in population.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Wp3BBsaVZ-lF",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191939673,
     "user_tz": 180,
     "elapsed": 883,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "eb439533-e26f-4e0f-82ca-0abcce14a4e0"
   },
   "execution_count": 74,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+-----------------+------+---+------+\n",
      "|Year|District_Code|District_Name|Neighborhood_Code|Neighborhood_Name|Gender|Age|Number|\n",
      "+----+-------------+-------------+-----------------+-----------------+------+---+------+\n",
      "|   0|            0|            0|                0|                0|     0|  0|     0|\n",
      "+----+-------------+-------------+-----------------+-----------------+------+---+------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar duplicados\n",
    "print(\"Registros duplicados en population:\")\n",
    "print(population.count() - population.dropDuplicates().count())"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "sr3jspv2dgnB",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191950418,
     "user_tz": 180,
     "elapsed": 3480,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "19853b92-98eb-4c3c-ea66-412409e8bfd1"
   },
   "execution_count": 75,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Registros duplicados en population:\n",
      "4277\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Eliminar duplicados\n",
    "population = population.dropDuplicates()"
   ],
   "metadata": {
    "id": "KEoi068Zdv1O",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191953727,
     "user_tz": 180,
     "elapsed": 442,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 76,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar claves primarias únicas\n",
    "population.groupBy(\"Year\", \"District_Code\", \"Neighborhood_Code\", \"Gender\", \"Age\").count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "CgROMnVMenmR",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191972079,
     "user_tz": 180,
     "elapsed": 8509,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "a81ba45f-fdf5-49da-98e7-59d77e2f082a"
   },
   "execution_count": 77,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-----------------+------+-----+-----+\n",
      "|Year|District_Code|Neighborhood_Code|Gender|  Age|count|\n",
      "+----+-------------+-----------------+------+-----+-----+\n",
      "|2015|            5|               26|  Male|35-39|    5|\n",
      "|2014|            8|               49|Female|40-44|    5|\n",
      "|2016|            8|               44|Female|85-89|    5|\n",
      "|2015|            8|               47|Female|  0-4|    4|\n",
      "|2014|            9|               61|  Male|20-24|    4|\n",
      "|2014|            2|                7|Female|55-59|    5|\n",
      "|2016|            5|               27|Female|50-54|    5|\n",
      "|2015|            2|               10|  Male|50-54|    5|\n",
      "|2017|           10|               72|Female|70-74|    5|\n",
      "|2015|            9|               57|  Male|75-79|    4|\n",
      "|2014|            8|               50|  Male|65-69|    5|\n",
      "|2014|            4|               20|Female|35-39|    5|\n",
      "|2013|            3|               13|Female|70-74|    5|\n",
      "|2015|            8|               56|Female|20-24|    3|\n",
      "|2016|            9|               61|Female|90-94|    5|\n",
      "|2017|            2|                5|  Male|70-74|    5|\n",
      "|2017|            1|                1|  Male|80-84|    5|\n",
      "|2016|            3|               11|  Male|75-79|    5|\n",
      "|2014|            6|               28|  Male|20-24|    5|\n",
      "|2016|            3|               13|Female|  0-4|    5|\n",
      "+----+-------------+-----------------+------+-----+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Determinar todas las columnas excepto 'Number'\n",
    "group_columns = [col for col in population.columns if col != 'Number']\n",
    "\n",
    "# Agrupo y sumo la cantidad\n",
    "population = population.groupBy(group_columns).agg(sum(\"Number\").alias(\"Number\"))"
   ],
   "metadata": {
    "id": "rjFKdlK72uBa",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191979213,
     "user_tz": 180,
     "elapsed": 471,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 78,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "population.write.csv(output_dir + \"/cleaned_population.csv\", header=True)"
   ],
   "metadata": {
    "id": "bsblrtx8gRUD",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192012746,
     "user_tz": 180,
     "elapsed": 23994,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 79,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Immigrants by nationality\n",
    "#### ********************************************************************************************"
   ],
   "metadata": {
    "id": "V4fIoucJi9hI"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_immigrants_nationality = input_dir + '/immigrants_by_nationality.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "immigrants_by_nationality = spark.read.csv(src_immigrants_nationality, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "Kx7457esorfk",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192021165,
     "user_tz": 180,
     "elapsed": 1630,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 80,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "immigrants_by_nationality.printSchema()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "BmQVey9_jsWQ",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080150384,
     "user_tz": 180,
     "elapsed": 10,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "97769261-0894-42a6-f45d-4e2661f90a2a"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "root\n",
      " |-- Year: integer (nullable = true)\n",
      " |-- District Code: integer (nullable = true)\n",
      " |-- District Name: string (nullable = true)\n",
      " |-- Neighborhood Code: integer (nullable = true)\n",
      " |-- Neighborhood Name: string (nullable = true)\n",
      " |-- Nationality: string (nullable = true)\n",
      " |-- Number: integer (nullable = true)\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "immigrants_by_nationality.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "pYQEvhQmj56F",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080150384,
     "user_tz": 180,
     "elapsed": 9,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "dc6a3e4a-ea18-4e4c-c598-7bbb84ce2d7c"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+--------------------+-----------+------+\n",
      "|Year|District Code|District Name|Neighborhood Code|   Neighborhood Name|Nationality|Number|\n",
      "+----+-------------+-------------+-----------------+--------------------+-----------+------+\n",
      "|2017|            1| Ciutat Vella|                1|            el Raval|      Spain|  1109|\n",
      "|2017|            1| Ciutat Vella|                2|      el Barri Gòtic|      Spain|   482|\n",
      "|2017|            1| Ciutat Vella|                3|      la Barceloneta|      Spain|   414|\n",
      "|2017|            1| Ciutat Vella|                4|Sant Pere, Santa ...|      Spain|   537|\n",
      "|2017|            2|     Eixample|                5|       el Fort Pienc|      Spain|   663|\n",
      "+----+-------------+-------------+-----------------+--------------------+-----------+------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(immigrants_by_nationality.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "puwda1OWj_D1",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080150384,
     "user_tz": 180,
     "elapsed": 8,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "d412cf11-ea90-4c73-aa52-6329b831cd90"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de columnas: 7\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = immigrants_by_nationality.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ZDuo2edIkE0F",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080150384,
     "user_tz": 180,
     "elapsed": 7,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "f946dbc6-cb60-4104-884e-0960c0802ff1"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Nombres de las columnas: ['Year', 'District Code', 'District Name', 'Neighborhood Code', 'Neighborhood Name', 'Nationality', 'Number']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "immigrants_by_nationality.describe().show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "VqzAJcxSkaZ1",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080153228,
     "user_tz": 180,
     "elapsed": 2850,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "e3a1b9f7-8f5d-4e2e-d418-ddb0608d5323"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+------------------+------------------+-------------------+------------------+-----------------+-----------+-----------------+\n",
      "|summary|              Year|     District Code|      District Name| Neighborhood Code|Neighborhood Name|Nationality|           Number|\n",
      "+-------+------------------+------------------+-------------------+------------------+-----------------+-----------+-----------------+\n",
      "|  count|             35224|             35224|              35224|             35224|            35224|      35224|            35224|\n",
      "|   mean|2016.0105042016808|               7.5|               null| 37.83783783783784|             null|       null|7.707273449920509|\n",
      "| stddev|0.8108460719474905|11.061716951762222|               null|22.119174116683812|             null|       null|50.42188329015287|\n",
      "|    min|              2015|                 1|       Ciutat Vella|                 1|    Baró de Viver|Afghanistan|                0|\n",
      "|    max|              2017|                99|Sarrià-Sant Gervasi|                99|  les Tres Torres|   Zimbabwe|             1603|\n",
      "+-------+------------------+------------------+-------------------+------------------+-----------------+-----------+-----------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = immigrants_by_nationality.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "7ANvArmgkh2H",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080153228,
     "user_tz": 180,
     "elapsed": 17,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "8ba2cd69-5707-4634-d3e6-922876580aea"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de registros: 35224\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "immigrants_by_nationality = immigrants_by_nationality.withColumnRenamed(\"District Code\", \"District_Code\") \\\n",
    "                       .withColumnRenamed(\"District Name\", \"District_Name\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood Code\", \"Neighborhood_Code\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood Name\", \"Neighborhood_Name\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "immigrants_by_nationality.select([count(when(col(c).isNull(), c)).alias(c) for c in immigrants_by_nationality.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "HypnFms-kr4k",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192040968,
     "user_tz": 180,
     "elapsed": 452,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "b1d2b7a8-e29a-4e0e-a25e-9eb5ed7c046f"
   },
   "execution_count": 81,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+-----------------+-----------+------+\n",
      "|Year|District_Code|District_Name|Neighborhood_Code|Neighborhood_Name|Nationality|Number|\n",
      "+----+-------------+-------------+-----------------+-----------------+-----------+------+\n",
      "|   0|            0|            0|                0|                0|          0|     0|\n",
      "+----+-------------+-------------+-----------------+-----------------+-----------+------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = immigrants_by_nationality.count() - immigrants_by_nationality.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "N7rCZyRxlRA9",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192052609,
     "user_tz": 180,
     "elapsed": 2747,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "8f4d009d-cfd1-4456-846a-e2d4bbc0d552"
   },
   "execution_count": 82,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Year\", \"District_Code\", \"Neighborhood_Code\", \"Nationality\"]\n",
    "immigrants_by_nationality.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ulDuEOotleI-",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192068489,
     "user_tz": 180,
     "elapsed": 2397,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "4acdde23-a707-4344-ad9e-c3ee0a2d9c63"
   },
   "execution_count": 83,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-----------------+-----------+-----+\n",
      "|Year|District_Code|Neighborhood_Code|Nationality|count|\n",
      "+----+-------------+-----------------+-----------+-----+\n",
      "+----+-------------+-----------------+-----------+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "immigrants_by_nationality.write.csv(output_dir + \"/cleaned_immigrants_by_nationality.csv\", header=True)"
   ],
   "metadata": {
    "id": "av_t_N_Jlxqv",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192073358,
     "user_tz": 180,
     "elapsed": 881,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 84,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Births\n",
    "#### ********************************************************************************************"
   ],
   "metadata": {
    "id": "NKaqpCbfmMiA"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_births = input_dir + '/births.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "births = spark.read.csv(src_births, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "fd-hfRHwozVd",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192087973,
     "user_tz": 180,
     "elapsed": 1216,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 85,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "births.printSchema()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080181936,
     "user_tz": 180,
     "elapsed": 7,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "8411089b-066f-467c-864e-a27a201dfab6",
    "id": "H-q2Mxg9mMiB"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "root\n",
      " |-- Year: integer (nullable = true)\n",
      " |-- District Code: integer (nullable = true)\n",
      " |-- District Name: string (nullable = true)\n",
      " |-- Neighborhood Code: integer (nullable = true)\n",
      " |-- Neighborhood Name: string (nullable = true)\n",
      " |-- Gender: string (nullable = true)\n",
      " |-- Number: integer (nullable = true)\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "births.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080181936,
     "user_tz": 180,
     "elapsed": 6,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "60d24693-3d29-43a6-c2f9-b78a771d2919",
    "id": "ibQe2SSWmMiB"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+--------------------+------+------+\n",
      "|Year|District Code|District Name|Neighborhood Code|   Neighborhood Name|Gender|Number|\n",
      "+----+-------------+-------------+-----------------+--------------------+------+------+\n",
      "|2017|            1| Ciutat Vella|                1|            el Raval|  Boys|   283|\n",
      "|2017|            1| Ciutat Vella|                2|      el Barri Gòtic|  Boys|    56|\n",
      "|2017|            1| Ciutat Vella|                3|      la Barceloneta|  Boys|    51|\n",
      "|2017|            1| Ciutat Vella|                4|Sant Pere, Santa ...|  Boys|    90|\n",
      "|2017|            2|     Eixample|                5|       el Fort Pienc|  Boys|   117|\n",
      "+----+-------------+-------------+-----------------+--------------------+------+------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(births.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080182321,
     "user_tz": 180,
     "elapsed": 390,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "258b207e-0a23-4254-9634-4aea5e6e4a97",
    "id": "mNP_eb8FmMiB"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de columnas: 7\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = births.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080182321,
     "user_tz": 180,
     "elapsed": 6,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "66dbd53c-a9eb-4aea-c02f-7854a838edaa",
    "id": "q88-3bX9mMiB"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Nombres de las columnas: ['Year', 'District Code', 'District Name', 'Neighborhood Code', 'Neighborhood Name', 'Gender', 'Number']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "births.describe().show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080182772,
     "user_tz": 180,
     "elapsed": 456,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "777ca1cd-10bd-4dda-ecaf-2fe316b308ba",
    "id": "qhGU2_6HmMiB"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+------------------+-----------------+-------------------+------------------+-----------------+------+------------------+\n",
      "|summary|              Year|    District Code|      District Name| Neighborhood Code|Neighborhood Name|Gender|            Number|\n",
      "+-------+------------------+-----------------+-------------------+------------------+-----------------+------+------------------+\n",
      "|  count|               734|              734|                734|               734|              734|   734|               734|\n",
      "|   mean|2014.9972752043598|6.752043596730245|               null| 37.33787465940055|             null|  null| 91.75476839237058|\n",
      "| stddev| 1.416138973152209|7.378009426880971|               null|21.518487469619597|             null|  null|61.928487762933905|\n",
      "|    min|              2013|                1|       Ciutat Vella|                 1|    Baró de Viver|  Boys|                 0|\n",
      "|    max|              2017|               99|Sarrià-Sant Gervasi|                99|  les Tres Torres| Girls|               283|\n",
      "+-------+------------------+-----------------+-------------------+------------------+-----------------+------+------------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = births.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080182772,
     "user_tz": 180,
     "elapsed": 5,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "567d978c-f641-4b2e-f922-a62ab31ecb99",
    "id": "S_Dc1OHpmMiB"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de registros: 734\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "births = births.withColumnRenamed(\"District Code\", \"District_Code\") \\\n",
    "                       .withColumnRenamed(\"District Name\", \"District_Name\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood Code\", \"Neighborhood_Code\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood Name\", \"Neighborhood_Name\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "births.select([count(when(col(c).isNull(), c)).alias(c) for c in births.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192106997,
     "user_tz": 180,
     "elapsed": 443,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "b02dac2f-7a04-4336-92f2-d08d583b49e5",
    "id": "1_ZDPvk6mMiB"
   },
   "execution_count": 86,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+-----------------+------+------+\n",
      "|Year|District_Code|District_Name|Neighborhood_Code|Neighborhood_Name|Gender|Number|\n",
      "+----+-------------+-------------+-----------------+-----------------+------+------+\n",
      "|   0|            0|            0|                0|                0|     0|     0|\n",
      "+----+-------------+-------------+-----------------+-----------------+------+------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = births.count() - births.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192122219,
     "user_tz": 180,
     "elapsed": 2194,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "35576bf6-f8bc-4f2f-a466-b2538c960d93",
    "id": "HBM-Ow6wmMiC"
   },
   "execution_count": 87,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Year\", \"District_Code\", \"Neighborhood_Code\", \"Gender\"]\n",
    "births.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192135251,
     "user_tz": 180,
     "elapsed": 1068,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "a6b945ab-db34-4f24-e4d9-b28a654298d4",
    "id": "OB2J72FumMiC"
   },
   "execution_count": 88,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-----------------+------+-----+\n",
      "|Year|District_Code|Neighborhood_Code|Gender|count|\n",
      "+----+-------------+-----------------+------+-----+\n",
      "+----+-------------+-----------------+------+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "births.write.csv(output_dir + \"/cleaned_births.csv\", header=True)"
   ],
   "metadata": {
    "id": "0vuV6sczmMiC",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192143996,
     "user_tz": 180,
     "elapsed": 452,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 89,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Accidents\n",
    "#### ********************************************************************************************\n"
   ],
   "metadata": {
    "id": "ds0oIILKpMM1"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_accidents = input_dir + '/accidents_2017.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "accidents = spark.read.csv(src_accidents, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "j7nK9hlzpMM2",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192151378,
     "user_tz": 180,
     "elapsed": 1941,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 90,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "accidents.printSchema()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080366826,
     "user_tz": 180,
     "elapsed": 14,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "5c48acd3-b244-41b4-fbf3-730f553e4d62",
    "id": "d1t0xT8RpMM2"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "root\n",
      " |-- Id: string (nullable = true)\n",
      " |-- District Name: string (nullable = true)\n",
      " |-- Neighborhood Name: string (nullable = true)\n",
      " |-- Street: string (nullable = true)\n",
      " |-- Weekday: string (nullable = true)\n",
      " |-- Month: string (nullable = true)\n",
      " |-- Day: integer (nullable = true)\n",
      " |-- Hour: integer (nullable = true)\n",
      " |-- Part of the day: string (nullable = true)\n",
      " |-- Mild injuries: integer (nullable = true)\n",
      " |-- Serious injuries: integer (nullable = true)\n",
      " |-- Victims: integer (nullable = true)\n",
      " |-- Vehicles involved: integer (nullable = true)\n",
      " |-- Longitude: double (nullable = true)\n",
      " |-- Latitude: double (nullable = true)\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "accidents.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080366826,
     "user_tz": 180,
     "elapsed": 11,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "78ff2fca-92ad-4481-9da3-fa5a08041bf9",
    "id": "R1s7tT7ppMM2"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+---------------+-------------+--------------------+--------------------+--------+---------+---+----+---------------+-------------+----------------+-------+-----------------+----------+-----------+\n",
      "|             Id|District Name|   Neighborhood Name|              Street| Weekday|    Month|Day|Hour|Part of the day|Mild injuries|Serious injuries|Victims|Vehicles involved| Longitude|   Latitude|\n",
      "+---------------+-------------+--------------------+--------------------+--------+---------+---+----+---------------+-------------+----------------+-------+-----------------+----------+-----------+\n",
      "|2017S008429    |      Unknown|             Unknown|Número 27        ...|  Friday|  October| 13|   8|        Morning|            2|               0|      2|                2|2.12562442|41.34004482|\n",
      "|2017S007316    |      Unknown|             Unknown|Número 3 Zona Fra...|  Friday|September|  1|  13|        Morning|            2|               0|      2|                2|2.12045245|41.33942606|\n",
      "|2017S010210    |      Unknown|             Unknown|Litoral (Besòs)  ...|  Friday| December|  8|  21|      Afternoon|            5|               0|      5|                2| 2.1673561| 41.3608855|\n",
      "|2017S006364    |      Unknown|             Unknown|Número 3 Zona Fra...|  Friday|     July| 21|   2|          Night|            1|               0|      1|                2|2.12452894|41.33766786|\n",
      "|2017S004615    |   Sant Martí|el Camp de l'Arpa...|Las Navas de Tolo...|Thursday|      May| 25|  14|      Afternoon|            1|               0|      1|                3|  2.185272|  41.416365|\n",
      "+---------------+-------------+--------------------+--------------------+--------+---------+---+----+---------------+-------------+----------------+-------+-----------------+----------+-----------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(accidents.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080366826,
     "user_tz": 180,
     "elapsed": 9,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "0bfc14e5-fb41-46b4-ac51-47e9a6b2eb9b",
    "id": "NDLv0dVypMM2"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de columnas: 15\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = accidents.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080366826,
     "user_tz": 180,
     "elapsed": 8,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "28c951af-c59d-49c3-8641-242757d94b65",
    "id": "Hpx_FK1WpMM3"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Nombres de las columnas: ['Id', 'District Name', 'Neighborhood Name', 'Street', 'Weekday', 'Month', 'Day', 'Hour', 'Part of the day', 'Mild injuries', 'Serious injuries', 'Victims', 'Vehicles involved', 'Longitude', 'Latitude']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "accidents.describe().show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080368578,
     "user_tz": 180,
     "elapsed": 1759,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "b5dee588-eee5-403b-8fd4-b063de681d88",
    "id": "GlbXEbaEpMM3"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+---------------+-------------+-----------------+--------------------+---------+---------+------------------+------------------+---------------+------------------+--------------------+------------------+------------------+-------------------+--------------------+\n",
      "|summary|             Id|District Name|Neighborhood Name|              Street|  Weekday|    Month|               Day|              Hour|Part of the day|     Mild injuries|    Serious injuries|           Victims| Vehicles involved|          Longitude|            Latitude|\n",
      "+-------+---------------+-------------+-----------------+--------------------+---------+---------+------------------+------------------+---------------+------------------+--------------------+------------------+------------------+-------------------+--------------------+\n",
      "|  count|          10339|        10339|            10339|               10339|    10339|    10339|             10339|             10339|          10339|             10339|               10339|             10339|             10339|              10339|               10339|\n",
      "|   mean|           null|         null|             null|                null|     null|     null|15.775993809846213|13.811393751813522|           null|1.1541735177483317|0.023309797852790404|1.1786439694361157|1.9209788180675114| 2.1628318513308824|   41.39934615903286|\n",
      "| stddev|           null|         null|             null|                null|     null|     null| 8.763454965660072| 5.316489693733526|           null|0.7422943556946563|  0.1638027312255887|0.7351446563384879|0.7140114237519759|0.02417571951773182|0.021251088489247713|\n",
      "|    min|2017S000001    | Ciutat Vella|    Baró de Viver| ORTIGOSA        ...|   Friday|    April|                 1|                 0|      Afternoon|                 0|                   0|                 0|                 0|           2.091855|           41.323094|\n",
      "|    max|2017S010785    |      Unknown|  les Tres Torres|Àvila / Llull    ...|Wednesday|September|                31|                23|          Night|                10|                   4|                10|                14|           2.222572|           41.468244|\n",
      "+-------+---------------+-------------+-----------------+--------------------+---------+---------+------------------+------------------+---------------+------------------+--------------------+------------------+------------------+-------------------+--------------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = accidents.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721080368579,
     "user_tz": 180,
     "elapsed": 13,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "22e1ceed-8675-4302-f8fb-b04417f56a5c",
    "id": "zn3d3XkZpMM3"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de registros: 10339\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "accidents = accidents.withColumnRenamed(\"Part of the day\", \"Part_of_the_day\") \\\n",
    "                       .withColumnRenamed(\"District Name\", \"District_Name\") \\\n",
    "                       .withColumnRenamed(\"Mild injuries\", \"Mild_injuries\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood Name\", \"Neighborhood_Name\") \\\n",
    "                       .withColumnRenamed(\"Serious injuries\", \"Serious_injuries\") \\\n",
    "                       .withColumnRenamed(\"Vehicles involved\", \"Vehicles_involved\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "accidents.select([count(when(col(c).isNull(), c)).alias(c) for c in accidents.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192179847,
     "user_tz": 180,
     "elapsed": 881,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "8bbe139b-bff0-46e8-a282-4ae97088f82a",
    "id": "riJNi6empMM3"
   },
   "execution_count": 91,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+---+-------------+-----------------+------+-------+-----+---+----+---------------+-------------+----------------+-------+-----------------+---------+--------+\n",
      "| Id|District_Name|Neighborhood_Name|Street|Weekday|Month|Day|Hour|Part_of_the_day|Mild_injuries|Serious_injuries|Victims|Vehicles_involved|Longitude|Latitude|\n",
      "+---+-------------+-----------------+------+-------+-----+---+----+---------------+-------------+----------------+-------+-----------------+---------+--------+\n",
      "|  0|            0|                0|     0|      0|    0|  0|   0|              0|            0|               0|      0|                0|        0|       0|\n",
      "+---+-------------+-----------------+------+-------+-----+---+----+---------------+-------------+----------------+-------+-----------------+---------+--------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = accidents.count() - accidents.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192192624,
     "user_tz": 180,
     "elapsed": 3636,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "1314dada-27b6-4a0b-c0d9-cb0529ec1803",
    "id": "vJ7iYkt9pMM4"
   },
   "execution_count": 92,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 4\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Eliminar registros duplicados\n",
    "accidents = accidents.dropDuplicates()"
   ],
   "metadata": {
    "id": "fJaHI7cnpMM4",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192198041,
     "user_tz": 180,
     "elapsed": 525,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 93,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Id\"]\n",
    "accidents.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192209753,
     "user_tz": 180,
     "elapsed": 7234,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "73c74d57-4459-48f1-8115-1d8be8a8d700",
    "id": "NwvYjRd1pMM4"
   },
   "execution_count": 94,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+---+-----+\n",
      "| Id|count|\n",
      "+---+-----+\n",
      "+---+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "accidents.write.csv(output_dir + \"/cleaned_accidents.csv\", header=True)"
   ],
   "metadata": {
    "id": "UyuQS5bXpMM4",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192231587,
     "user_tz": 180,
     "elapsed": 14865,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 95,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Life expectancy\n",
    "#### ********************************************************************************************\n"
   ],
   "metadata": {
    "id": "rNtRxttI5BuZ"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_life_expectancy = input_dir + '/life_expectancy.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "life_expectancy = spark.read.csv(src_life_expectancy, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "Dt4eM5xHst5G",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192237637,
     "user_tz": 180,
     "elapsed": 1078,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 96,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "life_expectancy.printSchema()"
   ],
   "metadata": {
    "id": "mbZRt0fWst5G"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "life_expectancy.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721088772647,
     "user_tz": 180,
     "elapsed": 396,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "eac6689d-7b37-48a6-e196-94a4236f591b",
    "id": "9I9Uteo1st5H"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+--------------------+---------+---------+---------+---------+---------+------+\n",
      "|        Neighborhood|2006-2010|2007-2011|2008-2012|2009-2013|2010-2014|Gender|\n",
      "+--------------------+---------+---------+---------+---------+---------+------+\n",
      "|            el Raval|     87.5|     84.9|     84.7|     84.9|     85.3|Female|\n",
      "|      el Barri Gòtic|     88.0|     84.3|     84.4|     87.5|     84.4|Female|\n",
      "|      la Barceloneta|     88.2|     85.3|     84.4|     86.3|     84.7|Female|\n",
      "|Sant Pere, Santa ...|     88.9|     85.9|     86.1|     85.5|     85.3|Female|\n",
      "|       el Fort Pienc|     89.7|     87.1|     87.2|     87.7|     86.7|Female|\n",
      "+--------------------+---------+---------+---------+---------+---------+------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(life_expectancy.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "id": "t5U4bj2ast5H"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = life_expectancy.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "id": "sxxttftast5H"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "life_expectancy.describe().show()"
   ],
   "metadata": {
    "id": "tme0e6IEst5H"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = life_expectancy.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "id": "1D8esKTBst5H"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar valores nulos\n",
    "life_expectancy.select([count(when(col(c).isNull(), c)).alias(c) for c in life_expectancy.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192247900,
     "user_tz": 180,
     "elapsed": 515,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "4167fd9d-5d77-4235-dcc7-04b102e83531",
    "id": "f7pLKOWZst5I"
   },
   "execution_count": 97,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+------------+---------+---------+---------+---------+---------+------+\n",
      "|Neighborhood|2006-2010|2007-2011|2008-2012|2009-2013|2010-2014|Gender|\n",
      "+------------+---------+---------+---------+---------+---------+------+\n",
      "|           0|        8|        6|        8|        6|        4|     0|\n",
      "+------------+---------+---------+---------+---------+---------+------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Sustituir valores nulos en todas las columnas excepto \"Gender\" y \"Neighborhood\" con 0.0\n",
    "columns_to_fill = [c for c in life_expectancy.columns if c not in [\"Gender\", \"Neighborhood\"]]\n",
    "life_expectancy = life_expectancy.fillna(0.0, subset=columns_to_fill)"
   ],
   "metadata": {
    "id": "SiAOtXUUst5I",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192262281,
     "user_tz": 180,
     "elapsed": 445,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 98,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = life_expectancy.count() - life_expectancy.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192266859,
     "user_tz": 180,
     "elapsed": 1474,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "1bf250c2-445b-4180-e099-39c091ba40d2",
    "id": "dfE2KtB3st5I"
   },
   "execution_count": 99,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Neighborhood\", \"Gender\"]\n",
    "life_expectancy.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192279017,
     "user_tz": 180,
     "elapsed": 1067,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "64cc2435-b03f-48df-eb2d-71c1dabc08b8",
    "id": "P-QC6SQwst5I"
   },
   "execution_count": 100,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+------------+------+-----+\n",
      "|Neighborhood|Gender|count|\n",
      "+------------+------+-----+\n",
      "+------------+------+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "life_expectancy.write.csv(output_dir + \"/cleaned_life_expectancy.csv\", header=True)"
   ],
   "metadata": {
    "id": "Iao0jmJwst5I",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192285620,
     "user_tz": 180,
     "elapsed": 439,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 101,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Unemployment\n",
    "#### ********************************************************************************************\n"
   ],
   "metadata": {
    "id": "oS4yH9V0ufCb"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_unemployment = input_dir + '/unemployment.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "unemployment = spark.read.csv(src_unemployment, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "6NWvlRutufCb",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192290467,
     "user_tz": 180,
     "elapsed": 1288,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 102,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "unemployment.printSchema()"
   ],
   "metadata": {
    "id": "G0owoN95ufCb"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "unemployment.show(5)"
   ],
   "metadata": {
    "id": "974dzbkTufCc"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(unemployment.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "id": "BWajPLJ9ufCc"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = unemployment.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "id": "0B0kWB1aufCc"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "unemployment.describe().show()"
   ],
   "metadata": {
    "id": "7t42cUeRufCc"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = unemployment.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "id": "34rfBcvUufCc"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "unemployment = unemployment.withColumnRenamed(\"District Code\", \"District_Code\") \\\n",
    "                       .withColumnRenamed(\"District Name\", \"District_Name\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood Code\", \"Neighborhood_Code\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood Name\", \"Neighborhood_Name\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "unemployment.select([count(when(col(c).isNull(), c)).alias(c) for c in unemployment.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192307665,
     "user_tz": 180,
     "elapsed": 1100,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "33792408-10a7-4e8c-b6a5-2aa2d4221114",
    "id": "69cdGg-9ufCd"
   },
   "execution_count": 103,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-----+-------------+-------------+-----------------+-----------------+------+-----------------+------+\n",
      "|Year|Month|District_Code|District_Name|Neighborhood_Code|Neighborhood_Name|Gender|Demand_occupation|Number|\n",
      "+----+-----+-------------+-------------+-----------------+-----------------+------+-----------------+------+\n",
      "|   0|    0|            0|            0|                0|                0|     0|                0|     0|\n",
      "+----+-----+-------------+-------------+-----------------+-----------------+------+-----------------+------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = unemployment.count() - unemployment.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192317538,
     "user_tz": 180,
     "elapsed": 1875,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "be0e9d91-fc2b-4c94-d2ae-30290cb49782",
    "id": "gb1L90chufCd"
   },
   "execution_count": 104,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Year\", \"Month\", \"District_Code\", \"Neighborhood_Code\", \"Gender\", \"Demand_occupation\"]\n",
    "unemployment.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "id": "MsFgYfxgufCd",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192325610,
     "user_tz": 180,
     "elapsed": 1605,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "e75a7f55-e62c-41ba-a61a-ef857f467a19"
   },
   "execution_count": 105,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-----+-------------+-----------------+------+-----------------+-----+\n",
      "|Year|Month|District_Code|Neighborhood_Code|Gender|Demand_occupation|count|\n",
      "+----+-----+-------------+-----------------+------+-----------------+-----+\n",
      "+----+-----+-------------+-----------------+------+-----------------+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "unemployment.write.csv(output_dir + \"/cleaned_unemployment.csv\", header=True)"
   ],
   "metadata": {
    "id": "MtWeoUtWufCe",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192332350,
     "user_tz": 180,
     "elapsed": 1087,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 106,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Air quality\n",
    "#### ********************************************************************************************"
   ],
   "metadata": {
    "id": "RfGmjdUG8sZW"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_air_quality = input_dir + '/air_quality_Nov2017.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "air_quality = spark.read.csv(src_air_quality, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "9bxrZpFW8sZW",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192350165,
     "user_tz": 180,
     "elapsed": 1130,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 107,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "air_quality.printSchema()"
   ],
   "metadata": {
    "id": "TCiGw7Tf8sZW"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "air_quality.show(5)"
   ],
   "metadata": {
    "id": "KRpYTk2r8sZW"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(air_quality.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "id": "00D4S36H8sZW"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = air_quality.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "id": "cqNFcV3i8sZX"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "air_quality.describe().show()"
   ],
   "metadata": {
    "id": "9LSO6vXA8sZX"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = air_quality.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "id": "NBYWCHWQ8sZX"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "air_quality = air_quality.withColumnRenamed(\"Air Quality\", \"Air_Quality\") \\\n",
    "                         .withColumnRenamed(\"O3 Hour\", \"O3_Hour\") \\\n",
    "                         .withColumnRenamed(\"O3 Quality\", \"O3_Quality\") \\\n",
    "                         .withColumnRenamed(\"O3 Value\", \"O3_Value\") \\\n",
    "                         .withColumnRenamed(\"NO2 Hour\", \"NO2_Hour\") \\\n",
    "                         .withColumnRenamed(\"NO2 Quality\", \"NO2_Quality\") \\\n",
    "                         .withColumnRenamed(\"NO2 Value\", \"NO2_Value\") \\\n",
    "                         .withColumnRenamed(\"PM10 Hour\", \"PM10_Hour\") \\\n",
    "                         .withColumnRenamed(\"PM10 Quality\", \"PM10_Quality\") \\\n",
    "                         .withColumnRenamed(\"PM10 Value\", \"PM10_Value\") \\\n",
    "                         .withColumnRenamed(\"Date Time\", \"Date_Time\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "air_quality.select([count(when(col(c).isNull(), c)).alias(c) for c in air_quality.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192368053,
     "user_tz": 180,
     "elapsed": 1091,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "6e25ad0b-1f93-4c96-ff08-86b326d3e970",
    "id": "-RvXCCET8sZX"
   },
   "execution_count": 108,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+-----------+---------+--------+-------+----------+--------+--------+-----------+---------+---------+------------+----------+---------+---------+\n",
      "|Station|Air_Quality|Longitude|Latitude|O3_Hour|O3_Quality|O3_Value|NO2_Hour|NO2_Quality|NO2_Value|PM10_Hour|PM10_Quality|PM10_Value|Generated|Date_Time|\n",
      "+-------+-----------+---------+--------+-------+----------+--------+--------+-----------+---------+---------+------------+----------+---------+---------+\n",
      "|      0|          0|        0|       0|      0|         0|       0|       0|          0|        0|        0|           0|         0|        0|        0|\n",
      "+-------+-----------+---------+--------+-------+----------+--------+--------+-----------+---------+---------+------------+----------+---------+---------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = air_quality.count() - air_quality.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192378548,
     "user_tz": 180,
     "elapsed": 2173,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "b3ecdcbe-ea54-47bd-8896-33d614e04560",
    "id": "PMENPrKI8sZX"
   },
   "execution_count": 109,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Station\", \"Date_Time\"]\n",
    "air_quality.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192385459,
     "user_tz": 180,
     "elapsed": 1064,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "1c05ed91-d93c-4fa1-8840-e607c180ed7b",
    "id": "K5V3cdSK8sZY"
   },
   "execution_count": 110,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+---------+-----+\n",
      "|Station|Date_Time|count|\n",
      "+-------+---------+-----+\n",
      "+-------+---------+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "air_quality.write.csv(output_dir + \"/cleaned_air_quality.csv\", header=True)"
   ],
   "metadata": {
    "id": "wSnxI-0B8sZY",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192388784,
     "user_tz": 180,
     "elapsed": 465,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 111,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Most frequent names\n",
    "#### ********************************************************************************************\n"
   ],
   "metadata": {
    "id": "12Upd3Q6RcU_"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_most_frequent_names = input_dir + '/most_frequent_names.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "most_frequent_names = spark.read.csv(src_most_frequent_names, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "QSPK9_FCRcU_",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192403608,
     "user_tz": 180,
     "elapsed": 1084,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 112,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "most_frequent_names.printSchema()"
   ],
   "metadata": {
    "id": "9goAk1byRcU_"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "most_frequent_names.show(5)"
   ],
   "metadata": {
    "id": "rR3tAQW8RcU_"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(most_frequent_names.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "id": "6BRFA1rlRcU_"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = most_frequent_names.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "id": "R2XM94pGRcVA"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "most_frequent_names.describe().show()"
   ],
   "metadata": {
    "id": "0ml9wz6wRcVA"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = most_frequent_names.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "id": "yrz5IpY9RcVA"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar valores nulos\n",
    "most_frequent_names.select([count(when(col(c).isNull(), c)).alias(c) for c in most_frequent_names.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192420780,
     "user_tz": 180,
     "elapsed": 625,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "95cc65df-abf6-4a9b-fac4-9944573e628c",
    "id": "3O7D6W-LRcVA"
   },
   "execution_count": 113,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-----+----+------+------+---------+\n",
      "|Order|Name|Gender|Decade|Frequency|\n",
      "+-----+----+------+------+---------+\n",
      "|    0|   0|     0|     0|        0|\n",
      "+-----+----+------+------+---------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = most_frequent_names.count() - most_frequent_names.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192428214,
     "user_tz": 180,
     "elapsed": 1136,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "fa05dfe2-f3d8-4c1f-c808-6503c9517eb3",
    "id": "0XB7RlmDRcVA"
   },
   "execution_count": 114,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Order\", \"Decade\", \"Name\", \"Gender\"]\n",
    "most_frequent_names.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192435342,
     "user_tz": 180,
     "elapsed": 1090,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "ddb3ba2c-5045-48d3-b9a0-161d627f9198",
    "id": "cZwyryVxRcVB"
   },
   "execution_count": 115,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-----+------+----+------+-----+\n",
      "|Order|Decade|Name|Gender|count|\n",
      "+-----+------+----+------+-----+\n",
      "+-----+------+----+------+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "most_frequent_names.write.csv(output_dir + \"/cleaned_most_frequent_names.csv\", header=True)"
   ],
   "metadata": {
    "id": "hBBlJpl6RcVB",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192441382,
     "user_tz": 180,
     "elapsed": 477,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 116,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Transports\n",
    "#### ********************************************************************************************\n"
   ],
   "metadata": {
    "id": "nN5lzu5f2V37"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_transports = input_dir + '/transports.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "transports = spark.read.csv(src_transports, header=True, inferSchema=True)"
   ],
   "metadata": {
    "id": "Fv3flj2b2V4H",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192460362,
     "user_tz": 180,
     "elapsed": 643,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 117,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "transports.printSchema()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "FwyuKc672V4H",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721188994847,
     "user_tz": 180,
     "elapsed": 439,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "856b59b8-c51e-42c7-ef7f-1e36a5316a9b"
   },
   "execution_count": 5,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "root\n",
      " |-- Code: string (nullable = true)\n",
      " |-- Transport: string (nullable = true)\n",
      " |-- Longitude: double (nullable = true)\n",
      " |-- Latitude: double (nullable = true)\n",
      " |-- Station: string (nullable = true)\n",
      " |-- District.Name: string (nullable = true)\n",
      " |-- Neighborhood.Name: string (nullable = true)\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "transports.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "cz5QcTKH2V4H",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189004982,
     "user_tz": 180,
     "elapsed": 1051,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "90a36014-cc40-48d0-a88e-d9d266e27d6a"
   },
   "execution_count": 6,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-----------+---------+---------+--------------------+-------------------+--------------------+\n",
      "|Code|  Transport|Longitude| Latitude|             Station|      District.Name|   Neighborhood.Name|\n",
      "+----+-----------+---------+---------+--------------------+-------------------+--------------------+\n",
      "|K001|Underground|  2.11937|41.399203|FGC (L6) - REINA ...|Sarrià-Sant Gervasi|              Sarrià|\n",
      "|K001|Underground| 2.135427|41.397791|FGC (L6) - LA BON...|Sarrià-Sant Gervasi|Sant Gervasi - Ga...|\n",
      "|K001|Underground| 2.185391|41.451492|METRO (L11) - CAS...|         Nou Barris|    la Trinitat Nova|\n",
      "|K001|Underground| 2.174473|41.460889|METRO (L11) - CIU...|         Nou Barris|    Ciutat Meridiana|\n",
      "|K001|Underground| 2.168588|  41.3872|METRO (L1) - CATA...|           Eixample|la Dreta de l'Eix...|\n",
      "+----+-----------+---------+---------+--------------------+-------------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(transports.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "pkqOXrpg2V4H",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189010729,
     "user_tz": 180,
     "elapsed": 465,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "42953ca0-c9bc-4a6c-8c94-2896e283ba26"
   },
   "execution_count": 7,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de columnas: 7\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = transports.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ZQw3PMwI2V4H",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189013340,
     "user_tz": 180,
     "elapsed": 451,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "07f3cbd9-31a5-4834-e0ac-f1238c93a255"
   },
   "execution_count": 8,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Nombres de las columnas: ['Code', 'Transport', 'Longitude', 'Latitude', 'Station', 'District.Name', 'Neighborhood.Name']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "transports.describe().show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Q7RiG0-r2V4H",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189018595,
     "user_tz": 180,
     "elapsed": 3012,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "17f50799-6bbb-4f37-c9c7-44cb58ec463a"
   },
   "execution_count": 9,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+----+-------------+-------------------+--------------------+--------------------+-------------------+-----------------+\n",
      "|summary|Code|    Transport|          Longitude|            Latitude|             Station|      District.Name|Neighborhood.Name|\n",
      "+-------+----+-------------+-------------------+--------------------+--------------------+-------------------+-----------------+\n",
      "|  count| 651|          651|                651|                 651|                 651|                487|              487|\n",
      "|   mean|null|         null|  2.156516794162825|   41.39935084024583|                null|               null|             null|\n",
      "| stddev|null|         null|0.04006143105067931|0.028519542256909725|                null|               null|             null|\n",
      "|    min|K001|Airport train|            2.00528|           41.304419| Estació Marítima A-|       Ciutat Vella|        Canyelles|\n",
      "|    max|K011|  Underground|           2.249093|           41.496321|Tren AEROPORT- AE...|Sarrià-Sant Gervasi|  les Tres Torres|\n",
      "+-------+----+-------------+-------------------+--------------------+--------------------+-------------------+-----------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = transports.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "D_uL6A122V4I",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189020975,
     "user_tz": 180,
     "elapsed": 446,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "b64059ab-a29b-4b89-b4b4-9849af320c9d"
   },
   "execution_count": 10,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de registros: 651\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "transports = transports.withColumnRenamed(\"District.Name\", \"District_Name\") \\\n",
    "                         .withColumnRenamed(\"Neighborhood.Name\", \"Neighborhood_Name\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "transports.select([count(when(col(c).isNull(), c)).alias(c) for c in transports.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192479417,
     "user_tz": 180,
     "elapsed": 442,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "9bac8360-2f90-4473-c52a-7f07371a4e84",
    "id": "BzpCgsym2V4I"
   },
   "execution_count": 118,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+---------+---------+--------+-------+-------------+-----------------+\n",
      "|Code|Transport|Longitude|Latitude|Station|District_Name|Neighborhood_Name|\n",
      "+----+---------+---------+--------+-------+-------------+-----------------+\n",
      "|   0|        0|        0|       0|      0|          164|              164|\n",
      "+----+---------+---------+--------+-------+-------------+-----------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "#Si Neighborhood_Name o District_Name son nulos rellenar con texto vacío\n",
    "transports = transports.na.fill({\"District_Name\": \"\", \"Neighborhood_Name\": \"\"})"
   ],
   "metadata": {
    "id": "ksBD13uz2V4I",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192491049,
     "user_tz": 180,
     "elapsed": 462,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 119,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = transports.count() - transports.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192496509,
     "user_tz": 180,
     "elapsed": 2660,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "2f94f1f7-690b-4420-aad6-16c1383c21f0",
    "id": "971Pw6Z12V4I"
   },
   "execution_count": 120,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Code\", \"Longitude\", \"Latitude\"]\n",
    "transports.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192505337,
     "user_tz": 180,
     "elapsed": 1251,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "7c360f34-83d4-49c6-eff1-547472297a76",
    "id": "724CKefW2V4J"
   },
   "execution_count": 121,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+---------+--------+-----+\n",
      "|Code|Longitude|Latitude|count|\n",
      "+----+---------+--------+-----+\n",
      "+----+---------+--------+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "transports.write.csv(output_dir + \"/cleaned_transports.csv\", header=True)"
   ],
   "metadata": {
    "id": "B3gkqJXJ2V4J",
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192509965,
     "user_tz": 180,
     "elapsed": 441,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    }
   },
   "execution_count": 122,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Immigrants and emigrants by age\n",
    "#### ********************************************************************************************\n"
   ],
   "metadata": {
    "id": "5Da0JeBX5OxR"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_immigrants_emigrants_by_age = input_dir + '/immigrants_emigrants_by_age.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "immigrants_emigrants_by_age = spark.read.csv(src_immigrants_emigrants_by_age, header=True, inferSchema=True)"
   ],
   "metadata": {
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192521660,
     "user_tz": 180,
     "elapsed": 486,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "id": "1k_Ws3ur5Oxj"
   },
   "execution_count": 123,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "immigrants_emigrants_by_age.printSchema()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189655140,
     "user_tz": 180,
     "elapsed": 434,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "8b459205-3c3b-4f3a-8a91-e0997b08417c",
    "id": "pNWtCcXk5Oxk"
   },
   "execution_count": 29,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "root\n",
      " |-- Year: integer (nullable = true)\n",
      " |-- District Code: integer (nullable = true)\n",
      " |-- District Name: string (nullable = true)\n",
      " |-- Neighborhood Code: integer (nullable = true)\n",
      " |-- Neighborhood Name: string (nullable = true)\n",
      " |-- Age: string (nullable = true)\n",
      " |-- Immigrants: integer (nullable = true)\n",
      " |-- Emigrants: integer (nullable = true)\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "immigrants_emigrants_by_age.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189659714,
     "user_tz": 180,
     "elapsed": 471,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "a46faa56-8f0d-4202-e7b0-2da263c072fb",
    "id": "_E6wU_Q65Oxk"
   },
   "execution_count": 30,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+--------------------+---+----------+---------+\n",
      "|Year|District Code|District Name|Neighborhood Code|   Neighborhood Name|Age|Immigrants|Emigrants|\n",
      "+----+-------------+-------------+-----------------+--------------------+---+----------+---------+\n",
      "|2017|            1| Ciutat Vella|                1|            el Raval|0-4|       154|      108|\n",
      "|2017|            1| Ciutat Vella|                2|      el Barri Gòtic|0-4|        58|       33|\n",
      "|2017|            1| Ciutat Vella|                3|      la Barceloneta|0-4|        38|       37|\n",
      "|2017|            1| Ciutat Vella|                4|Sant Pere, Santa ...|0-4|        56|       55|\n",
      "|2017|            2|     Eixample|                5|       el Fort Pienc|0-4|        79|       60|\n",
      "+----+-------------+-------------+-----------------+--------------------+---+----------+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(immigrants_emigrants_by_age.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189676206,
     "user_tz": 180,
     "elapsed": 449,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "0e29c17e-597a-4256-cbf5-685c877af737",
    "id": "O6rm4R145Oxk"
   },
   "execution_count": 31,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de columnas: 8\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = immigrants_emigrants_by_age.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189686463,
     "user_tz": 180,
     "elapsed": 459,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "8e55136c-ddb0-4c6a-86ac-572fa220c79c",
    "id": "WWRyfG6Z5Oxk"
   },
   "execution_count": 32,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Nombres de las columnas: ['Year', 'District Code', 'District Name', 'Neighborhood Code', 'Neighborhood Name', 'Age', 'Immigrants', 'Emigrants']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "immigrants_emigrants_by_age.describe().show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189692339,
     "user_tz": 180,
     "elapsed": 1096,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "6e592827-a48d-4c48-c231-c6517e5516ea",
    "id": "bI8e69Bu5Oxl"
   },
   "execution_count": 33,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+------------------+------------------+-------------------+-----------------+-----------------+-----+------------------+------------------+\n",
      "|summary|              Year|     District Code|      District Name|Neighborhood Code|Neighborhood Name|  Age|        Immigrants|         Emigrants|\n",
      "+-------+------------------+------------------+-------------------+-----------------+-----------------+-----+------------------+------------------+\n",
      "|  count|              4662|              4662|               4662|             4662|             4662| 4662|              4662|              4662|\n",
      "|   mean|            2016.0|               7.5|               null|37.83783783783784|             null| null|58.232732732732735|33.684041184041185|\n",
      "| stddev|0.8165841643654697|11.062746475423248|               null|22.12123276754366|             null| null|108.97053575400817|    45.92536502973|\n",
      "|    min|              2015|                 1|       Ciutat Vella|                1|    Baró de Viver|  0-4|                 0|                 0|\n",
      "|    max|              2017|                99|Sarrià-Sant Gervasi|               99|  les Tres Torres|>=100|              1393|               451|\n",
      "+-------+------------------+------------------+-------------------+-----------------+-----------------+-----+------------------+------------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = immigrants_emigrants_by_age.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721189697615,
     "user_tz": 180,
     "elapsed": 444,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "ea3e80aa-075e-4609-acfa-7bf1687eed29",
    "id": "Fe3zxNrb5Oxl"
   },
   "execution_count": 34,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de registros: 4662\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "immigrants_emigrants_by_age = immigrants_emigrants_by_age.withColumnRenamed(\"District Name\", \"District_Name\") \\\n",
    "                         .withColumnRenamed(\"Neighborhood Name\", \"Neighborhood_Name\") \\\n",
    "                         .withColumnRenamed(\"District Code\", \"District_Code\") \\\n",
    "                         .withColumnRenamed(\"Neighborhood Code\", \"Neighborhood_Code\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "immigrants_emigrants_by_age.select([count(when(col(c).isNull(), c)).alias(c) for c in immigrants_emigrants_by_age.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192541396,
     "user_tz": 180,
     "elapsed": 458,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "ca1e3f8e-9ada-4c00-a44d-886c48e67829",
    "id": "U7MT7j2g5Oxl"
   },
   "execution_count": 124,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+-----------------+---+----------+---------+\n",
      "|Year|District_Code|District_Name|Neighborhood_Code|Neighborhood_Name|Age|Immigrants|Emigrants|\n",
      "+----+-------------+-------------+-----------------+-----------------+---+----------+---------+\n",
      "|   0|            0|            0|                0|                0|  0|         0|        0|\n",
      "+----+-------------+-------------+-----------------+-----------------+---+----------+---------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = immigrants_emigrants_by_age.count() - immigrants_emigrants_by_age.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192549355,
     "user_tz": 180,
     "elapsed": 2686,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "76704526-3197-4797-9e82-9726ab2a8a27",
    "id": "RmKMZvLi5Oxl"
   },
   "execution_count": 125,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Year\", \"District_Code\", \"Neighborhood_Code\", \"Age\"]\n",
    "immigrants_emigrants_by_age.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192554573,
     "user_tz": 180,
     "elapsed": 1111,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "40040f28-302f-47af-db82-bbdf806593c8",
    "id": "ITLJofjX5Oxm"
   },
   "execution_count": 126,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-----------------+---+-----+\n",
      "|Year|District_Code|Neighborhood_Code|Age|count|\n",
      "+----+-------------+-----------------+---+-----+\n",
      "+----+-------------+-----------------+---+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "immigrants_emigrants_by_age.write.csv(output_dir + \"/cleaned_immigrants_emigrants_by_age.csv\", header=True)"
   ],
   "metadata": {
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192558664,
     "user_tz": 180,
     "elapsed": 443,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "id": "b06QK9Ih5Oxm"
   },
   "execution_count": 127,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Deaths\n",
    "#### ********************************************************************************************\n"
   ],
   "metadata": {
    "id": "gre3L6Xq6qC0"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_deaths = input_dir + '/deaths.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "deaths = spark.read.csv(src_deaths, header=True, inferSchema=True)"
   ],
   "metadata": {
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192565165,
     "user_tz": 180,
     "elapsed": 467,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "id": "y9WAVzrJ6qDL"
   },
   "execution_count": 128,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "deaths.printSchema()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721190024476,
     "user_tz": 180,
     "elapsed": 533,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "395f524d-8a27-4918-92b5-3e2fa0d5f28f",
    "id": "hzX0jbOL6qDM"
   },
   "execution_count": 41,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "root\n",
      " |-- Year: integer (nullable = true)\n",
      " |-- District.Code: integer (nullable = true)\n",
      " |-- District.Name: string (nullable = true)\n",
      " |-- Neighborhood.Code: integer (nullable = true)\n",
      " |-- Neighborhood.Name: string (nullable = true)\n",
      " |-- Age: string (nullable = true)\n",
      " |-- Number: integer (nullable = true)\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "deaths.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721190031905,
     "user_tz": 180,
     "elapsed": 455,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "8ad5e441-ad23-44bf-d814-574cda0df2d8",
    "id": "6HrEke6G6qDM"
   },
   "execution_count": 42,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+--------------------+---+------+\n",
      "|Year|District.Code|District.Name|Neighborhood.Code|   Neighborhood.Name|Age|Number|\n",
      "+----+-------------+-------------+-----------------+--------------------+---+------+\n",
      "|2017|            1| Ciutat Vella|                1|            el Raval|0-4|     1|\n",
      "|2017|            1| Ciutat Vella|                2|      el Barri Gòtic|0-4|     1|\n",
      "|2017|            1| Ciutat Vella|                3|      la Barceloneta|0-4|     0|\n",
      "|2017|            1| Ciutat Vella|                4|Sant Pere, Santa ...|0-4|     0|\n",
      "|2017|            2|     Eixample|                5|       el Fort Pienc|0-4|     0|\n",
      "+----+-------------+-------------+-----------------+--------------------+---+------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(deaths.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721190035673,
     "user_tz": 180,
     "elapsed": 3,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "ef9fb289-46b8-4cbf-db23-cbd91ddd698c",
    "id": "K3gbG4e06qDM"
   },
   "execution_count": 43,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de columnas: 7\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = deaths.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721190041043,
     "user_tz": 180,
     "elapsed": 442,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "b51bd0cf-c901-436d-bc87-1e85698365d6",
    "id": "91fP07D16qDN"
   },
   "execution_count": 44,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Nombres de las columnas: ['Year', 'District.Code', 'District.Name', 'Neighborhood.Code', 'Neighborhood.Name', 'Age', 'Number']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "deaths.describe().show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721190046722,
     "user_tz": 180,
     "elapsed": 1627,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "ef92beb4-d849-41d5-8323-df2731306109",
    "id": "7-yMQwuP6qDN"
   },
   "execution_count": 45,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+------------------+------------------+-------------------+-----------------+-----------------+-----+------------------+\n",
      "|summary|              Year|     District.Code|      District.Name|Neighborhood.Code|Neighborhood.Name|  Age|            Number|\n",
      "+-------+------------------+------------------+-------------------+-----------------+-----------------+-----+------------------+\n",
      "|  count|              4599|              4599|               4599|             4599|             4599| 4599|              4599|\n",
      "|   mean|            2016.0|6.2465753424657535|               null|             37.0|             null| null|10.051098064796696|\n",
      "| stddev|0.8165853643324936| 2.788639085941505|               null|21.07359873688776|             null| null|18.559140929441728|\n",
      "|    min|              2015|                 1|       Ciutat Vella|                1|    Baró de Viver|  0-4|                 0|\n",
      "|    max|              2017|                10|Sarrià-Sant Gervasi|               73|  les Tres Torres|>=100|               139|\n",
      "+-------+------------------+------------------+-------------------+-----------------+-----------------+-----+------------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = deaths.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721190052845,
     "user_tz": 180,
     "elapsed": 426,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "28f4e897-8f39-4707-bb44-7b67b5fc343b",
    "id": "0GWCyfgz6qDN"
   },
   "execution_count": 46,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de registros: 4599\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Renombrar columnas\n",
    "deaths = deaths.withColumnRenamed(\"District.Code\", \"District_Code\") \\\n",
    "                       .withColumnRenamed(\"District.Name\", \"District_Name\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood.Code\", \"Neighborhood_Code\") \\\n",
    "                       .withColumnRenamed(\"Neighborhood.Name\", \"Neighborhood_Name\")\n",
    "\n",
    "# Verificar valores nulos\n",
    "deaths.select([count(when(col(c).isNull(), c)).alias(c) for c in deaths.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192580537,
     "user_tz": 180,
     "elapsed": 626,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "f3f85221-220b-4032-ae2b-47eb24ddea46",
    "id": "9te2mlFW6qDN"
   },
   "execution_count": 129,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-------------+-----------------+-----------------+---+------+\n",
      "|Year|District_Code|District_Name|Neighborhood_Code|Neighborhood_Name|Age|Number|\n",
      "+----+-------------+-------------+-----------------+-----------------+---+------+\n",
      "|   0|            0|            0|                0|                0|  0|     0|\n",
      "+----+-------------+-------------+-----------------+-----------------+---+------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = deaths.count() - deaths.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192585311,
     "user_tz": 180,
     "elapsed": 1113,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "73774ed5-5f4a-49fb-8660-99e6fdb33c26",
    "id": "M6a0lPYJ6qDO"
   },
   "execution_count": 130,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Year\", \"District_Code\", \"Neighborhood_Code\", \"Age\"]\n",
    "deaths.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192588499,
     "user_tz": 180,
     "elapsed": 1193,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "cbfe25c7-b95e-430f-a2ae-987b08641926",
    "id": "cAANkKo96qDO"
   },
   "execution_count": 131,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+-------------+-----------------+---+-----+\n",
      "|Year|District_Code|Neighborhood_Code|Age|count|\n",
      "+----+-------------+-----------------+---+-----+\n",
      "+----+-------------+-----------------+---+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "deaths.write.csv(output_dir + \"/cleaned_deaths.csv\", header=True)"
   ],
   "metadata": {
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192594748,
     "user_tz": 180,
     "elapsed": 453,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "id": "Buhv-Vor6qDP"
   },
   "execution_count": 132,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### ********************************************************************************************\n",
    "### Dataset: Gross income\n",
    "#### ********************************************************************************************\n"
   ],
   "metadata": {
    "id": "wB4AebzYAv-t"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "# Definir la ruta de origen\n",
    "src_gross_income = input_dir + '/2021_atles_renda_bruta_llar.csv'\n",
    "\n",
    "# Cargar las tablas de datos\n",
    "gross_income = spark.read.csv(src_gross_income, header=True, inferSchema=True)"
   ],
   "metadata": {
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192607631,
     "user_tz": 180,
     "elapsed": 621,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "id": "ENRnEUDfAv_G"
   },
   "execution_count": 133,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "# Mostrar el esquema (tipo de datos de cada columna)\n",
    "gross_income.printSchema()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191646158,
     "user_tz": 180,
     "elapsed": 454,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "85620535-8f51-44d7-e07e-c265fd3c49e8",
    "id": "tMK9Atl9Av_H"
   },
   "execution_count": 54,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "root\n",
      " |-- Any: integer (nullable = true)\n",
      " |-- Codi_Districte: integer (nullable = true)\n",
      " |-- Nom_Districte: string (nullable = true)\n",
      " |-- Codi_Barri: integer (nullable = true)\n",
      " |-- Nom_Barri: string (nullable = true)\n",
      " |-- Seccio_Censal: integer (nullable = true)\n",
      " |-- Import_Renda_Bruta_€: integer (nullable = true)\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Vista previa de las primeras filas\n",
    "gross_income.show(5)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191650535,
     "user_tz": 180,
     "elapsed": 466,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "2d70fa9d-37e8-4f8a-ca2f-3fb49120a0d6",
    "id": "5UlmdEiqAv_H"
   },
   "execution_count": 55,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+----+--------------+-------------+----------+---------+-------------+--------------------+\n",
      "| Any|Codi_Districte|Nom_Districte|Codi_Barri|Nom_Barri|Seccio_Censal|Import_Renda_Bruta_€|\n",
      "+----+--------------+-------------+----------+---------+-------------+--------------------+\n",
      "|2021|             1| Ciutat Vella|         1| el Raval|            1|               34839|\n",
      "|2021|             1| Ciutat Vella|         1| el Raval|            2|               27777|\n",
      "|2021|             1| Ciutat Vella|         1| el Raval|            3|               31082|\n",
      "|2021|             1| Ciutat Vella|         1| el Raval|            4|               34635|\n",
      "|2021|             1| Ciutat Vella|         1| el Raval|            5|               27950|\n",
      "+----+--------------+-------------+----------+---------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de columnas\n",
    "num_columns = len(gross_income.columns)\n",
    "print(f\"Cantidad de columnas: {num_columns}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191659265,
     "user_tz": 180,
     "elapsed": 451,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "5ee15928-72d8-400b-983c-38f43053c9ca",
    "id": "GPfBKd5pAv_H"
   },
   "execution_count": 56,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de columnas: 7\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Nombre de cada columna\n",
    "column_names = gross_income.columns\n",
    "print(f\"Nombres de las columnas: {column_names}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191666616,
     "user_tz": 180,
     "elapsed": 461,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "b162c5b2-e1b9-4f4b-a674-0fe8cbebfe28",
    "id": "c1rL9CcCAv_H"
   },
   "execution_count": 57,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Nombres de las columnas: ['Any', 'Codi_Districte', 'Nom_Districte', 'Codi_Barri', 'Nom_Barri', 'Seccio_Censal', 'Import_Renda_Bruta_€']\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Descripción de los datos de cada columna\n",
    "gross_income.describe().show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191671946,
     "user_tz": 180,
     "elapsed": 1543,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "fcb0b20e-e3be-4e98-b69b-9a332eccf090",
    "id": "HWF3DGmvAv_I"
   },
   "execution_count": 58,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+-------+------+------------------+-------------------+-----------------+---------------+-----------------+--------------------+\n",
      "|summary|   Any|    Codi_Districte|      Nom_Districte|       Codi_Barri|      Nom_Barri|    Seccio_Censal|Import_Renda_Bruta_€|\n",
      "+-------+------+------------------+-------------------+-----------------+---------------+-----------------+--------------------+\n",
      "|  count|  1068|              1068|               1068|             1068|           1068|             1068|                1068|\n",
      "|   mean|2021.0|  5.72378277153558|               null|33.24625468164794|           null|60.64513108614232|   53840.52808988764|\n",
      "| stddev|   0.0|2.9385324132584225|               null|21.89298833329705|           null|41.56954070940254|   20741.88373398768|\n",
      "|    min|  2021|                 1|       Ciutat Vella|                1|  Baró de Viver|                1|               25218|\n",
      "|    max|  2021|                10|Sarrià-Sant Gervasi|               73|les Tres Torres|              237|              131719|\n",
      "+-------+------+------------------+-------------------+-----------------+---------------+-----------------+--------------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Cantidad de registros (número de filas)\n",
    "num_records = gross_income.count()\n",
    "print(f\"Cantidad de registros: {num_records}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721191676852,
     "user_tz": 180,
     "elapsed": 435,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "190cceda-3975-4117-d6be-58afd1baae78",
    "id": "cPpW7ndUAv_I"
   },
   "execution_count": 59,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Cantidad de registros: 1068\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar valores nulos\n",
    "gross_income.select([count(when(col(c).isNull(), c)).alias(c) for c in gross_income.columns]).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192624900,
     "user_tz": 180,
     "elapsed": 472,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "b909b184-8d29-4b9e-c50f-6923a3b21239",
    "id": "AhTs7P8cAv_I"
   },
   "execution_count": 134,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+---+--------------+-------------+----------+---------+-------------+--------------------+\n",
      "|Any|Codi_Districte|Nom_Districte|Codi_Barri|Nom_Barri|Seccio_Censal|Import_Renda_Bruta_€|\n",
      "+---+--------------+-------------+----------+---------+-------------+--------------------+\n",
      "|  0|             0|            0|         0|        0|            0|                   0|\n",
      "+---+--------------+-------------+----------+---------+-------------+--------------------+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar registros duplicados\n",
    "num_duplicates = gross_income.count() - gross_income.dropDuplicates().count()\n",
    "print(f\"Número de registros duplicados: {num_duplicates}\")"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192629347,
     "user_tz": 180,
     "elapsed": 1198,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "d3b40811-f44d-48b8-a6ac-c8f21317675f",
    "id": "0BcRO6NZAv_J"
   },
   "execution_count": 135,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Número de registros duplicados: 0\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Verificar si las claves primarias son únicas\n",
    "primary_key_columns = [\"Any\", \"Codi_Districte\", \"Codi_Barri\", \"Seccio_Censal\"]\n",
    "gross_income.groupBy(primary_key_columns).count().filter(col(\"count\") > 1).show()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192633906,
     "user_tz": 180,
     "elapsed": 1092,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "outputId": "9c7ac554-b333-4f07-d559-fc5b228a4351",
    "id": "38sDtJa-Av_K"
   },
   "execution_count": 136,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "+---+--------------+----------+-------------+-----+\n",
      "|Any|Codi_Districte|Codi_Barri|Seccio_Censal|count|\n",
      "+---+--------------+----------+-------------+-----+\n",
      "+---+--------------+----------+-------------+-----+\n",
      "\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "# Guardar los datos limpios en un nuevo archivo\n",
    "gross_income.write.csv(output_dir + \"/cleaned_gross_income.csv\", header=True)"
   ],
   "metadata": {
    "executionInfo": {
     "status": "ok",
     "timestamp": 1721192641794,
     "user_tz": 180,
     "elapsed": 974,
     "user": {
      "displayName": "Gerónimo de Lisa",
      "userId": "11364786851394057831"
     }
    },
    "id": "VyY_rjUrAv_K"
   },
   "execution_count": 137,
   "outputs": []
  }
 ]
}