iptu_loading.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "# IPTU loading <a href=\"https://colab.research.google.com/github/jvcanavarro/HackSerpro/blob/master/iptu_loading.ipynb\" target=\"_parent\"><img style=\"display:block;margin-right:0;margin-left:auto; margin-top:-.8em\" src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Requirements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import HTML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading the files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "VmX5OTVEWvM3"
   },
   "outputs": [],
   "source": [
    "# files, dataframes = [\"IPTU_Part_01_750K.csv\", \"IPTU_Part_02_750K.csv\", \"IPTU_Part_03_970K.csv\",\n",
    "#                      \"IPTU_Part_04_750K.csv\", \"IPTU_Part_05_750K.csv\"], []\n",
    "# \n",
    "# for file in files:\n",
    "#     dataframes.append(pd.read_csv(\"datasets/iptu-sp/\" + file, sep=\";\", encoding = \"ISO-8859-1\"))\n",
    "#     \n",
    "# iptu = pd.concat(dataframes,axis=0)\n",
    "# \n",
    "# for df in dataframes:\n",
    "#     del df\n",
    "# \n",
    "# del dataframes, files\n",
    "\n",
    "iptu = pd.read_csv(\"datasets/iptu-sp/iptu.csv\", encoding=\"utf-8\", sep=\";\")\n",
    "imoveis = pd.read_csv(\"datasets/iptu-sp/toninho.csv\", sep=\";\", encoding=\"unicode_escape\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>NUMERO DO CONTRIBUINTE</th>\n",
       "      <th>ANO DO EXERCICIO</th>\n",
       "      <th>NUMERO DA NL</th>\n",
       "      <th>DATA DO CADASTRAMENTO</th>\n",
       "      <th>TIPO DE CONTRIBUINTE 1</th>\n",
       "      <th>NOME DO CONTRIBUINTE 1</th>\n",
       "      <th>TIPO DE CONTRIBUINTE 2</th>\n",
       "      <th>NOME DO CONTRIBUINTE 2</th>\n",
       "      <th>NUMERO DO CONDOMINIO</th>\n",
       "      <th>CODLOG DO IMOVEL</th>\n",
       "      <th>...</th>\n",
       "      <th>ANO DA CONSTRUCAO CORRIGIDO</th>\n",
       "      <th>QUANTIDADE DE PAVIMENTOS</th>\n",
       "      <th>TESTADA PARA CALCULO</th>\n",
       "      <th>TIPO DE USO DO IMOVEL</th>\n",
       "      <th>TIPO DE PADRAO DA CONSTRUCAO</th>\n",
       "      <th>TIPO DE TERRENO</th>\n",
       "      <th>FATOR DE OBSOLESCENCIA</th>\n",
       "      <th>ANO DE INICIO DA VIDA DO CONTRIBUINTE</th>\n",
       "      <th>MES DE INICIO DA VIDA DO CONTRIBUINTE</th>\n",
       "      <th>FASE DO CONTRIBUINTE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0010030001-4</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>16/01/2016</td>\n",
       "      <td>PESSOA FISICA (CPF)</td>\n",
       "      <td>MARCIO MOURCHED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00-0</td>\n",
       "      <td>03812-1</td>\n",
       "      <td>...</td>\n",
       "      <td>1924</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>Loja</td>\n",
       "      <td>Comercial horizontal - padrão B</td>\n",
       "      <td>De esquina</td>\n",
       "      <td>0,2</td>\n",
       "      <td>1963</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0010030002-2</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>16/01/2016</td>\n",
       "      <td>PESSOA FISICA (CPF)</td>\n",
       "      <td>MARCIO MOURCHED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00-0</td>\n",
       "      <td>03812-1</td>\n",
       "      <td>...</td>\n",
       "      <td>1944</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>Loja</td>\n",
       "      <td>Comercial horizontal - padrão B</td>\n",
       "      <td>Normal</td>\n",
       "      <td>0,2</td>\n",
       "      <td>1963</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0010030003-0</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>16/01/2016</td>\n",
       "      <td>PESSOA FISICA (CPF)</td>\n",
       "      <td>MARCIO MOURCHED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00-0</td>\n",
       "      <td>03812-1</td>\n",
       "      <td>...</td>\n",
       "      <td>1965</td>\n",
       "      <td>2</td>\n",
       "      <td>7,85</td>\n",
       "      <td>Loja</td>\n",
       "      <td>Comercial horizontal - padrão B</td>\n",
       "      <td>Normal</td>\n",
       "      <td>0,37</td>\n",
       "      <td>1963</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0010030004-9</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>16/01/2016</td>\n",
       "      <td>PESSOA FISICA (CPF)</td>\n",
       "      <td>AUGUSTO CESAR DE MATTOS JUNIOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00-0</td>\n",
       "      <td>03812-1</td>\n",
       "      <td>...</td>\n",
       "      <td>1944</td>\n",
       "      <td>1</td>\n",
       "      <td>6,05</td>\n",
       "      <td>Loja</td>\n",
       "      <td>Comercial horizontal - padrão B</td>\n",
       "      <td>Normal</td>\n",
       "      <td>0,2</td>\n",
       "      <td>1963</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0010030005-7</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>16/01/2016</td>\n",
       "      <td>PESSOA FISICA (CPF)</td>\n",
       "      <td>AUGUSTO CESAR DE MATTOS JUNIOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00-0</td>\n",
       "      <td>03812-1</td>\n",
       "      <td>...</td>\n",
       "      <td>1944</td>\n",
       "      <td>1</td>\n",
       "      <td>6,7</td>\n",
       "      <td>Loja</td>\n",
       "      <td>Comercial horizontal - padrão B</td>\n",
       "      <td>Normal</td>\n",
       "      <td>0,2</td>\n",
       "      <td>1963</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  NUMERO DO CONTRIBUINTE  ANO DO EXERCICIO  NUMERO DA NL  \\\n",
       "0           0010030001-4              2016             1   \n",
       "1           0010030002-2              2016             1   \n",
       "2           0010030003-0              2016             1   \n",
       "3           0010030004-9              2016             1   \n",
       "4           0010030005-7              2016             1   \n",
       "\n",
       "  DATA DO CADASTRAMENTO TIPO DE CONTRIBUINTE 1  \\\n",
       "0            16/01/2016    PESSOA FISICA (CPF)   \n",
       "1            16/01/2016    PESSOA FISICA (CPF)   \n",
       "2            16/01/2016    PESSOA FISICA (CPF)   \n",
       "3            16/01/2016    PESSOA FISICA (CPF)   \n",
       "4            16/01/2016    PESSOA FISICA (CPF)   \n",
       "\n",
       "           NOME DO CONTRIBUINTE 1 TIPO DE CONTRIBUINTE 2  \\\n",
       "0                 MARCIO MOURCHED                    NaN   \n",
       "1                 MARCIO MOURCHED                    NaN   \n",
       "2                 MARCIO MOURCHED                    NaN   \n",
       "3  AUGUSTO CESAR DE MATTOS JUNIOR                    NaN   \n",
       "4  AUGUSTO CESAR DE MATTOS JUNIOR                    NaN   \n",
       "\n",
       "  NOME DO CONTRIBUINTE 2 NUMERO DO CONDOMINIO CODLOG DO IMOVEL  ...  \\\n",
       "0                    NaN                 00-0          03812-1  ...   \n",
       "1                    NaN                 00-0          03812-1  ...   \n",
       "2                    NaN                 00-0          03812-1  ...   \n",
       "3                    NaN                 00-0          03812-1  ...   \n",
       "4                    NaN                 00-0          03812-1  ...   \n",
       "\n",
       "  ANO DA CONSTRUCAO CORRIGIDO  QUANTIDADE DE PAVIMENTOS TESTADA PARA CALCULO  \\\n",
       "0                        1924                         1                   13   \n",
       "1                        1944                         1                    6   \n",
       "2                        1965                         2                 7,85   \n",
       "3                        1944                         1                 6,05   \n",
       "4                        1944                         1                  6,7   \n",
       "\n",
       "  TIPO DE USO DO IMOVEL     TIPO DE PADRAO DA CONSTRUCAO TIPO DE TERRENO  \\\n",
       "0                  Loja  Comercial horizontal - padrão B      De esquina   \n",
       "1                  Loja  Comercial horizontal - padrão B          Normal   \n",
       "2                  Loja  Comercial horizontal - padrão B          Normal   \n",
       "3                  Loja  Comercial horizontal - padrão B          Normal   \n",
       "4                  Loja  Comercial horizontal - padrão B          Normal   \n",
       "\n",
       "   FATOR DE OBSOLESCENCIA ANO DE INICIO DA VIDA DO CONTRIBUINTE  \\\n",
       "0                     0,2                                  1963   \n",
       "1                     0,2                                  1963   \n",
       "2                    0,37                                  1963   \n",
       "3                     0,2                                  1963   \n",
       "4                     0,2                                  1963   \n",
       "\n",
       "   MES DE INICIO DA VIDA DO CONTRIBUINTE  FASE DO CONTRIBUINTE  \n",
       "0                                      1                     0  \n",
       "1                                      1                     0  \n",
       "2                                      1                     0  \n",
       "3                                      1                     0  \n",
       "4                                      1                     0  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iptu.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>Classe</th>\n",
       "      <th>Número do RIP</th>\n",
       "      <th>UF</th>\n",
       "      <th>Município</th>\n",
       "      <th>Endereço</th>\n",
       "      <th>Bairro</th>\n",
       "      <th>Conceituação</th>\n",
       "      <th>Tipo Imóvel</th>\n",
       "      <th>Situação da Utilização</th>\n",
       "      <th>Proprietário Oficial</th>\n",
       "      <th>Regime de Utilização</th>\n",
       "      <th>CEP DO IMOVEL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213010829293</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AV TUCUNARE 1192 AP 61 BL 3-ED TOP POINT-COND ...</td>\n",
       "      <td>TAMBORE</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6460020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213011966650</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AL RIO NEGRO 503 SALA 2306 - ESCRITORIOS RIO N...</td>\n",
       "      <td>ALPHAVILLE</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6454000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213011631551</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AL GRAJAU 482 VG-29/30 A-16 2SS ED AMERICA 1 C...</td>\n",
       "      <td>ALPHAVILLE INDUSTRIA</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6454050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213011074849</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AV ANDROMEDA 885 SALA 317 - BRASCAN CENTURY OF...</td>\n",
       "      <td>GREEN VALLEY</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213010760900</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AL RIO NEGRO 585 BL-C/ ED DEMINI, CONJ CORPORA...</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6454000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Classe  Número do RIP  UF Município  \\\n",
       "0  DOMINIAL  6213010829293  SP   BARUERI   \n",
       "1  DOMINIAL  6213011966650  SP   BARUERI   \n",
       "2  DOMINIAL  6213011631551  SP   BARUERI   \n",
       "3  DOMINIAL  6213011074849  SP   BARUERI   \n",
       "4  DOMINIAL  6213010760900  SP   BARUERI   \n",
       "\n",
       "                                            Endereço                Bairro  \\\n",
       "0  AV TUCUNARE 1192 AP 61 BL 3-ED TOP POINT-COND ...               TAMBORE   \n",
       "1  AL RIO NEGRO 503 SALA 2306 - ESCRITORIOS RIO N...            ALPHAVILLE   \n",
       "2  AL GRAJAU 482 VG-29/30 A-16 2SS ED AMERICA 1 C...  ALPHAVILLE INDUSTRIA   \n",
       "3  AV ANDROMEDA 885 SALA 317 - BRASCAN CENTURY OF...          GREEN VALLEY   \n",
       "4  AL RIO NEGRO 585 BL-C/ ED DEMINI, CONJ CORPORA...               BARUERI   \n",
       "\n",
       "        Conceituação Tipo Imóvel Situação da Utilização  \\\n",
       "0  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "1  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "2  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "3  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "4  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "\n",
       "            Proprietário Oficial Regime de Utilização  CEP DO IMOVEL  \n",
       "0  União (Adm. Pub. Fed. direta)           AFORAMENTO        6460020  \n",
       "1  União (Adm. Pub. Fed. direta)           AFORAMENTO        6454000  \n",
       "2  União (Adm. Pub. Fed. direta)           AFORAMENTO        6454050  \n",
       "3  União (Adm. Pub. Fed. direta)           AFORAMENTO        6400000  \n",
       "4  União (Adm. Pub. Fed. direta)           AFORAMENTO        6454000  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "imoveis.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Treating the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "imoveis[\"CEP DO IMOVEL\"] = imoveis[\"Endereço\"].apply(lambda e: int(e.split(\"CEP: \")[-1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Classe</th>\n",
       "      <th>Número do RIP</th>\n",
       "      <th>UF</th>\n",
       "      <th>Município</th>\n",
       "      <th>Endereço</th>\n",
       "      <th>Bairro</th>\n",
       "      <th>Conceituação</th>\n",
       "      <th>Tipo Imóvel</th>\n",
       "      <th>Situação da Utilização</th>\n",
       "      <th>Proprietário Oficial</th>\n",
       "      <th>Regime de Utilização</th>\n",
       "      <th>CEP DO IMOVEL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213010829293</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AV TUCUNARE 1192 AP 61 BL 3-ED TOP POINT-COND ...</td>\n",
       "      <td>TAMBORE</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6460020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213011966650</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AL RIO NEGRO 503 SALA 2306 - ESCRITORIOS RIO N...</td>\n",
       "      <td>ALPHAVILLE</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6454000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213011631551</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AL GRAJAU 482 VG-29/30 A-16 2SS ED AMERICA 1 C...</td>\n",
       "      <td>ALPHAVILLE INDUSTRIA</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6454050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213011074849</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AV ANDROMEDA 885 SALA 317 - BRASCAN CENTURY OF...</td>\n",
       "      <td>GREEN VALLEY</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>6213010760900</td>\n",
       "      <td>SP</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>AL RIO NEGRO 585 BL-C/ ED DEMINI, CONJ CORPORA...</td>\n",
       "      <td>BARUERI</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>6454000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Classe  Número do RIP  UF Município  \\\n",
       "0  DOMINIAL  6213010829293  SP   BARUERI   \n",
       "1  DOMINIAL  6213011966650  SP   BARUERI   \n",
       "2  DOMINIAL  6213011631551  SP   BARUERI   \n",
       "3  DOMINIAL  6213011074849  SP   BARUERI   \n",
       "4  DOMINIAL  6213010760900  SP   BARUERI   \n",
       "\n",
       "                                            Endereço                Bairro  \\\n",
       "0  AV TUCUNARE 1192 AP 61 BL 3-ED TOP POINT-COND ...               TAMBORE   \n",
       "1  AL RIO NEGRO 503 SALA 2306 - ESCRITORIOS RIO N...            ALPHAVILLE   \n",
       "2  AL GRAJAU 482 VG-29/30 A-16 2SS ED AMERICA 1 C...  ALPHAVILLE INDUSTRIA   \n",
       "3  AV ANDROMEDA 885 SALA 317 - BRASCAN CENTURY OF...          GREEN VALLEY   \n",
       "4  AL RIO NEGRO 585 BL-C/ ED DEMINI, CONJ CORPORA...               BARUERI   \n",
       "\n",
       "        Conceituação Tipo Imóvel Situação da Utilização  \\\n",
       "0  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "1  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "2  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "3  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "4  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "\n",
       "            Proprietário Oficial Regime de Utilização  CEP DO IMOVEL  \n",
       "0  União (Adm. Pub. Fed. direta)           AFORAMENTO        6460020  \n",
       "1  União (Adm. Pub. Fed. direta)           AFORAMENTO        6454000  \n",
       "2  União (Adm. Pub. Fed. direta)           AFORAMENTO        6454050  \n",
       "3  União (Adm. Pub. Fed. direta)           AFORAMENTO        6400000  \n",
       "4  União (Adm. Pub. Fed. direta)           AFORAMENTO        6454000  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "imoveis.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "iptu = iptu.loc[iptu[\"CEP DO IMOVEL\"] != \"-\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "iptu2 = iptu[[\"BAIRRO DO IMOVEL\", \"CEP DO IMOVEL\", \"VALOR DO M2 DO TERRENO\", \"VALOR DO M2 DE CONSTRUCAO\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\igor\\appdata\\local\\programs\\python\\python37\\lib\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "iptu2.loc[\"CEP DO IMOVEL\"] = iptu2[\"CEP DO IMOVEL\"].apply(lambda cep: int(\"\".join(cep.split(\"-\"))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_imoveis = imoveis.join(iptu2.groupby(\"CEP DO IMOVEL\").mean(), on=\"CEP DO IMOVEL\").dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>Classe</th>\n",
       "      <th>Número do RIP</th>\n",
       "      <th>UF</th>\n",
       "      <th>Município</th>\n",
       "      <th>Endereço</th>\n",
       "      <th>Bairro</th>\n",
       "      <th>Conceituação</th>\n",
       "      <th>Tipo Imóvel</th>\n",
       "      <th>Situação da Utilização</th>\n",
       "      <th>Proprietário Oficial</th>\n",
       "      <th>Regime de Utilização</th>\n",
       "      <th>CEP DO IMOVEL</th>\n",
       "      <th>VALOR DO M2 DO TERRENO</th>\n",
       "      <th>VALOR DO M2 DE CONSTRUCAO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>83349</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>7107010006887</td>\n",
       "      <td>SP</td>\n",
       "      <td>SAO PAULO</td>\n",
       "      <td>R GENERAL CARNEIRO 115 16 PAVTO EDIFICIO VISTA...</td>\n",
       "      <td>CENTRO</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>1015000</td>\n",
       "      <td>11492.647059</td>\n",
       "      <td>1918.808824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83350</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>7107010008588</td>\n",
       "      <td>SP</td>\n",
       "      <td>SAO PAULO</td>\n",
       "      <td>R GENERAL SOCRATES 51 E 53-PARTE DO LOTE 04 CE...</td>\n",
       "      <td>PENHA DE FRANCA</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>3632040</td>\n",
       "      <td>1114.973770</td>\n",
       "      <td>1332.852459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83351</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>7107000000487</td>\n",
       "      <td>SP</td>\n",
       "      <td>SAO PAULO</td>\n",
       "      <td>R GENERAL CARNEIRO S/N TERRENO CEP: 01015000</td>\n",
       "      <td>CENTRO</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>1015000</td>\n",
       "      <td>11492.647059</td>\n",
       "      <td>1918.808824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83352</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>7107010001141</td>\n",
       "      <td>SP</td>\n",
       "      <td>SAO PAULO</td>\n",
       "      <td>R QUINZE DE NOVEMBRO 184 CJ 1804 - COND DAS BA...</td>\n",
       "      <td>CENTRO</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>1013000</td>\n",
       "      <td>5651.013986</td>\n",
       "      <td>2270.706294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83353</th>\n",
       "      <td>DOMINIAL</td>\n",
       "      <td>7107010006704</td>\n",
       "      <td>SP</td>\n",
       "      <td>SAO PAULO</td>\n",
       "      <td>R GENERAL CARNEIRO 115 14 PAVTO EDIFICIO VISTA...</td>\n",
       "      <td>CENTRO</td>\n",
       "      <td>NACIONAL INTERIOR</td>\n",
       "      <td>TERRENO</td>\n",
       "      <td>EM USO</td>\n",
       "      <td>União (Adm. Pub. Fed. direta)</td>\n",
       "      <td>AFORAMENTO</td>\n",
       "      <td>1015000</td>\n",
       "      <td>11492.647059</td>\n",
       "      <td>1918.808824</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Classe  Número do RIP  UF  Município  \\\n",
       "83349  DOMINIAL  7107010006887  SP  SAO PAULO   \n",
       "83350  DOMINIAL  7107010008588  SP  SAO PAULO   \n",
       "83351  DOMINIAL  7107000000487  SP  SAO PAULO   \n",
       "83352  DOMINIAL  7107010001141  SP  SAO PAULO   \n",
       "83353  DOMINIAL  7107010006704  SP  SAO PAULO   \n",
       "\n",
       "                                                Endereço           Bairro  \\\n",
       "83349  R GENERAL CARNEIRO 115 16 PAVTO EDIFICIO VISTA...           CENTRO   \n",
       "83350  R GENERAL SOCRATES 51 E 53-PARTE DO LOTE 04 CE...  PENHA DE FRANCA   \n",
       "83351       R GENERAL CARNEIRO S/N TERRENO CEP: 01015000           CENTRO   \n",
       "83352  R QUINZE DE NOVEMBRO 184 CJ 1804 - COND DAS BA...           CENTRO   \n",
       "83353  R GENERAL CARNEIRO 115 14 PAVTO EDIFICIO VISTA...           CENTRO   \n",
       "\n",
       "            Conceituação Tipo Imóvel Situação da Utilização  \\\n",
       "83349  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "83350  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "83351  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "83352  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "83353  NACIONAL INTERIOR     TERRENO                 EM USO   \n",
       "\n",
       "                Proprietário Oficial Regime de Utilização  CEP DO IMOVEL  \\\n",
       "83349  União (Adm. Pub. Fed. direta)           AFORAMENTO        1015000   \n",
       "83350  União (Adm. Pub. Fed. direta)           AFORAMENTO        3632040   \n",
       "83351  União (Adm. Pub. Fed. direta)           AFORAMENTO        1015000   \n",
       "83352  União (Adm. Pub. Fed. direta)           AFORAMENTO        1013000   \n",
       "83353  União (Adm. Pub. Fed. direta)           AFORAMENTO        1015000   \n",
       "\n",
       "       VALOR DO M2 DO TERRENO  VALOR DO M2 DE CONSTRUCAO  \n",
       "83349            11492.647059                1918.808824  \n",
       "83350             1114.973770                1332.852459  \n",
       "83351            11492.647059                1918.808824  \n",
       "83352             5651.013986                2270.706294  \n",
       "83353            11492.647059                1918.808824  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_imoveis.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'final_imoveis' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-1-1b83ee71af93>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mfinal_imoveis\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"datasets/imoveis/imoveis_final.csv\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msep\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\";\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mencoding\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"utf-8\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'final_imoveis' is not defined"
     ]
    }
   ],
   "source": [
    "final_imoveis.to_csv(\"datasets/imoveis/imoveis_final.csv\", sep=\";\", encoding=\"utf-8\", index=False)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "name": "Untitled0.ipynb",
   "provenance": []
  },
  "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}