13_ntb_PySpark_Pivot.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyOSzypQECiRkIKvGG3MSb1u",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/luasampaio/data-engineering/blob/main/13_ntb_PySpark_Pivot.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "Lfa6-iIdm3Tt"
      },
      "outputs": [],
      "source": [
        "from pyspark.sql import SparkSession\n",
        "import pyspark .sql.functions as F"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Mount Google Drive\n",
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "41mNN7Kuo2IK",
        "outputId": "c9c15df9-0013-4af7-d428-a768a3fddcfe"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "spark = (\n",
        "    SparkSession.builder.appName('PySpark - LucianaSampaio')\n",
        "    .config('spark.sql.repl.eagerEval.enabled', True)\n",
        "    .getOrCreate()\n",
        ")"
      ],
      "metadata": {
        "id": "o2mVOKksm5NH"
      },
      "execution_count": 8,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df = spark.read.csv('/content/drive/MyDrive/.Dataset/test.csv', sep=',', encoding='UTF-8', header=True, inferSchema=True)\n",
        "df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 529
        },
        "id": "aiAu2NWyoieU",
        "outputId": "c836dc24-9096-42b1-c4cc-3d3b8b8965f2"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "+-----------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+\n",
              "|PassengerId|Pclass|                Name|   Sex| Age|SibSp|Parch|          Ticket|   Fare|Cabin|Embarked|\n",
              "+-----------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+\n",
              "|        892|     3|    Kelly, Mr. James|  male|34.5|    0|    0|          330911| 7.8292| NULL|       Q|\n",
              "|        893|     3|Wilkes, Mrs. Jame...|female|47.0|    1|    0|          363272|    7.0| NULL|       S|\n",
              "|        894|     2|Myles, Mr. Thomas...|  male|62.0|    0|    0|          240276| 9.6875| NULL|       Q|\n",
              "|        895|     3|    Wirz, Mr. Albert|  male|27.0|    0|    0|          315154| 8.6625| NULL|       S|\n",
              "|        896|     3|Hirvonen, Mrs. Al...|female|22.0|    1|    1|         3101298|12.2875| NULL|       S|\n",
              "|        897|     3|Svensson, Mr. Joh...|  male|14.0|    0|    0|            7538|  9.225| NULL|       S|\n",
              "|        898|     3|Connolly, Miss. Kate|female|30.0|    0|    0|          330972| 7.6292| NULL|       Q|\n",
              "|        899|     2|Caldwell, Mr. Alb...|  male|26.0|    1|    1|          248738|   29.0| NULL|       S|\n",
              "|        900|     3|Abrahim, Mrs. Jos...|female|18.0|    0|    0|            2657| 7.2292| NULL|       C|\n",
              "|        901|     3|Davies, Mr. John ...|  male|21.0|    2|    0|       A/4 48871|  24.15| NULL|       S|\n",
              "|        902|     3|    Ilieff, Mr. Ylio|  male|NULL|    0|    0|          349220| 7.8958| NULL|       S|\n",
              "|        903|     1|Jones, Mr. Charle...|  male|46.0|    0|    0|             694|   26.0| NULL|       S|\n",
              "|        904|     1|Snyder, Mrs. John...|female|23.0|    1|    0|           21228|82.2667|  B45|       S|\n",
              "|        905|     2|Howard, Mr. Benjamin|  male|63.0|    1|    0|           24065|   26.0| NULL|       S|\n",
              "|        906|     1|Chaffee, Mrs. Her...|female|47.0|    1|    0|     W.E.P. 5734| 61.175|  E31|       S|\n",
              "|        907|     2|del Carlo, Mrs. S...|female|24.0|    1|    0|   SC/PARIS 2167|27.7208| NULL|       C|\n",
              "|        908|     2|   Keane, Mr. Daniel|  male|35.0|    0|    0|          233734|  12.35| NULL|       Q|\n",
              "|        909|     3|   Assaf, Mr. Gerios|  male|21.0|    0|    0|            2692|  7.225| NULL|       C|\n",
              "|        910|     3|Ilmakangas, Miss....|female|27.0|    1|    0|STON/O2. 3101270|  7.925| NULL|       S|\n",
              "|        911|     3|\"Assaf Khalil, Mr...|female|45.0|    0|    0|            2696|  7.225| NULL|       C|\n",
              "+-----------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+\n",
              "only showing top 20 rows"
            ],
            "text/html": [
              "<table border='1'>\n",
              "<tr><th>PassengerId</th><th>Pclass</th><th>Name</th><th>Sex</th><th>Age</th><th>SibSp</th><th>Parch</th><th>Ticket</th><th>Fare</th><th>Cabin</th><th>Embarked</th></tr>\n",
              "<tr><td>892</td><td>3</td><td>Kelly, Mr. James</td><td>male</td><td>34.5</td><td>0</td><td>0</td><td>330911</td><td>7.8292</td><td>NULL</td><td>Q</td></tr>\n",
              "<tr><td>893</td><td>3</td><td>Wilkes, Mrs. Jame...</td><td>female</td><td>47.0</td><td>1</td><td>0</td><td>363272</td><td>7.0</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>894</td><td>2</td><td>Myles, Mr. Thomas...</td><td>male</td><td>62.0</td><td>0</td><td>0</td><td>240276</td><td>9.6875</td><td>NULL</td><td>Q</td></tr>\n",
              "<tr><td>895</td><td>3</td><td>Wirz, Mr. Albert</td><td>male</td><td>27.0</td><td>0</td><td>0</td><td>315154</td><td>8.6625</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>896</td><td>3</td><td>Hirvonen, Mrs. Al...</td><td>female</td><td>22.0</td><td>1</td><td>1</td><td>3101298</td><td>12.2875</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>897</td><td>3</td><td>Svensson, Mr. Joh...</td><td>male</td><td>14.0</td><td>0</td><td>0</td><td>7538</td><td>9.225</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>898</td><td>3</td><td>Connolly, Miss. Kate</td><td>female</td><td>30.0</td><td>0</td><td>0</td><td>330972</td><td>7.6292</td><td>NULL</td><td>Q</td></tr>\n",
              "<tr><td>899</td><td>2</td><td>Caldwell, Mr. Alb...</td><td>male</td><td>26.0</td><td>1</td><td>1</td><td>248738</td><td>29.0</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>900</td><td>3</td><td>Abrahim, Mrs. Jos...</td><td>female</td><td>18.0</td><td>0</td><td>0</td><td>2657</td><td>7.2292</td><td>NULL</td><td>C</td></tr>\n",
              "<tr><td>901</td><td>3</td><td>Davies, Mr. John ...</td><td>male</td><td>21.0</td><td>2</td><td>0</td><td>A/4 48871</td><td>24.15</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>902</td><td>3</td><td>Ilieff, Mr. Ylio</td><td>male</td><td>NULL</td><td>0</td><td>0</td><td>349220</td><td>7.8958</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>903</td><td>1</td><td>Jones, Mr. Charle...</td><td>male</td><td>46.0</td><td>0</td><td>0</td><td>694</td><td>26.0</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>904</td><td>1</td><td>Snyder, Mrs. John...</td><td>female</td><td>23.0</td><td>1</td><td>0</td><td>21228</td><td>82.2667</td><td>B45</td><td>S</td></tr>\n",
              "<tr><td>905</td><td>2</td><td>Howard, Mr. Benjamin</td><td>male</td><td>63.0</td><td>1</td><td>0</td><td>24065</td><td>26.0</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>906</td><td>1</td><td>Chaffee, Mrs. Her...</td><td>female</td><td>47.0</td><td>1</td><td>0</td><td>W.E.P. 5734</td><td>61.175</td><td>E31</td><td>S</td></tr>\n",
              "<tr><td>907</td><td>2</td><td>del Carlo, Mrs. S...</td><td>female</td><td>24.0</td><td>1</td><td>0</td><td>SC/PARIS 2167</td><td>27.7208</td><td>NULL</td><td>C</td></tr>\n",
              "<tr><td>908</td><td>2</td><td>Keane, Mr. Daniel</td><td>male</td><td>35.0</td><td>0</td><td>0</td><td>233734</td><td>12.35</td><td>NULL</td><td>Q</td></tr>\n",
              "<tr><td>909</td><td>3</td><td>Assaf, Mr. Gerios</td><td>male</td><td>21.0</td><td>0</td><td>0</td><td>2692</td><td>7.225</td><td>NULL</td><td>C</td></tr>\n",
              "<tr><td>910</td><td>3</td><td>Ilmakangas, Miss....</td><td>female</td><td>27.0</td><td>1</td><td>0</td><td>STON/O2. 3101270</td><td>7.925</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>911</td><td>3</td><td>&quot;Assaf Khalil, Mr...</td><td>female</td><td>45.0</td><td>0</td><td>0</td><td>2696</td><td>7.225</td><td>NULL</td><td>C</td></tr>\n",
              "</table>\n",
              "only showing top 20 rows\n"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JtgDo0RspJaT",
        "outputId": "4095c9bd-9c77-4c91-ef06-169d2b050750"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- PassengerId: integer (nullable = true)\n",
            " |-- Pclass: integer (nullable = true)\n",
            " |-- Name: string (nullable = true)\n",
            " |-- Sex: string (nullable = true)\n",
            " |-- Age: double (nullable = true)\n",
            " |-- SibSp: integer (nullable = true)\n",
            " |-- Parch: integer (nullable = true)\n",
            " |-- Ticket: string (nullable = true)\n",
            " |-- Fare: double (nullable = true)\n",
            " |-- Cabin: string (nullable = true)\n",
            " |-- Embarked: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "TSrROy1xpNOF",
        "outputId": "c818e474-a5e0-4533-9cc3-293d73bce06c"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+\n",
            "|PassengerId|Pclass|                Name|   Sex| Age|SibSp|Parch|          Ticket|   Fare|Cabin|Embarked|\n",
            "+-----------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+\n",
            "|        892|     3|    Kelly, Mr. James|  male|34.5|    0|    0|          330911| 7.8292| NULL|       Q|\n",
            "|        893|     3|Wilkes, Mrs. Jame...|female|47.0|    1|    0|          363272|    7.0| NULL|       S|\n",
            "|        894|     2|Myles, Mr. Thomas...|  male|62.0|    0|    0|          240276| 9.6875| NULL|       Q|\n",
            "|        895|     3|    Wirz, Mr. Albert|  male|27.0|    0|    0|          315154| 8.6625| NULL|       S|\n",
            "|        896|     3|Hirvonen, Mrs. Al...|female|22.0|    1|    1|         3101298|12.2875| NULL|       S|\n",
            "|        897|     3|Svensson, Mr. Joh...|  male|14.0|    0|    0|            7538|  9.225| NULL|       S|\n",
            "|        898|     3|Connolly, Miss. Kate|female|30.0|    0|    0|          330972| 7.6292| NULL|       Q|\n",
            "|        899|     2|Caldwell, Mr. Alb...|  male|26.0|    1|    1|          248738|   29.0| NULL|       S|\n",
            "|        900|     3|Abrahim, Mrs. Jos...|female|18.0|    0|    0|            2657| 7.2292| NULL|       C|\n",
            "|        901|     3|Davies, Mr. John ...|  male|21.0|    2|    0|       A/4 48871|  24.15| NULL|       S|\n",
            "|        902|     3|    Ilieff, Mr. Ylio|  male|NULL|    0|    0|          349220| 7.8958| NULL|       S|\n",
            "|        903|     1|Jones, Mr. Charle...|  male|46.0|    0|    0|             694|   26.0| NULL|       S|\n",
            "|        904|     1|Snyder, Mrs. John...|female|23.0|    1|    0|           21228|82.2667|  B45|       S|\n",
            "|        905|     2|Howard, Mr. Benjamin|  male|63.0|    1|    0|           24065|   26.0| NULL|       S|\n",
            "|        906|     1|Chaffee, Mrs. Her...|female|47.0|    1|    0|     W.E.P. 5734| 61.175|  E31|       S|\n",
            "|        907|     2|del Carlo, Mrs. S...|female|24.0|    1|    0|   SC/PARIS 2167|27.7208| NULL|       C|\n",
            "|        908|     2|   Keane, Mr. Daniel|  male|35.0|    0|    0|          233734|  12.35| NULL|       Q|\n",
            "|        909|     3|   Assaf, Mr. Gerios|  male|21.0|    0|    0|            2692|  7.225| NULL|       C|\n",
            "|        910|     3|Ilmakangas, Miss....|female|27.0|    1|    0|STON/O2. 3101270|  7.925| NULL|       S|\n",
            "|        911|     3|\"Assaf Khalil, Mr...|female|45.0|    0|    0|            2696|  7.225| NULL|       C|\n",
            "+-----------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "display(df)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 529
        },
        "id": "Puk7WbUvpVSe",
        "outputId": "860e66a7-ed18-423c-b27f-20dc051589d0"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "DataFrame[PassengerId: int, Pclass: int, Name: string, Sex: string, Age: double, SibSp: int, Parch: int, Ticket: string, Fare: double, Cabin: string, Embarked: string]"
            ],
            "text/html": [
              "<table border='1'>\n",
              "<tr><th>PassengerId</th><th>Pclass</th><th>Name</th><th>Sex</th><th>Age</th><th>SibSp</th><th>Parch</th><th>Ticket</th><th>Fare</th><th>Cabin</th><th>Embarked</th></tr>\n",
              "<tr><td>892</td><td>3</td><td>Kelly, Mr. James</td><td>male</td><td>34.5</td><td>0</td><td>0</td><td>330911</td><td>7.8292</td><td>NULL</td><td>Q</td></tr>\n",
              "<tr><td>893</td><td>3</td><td>Wilkes, Mrs. Jame...</td><td>female</td><td>47.0</td><td>1</td><td>0</td><td>363272</td><td>7.0</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>894</td><td>2</td><td>Myles, Mr. Thomas...</td><td>male</td><td>62.0</td><td>0</td><td>0</td><td>240276</td><td>9.6875</td><td>NULL</td><td>Q</td></tr>\n",
              "<tr><td>895</td><td>3</td><td>Wirz, Mr. Albert</td><td>male</td><td>27.0</td><td>0</td><td>0</td><td>315154</td><td>8.6625</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>896</td><td>3</td><td>Hirvonen, Mrs. Al...</td><td>female</td><td>22.0</td><td>1</td><td>1</td><td>3101298</td><td>12.2875</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>897</td><td>3</td><td>Svensson, Mr. Joh...</td><td>male</td><td>14.0</td><td>0</td><td>0</td><td>7538</td><td>9.225</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>898</td><td>3</td><td>Connolly, Miss. Kate</td><td>female</td><td>30.0</td><td>0</td><td>0</td><td>330972</td><td>7.6292</td><td>NULL</td><td>Q</td></tr>\n",
              "<tr><td>899</td><td>2</td><td>Caldwell, Mr. Alb...</td><td>male</td><td>26.0</td><td>1</td><td>1</td><td>248738</td><td>29.0</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>900</td><td>3</td><td>Abrahim, Mrs. Jos...</td><td>female</td><td>18.0</td><td>0</td><td>0</td><td>2657</td><td>7.2292</td><td>NULL</td><td>C</td></tr>\n",
              "<tr><td>901</td><td>3</td><td>Davies, Mr. John ...</td><td>male</td><td>21.0</td><td>2</td><td>0</td><td>A/4 48871</td><td>24.15</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>902</td><td>3</td><td>Ilieff, Mr. Ylio</td><td>male</td><td>NULL</td><td>0</td><td>0</td><td>349220</td><td>7.8958</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>903</td><td>1</td><td>Jones, Mr. Charle...</td><td>male</td><td>46.0</td><td>0</td><td>0</td><td>694</td><td>26.0</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>904</td><td>1</td><td>Snyder, Mrs. John...</td><td>female</td><td>23.0</td><td>1</td><td>0</td><td>21228</td><td>82.2667</td><td>B45</td><td>S</td></tr>\n",
              "<tr><td>905</td><td>2</td><td>Howard, Mr. Benjamin</td><td>male</td><td>63.0</td><td>1</td><td>0</td><td>24065</td><td>26.0</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>906</td><td>1</td><td>Chaffee, Mrs. Her...</td><td>female</td><td>47.0</td><td>1</td><td>0</td><td>W.E.P. 5734</td><td>61.175</td><td>E31</td><td>S</td></tr>\n",
              "<tr><td>907</td><td>2</td><td>del Carlo, Mrs. S...</td><td>female</td><td>24.0</td><td>1</td><td>0</td><td>SC/PARIS 2167</td><td>27.7208</td><td>NULL</td><td>C</td></tr>\n",
              "<tr><td>908</td><td>2</td><td>Keane, Mr. Daniel</td><td>male</td><td>35.0</td><td>0</td><td>0</td><td>233734</td><td>12.35</td><td>NULL</td><td>Q</td></tr>\n",
              "<tr><td>909</td><td>3</td><td>Assaf, Mr. Gerios</td><td>male</td><td>21.0</td><td>0</td><td>0</td><td>2692</td><td>7.225</td><td>NULL</td><td>C</td></tr>\n",
              "<tr><td>910</td><td>3</td><td>Ilmakangas, Miss....</td><td>female</td><td>27.0</td><td>1</td><td>0</td><td>STON/O2. 3101270</td><td>7.925</td><td>NULL</td><td>S</td></tr>\n",
              "<tr><td>911</td><td>3</td><td>&quot;Assaf Khalil, Mr...</td><td>female</td><td>45.0</td><td>0</td><td>0</td><td>2696</td><td>7.225</td><td>NULL</td><td>C</td></tr>\n",
              "</table>\n",
              "only showing top 20 rows\n"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Convertendo para int"
      ],
      "metadata": {
        "id": "Ue2gbLGvqDqk"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import functions as F\n",
        "\n",
        "df = (\n",
        "    df\n",
        "    .withColumn('Age', F.col('Age').cast('int'))  # Converte a coluna 'Age' para o tipo inteiro\n",
        ")\n",
        "\n",
        "# Exibindo o esquema para verificar o tipo atualizado\n",
        "df.printSchema()\n",
        "\n",
        "# Mostrando as primeiras linhas do DataFrame\n",
        "df.show()\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ErzhL921pcCX",
        "outputId": "8da93eb1-224b-4a51-963a-2926dba861df"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- PassengerId: integer (nullable = true)\n",
            " |-- Pclass: integer (nullable = true)\n",
            " |-- Name: string (nullable = true)\n",
            " |-- Sex: string (nullable = true)\n",
            " |-- Age: integer (nullable = true)\n",
            " |-- SibSp: integer (nullable = true)\n",
            " |-- Parch: integer (nullable = true)\n",
            " |-- Ticket: string (nullable = true)\n",
            " |-- Fare: double (nullable = true)\n",
            " |-- Cabin: string (nullable = true)\n",
            " |-- Embarked: string (nullable = true)\n",
            "\n",
            "+-----------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+\n",
            "|PassengerId|Pclass|                Name|   Sex| Age|SibSp|Parch|          Ticket|   Fare|Cabin|Embarked|\n",
            "+-----------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+\n",
            "|        892|     3|    Kelly, Mr. James|  male|  34|    0|    0|          330911| 7.8292| NULL|       Q|\n",
            "|        893|     3|Wilkes, Mrs. Jame...|female|  47|    1|    0|          363272|    7.0| NULL|       S|\n",
            "|        894|     2|Myles, Mr. Thomas...|  male|  62|    0|    0|          240276| 9.6875| NULL|       Q|\n",
            "|        895|     3|    Wirz, Mr. Albert|  male|  27|    0|    0|          315154| 8.6625| NULL|       S|\n",
            "|        896|     3|Hirvonen, Mrs. Al...|female|  22|    1|    1|         3101298|12.2875| NULL|       S|\n",
            "|        897|     3|Svensson, Mr. Joh...|  male|  14|    0|    0|            7538|  9.225| NULL|       S|\n",
            "|        898|     3|Connolly, Miss. Kate|female|  30|    0|    0|          330972| 7.6292| NULL|       Q|\n",
            "|        899|     2|Caldwell, Mr. Alb...|  male|  26|    1|    1|          248738|   29.0| NULL|       S|\n",
            "|        900|     3|Abrahim, Mrs. Jos...|female|  18|    0|    0|            2657| 7.2292| NULL|       C|\n",
            "|        901|     3|Davies, Mr. John ...|  male|  21|    2|    0|       A/4 48871|  24.15| NULL|       S|\n",
            "|        902|     3|    Ilieff, Mr. Ylio|  male|NULL|    0|    0|          349220| 7.8958| NULL|       S|\n",
            "|        903|     1|Jones, Mr. Charle...|  male|  46|    0|    0|             694|   26.0| NULL|       S|\n",
            "|        904|     1|Snyder, Mrs. John...|female|  23|    1|    0|           21228|82.2667|  B45|       S|\n",
            "|        905|     2|Howard, Mr. Benjamin|  male|  63|    1|    0|           24065|   26.0| NULL|       S|\n",
            "|        906|     1|Chaffee, Mrs. Her...|female|  47|    1|    0|     W.E.P. 5734| 61.175|  E31|       S|\n",
            "|        907|     2|del Carlo, Mrs. S...|female|  24|    1|    0|   SC/PARIS 2167|27.7208| NULL|       C|\n",
            "|        908|     2|   Keane, Mr. Daniel|  male|  35|    0|    0|          233734|  12.35| NULL|       Q|\n",
            "|        909|     3|   Assaf, Mr. Gerios|  male|  21|    0|    0|            2692|  7.225| NULL|       C|\n",
            "|        910|     3|Ilmakangas, Miss....|female|  27|    1|    0|STON/O2. 3101270|  7.925| NULL|       S|\n",
            "|        911|     3|\"Assaf Khalil, Mr...|female|  45|    0|    0|            2696|  7.225| NULL|       C|\n",
            "+-----------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import functions as F\n",
        "\n",
        "# Agrupa os dados pela coluna 'Pclass' e conta os registros em cada grupo\n",
        "result = (\n",
        "    df\n",
        "    .groupBy('Pclass')  # Agrupa os dados pela coluna 'Pclass'\n",
        "    .agg(F.count('*').alias('Count'))  # Conta os registros em cada grupo\n",
        ")\n",
        "\n",
        "# Exibe o resultado\n",
        "result.show()\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "zlhiaGoMqBw0",
        "outputId": "e1a7f2c0-1ff3-4e0f-ac36-6294fc46404f"
      },
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+-----+\n",
            "|Pclass|Count|\n",
            "+------+-----+\n",
            "|     1|  107|\n",
            "|     3|  218|\n",
            "|     2|   93|\n",
            "+------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import functions as F\n",
        "\n",
        "# Agrupa os dados pela coluna 'sex' e conta os registros em cada grupo\n",
        "sex = (\n",
        "    df\n",
        "    .groupBy('sex')  # Agrupa os dados pela coluna 'Pclass'\n",
        "    .agg(F.count('*').alias('Count'))  # Conta os registros em cada grupo\n",
        ")\n",
        "\n",
        "# Exibe o resultado\n",
        "sex.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GwgsFv1wrQ1y",
        "outputId": "58b31686-b600-4795-a604-9c769b648a3e"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+-----+\n",
            "|   sex|Count|\n",
            "+------+-----+\n",
            "|female|  152|\n",
            "|  male|  266|\n",
            "+------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import functions as F\n",
        "\n",
        "# Pivotando os dados para contar o número de registros por 'sex'\n",
        "pivot_df = (\n",
        "    df\n",
        "    .groupBy('sex')\n",
        "    .pivot('sex')\n",
        "    .agg(F.count('*'))\n",
        ")\n",
        "\n",
        "# Exibe o resultado\n",
        "pivot_df.show()\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "l3z5Mj0LsKWb",
        "outputId": "8f00c68a-4666-45c9-8ca2-62b4c7efaf37"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------+------+----+\n",
            "|   sex|female|male|\n",
            "+------+------+----+\n",
            "|female|   152|NULL|\n",
            "|  male|  NULL| 266|\n",
            "+------+------+----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import functions as F\n",
        "\n",
        "# Pivotando os dados para contar o número de registros por 'sex'\n",
        "df2 = (\n",
        "    df\n",
        "    .groupBy('sex')\n",
        "    .unpivot('sex')\n",
        "    .agg(F.count('*'))\n",
        ")\n",
        "\n",
        "# Exibe o resultado\n",
        "pivot_df.show()\n"
      ],
      "metadata": {
        "id": "W1RczS5zst4v"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}