31_ntb_PySpark_Functions.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyNjxC8pRrXxN+0F2QfthOnY",
      "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/31_ntb_PySpark_Functions.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "- Em andamento análise\n",
        "- Luciana Sampaio Mayer\n",
        "- 27.01.25\n"
      ],
      "metadata": {
        "id": "dibYcjmd3ZQB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install unidecode"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8OyWU6KrgInA",
        "outputId": "57391461-3d03-40f5-d5af-3a6e7e9e40d3"
      },
      "execution_count": 37,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting unidecode\n",
            "  Downloading Unidecode-1.3.8-py3-none-any.whl.metadata (13 kB)\n",
            "Downloading Unidecode-1.3.8-py3-none-any.whl (235 kB)\n",
            "\u001b[?25l   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m0.0/235.5 kB\u001b[0m \u001b[31m?\u001b[0m eta \u001b[36m-:--:--\u001b[0m\r\u001b[2K   \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[91m╸\u001b[0m\u001b[90m━━━━━\u001b[0m \u001b[32m204.8/235.5 kB\u001b[0m \u001b[31m6.0 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K   \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m235.5/235.5 kB\u001b[0m \u001b[31m4.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hInstalling collected packages: unidecode\n",
            "Successfully installed unidecode-1.3.8\n"
          ]
        }
      ]
    },
    {
      "source": [
        "from pyspark.sql import SparkSession\n",
        "from unidecode import unidecode\n",
        "import requests\n",
        "import pandas as pd\n",
        "import io\n",
        "import warnings\n",
        "warnings.filterwarnings(\"ignore\")\n",
        "\n",
        "# Cria uma SparkSession\n",
        "spark = SparkSession.builder.appName(\"diabetes\").getOrCreate()\n",
        "\n",
        "# Link direto para download do arquivo - Changed to raw content URL\n",
        "url = 'https://github.com/luasampaio/ModeloPredi-oDiabetes/raw/main/diabetes.csv'\n",
        "\n",
        "# Download the CSV file using requests\n",
        "response = requests.get(url)\n",
        "response.raise_for_status()  # Raise an exception for bad responses\n",
        "\n",
        "# Read the CSV data into a Pandas DataFrame\n",
        "diabetes = pd.read_csv(io.StringIO(response.text), sep=',')\n"
      ],
      "cell_type": "code",
      "metadata": {
        "id": "Zu0-mk2Knl--"
      },
      "execution_count": 38,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Convert the Pandas DataFrame to a Spark DataFrame\n",
        "df = spark.createDataFrame(diabetes)"
      ],
      "metadata": {
        "id": "BY4qraSE27zb"
      },
      "execution_count": 39,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print(type(df))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0fbbfpnSlM7d",
        "outputId": "472fea86-b304-4dcb-a4b1-64c596fea5a8"
      },
      "execution_count": 40,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pyspark.sql.dataframe.DataFrame'>\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.show(4)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KcBC9v33n-B6",
        "outputId": "0f616d07-81f9-407d-b9ac-6021ee3499e1"
      },
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+\n",
            "|Pregnancies|Glucose|BloodPressure|SkinThickness|Insulin| BMI|DiabetesPedigreeFunction|Age|Outcome|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+\n",
            "|          6|    148|           72|           35|      0|33.6|                   0.627| 50|      1|\n",
            "|          1|     85|           66|           29|      0|26.6|                   0.351| 31|      0|\n",
            "|          8|    183|           64|            0|      0|23.3|                   0.672| 32|      1|\n",
            "|          1|     89|           66|           23|     94|28.1|                   0.167| 21|      0|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+\n",
            "only showing top 4 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GeKpgQmCk_Ce",
        "outputId": "e254b18e-fa94-4cdb-9c39-696aa6d9f786"
      },
      "execution_count": 42,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- Pregnancies: long (nullable = true)\n",
            " |-- Glucose: long (nullable = true)\n",
            " |-- BloodPressure: long (nullable = true)\n",
            " |-- SkinThickness: long (nullable = true)\n",
            " |-- Insulin: long (nullable = true)\n",
            " |-- BMI: double (nullable = true)\n",
            " |-- DiabetesPedigreeFunction: double (nullable = true)\n",
            " |-- Age: long (nullable = true)\n",
            " |-- Outcome: long (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Columns of dataframe\n",
        "df.columns"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vQOv9p1QlZj3",
        "outputId": "fa74d8bd-0ecc-49f5-d1e3-0ae4caacf755"
      },
      "execution_count": 43,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Pregnancies',\n",
              " 'Glucose',\n",
              " 'BloodPressure',\n",
              " 'SkinThickness',\n",
              " 'Insulin',\n",
              " 'BMI',\n",
              " 'DiabetesPedigreeFunction',\n",
              " 'Age',\n",
              " 'Outcome']"
            ]
          },
          "metadata": {},
          "execution_count": 43
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Volumetria"
      ],
      "metadata": {
        "id": "Yt5XAtcDgpcw"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "num_rows = df.count()\n",
        "num_columns = len(df.columns)\n",
        "(num_rows, num_columns)\n",
        "print(f\"O DataFrame tem {num_rows} linhas e {num_columns} colunas.\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qm8kac3QleBF",
        "outputId": "2be947ab-7d25-4311-9346-6d1a3acf3147"
      },
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "O DataFrame tem 768 linhas e 9 colunas.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Function to remove accents\n",
        "def remover_acentos(texto):\n",
        "    return unidecode(texto) if texto else None"
      ],
      "metadata": {
        "id": "OZsSGyGOgB73"
      },
      "execution_count": 45,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Distribuição Estatística\n"
      ],
      "metadata": {
        "id": "0vNqSuSYoHlO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# description statistics\n",
        "df.describe().show(truncate=8)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "L38PdmBNlhHP",
        "outputId": "b91a6ed6-4e72-4c38-d5ef-1042971b0ccc"
      },
      "execution_count": 46,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+-----------+--------+-------------+-------------+--------+--------+------------------------+--------+--------+\n",
            "|summary|Pregnancies| Glucose|BloodPressure|SkinThickness| Insulin|     BMI|DiabetesPedigreeFunction|     Age| Outcome|\n",
            "+-------+-----------+--------+-------------+-------------+--------+--------+------------------------+--------+--------+\n",
            "|  count|        768|     768|          768|          768|     768|     768|                     768|     768|     768|\n",
            "|   mean|   3.845...|120.8...|     69.10...|     20.53...|79.79...|31.99...|                0.471...|33.24...|0.348...|\n",
            "| stddev|   3.369...|31.97...|     19.35...|     15.95...|115.2...|7.884...|                0.331...|11.76...|0.476...|\n",
            "|    min|          0|       0|            0|            0|       0|     0.0|                   0.078|      21|       0|\n",
            "|    max|         17|     199|          122|           99|     846|    67.1|                    2.42|      81|       1|\n",
            "+-------+-----------+--------+-------------+-------------+--------+--------+------------------------+--------+--------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Filtrando"
      ],
      "metadata": {
        "id": "EqB3O6sSl3Bx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Selecionando somente duas colunas\n",
        "df.select(\"Pregnancies\", \"Age\").show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PCNQHNOAllVS",
        "outputId": "90fcc700-8876-4709-c9e2-36ea95e3761d"
      },
      "execution_count": 47,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+---+\n",
            "|Pregnancies|Age|\n",
            "+-----------+---+\n",
            "|          6| 50|\n",
            "|          1| 31|\n",
            "|          8| 32|\n",
            "|          1| 21|\n",
            "|          0| 33|\n",
            "|          5| 30|\n",
            "|          3| 26|\n",
            "|         10| 29|\n",
            "|          2| 53|\n",
            "|          8| 54|\n",
            "+-----------+---+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "source": [
        "from pyspark.sql.functions import current_date\n",
        "# Add current date column\n",
        "df = df.withColumn(\"date\", current_date())"
      ],
      "cell_type": "code",
      "metadata": {
        "id": "r3FvX5oLmI86"
      },
      "execution_count": 48,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KWiQY1xemOD9",
        "outputId": "1fe946ce-45a7-4caa-f21f-f41bbcad84e6"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "|Pregnancies|Glucose|BloodPressure|SkinThickness|Insulin| BMI|DiabetesPedigreeFunction|Age|Outcome|      date|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "|          6|    148|           72|           35|      0|33.6|                   0.627| 50|      1|2025-01-27|\n",
            "|          1|     85|           66|           29|      0|26.6|                   0.351| 31|      0|2025-01-27|\n",
            "|          8|    183|           64|            0|      0|23.3|                   0.672| 32|      1|2025-01-27|\n",
            "|          1|     89|           66|           23|     94|28.1|                   0.167| 21|      0|2025-01-27|\n",
            "|          0|    137|           40|           35|    168|43.1|                   2.288| 33|      1|2025-01-27|\n",
            "|          5|    116|           74|            0|      0|25.6|                   0.201| 30|      0|2025-01-27|\n",
            "|          3|     78|           50|           32|     88|31.0|                   0.248| 26|      1|2025-01-27|\n",
            "|         10|    115|            0|            0|      0|35.3|                   0.134| 29|      0|2025-01-27|\n",
            "|          2|    197|           70|           45|    543|30.5|                   0.158| 53|      1|2025-01-27|\n",
            "|          8|    125|           96|            0|      0| 0.0|                   0.232| 54|      1|2025-01-27|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Segunda opção de usar o Metodo Col\n",
        "import pyspark.sql.functions as F\n",
        "df.select(F.col(\"Pregnancies\"), F.col(\"Age\")).show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BckRSyC9mQLq",
        "outputId": "134033ba-fc49-4e93-b8f9-d98a1881420a"
      },
      "execution_count": 49,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+---+\n",
            "|Pregnancies|Age|\n",
            "+-----------+---+\n",
            "|          6| 50|\n",
            "|          1| 31|\n",
            "|          8| 32|\n",
            "|          1| 21|\n",
            "|          0| 33|\n",
            "|          5| 30|\n",
            "|          3| 26|\n",
            "|         10| 29|\n",
            "|          2| 53|\n",
            "|          8| 54|\n",
            "+-----------+---+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Filtrando somente os pacientes menores de  40 anos"
      ],
      "metadata": {
        "id": "bwcTLs7Goran"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Filtering\n",
        "df.filter(df['Age'] < 40).show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3N7vYrCxmknI",
        "outputId": "8914ad6a-5762-435b-a73f-92b0e211181e"
      },
      "execution_count": 50,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "|Pregnancies|Glucose|BloodPressure|SkinThickness|Insulin| BMI|DiabetesPedigreeFunction|Age|Outcome|      date|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "|          1|     85|           66|           29|      0|26.6|                   0.351| 31|      0|2025-01-27|\n",
            "|          8|    183|           64|            0|      0|23.3|                   0.672| 32|      1|2025-01-27|\n",
            "|          1|     89|           66|           23|     94|28.1|                   0.167| 21|      0|2025-01-27|\n",
            "|          0|    137|           40|           35|    168|43.1|                   2.288| 33|      1|2025-01-27|\n",
            "|          5|    116|           74|            0|      0|25.6|                   0.201| 30|      0|2025-01-27|\n",
            "|          3|     78|           50|           32|     88|31.0|                   0.248| 26|      1|2025-01-27|\n",
            "|         10|    115|            0|            0|      0|35.3|                   0.134| 29|      0|2025-01-27|\n",
            "|          4|    110|           92|            0|      0|37.6|                   0.191| 30|      0|2025-01-27|\n",
            "|         10|    168|           74|            0|      0|38.0|                   0.537| 34|      1|2025-01-27|\n",
            "|          7|    100|            0|            0|      0|30.0|                   0.484| 32|      1|2025-01-27|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Filtrando duas Colunas\n",
        "\n",
        "df.filter(df['age'] < 40).select('Insulin','Outcome').show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "z_vB4fraozWl",
        "outputId": "466901df-044f-45ac-94b2-0e1ad3a5fff5"
      },
      "execution_count": 51,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+-------+\n",
            "|Insulin|Outcome|\n",
            "+-------+-------+\n",
            "|      0|      0|\n",
            "|      0|      1|\n",
            "|     94|      0|\n",
            "|    168|      1|\n",
            "|      0|      0|\n",
            "|     88|      1|\n",
            "|      0|      0|\n",
            "|      0|      0|\n",
            "|      0|      1|\n",
            "|      0|      1|\n",
            "+-------+-------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Filtrando com duas colunas e com uma condicional\n",
        "df.filter(df['age'] > 60).filter(df['Outcome'] == '1').show(10)"
      ],
      "metadata": {
        "id": "w34UnTEXo_zw",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "94ad41b1-484b-40e2-8ac1-db5ddf468489"
      },
      "execution_count": 52,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "|Pregnancies|Glucose|BloodPressure|SkinThickness|Insulin| BMI|DiabetesPedigreeFunction|Age|Outcome|      date|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "|          4|    146|           92|            0|      0|31.2|                   0.539| 61|      1|2025-01-27|\n",
            "|          0|    105|           84|            0|      0|27.9|                   0.741| 62|      1|2025-01-27|\n",
            "|          2|    158|           90|            0|      0|31.6|                   0.805| 66|      1|2025-01-27|\n",
            "|          4|    146|           78|            0|      0|38.5|                    0.52| 67|      1|2025-01-27|\n",
            "|          2|    197|           70|           99|      0|34.7|                   0.575| 62|      1|2025-01-27|\n",
            "|          4|    145|           82|           18|      0|32.5|                   0.235| 70|      1|2025-01-27|\n",
            "|          6|    190|           92|            0|      0|35.5|                   0.278| 66|      1|2025-01-27|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Filtrando com duas condições\n",
        "df.filter((df['Outcome']==1) & (df['Pregnancies'] >=9)).show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "pRmBt1zmpGJU",
        "outputId": "2dff7b5b-6cd4-4fdd-8d7c-a6ca9756f1f4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "|Pregnancies|Glucose|BloodPressure|SkinThickness|Insulin| BMI|DiabetesPedigreeFunction|Age|Outcome|      date|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "|         10|    168|           74|            0|      0|38.0|                   0.537| 34|      1|2025-01-27|\n",
            "|          9|    119|           80|           35|      0|29.0|                   0.263| 29|      1|2025-01-27|\n",
            "|         11|    143|           94|           33|    146|36.6|                   0.254| 51|      1|2025-01-27|\n",
            "|         10|    125|           70|           26|    115|31.1|                   0.205| 41|      1|2025-01-27|\n",
            "|          9|    102|           76|           37|      0|32.9|                   0.665| 46|      1|2025-01-27|\n",
            "|          9|    171|          110|           24|    240|45.4|                   0.721| 54|      1|2025-01-27|\n",
            "|         13|    126|           90|            0|      0|43.4|                   0.583| 42|      1|2025-01-27|\n",
            "|         15|    136|           70|           32|    110|37.1|                   0.153| 43|      1|2025-01-27|\n",
            "|          9|    122|           56|            0|      0|33.3|                   1.114| 33|      1|2025-01-27|\n",
            "|         10|    108|           66|            0|      0|32.4|                   0.272| 42|      1|2025-01-27|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Filtrando usando o metodo where e usando o count\n",
        "df.where(df['age']>40).count()"
      ],
      "metadata": {
        "id": "pQTO1c5SpOYk",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "5a759add-81c7-432d-e454-a2fa7b3940de"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "194"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Groupby"
      ],
      "metadata": {
        "id": "K9zXTrTiphOp"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Grouping data\n",
        "df.groupBy('Outcome').count().show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LGjXhrvHpV_W",
        "outputId": "963a2530-aa08-4ce7-ebba-321081c6b60a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+-----+\n",
            "|Outcome|count|\n",
            "+-------+-----+\n",
            "|      0|  500|\n",
            "|      1|  268|\n",
            "+-------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "source": [
        "df.groupBy('Outcome').count().show()"
      ],
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_7RXEeJUhENN",
        "outputId": "5b03e033-39a4-4826-8ba9-a4212fae897b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+-----+\n",
            "|Outcome|count|\n",
            "+-------+-----+\n",
            "|      0|  500|\n",
            "|      1|  268|\n",
            "+-------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Valores Distintos"
      ],
      "metadata": {
        "id": "Da5sy-ndppQI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# valores distintos\n",
        "df.select('Pregnancies').distinct().count()"
      ],
      "metadata": {
        "id": "U3okwm5wpkHJ",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "dde6793d-b38a-4e3e-c9b1-6feea8d678e9"
      },
      "execution_count": 53,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "17"
            ]
          },
          "metadata": {},
          "execution_count": 53
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#  Idade Media com e sem diabetes\n",
        "df.groupBy('Outcome').agg(F.mean(\"age\").alias(\"age_mean\")).show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4Hv6EmfQptO6",
        "outputId": "d83b8bf1-3ee3-4898-fcb7-88a6184098c4"
      },
      "execution_count": 54,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+-----------------+\n",
            "|Outcome|         age_mean|\n",
            "+-------+-----------------+\n",
            "|      0|            31.19|\n",
            "|      1|37.06716417910448|\n",
            "+-------+-----------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "from pyspark.sql import functions as F\n",
        "\n",
        "# Calculate the mean age grouped by Outcome\n",
        "result = df.groupBy('Outcome').agg(F.mean(\"age\").alias(\"age_mean\"))\n",
        "result.show(10)\n",
        "\n",
        "\n",
        "for row in result.collect():\n",
        "    print(f\"Outcome: {row['Outcome']}, Idade média: {row['age_mean']:.2f}\")\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "o7QB7tPyp5Qw",
        "outputId": "daa07fc8-014d-4965-de8a-0d2250d7d030"
      },
      "execution_count": 55,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+-----------------+\n",
            "|Outcome|         age_mean|\n",
            "+-------+-----------------+\n",
            "|      0|            31.19|\n",
            "|      1|37.06716417910448|\n",
            "+-------+-----------------+\n",
            "\n",
            "Outcome: 0, Idade média: 31.19\n",
            "Outcome: 1, Idade média: 37.07\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Criando Função\n",
        "def diabete(case):\n",
        "    if case == 1 :\n",
        "        return \"diabete\"\n",
        "    else:\n",
        "        return 'no diabete'"
      ],
      "metadata": {
        "id": "LwhcXtQQqWI-"
      },
      "execution_count": 56,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Aplicando a função\n",
        "from pyspark.sql.types import *\n",
        "diabete_udf = F.udf(diabete, StringType())\n",
        "df.withColumn('diabete_case', diabete_udf(df['Outcome'])).show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KifRUOwkqgdX",
        "outputId": "3dd08ce4-e4b4-40e1-e99e-68576bbb6fb4"
      },
      "execution_count": 57,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+------------+\n",
            "|Pregnancies|Glucose|BloodPressure|SkinThickness|Insulin| BMI|DiabetesPedigreeFunction|Age|Outcome|      date|diabete_case|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+------------+\n",
            "|          6|    148|           72|           35|      0|33.6|                   0.627| 50|      1|2025-01-27|     diabete|\n",
            "|          1|     85|           66|           29|      0|26.6|                   0.351| 31|      0|2025-01-27|  no diabete|\n",
            "|          8|    183|           64|            0|      0|23.3|                   0.672| 32|      1|2025-01-27|     diabete|\n",
            "|          1|     89|           66|           23|     94|28.1|                   0.167| 21|      0|2025-01-27|  no diabete|\n",
            "|          0|    137|           40|           35|    168|43.1|                   2.288| 33|      1|2025-01-27|     diabete|\n",
            "|          5|    116|           74|            0|      0|25.6|                   0.201| 30|      0|2025-01-27|  no diabete|\n",
            "|          3|     78|           50|           32|     88|31.0|                   0.248| 26|      1|2025-01-27|     diabete|\n",
            "|         10|    115|            0|            0|      0|35.3|                   0.134| 29|      0|2025-01-27|  no diabete|\n",
            "|          2|    197|           70|           45|    543|30.5|                   0.158| 53|      1|2025-01-27|     diabete|\n",
            "|          8|    125|           96|            0|      0| 0.0|                   0.232| 54|      1|2025-01-27|     diabete|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+----------+------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# salvar em um csv\n",
        "df.write.format(\"csv\").option(\"header\", \"true\").save(\"diabetes_csv\")"
      ],
      "metadata": {
        "id": "mADu2-jeqtjp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Download do arquivo CSV para o seu ambiente local\n",
        "from google.colab import files\n",
        "import os\n",
        "\n",
        "# Define o nome do arquivo e diretório\n",
        "output_filename = \"diabetes.csv\"\n",
        "output_dir = \"diabetes_csv\"\n",
        "\n",
        "# Verifica se existe algum arquivo dentro do diretório\n",
        "file_list = [f for f in os.listdir(output_dir) if os.path.isfile(os.path.join(output_dir, f))]\n",
        "\n",
        "if file_list:\n",
        "    # Seleciona o primeiro arquivo encontrado (assumindo que apenas um é gerado)\n",
        "    output_file = os.path.join(output_dir, file_list[0])\n",
        "\n",
        "    try:\n",
        "        files.download(output_file)\n",
        "        print(f\"Arquivo '{output_file}' baixado com sucesso.\")\n",
        "    except Exception as e:\n",
        "        print(f\"Erro ao baixar o arquivo: {e}\")\n",
        "else:\n",
        "    print(\"Nenhum arquivo encontrado no diretório especificado.\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "88uWg2W2rCdD",
        "outputId": "ca34bf4b-e60c-4d72-82af-33657df5c4c1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "download(\"download_6007a118-ce47-429e-852b-46673153d401\", \"._SUCCESS.crc\", 8)"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Arquivo 'diabetes_csv/._SUCCESS.crc' baixado com sucesso.\n"
          ]
        }
      ]
    }
  ]
}