27_ntb_PySpark_ReadFunctions_diabetes.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyPFB7z8kPf2ZRrQ2O2WspNj",
      "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/27_ntb_PySpark_ReadFunctions_diabetes.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##  Tecnologias Utilizadas:\n",
        "- Pyspark\n",
        "- Python\n",
        "\n",
        "## Contato:\n",
        "Se precisar de mais informações, entre em contato:\n",
        "\n",
        "**Email: luciana.sampaio84@gmail.com**\n",
        "\n",
        "LinkedIn: [Luciana Sampaio ](https://www.linkedin.com/in/luciana-sampaio/)\n"
      ],
      "metadata": {
        "id": "dibYcjmd3ZQB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "import requests\n",
        "import pandas as pd\n",
        "import io\n",
        "import warnings\n",
        "warnings.filterwarnings(\"ignore\")\n"
      ],
      "metadata": {
        "id": "7XZ6g1BZ21lE"
      },
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Cria uma SparkSession\n",
        "spark = SparkSession.builder.appName(\"diabetes\").getOrCreate()\n",
        "\n",
        "# Link direto para download do arquivo\n",
        "url = 'https://github.com/luasampaio/ModeloPredi-oDiabetes/blob/main/diabetes.csv'"
      ],
      "metadata": {
        "id": "672UdwLtyeAc"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "source": [
        "from pyspark.sql import SparkSession\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": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Zu0-mk2Knl--",
        "outputId": "cfe40a61-13c3-4d68-a2d4-ceaebdec86d7"
      },
      "execution_count": 8,
      "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",
            "|          0|    137|           40|           35|    168|43.1|                   2.288| 33|      1|\n",
            "|          5|    116|           74|            0|      0|25.6|                   0.201| 30|      0|\n",
            "|          3|     78|           50|           32|     88|31.0|                   0.248| 26|      1|\n",
            "|         10|    115|            0|            0|      0|35.3|                   0.134| 29|      0|\n",
            "|          2|    197|           70|           45|    543|30.5|                   0.158| 53|      1|\n",
            "|          8|    125|           96|            0|      0| 0.0|                   0.232| 54|      1|\n",
            "|          4|    110|           92|            0|      0|37.6|                   0.191| 30|      0|\n",
            "|         10|    168|           74|            0|      0|38.0|                   0.537| 34|      1|\n",
            "|         10|    139|           80|            0|      0|27.1|                   1.441| 57|      0|\n",
            "|          1|    189|           60|           23|    846|30.1|                   0.398| 59|      1|\n",
            "|          5|    166|           72|           19|    175|25.8|                   0.587| 51|      1|\n",
            "|          7|    100|            0|            0|      0|30.0|                   0.484| 32|      1|\n",
            "|          0|    118|           84|           47|    230|45.8|                   0.551| 31|      1|\n",
            "|          7|    107|           74|            0|      0|29.6|                   0.254| 31|      1|\n",
            "|          1|    103|           30|           38|     83|43.3|                   0.183| 33|      0|\n",
            "|          1|    115|           70|           30|     96|34.6|                   0.529| 32|      1|\n",
            "+-----------+-------+-------------+-------------+-------+----+------------------------+---+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Convert the Pandas DataFrame to a Spark DataFrame\n",
        "df = spark.createDataFrame(diabetes)"
      ],
      "metadata": {
        "id": "BY4qraSE27zb"
      },
      "execution_count": 15,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "df.show(4)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KcBC9v33n-B6",
        "outputId": "c57f4266-2606-4fb6-dbfe-35b38da8331e"
      },
      "execution_count": 18,
      "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": "59a6eda0-c98a-4df6-a3b9-55302da2af00"
      },
      "execution_count": 19,
      "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": [
        "print(type(df))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0fbbfpnSlM7d",
        "outputId": "2168d661-9407-450a-dd79-1f1ec8ab48a8"
      },
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pyspark.sql.dataframe.DataFrame'>\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Columns of dataframe\n",
        "df.columns"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vQOv9p1QlZj3",
        "outputId": "62a3812c-3907-42b3-d667-afb88f7ea1c3"
      },
      "execution_count": 22,
      "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": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Shape of dataset\n",
        "print((df.count(),len(df.columns)))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qm8kac3QleBF",
        "outputId": "748ce100-f633-4dc8-c001-61cd24402f37"
      },
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(768, 9)\n"
          ]
        }
      ]
    },
    {
      "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": "db3ae853-37b2-4592-8adc-20a351bf9ed7"
      },
      "execution_count": 24,
      "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": "7f4c384b-86a6-4b78-e954-fb329ce10a1a"
      },
      "execution_count": 25,
      "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",
        "\n",
        "\n",
        "# Add current date column\n",
        "df = df.withColumn(\"date\", current_date())\n"
      ],
      "cell_type": "code",
      "metadata": {
        "id": "r3FvX5oLmI86"
      },
      "execution_count": 26,
      "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": 27,
      "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": "15ddf18a-d815-43f6-9759-4f6ca57ca204"
      },
      "execution_count": 28,
      "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": "4e8c13f1-0c96-4eab-94f9-1ace003dfa48"
      },
      "execution_count": 29,
      "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": "da0616c6-ff34-49e0-cfad-63379ff9cfee"
      },
      "execution_count": 30,
      "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"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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": "41962d4d-ee0a-419e-ebfa-bb9163a0f483"
      },
      "execution_count": 31,
      "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"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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": "d87895ce-9d00-4ee1-aa21-a59d279a74f6"
      },
      "execution_count": 32,
      "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"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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": "57af5b75-e1d3-48f1-c377-9172f708e05c"
      },
      "execution_count": 33,
      "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": "63f28374-dc48-4e3a-cc44-a664323fa726"
      },
      "execution_count": 35,
      "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": 36,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "diabete_udf = F.udf(diabete)"
      ],
      "metadata": {
        "id": "U6MZVWreqduX"
      },
      "execution_count": 37,
      "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": "6352904d-7884-4001-a645-5e42700e8690"
      },
      "execution_count": 38,
      "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": 39,
      "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": 41,
      "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"
          ]
        }
      ]
    }
  ]
}