roboempresa.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8\n",
    "# @paidatocandeira\n",
    "# Cria base com todos CNPJs e sócios do Brasil (Receita Federal) e mostra quais empresas são de deputados federais\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "import pandas as pd\n",
    "import unidecode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Função para ler links de empresas nos Estados no site da Receita - retorna todas as linhas da página\n",
    "def le_estado(url):\n",
    "    r = requests.get(url)\n",
    "    page_text = r.text\n",
    "    lista_de_linha = page_text.split('\\n')\n",
    "    return lista_de_linha"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Função para retirar acentuação \n",
    "def f(str):\n",
    "    return (unidecode.unidecode(str))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lê página principal da Receita com dados das empresas, faz sopa e seleciona tabela com os links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = requests.get(\"http://idg.receita.fazenda.gov.br/orientacao/tributaria/cadastros/cadastro-nacional-de-pessoas-juridicas-cnpj/dados-abertos-do-cnpj\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "soup =  BeautifulSoup(res.text, \"lxml\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "table = soup.select(\"table.plain\")[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Listas que vão abrigar dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "empresas = []\n",
    "socios = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Acre\n",
      "Espírito Santo\n",
      "Paraíba\n",
      "Rondônia\n",
      "Alagoas\n",
      "Goiás\n",
      "Paraná\n",
      "Roraima\n",
      "Amapá\n",
      "Maranhão\n",
      "Pernambuco\n",
      "Santa Catarina\n",
      "Amazonas\n",
      "Mato Grosso\n",
      "Piauí\n",
      "São Paulo\n",
      "Bahia\n",
      "Mato Grosso do Sul\n",
      "Rio de Janeiro\n",
      "Sergipe\n",
      "Ceará\n",
      "Minas Gerais\n",
      "Rio Grande do Norte\n",
      "Tocantins\n",
      "Distrito Federal\n",
      "Pará\n",
      "Rio Grande do Sul\n"
     ]
    }
   ],
   "source": [
    "# Itera na tabela\n",
    "for items in table.select(\"tr\"):\n",
    "    # Seleciona o atributo text - Estados - e o link - href - cria uma lista\n",
    "    data = [[item.text,item['href']] for item in items.select(\".external-link\")]\n",
    "    # Itera na lista\n",
    "    for data_dict in data:\n",
    "        # Chama função com o link href e retorna página inteira com dados do Estado\n",
    "        pagina = le_estado(data_dict[1])\n",
    "        # Guarda nome do Estado\n",
    "        estado = data_dict[0]\n",
    "        print(estado)\n",
    "        # Itera na página do Estado\n",
    "        for linha in pagina:\n",
    "            # Segue uma série de condições de posições de informações, que estão no site principal da Receita para informações das empresas            \n",
    "            if linha.startswith(\"01\"):\n",
    "                cod = linha[2:16]\n",
    "                nome = linha[16:]\n",
    "                #print(cod)\n",
    "                #print(nome)\n",
    "                # Abastece a lista com um dicionário das empresas de cada Estado\n",
    "                empresas.append({'cnpj': str(cod), 'nome_empresa': str(nome).strip(), 'estado': str(estado)})\n",
    "            elif linha.startswith(\"02\"):\n",
    "                cod2 = linha[2:16]\n",
    "                indicador = linha[16:17]\n",
    "                doc = linha[17:31]\n",
    "                qualif = linha[31:33]\n",
    "                socio = linha[33:]\n",
    "                #print(cod2)\n",
    "                #print(indicador)\n",
    "                #print(doc)\n",
    "                #print(qualif)\n",
    "                #print(socio)\n",
    "                # Abastece a lista com um dicionário dos sócios das empresas de cada Estado\n",
    "                socios.append({'cnpj': str(cod2), \n",
    "                                'indicador': str(indicador), \n",
    "                                'documento': str(doc), \n",
    "                                'qualificacao': str(qualif), \n",
    "                                'socio': str(socio).strip(),\n",
    "                                'estado': str(estado)})   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cria dataframe com todas as empresas do Brasil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_empresas = pd.DataFrame(empresas, columns = ['cnpj', 'nome_empresa', 'estado'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 8952217 entries, 0 to 8952216\n",
      "Data columns (total 3 columns):\n",
      "cnpj            object\n",
      "nome_empresa    object\n",
      "estado          object\n",
      "dtypes: object(3)\n",
      "memory usage: 204.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df_empresas.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_empresas.to_csv('empresas_br_4_1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cria dataframe com os sócios de todas as empresas do Brasil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_socios = pd.DataFrame(socios, columns = ['cnpj', 'indicador', 'documento', 'qualificacao', 'socio', 'estado'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 17601862 entries, 0 to 17601861\n",
      "Data columns (total 6 columns):\n",
      "cnpj            object\n",
      "indicador       object\n",
      "documento       object\n",
      "qualificacao    object\n",
      "socio           object\n",
      "estado          object\n",
      "dtypes: object(6)\n",
      "memory usage: 805.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df_socios.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_socios.to_csv('socios_br_4_1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Acessa API da Câmara dos Deputados para pegar o link de API de cada deputado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://dadosabertos.camara.leg.br/api/v2/deputados'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "deputados = []\n",
    "for pagina in [1, 2, 3, 4, 5, 6]:\n",
    "    parametros = {'formato': 'json', 'itens': 100, 'pagina': pagina}\n",
    "    resposta = requests.get(url, parametros)\n",
    "    for deputado in resposta.json()['dados']:\n",
    "        dicionario = {\"deputado\": deputado['nome'], \"link_api\": deputado['uri']}\n",
    "        deputados.append(dicionario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cria dataframe com essa lista"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_deputados_api = pd.DataFrame(deputados)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Itera no dataframe para vasculhar cada API dos deputados e pegar os dados de cada um"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "perfis = []\n",
    "for num, row in df_deputados_api.iterrows():\n",
    "    parametros = {'formato': 'json'}\n",
    "    resposta = requests.get(row['link_api'], parametros)\n",
    "    linha = resposta.json()['dados']\n",
    "    api = linha['uri']\n",
    "    nomecompleto = linha['nomeCivil'].upper()\n",
    "    sexo = linha['sexo']\n",
    "    nascimento = linha['dataNascimento']\n",
    "    linha2 = resposta.json()['dados']['ultimoStatus']\n",
    "    nomeurna = linha2['nomeEleitoral']\n",
    "    partido = linha2['siglaPartido']\n",
    "    uf = linha2['siglaUf']\n",
    "    foto = linha2['urlFoto']\n",
    "    linha3 = resposta.json()['dados']['ultimoStatus']['gabinete']\n",
    "    tel = linha3['telefone']\n",
    "    e_mail = linha3['email']\n",
    "    dicionario = {\"link_api\": api, \"nome_completo\": nomecompleto, \"nome_eleitoral\": nomeurna, \"partido\": partido, \"uf\": uf, \"link_foto\": foto, \"telefone\": tel, \"e_mail\": e_mail, \"sexo\": sexo, \"data_nascimento\": nascimento}\n",
    "    perfis.append(dicionario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cria dataframe final com os 513 deputados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "perfis_deputados = pd.DataFrame(perfis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Retira acentuação da coluna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "perfis_deputados[\"nome_completo\"] = perfis_deputados[\"nome_completo\"].apply(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 513 entries, 0 to 512\n",
      "Data columns (total 10 columns):\n",
      "data_nascimento    513 non-null object\n",
      "e_mail             513 non-null object\n",
      "link_api           513 non-null object\n",
      "link_foto          513 non-null object\n",
      "nome_completo      513 non-null object\n",
      "nome_eleitoral     513 non-null object\n",
      "partido            513 non-null object\n",
      "sexo               513 non-null object\n",
      "telefone           512 non-null object\n",
      "uf                 513 non-null object\n",
      "dtypes: object(10)\n",
      "memory usage: 40.2+ KB\n"
     ]
    }
   ],
   "source": [
    "perfis_deputados.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "perfis_deputados.to_csv('perfis_deputados_5_1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cria dataframe a partir dos arquivos de empresas e sócios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "socios_brasil = pd.read_csv(\"socios_br_4_1.csv\",sep=',',encoding = 'utf-8', converters={'cnpj': lambda x: str(x)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 17601862 entries, 0 to 17601861\n",
      "Data columns (total 7 columns):\n",
      "Unnamed: 0      int64\n",
      "cnpj            object\n",
      "indicador       int64\n",
      "documento       object\n",
      "qualificacao    int64\n",
      "socio           object\n",
      "estado          object\n",
      "dtypes: int64(3), object(4)\n",
      "memory usage: 940.0+ MB\n"
     ]
    }
   ],
   "source": [
    "socios_brasil.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>index</th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>cnpj</th>\n",
       "      <th>indicador</th>\n",
       "      <th>documento</th>\n",
       "      <th>qualificacao</th>\n",
       "      <th>socio</th>\n",
       "      <th>estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17601857</th>\n",
       "      <td>17601857</td>\n",
       "      <td>17601857</td>\n",
       "      <td>29236068000156</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>65</td>\n",
       "      <td>CRISTINA DE MEDEIROS HUISMAN</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17601858</th>\n",
       "      <td>17601858</td>\n",
       "      <td>17601858</td>\n",
       "      <td>29236075000158</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>65</td>\n",
       "      <td>CLAUDIOMIRO REIS</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17601859</th>\n",
       "      <td>17601859</td>\n",
       "      <td>17601859</td>\n",
       "      <td>29236906000191</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>RONALDO SOUZA DE OLIVEIRA</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17601860</th>\n",
       "      <td>17601860</td>\n",
       "      <td>17601860</td>\n",
       "      <td>29237812000137</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>49</td>\n",
       "      <td>ADELAR ANTONIO MORARI</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17601861</th>\n",
       "      <td>17601861</td>\n",
       "      <td>17601861</td>\n",
       "      <td>29237812000137</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>22</td>\n",
       "      <td>JEFERSON MARASCA MORARI</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             index  Unnamed: 0            cnpj  indicador       documento  \\\n",
       "17601857  17601857    17601857  29236068000156          2                   \n",
       "17601858  17601858    17601858  29236075000158          2                   \n",
       "17601859  17601859    17601859  29236906000191          2                   \n",
       "17601860  17601860    17601860  29237812000137          2                   \n",
       "17601861  17601861    17601861  29237812000137          2                   \n",
       "\n",
       "          qualificacao                         socio             estado  \n",
       "17601857            65  CRISTINA DE MEDEIROS HUISMAN  Rio Grande do Sul  \n",
       "17601858            65              CLAUDIOMIRO REIS  Rio Grande do Sul  \n",
       "17601859            16     RONALDO SOUZA DE OLIVEIRA  Rio Grande do Sul  \n",
       "17601860            49         ADELAR ANTONIO MORARI  Rio Grande do Sul  \n",
       "17601861            22       JEFERSON MARASCA MORARI  Rio Grande do Sul  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "socios_brasil.reset_index().tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cruzamento dos nomes completos dos deputados com os nomes de sócios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "deputados_socios = pd.merge(perfis_deputados, socios_brasil, left_on='nome_completo', right_on='socio')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2086 entries, 0 to 2085\n",
      "Data columns (total 17 columns):\n",
      "data_nascimento    2086 non-null object\n",
      "e_mail             2086 non-null object\n",
      "link_api           2086 non-null object\n",
      "link_foto          2086 non-null object\n",
      "nome_completo      2086 non-null object\n",
      "nome_eleitoral     2086 non-null object\n",
      "partido            2086 non-null object\n",
      "sexo               2086 non-null object\n",
      "telefone           2083 non-null object\n",
      "uf                 2086 non-null object\n",
      "Unnamed: 0         2086 non-null int64\n",
      "cnpj               2086 non-null object\n",
      "indicador          2086 non-null int64\n",
      "documento          2086 non-null object\n",
      "qualificacao       2086 non-null int64\n",
      "socio              2086 non-null object\n",
      "estado             2086 non-null object\n",
      "dtypes: int64(3), object(14)\n",
      "memory usage: 293.3+ KB\n"
     ]
    }
   ],
   "source": [
    "deputados_socios.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Carrega nomes de empresas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "empresas_brasil = pd.read_csv(\"empresas_br_4_1.csv\",sep=',',encoding = 'utf-8', converters={'cnpj': lambda x: str(x)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 8952217 entries, 0 to 8952216\n",
      "Data columns (total 4 columns):\n",
      "Unnamed: 0      int64\n",
      "cnpj            object\n",
      "nome_empresa    object\n",
      "estado          object\n",
      "dtypes: int64(1), object(3)\n",
      "memory usage: 273.2+ MB\n"
     ]
    }
   ],
   "source": [
    "empresas_brasil.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Mostra os nomes das empresas dos deputados - a partir do dataframe criado acima"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "deputados_socios_empresas = pd.merge(deputados_socios, empresas_brasil, left_on='cnpj', right_on='cnpj')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2086 entries, 0 to 2085\n",
      "Data columns (total 20 columns):\n",
      "data_nascimento    2086 non-null object\n",
      "e_mail             2086 non-null object\n",
      "link_api           2086 non-null object\n",
      "link_foto          2086 non-null object\n",
      "nome_completo      2086 non-null object\n",
      "nome_eleitoral     2086 non-null object\n",
      "partido            2086 non-null object\n",
      "sexo               2086 non-null object\n",
      "telefone           2083 non-null object\n",
      "uf                 2086 non-null object\n",
      "Unnamed: 0_x       2086 non-null int64\n",
      "cnpj               2086 non-null object\n",
      "indicador          2086 non-null int64\n",
      "documento          2086 non-null object\n",
      "qualificacao       2086 non-null int64\n",
      "socio              2086 non-null object\n",
      "estado_x           2086 non-null object\n",
      "Unnamed: 0_y       2086 non-null int64\n",
      "nome_empresa       2086 non-null object\n",
      "estado_y           2086 non-null object\n",
      "dtypes: int64(4), object(16)\n",
      "memory usage: 342.2+ KB\n"
     ]
    }
   ],
   "source": [
    "deputados_socios_empresas.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "deputados_socios_empresas.to_csv('empresas_deputados_5_1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>index</th>\n",
       "      <th>data_nascimento</th>\n",
       "      <th>e_mail</th>\n",
       "      <th>link_api</th>\n",
       "      <th>link_foto</th>\n",
       "      <th>nome_completo</th>\n",
       "      <th>nome_eleitoral</th>\n",
       "      <th>partido</th>\n",
       "      <th>sexo</th>\n",
       "      <th>telefone</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 0_x</th>\n",
       "      <th>cnpj</th>\n",
       "      <th>indicador</th>\n",
       "      <th>documento</th>\n",
       "      <th>qualificacao</th>\n",
       "      <th>socio</th>\n",
       "      <th>estado_x</th>\n",
       "      <th>Unnamed: 0_y</th>\n",
       "      <th>nome_empresa</th>\n",
       "      <th>estado_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1962-03-29</td>\n",
       "      <td>dep.abelmesquitajr.@camara.leg.br</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>http://www.camara.leg.br/internet/deputado/ban...</td>\n",
       "      <td>ABEL SALVADOR MESQUITA JUNIOR</td>\n",
       "      <td>ABEL MESQUITA JR.</td>\n",
       "      <td>DEM</td>\n",
       "      <td>M</td>\n",
       "      <td>3215-5248</td>\n",
       "      <td>...</td>\n",
       "      <td>2752659</td>\n",
       "      <td>00376437000124</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>22</td>\n",
       "      <td>ABEL SALVADOR MESQUITA JUNIOR</td>\n",
       "      <td>Roraima</td>\n",
       "      <td>1434026</td>\n",
       "      <td>AUTO POSTO ABEL GALINHA LIMITADA</td>\n",
       "      <td>Roraima</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1962-03-29</td>\n",
       "      <td>dep.abelmesquitajr.@camara.leg.br</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>http://www.camara.leg.br/internet/deputado/ban...</td>\n",
       "      <td>ABEL SALVADOR MESQUITA JUNIOR</td>\n",
       "      <td>ABEL MESQUITA JR.</td>\n",
       "      <td>DEM</td>\n",
       "      <td>M</td>\n",
       "      <td>3215-5248</td>\n",
       "      <td>...</td>\n",
       "      <td>2757584</td>\n",
       "      <td>04651634000165</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>22</td>\n",
       "      <td>ABEL SALVADOR MESQUITA JUNIOR</td>\n",
       "      <td>Roraima</td>\n",
       "      <td>1436671</td>\n",
       "      <td>SALOMAO VEICULO LTDA</td>\n",
       "      <td>Roraima</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1963-07-11</td>\n",
       "      <td>dep.adailcarneiro@camara.leg.br</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>http://www.camara.leg.br/internet/deputado/ban...</td>\n",
       "      <td>JOSE ADAIL CARNEIRO SILVA</td>\n",
       "      <td>ADAIL CARNEIRO</td>\n",
       "      <td>PP</td>\n",
       "      <td>M</td>\n",
       "      <td>3215-5335</td>\n",
       "      <td>...</td>\n",
       "      <td>13100369</td>\n",
       "      <td>03313310000154</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>49</td>\n",
       "      <td>JOSE ADAIL CARNEIRO SILVA</td>\n",
       "      <td>Ceará</td>\n",
       "      <td>6639984</td>\n",
       "      <td>MOVEMENT FOMENTO COMERCIAL LTDA</td>\n",
       "      <td>Ceará</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1963-07-11</td>\n",
       "      <td>dep.adailcarneiro@camara.leg.br</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>http://www.camara.leg.br/internet/deputado/ban...</td>\n",
       "      <td>JOSE ADAIL CARNEIRO SILVA</td>\n",
       "      <td>ADAIL CARNEIRO</td>\n",
       "      <td>PP</td>\n",
       "      <td>M</td>\n",
       "      <td>3215-5335</td>\n",
       "      <td>...</td>\n",
       "      <td>13177060</td>\n",
       "      <td>04457059000164</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>49</td>\n",
       "      <td>JOSE ADAIL CARNEIRO SILVA</td>\n",
       "      <td>Ceará</td>\n",
       "      <td>6684193</td>\n",
       "      <td>MOVEMENT CORRETAGEM &amp; COMERCIO DE VEICULOS LTDA</td>\n",
       "      <td>Ceará</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1958-02-10</td>\n",
       "      <td>dep.adalbertocavalcanti@camara.leg.br</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>http://www.camara.leg.br/internet/deputado/ban...</td>\n",
       "      <td>ADALBERTO CAVALCANTI RODRIGUES</td>\n",
       "      <td>ADALBERTO CAVALCANTI</td>\n",
       "      <td>AVANTE</td>\n",
       "      <td>M</td>\n",
       "      <td>3215-5402</td>\n",
       "      <td>...</td>\n",
       "      <td>3018287</td>\n",
       "      <td>03483627000139</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>49</td>\n",
       "      <td>ADALBERTO CAVALCANTI RODRIGUES</td>\n",
       "      <td>Pernambuco</td>\n",
       "      <td>1592684</td>\n",
       "      <td>LUCIA MARIANO &amp; CIA LTDA - ME</td>\n",
       "      <td>Pernambuco</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index data_nascimento                                 e_mail  \\\n",
       "0      0      1962-03-29      dep.abelmesquitajr.@camara.leg.br   \n",
       "1      1      1962-03-29      dep.abelmesquitajr.@camara.leg.br   \n",
       "2      2      1963-07-11        dep.adailcarneiro@camara.leg.br   \n",
       "3      3      1963-07-11        dep.adailcarneiro@camara.leg.br   \n",
       "4      4      1958-02-10  dep.adalbertocavalcanti@camara.leg.br   \n",
       "\n",
       "                                            link_api  \\\n",
       "0  https://dadosabertos.camara.leg.br/api/v2/depu...   \n",
       "1  https://dadosabertos.camara.leg.br/api/v2/depu...   \n",
       "2  https://dadosabertos.camara.leg.br/api/v2/depu...   \n",
       "3  https://dadosabertos.camara.leg.br/api/v2/depu...   \n",
       "4  https://dadosabertos.camara.leg.br/api/v2/depu...   \n",
       "\n",
       "                                           link_foto  \\\n",
       "0  http://www.camara.leg.br/internet/deputado/ban...   \n",
       "1  http://www.camara.leg.br/internet/deputado/ban...   \n",
       "2  http://www.camara.leg.br/internet/deputado/ban...   \n",
       "3  http://www.camara.leg.br/internet/deputado/ban...   \n",
       "4  http://www.camara.leg.br/internet/deputado/ban...   \n",
       "\n",
       "                    nome_completo        nome_eleitoral partido sexo  \\\n",
       "0   ABEL SALVADOR MESQUITA JUNIOR     ABEL MESQUITA JR.     DEM    M   \n",
       "1   ABEL SALVADOR MESQUITA JUNIOR     ABEL MESQUITA JR.     DEM    M   \n",
       "2       JOSE ADAIL CARNEIRO SILVA        ADAIL CARNEIRO      PP    M   \n",
       "3       JOSE ADAIL CARNEIRO SILVA        ADAIL CARNEIRO      PP    M   \n",
       "4  ADALBERTO CAVALCANTI RODRIGUES  ADALBERTO CAVALCANTI  AVANTE    M   \n",
       "\n",
       "    telefone     ...     Unnamed: 0_x            cnpj indicador  \\\n",
       "0  3215-5248     ...          2752659  00376437000124         2   \n",
       "1  3215-5248     ...          2757584  04651634000165         2   \n",
       "2  3215-5335     ...         13100369  03313310000154         2   \n",
       "3  3215-5335     ...         13177060  04457059000164         2   \n",
       "4  3215-5402     ...          3018287  03483627000139         2   \n",
       "\n",
       "        documento qualificacao                           socio    estado_x  \\\n",
       "0                           22   ABEL SALVADOR MESQUITA JUNIOR     Roraima   \n",
       "1                           22   ABEL SALVADOR MESQUITA JUNIOR     Roraima   \n",
       "2                           49       JOSE ADAIL CARNEIRO SILVA       Ceará   \n",
       "3                           49       JOSE ADAIL CARNEIRO SILVA       Ceará   \n",
       "4                           49  ADALBERTO CAVALCANTI RODRIGUES  Pernambuco   \n",
       "\n",
       "  Unnamed: 0_y                                     nome_empresa    estado_y  \n",
       "0      1434026                 AUTO POSTO ABEL GALINHA LIMITADA     Roraima  \n",
       "1      1436671                             SALOMAO VEICULO LTDA     Roraima  \n",
       "2      6639984                  MOVEMENT FOMENTO COMERCIAL LTDA       Ceará  \n",
       "3      6684193  MOVEMENT CORRETAGEM & COMERCIO DE VEICULOS LTDA       Ceará  \n",
       "4      1592684                    LUCIA MARIANO & CIA LTDA - ME  Pernambuco  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deputados_socios_empresas.reset_index().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bônus - procura por nome individual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "nome = socios_brasil[(socios_brasil['socio'] == 'MICHEL MIGUEL ELIAS TEMER LULIA')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3 entries, 5169384 to 8772429\n",
      "Data columns (total 7 columns):\n",
      "Unnamed: 0      3 non-null int64\n",
      "cnpj            3 non-null object\n",
      "indicador       3 non-null int64\n",
      "documento       3 non-null object\n",
      "qualificacao    3 non-null int64\n",
      "socio           3 non-null object\n",
      "estado          3 non-null object\n",
      "dtypes: int64(3), object(4)\n",
      "memory usage: 192.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "nome.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "nome_socios = pd.merge(nome, empresas_brasil, left_on='cnpj', right_on='cnpj')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>index</th>\n",
       "      <th>Unnamed: 0_x</th>\n",
       "      <th>cnpj</th>\n",
       "      <th>indicador</th>\n",
       "      <th>documento</th>\n",
       "      <th>qualificacao</th>\n",
       "      <th>socio</th>\n",
       "      <th>estado_x</th>\n",
       "      <th>Unnamed: 0_y</th>\n",
       "      <th>nome_empresa</th>\n",
       "      <th>estado_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5169384</td>\n",
       "      <td>05397402000195</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>28</td>\n",
       "      <td>MICHEL MIGUEL ELIAS TEMER LULIA</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>2701368</td>\n",
       "      <td>TEMER ADVOGADOS ASSOCIADOS</td>\n",
       "      <td>São Paulo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>6566575</td>\n",
       "      <td>59492884000191</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>MICHEL MIGUEL ELIAS TEMER LULIA</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>3390454</td>\n",
       "      <td>CENTRO DE ESTUDOS CONSTITUCIONAIS=CEC</td>\n",
       "      <td>São Paulo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>8772429</td>\n",
       "      <td>12982441000115</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>22</td>\n",
       "      <td>MICHEL MIGUEL ELIAS TEMER LULIA</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>4434037</td>\n",
       "      <td>TABAPUA INVESTIMENTOS E PARTICIPACOES LTDA</td>\n",
       "      <td>São Paulo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  Unnamed: 0_x            cnpj  indicador       documento  \\\n",
       "0      0       5169384  05397402000195          2                   \n",
       "1      1       6566575  59492884000191          2                   \n",
       "2      2       8772429  12982441000115          2                   \n",
       "\n",
       "   qualificacao                            socio   estado_x  Unnamed: 0_y  \\\n",
       "0            28  MICHEL MIGUEL ELIAS TEMER LULIA  São Paulo       2701368   \n",
       "1            16  MICHEL MIGUEL ELIAS TEMER LULIA  São Paulo       3390454   \n",
       "2            22  MICHEL MIGUEL ELIAS TEMER LULIA  São Paulo       4434037   \n",
       "\n",
       "                                 nome_empresa   estado_y  \n",
       "0                  TEMER ADVOGADOS ASSOCIADOS  São Paulo  \n",
       "1       CENTRO DE ESTUDOS CONSTITUCIONAIS=CEC  São Paulo  \n",
       "2  TABAPUA INVESTIMENTOS E PARTICIPACOES LTDA  São Paulo  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nome_socios.reset_index()"
   ]
  },
  {
   "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.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}