20210103_cruzamento_servidores_eleicoes.ipynb.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from unidecode import unidecode"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cruzamento: Afastamentos para candidatura em eleições x Votos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Dados do SIAPE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "lic_2020_03_11 = pd.read_csv('AFASTREM_032020.csv', sep = ';', encoding = 'latin1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AFASTREM_042020.csv\n",
      "AFASTREM_052020.csv\n",
      "AFASTREM_062020.csv\n",
      "AFASTREM_072020.csv\n",
      "AFASTREM_082020.csv\n",
      "AFASTREM_092020.csv\n",
      "AFASTREM_102020.csv\n",
      "AFASTREM_112020.csv\n"
     ]
    }
   ],
   "source": [
    "for i in ['AFASTREM_042020.csv', 'AFASTREM_052020.csv', 'AFASTREM_062020.csv', 'AFASTREM_072020.csv', 'AFASTREM_082020.csv', 'AFASTREM_092020.csv', 'AFASTREM_102020.csv', 'AFASTREM_112020.csv']:\n",
    "    lic_2020_03_11 = pd.concat([lic_2020_03_11, pd.read_csv(i, sep = ';', encoding = 'latin1')])\n",
    "    print(i)\n"
   ]
  },
  {
   "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>Nome</th>\n",
       "      <th>CPF</th>\n",
       "      <th>Descrição do cargo emprego</th>\n",
       "      <th>Nome do órgão de origem</th>\n",
       "      <th>UF da UPAG de vinculação</th>\n",
       "      <th>Cidade da residência</th>\n",
       "      <th>Nível escolaridade</th>\n",
       "      <th>Ano/Mês início afastamento</th>\n",
       "      <th>Ano/Mês referência</th>\n",
       "      <th>Valor rendimento liquido</th>\n",
       "      <th>Descrição do afastamento</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>176150</th>\n",
       "      <td>LENILSON DA SILVA FREITAS                     ...</td>\n",
       "      <td>***997827**</td>\n",
       "      <td>AGENTE ADMINISTRATIVO</td>\n",
       "      <td>MINISTERIO DA INFRAESTRUTURA</td>\n",
       "      <td>DF</td>\n",
       "      <td>CAMPOS DOS GOYTACAZES</td>\n",
       "      <td>NI</td>\n",
       "      <td>201907</td>\n",
       "      <td>202006</td>\n",
       "      <td>4373,90</td>\n",
       "      <td>Exer. Exter. Par. 6º e 7º, art. 93, Lei 8.112/...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83312</th>\n",
       "      <td>STEFAN JOSEF EHBAUER                          ...</td>\n",
       "      <td>***282702**</td>\n",
       "      <td>PROFESSOR DO MAGISTERIO SUPERIOR</td>\n",
       "      <td>FUNDACAO UNIVERSIDADE DO AMAZONAS</td>\n",
       "      <td>AM</td>\n",
       "      <td>MANAUS</td>\n",
       "      <td>NS</td>\n",
       "      <td>202009</td>\n",
       "      <td>202011</td>\n",
       "      <td>25123,38</td>\n",
       "      <td>TRABALHO REMOTO COVID-19                      ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107747</th>\n",
       "      <td>MARCIO LIMA RIOS                              ...</td>\n",
       "      <td>***056595**</td>\n",
       "      <td>PROFESSOR ENS BASICO TECN TECNOLOGICO</td>\n",
       "      <td>INSTITUTO FEDERAL BAIANO</td>\n",
       "      <td>BA</td>\n",
       "      <td>SENHOR DO BONFIM</td>\n",
       "      <td>NS</td>\n",
       "      <td>201907</td>\n",
       "      <td>202009</td>\n",
       "      <td>19455,96</td>\n",
       "      <td>Afas. no País (Com Ônus) Est/Dout/Mestrado - E...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69521</th>\n",
       "      <td>NATASHA PACHECO DE MELLO OLIVEIRA             ...</td>\n",
       "      <td>***473001**</td>\n",
       "      <td>BIBLIOTECARIO-DOCUMENTALISTA</td>\n",
       "      <td>UNIVERSIDADE FEDERAL DO PARANA</td>\n",
       "      <td>PR</td>\n",
       "      <td>COLOMBO</td>\n",
       "      <td>NS</td>\n",
       "      <td>202008</td>\n",
       "      <td>202009</td>\n",
       "      <td>7529,37</td>\n",
       "      <td>Trabalho Remoto COVID-19                      ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84226</th>\n",
       "      <td>KATIA MARIA DE PAULA LOPES GONZALEZ           ...</td>\n",
       "      <td>***260457**</td>\n",
       "      <td>PROFESSOR ENS BASICO TECN TECNOLOGICO</td>\n",
       "      <td>CENTRO FED.EDUC.TECNOL.CELSO S. FONSECA</td>\n",
       "      <td>RJ</td>\n",
       "      <td>RIO DE JANEIRO</td>\n",
       "      <td>NS</td>\n",
       "      <td>202008</td>\n",
       "      <td>202008</td>\n",
       "      <td>19322,07</td>\n",
       "      <td>Trabalho Remoto COVID-19                      ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     Nome          CPF  \\\n",
       "176150  LENILSON DA SILVA FREITAS                     ...  ***997827**   \n",
       "83312   STEFAN JOSEF EHBAUER                          ...  ***282702**   \n",
       "107747  MARCIO LIMA RIOS                              ...  ***056595**   \n",
       "69521   NATASHA PACHECO DE MELLO OLIVEIRA             ...  ***473001**   \n",
       "84226   KATIA MARIA DE PAULA LOPES GONZALEZ           ...  ***260457**   \n",
       "\n",
       "                      Descrição do cargo emprego  \\\n",
       "176150  AGENTE ADMINISTRATIVO                      \n",
       "83312   PROFESSOR DO MAGISTERIO SUPERIOR           \n",
       "107747  PROFESSOR ENS BASICO TECN TECNOLOGICO      \n",
       "69521   BIBLIOTECARIO-DOCUMENTALISTA               \n",
       "84226   PROFESSOR ENS BASICO TECN TECNOLOGICO      \n",
       "\n",
       "                         Nome do órgão de origem UF da UPAG de vinculação  \\\n",
       "176150  MINISTERIO DA INFRAESTRUTURA                                   DF   \n",
       "83312   FUNDACAO UNIVERSIDADE DO AMAZONAS                              AM   \n",
       "107747  INSTITUTO FEDERAL BAIANO                                       BA   \n",
       "69521   UNIVERSIDADE FEDERAL DO PARANA                                 PR   \n",
       "84226   CENTRO FED.EDUC.TECNOL.CELSO S. FONSECA                        RJ   \n",
       "\n",
       "                  Cidade da residência Nível escolaridade  \\\n",
       "176150  CAMPOS DOS GOYTACAZES                          NI   \n",
       "83312   MANAUS                                         NS   \n",
       "107747  SENHOR DO BONFIM                               NS   \n",
       "69521   COLOMBO                                        NS   \n",
       "84226   RIO DE JANEIRO                                 NS   \n",
       "\n",
       "        Ano/Mês início afastamento  Ano/Mês referência  \\\n",
       "176150                      201907              202006   \n",
       "83312                       202009              202011   \n",
       "107747                      201907              202009   \n",
       "69521                       202008              202009   \n",
       "84226                       202008              202008   \n",
       "\n",
       "       Valor rendimento liquido  \\\n",
       "176150                  4373,90   \n",
       "83312                  25123,38   \n",
       "107747                 19455,96   \n",
       "69521                   7529,37   \n",
       "84226                  19322,07   \n",
       "\n",
       "                                 Descrição do afastamento  \\\n",
       "176150  Exer. Exter. Par. 6º e 7º, art. 93, Lei 8.112/...   \n",
       "83312   TRABALHO REMOTO COVID-19                      ...   \n",
       "107747  Afas. no País (Com Ônus) Est/Dout/Mestrado - E...   \n",
       "69521   Trabalho Remoto COVID-19                      ...   \n",
       "84226   Trabalho Remoto COVID-19                      ...   \n",
       "\n",
       "                                                                     \n",
       "176150                                                NaN            \n",
       "83312                                                 NaN            \n",
       "107747                                                NaN            \n",
       "69521                                                 NaN            \n",
       "84226                                                 NaN            "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lic_2020_03_11.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Nome',\n",
       " 'CPF',\n",
       " 'Descrição do cargo emprego',\n",
       " 'Nome do órgão de origem',\n",
       " 'UF da UPAG de vinculação',\n",
       " 'Cidade da residência',\n",
       " 'Nível escolaridade',\n",
       " 'Ano/Mês início afastamento',\n",
       " 'Ano/Mês referência',\n",
       " 'Valor rendimento liquido',\n",
       " 'Descrição do afastamento',\n",
       " '                                                           ']"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(lic_2020_03_11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AFAS CARGO EFETIVO ART. 120 LEI 8112/90 SEM REMUN. -  EST   ',\n",
       " 'AFAS. CARREIRA MRE MISSAO EXTERIOR (SUSP. PAGAM.) - EST     ',\n",
       " 'AFAS. CURSO FORMACAO OPCAO CARGO EFETIVO- C/ REMUN. -  EST  ',\n",
       " 'AFAS. MISSAO EXTERIOR SEM ONUS - EST                        ',\n",
       " 'AFAS. P/ PRESTAR COLABORACAO, CARREIRA DO MAGISTERIO - EST  ',\n",
       " 'AFASTAMENTO COVID-19                                        ',\n",
       " 'AFastamento Sem Remuneração - CLT                           ',\n",
       " 'Abandono Cargo Inassiduidade Habitual - EST                 ',\n",
       " 'Abandono de Cargo Ausência Intencional - EST                ',\n",
       " 'Afas Cargo efetivo Art. 120 Lei 8112/90 Sem Remun. -  EST   ',\n",
       " 'Afas.  exercer Cargo Político de Ministro de Estado - EST   ',\n",
       " 'Afas. (Cessão) para Justiça Eleitoral - EST                 ',\n",
       " 'Afas. Aperfeiç. CNPq, FINEP e CAPES Fora País C/Ônus - EST  ',\n",
       " 'Afas. Aperfeiç. CNPq, FINEP e CAPES No País C/Ônus  -  EST  ',\n",
       " 'Afas. Cargo Efetivo Art. 120 Lei 8.112/1990 - EST           ',\n",
       " 'Afas. Carreira MRE Missão Exterior (Susp. Pagam.) - EST     ',\n",
       " 'Afas. Competição Desportiva - EST                           ',\n",
       " 'Afas. Curso Form Opção Cargo Efet (Dec. Judic)  C/Rem - EST ',\n",
       " 'Afas. Curso Formação Opção Cargo Efetivo- C/ Remun. -  EST  ',\n",
       " 'Afas. Curso de Form S/Rem. (Dec. Jud.) - EST                ',\n",
       " 'Afas. Curso de Formação Opção Auxilio Financeiro - EST      ',\n",
       " 'Afas. Dirigente Sindical Empresa S/Remuneração - CLT        ',\n",
       " 'Afas. Escola Superior de Guerra - EST                       ',\n",
       " 'Afas. Est/ Prog.Trein.(Cong-Enc-Semin.) País S/Ônus - EST   ',\n",
       " 'Afas. Est/Prog.Trein.(Cong-Enc-Semin) País C/Ônus - EST     ',\n",
       " 'Afas. Est/Prog.Trein.(Congr-Encon) País C/Ônus Limit - EST  ',\n",
       " 'Afas. Estudo Exterior C/Ônus - EST                          ',\n",
       " 'Afas. Estudo Exterior C/Ônus Limitado - EST                 ',\n",
       " 'Afas. Estudo Exterior Sem Ônus - EST                        ',\n",
       " 'Afas. Exer. Mand. Elet. Gov e Vice-Gov Dep S/Rem - EST      ',\n",
       " 'Afas. Exer. Mand. Elet. Prefeito C/Remun. - EST             ',\n",
       " 'Afas. Exer. Mand. Elet. Prefeito S/Remun. - EST             ',\n",
       " 'Afas. Exer. Mand. Elet. Senador 4 anos S/Remun - EST        ',\n",
       " 'Afas. Exer. Mand. Elet. Senador 8 anos S/Remun - EST        ',\n",
       " 'Afas. Exer. Mand. Elet. Vereador C/Remun - EST              ',\n",
       " 'Afas. Exer. Mand. Elet. Vereador S/Remun - EST              ',\n",
       " 'Afas. Exerc. Mandato Eletivo Sem Remuneração - CLT          ',\n",
       " 'Afas. Exercício Mandato Eletivo Com Remuneração - CLT       ',\n",
       " 'Afas. Integrar Comissão de Inquerito Administrativo - EST   ',\n",
       " 'Afas. Juri e Outros Serviços - EST                          ',\n",
       " 'Afas. Missão Exterior Com Ônus - EST                        ',\n",
       " 'Afas. Missão Exterior Com Ônus Limitado - EST               ',\n",
       " 'Afas. Missão Exterior Sem Ônus - EST                        ',\n",
       " 'Afas. Part.Pro.Pos.Grad. Stricto Sensu no País C/Ônus - EST ',\n",
       " 'Afas. Part.Prog.Trein. (Congr) Fora  País C/Ônus Limit - EST',\n",
       " 'Afas. Part.Prog.Trein.(Cong-Encont) Fora País C/Ônus - EST  ',\n",
       " 'Afas. Part.Prog.Trein.(Congr-Encont )Fora  País S/Ônus - EST',\n",
       " 'Afas. Prestar Colaboração a Outra ICT - EST                 ',\n",
       " 'Afas. Prestar Colaboração, PCCTAE e Magistério Federal - EST',\n",
       " 'Afas. Preventivo - EST                                      ',\n",
       " 'Afas. Servir Organ. Internacional S/Ônus - EST              ',\n",
       " 'Afas. Servir a Outro Órgão (REQUISIÇÃO) - EST               ',\n",
       " 'Afas. Serviço Externo - CLT                                 ',\n",
       " 'Afas. Sindicância (Suspensão - art. 145) - EST              ',\n",
       " 'Afas. Temporário Serv. Art. 20 Lei 8429/1992 - EST          ',\n",
       " 'Afas. Viagem /Serv Fora do País Com Ônus Limitado - EST     ',\n",
       " 'Afas. Viagem Serviço No País Com Ônus - CLT                 ',\n",
       " 'Afas. Viagem/Serv Fora do País Com Ônus - EST               ',\n",
       " 'Afas. Viagem/Serv País Com Ônus - EST                       ',\n",
       " 'Afas. Viagem/Serv País Com Ônus Limit. - EST                ',\n",
       " 'Afas. no País (Com Ônus) Est/Dout/Mestrado - EST            ',\n",
       " 'Afas. p/ Prestar Colaboração, Carreira do Magistério - EST  ',\n",
       " 'Afastamento COVID-19                                        ',\n",
       " 'Atraso ou Saída Antecipada - CLT                            ',\n",
       " 'Atraso ou Saída Antecipada - EST                            ',\n",
       " 'Ausência Alistamento Eleitoral - EST                        ',\n",
       " 'Auxílio Doença - CLT                                        ',\n",
       " 'CARREIRA DESCENTRALIZADA - EST                              ',\n",
       " 'CESSAO    6 , ART 93, LEI 8112/90 - EST                     ',\n",
       " 'CESSAO (COM ONUS) PARA OUTROS ORGAOS - EST                  ',\n",
       " 'CESSAO (SEM ONUS) PARA OUTROS ORGAOS - EST                  ',\n",
       " 'CESSAO (SEM PAGTO)  OUTROS ORGAOS/PODERES ART. 93 - EST     ',\n",
       " 'CESSAO ESP. (SEM ONUS)  MP 907/19 - EMBRATUR                ',\n",
       " 'CESSÃO  § 6º, art 93, Lei 8112/90 - EST                     ',\n",
       " 'CESSÃO (Com Ônus) para outros Órgãos - EST                  ',\n",
       " 'CESSÃO (Sem Pagto)  Outros Órgãos/Poderes art. 93 - EST     ',\n",
       " 'CESSÃO (Sem Pagto) Serv. Social Autônomo - Lei 13.844/2019  ',\n",
       " 'CESSÃO (Sem Ônus) para Outros Órgãos - EST                  ',\n",
       " 'CESSÃO ESP. (Sem Ônus)  MP 907/19 - EMBRATUR                ',\n",
       " 'CESSÃO SUS - Disp. Estados, D.F.  e   Mun.  -  EST          ',\n",
       " 'Carreira Descentralizada - EST                              ',\n",
       " 'Casamento / União Estável (Lic. Gala) - EST                 ',\n",
       " 'Comparecimento Justiça - CLT                                ',\n",
       " 'Decisão Judicial  (C/Rem.) -  EST                           ',\n",
       " 'Decisão Judicial  - S/Remun. - EST                          ',\n",
       " 'Deslocam. Nova Sede Situação em Transito - EST              ',\n",
       " 'Doação de Sangue - EST                                      ',\n",
       " 'EXER. EXTER. PAR. 6  E 7 , ART. 93, LEI 8.112/1990 - EST    ',\n",
       " 'EXER. PROVIS. PARAG 2 , ART. 84, LEI 8112/1990 - EST        ',\n",
       " 'EXER. PROVISORIO ART. 37, LEI 8.112 DE 1990 - EST           ',\n",
       " 'Eleitoral Convocação Servidores - Lei 9.504 - EST           ',\n",
       " 'Eleitoral Suspensão Pagamento - EST                         ',\n",
       " 'Exer. Exter. Par. 6º e 7º, art. 93, Lei 8.112/1990 - EST    ',\n",
       " 'Exer. Provis. Parag 2º, art. 84, Lei 8112/1990 - EST        ',\n",
       " 'Exer. Provisório Art. 37, Lei 8.112 de 1990 - EST           ',\n",
       " 'Falecimento Pessoa Familia - CLT                            ',\n",
       " 'Falecimento Pessoa da  Familia - EST                        ',\n",
       " 'Falta - EST                                                 ',\n",
       " 'Falta Justificada - Estagiario - ETG                        ',\n",
       " 'Falta Justificada Decisão Administrativa - EST              ',\n",
       " 'Férias - EST                                                ',\n",
       " 'HIST. CLT- Suspensão Preventiva                             ',\n",
       " 'HIST. EST - Afas. Servir Organ. Internacional S/Ônus        ',\n",
       " 'HIST. EST - Aguardando Exoneração                           ',\n",
       " 'HIST. EST L1711/52- Lic. Acompanhar Conjuge                 ',\n",
       " 'HIST. EST L1711/52- Lic. Interesse Particular               ',\n",
       " 'HIST. EST L1711/52- Prisão Administrativa                   ',\n",
       " 'Invalidez Provisória - CLT                                  ',\n",
       " 'LIC. TRATAMENTO DE SAUDE - EST                              ',\n",
       " 'LIC. TRATAMENTO SAUDE  (+ DE 15 DIAS)  -  CONT. RGPS        ',\n",
       " 'Lic. Acidente Trabalho - CLT                                ',\n",
       " 'Lic. Acidente Trabalho Empresa - Convênio INSS - CLT        ',\n",
       " 'Lic. Acidente em Serviço - EST                              ',\n",
       " 'Lic. Adotante - CLT                                         ',\n",
       " 'Lic. Adotante - EST                                         ',\n",
       " 'Lic. Adotante - Prorrogação - EST                           ',\n",
       " 'Lic. Adotante Crianças Até 1 Ano Prorrog. - EST             ',\n",
       " 'Lic. Adotante Crianças Até 1 ano - EST                      ',\n",
       " 'Lic. Adotante Prorrogação - CLT                             ',\n",
       " 'Lic. Adotante Sentença judicial - EST                       ',\n",
       " 'Lic. Ativ. Política C/Rem. (Desincompatibilização) - EST    ',\n",
       " 'Lic. Atividade Empresarial - EST                            ',\n",
       " 'Lic. Atividade Politica Com Remuneração - EST               ',\n",
       " 'Lic. Atividade Politica Sem Remuneração - EST               ',\n",
       " 'Lic. Capacitação - EST                                      ',\n",
       " 'Lic. Desemp. Mand.  Classista (Sem Remuneração) - EST       ',\n",
       " 'Lic. Desemp. Mand. Classista (Com Ressarcimento) - EST      ',\n",
       " 'Lic. Disputa Atividade Politica Com Remuneração - CLT       ',\n",
       " 'Lic. Gestante  ( Concedida Administrat.) - EST              ',\n",
       " 'Lic. Gestante (Concedida SIASS) - EST                       ',\n",
       " 'Lic. Gestante Aborto - EST                                  ',\n",
       " 'Lic. Gestante Natimorto - EST                               ',\n",
       " 'Lic. Gestante Prorrogação - Dec. Jud./Adm - EST             ',\n",
       " 'Lic. Gestante Prorrogação - EST                             ',\n",
       " 'Lic. Incentivada Sem Remuneração - EST                      ',\n",
       " 'Lic. Martern. S/Exclusão Convênio INSS - CLT                ',\n",
       " 'Lic. Mater. Prorrog. S/Exclusão  Pag. Conv. INSS - RGPS     ',\n",
       " 'Lic. Maternidade (S/Pgto) - RGPS                            ',\n",
       " 'Lic. Maternidade -  RGPS                                    ',\n",
       " 'Lic. Maternidade Prorrogação (S/Pagto) - RGPS               ',\n",
       " 'Lic. Maternidade Prorrogação - RGPS                         ',\n",
       " 'Lic. Motiv. Doen. Pes. Fam. Após 60 dias S/Remuneração - EST',\n",
       " 'Lic. Motivo Acompanhamento Conjuge - EST                    ',\n",
       " 'Lic. Motivo Doença Pessoa Familia - CLT                     ',\n",
       " 'Lic. Paternidade  Prorrogação - CLT                         ',\n",
       " 'Lic. Paternidade - CLT                                      ',\n",
       " 'Lic. Paternidade - Dec. Jud. - EST                          ',\n",
       " 'Lic. Paternidade - EST                                      ',\n",
       " 'Lic. Paternidade Prorrogação - EST                          ',\n",
       " 'Lic. Prêmio Por Assiduidade - EST                           ',\n",
       " 'Lic. Serviço Militar - EST                                  ',\n",
       " 'Lic. Tratam. Saúde (Dec. Judicial) - EST                    ',\n",
       " 'Lic. Tratamento Saúde  (+ de 15 dias)  -  Cont. RGPS        ',\n",
       " 'Lic. Tratamento Saúde - (até 15 dias) -  RGPS               ',\n",
       " 'Lic. Tratamento Saúde - Convênio INSS - CLT                 ',\n",
       " 'Lic. Tratamento Saúde Cid Especial - Convênio INSS - CLT    ',\n",
       " 'Lic. Tratamento Saúde Cid Reincid Esp. Conv. INSS - CLT     ',\n",
       " 'Lic. Tratamento Saúde S/Complemento Conv. INSS - CLT        ',\n",
       " 'Lic. Tratamento de Saúde - EST                              ',\n",
       " 'Lic. Tratar de Interesses Particulares - EST                ',\n",
       " 'Lic. para Tratar de Interesse Particular-LTIP - RMI         ',\n",
       " 'Lic. por Motivo de Doença em Pessoa da Família - EST        ',\n",
       " 'Licença Extraordinária (Serv. Ext. Bras) Lei 11440/06 - EST ',\n",
       " 'Licença Tratamento Saúde Inferior 15 dias - EST             ',\n",
       " \"Opção Por Outro Cargo, Acumulação Licita IF'S - EST         \",\n",
       " 'Participação Sist. Nac. Negociação Permanente - EST         ',\n",
       " 'Penalidade Suspensão - EST                                  ',\n",
       " 'Penalidade Suspensão Convertida em Multa - EST              ',\n",
       " 'Prisão Preventiva PCDF - EST                                ',\n",
       " 'RECESSO ESTAGIARIO - ETG                                    ',\n",
       " 'RECLUSÃO (Prisão) - EST                                     ',\n",
       " 'REQUISICAO (AG. REGULAD.) - ART 16 - LEI 9986/2000          ',\n",
       " 'REQUISICAO (AGU) ART. 47 - LEI COMPLEMENTAR 73/1993         ',\n",
       " 'REQUISICAO (CADE) INC. XII - ART. 9  - LEI 12.529/2011      ',\n",
       " 'REQUISICAO (EPL)  ART. 51   1  E    2   - LEI 13.464/2017   ',\n",
       " 'REQUISICAO (IBRAM)  LEI N  11.906/2009                      ',\n",
       " 'REQUISICAO (LEI ELEITORAL) - LEI 6.999/1982                 ',\n",
       " 'REQUISICAO (MINIST. DEFESA) - ART. 66, DEC. 9.570/2018      ',\n",
       " 'REQUISICAO (MJSP - MMFDH)  - LEI 13.844/2019                ',\n",
       " 'REQUISICAO (MPU) LEI COMPLEM. N  75/93 E LEG. CORRELATAS    ',\n",
       " 'REQUISICAO (P.R.) LEI 9.007/95 E LEG. CORRELATAS            ',\n",
       " 'REQUISICÃO (AGU) Art. 47 - Lei Complementar 73/1993         ',\n",
       " 'REQUISICÃO (DPU) Lei nº 9.020/1995                          ',\n",
       " 'REQUISICÃO (Lei Eleitoral) - Lei 6.999/1982                 ',\n",
       " 'REQUISIÇÃO (AG. REGULAD.) - Art 16 - Lei 9986/2000          ',\n",
       " 'REQUISIÇÃO (CADE) inc. XII - art. 9º - Lei 12.529/2011      ',\n",
       " 'REQUISIÇÃO (EPL)  art. 51 § 1º e  § 2º  - Lei 13.464/2017   ',\n",
       " 'REQUISIÇÃO (IBRAM)  Lei nº 11.906/2009                      ',\n",
       " 'REQUISIÇÃO (MJSP - MMFDH)  - Lei 13.844/2019                ',\n",
       " 'REQUISIÇÃO (MPU) Lei Complem. nº 75/93 e Leg. Correlatas    ',\n",
       " 'REQUISIÇÃO (Minist. Defesa) - art. 66, Dec. 9.570/2018      ',\n",
       " 'REQUISIÇÃO (P.R.) Lei 9.007/95 e Leg. Correlatas            ',\n",
       " 'Recesso Estagiário - ETG                                    ',\n",
       " 'Reclusão (Prisão) - Dec. Jud. - EST                         ',\n",
       " 'Serviço Militar - CLT                                       ',\n",
       " 'Suspensão Contrato Trabalho - CLT                           ',\n",
       " 'Suspensão Temporária Administrativa - CLT                   ',\n",
       " 'Suspensão Temporária Administrativa - EST                   ',\n",
       " 'TELETRABALHO INTEGRAL                                       ',\n",
       " 'TRABALHO REMOTO COVID-19                                    ',\n",
       " 'Trabalho Remoto COVID-19                                    ',\n",
       " 'Tratamento Doença Especificada em Lei - EST                 ',\n",
       " 'Tratamento Doença Especificado em Lei - EST                 '}"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(lic_2020_03_11['Descrição do afastamento'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": false
   },
   "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></th>\n",
       "      <th>Descrição do afastamento</th>\n",
       "      <th>Ano/Mês início afastamento</th>\n",
       "      <th>CPF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Descrição do afastamento</th>\n",
       "      <th>Ano/Mês início afastamento</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"9\" valign=\"top\">Lic. Atividade Politica Com Remuneração - EST</th>\n",
       "      <th>201701</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201904</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202004</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202006</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202007</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202008</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202009</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202010</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202011</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                               Descrição do afastamento  \\\n",
       "Descrição do afastamento                           Ano/Mês início afastamento                             \n",
       "Lic. Atividade Politica Com Remuneração - EST  ... 201701                                             1   \n",
       "                                                   201904                                             1   \n",
       "                                                   202004                                             1   \n",
       "                                                   202006                                             1   \n",
       "                                                   202007                                             1   \n",
       "                                                   202008                                             1   \n",
       "                                                   202009                                             1   \n",
       "                                                   202010                                             1   \n",
       "                                                   202011                                             1   \n",
       "\n",
       "                                                                               Ano/Mês início afastamento  \\\n",
       "Descrição do afastamento                           Ano/Mês início afastamento                               \n",
       "Lic. Atividade Politica Com Remuneração - EST  ... 201701                                               1   \n",
       "                                                   201904                                               1   \n",
       "                                                   202004                                               1   \n",
       "                                                   202006                                               1   \n",
       "                                                   202007                                               1   \n",
       "                                                   202008                                               1   \n",
       "                                                   202009                                               1   \n",
       "                                                   202010                                               1   \n",
       "                                                   202011                                               1   \n",
       "\n",
       "                                                                               CPF  \n",
       "Descrição do afastamento                           Ano/Mês início afastamento       \n",
       "Lic. Atividade Politica Com Remuneração - EST  ... 201701                        1  \n",
       "                                                   201904                        1  \n",
       "                                                   202004                       22  \n",
       "                                                   202006                        5  \n",
       "                                                   202007                        7  \n",
       "                                                   202008                      833  \n",
       "                                                   202009                       38  \n",
       "                                                   202010                       30  \n",
       "                                                   202011                        1  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lic_2020_03_11[lic_2020_03_11['Descrição do afastamento'] == 'Lic. Atividade Politica Com Remuneração - EST               '][['Descrição do afastamento', 'Ano/Mês início afastamento', 'CPF']].groupby(['Descrição do afastamento', 'Ano/Mês início afastamento']).nunique()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_texto(x):\n",
    "    x_ = x.replace('  ', '')\n",
    "    if x_[-1] == ' ':\n",
    "        y = x_[:-1]\n",
    "    else:\n",
    "        y = x_\n",
    "    return y\n",
    "\n",
    "lic_2020_03_11['Nome'] = lic_2020_03_11['Nome'].map(corrige_texto)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "lic_2020_03_11['cpf_miolo'] = lic_2020_03_11['CPF'].apply(lambda x: x[3:-2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "scrolled": true
   },
   "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>Nome</th>\n",
       "      <th>CPF</th>\n",
       "      <th>Descrição do cargo emprego</th>\n",
       "      <th>Nome do órgão de origem</th>\n",
       "      <th>UF da UPAG de vinculação</th>\n",
       "      <th>Cidade da residência</th>\n",
       "      <th>Nível escolaridade</th>\n",
       "      <th>Ano/Mês início afastamento</th>\n",
       "      <th>Ano/Mês referência</th>\n",
       "      <th>Valor rendimento liquido</th>\n",
       "      <th>Descrição do afastamento</th>\n",
       "      <th></th>\n",
       "      <th>cpf_miolo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SONIA CANDIDA BATISTA</td>\n",
       "      <td>***086171**</td>\n",
       "      <td>CONTADOR</td>\n",
       "      <td>MINIST.DA AGRICULTURA,PECUARIA E ABAST.</td>\n",
       "      <td>DF</td>\n",
       "      <td>BRASILIA</td>\n",
       "      <td>NS</td>\n",
       "      <td>201005</td>\n",
       "      <td>202003</td>\n",
       "      <td>11549,56</td>\n",
       "      <td>CESSÃO (Com Ônus) para outros Órgãos - EST    ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>086171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MARIA GRACI DA CONCEICAO MAXIMA</td>\n",
       "      <td>***803611**</td>\n",
       "      <td>AGENTE DE PORTARIA</td>\n",
       "      <td>MINIST.DA AGRICULTURA,PECUARIA E ABAST.</td>\n",
       "      <td>DF</td>\n",
       "      <td>BRASILIA</td>\n",
       "      <td>NI</td>\n",
       "      <td>201703</td>\n",
       "      <td>202003</td>\n",
       "      <td>5952,27</td>\n",
       "      <td>CESSÃO (Com Ônus) para outros Órgãos - EST    ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>803611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>JURCINO CORDEIRO DA ROCHA</td>\n",
       "      <td>***886561**</td>\n",
       "      <td>AGENTE DE PORTARIA</td>\n",
       "      <td>MINIST.DA AGRICULTURA,PECUARIA E ABAST.</td>\n",
       "      <td>DF</td>\n",
       "      <td>BRASILIA</td>\n",
       "      <td>NI</td>\n",
       "      <td>200705</td>\n",
       "      <td>202003</td>\n",
       "      <td>6287,67</td>\n",
       "      <td>CESSÃO (Sem Ônus) para Outros Órgãos - EST    ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>886561</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Nome          CPF  \\\n",
       "0            SONIA CANDIDA BATISTA  ***086171**   \n",
       "1  MARIA GRACI DA CONCEICAO MAXIMA  ***803611**   \n",
       "2        JURCINO CORDEIRO DA ROCHA  ***886561**   \n",
       "\n",
       "                 Descrição do cargo emprego  \\\n",
       "0  CONTADOR                                   \n",
       "1  AGENTE DE PORTARIA                         \n",
       "2  AGENTE DE PORTARIA                         \n",
       "\n",
       "                    Nome do órgão de origem UF da UPAG de vinculação  \\\n",
       "0  MINIST.DA AGRICULTURA,PECUARIA E ABAST.                        DF   \n",
       "1  MINIST.DA AGRICULTURA,PECUARIA E ABAST.                        DF   \n",
       "2  MINIST.DA AGRICULTURA,PECUARIA E ABAST.                        DF   \n",
       "\n",
       "             Cidade da residência Nível escolaridade  \\\n",
       "0  BRASILIA                                       NS   \n",
       "1  BRASILIA                                       NI   \n",
       "2  BRASILIA                                       NI   \n",
       "\n",
       "   Ano/Mês início afastamento  Ano/Mês referência Valor rendimento liquido  \\\n",
       "0                      201005              202003                 11549,56   \n",
       "1                      201703              202003                  5952,27   \n",
       "2                      200705              202003                  6287,67   \n",
       "\n",
       "                            Descrição do afastamento  \\\n",
       "0  CESSÃO (Com Ônus) para outros Órgãos - EST    ...   \n",
       "1  CESSÃO (Com Ônus) para outros Órgãos - EST    ...   \n",
       "2  CESSÃO (Sem Ônus) para Outros Órgãos - EST    ...   \n",
       "\n",
       "                                                               cpf_miolo  \n",
       "0                                                NaN              086171  \n",
       "1                                                NaN              803611  \n",
       "2                                                NaN              886561  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lic_2020_03_11.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_alvos = lic_2020_03_11[(lic_2020_03_11['Descrição do afastamento'] == 'Lic. Atividade Politica Com Remuneração - EST               ') & (lic_2020_03_11['Ano/Mês início afastamento'] >= 202008)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Nome</th>\n",
       "      <th>CPF</th>\n",
       "      <th>Descrição do cargo emprego</th>\n",
       "      <th>Nome do órgão de origem</th>\n",
       "      <th>UF da UPAG de vinculação</th>\n",
       "      <th>Cidade da residência</th>\n",
       "      <th>Nível escolaridade</th>\n",
       "      <th>Ano/Mês início afastamento</th>\n",
       "      <th>Ano/Mês referência</th>\n",
       "      <th>Valor rendimento liquido</th>\n",
       "      <th>Descrição do afastamento</th>\n",
       "      <th></th>\n",
       "      <th>cpf_miolo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>JOAILTON MANOEL DE JESUS</td>\n",
       "      <td>***675785**</td>\n",
       "      <td>AUDITOR FISCAL FEDERAL AGROPECUARIO</td>\n",
       "      <td>MINIST.DA AGRICULTURA,PECUARIA E ABAST.</td>\n",
       "      <td>BA</td>\n",
       "      <td>VALENCA</td>\n",
       "      <td>NS</td>\n",
       "      <td>202008</td>\n",
       "      <td>202008</td>\n",
       "      <td>24760,05</td>\n",
       "      <td>Lic. Atividade Politica Com Remuneração - EST ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>675785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>HELVECIO SERRA RUFINO</td>\n",
       "      <td>***768545**</td>\n",
       "      <td>AGENTE ADMINISTRATIVO</td>\n",
       "      <td>MINIST.DA AGRICULTURA,PECUARIA E ABAST.</td>\n",
       "      <td>BA</td>\n",
       "      <td>UNA</td>\n",
       "      <td>NI</td>\n",
       "      <td>202008</td>\n",
       "      <td>202008</td>\n",
       "      <td>7137,64</td>\n",
       "      <td>Lic. Atividade Politica Com Remuneração - EST ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>768545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>ELIAS PAES BARRETO</td>\n",
       "      <td>***922162**</td>\n",
       "      <td>TECNICO DE LABORATORIO</td>\n",
       "      <td>MINIST.DA AGRICULTURA,PECUARIA E ABAST.</td>\n",
       "      <td>BA</td>\n",
       "      <td>ANANINDEUA</td>\n",
       "      <td>NI</td>\n",
       "      <td>202008</td>\n",
       "      <td>202008</td>\n",
       "      <td>11258,44</td>\n",
       "      <td>Lic. Atividade Politica Com Remuneração - EST ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>922162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>RAIMUNDO MONTEIRO RAMOS</td>\n",
       "      <td>***179565**</td>\n",
       "      <td>AGENTE DE PORTARIA</td>\n",
       "      <td>MINIST.DA AGRICULTURA,PECUARIA E ABAST.</td>\n",
       "      <td>BA</td>\n",
       "      <td>ILHEUS</td>\n",
       "      <td>NI</td>\n",
       "      <td>202008</td>\n",
       "      <td>202008</td>\n",
       "      <td>6924,18</td>\n",
       "      <td>Lic. Atividade Politica Com Remuneração - EST ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>179565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240</th>\n",
       "      <td>ROMALI MOURA DOS SANTOS</td>\n",
       "      <td>***686275**</td>\n",
       "      <td>AGENTE ADMINISTRATIVO</td>\n",
       "      <td>MINIST.DA AGRICULTURA,PECUARIA E ABAST.</td>\n",
       "      <td>BA</td>\n",
       "      <td>PORTO SEGURO</td>\n",
       "      <td>NI</td>\n",
       "      <td>202008</td>\n",
       "      <td>202008</td>\n",
       "      <td>6875,26</td>\n",
       "      <td>Lic. Atividade Politica Com Remuneração - EST ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>686275</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Nome          CPF  \\\n",
       "192  JOAILTON MANOEL DE JESUS  ***675785**   \n",
       "207     HELVECIO SERRA RUFINO  ***768545**   \n",
       "217        ELIAS PAES BARRETO  ***922162**   \n",
       "229   RAIMUNDO MONTEIRO RAMOS  ***179565**   \n",
       "240   ROMALI MOURA DOS SANTOS  ***686275**   \n",
       "\n",
       "                   Descrição do cargo emprego  \\\n",
       "192  AUDITOR FISCAL FEDERAL AGROPECUARIO        \n",
       "207  AGENTE ADMINISTRATIVO                      \n",
       "217  TECNICO DE LABORATORIO                     \n",
       "229  AGENTE DE PORTARIA                         \n",
       "240  AGENTE ADMINISTRATIVO                      \n",
       "\n",
       "                      Nome do órgão de origem UF da UPAG de vinculação  \\\n",
       "192  MINIST.DA AGRICULTURA,PECUARIA E ABAST.                        BA   \n",
       "207  MINIST.DA AGRICULTURA,PECUARIA E ABAST.                        BA   \n",
       "217  MINIST.DA AGRICULTURA,PECUARIA E ABAST.                        BA   \n",
       "229  MINIST.DA AGRICULTURA,PECUARIA E ABAST.                        BA   \n",
       "240  MINIST.DA AGRICULTURA,PECUARIA E ABAST.                        BA   \n",
       "\n",
       "               Cidade da residência Nível escolaridade  \\\n",
       "192  VALENCA                                        NS   \n",
       "207  UNA                                            NI   \n",
       "217  ANANINDEUA                                     NI   \n",
       "229  ILHEUS                                         NI   \n",
       "240  PORTO SEGURO                                   NI   \n",
       "\n",
       "     Ano/Mês início afastamento  Ano/Mês referência Valor rendimento liquido  \\\n",
       "192                      202008              202008                 24760,05   \n",
       "207                      202008              202008                  7137,64   \n",
       "217                      202008              202008                 11258,44   \n",
       "229                      202008              202008                  6924,18   \n",
       "240                      202008              202008                  6875,26   \n",
       "\n",
       "                              Descrição do afastamento  \\\n",
       "192  Lic. Atividade Politica Com Remuneração - EST ...   \n",
       "207  Lic. Atividade Politica Com Remuneração - EST ...   \n",
       "217  Lic. Atividade Politica Com Remuneração - EST ...   \n",
       "229  Lic. Atividade Politica Com Remuneração - EST ...   \n",
       "240  Lic. Atividade Politica Com Remuneração - EST ...   \n",
       "\n",
       "                                                                 cpf_miolo  \n",
       "192                                                NaN              675785  \n",
       "207                                                NaN              768545  \n",
       "217                                                NaN              922162  \n",
       "229                                                NaN              179565  \n",
       "240                                                NaN              686275  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_alvos.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Nome',\n",
       " 'CPF',\n",
       " 'Descrição do cargo emprego',\n",
       " 'Nome do órgão de origem',\n",
       " 'UF da UPAG de vinculação',\n",
       " 'Cidade da residência',\n",
       " 'Nível escolaridade',\n",
       " 'Ano/Mês início afastamento',\n",
       " 'Ano/Mês referência',\n",
       " 'Valor rendimento liquido',\n",
       " 'Descrição do afastamento',\n",
       " '                                                           ',\n",
       " 'cpf_miolo']"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(df_alvos)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/leonardosales/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:5: 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/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "def corrige_numero(x):\n",
    "    y = float(x.replace(',', '.'))\n",
    "    return y\n",
    "\n",
    "df_alvos['Valor rendimento liquido'] = df_alvos['Valor rendimento liquido'].map(corrige_numero)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_alvos_resumo = df_alvos[['Nome', 'CPF', 'cpf_miolo', 'UF da UPAG de vinculação', 'Ano/Mês início afastamento']].groupby(['Nome', 'CPF', 'cpf_miolo', 'UF da UPAG de vinculação']).count().reset_index()\\\n",
    "                 .merge(df_alvos[['Nome', 'CPF', 'cpf_miolo', 'UF da UPAG de vinculação', 'Valor rendimento liquido']].groupby(['Nome', 'CPF', 'cpf_miolo', 'UF da UPAG de vinculação']).mean().reset_index()\\\n",
    "                 , how = 'inner', on = ['Nome', 'CPF', 'cpf_miolo', 'UF da UPAG de vinculação'])\n",
    "\n",
    "df_alvos_resumo.columns = ['Nome', 'CPF', 'cpf_miolo', 'UF da UPAG de vinculação', 'num_meses_registro_afastamento', 'Valor rendimento liquido']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "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>Nome</th>\n",
       "      <th>CPF</th>\n",
       "      <th>cpf_miolo</th>\n",
       "      <th>UF da UPAG de vinculação</th>\n",
       "      <th>num_meses_registro_afastamento</th>\n",
       "      <th>Valor rendimento liquido</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABEL AURELIO DUARTE FILHO</td>\n",
       "      <td>***650424**</td>\n",
       "      <td>650424</td>\n",
       "      <td>AL</td>\n",
       "      <td>2</td>\n",
       "      <td>11965.685000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABRAAO CAVALCANTI DE LACERDA</td>\n",
       "      <td>***297894**</td>\n",
       "      <td>297894</td>\n",
       "      <td>PB</td>\n",
       "      <td>3</td>\n",
       "      <td>6218.146667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABRAAO SOARES DIAS DOS SANTOS GRACCO</td>\n",
       "      <td>***021156**</td>\n",
       "      <td>021156</td>\n",
       "      <td>DF</td>\n",
       "      <td>4</td>\n",
       "      <td>34675.395000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Nome          CPF cpf_miolo  \\\n",
       "0             ABEL AURELIO DUARTE FILHO  ***650424**    650424   \n",
       "1          ABRAAO CAVALCANTI DE LACERDA  ***297894**    297894   \n",
       "2  ABRAAO SOARES DIAS DOS SANTOS GRACCO  ***021156**    021156   \n",
       "\n",
       "  UF da UPAG de vinculação  num_meses_registro_afastamento  \\\n",
       "0                       AL                               2   \n",
       "1                       PB                               3   \n",
       "2                       DF                               4   \n",
       "\n",
       "   Valor rendimento liquido  \n",
       "0              11965.685000  \n",
       "1               6218.146667  \n",
       "2              34675.395000  "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_alvos_resumo.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Dados do TSE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tse_votos = pd.read_csv('../dados_tse/2020/votacao_candidato_munzona_2020_BRASIL.csv', sep = ';', encoding = 'latin1')\n",
    "df_tse_votos_grupo = df_tse_votos[['SG_UF', 'DS_CARGO', 'SQ_CANDIDATO', 'NM_CANDIDATO', 'SG_PARTIDO', 'DS_SIT_TOT_TURNO', 'DS_SITUACAO_CANDIDATURA', 'QT_VOTOS_NOMINAIS']].groupby(['SG_UF', 'DS_CARGO', 'SQ_CANDIDATO', 'NM_CANDIDATO', 'SG_PARTIDO', 'DS_SIT_TOT_TURNO', 'DS_SITUACAO_CANDIDATURA']).sum().reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['DT_GERACAO', 'HH_GERACAO', 'ANO_ELEICAO', 'CD_TIPO_ELEICAO',\n",
       "       'NM_TIPO_ELEICAO', 'NR_TURNO', 'CD_ELEICAO', 'DS_ELEICAO', 'DT_ELEICAO',\n",
       "       'TP_ABRANGENCIA', 'SG_UF', 'SG_UE', 'NM_UE', 'CD_MUNICIPIO',\n",
       "       'NM_MUNICIPIO', 'NR_ZONA', 'CD_CARGO', 'DS_CARGO', 'SQ_CANDIDATO',\n",
       "       'NR_CANDIDATO', 'NM_CANDIDATO', 'NM_URNA_CANDIDATO',\n",
       "       'NM_SOCIAL_CANDIDATO', 'CD_SITUACAO_CANDIDATURA',\n",
       "       'DS_SITUACAO_CANDIDATURA', 'CD_DETALHE_SITUACAO_CAND',\n",
       "       'DS_DETALHE_SITUACAO_CAND', 'TP_AGREMIACAO', 'NR_PARTIDO', 'SG_PARTIDO',\n",
       "       'NM_PARTIDO', 'SQ_COLIGACAO', 'NM_COLIGACAO', 'DS_COMPOSICAO_COLIGACAO',\n",
       "       'CD_SIT_TOT_TURNO', 'DS_SIT_TOT_TURNO', 'ST_VOTO_EM_TRANSITO',\n",
       "       'QT_VOTOS_NOMINAIS'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tse_votos.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'APTO'}"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df_tse_votos['DS_SITUACAO_CANDIDATURA'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>SG_UF</th>\n",
       "      <th>DS_CARGO</th>\n",
       "      <th>SQ_CANDIDATO</th>\n",
       "      <th>NM_CANDIDATO</th>\n",
       "      <th>SG_PARTIDO</th>\n",
       "      <th>DS_SIT_TOT_TURNO</th>\n",
       "      <th>DS_SITUACAO_CANDIDATURA</th>\n",
       "      <th>QT_VOTOS_NOMINAIS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>115395</th>\n",
       "      <td>MA</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>100001061034</td>\n",
       "      <td>OZENEIDE SOUSA DE LIMA LEITE</td>\n",
       "      <td>PODE</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303674</th>\n",
       "      <td>PR</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>160001142476</td>\n",
       "      <td>DJONATAN DE SOUZA</td>\n",
       "      <td>PDT</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176656</th>\n",
       "      <td>MG</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>130001110286</td>\n",
       "      <td>VIVIANE VALADÃO RODRIGUES DE MELO</td>\n",
       "      <td>PATRIOTA</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357022</th>\n",
       "      <td>RS</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>210000682946</td>\n",
       "      <td>GECI BARBON</td>\n",
       "      <td>PTB</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67196</th>\n",
       "      <td>CE</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>60001044985</td>\n",
       "      <td>JORGE DE PAULO CASTRO NETO</td>\n",
       "      <td>REPUBLICANOS</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       SG_UF  DS_CARGO  SQ_CANDIDATO                       NM_CANDIDATO  \\\n",
       "115395    MA  Vereador  100001061034       OZENEIDE SOUSA DE LIMA LEITE   \n",
       "303674    PR  Vereador  160001142476                  DJONATAN DE SOUZA   \n",
       "176656    MG  Vereador  130001110286  VIVIANE VALADÃO RODRIGUES DE MELO   \n",
       "357022    RS  Vereador  210000682946                        GECI BARBON   \n",
       "67196     CE  Vereador   60001044985         JORGE DE PAULO CASTRO NETO   \n",
       "\n",
       "          SG_PARTIDO DS_SIT_TOT_TURNO DS_SITUACAO_CANDIDATURA  \\\n",
       "115395          PODE         SUPLENTE                    APTO   \n",
       "303674           PDT         SUPLENTE                    APTO   \n",
       "176656      PATRIOTA       NÃO ELEITO                    APTO   \n",
       "357022           PTB       NÃO ELEITO                    APTO   \n",
       "67196   REPUBLICANOS       NÃO ELEITO                    APTO   \n",
       "\n",
       "        QT_VOTOS_NOMINAIS  \n",
       "115395                404  \n",
       "303674                 24  \n",
       "176656                 41  \n",
       "357022                  2  \n",
       "67196                 210  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tse_votos_grupo.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_candidatos = pd.read_csv('../dados_tse/2020/consulta_cand_2020_BRASIL.csv', sep = ';', encoding = 'latin1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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>SQ_CANDIDATO</th>\n",
       "      <th>NR_CPF_CANDIDATO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>191725</th>\n",
       "      <td>50000880784</td>\n",
       "      <td>37308467520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>425012</th>\n",
       "      <td>250001151042</td>\n",
       "      <td>30679519807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263424</th>\n",
       "      <td>210001150815</td>\n",
       "      <td>44650426049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>401254</th>\n",
       "      <td>210000749949</td>\n",
       "      <td>676469051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43445</th>\n",
       "      <td>190001066803</td>\n",
       "      <td>12569028703</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        SQ_CANDIDATO  NR_CPF_CANDIDATO\n",
       "191725   50000880784       37308467520\n",
       "425012  250001151042       30679519807\n",
       "263424  210001150815       44650426049\n",
       "401254  210000749949         676469051\n",
       "43445   190001066803       12569028703"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_candidatos[['SQ_CANDIDATO', 'NR_CPF_CANDIDATO']].sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['DT_GERACAO', 'HH_GERACAO', 'ANO_ELEICAO', 'CD_TIPO_ELEICAO',\n",
       "       'NM_TIPO_ELEICAO', 'NR_TURNO', 'CD_ELEICAO', 'DS_ELEICAO', 'DT_ELEICAO',\n",
       "       'TP_ABRANGENCIA', 'SG_UF', 'SG_UE', 'NM_UE', 'CD_CARGO', 'DS_CARGO',\n",
       "       'SQ_CANDIDATO', 'NR_CANDIDATO', 'NM_CANDIDATO', 'NM_URNA_CANDIDATO',\n",
       "       'NM_SOCIAL_CANDIDATO', 'NR_CPF_CANDIDATO', 'NM_EMAIL',\n",
       "       'CD_SITUACAO_CANDIDATURA', 'DS_SITUACAO_CANDIDATURA',\n",
       "       'CD_DETALHE_SITUACAO_CAND', 'DS_DETALHE_SITUACAO_CAND', 'TP_AGREMIACAO',\n",
       "       'NR_PARTIDO', 'SG_PARTIDO', 'NM_PARTIDO', 'SQ_COLIGACAO',\n",
       "       'NM_COLIGACAO', 'DS_COMPOSICAO_COLIGACAO', 'CD_NACIONALIDADE',\n",
       "       'DS_NACIONALIDADE', 'SG_UF_NASCIMENTO', 'CD_MUNICIPIO_NASCIMENTO',\n",
       "       'NM_MUNICIPIO_NASCIMENTO', 'DT_NASCIMENTO', 'NR_IDADE_DATA_POSSE',\n",
       "       'NR_TITULO_ELEITORAL_CANDIDATO', 'CD_GENERO', 'DS_GENERO',\n",
       "       'CD_GRAU_INSTRUCAO', 'DS_GRAU_INSTRUCAO', 'CD_ESTADO_CIVIL',\n",
       "       'DS_ESTADO_CIVIL', 'CD_COR_RACA', 'DS_COR_RACA', 'CD_OCUPACAO',\n",
       "       'DS_OCUPACAO', 'VR_DESPESA_MAX_CAMPANHA', 'CD_SIT_TOT_TURNO',\n",
       "       'DS_SIT_TOT_TURNO', 'ST_REELEICAO', 'ST_DECLARAR_BENS',\n",
       "       'NR_PROTOCOLO_CANDIDATURA', 'NR_PROCESSO',\n",
       "       'CD_SITUACAO_CANDIDATO_PLEITO', 'DS_SITUACAO_CANDIDATO_PLEITO',\n",
       "       'CD_SITUACAO_CANDIDATO_URNA', 'DS_SITUACAO_CANDIDATO_URNA',\n",
       "       'ST_CANDIDATO_INSERIDO_URNA'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_candidatos.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'NÃO', 'SIM'}"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df_candidatos['ST_CANDIDATO_INSERIDO_URNA'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tse_votos_grupo = df_tse_votos_grupo.merge(df_candidatos[['SQ_CANDIDATO', 'NR_CPF_CANDIDATO', 'ST_CANDIDATO_INSERIDO_URNA']], on = 'SQ_CANDIDATO', how = 'inner')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "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>SG_UF</th>\n",
       "      <th>DS_CARGO</th>\n",
       "      <th>SQ_CANDIDATO</th>\n",
       "      <th>NM_CANDIDATO</th>\n",
       "      <th>SG_PARTIDO</th>\n",
       "      <th>DS_SIT_TOT_TURNO</th>\n",
       "      <th>DS_SITUACAO_CANDIDATURA</th>\n",
       "      <th>QT_VOTOS_NOMINAIS</th>\n",
       "      <th>NR_CPF_CANDIDATO</th>\n",
       "      <th>ST_CANDIDATO_INSERIDO_URNA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000644872</td>\n",
       "      <td>BARTOLOMEU FRANCISCO DA ROCHA MARQUES</td>\n",
       "      <td>PSOL</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>280</td>\n",
       "      <td>43459234253</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000645733</td>\n",
       "      <td>ISAAC DE SOUZA LIMA</td>\n",
       "      <td>PT</td>\n",
       "      <td>ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>4115</td>\n",
       "      <td>34009973234</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000647115</td>\n",
       "      <td>SEBASTIÃO CRUZ DA SILVA</td>\n",
       "      <td>REPUBLICANOS</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>1063</td>\n",
       "      <td>21577056272</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000650671</td>\n",
       "      <td>JOSE FERREIRA DO NASCIMENTO FILHO</td>\n",
       "      <td>PSD</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>1082</td>\n",
       "      <td>56553390215</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000650857</td>\n",
       "      <td>JOSE ALTANIZIO TAUMATURGO SÁ</td>\n",
       "      <td>MDB</td>\n",
       "      <td>ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>3082</td>\n",
       "      <td>30875978215</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000664751</td>\n",
       "      <td>FRANCISCO RODRIGUES DE LIMA</td>\n",
       "      <td>MDB</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>3460</td>\n",
       "      <td>55901301234</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000664926</td>\n",
       "      <td>JAILSON EUGENIO DE SOUZA</td>\n",
       "      <td>PSOL</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>4</td>\n",
       "      <td>71110844204</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000702031</td>\n",
       "      <td>FRANCISCO TAVARES DE SOUZA</td>\n",
       "      <td>MDB</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>2679</td>\n",
       "      <td>4577345234</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000702299</td>\n",
       "      <td>ROSANGELA SILVA DOS SANTOS</td>\n",
       "      <td>PROS</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>569</td>\n",
       "      <td>56464207253</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000733704</td>\n",
       "      <td>MANOEL PRETE</td>\n",
       "      <td>PSDB</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>627</td>\n",
       "      <td>31207642800</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000739945</td>\n",
       "      <td>RIBAMAR DE SOUSA FEITOZA JUNIOR</td>\n",
       "      <td>MDB</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>3634</td>\n",
       "      <td>67146902234</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000744295</td>\n",
       "      <td>VALDIVINO NONATO DE SOUSA</td>\n",
       "      <td>PSB</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>735</td>\n",
       "      <td>5162602272</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000750117</td>\n",
       "      <td>JESUS SEBASTIAN LOPEZ CARDOSO</td>\n",
       "      <td>PC do B</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>749</td>\n",
       "      <td>32243820234</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000766416</td>\n",
       "      <td>FRANCISCO GUIMARÃES PEREIRA</td>\n",
       "      <td>PTB</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>57</td>\n",
       "      <td>19651643234</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000775749</td>\n",
       "      <td>SERGIO LOPES DE SOUZA</td>\n",
       "      <td>PSDB</td>\n",
       "      <td>ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>3984</td>\n",
       "      <td>59003227268</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000801178</td>\n",
       "      <td>JOSE ARNOLDO LIMA DE ALMEIDA</td>\n",
       "      <td>PROS</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>2012</td>\n",
       "      <td>43449140230</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000801971</td>\n",
       "      <td>ISAAC DA SILVA PIYAKO</td>\n",
       "      <td>PSD</td>\n",
       "      <td>ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>4521</td>\n",
       "      <td>43481221215</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000804468</td>\n",
       "      <td>SEBASTIAO NOGUEIRA DE ANDRADE</td>\n",
       "      <td>MDB</td>\n",
       "      <td>ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>3411</td>\n",
       "      <td>64834824268</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000805605</td>\n",
       "      <td>FAGNER ROJAS SALES</td>\n",
       "      <td>MDB</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>14666</td>\n",
       "      <td>72816082253</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000805780</td>\n",
       "      <td>ALDEMAR FERREIRA JUNIOR</td>\n",
       "      <td>PSD</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>2314</td>\n",
       "      <td>46570055291</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000806621</td>\n",
       "      <td>SEBASTIÃO GOMES DOS SANTOS</td>\n",
       "      <td>PSL</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>169</td>\n",
       "      <td>19726155215</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000807983</td>\n",
       "      <td>FRANCISCO NAUDINO RIBEIRO SOUZA</td>\n",
       "      <td>PDT</td>\n",
       "      <td>ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>1264</td>\n",
       "      <td>84378786249</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000808597</td>\n",
       "      <td>NÁGELA MARIA DE FIGUEIRÉDO SILVA</td>\n",
       "      <td>PT</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>915</td>\n",
       "      <td>30846005204</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000809574</td>\n",
       "      <td>KIEFER ROBERTO CAVALCANTE LIMA</td>\n",
       "      <td>PP</td>\n",
       "      <td>ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>8528</td>\n",
       "      <td>30870968220</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000809636</td>\n",
       "      <td>JOSÉ ALTAMIR TAUMATURGO DE SÁ</td>\n",
       "      <td>MDB</td>\n",
       "      <td>ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>1156</td>\n",
       "      <td>37279580220</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000809638</td>\n",
       "      <td>ODEIZA GOMES COELHO</td>\n",
       "      <td>PROS</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>94</td>\n",
       "      <td>46529004220</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000824349</td>\n",
       "      <td>JOÃO ALVES CORDEIRO</td>\n",
       "      <td>PL</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>186</td>\n",
       "      <td>46623558268</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000848975</td>\n",
       "      <td>DANIEL QUEIROZ DE SANT ANA</td>\n",
       "      <td>PT</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>7121</td>\n",
       "      <td>64564177249</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000849828</td>\n",
       "      <td>JAMYL ASFURY MARTINS OLIVEIRA</td>\n",
       "      <td>PSC</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>1509</td>\n",
       "      <td>60746440120</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AC</td>\n",
       "      <td>Prefeito</td>\n",
       "      <td>10000854194</td>\n",
       "      <td>JUCIMAR PESSOA DE SOUZA</td>\n",
       "      <td>SOLIDARIEDADE</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>3993</td>\n",
       "      <td>39079708291</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497512</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001268354</td>\n",
       "      <td>MARIA DO CARMO ALMEIDA DA CONCEIÇÃO</td>\n",
       "      <td>PV</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>9</td>\n",
       "      <td>37593226549</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497513</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001268605</td>\n",
       "      <td>EVA ARRUDA DE ARAUJO</td>\n",
       "      <td>PL</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>36</td>\n",
       "      <td>45715068134</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497514</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001268832</td>\n",
       "      <td>ILMA DE JESUS SOUSA DA SILVA</td>\n",
       "      <td>DC</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>12</td>\n",
       "      <td>7162057268</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497515</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001268958</td>\n",
       "      <td>JOYSE SANDES PONCIANO</td>\n",
       "      <td>PL</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>3</td>\n",
       "      <td>99584247115</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497516</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001269393</td>\n",
       "      <td>FRANCISCO ANICETO SIQUEIRA NETO</td>\n",
       "      <td>DC</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>8</td>\n",
       "      <td>33172382915</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497517</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001270082</td>\n",
       "      <td>MAGDA LUCIA PEREIRA LIMA</td>\n",
       "      <td>PSC</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>54</td>\n",
       "      <td>335470173</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497518</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001270175</td>\n",
       "      <td>LEIDIANE PINTO DE ALENCAR</td>\n",
       "      <td>DEM</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>17</td>\n",
       "      <td>828867178</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497519</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001270176</td>\n",
       "      <td>DEBORA DA SILVA OLIVEIRA</td>\n",
       "      <td>DEM</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>2</td>\n",
       "      <td>2717358137</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497520</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001270499</td>\n",
       "      <td>LUCILENE CARDOSO MONTEIRO RODRIGUES</td>\n",
       "      <td>PSL</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>3</td>\n",
       "      <td>1293190152</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497521</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001270511</td>\n",
       "      <td>SHEILA LIMA DE SOUSA</td>\n",
       "      <td>PSD</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>45</td>\n",
       "      <td>3017898199</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497522</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001270819</td>\n",
       "      <td>MAÍSA RAMOS AGUIAR</td>\n",
       "      <td>PL</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>17</td>\n",
       "      <td>3494171130</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497523</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001271144</td>\n",
       "      <td>LUCINEIDE NOVAIS DOS ANJOS</td>\n",
       "      <td>PTB</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>5</td>\n",
       "      <td>83809759104</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497524</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001271251</td>\n",
       "      <td>MAYSA CORREIA DA SILVA</td>\n",
       "      <td>SOLIDARIEDADE</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>12</td>\n",
       "      <td>1762639181</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497525</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001271408</td>\n",
       "      <td>FRANCIELLY CARDOSO PEREIRA AZEVEDO</td>\n",
       "      <td>PSL</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>4</td>\n",
       "      <td>4677903107</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497526</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001271520</td>\n",
       "      <td>MARIA DE JESUS ARAÚJO DE ALMEIDA</td>\n",
       "      <td>PTB</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>35</td>\n",
       "      <td>49356470359</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497527</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001273453</td>\n",
       "      <td>RAQUEL RIZ VIEIRA CAMPELO</td>\n",
       "      <td>PSL</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>7</td>\n",
       "      <td>3592884131</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497528</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001273531</td>\n",
       "      <td>CLAUBETANIA SOBRINHO MOREIRA</td>\n",
       "      <td>AVANTE</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>0</td>\n",
       "      <td>3979958140</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497529</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001273536</td>\n",
       "      <td>MARIA DO ROSARIO FERREIRA DA SILVA</td>\n",
       "      <td>PV</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>3</td>\n",
       "      <td>661096122</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497530</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001273539</td>\n",
       "      <td>RICARDO SILVA DINIZ</td>\n",
       "      <td>PSC</td>\n",
       "      <td>ELEITO POR MÉDIA</td>\n",
       "      <td>APTO</td>\n",
       "      <td>450</td>\n",
       "      <td>246386177</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497531</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001273602</td>\n",
       "      <td>ELIENE FERNANDES DA SILVA</td>\n",
       "      <td>MDB</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>4</td>\n",
       "      <td>1593996101</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497532</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001273640</td>\n",
       "      <td>JOSELIA DIAS DE ARAÚJO MARTINS</td>\n",
       "      <td>PP</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>17</td>\n",
       "      <td>1353129144</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497533</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001273674</td>\n",
       "      <td>MILENA MORENO DE CARVALHO</td>\n",
       "      <td>PSD</td>\n",
       "      <td>NÃO ELEITO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>0</td>\n",
       "      <td>5762174174</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497534</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001273780</td>\n",
       "      <td>JANETE PEREIRA DA SILVA</td>\n",
       "      <td>SOLIDARIEDADE</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>5</td>\n",
       "      <td>3141305196</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497535</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001274280</td>\n",
       "      <td>SALETE DE SOUSA SILVA</td>\n",
       "      <td>PSL</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>6</td>\n",
       "      <td>5832419138</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497536</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001274346</td>\n",
       "      <td>ELIANE SOARES VIEIRA</td>\n",
       "      <td>PL</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>16</td>\n",
       "      <td>1326386140</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497537</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001274449</td>\n",
       "      <td>NILMARA MACÊDO BITZCOF</td>\n",
       "      <td>DEM</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>46</td>\n",
       "      <td>9119782160</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497538</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001274471</td>\n",
       "      <td>ADAILTA LIMA DOS SANTOS</td>\n",
       "      <td>SOLIDARIEDADE</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>23</td>\n",
       "      <td>71861122187</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497539</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001274474</td>\n",
       "      <td>JOANA BARBOSA AIRES GLÓRIA</td>\n",
       "      <td>SOLIDARIEDADE</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>14</td>\n",
       "      <td>1115017179</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497540</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001275304</td>\n",
       "      <td>GERUILSON ALVES DO NASCIMENTO</td>\n",
       "      <td>PT</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>116</td>\n",
       "      <td>64072304115</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497541</th>\n",
       "      <td>TO</td>\n",
       "      <td>Vereador</td>\n",
       "      <td>270001276242</td>\n",
       "      <td>PATRICIA FREIRE ROCHA</td>\n",
       "      <td>PT</td>\n",
       "      <td>SUPLENTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>14</td>\n",
       "      <td>3384032136</td>\n",
       "      <td>SIM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>497542 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       SG_UF  DS_CARGO  SQ_CANDIDATO                           NM_CANDIDATO  \\\n",
       "0         AC  Prefeito   10000644872  BARTOLOMEU FRANCISCO DA ROCHA MARQUES   \n",
       "1         AC  Prefeito   10000645733                    ISAAC DE SOUZA LIMA   \n",
       "2         AC  Prefeito   10000647115                SEBASTIÃO CRUZ DA SILVA   \n",
       "3         AC  Prefeito   10000650671      JOSE FERREIRA DO NASCIMENTO FILHO   \n",
       "4         AC  Prefeito   10000650857           JOSE ALTANIZIO TAUMATURGO SÁ   \n",
       "5         AC  Prefeito   10000664751            FRANCISCO RODRIGUES DE LIMA   \n",
       "6         AC  Prefeito   10000664926               JAILSON EUGENIO DE SOUZA   \n",
       "7         AC  Prefeito   10000702031             FRANCISCO TAVARES DE SOUZA   \n",
       "8         AC  Prefeito   10000702299             ROSANGELA SILVA DOS SANTOS   \n",
       "9         AC  Prefeito   10000733704                           MANOEL PRETE   \n",
       "10        AC  Prefeito   10000739945        RIBAMAR DE SOUSA FEITOZA JUNIOR   \n",
       "11        AC  Prefeito   10000744295              VALDIVINO NONATO DE SOUSA   \n",
       "12        AC  Prefeito   10000750117          JESUS SEBASTIAN LOPEZ CARDOSO   \n",
       "13        AC  Prefeito   10000766416            FRANCISCO GUIMARÃES PEREIRA   \n",
       "14        AC  Prefeito   10000775749                  SERGIO LOPES DE SOUZA   \n",
       "15        AC  Prefeito   10000801178           JOSE ARNOLDO LIMA DE ALMEIDA   \n",
       "16        AC  Prefeito   10000801971                  ISAAC DA SILVA PIYAKO   \n",
       "17        AC  Prefeito   10000804468          SEBASTIAO NOGUEIRA DE ANDRADE   \n",
       "18        AC  Prefeito   10000805605                     FAGNER ROJAS SALES   \n",
       "19        AC  Prefeito   10000805780                ALDEMAR FERREIRA JUNIOR   \n",
       "20        AC  Prefeito   10000806621             SEBASTIÃO GOMES DOS SANTOS   \n",
       "21        AC  Prefeito   10000807983        FRANCISCO NAUDINO RIBEIRO SOUZA   \n",
       "22        AC  Prefeito   10000808597       NÁGELA MARIA DE FIGUEIRÉDO SILVA   \n",
       "23        AC  Prefeito   10000809574         KIEFER ROBERTO CAVALCANTE LIMA   \n",
       "24        AC  Prefeito   10000809636          JOSÉ ALTAMIR TAUMATURGO DE SÁ   \n",
       "25        AC  Prefeito   10000809638                    ODEIZA GOMES COELHO   \n",
       "26        AC  Prefeito   10000824349                    JOÃO ALVES CORDEIRO   \n",
       "27        AC  Prefeito   10000848975             DANIEL QUEIROZ DE SANT ANA   \n",
       "28        AC  Prefeito   10000849828          JAMYL ASFURY MARTINS OLIVEIRA   \n",
       "29        AC  Prefeito   10000854194                JUCIMAR PESSOA DE SOUZA   \n",
       "...      ...       ...           ...                                    ...   \n",
       "497512    TO  Vereador  270001268354    MARIA DO CARMO ALMEIDA DA CONCEIÇÃO   \n",
       "497513    TO  Vereador  270001268605                   EVA ARRUDA DE ARAUJO   \n",
       "497514    TO  Vereador  270001268832           ILMA DE JESUS SOUSA DA SILVA   \n",
       "497515    TO  Vereador  270001268958                  JOYSE SANDES PONCIANO   \n",
       "497516    TO  Vereador  270001269393        FRANCISCO ANICETO SIQUEIRA NETO   \n",
       "497517    TO  Vereador  270001270082               MAGDA LUCIA PEREIRA LIMA   \n",
       "497518    TO  Vereador  270001270175              LEIDIANE PINTO DE ALENCAR   \n",
       "497519    TO  Vereador  270001270176               DEBORA DA SILVA OLIVEIRA   \n",
       "497520    TO  Vereador  270001270499    LUCILENE CARDOSO MONTEIRO RODRIGUES   \n",
       "497521    TO  Vereador  270001270511                   SHEILA LIMA DE SOUSA   \n",
       "497522    TO  Vereador  270001270819                     MAÍSA RAMOS AGUIAR   \n",
       "497523    TO  Vereador  270001271144             LUCINEIDE NOVAIS DOS ANJOS   \n",
       "497524    TO  Vereador  270001271251                 MAYSA CORREIA DA SILVA   \n",
       "497525    TO  Vereador  270001271408     FRANCIELLY CARDOSO PEREIRA AZEVEDO   \n",
       "497526    TO  Vereador  270001271520       MARIA DE JESUS ARAÚJO DE ALMEIDA   \n",
       "497527    TO  Vereador  270001273453              RAQUEL RIZ VIEIRA CAMPELO   \n",
       "497528    TO  Vereador  270001273531           CLAUBETANIA SOBRINHO MOREIRA   \n",
       "497529    TO  Vereador  270001273536     MARIA DO ROSARIO FERREIRA DA SILVA   \n",
       "497530    TO  Vereador  270001273539                    RICARDO SILVA DINIZ   \n",
       "497531    TO  Vereador  270001273602              ELIENE FERNANDES DA SILVA   \n",
       "497532    TO  Vereador  270001273640         JOSELIA DIAS DE ARAÚJO MARTINS   \n",
       "497533    TO  Vereador  270001273674              MILENA MORENO DE CARVALHO   \n",
       "497534    TO  Vereador  270001273780                JANETE PEREIRA DA SILVA   \n",
       "497535    TO  Vereador  270001274280                  SALETE DE SOUSA SILVA   \n",
       "497536    TO  Vereador  270001274346                   ELIANE SOARES VIEIRA   \n",
       "497537    TO  Vereador  270001274449                 NILMARA MACÊDO BITZCOF   \n",
       "497538    TO  Vereador  270001274471                ADAILTA LIMA DOS SANTOS   \n",
       "497539    TO  Vereador  270001274474             JOANA BARBOSA AIRES GLÓRIA   \n",
       "497540    TO  Vereador  270001275304          GERUILSON ALVES DO NASCIMENTO   \n",
       "497541    TO  Vereador  270001276242                  PATRICIA FREIRE ROCHA   \n",
       "\n",
       "           SG_PARTIDO  DS_SIT_TOT_TURNO DS_SITUACAO_CANDIDATURA  \\\n",
       "0                PSOL        NÃO ELEITO                    APTO   \n",
       "1                  PT            ELEITO                    APTO   \n",
       "2        REPUBLICANOS        NÃO ELEITO                    APTO   \n",
       "3                 PSD        NÃO ELEITO                    APTO   \n",
       "4                 MDB            ELEITO                    APTO   \n",
       "5                 MDB        NÃO ELEITO                    APTO   \n",
       "6                PSOL        NÃO ELEITO                    APTO   \n",
       "7                 MDB        NÃO ELEITO                    APTO   \n",
       "8                PROS        NÃO ELEITO                    APTO   \n",
       "9                PSDB        NÃO ELEITO                    APTO   \n",
       "10                MDB        NÃO ELEITO                    APTO   \n",
       "11                PSB        NÃO ELEITO                    APTO   \n",
       "12            PC do B        NÃO ELEITO                    APTO   \n",
       "13                PTB        NÃO ELEITO                    APTO   \n",
       "14               PSDB            ELEITO                    APTO   \n",
       "15               PROS        NÃO ELEITO                    APTO   \n",
       "16                PSD            ELEITO                    APTO   \n",
       "17                MDB            ELEITO                    APTO   \n",
       "18                MDB        NÃO ELEITO                    APTO   \n",
       "19                PSD        NÃO ELEITO                    APTO   \n",
       "20                PSL        NÃO ELEITO                    APTO   \n",
       "21                PDT            ELEITO                    APTO   \n",
       "22                 PT        NÃO ELEITO                    APTO   \n",
       "23                 PP            ELEITO                    APTO   \n",
       "24                MDB            ELEITO                    APTO   \n",
       "25               PROS        NÃO ELEITO                    APTO   \n",
       "26                 PL        NÃO ELEITO                    APTO   \n",
       "27                 PT        NÃO ELEITO                    APTO   \n",
       "28                PSC        NÃO ELEITO                    APTO   \n",
       "29      SOLIDARIEDADE        NÃO ELEITO                    APTO   \n",
       "...               ...               ...                     ...   \n",
       "497512             PV          SUPLENTE                    APTO   \n",
       "497513             PL          SUPLENTE                    APTO   \n",
       "497514             DC        NÃO ELEITO                    APTO   \n",
       "497515             PL          SUPLENTE                    APTO   \n",
       "497516             DC        NÃO ELEITO                    APTO   \n",
       "497517            PSC          SUPLENTE                    APTO   \n",
       "497518            DEM          SUPLENTE                    APTO   \n",
       "497519            DEM          SUPLENTE                    APTO   \n",
       "497520            PSL          SUPLENTE                    APTO   \n",
       "497521            PSD          SUPLENTE                    APTO   \n",
       "497522             PL          SUPLENTE                    APTO   \n",
       "497523            PTB          SUPLENTE                    APTO   \n",
       "497524  SOLIDARIEDADE          SUPLENTE                    APTO   \n",
       "497525            PSL          SUPLENTE                    APTO   \n",
       "497526            PTB          SUPLENTE                    APTO   \n",
       "497527            PSL          SUPLENTE                    APTO   \n",
       "497528         AVANTE        NÃO ELEITO                    APTO   \n",
       "497529             PV          SUPLENTE                    APTO   \n",
       "497530            PSC  ELEITO POR MÉDIA                    APTO   \n",
       "497531            MDB          SUPLENTE                    APTO   \n",
       "497532             PP          SUPLENTE                    APTO   \n",
       "497533            PSD        NÃO ELEITO                    APTO   \n",
       "497534  SOLIDARIEDADE          SUPLENTE                    APTO   \n",
       "497535            PSL          SUPLENTE                    APTO   \n",
       "497536             PL          SUPLENTE                    APTO   \n",
       "497537            DEM          SUPLENTE                    APTO   \n",
       "497538  SOLIDARIEDADE          SUPLENTE                    APTO   \n",
       "497539  SOLIDARIEDADE          SUPLENTE                    APTO   \n",
       "497540             PT          SUPLENTE                    APTO   \n",
       "497541             PT          SUPLENTE                    APTO   \n",
       "\n",
       "        QT_VOTOS_NOMINAIS  NR_CPF_CANDIDATO ST_CANDIDATO_INSERIDO_URNA  \n",
       "0                     280       43459234253                        SIM  \n",
       "1                    4115       34009973234                        SIM  \n",
       "2                    1063       21577056272                        SIM  \n",
       "3                    1082       56553390215                        SIM  \n",
       "4                    3082       30875978215                        SIM  \n",
       "5                    3460       55901301234                        SIM  \n",
       "6                       4       71110844204                        SIM  \n",
       "7                    2679        4577345234                        SIM  \n",
       "8                     569       56464207253                        SIM  \n",
       "9                     627       31207642800                        SIM  \n",
       "10                   3634       67146902234                        SIM  \n",
       "11                    735        5162602272                        SIM  \n",
       "12                    749       32243820234                        SIM  \n",
       "13                     57       19651643234                        SIM  \n",
       "14                   3984       59003227268                        SIM  \n",
       "15                   2012       43449140230                        SIM  \n",
       "16                   4521       43481221215                        SIM  \n",
       "17                   3411       64834824268                        SIM  \n",
       "18                  14666       72816082253                        SIM  \n",
       "19                   2314       46570055291                        SIM  \n",
       "20                    169       19726155215                        SIM  \n",
       "21                   1264       84378786249                        SIM  \n",
       "22                    915       30846005204                        SIM  \n",
       "23                   8528       30870968220                        SIM  \n",
       "24                   1156       37279580220                        SIM  \n",
       "25                     94       46529004220                        SIM  \n",
       "26                    186       46623558268                        SIM  \n",
       "27                   7121       64564177249                        SIM  \n",
       "28                   1509       60746440120                        SIM  \n",
       "29                   3993       39079708291                        SIM  \n",
       "...                   ...               ...                        ...  \n",
       "497512                  9       37593226549                        SIM  \n",
       "497513                 36       45715068134                        SIM  \n",
       "497514                 12        7162057268                        SIM  \n",
       "497515                  3       99584247115                        SIM  \n",
       "497516                  8       33172382915                        SIM  \n",
       "497517                 54         335470173                        SIM  \n",
       "497518                 17         828867178                        SIM  \n",
       "497519                  2        2717358137                        SIM  \n",
       "497520                  3        1293190152                        SIM  \n",
       "497521                 45        3017898199                        SIM  \n",
       "497522                 17        3494171130                        SIM  \n",
       "497523                  5       83809759104                        SIM  \n",
       "497524                 12        1762639181                        SIM  \n",
       "497525                  4        4677903107                        SIM  \n",
       "497526                 35       49356470359                        SIM  \n",
       "497527                  7        3592884131                        SIM  \n",
       "497528                  0        3979958140                        SIM  \n",
       "497529                  3         661096122                        SIM  \n",
       "497530                450         246386177                        SIM  \n",
       "497531                  4        1593996101                        SIM  \n",
       "497532                 17        1353129144                        SIM  \n",
       "497533                  0        5762174174                        SIM  \n",
       "497534                  5        3141305196                        SIM  \n",
       "497535                  6        5832419138                        SIM  \n",
       "497536                 16        1326386140                        SIM  \n",
       "497537                 46        9119782160                        SIM  \n",
       "497538                 23       71861122187                        SIM  \n",
       "497539                 14        1115017179                        SIM  \n",
       "497540                116       64072304115                        SIM  \n",
       "497541                 14        3384032136                        SIM  \n",
       "\n",
       "[497542 rows x 10 columns]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tse_votos_grupo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_cpf(x):\n",
    "    y = '0'*(11-len(str(x)))+str(x)\n",
    "    return y\n",
    "\n",
    "df_tse_votos_grupo['NR_CPF_CANDIDATO_ajustado'] = df_tse_votos_grupo['NR_CPF_CANDIDATO'].map(corrige_cpf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tse_votos_grupo['cpf_miolo'] = df_tse_votos_grupo['NR_CPF_CANDIDATO_ajustado'].apply(lambda x: x[3:-2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_nome(x):\n",
    "    y = unidecode(x)\n",
    "    return y\n",
    "\n",
    "df_tse_votos_grupo['NM_CANDIDATO'] = df_tse_votos_grupo['NM_CANDIDATO'].map(corrige_nome)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Cruzamento"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.1. Tem registro de afastamento mas não está na base de candidatos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_alvos_votos_t1 = df_alvos_resumo.merge(df_tse_votos_grupo[['cpf_miolo', 'NM_CANDIDATO', 'DS_SITUACAO_CANDIDATURA', 'QT_VOTOS_NOMINAIS']], how = 'left', left_on = ['cpf_miolo', 'Nome'], right_on = ['cpf_miolo', 'NM_CANDIDATO'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df_resultado_t1 = df_alvos_votos_t1[pd.isna(df_alvos_votos_t1['NM_CANDIDATO']) == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>Nome</th>\n",
       "      <th>CPF</th>\n",
       "      <th>cpf_miolo</th>\n",
       "      <th>UF da UPAG de vinculação</th>\n",
       "      <th>num_meses_registro_afastamento</th>\n",
       "      <th>Valor rendimento liquido</th>\n",
       "      <th>NM_CANDIDATO</th>\n",
       "      <th>DS_SITUACAO_CANDIDATURA</th>\n",
       "      <th>QT_VOTOS_NOMINAIS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ADALBERTO PEGORARO</td>\n",
       "      <td>***573300**</td>\n",
       "      <td>573300</td>\n",
       "      <td>RS</td>\n",
       "      <td>3</td>\n",
       "      <td>23376.053333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>AELCIO VANDER DOS SANTOS</td>\n",
       "      <td>***040156**</td>\n",
       "      <td>040156</td>\n",
       "      <td>MG</td>\n",
       "      <td>3</td>\n",
       "      <td>18521.396667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AL GIBRAN DALLA ROSA MANSUR FERREIRA</td>\n",
       "      <td>***201449**</td>\n",
       "      <td>201449</td>\n",
       "      <td>RS</td>\n",
       "      <td>2</td>\n",
       "      <td>4300.510000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>ALEXANDRE DA SILVA SOARES</td>\n",
       "      <td>***555026**</td>\n",
       "      <td>555026</td>\n",
       "      <td>MG</td>\n",
       "      <td>3</td>\n",
       "      <td>6999.360000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>ALUIZIO JOSE GONCALVES DE SOUSA</td>\n",
       "      <td>***878963**</td>\n",
       "      <td>878963</td>\n",
       "      <td>MA</td>\n",
       "      <td>1</td>\n",
       "      <td>14241.880000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Nome          CPF cpf_miolo  \\\n",
       "6                     ADALBERTO PEGORARO  ***573300**    573300   \n",
       "17              AELCIO VANDER DOS SANTOS  ***040156**    040156   \n",
       "26  AL GIBRAN DALLA ROSA MANSUR FERREIRA  ***201449**    201449   \n",
       "37             ALEXANDRE DA SILVA SOARES  ***555026**    555026   \n",
       "46       ALUIZIO JOSE GONCALVES DE SOUSA  ***878963**    878963   \n",
       "\n",
       "   UF da UPAG de vinculação  num_meses_registro_afastamento  \\\n",
       "6                        RS                               3   \n",
       "17                       MG                               3   \n",
       "26                       RS                               2   \n",
       "37                       MG                               3   \n",
       "46                       MA                               1   \n",
       "\n",
       "    Valor rendimento liquido NM_CANDIDATO DS_SITUACAO_CANDIDATURA  \\\n",
       "6               23376.053333          NaN                     NaN   \n",
       "17              18521.396667          NaN                     NaN   \n",
       "26               4300.510000          NaN                     NaN   \n",
       "37               6999.360000          NaN                     NaN   \n",
       "46              14241.880000          NaN                     NaN   \n",
       "\n",
       "    QT_VOTOS_NOMINAIS  \n",
       "6                 NaN  \n",
       "17                NaN  \n",
       "26                NaN  \n",
       "37                NaN  \n",
       "46                NaN  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resultado_t1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "153"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_resultado_t1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2. Tem registro mas tem pouco voto (menos que 50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_alvos_votos_t2 = df_alvos_resumo.merge(df_tse_votos_grupo[['cpf_miolo', 'NM_CANDIDATO', 'DS_SITUACAO_CANDIDATURA', 'ST_CANDIDATO_INSERIDO_URNA', 'QT_VOTOS_NOMINAIS']], how = 'inner', left_on = ['cpf_miolo', 'Nome'], right_on = ['cpf_miolo', 'NM_CANDIDATO'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_resultado_t2 = df_alvos_votos_t2[(df_alvos_votos_t2['DS_SITUACAO_CANDIDATURA'] == 'APTO') & (df_alvos_votos_t2['QT_VOTOS_NOMINAIS'] < 50) & (df_alvos_votos_t2['ST_CANDIDATO_INSERIDO_URNA'] == 'SIM')].sort_values('QT_VOTOS_NOMINAIS', ascending = True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "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>Nome</th>\n",
       "      <th>CPF</th>\n",
       "      <th>cpf_miolo</th>\n",
       "      <th>UF da UPAG de vinculação</th>\n",
       "      <th>num_meses_registro_afastamento</th>\n",
       "      <th>Valor rendimento liquido</th>\n",
       "      <th>NM_CANDIDATO</th>\n",
       "      <th>DS_SITUACAO_CANDIDATURA</th>\n",
       "      <th>ST_CANDIDATO_INSERIDO_URNA</th>\n",
       "      <th>QT_VOTOS_NOMINAIS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>GLAUDEVALDO NUNES COSTA</td>\n",
       "      <td>***578464**</td>\n",
       "      <td>578464</td>\n",
       "      <td>PB</td>\n",
       "      <td>3</td>\n",
       "      <td>26768.396667</td>\n",
       "      <td>GLAUDEVALDO NUNES COSTA</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>723</th>\n",
       "      <td>WALACE SANTOS CAVALCANTE</td>\n",
       "      <td>***608941**</td>\n",
       "      <td>608941</td>\n",
       "      <td>DF</td>\n",
       "      <td>3</td>\n",
       "      <td>6560.730000</td>\n",
       "      <td>WALACE SANTOS CAVALCANTE</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <td>JOAO SALES DE SOUZA FILHO</td>\n",
       "      <td>***674814**</td>\n",
       "      <td>674814</td>\n",
       "      <td>PE</td>\n",
       "      <td>1</td>\n",
       "      <td>12338.840000</td>\n",
       "      <td>JOAO SALES DE SOUZA FILHO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>CARLOS OLIVEIRA SANTOS</td>\n",
       "      <td>***367888**</td>\n",
       "      <td>367888</td>\n",
       "      <td>MT</td>\n",
       "      <td>3</td>\n",
       "      <td>3475.613333</td>\n",
       "      <td>CARLOS OLIVEIRA SANTOS</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>568</th>\n",
       "      <td>NECILDA MAIA SOUSA</td>\n",
       "      <td>***314832**</td>\n",
       "      <td>314832</td>\n",
       "      <td>RR</td>\n",
       "      <td>3</td>\n",
       "      <td>13240.266667</td>\n",
       "      <td>NECILDA MAIA SOUSA</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658</th>\n",
       "      <td>ROSANETE ALMEIDA MELO</td>\n",
       "      <td>***817212**</td>\n",
       "      <td>817212</td>\n",
       "      <td>RR</td>\n",
       "      <td>3</td>\n",
       "      <td>7714.520000</td>\n",
       "      <td>ROSANETE ALMEIDA MELO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>675</th>\n",
       "      <td>SEVERINO DIONISIO FILHO</td>\n",
       "      <td>***405364**</td>\n",
       "      <td>405364</td>\n",
       "      <td>RN</td>\n",
       "      <td>3</td>\n",
       "      <td>21053.590000</td>\n",
       "      <td>SEVERINO DIONISIO FILHO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>569</th>\n",
       "      <td>NEISOMAR OLIMPIO LIMA</td>\n",
       "      <td>***629602**</td>\n",
       "      <td>629602</td>\n",
       "      <td>AM</td>\n",
       "      <td>2</td>\n",
       "      <td>10010.300000</td>\n",
       "      <td>NEISOMAR OLIMPIO LIMA</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AIRTON SILVA SERRA</td>\n",
       "      <td>***145741**</td>\n",
       "      <td>145741</td>\n",
       "      <td>TO</td>\n",
       "      <td>3</td>\n",
       "      <td>8868.193333</td>\n",
       "      <td>AIRTON SILVA SERRA</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>FRANCISCO CARLOS BADARO</td>\n",
       "      <td>***289368**</td>\n",
       "      <td>289368</td>\n",
       "      <td>MG</td>\n",
       "      <td>4</td>\n",
       "      <td>8428.865000</td>\n",
       "      <td>FRANCISCO CARLOS BADARO</td>\n",
       "      <td>APTO</td>\n",
       "      <td>SIM</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Nome          CPF cpf_miolo  \\\n",
       "292    GLAUDEVALDO NUNES COSTA  ***578464**    578464   \n",
       "723   WALACE SANTOS CAVALCANTE  ***608941**    608941   \n",
       "368  JOAO SALES DE SOUZA FILHO  ***674814**    674814   \n",
       "114     CARLOS OLIVEIRA SANTOS  ***367888**    367888   \n",
       "568         NECILDA MAIA SOUSA  ***314832**    314832   \n",
       "658      ROSANETE ALMEIDA MELO  ***817212**    817212   \n",
       "675    SEVERINO DIONISIO FILHO  ***405364**    405364   \n",
       "569      NEISOMAR OLIMPIO LIMA  ***629602**    629602   \n",
       "23          AIRTON SILVA SERRA  ***145741**    145741   \n",
       "248    FRANCISCO CARLOS BADARO  ***289368**    289368   \n",
       "\n",
       "    UF da UPAG de vinculação  num_meses_registro_afastamento  \\\n",
       "292                       PB                               3   \n",
       "723                       DF                               3   \n",
       "368                       PE                               1   \n",
       "114                       MT                               3   \n",
       "568                       RR                               3   \n",
       "658                       RR                               3   \n",
       "675                       RN                               3   \n",
       "569                       AM                               2   \n",
       "23                        TO                               3   \n",
       "248                       MG                               4   \n",
       "\n",
       "     Valor rendimento liquido               NM_CANDIDATO  \\\n",
       "292              26768.396667    GLAUDEVALDO NUNES COSTA   \n",
       "723               6560.730000   WALACE SANTOS CAVALCANTE   \n",
       "368              12338.840000  JOAO SALES DE SOUZA FILHO   \n",
       "114               3475.613333     CARLOS OLIVEIRA SANTOS   \n",
       "568              13240.266667         NECILDA MAIA SOUSA   \n",
       "658               7714.520000      ROSANETE ALMEIDA MELO   \n",
       "675              21053.590000    SEVERINO DIONISIO FILHO   \n",
       "569              10010.300000      NEISOMAR OLIMPIO LIMA   \n",
       "23                8868.193333         AIRTON SILVA SERRA   \n",
       "248               8428.865000    FRANCISCO CARLOS BADARO   \n",
       "\n",
       "    DS_SITUACAO_CANDIDATURA ST_CANDIDATO_INSERIDO_URNA  QT_VOTOS_NOMINAIS  \n",
       "292                    APTO                        SIM                  1  \n",
       "723                    APTO                        SIM                  2  \n",
       "368                    APTO                        SIM                  3  \n",
       "114                    APTO                        SIM                  3  \n",
       "568                    APTO                        SIM                  4  \n",
       "658                    APTO                        SIM                  4  \n",
       "675                    APTO                        SIM                  5  \n",
       "569                    APTO                        SIM                  6  \n",
       "23                     APTO                        SIM                  6  \n",
       "248                    APTO                        SIM                  6  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resultado_t2.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_resultado_t2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_resultado_t2.to_excel('resultado_servidores_afastamento_votos.xlsx', encoding = 'latin1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.3. Custo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/leonardosales/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:2: 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/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    },
    {
     "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>Valor rendimento liquido</th>\n",
       "      <th>num_meses_registro_afastamento</th>\n",
       "      <th>valor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>26768.396667</td>\n",
       "      <td>3</td>\n",
       "      <td>80305.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>723</th>\n",
       "      <td>6560.730000</td>\n",
       "      <td>3</td>\n",
       "      <td>19682.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <td>12338.840000</td>\n",
       "      <td>1</td>\n",
       "      <td>12338.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>3475.613333</td>\n",
       "      <td>3</td>\n",
       "      <td>10426.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>568</th>\n",
       "      <td>13240.266667</td>\n",
       "      <td>3</td>\n",
       "      <td>39720.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658</th>\n",
       "      <td>7714.520000</td>\n",
       "      <td>3</td>\n",
       "      <td>23143.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>675</th>\n",
       "      <td>21053.590000</td>\n",
       "      <td>3</td>\n",
       "      <td>63160.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>569</th>\n",
       "      <td>10010.300000</td>\n",
       "      <td>2</td>\n",
       "      <td>20020.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>8868.193333</td>\n",
       "      <td>3</td>\n",
       "      <td>26604.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>8428.865000</td>\n",
       "      <td>4</td>\n",
       "      <td>33715.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>6681.890000</td>\n",
       "      <td>3</td>\n",
       "      <td>20045.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>8938.036667</td>\n",
       "      <td>3</td>\n",
       "      <td>26814.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>5164.290000</td>\n",
       "      <td>3</td>\n",
       "      <td>15492.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>12631.420000</td>\n",
       "      <td>3</td>\n",
       "      <td>37894.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>4147.920000</td>\n",
       "      <td>3</td>\n",
       "      <td>12443.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>10560.206667</td>\n",
       "      <td>3</td>\n",
       "      <td>31680.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>30727.296667</td>\n",
       "      <td>3</td>\n",
       "      <td>92181.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>722</th>\n",
       "      <td>10375.830000</td>\n",
       "      <td>3</td>\n",
       "      <td>31127.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>9106.170000</td>\n",
       "      <td>1</td>\n",
       "      <td>9106.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>411</th>\n",
       "      <td>9964.350000</td>\n",
       "      <td>3</td>\n",
       "      <td>29893.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>13445.515000</td>\n",
       "      <td>2</td>\n",
       "      <td>26891.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>25694.310000</td>\n",
       "      <td>2</td>\n",
       "      <td>51388.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426</th>\n",
       "      <td>14199.043333</td>\n",
       "      <td>3</td>\n",
       "      <td>42597.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>23024.407500</td>\n",
       "      <td>4</td>\n",
       "      <td>92097.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>18318.820000</td>\n",
       "      <td>3</td>\n",
       "      <td>54956.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>10536.690000</td>\n",
       "      <td>4</td>\n",
       "      <td>42146.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>729</th>\n",
       "      <td>23711.526667</td>\n",
       "      <td>3</td>\n",
       "      <td>71134.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>649</th>\n",
       "      <td>4281.375000</td>\n",
       "      <td>4</td>\n",
       "      <td>17125.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>625</th>\n",
       "      <td>6270.706667</td>\n",
       "      <td>3</td>\n",
       "      <td>18812.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>18940.470000</td>\n",
       "      <td>3</td>\n",
       "      <td>56821.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>10554.445000</td>\n",
       "      <td>2</td>\n",
       "      <td>21108.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>14301.373333</td>\n",
       "      <td>3</td>\n",
       "      <td>42904.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>8375.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>33500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>9503.310000</td>\n",
       "      <td>1</td>\n",
       "      <td>9503.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>4880.220000</td>\n",
       "      <td>2</td>\n",
       "      <td>9760.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>657</th>\n",
       "      <td>29097.053333</td>\n",
       "      <td>3</td>\n",
       "      <td>87291.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>7738.096667</td>\n",
       "      <td>3</td>\n",
       "      <td>23214.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>7249.570000</td>\n",
       "      <td>3</td>\n",
       "      <td>21748.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>7033.220000</td>\n",
       "      <td>3</td>\n",
       "      <td>21099.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>5004.520000</td>\n",
       "      <td>4</td>\n",
       "      <td>20018.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>17378.373333</td>\n",
       "      <td>3</td>\n",
       "      <td>52135.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td>11332.760000</td>\n",
       "      <td>3</td>\n",
       "      <td>33998.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>8821.690000</td>\n",
       "      <td>3</td>\n",
       "      <td>26465.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688</th>\n",
       "      <td>13490.695000</td>\n",
       "      <td>4</td>\n",
       "      <td>53962.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352</th>\n",
       "      <td>5036.680000</td>\n",
       "      <td>3</td>\n",
       "      <td>15110.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580</th>\n",
       "      <td>4908.043333</td>\n",
       "      <td>3</td>\n",
       "      <td>14724.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>7792.546667</td>\n",
       "      <td>3</td>\n",
       "      <td>23377.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>21327.330000</td>\n",
       "      <td>3</td>\n",
       "      <td>63981.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>15903.133333</td>\n",
       "      <td>3</td>\n",
       "      <td>47709.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>16314.480000</td>\n",
       "      <td>3</td>\n",
       "      <td>48943.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>24886.962500</td>\n",
       "      <td>4</td>\n",
       "      <td>99547.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>593</th>\n",
       "      <td>7996.926667</td>\n",
       "      <td>3</td>\n",
       "      <td>23990.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>601</th>\n",
       "      <td>12734.292500</td>\n",
       "      <td>4</td>\n",
       "      <td>50937.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600</th>\n",
       "      <td>28047.586667</td>\n",
       "      <td>3</td>\n",
       "      <td>84142.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>5635.136667</td>\n",
       "      <td>3</td>\n",
       "      <td>16905.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>26341.383333</td>\n",
       "      <td>3</td>\n",
       "      <td>79024.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>5306.733333</td>\n",
       "      <td>3</td>\n",
       "      <td>15920.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434</th>\n",
       "      <td>5662.406667</td>\n",
       "      <td>3</td>\n",
       "      <td>16987.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704</th>\n",
       "      <td>9444.640000</td>\n",
       "      <td>1</td>\n",
       "      <td>9444.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>4678.943333</td>\n",
       "      <td>3</td>\n",
       "      <td>14036.83</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Valor rendimento liquido  num_meses_registro_afastamento     valor\n",
       "292              26768.396667                               3  80305.19\n",
       "723               6560.730000                               3  19682.19\n",
       "368              12338.840000                               1  12338.84\n",
       "114               3475.613333                               3  10426.84\n",
       "568              13240.266667                               3  39720.80\n",
       "658               7714.520000                               3  23143.56\n",
       "675              21053.590000                               3  63160.77\n",
       "569              10010.300000                               2  20020.60\n",
       "23                8868.193333                               3  26604.58\n",
       "248               8428.865000                               4  33715.46\n",
       "687               6681.890000                               3  20045.67\n",
       "92                8938.036667                               3  26814.11\n",
       "313               5164.290000                               3  15492.87\n",
       "155              12631.420000                               3  37894.26\n",
       "472               4147.920000                               3  12443.76\n",
       "394              10560.206667                               3  31680.62\n",
       "363              30727.296667                               3  92181.89\n",
       "722              10375.830000                               3  31127.49\n",
       "98                9106.170000                               1   9106.17\n",
       "411               9964.350000                               3  29893.05\n",
       "674              13445.515000                               2  26891.03\n",
       "182              25694.310000                               2  51388.62\n",
       "426              14199.043333                               3  42597.13\n",
       "188              23024.407500                               4  92097.63\n",
       "24               18318.820000                               3  54956.46\n",
       "134              10536.690000                               4  42146.76\n",
       "729              23711.526667                               3  71134.58\n",
       "649               4281.375000                               4  17125.50\n",
       "625               6270.706667                               3  18812.12\n",
       "212              18940.470000                               3  56821.41\n",
       "..                        ...                             ...       ...\n",
       "390              10554.445000                               2  21108.89\n",
       "192              14301.373333                               3  42904.12\n",
       "448               8375.000000                               4  33500.00\n",
       "437               9503.310000                               1   9503.31\n",
       "120               4880.220000                               2   9760.44\n",
       "657              29097.053333                               3  87291.16\n",
       "204               7738.096667                               3  23214.29\n",
       "544               7249.570000                               3  21748.71\n",
       "225               7033.220000                               3  21099.66\n",
       "160               5004.520000                               4  20018.08\n",
       "90               17378.373333                               3  52135.12\n",
       "386              11332.760000                               3  33998.28\n",
       "102               8821.690000                               3  26465.07\n",
       "688              13490.695000                               4  53962.78\n",
       "352               5036.680000                               3  15110.04\n",
       "580               4908.043333                               3  14724.13\n",
       "131               7792.546667                               3  23377.64\n",
       "565              21327.330000                               3  63981.99\n",
       "179              15903.133333                               3  47709.40\n",
       "99               16314.480000                               3  48943.44\n",
       "717              24886.962500                               4  99547.85\n",
       "593               7996.926667                               3  23990.78\n",
       "601              12734.292500                               4  50937.17\n",
       "600              28047.586667                               3  84142.76\n",
       "552               5635.136667                               3  16905.41\n",
       "538              26341.383333                               3  79024.15\n",
       "196               5306.733333                               3  15920.20\n",
       "434               5662.406667                               3  16987.22\n",
       "704               9444.640000                               1   9444.64\n",
       "291               4678.943333                               3  14036.83\n",
       "\n",
       "[74 rows x 3 columns]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resultado_t2_custo = df_resultado_t2[['Valor rendimento liquido', 'num_meses_registro_afastamento']]\n",
    "df_resultado_t2_custo['valor'] = df_resultado_t2_custo['Valor rendimento liquido'] * df_resultado_t2_custo['num_meses_registro_afastamento']\n",
    "df_resultado_t2_custo\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2532985.4800000004"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resultado_t2_custo['valor'].sum()"
   ]
  },
  {
   "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}