airbnb_rj_projeto_v2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Projeto Final | Analytics Engineering\n",
    "----\n",
    "**Engenharia de Dados e Garantia de Qualidade no Conjunto de Dados do Airbnb no Rio de Janeiro**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Etapa 1:\n",
    "\n",
    "**Aquisição de Dados e Armazenamento de Dados em PostgreSQL - Camada Bronze**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   - Baixe o conjunto de dados \"Inside Airbnb\" do Rio de Janeiro da fonte oficial (http://insideairbnb.com/) e promova uma estruturação simples nos dados.\n",
    "   - Crie um banco de dados PostgreSQL para armazenar os dados brutos das 3 tabelas (\"Listing\", \"Reviews\" e Calendar\") na camada \"bronze\"."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bibliotecas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, text as sql_text\n",
    "import pandas as pd\n",
    "import datetime\n",
    "\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Chamada da biblioteca para usar o SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Informações do banco de dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Importar modulo de conexão e implementação do banco  \n",
    "import modules.database as db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Estabelecer uma conexão com um banco de dados PostgreSQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Configurar conexão\n",
    "engine = db.engine_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Conexão bem sucedida!\n"
     ]
    }
   ],
   "source": [
    "# Testar a conexão\n",
    "try:\n",
    "    with engine.connect():\n",
    "        print(\"Conexão bem sucedida!\")\n",
    "except Exception as e:\n",
    "    print(\"Erro ao conectar:\", e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Camada Landing - Extração de dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "O diretório ./data/ não existe e tentaremos criar.\n",
      "Diretório criado com sucesso!!\n",
      "Efetuando a requisição para a url https://data.insideairbnb.com/brazil/rj/rio-de-janeiro/2023-12-26/data/listings.csv.gz\n",
      "Efetuando a requisição para a url https://data.insideairbnb.com/brazil/rj/rio-de-janeiro/2023-12-26/data/calendar.csv.gz\n",
      "Efetuando a requisição para a url https://data.insideairbnb.com/brazil/rj/rio-de-janeiro/2023-12-26/data/reviews.csv.gz\n",
      "Salvando o arquivo ./data/calendar.csv.gz\n",
      "Salvando o arquivo ./data/reviews.csv.gz\n",
      "Salvando o arquivo ./data/listings.csv.gz\n"
     ]
    }
   ],
   "source": [
    "## Importar modulo para extração dos dados\n",
    "from modules.etl import Landing\n",
    "\n",
    "url = [\n",
    "    'https://data.insideairbnb.com/brazil/rj/rio-de-janeiro/2023-12-26/data/listings.csv.gz',\n",
    "    'https://data.insideairbnb.com/brazil/rj/rio-de-janeiro/2023-12-26/data/calendar.csv.gz',\n",
    "    'https://data.insideairbnb.com/brazil/rj/rio-de-janeiro/2023-12-26/data/reviews.csv.gz'\n",
    "]\n",
    "data_path = './data/'\n",
    "landing_step = Landing(landing_path=data_path)\n",
    "landing_step.extract(url=url)\n",
    "landing_step.transform()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preparação do Datalake"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "O banco de dados datalake já existe.\n",
      "Schema 'bronze' criado com sucesso.\n",
      "O Schema 'bronze' ja existe.\n",
      "O Schema 'silver' ja existe.\n",
      "O Schema 'gold' ja existe.\n"
     ]
    }
   ],
   "source": [
    "db.criar_database()\n",
    "db.criar_schemas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Camada Bronze - Ingestão e Análise dos Dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Iniciando a estapa bronze.\n",
      "Criando a tabela 'listings' no schema 'bronze'....\n",
      "A tabela 'listings' foi criada no schema 'bronze', e os dados foram inseridos.\n",
      "Criando a tabela 'reviews' no schema 'bronze'....\n",
      "A tabela 'reviews' foi criada no schema 'bronze', e os dados foram inseridos.\n",
      "Criando a tabela 'calendar' no schema 'bronze'....\n",
      "A tabela 'calendar' foi criada no schema 'bronze', e os dados foram inseridos.\n",
      "Etapas bronze executada com sucesso!!\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    print(\"Iniciando a estapa bronze.\")\n",
    "\n",
    "    ## Listings\n",
    "    data_path = './data/'\n",
    "    df_listings = pd.read_csv(data_path + 'listings.csv') # Carregando o csv listings em um Dataframe Python\n",
    "    db.criar_tabela_df(schema='bronze',nome_tabela='listings', df=df_listings,if_exists='replace') \n",
    "    del df_listings # Limpeza de memória\n",
    "\n",
    "    ## Reviews\n",
    "    df_reviews = pd.read_csv(data_path + 'reviews.csv') # Carregando o csv reviews em um Dataframe Python\n",
    "    db.criar_tabela_df(schema='bronze',nome_tabela='reviews', df=df_reviews,if_exists='replace')\n",
    "    del df_reviews # Limpeza de memória\n",
    "\n",
    "    ## Calendar\n",
    "    df_calendar = pd.read_csv(data_path + 'calendar.csv',nrows=1000000) # Carregando o csv calendar em um Dataframe Python\n",
    "    db.criar_tabela_df(schema='bronze',nome_tabela='calendar', df=df_calendar,if_exists='replace')\n",
    "    del df_calendar # Limpeza de memória    \n",
    "\n",
    "    print(\"Etapas bronze executada com sucesso!!\")\n",
    "\n",
    "except Exception as e:\n",
    "    print(\"Ocorreu um erro inesperado ao efetua a etapa bronze:\", e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Etapa 2:\n",
    "\n",
    "**Data Clean - Camada Silver**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   - Identifique e lide com valores ausentes, duplicatas e outliers nos dados brutos da camada \"bronze\".\n",
    "   - Padronize e limpe os nomes das colunas, convertendo-os em um formato consistente.\n",
    "   - Realize uma limpeza textual em campos, como descrições de propriedades, removendo caracteres especiais e erros de digitação."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Camada Silver - Limpeza de Dados e Criação de colunas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Leitura das tabelas bronze"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT * FROM bronze.listings\n",
    "\"\"\"\n",
    "df_silver_listings = pd.read_sql(sql=sql_text(query), con=engine.connect())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT * FROM bronze.reviews\n",
    "\"\"\"\n",
    "df_silver_reviews = pd.read_sql(sql=sql_text(query), con=engine.connect())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT * FROM bronze.calendar\n",
    "\"\"\"\n",
    "df_silver_calendar = pd.read_sql(sql=sql_text(query), con=engine.connect())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importação da biblioteca de limpeza"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from modules.utils import data_profiling, remove_special_caracters, process_dataframe, remove_outliers, change_t_for_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Relatório com informações do perfil dos dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data_profiling(df_silver_listings, 'listings', 'data_profiling/')\n",
    "# data_profiling(df_silver_reviews, 'reviews', 'data_profiling/')\n",
    "# data_profiling(df_silver_calendar, 'calendar', 'data_profiling/')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processamento da tabela **listings**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Limpeza de caracteres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_silver_listings[\"neighborhood_overview\"] = df_silver_listings[\"neighborhood_overview\"].apply(remove_special_caracters)\n",
    "df_silver_listings[\"host_about\"] = df_silver_listings[\"host_about\"].apply(remove_special_caracters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Identificar e lidar com valores ausentes, duplicatas e outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Analisando a tabela listings\n",
      "\n",
      "Removendo colunas que possuem 100% de valores faltantes ...\n",
      "Colunas removidas da tabela  listings : \n",
      " ['description', 'license', 'calendar_updated', 'bedrooms', 'neighbourhood_group_cleansed', 'bathrooms']\n",
      "\n",
      "Removendo dados duplicados ...\n",
      "Foram removidas  0 linhas da tabela listings\n",
      "\n",
      "Removendo colunas constantes ...\n",
      "Colunas constantes removidas da tabela listings: \n",
      " ['scrape_id', 'amenities']\n",
      "\n",
      "Quantidade de linhas (outliers) eliminadas:  27265\n"
     ]
    }
   ],
   "source": [
    "# colunas para considerar a eliminação de outliers\n",
    "cols_listings = ['review_scores_communication', 'review_scores_location', 'review_scores_value', 'reviews_per_month']\n",
    "\n",
    "df_process = process_dataframe(df_silver_listings, 'listings')\n",
    "df_silver_listings = remove_outliers(df_process, cols_listings)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Conversão do T e F para 1 e 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_listings2 = ['host_is_superhost', 'host_has_profile_pic', 'host_identity_verified', 'instant_bookable']\n",
    "\n",
    "for col in cols_listings2:\n",
    "    df_silver_listings[col] = df_silver_listings[col].apply(change_t_for_1).astype(\"int\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Transformação da coluna bathroom_text em 2 colunas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['1 bath', '1.5 baths', '5 baths', '2 baths', '7 baths', '3 baths',\n",
       "       '1 shared bath', '4 baths', '2.5 baths', '5 shared baths',\n",
       "       '1 private bath', '2.5 shared baths', '2 shared baths',\n",
       "       '4.5 baths', '0 shared baths', 'Shared half-bath',\n",
       "       '1.5 shared baths', '0 baths', None, '3.5 baths', '6 baths',\n",
       "       '3 shared baths', '5.5 baths', '7.5 baths', '3.5 shared baths',\n",
       "       '4.5 shared baths', '6.5 shared baths', '6.5 baths', '10 baths',\n",
       "       '4 shared baths', '16 baths', '8 baths', 'Half-bath'], dtype=object)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_silver_listings['bathrooms_text'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preencha os valores nulos na coluna 'bathrooms_text' com '0 bath'\n",
    "df_silver_listings['bathrooms_text'] = df_silver_listings['bathrooms_text'].fillna('0 bath')\n",
    "    \n",
    "# Defina o tipo de banheiro\n",
    "df_silver_listings['bathroom_type'] = df_silver_listings['bathrooms_text'].str.replace(r'(\\d+\\.?\\d*)', '', regex=True)\n",
    "    \n",
    "# Remova 's' do final de 'baths e traz o strip()'\n",
    "df_silver_listings['bathroom_type'] = df_silver_listings['bathroom_type'].str.replace(r's?$', '', regex=True).apply(lambda x: x.strip())\n",
    "    \n",
    "# Extraia o número de banheiros usando expressões regulares\n",
    "df_silver_listings['bathroom_quantity'] = df_silver_listings['bathrooms_text'].str.extract(r'(\\d+\\.?\\d*)')[0].astype(float)\n",
    "    \n",
    "# Converta a coluna 'bathroom_quantity' para tipo numérico\n",
    "df_silver_listings['bathroom_quantity'] = pd.to_numeric(df_silver_listings['bathroom_quantity'], errors='coerce').fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['bath', 'shared bath', 'private bath', 'Shared half-bath',\n",
       "       'Half-bath'], dtype=object)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_silver_listings['bathroom_type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bathroom_type</th>\n",
       "      <th>bathroom_quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bath</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bath</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bath</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bath</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bath</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32278</th>\n",
       "      <td>bath</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32498</th>\n",
       "      <td>bath</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32739</th>\n",
       "      <td>bath</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33052</th>\n",
       "      <td>bath</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33152</th>\n",
       "      <td>bath</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8743 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      bathroom_type  bathroom_quantity\n",
       "0              bath                1.0\n",
       "1              bath                1.0\n",
       "2              bath                1.5\n",
       "3              bath                1.0\n",
       "4              bath                1.0\n",
       "...             ...                ...\n",
       "32278          bath                1.0\n",
       "32498          bath                1.0\n",
       "32739          bath                2.0\n",
       "33052          bath                1.0\n",
       "33152          bath                1.0\n",
       "\n",
       "[8743 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_silver_listings[[\"bathroom_type\",'bathroom_quantity']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processamento da tabela **reviews**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Limpeza de caracteres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_silver_reviews[\"comments\"] = df_silver_reviews[\"comments\"].apply(remove_special_caracters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Identificar e lidar com valores ausentes, duplicatas e outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Analisando a tabela reviews\n",
      "\n",
      "Removendo colunas que possuem 100% de valores faltantes ...\n",
      "Colunas removidas da tabela  reviews : \n",
      " []\n",
      "\n",
      "Removendo dados duplicados ...\n",
      "Foram removidas  0 linhas da tabela reviews\n",
      "\n",
      "Removendo colunas constantes ...\n",
      "Colunas constantes removidas da tabela reviews: \n",
      " []\n"
     ]
    }
   ],
   "source": [
    "# colunas para considerar a eliminação de outliers\n",
    "# cols_reviews = []\n",
    "\n",
    "df_process = process_dataframe(df_silver_reviews, 'reviews')\n",
    "# df_silver_reviews = remove_outliers(df_process, cols_reviews)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processamento da tabela **calendar**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Limpeza de caracteres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_silver_calendar[\"price\"] = df_silver_calendar[\"price\"].apply(remove_special_caracters).astype(\"float\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Identificar e lidar com valores ausentes, duplicatas e outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Analisando a tabela calendar\n",
      "\n",
      "Removendo colunas que possuem 100% de valores faltantes ...\n",
      "Colunas removidas da tabela  calendar : \n",
      " ['adjusted_price']\n",
      "\n",
      "Removendo dados duplicados ...\n",
      "Foram removidas  0 linhas da tabela calendar\n",
      "\n",
      "Removendo colunas constantes ...\n",
      "Colunas constantes removidas da tabela calendar: \n",
      " []\n",
      "\n",
      "Quantidade de linhas (outliers) eliminadas:  40153\n"
     ]
    }
   ],
   "source": [
    "# colunas para considerar a eliminação de outliers\n",
    "cols_calendar = ['price']\n",
    "\n",
    "df_process = process_dataframe(df_silver_calendar, 'calendar')\n",
    "df_silver_calendar = remove_outliers(df_process, cols_calendar)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Conversão do T e F para 1 e 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_silver_calendar['available'] = df_silver_calendar['available'].apply(change_t_for_1).astype(\"int\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Etapa 3:\n",
    "\n",
    "**Data Quality - Camada Silver**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   - Defina métricas de qualidade de dados, como integridade, precisão e consistência para os dados da camada \"bronze\".\n",
    "   - Implemente verificações para garantir que os dados da camada \"silver\" estejam em conformidade com essas métricas.\n",
    "   - Estabeleça um sistema de monitoramento contínuo da qualidade dos dados da camada \"silver\"."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importação da funções de verificação"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "from modules.utils import check_missing, check_date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processamento da tabela **listings**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Preenchimento dos campos nulos com valores fixos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "host_about                   3553\n",
       "neighborhood_overview        3001\n",
       "neighbourhood                3001\n",
       "host_neighbourhood           2085\n",
       "host_location                1128\n",
       "host_response_time            508\n",
       "host_response_rate            508\n",
       "host_acceptance_rate          344\n",
       "price                         196\n",
       "has_availability              196\n",
       "beds                           65\n",
       "host_name                       2\n",
       "host_listings_count             2\n",
       "host_total_listings_count       2\n",
       "host_verifications              2\n",
       "host_thumbnail_url              2\n",
       "host_since                      2\n",
       "host_picture_url                2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Colunas com dados faltantes \n",
    "check_missing(df_silver_listings).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preenchimento dos campos nulos com \"no_info\"\n",
    "\n",
    "cols_no_info = ['neighborhood_overview', 'host_name', 'host_location', 'host_about', 'host_response_time', 'host_thumbnail_url', 'host_picture_url', 'host_neighbourhood', 'host_verifications']\n",
    "\n",
    "for column in cols_no_info:\n",
    "    df_silver_listings[column] = df_silver_listings[column].fillna(\"no_info\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preenchimento dos campos nulos com outros valores\n",
    "\n",
    "# Coluna 'neighbourhood' preencher com 'Rio de Janeiro'\n",
    "df_silver_listings['neighbourhood'] = df_silver_listings['neighbourhood'].fillna(\"Rio de Janeiro\")\n",
    "\n",
    "\n",
    "# Coluna 'beds' preencher com valor 1 (mínimo necessário para hospedar) \n",
    "df_silver_listings['beds'] = df_silver_listings['beds'].fillna(1)\n",
    "\n",
    "\n",
    "# Coluna 'host_listings_count' e 'host_total_listings_count' preencher com valor 1 (the number of listings the host has)\n",
    "for col in ['host_listings_count','host_total_listings_count']:\n",
    "    df_silver_listings[col] = df_silver_listings[col].fillna(1)\n",
    "\n",
    "# Coluna 'has_availability' preencher com valor 't'\n",
    "df_silver_listings['has_availability'] = df_silver_listings['has_availability'].fillna('t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Coluna 'host_since' preencher com a data atual\n",
    "data_atual = pd.Timestamp.now().strftime('%Y-%m-%d')\n",
    "\n",
    "df_silver_listings['host_since'] = df_silver_listings['host_since'].fillna(data_atual)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Coluna 'price' preencher com a média de preço para hospedagens com quant de camas e banheiros semelhantes\n",
    "\n",
    "## limpeza de caracteres\n",
    "df_silver_listings[\"price\"] = df_silver_listings[\"price\"].apply(remove_special_caracters)\n",
    "\n",
    "## Coverter tipo para float\n",
    "df_silver_listings['price'] = df_silver_listings['price'].astype(\"float\")\n",
    "\n",
    "## Calcula a média dos preços agrupando pelos valores das colunas 'beds' e 'bathroom_quantity'\n",
    "mean_prices = df_silver_listings.groupby(['beds', 'bathroom_quantity'])['price'].mean()\n",
    "\n",
    "# Preenche os valores nulos na coluna 'price' com as médias calculadas\n",
    "df_silver_listings['price'] = df_silver_listings.apply(lambda row: mean_prices[row['beds'], row['bathroom_quantity']] if pd.isnull(row['price']) else row['price'], axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dropar coluna 'name'\n",
    "\n",
    "df_silver_listings.drop('name', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Colunas 'host_response_rate' e 'host_acceptance_rate'  preencher com \"0%\"\n",
    "\n",
    "for col in ['host_response_rate', 'host_acceptance_rate']:\n",
    "    df_silver_listings[col] = df_silver_listings[col].fillna('0%')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], dtype: int64)"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Verificar novamente dados faltantes \n",
    "check_missing(df_silver_listings).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Padronização dos nomes e tipos das colunas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Definir o tipo das colunas que não serão de texto\n",
    "list_int_listings = ['id', 'host_id', 'calculated_host_listings_count', 'calculated_host_listings_count_entire_homes', \n",
    "                     'calculated_host_listings_count_private_rooms', 'calculated_host_listings_count_shared_rooms',\n",
    "                     'host_listings_count','host_total_listings_count', 'beds', 'accommodates', 'minimum_nights', \n",
    "                     'maximum_nights', 'minimum_minimum_nights', 'maximum_minimum_nights', 'minimum_maximum_nights', \n",
    "                     'maximum_maximum_nights', 'availability_30', 'availability_60', 'availability_90', 'availability_365',\n",
    "                     'number_of_reviews', 'number_of_reviews_ltm', 'number_of_reviews_l30d']\n",
    "\n",
    "\n",
    "list_float_listings = ['review_scores_location', 'review_scores_value', 'reviews_per_month', 'price', 'minimum_nights_avg_ntm', \n",
    "                       'maximum_nights_avg_ntm', 'review_scores_rating', 'review_scores_accuracy', 'review_scores_cleanliness', \n",
    "                       'review_scores_checkin', 'review_scores_communication', 'review_scores_location', 'review_scores_value']\n",
    "\n",
    "\n",
    "list_datetime_listings = ['last_scraped', 'host_since', 'calendar_last_scraped', 'first_review', 'last_review']\n",
    "\n",
    "\n",
    "list_bool_listings = ['host_is_superhost', 'host_has_profile_pic', 'host_identity_verified', \n",
    "                      'has_availability', 'instant_bookable']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Definir tipos das colunas\n",
    "\n",
    "for column in list_int_listings:\n",
    "    # transforma a coluna para o tipo inteiro\n",
    "    df_silver_listings[column] = df_silver_listings[column].astype(\"int\")\n",
    "\n",
    "for column in list_float_listings:\n",
    "    # transforma a coluna para o tipo float\n",
    "    df_silver_listings[column] = df_silver_listings[column].astype(\"float\")\n",
    "\n",
    "for column in list_datetime_listings:\n",
    "    # transforma a coluna para o tipo \"datetime\" com o formato passado\n",
    "    df_silver_listings[column] = pd.to_datetime(df_silver_listings[column], format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Conversão do T e F para 1 e 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_silver_listings['has_availability'] = df_silver_listings['has_availability'].apply(change_t_for_1).astype(\"int\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>host_is_superhost</th>\n",
       "      <th>host_has_profile_pic</th>\n",
       "      <th>host_identity_verified</th>\n",
       "      <th>has_availability</th>\n",
       "      <th>instant_bookable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32278</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32498</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32739</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33052</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33152</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8743 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       host_is_superhost  host_has_profile_pic  host_identity_verified  \\\n",
       "0                      1                     1                       1   \n",
       "1                      1                     1                       1   \n",
       "2                      1                     1                       1   \n",
       "3                      0                     1                       1   \n",
       "4                      0                     1                       0   \n",
       "...                  ...                   ...                     ...   \n",
       "32278                  0                     1                       1   \n",
       "32498                  0                     1                       1   \n",
       "32739                  0                     1                       0   \n",
       "33052                  0                     1                       1   \n",
       "33152                  0                     1                       1   \n",
       "\n",
       "       has_availability  instant_bookable  \n",
       "0                     1                 0  \n",
       "1                     1                 0  \n",
       "2                     1                 0  \n",
       "3                     1                 0  \n",
       "4                     1                 0  \n",
       "...                 ...               ...  \n",
       "32278                 1                 1  \n",
       "32498                 1                 0  \n",
       "32739                 1                 0  \n",
       "33052                 1                 0  \n",
       "33152                 1                 0  \n",
       "\n",
       "[8743 rows x 5 columns]"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_silver_listings[list_bool_listings] # Confirmando a alteração"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Verificação da precisão dos dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Analisando datas da coluna last_scraped\n",
      "  - Nenhum valor de data fora do intervalo\n",
      "\n",
      "Analisando datas da coluna host_since\n",
      "  - Nenhum valor de data fora do intervalo\n",
      "\n",
      "Analisando datas da coluna calendar_last_scraped\n",
      "  - Nenhum valor de data fora do intervalo\n",
      "\n",
      "Analisando datas da coluna first_review\n",
      "  - Nenhum valor de data fora do intervalo\n",
      "\n",
      "Analisando datas da coluna last_review\n",
      "  - Nenhum valor de data fora do intervalo\n",
      "\n"
     ]
    }
   ],
   "source": [
    "## Verificação de datas \n",
    "\n",
    "for col in list_datetime_listings:\n",
    "    df_filtrado, valores_fora_intervalo = check_date(df_silver_listings, col)\n",
    "\n",
    "    print('Analisando datas da coluna ' + col)\n",
    "\n",
    "    if not valores_fora_intervalo.notnull().sum().any():\n",
    "        print('  - Nenhum valor de data fora do intervalo\\n')\n",
    "    else:\n",
    "        print('  - Existem bad datas na coluna\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>listing_url</th>\n",
       "      <th>last_scraped</th>\n",
       "      <th>source</th>\n",
       "      <th>neighborhood_overview</th>\n",
       "      <th>picture_url</th>\n",
       "      <th>host_id</th>\n",
       "      <th>host_url</th>\n",
       "      <th>host_name</th>\n",
       "      <th>host_since</th>\n",
       "      <th>...</th>\n",
       "      <th>review_scores_location</th>\n",
       "      <th>review_scores_value</th>\n",
       "      <th>instant_bookable</th>\n",
       "      <th>calculated_host_listings_count</th>\n",
       "      <th>calculated_host_listings_count_entire_homes</th>\n",
       "      <th>calculated_host_listings_count_private_rooms</th>\n",
       "      <th>calculated_host_listings_count_shared_rooms</th>\n",
       "      <th>reviews_per_month</th>\n",
       "      <th>bathroom_type</th>\n",
       "      <th>bathroom_quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 68 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id, listing_url, last_scraped, source, neighborhood_overview, picture_url, host_id, host_url, host_name, host_since, host_location, host_about, host_response_time, host_response_rate, host_acceptance_rate, host_is_superhost, host_thumbnail_url, host_picture_url, host_neighbourhood, host_listings_count, host_total_listings_count, host_verifications, host_has_profile_pic, host_identity_verified, neighbourhood, neighbourhood_cleansed, latitude, longitude, property_type, room_type, accommodates, bathrooms_text, beds, price, minimum_nights, maximum_nights, minimum_minimum_nights, maximum_minimum_nights, minimum_maximum_nights, maximum_maximum_nights, minimum_nights_avg_ntm, maximum_nights_avg_ntm, has_availability, availability_30, availability_60, availability_90, availability_365, calendar_last_scraped, number_of_reviews, number_of_reviews_ltm, number_of_reviews_l30d, first_review, last_review, review_scores_rating, review_scores_accuracy, review_scores_cleanliness, review_scores_checkin, review_scores_communication, review_scores_location, review_scores_value, instant_bookable, calculated_host_listings_count, calculated_host_listings_count_entire_homes, calculated_host_listings_count_private_rooms, calculated_host_listings_count_shared_rooms, reviews_per_month, bathroom_type, bathroom_quantity]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 68 columns]"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Verificação de preços  \n",
    "df_silver_listings[df_silver_listings['price']<=0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processamento da tabela **reviews**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Preenchimento dos campos nulos com valores fixos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "comments    20\n",
       "dtype: int64"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Colunas com dados faltantes + quant de dados faltantes\n",
    "check_missing(df_silver_reviews).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_silver_reviews['comments'] = df_silver_reviews['comments'].fillna(\"no_info\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], dtype: int64)"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Verificar novamente dados faltantes \n",
    "check_missing(df_silver_reviews).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Padronização dos nomes e tipos das colunas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Definir o tipo das colunas que não serão de texto\n",
    "list_int_reviews = ['listing_id', 'id', 'reviewer_id']\n",
    "list_datetime_reviews = ['date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "listing_id        int64\n",
       "id                int64\n",
       "date             object\n",
       "reviewer_id       int64\n",
       "reviewer_name    object\n",
       "comments         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_silver_reviews.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Apenas a alteração do tipo da coluna 'date' é necessária.\n",
    "df_silver_reviews['date'] = pd.to_datetime(df_silver_reviews['date'], format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "listing_id                int64\n",
       "id                        int64\n",
       "date             datetime64[ns]\n",
       "reviewer_id               int64\n",
       "reviewer_name            object\n",
       "comments                 object\n",
       "dtype: object"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_silver_reviews.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Verificação da precisão dos dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Verificação de datas \n",
    "\n",
    "df_filtrado, valores_fora_intervalo = check_date(df_silver_reviews, 'date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>listing_id</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>reviewer_id</th>\n",
       "      <th>reviewer_name</th>\n",
       "      <th>comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [listing_id, id, date, reviewer_id, reviewer_name, comments]\n",
       "Index: []"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Nenhum valor de data fora do intervalo \n",
    "valores_fora_intervalo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processamento da tabela **calendar**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Preenchimento dos campos nulos com o valor que mais se repete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "minimum_nights    3\n",
       "maximum_nights    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Colunas com dados faltantes + quant de dados faltantes\n",
    "check_missing(df_silver_calendar).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "minimum_freq = df_silver_calendar['minimum_nights'].value_counts().idxmax()\n",
    "maximum_freq = df_silver_calendar['maximum_nights'].value_counts().idxmax()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_silver_calendar['minimum_nights'] = df_silver_calendar['minimum_nights'].fillna(minimum_freq)\n",
    "df_silver_calendar['maximum_nights'] = df_silver_calendar['maximum_nights'].fillna(maximum_freq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], dtype: int64)"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Verificar novamente dados faltantes \n",
    "check_missing(df_silver_calendar).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Padronização dos nomes e tipos das colunas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Definir o tipo das colunas que não serão de texto\n",
    "list_int_calendar = ['listing_id', 'minimum_nights', 'maximum_nights']\n",
    "list_datetime_calendar = ['date']\n",
    "list_float_calendar = ['price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "listing_id          int64\n",
       "date               object\n",
       "available          object\n",
       "price             float64\n",
       "minimum_nights    float64\n",
       "maximum_nights    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_silver_calendar.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Nessário apenas alterar o tipo das colunas 'date','minimum_nights', 'maximum_nights'\n",
    "\n",
    "df_silver_calendar['date'] = pd.to_datetime(df_silver_calendar['date'], format='%Y-%m-%d')\n",
    "\n",
    "for column in ['minimum_nights', 'maximum_nights']:\n",
    "    #transforma a coluna para o tipo inteiro\n",
    "    df_silver_calendar[column] = df_silver_calendar[column].astype(\"int\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "listing_id                 int64\n",
       "date              datetime64[ns]\n",
       "available                 object\n",
       "price                    float64\n",
       "minimum_nights             int64\n",
       "maximum_nights             int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Verificar novamente tipos das colunas\n",
    "df_silver_calendar.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Verificação da precisão dos dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Verificação de datas \n",
    "\n",
    "df_filtrado, valores_fora_intervalo = check_date(df_silver_calendar, 'date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>listing_id</th>\n",
       "      <th>date</th>\n",
       "      <th>available</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>maximum_nights</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [listing_id, date, available, price, minimum_nights, maximum_nights]\n",
       "Index: []"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Nenhum valor de data fora do intervalo \n",
    "valores_fora_intervalo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>listing_id</th>\n",
       "      <th>date</th>\n",
       "      <th>available</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>maximum_nights</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [listing_id, date, available, price, minimum_nights, maximum_nights]\n",
       "Index: []"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Verificação de preços  \n",
    "\n",
    "df_silver_calendar[df_silver_calendar['price']<=0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Etapa 4:\n",
    "\n",
    "**Testes de Qualidade - Camada Silver**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   - Utilize a biblioteca Great Expectations para criar testes de qualidade automatizados que verifiquem as expectativas definidas para os dados da camada \"silver\".\n",
    "   - Desenvolva testes que assegurem que os dados da camada \"silver\" atendam às regras de negócios e aos requisitos de qualidade."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import modules.utils as utils\n",
    "import great_expectations as gx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Listings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "gx_df_listings = gx.from_pandas(df_silver_listings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# COLUNAS DATEIME\n",
      "A coluna 'last_scraped' é válida. (Tipo e Valor)\n",
      "A coluna 'host_since' é válida. (Tipo e Valor)\n",
      "A coluna 'calendar_last_scraped' é válida. (Tipo e Valor)\n",
      "A coluna 'last_review' é válida. (Tipo e Valor)\n",
      "A coluna 'first_review' é válida. (Tipo e Valor)\n",
      "\n",
      "# COLUNAS CATEGORICAS\n",
      "Os valores da coluna source contêm apenas valores esperados. (['city scrape', 'previous scrape'])\n",
      "Os valores da coluna room_type contêm apenas valores esperados. (['Entire home/apt', 'Private room', 'Shared room', 'Hotel room'])\n",
      "\n",
      "# COLUNAS BOOLEANAS\n",
      "A coluna 'host_is_superhost' é válida. (Tipo e Valor)\n",
      "A coluna 'host_has_profile_pic' é válida. (Tipo e Valor)\n",
      "A coluna 'host_identity_verified' é válida. (Tipo e Valor)\n",
      "A coluna 'instant_bookable' é válida. (Tipo e Valor)\n",
      "A coluna 'has_availability' é válida. (Tipo e Valor)\n",
      "\n",
      "# COLUNAS NÃO VAZIAS\n",
      "A coluna 'review_scores_rating' é válida. (Não tem valores nulos)\n",
      "A coluna 'review_scores_accuracy' é válida. (Não tem valores nulos)\n",
      "A coluna 'review_scores_cleanliness' é válida. (Não tem valores nulos)\n",
      "A coluna 'review_scores_checkin' é válida. (Não tem valores nulos)\n",
      "A coluna 'review_scores_communication' é válida. (Não tem valores nulos)\n",
      "A coluna 'review_scores_location' é válida. (Não tem valores nulos)\n",
      "A coluna 'review_scores_value' é válida. (Não tem valores nulos)\n",
      "A coluna 'reviews_per_month' é válida. (Não tem valores nulos)\n"
     ]
    }
   ],
   "source": [
    "# Verificando os tipos das colunas\n",
    "print(\"# COLUNAS DATEIME\")\n",
    "colunas_datetime = ['last_scraped', 'host_since', 'calendar_last_scraped', 'last_review', 'first_review']\n",
    "utils.verifica_colunas_datetime(gx_df_listings, colunas_datetime)\n",
    "\n",
    "# Checando os valores Categóricos\n",
    "print(\"\\n# COLUNAS CATEGORICAS\")\n",
    "coluna_a_ser_analisada, valores_esperados = 'source', ['city scrape', 'previous scrape']\n",
    "utils.verificar_colunas_categoricas(gx_df_listings,coluna_a_ser_analisada, valores_esperados)\n",
    "\n",
    "coluna_a_ser_analisada, valores_esperados = 'room_type', ['Entire home/apt', 'Private room', 'Shared room', 'Hotel room']\n",
    "utils.verificar_colunas_categoricas(gx_df_listings,coluna_a_ser_analisada, valores_esperados)\n",
    "\n",
    "# Checando as colunas Booleanas\n",
    "print(\"\\n# COLUNAS BOOLEANAS\")\n",
    "colunas_booleanas = ['host_is_superhost','host_has_profile_pic','host_identity_verified','instant_bookable','has_availability']\n",
    "utils.verificar_colunas_booleanas(gx_df=gx_df_listings,list_of_columns=colunas_booleanas)\n",
    "\n",
    "# Verificando nulos\n",
    "print(\"\\n# COLUNAS NÃO VAZIAS\")\n",
    "colunas_nao_vazias = ['review_scores_rating', 'review_scores_accuracy', 'review_scores_cleanliness', 'review_scores_checkin', 'review_scores_communication', 'review_scores_location', 'review_scores_value', 'reviews_per_month']\n",
    "utils.verificar_colunas_com_none(gx_df=gx_df_listings,list_of_columns=colunas_nao_vazias)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "gx_df_reviews = gx.from_pandas(df_silver_reviews)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "# COLUNAS ID\n",
      "Verificando as colunas  id, reviewer_id\n",
      "A coluna 'id' é válida. (Tipo e Valor)\n",
      "A coluna 'reviewer_id' é válida. (Tipo e Valor)\n",
      "\n",
      " COLUNAS DATETIME\n",
      "A coluna 'date' é válida. (Tipo e Valor)\n"
     ]
    }
   ],
   "source": [
    "# Verificando valores\n",
    "print(\"\\n# COLUNAS ID\")\n",
    "colunas_a_verificar = [\"id\",\"reviewer_id\"]\n",
    "print(f\"Verificando as colunas \",\", \".join(colunas_a_verificar))\n",
    "utils.verificar_colunas_id(gx_df=gx_df_reviews,list_of_id_columns=colunas_a_verificar)\n",
    "\n",
    "print(\"\\n COLUNAS DATETIME\")\n",
    "colunas_a_verificar = ['date']\n",
    "utils.verifica_colunas_datetime(gx_df=gx_df_reviews,colunas_datetime=colunas_a_verificar)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calendar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>listing_id</th>\n",
       "      <th>date</th>\n",
       "      <th>available</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>maximum_nights</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17878</td>\n",
       "      <td>2023-12-27</td>\n",
       "      <td>0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17878</td>\n",
       "      <td>2023-12-28</td>\n",
       "      <td>0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17878</td>\n",
       "      <td>2023-12-29</td>\n",
       "      <td>0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17878</td>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17878</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   listing_id        date  available  price  minimum_nights  maximum_nights\n",
       "0       17878  2023-12-27          0  350.0             5.0            28.0\n",
       "1       17878  2023-12-28          0  350.0             5.0            28.0\n",
       "2       17878  2023-12-29          0  350.0             5.0            28.0\n",
       "3       17878  2023-12-30          0  350.0             5.0            28.0\n",
       "4       17878  2023-12-31          0  350.0             5.0            28.0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_silver_calendar.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "gx_df_calendar = gx.from_pandas(df_silver_calendar)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "# COLUNAS BOOLEANAS\n",
      "A coluna 'available' é válida. (Tipo e Valor)\n",
      "\n",
      "# COLUNAS NÃO VAZIAS\n",
      "A coluna 'listing_id' é válida. (Não tem valores nulos)\n",
      "A coluna 'date' é válida. (Não tem valores nulos)\n",
      "A coluna 'available' é válida. (Não tem valores nulos)\n",
      "A coluna 'price' é válida. (Não tem valores nulos)\n",
      "ERRO: A coluna 'minimum_nights' contém valores nulos.\n",
      "ERRO: A coluna 'maximum_nights' contém valores nulos.\n",
      "\n",
      "# COLUNAS COM VALORES A ANALIZAR\n",
      "A coluna 'price' está entre os valores de 0 e 10000.\n"
     ]
    }
   ],
   "source": [
    "# Checando as colunas Booleanas\n",
    "print(\"\\n# COLUNAS BOOLEANAS\")\n",
    "colunas_booleanas = ['available']\n",
    "utils.verificar_colunas_booleanas(gx_df=gx_df_calendar,list_of_columns=colunas_booleanas)\n",
    "\n",
    "# Verificando nulos\n",
    "print(\"\\n# COLUNAS NÃO VAZIAS\")\n",
    "colunas_nao_vazias = ['listing_id','date','available','price','minimum_nights','maximum_nights']\n",
    "utils.verificar_colunas_com_none(gx_df=gx_df_calendar,list_of_columns=colunas_nao_vazias)\n",
    "\n",
    "# Verificando valores\n",
    "print(\"\\n# COLUNAS COM VALORES A ANALIZAR\")\n",
    "colunas_valores = [\"price\"]\n",
    "utils.verificar_valores_min_max(gx_df=gx_df_calendar,list_of_columns=colunas_valores,min=0,max=10000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Etapa 5:\n",
    "\n",
    "**Transformação de Dados com dbt - Camada Silver**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   - Utilize a ferramenta dbt para criar a camada \"silver\" de dados, realizando transformações e preparando os dados da camada em questão.\n",
    "   - Mantenha um controle de versão dos modelos dbt relacionados à camada \"silver\" e automatize a execução das transformações."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OBS: Essa etapa é feita utilizando a estrutura do DBT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Etapa 6:\n",
    "\n",
    "**Armazenamento de Dados em PostgreSQL - Camada Silver**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   - Armazene os dados da camada \"silver\" no mesmo banco de dados PostgreSQL.\n",
    "   - Estabeleça conexões entre o dbt e o PostgreSQL para carregar os dados transformados da camada \"silver\" no banco."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Etapa 7:\n",
    "\n",
    "**Validação de Expectativas com Great Expectations - Camada Silver**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   - Implemente validações adicionais usando Great Expectations nas camadas de dados da camada \"silver\".\n",
    "   - Monitore a qualidade dos dados da camada \"silver\" após cada transformação e ajuste os testes de acordo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Etapa 8:\n",
    "\n",
    "**Transformação de Dados com dbt - Camada Gold**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   - Utilize o dbt para criar a camada \"gold\" de dados, aplicando agregações especializadas, como médias de preços por propriedade, por período, e outras agregações especializadas.\n",
    "   - Mantenha um controle de versão dos modelos dbt relacionados à camada \"gold\" e automatize a execução das transformações.\n",
    "   - Armazene os dados da camada \"gold\" no mesmo banco de dados PostgreSQL, mantendo a estrutura de dados otimizada para consultas analíticas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}