dataCleanSP.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "741404f5-08e2-4d26-a599-4b6b0715b9fc",
   "metadata": {},
   "source": [
    "# Projeto - Análise do preço das casas de São Paulo\n",
    "\n",
    "### Autor: Guilherme Iram\n",
    "### Fonte dos dados: https://www.kaggle.com/datasets/kaggleshashankk/house-price-data-of-sao-paulo\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "072f32c9-58a7-4c29-b9ad-5ec104b88dd3",
   "metadata": {},
   "source": [
    "# Informações sobre o dataset\n",
    "- Street (Rua) - Localização da rua a qual a casa pertence\n",
    "- City (Cidade) - Cidade que a casa está localizada\n",
    "- Propertycard__detailvalue (Área) - Área em metros quadrados da casa\n",
    "- Quartos (Quartos) - Número de quartos da casa\n",
    "- Banheiros (Banheiros) - Número de banheiros da casa\n",
    "- Vagas (Vagas) - Vagas de estacionamento por veículo\n",
    "- Price (Custo) - Preço da casa em BRL (real brasileiro)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38931407-a527-4e82-b20d-ca80e607b36f",
   "metadata": {},
   "source": [
    "## Objetivos da análises:\n",
    "- Baseado nas características quantitativas do dataset, fazer uma análise visual desses atributos e propor um modelo de regressão.\n",
    "- Sugerir uma previsão do preço da casa baseado nas características desejadas pelo cliente."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c12d0610-ca86-44cf-872e-39d24f9b2d43",
   "metadata": {},
   "source": [
    "## 1 - Carregando as bibliotecas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8aa1d5fc-7982-47c0-9c13-c54d8eb047b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import statistics as st"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "67a9a9ad-1e3c-4705-ae31-3d34436ceb45",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acca1019-afdb-412f-9ce1-df48a8544081",
   "metadata": {},
   "source": [
    "## 2 - Carregando o dataset, tratamento & análises prévias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "028a38e3-06ce-4b08-917f-d7cab1995e1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sp = pd.read_excel(\"./dados/SaoPaulo.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "db0803a3-4461-4f71-865d-014e9750126a",
   "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>Street</th>\n",
       "      <th>City</th>\n",
       "      <th>propertycard__detailvalue</th>\n",
       "      <th>quartos</th>\n",
       "      <th>banheiros</th>\n",
       "      <th>vagas</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avenida Itacira, 255</td>\n",
       "      <td>Planalto Paulista, São Paulo</td>\n",
       "      <td>1000</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>R$ 7.000.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rua Aurelia Perez Alvarez, 42</td>\n",
       "      <td>Jardim dos Estados, São Paulo</td>\n",
       "      <td>469-524</td>\n",
       "      <td>3-6</td>\n",
       "      <td>3-6</td>\n",
       "      <td>4</td>\n",
       "      <td>R$ 3.700.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rua Alba Valdez</td>\n",
       "      <td>Jardim Reimberg, São Paulo</td>\n",
       "      <td>125</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>R$ 380.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jardim Morumbi, São Paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>310</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>R$ 685.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rua Tobias Barreto, 195</td>\n",
       "      <td>Mooca, São Paulo</td>\n",
       "      <td>100</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>R$ 540.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Street                             City  \\\n",
       "0           Avenida Itacira, 255     Planalto Paulista, São Paulo    \n",
       "1  Rua Aurelia Perez Alvarez, 42    Jardim dos Estados, São Paulo    \n",
       "2                Rua Alba Valdez       Jardim Reimberg, São Paulo    \n",
       "3      Jardim Morumbi, São Paulo                                SP   \n",
       "4        Rua Tobias Barreto, 195                 Mooca, São Paulo    \n",
       "\n",
       "  propertycard__detailvalue quartos banheiros vagas         price  \n",
       "0                      1000       4         8     6  R$ 7.000.000  \n",
       "1                   469-524     3-6       3-6     4  R$ 3.700.000  \n",
       "2                       125       4         3     2    R$ 380.000  \n",
       "3                       310       3         2     4    R$ 685.000  \n",
       "4                       100       3         2     2    R$ 540.000  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a70be416-636c-4cd8-abb4-99f26a680c29",
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_rename = {'Street': 'rua',\n",
    "              'City': 'cidade',\n",
    "              'propertycard__detailvalue': 'area_metro_quadrado',\n",
    "              'quartos': 'quartos',\n",
    "              'banheiros': 'banheiros',\n",
    "              'vagas': 'vagas',\n",
    "              'price': 'preco'}\n",
    "df_sp.rename(columns=dict_rename, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f7efcb66-2af5-48cf-ae7a-b763fad7713b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10033 entries, 0 to 10032\n",
      "Data columns (total 7 columns):\n",
      " #   Column               Non-Null Count  Dtype \n",
      "---  ------               --------------  ----- \n",
      " 0   rua                  10033 non-null  object\n",
      " 1   cidade               10033 non-null  object\n",
      " 2   area_metro_quadrado  10033 non-null  object\n",
      " 3   quartos              10033 non-null  object\n",
      " 4   banheiros            10033 non-null  object\n",
      " 5   vagas                10033 non-null  object\n",
      " 6   preco                10033 non-null  object\n",
      "dtypes: object(7)\n",
      "memory usage: 548.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df_sp.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc920b38-7790-4e16-906b-bced80e79f79",
   "metadata": {},
   "source": [
    " - Necessidade de efetuar limpesa e tratamento de dados \n",
    " - Limpar valores quantitativos, como os metros quadrados, número de quartos, banheiros e remover o cifrão do preço\n",
    " - Sem dados faltantes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee3b4913-d116-4514-8093-6352a0b5fd11",
   "metadata": {},
   "source": [
    "## 3 - Limpeza dos dados"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7df07b6a-174d-4cbe-b92e-264a3d14f9ed",
   "metadata": {},
   "source": [
    "### 3.1 - Tratando a coluna 'preco'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "af1f8f94-1cf6-4839-b205-6330fd978bb3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['R$ 7.000.000', 'R$ 3.700.000', 'R$ 380.000', ..., 'R$ 553.000',\n",
       "       'R$ 951.000', 'R$ 2.200\\n                      /Mês'], dtype=object)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp[\"preco\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0b247f7d-0bc6-4573-9a10-a8ec1b9f0441",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>R$</td>\n",
       "      <td>7.000.000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>R$</td>\n",
       "      <td>3.700.000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>R$</td>\n",
       "      <td>380.000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R$</td>\n",
       "      <td>685.000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R$</td>\n",
       "      <td>540.000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10028</th>\n",
       "      <td>R$</td>\n",
       "      <td>665.000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10029</th>\n",
       "      <td>R$</td>\n",
       "      <td>2.300.000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10030</th>\n",
       "      <td>R$</td>\n",
       "      <td>1.050.000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10031</th>\n",
       "      <td>R$</td>\n",
       "      <td>2.200</td>\n",
       "      <td>/Mês</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10032</th>\n",
       "      <td>R$</td>\n",
       "      <td>270.000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10033 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        0          1     2\n",
       "0      R$  7.000.000  None\n",
       "1      R$  3.700.000  None\n",
       "2      R$    380.000  None\n",
       "3      R$    685.000  None\n",
       "4      R$    540.000  None\n",
       "...    ..        ...   ...\n",
       "10028  R$    665.000  None\n",
       "10029  R$  2.300.000  None\n",
       "10030  R$  1.050.000  None\n",
       "10031  R$      2.200  /Mês\n",
       "10032  R$    270.000  None\n",
       "\n",
       "[10033 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp[\"preco\"].str.split(expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "10866936-913b-4b19-97bc-ab974fda109c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([None, '/Mês', '/Ano', '/Dia'], dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp[\"preco\"].str.split(expand = True)[2].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b41244d9-3153-47b7-9249-9c5104f7d56f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sp[[\"moeda\", \"preco_anuncio\", \"tipo_anuncio\"]] = df_sp[\"preco\"].str.split(expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "83588062-da17-4969-82c9-8d98ff306f90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['R$'], dtype=object)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp[\"moeda\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "903fa55c-bdab-433f-90fc-1c933595b051",
   "metadata": {},
   "outputs": [],
   "source": [
    "mask_none = df_sp[\"tipo_anuncio\"].isnull()\n",
    "df_sp_filter = df_sp[mask_none]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3f480912-c314-46a1-83ac-3aac9edd0e9f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 9181 entries, 0 to 10032\n",
      "Data columns (total 11 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   rua                  9181 non-null   object \n",
      " 1   cidade               9181 non-null   object \n",
      " 2   area_metro_quadrado  9181 non-null   object \n",
      " 3   quartos              9181 non-null   object \n",
      " 4   banheiros            9181 non-null   object \n",
      " 5   vagas                9181 non-null   object \n",
      " 6   preco                9181 non-null   object \n",
      " 7   moeda                9181 non-null   object \n",
      " 8   preco_anuncio        9181 non-null   object \n",
      " 9   tipo_anuncio         0 non-null      object \n",
      " 10  preco_anuncio_float  9181 non-null   float64\n",
      "dtypes: float64(1), object(10)\n",
      "memory usage: 860.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df_sp_filter[\"preco_anuncio_float\"] = df_sp_filter[\"preco_anuncio\"].str.replace(\".\",\"\").astype(float)\n",
    "df_sp_filter.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "863e3e3e-9842-4955-9dc0-5e602fe1c6ce",
   "metadata": {},
   "source": [
    "### 3.2 - Tratando a coluna 'area_metro_quadrado'\n",
    "\n",
    "- Aqui será feito uma convenção, para todos os valores no formato 'x-y', será usado o x para substituir a expressão.\n",
    "- Isso vale pras demais colunas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "4f6f5c79-47d3-4537-b69e-2d3a8c0da25d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['1000', '469-524', '125', '310', '100', '440', '145', '150',\n",
       "       '46-50', '114'], dtype=object)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"area_metro_quadrado\"].unique()[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f8a4c9d2-6114-4b3e-87aa-4bbf6c237bc3",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>469</td>\n",
       "      <td>524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>125</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>310</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10027</th>\n",
       "      <td>262</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10028</th>\n",
       "      <td>90</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10029</th>\n",
       "      <td>600</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10030</th>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10032</th>\n",
       "      <td>117</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9181 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          0     1\n",
       "0      1000  None\n",
       "1       469   524\n",
       "2       125  None\n",
       "3       310  None\n",
       "4       100  None\n",
       "...     ...   ...\n",
       "10027   262  None\n",
       "10028    90  None\n",
       "10029   600  None\n",
       "10030   200  None\n",
       "10032   117  None\n",
       "\n",
       "[9181 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"area_metro_quadrado\"].str.split(\"-\" ,expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2bf3f847-1952-4950-b238-2fe099f01ab1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sp_filter[\"area_metro_quadrado_int\"] = df_sp_filter[\"area_metro_quadrado\"].str.split(\"-\" ,expand=True)[0].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8d5259e-f3b7-4a89-b77c-d5dd1a900800",
   "metadata": {},
   "source": [
    "### 3.3 - Tratando a coluna 'quartos'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e0e78ecb-dd63-4908-a3e6-1a716267d579",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['4', '3-6', '3', '2', '7', '2-3', '5', '6', '1', '8', '11', '10',\n",
       "       '9', '13', '12', '16'], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"quartos\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "dfe9d270-9f76-42d6-b7d8-324bc6a58e89",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10027</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10028</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10029</th>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10030</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10032</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9181 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       0     1\n",
       "0      4  None\n",
       "1      3     6\n",
       "2      4  None\n",
       "3      3  None\n",
       "4      3  None\n",
       "...   ..   ...\n",
       "10027  3  None\n",
       "10028  2  None\n",
       "10029  8  None\n",
       "10030  3  None\n",
       "10032  3  None\n",
       "\n",
       "[9181 rows x 2 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"quartos\"].str.split(\"-\", expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3db78f52-858d-4742-aa4c-941092add0cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        4\n",
       "1        3\n",
       "2        4\n",
       "3        3\n",
       "4        3\n",
       "        ..\n",
       "10027    3\n",
       "10028    2\n",
       "10029    8\n",
       "10030    3\n",
       "10032    3\n",
       "Name: 0, Length: 9181, dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"quartos\"].str.split(\"-\", expand=True)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "092252cb-2e58-46f5-b179-dc2e977db109",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sp_filter[\"quartos_int\"] = df_sp_filter[\"quartos\"].str.split(\"-\", expand=True)[0].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be6ed2d6-6252-4bef-92b6-6020a6f2f806",
   "metadata": {},
   "source": [
    "### 3.4 - Tratando a coluna 'banheiros'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "bf2ad94e-149b-4eb7-8666-ec8642cf3cd1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['8', '3-6', '3', '2', '4', '1', '5', '7', '6', '9', '10', '11',\n",
       "       '12', '40', '14'], dtype=object)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"banheiros\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ca9a7576-7c18-45a6-b820-61f2f259d190",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10027</th>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10028</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10029</th>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10030</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10032</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9181 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       0     1\n",
       "0      8  None\n",
       "1      3     6\n",
       "2      3  None\n",
       "3      2  None\n",
       "4      2  None\n",
       "...   ..   ...\n",
       "10027  6  None\n",
       "10028  2  None\n",
       "10029  7  None\n",
       "10030  3  None\n",
       "10032  2  None\n",
       "\n",
       "[9181 rows x 2 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"banheiros\"].str.split('-', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7068bcf6-db07-4485-aa5f-8df0427bd39a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        8\n",
       "1        3\n",
       "2        3\n",
       "3        2\n",
       "4        2\n",
       "        ..\n",
       "10027    6\n",
       "10028    2\n",
       "10029    7\n",
       "10030    3\n",
       "10032    2\n",
       "Name: 0, Length: 9181, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"banheiros\"].str.split('-', expand=True)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ec35306d-a820-4521-a32c-34d99da00639",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sp_filter[\"banheiros_int\"] = df_sp_filter[\"banheiros\"].str.split('-', expand=True)[0].astype(int) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f58a4572-f1b7-4c57-ab9c-3b7411c032d2",
   "metadata": {},
   "source": [
    "### 3.5 - Tratando a coluna 'vagas'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "69616b6c-2003-4700-8e3b-8e739a6923c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['6', '4', '2', '1', '3', '5', '1-2', '8', '7', '10', '9', '20',\n",
       "       '13', '25', '14', '12', '11', '38', '30', '26', '15', '24', '16',\n",
       "       '18', '50'], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"vagas\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "5ddfc8ee-5462-4c3b-b273-46358e511984",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10027</th>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10028</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10029</th>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10030</th>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10032</th>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9181 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       0     1\n",
       "0      6  None\n",
       "1      4  None\n",
       "2      2  None\n",
       "3      4  None\n",
       "4      2  None\n",
       "...   ..   ...\n",
       "10027  4  None\n",
       "10028  2  None\n",
       "10029  5  None\n",
       "10030  6  None\n",
       "10032  2  None\n",
       "\n",
       "[9181 rows x 2 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"vagas\"].str.split(\"-\", expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f0369865-5462-4ead-a290-80403842736b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        6\n",
       "1        4\n",
       "2        2\n",
       "3        4\n",
       "4        2\n",
       "        ..\n",
       "10027    4\n",
       "10028    2\n",
       "10029    5\n",
       "10030    6\n",
       "10032    2\n",
       "Name: 0, Length: 9181, dtype: object"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter[\"vagas\"].str.split(\"-\", expand=True)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b9504028-cc36-4aee-9153-bd8d9f81550b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sp_filter[\"vagas_int\"] = df_sp_filter[\"vagas\"].str.split(\"-\", expand=True)[0].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7afae8a-ca01-4d1c-bd10-58ceb492dd89",
   "metadata": {},
   "source": [
    "### 3.6 - Cotemplando as mudanças & salvando novo xlsx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "3f94fe32-d005-45e7-8df1-95c2c05cd663",
   "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>rua</th>\n",
       "      <th>cidade</th>\n",
       "      <th>area_metro_quadrado</th>\n",
       "      <th>quartos</th>\n",
       "      <th>banheiros</th>\n",
       "      <th>vagas</th>\n",
       "      <th>preco</th>\n",
       "      <th>moeda</th>\n",
       "      <th>preco_anuncio</th>\n",
       "      <th>tipo_anuncio</th>\n",
       "      <th>preco_anuncio_float</th>\n",
       "      <th>area_metro_quadrado_int</th>\n",
       "      <th>quartos_int</th>\n",
       "      <th>banheiros_int</th>\n",
       "      <th>vagas_int</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Avenida Itacira, 255</td>\n",
       "      <td>Planalto Paulista, São Paulo</td>\n",
       "      <td>1000</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>R$ 7.000.000</td>\n",
       "      <td>R$</td>\n",
       "      <td>7.000.000</td>\n",
       "      <td>None</td>\n",
       "      <td>7000000.0</td>\n",
       "      <td>1000</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rua Aurelia Perez Alvarez, 42</td>\n",
       "      <td>Jardim dos Estados, São Paulo</td>\n",
       "      <td>469-524</td>\n",
       "      <td>3-6</td>\n",
       "      <td>3-6</td>\n",
       "      <td>4</td>\n",
       "      <td>R$ 3.700.000</td>\n",
       "      <td>R$</td>\n",
       "      <td>3.700.000</td>\n",
       "      <td>None</td>\n",
       "      <td>3700000.0</td>\n",
       "      <td>469</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rua Alba Valdez</td>\n",
       "      <td>Jardim Reimberg, São Paulo</td>\n",
       "      <td>125</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>R$ 380.000</td>\n",
       "      <td>R$</td>\n",
       "      <td>380.000</td>\n",
       "      <td>None</td>\n",
       "      <td>380000.0</td>\n",
       "      <td>125</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jardim Morumbi, São Paulo</td>\n",
       "      <td>SP</td>\n",
       "      <td>310</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>R$ 685.000</td>\n",
       "      <td>R$</td>\n",
       "      <td>685.000</td>\n",
       "      <td>None</td>\n",
       "      <td>685000.0</td>\n",
       "      <td>310</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rua Tobias Barreto, 195</td>\n",
       "      <td>Mooca, São Paulo</td>\n",
       "      <td>100</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>R$ 540.000</td>\n",
       "      <td>R$</td>\n",
       "      <td>540.000</td>\n",
       "      <td>None</td>\n",
       "      <td>540000.0</td>\n",
       "      <td>100</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              rua                           cidade  \\\n",
       "0           Avenida Itacira, 255     Planalto Paulista, São Paulo    \n",
       "1  Rua Aurelia Perez Alvarez, 42    Jardim dos Estados, São Paulo    \n",
       "2                Rua Alba Valdez       Jardim Reimberg, São Paulo    \n",
       "3      Jardim Morumbi, São Paulo                                SP   \n",
       "4        Rua Tobias Barreto, 195                 Mooca, São Paulo    \n",
       "\n",
       "  area_metro_quadrado quartos banheiros vagas         preco moeda  \\\n",
       "0                1000       4         8     6  R$ 7.000.000    R$   \n",
       "1             469-524     3-6       3-6     4  R$ 3.700.000    R$   \n",
       "2                 125       4         3     2    R$ 380.000    R$   \n",
       "3                 310       3         2     4    R$ 685.000    R$   \n",
       "4                 100       3         2     2    R$ 540.000    R$   \n",
       "\n",
       "  preco_anuncio tipo_anuncio  preco_anuncio_float  area_metro_quadrado_int  \\\n",
       "0     7.000.000         None            7000000.0                     1000   \n",
       "1     3.700.000         None            3700000.0                      469   \n",
       "2       380.000         None             380000.0                      125   \n",
       "3       685.000         None             685000.0                      310   \n",
       "4       540.000         None             540000.0                      100   \n",
       "\n",
       "   quartos_int  banheiros_int  vagas_int  \n",
       "0            4              8          6  \n",
       "1            3              3          4  \n",
       "2            4              3          2  \n",
       "3            3              2          4  \n",
       "4            3              2          2  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sp_filter.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "7b64a825-d5f8-4988-9dcf-23548d20d3a7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 9181 entries, 0 to 10032\n",
      "Data columns (total 5 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   area_metro_quadrado_int  9181 non-null   int32  \n",
      " 1   quartos_int              9181 non-null   int32  \n",
      " 2   banheiros_int            9181 non-null   int32  \n",
      " 3   vagas_int                9181 non-null   int32  \n",
      " 4   preco_anuncio_float      9181 non-null   float64\n",
      "dtypes: float64(1), int32(4)\n",
      "memory usage: 286.9 KB\n"
     ]
    }
   ],
   "source": [
    "df_to_save = df_sp_filter[[\"area_metro_quadrado_int\", \"quartos_int\", \"banheiros_int\", \"vagas_int\", \"preco_anuncio_float\"]]\n",
    "df_to_save.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "4e2c7390-5f18-4026-b52a-519e5fa25561",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_to_save.to_csv(\"./dados/SaoPauloClean.csv\")"
   ]
  }
 ],
 "metadata": {
  "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}