Transformation_All_Data_From_DBFS.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "1d65118a-b377-47b8-b5f1-271284f5f2c9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "from pyspark.sql.functions import to_date,date_format\n",
    "from pyspark.sql import types,Row\n",
    "import pandas as pd\n",
    "# Ler as duas tabelas e todas as partiçoes\n",
    "spark.conf.set('spark.sql.repl.eagerEval.enabled', True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "9663943c-7fb7-4540-b102-30f7d2625ff6",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Efetuando leitura dos dados em JSON para transformação dos dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "1820694a-8183-4271-9675-bec074be6496",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "countries = spark.read \\\n",
    "                .json(\"./FileStore/covid_data_lake/raw/countries/*.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "7e3b21f4-4d42-4dd1-9e7f-0aa7a098dd0d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Juntar as tabelas\n",
    "covid_cases = spark.read \\\n",
    "                   .json(\"./FileStore/covid_data_lake/raw/covid_cases/*.json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "c8276966-6ab8-434a-95d9-b86cbfd06133",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Junção das tabelas carregadas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "39911c21-a51c-47f6-bcce-54f919af746f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#df = spark.sql(\"select * from countries, covid_cases where countries.country_name = covid_cases.country_name\")\n",
    "df = countries.join(covid_cases,'country_name','inner') \\\n",
    "                .select(covid_cases.active, covid_cases.cases_covid_id, covid_cases.confirmed, countries.country_name, covid_cases.date_register, covid_cases.deaths, covid_cases.lat, covid_cases.lon, covid_cases.recovered, countries.country_code,countries.slug)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "f4a4b734-ef63-488b-9867-8d552261c443",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Alterando dados do campo \"date_regsiter\" para o tipo date\n",
    "df = df.withColumn('date_register',df[\"date_register\"].cast(types.DateType())) \\\n",
    "       .withColumn('country',df[\"country_name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "2c373dd8-c3ed-44ba-be24-a252288882a2",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">Out[23]: [(&#39;active&#39;, &#39;bigint&#39;),\n",
       " (&#39;cases_covid_id&#39;, &#39;bigint&#39;),\n",
       " (&#39;confirmed&#39;, &#39;bigint&#39;),\n",
       " (&#39;country_name&#39;, &#39;string&#39;),\n",
       " (&#39;date_register&#39;, &#39;date&#39;),\n",
       " (&#39;deaths&#39;, &#39;bigint&#39;),\n",
       " (&#39;lat&#39;, &#39;double&#39;),\n",
       " (&#39;lon&#39;, &#39;double&#39;),\n",
       " (&#39;recovered&#39;, &#39;bigint&#39;),\n",
       " (&#39;country_code&#39;, &#39;string&#39;),\n",
       " (&#39;slug&#39;, &#39;string&#39;),\n",
       " (&#39;country&#39;, &#39;string&#39;)]</div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\">Out[23]: [(&#39;active&#39;, &#39;bigint&#39;),\n (&#39;cases_covid_id&#39;, &#39;bigint&#39;),\n (&#39;confirmed&#39;, &#39;bigint&#39;),\n (&#39;country_name&#39;, &#39;string&#39;),\n (&#39;date_register&#39;, &#39;date&#39;),\n (&#39;deaths&#39;, &#39;bigint&#39;),\n (&#39;lat&#39;, &#39;double&#39;),\n (&#39;lon&#39;, &#39;double&#39;),\n (&#39;recovered&#39;, &#39;bigint&#39;),\n (&#39;country_code&#39;, &#39;string&#39;),\n (&#39;slug&#39;, &#39;string&#39;),\n (&#39;country&#39;, &#39;string&#39;)]</div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Verificando se o tipo foi alterado\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "503ddd54-d2e2-413b-a8b5-cfedb79312b7",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Realizando a criação de features que poderiam ser utilizadas futuramente"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "d7297fd6-5e41-4bab-bd85-f4408f1a5867",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Criando alguns campos baseados nas datas\n",
    "df = df.withColumn('year_register',date_format(df['date_register'],'yyyy')) \\\n",
    "       .withColumn('month_year_register',date_format(df['date_register'],'yyyy-MM'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "cbd9dcb6-4548-4371-8bbf-81ed40acb974",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Dropando colunas que não serão utilizadas\n",
    "df = df.drop(\"country_name\") \\\n",
    "       .drop(\"slug\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "eb14dffe-22bd-4719-bccb-1a9d67289a98",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Corrigindo Outliers encontradas na India"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "b0c56ed7-482d-4ad0-8f12-49cb590425a7",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Transformando outlier da India - 07-03-2021\n",
    "dl = df.filter(df[\"date_register\"] >= '2021-03-06') \\\n",
    "       .filter(df[\"date_register\"] <= '2021-03-08') \\\n",
    "       .filter(df[\"country\"] == \"India\").collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "c2f113e3-78c8-46ce-aa51-8963d917aee6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">Out[27]: 185992.5</div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\">Out[27]: 185992.5</div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "result_mean = (dl[0]['active'] + dl[2]['active'])/2\n",
    "result_mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "56a6809b-f3a5-4189-ba04-8328ffee4c4f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "dp = df.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "17fd46e5-cf20-4e56-9ac7-2447993ba874",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "dp.loc[(dp['country'] == 'India') & (dp['date_register'] == pd.to_datetime('2021-03-07')),'active'] = result_mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "129b1173-074b-4548-b641-7e2262c6f8ce",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = spark.createDataFrame(dp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "1d2fc1e9-72a9-470c-949e-ef542f1acc12",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.createOrReplaceTempView('data_check')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "80b729c9-88b1-4811-a9c0-0f80223c2c8f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">Out[32]: </div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\">Out[32]: </div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>active</th><th>cases_covid_id</th><th>confirmed</th><th>date_register</th><th>deaths</th><th>lat</th><th>lon</th><th>recovered</th><th>country_code</th><th>country</th><th>year_register</th><th>month_year_register</th></tr>\n",
       "<tr><td>185992.5</td><td>137969</td><td>11229398</td><td>2021-03-07</td><td>157853</td><td>20.59</td><td>78.96</td><td>10882798</td><td>IN</td><td>India</td><td>2021</td><td>2021-03</td></tr>\n",
       "<tr><td>185992.5</td><td>100589</td><td>11229398</td><td>2021-03-07</td><td>157853</td><td>20.59</td><td>78.96</td><td>10882798</td><td>IN</td><td>India</td><td>2021</td><td>2021-03</td></tr>\n",
       "</table>\n"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<table border='1'>\n<tr><th>active</th><th>cases_covid_id</th><th>confirmed</th><th>date_register</th><th>deaths</th><th>lat</th><th>lon</th><th>recovered</th><th>country_code</th><th>country</th><th>year_register</th><th>month_year_register</th></tr>\n<tr><td>185992.5</td><td>137969</td><td>11229398</td><td>2021-03-07</td><td>157853</td><td>20.59</td><td>78.96</td><td>10882798</td><td>IN</td><td>India</td><td>2021</td><td>2021-03</td></tr>\n<tr><td>185992.5</td><td>100589</td><td>11229398</td><td>2021-03-07</td><td>157853</td><td>20.59</td><td>78.96</td><td>10882798</td><td>IN</td><td>India</td><td>2021</td><td>2021-03</td></tr>\n</table>\n",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "textData": null,
       "type": "htmlSandbox"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "spark.sql(\"select * from data_check where data_check.country = 'India' and data_check.date_register = '2021-03-07'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "3cebeb18-2625-43e4-b2fb-0161ef7b0307",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">Out[33]: </div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\">Out[33]: </div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>active</th><th>cases_covid_id</th><th>confirmed</th><th>date_register</th><th>deaths</th><th>lat</th><th>lon</th><th>recovered</th><th>country_code</th><th>country</th><th>year_register</th><th>month_year_register</th></tr>\n",
       "<tr><td>5977.0</td><td>259897</td><td>10183</td><td>2020-10-25</td><td>122</td><td>55.17</td><td>23.88</td><td>4633</td><td>LT</td><td>Lithuania</td><td>2020</td><td>2020-10</td></tr>\n",
       "<tr><td>5.0</td><td>20802</td><td>88</td><td>2020-12-14</td><td>0</td><td>15.41</td><td>-61.37</td><td>83</td><td>DM</td><td>Dominica</td><td>2020</td><td>2020-12</td></tr>\n",
       "<tr><td>12457.0</td><td>212226</td><td>475355</td><td>2021-02-07</td><td>8394</td><td>31.79</td><td>-7.09</td><td>454504</td><td>MA</td><td>Morocco</td><td>2021</td><td>2021-02</td></tr>\n",
       "<tr><td>2.0</td><td>20621</td><td>18</td><td>2020-06-16</td><td>0</td><td>15.41</td><td>-61.37</td><td>16</td><td>DM</td><td>Dominica</td><td>2020</td><td>2020-06</td></tr>\n",
       "<tr><td>2061.0</td><td>137161</td><td>3538</td><td>2020-05-12</td><td>47</td><td>40.07</td><td>45.04</td><td>1430</td><td>AM</td><td>Armenia</td><td>2020</td><td>2020-05</td></tr>\n",
       "<tr><td>5542.0</td><td>67589</td><td>113767</td><td>2020-08-31</td><td>6556</td><td>-1.83</td><td>-78.18</td><td>101669</td><td>EC</td><td>Ecuador</td><td>2020</td><td>2020-08</td></tr>\n",
       "<tr><td>959556.0</td><td>95090</td><td>973604</td><td>2021-04-30</td><td>14048</td><td>60.13</td><td>18.64</td><td>0</td><td>SE</td><td>Sweden</td><td>2021</td><td>2021-04</td></tr>\n",
       "<tr><td>82767.0</td><td>112601</td><td>255970</td><td>2020-11-21</td><td>3824</td><td>39.4</td><td>-8.22</td><td>169379</td><td>PT</td><td>Portugal</td><td>2020</td><td>2020-11</td></tr>\n",
       "<tr><td>147994.0</td><td>138870</td><td>438805</td><td>2020-11-11</td><td>5570</td><td>49.82</td><td>15.47</td><td>285241</td><td>CZ</td><td>Czech Republic</td><td>2020</td><td>2020-11</td></tr>\n",
       "<tr><td>0.0</td><td>226547</td><td>113</td><td>2020-08-29</td><td>3</td><td>-35.47</td><td>149.01</td><td>110</td><td>AU</td><td>Australia</td><td>2020</td><td>2020-08</td></tr>\n",
       "<tr><td>0.0</td><td>56818</td><td>980</td><td>2020-11-29</td><td>3</td><td>41.11</td><td>85.24</td><td>977</td><td>CN</td><td>China</td><td>2020</td><td>2020-11</td></tr>\n",
       "<tr><td>16.0</td><td>155961</td><td>35</td><td>2020-04-19</td><td>1</td><td>9.31</td><td>2.32</td><td>18</td><td>BJ</td><td>Benin</td><td>2020</td><td>2020-04</td></tr>\n",
       "<tr><td>0.0</td><td>244601</td><td>18</td><td>2020-07-25</td><td>0</td><td>35.75</td><td>96.0</td><td>18</td><td>CN</td><td>China</td><td>2020</td><td>2020-07</td></tr>\n",
       "<tr><td>24567.0</td><td>154062</td><td>94084</td><td>2020-09-02</td><td>2030</td><td>8.54</td><td>-80.78</td><td>67487</td><td>PA</td><td>Panama</td><td>2020</td><td>2020-09</td></tr>\n",
       "<tr><td>0.0</td><td>51230</td><td>76</td><td>2020-06-18</td><td>3</td><td>41.11</td><td>85.24</td><td>73</td><td>CN</td><td>China</td><td>2020</td><td>2020-06</td></tr>\n",
       "<tr><td>1741.0</td><td>187096</td><td>5338</td><td>2020-07-31</td><td>49</td><td>-23.44</td><td>-58.44</td><td>3548</td><td>PY</td><td>Paraguay</td><td>2020</td><td>2020-07</td></tr>\n",
       "<tr><td>3916.0</td><td>132927</td><td>496097</td><td>2021-03-31</td><td>8818</td><td>31.79</td><td>-7.09</td><td>483363</td><td>MA</td><td>Morocco</td><td>2021</td><td>2021-03</td></tr>\n",
       "<tr><td>2.0</td><td>130659</td><td>17</td><td>2020-07-25</td><td>0</td><td>17.36</td><td>-62.78</td><td>15</td><td>KN</td><td>Saint Kitts and N...</td><td>2020</td><td>2020-07</td></tr>\n",
       "<tr><td>158604.0</td><td>110171</td><td>1298608</td><td>2021-02-23</td><td>35014</td><td>-0.79</td><td>113.92</td><td>1104990</td><td>ID</td><td>Indonesia</td><td>2021</td><td>2021-02</td></tr>\n",
       "<tr><td>46.0</td><td>184781</td><td>3657</td><td>2021-03-18</td><td>146</td><td>-11.88</td><td>43.87</td><td>3465</td><td>KM</td><td>Comoros</td><td>2021</td><td>2021-03</td></tr>\n",
       "</table>\n",
       "only showing top 20 rows\n"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<table border='1'>\n<tr><th>active</th><th>cases_covid_id</th><th>confirmed</th><th>date_register</th><th>deaths</th><th>lat</th><th>lon</th><th>recovered</th><th>country_code</th><th>country</th><th>year_register</th><th>month_year_register</th></tr>\n<tr><td>5977.0</td><td>259897</td><td>10183</td><td>2020-10-25</td><td>122</td><td>55.17</td><td>23.88</td><td>4633</td><td>LT</td><td>Lithuania</td><td>2020</td><td>2020-10</td></tr>\n<tr><td>5.0</td><td>20802</td><td>88</td><td>2020-12-14</td><td>0</td><td>15.41</td><td>-61.37</td><td>83</td><td>DM</td><td>Dominica</td><td>2020</td><td>2020-12</td></tr>\n<tr><td>12457.0</td><td>212226</td><td>475355</td><td>2021-02-07</td><td>8394</td><td>31.79</td><td>-7.09</td><td>454504</td><td>MA</td><td>Morocco</td><td>2021</td><td>2021-02</td></tr>\n<tr><td>2.0</td><td>20621</td><td>18</td><td>2020-06-16</td><td>0</td><td>15.41</td><td>-61.37</td><td>16</td><td>DM</td><td>Dominica</td><td>2020</td><td>2020-06</td></tr>\n<tr><td>2061.0</td><td>137161</td><td>3538</td><td>2020-05-12</td><td>47</td><td>40.07</td><td>45.04</td><td>1430</td><td>AM</td><td>Armenia</td><td>2020</td><td>2020-05</td></tr>\n<tr><td>5542.0</td><td>67589</td><td>113767</td><td>2020-08-31</td><td>6556</td><td>-1.83</td><td>-78.18</td><td>101669</td><td>EC</td><td>Ecuador</td><td>2020</td><td>2020-08</td></tr>\n<tr><td>959556.0</td><td>95090</td><td>973604</td><td>2021-04-30</td><td>14048</td><td>60.13</td><td>18.64</td><td>0</td><td>SE</td><td>Sweden</td><td>2021</td><td>2021-04</td></tr>\n<tr><td>82767.0</td><td>112601</td><td>255970</td><td>2020-11-21</td><td>3824</td><td>39.4</td><td>-8.22</td><td>169379</td><td>PT</td><td>Portugal</td><td>2020</td><td>2020-11</td></tr>\n<tr><td>147994.0</td><td>138870</td><td>438805</td><td>2020-11-11</td><td>5570</td><td>49.82</td><td>15.47</td><td>285241</td><td>CZ</td><td>Czech Republic</td><td>2020</td><td>2020-11</td></tr>\n<tr><td>0.0</td><td>226547</td><td>113</td><td>2020-08-29</td><td>3</td><td>-35.47</td><td>149.01</td><td>110</td><td>AU</td><td>Australia</td><td>2020</td><td>2020-08</td></tr>\n<tr><td>0.0</td><td>56818</td><td>980</td><td>2020-11-29</td><td>3</td><td>41.11</td><td>85.24</td><td>977</td><td>CN</td><td>China</td><td>2020</td><td>2020-11</td></tr>\n<tr><td>16.0</td><td>155961</td><td>35</td><td>2020-04-19</td><td>1</td><td>9.31</td><td>2.32</td><td>18</td><td>BJ</td><td>Benin</td><td>2020</td><td>2020-04</td></tr>\n<tr><td>0.0</td><td>244601</td><td>18</td><td>2020-07-25</td><td>0</td><td>35.75</td><td>96.0</td><td>18</td><td>CN</td><td>China</td><td>2020</td><td>2020-07</td></tr>\n<tr><td>24567.0</td><td>154062</td><td>94084</td><td>2020-09-02</td><td>2030</td><td>8.54</td><td>-80.78</td><td>67487</td><td>PA</td><td>Panama</td><td>2020</td><td>2020-09</td></tr>\n<tr><td>0.0</td><td>51230</td><td>76</td><td>2020-06-18</td><td>3</td><td>41.11</td><td>85.24</td><td>73</td><td>CN</td><td>China</td><td>2020</td><td>2020-06</td></tr>\n<tr><td>1741.0</td><td>187096</td><td>5338</td><td>2020-07-31</td><td>49</td><td>-23.44</td><td>-58.44</td><td>3548</td><td>PY</td><td>Paraguay</td><td>2020</td><td>2020-07</td></tr>\n<tr><td>3916.0</td><td>132927</td><td>496097</td><td>2021-03-31</td><td>8818</td><td>31.79</td><td>-7.09</td><td>483363</td><td>MA</td><td>Morocco</td><td>2021</td><td>2021-03</td></tr>\n<tr><td>2.0</td><td>130659</td><td>17</td><td>2020-07-25</td><td>0</td><td>17.36</td><td>-62.78</td><td>15</td><td>KN</td><td>Saint Kitts and N...</td><td>2020</td><td>2020-07</td></tr>\n<tr><td>158604.0</td><td>110171</td><td>1298608</td><td>2021-02-23</td><td>35014</td><td>-0.79</td><td>113.92</td><td>1104990</td><td>ID</td><td>Indonesia</td><td>2021</td><td>2021-02</td></tr>\n<tr><td>46.0</td><td>184781</td><td>3657</td><td>2021-03-18</td><td>146</td><td>-11.88</td><td>43.87</td><td>3465</td><td>KM</td><td>Comoros</td><td>2021</td><td>2021-03</td></tr>\n</table>\nonly showing top 20 rows\n",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "textData": null,
       "type": "htmlSandbox"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "bef36dfc-82dd-452c-a9cd-089cae66f3ba",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\"></div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Inserindo dados no parquet\n",
    "df.write.parquet('./FileStore/covid_data_lake/cleaned', mode='overwrite',partitionBy=['year_register'])"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 2
   },
   "notebookName": "Transformation_All_Data_From_DBFS",
   "notebookOrigID": 3201239750388032,
   "widgets": {}
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}