src/notebooks/cardiovascular_diseases_risk_databricks.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b9645fe4-53b0-4513-96b5-1e64417a83d0",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Ingestão de Dados ELT\n",
    "> Conjunto de dados de previsão de risco de doenças cardiovasculares"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "22558479-b792-4872-b6ff-e2e30355deb0",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Verificando pastas criadas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2edadaba-12dd-4575-9314-a53aaa0ca98d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "display(dbutils.fs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "21cce6d7-fe64-4f55-a003-361402d8c5ad",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "display(dbutils.fs.ls(\"/\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "7ee4de27-cdfb-4258-9654-bcf896e71a25",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Extraindo dados/Realizando a leitura"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "dd19e6c7-2b76-4058-8b7d-84a4617a054d",
     "showTitle": true,
     "title": "Leitura dos dados"
    }
   },
   "outputs": [],
   "source": [
    "df = spark.read.format(\"csv\").option(\"header\", True).load(\"/tmp/cardiovascular-diseases-risk.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "f66bcc86-c524-44f5-a61b-e62e61507616",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "df240ca3-bfb6-4fc2-a9f5-9cfcb493c9c7",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Verificando valores distintos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "acf23947-68df-4959-9d62-f0b8986a09cd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df.select(\"General_Health\").distinct().display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "829da60c-3434-459f-8a29-506dc15b5dbb",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Alterando nome da coluna (o Delta Table não aceita caracteres especiais em nome de colunas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "be3320af-38cb-40ea-84fc-0eb45b2d3653",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "d81c4c66-9bef-4827-bdff-4a21d3d9fb27",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df = df.withColumnRenamed(\"Height_(cm)\", \"Height_cm\").withColumnRenamed(\"Weight_(kg)\", \"Weight_kg\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "30f822f5-9a2b-44af-9455-1209051164a4",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Realizando o load/Armazenamento de dados com configurações especificas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "47015310-3c2c-4cf0-b898-1a5fe69adb86",
     "showTitle": true,
     "title": "Armazenamento de dados com Delta Table"
    }
   },
   "outputs": [],
   "source": [
    "df.write.format(\"delta\").mode(\"overwrite\").option(\"mergeSchema\", \"true\").partitionBy(\"General_Health\").save(\"/hospital/rw/cardiovascular_diseases/\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "75f7e1ad-4d64-4284-9a04-cc687365655e",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Criar database e tabela delta location storage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6800bb40-e566-4902-a8eb-a737c8648e99",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "CREATE DATABASE IF NOT EXISTS db_hospital"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "ea21bc93-a279-4854-9f1e-a1c0da82c90b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "CREATE TABLE IF NOT EXISTS db_hospital.cardiovascular_diseases LOCATION \"/hospital/rw/cardiovascular_diseases/\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "01acd8be-60c8-4479-b27d-446fe8e4ad59",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Verificando detalhes da tabela criada"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e9d42cce-c037-435b-b367-f5758776f75c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select * from db_hospital.cardiovascular_diseases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0be26db2-f079-4b68-8316-dfc9752430ef",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "desc detail db_hospital.cardiovascular_diseases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "61194635-b1d9-4f81-bf9e-9888bf703dde",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "desc detail delta.`/hospital/rw/cardiovascular_diseases/`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "276692ee-1e80-4d43-a3ec-257d06cfaba2",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Analisando dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "df92f25a-c139-4786-b9d3-81c03f1716e3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_cardio = spark.table(\"db_hospital.cardiovascular_diseases\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "01398e6b-f198-4e12-a6a0-13079d665dbd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_cardio.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b778017f-dc35-4c91-914c-0c902abfad7f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_cardio.show(10, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "38ba4c6a-2b43-4c02-b648-a7c12361edec",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_cardio.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "1020dac3-9755-4240-87df-d892e1ae9638",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Verificando classificação de saúde cardiovascular\n",
    "Pesquisa de como as pessoas classificam a sua saúde do coração"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "7c772c94-3887-4618-a6d3-ffcc70145162",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_cardio.groupby(\"General_Health\", \"Sex\").count().display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "dca4225f-cd5a-47a5-b4d5-fa71fdfdd534",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Verificando a quantidade de pessoas que tiveram frequência no hospital"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6544bac0-bb5c-45d5-831e-ba44fc59415c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_cardio.groupBy(\"Checkup\", \"Age_Category\").count().distinct().display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "5550a35e-a49b-4b79-afda-a5ad449209a0",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Verificando quem faz atividade fisica"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "26727464-f048-47b4-9594-6ac4b23f3a7d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_cardio.groupBy(\"Exercise\", \"Sex\").count().display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "cb1a10f8-fb08-4c70-946d-e8bf6ff939f6",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Verificando quem teve doença cardíaca coronária ou infarto do miocárdio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "77cb4b23-f189-4fe1-a71a-9ee62260df5e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_cardio.groupBy(\"Heart_Disease\", \"Sex\").count().display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0baddc07-73b5-4793-b06b-e75e661ffd83",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_cardio.groupBy(\"Heart_Disease\", \"Age_Category\").count().display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "52c42be9-f0bf-416e-9d2d-d0a3c99e9182",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 1310385912103996,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "cardiovascular_diseases_risk_databricks",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}