documentos_cota_parlamentar_camara_dos_deputados_2020_2021.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38476855-486e-4e00-bee2-657e3b81d2eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8\n",
    "# Reinaldo Chaves (@paidatocandeira)\n",
    "# Estuda os documentos de Cota para Exercício da Atividade Parlamentar da Câmara dos Deputados\n",
    "# https://www2.camara.leg.br/transparencia/cota-para-exercicio-da-atividade-parlamentar/dados-abertos-cota-parlamentar\n",
    "# Arquivos XML e baixa os documentos .pdf\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0affc841-fafa-4015-8be4-7fb89b34db50",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "31676b04-ccb4-46bd-b27f-6420f755ab87",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import xmltodict\n",
    "import pandas as pd\n",
    "import os\n",
    "import wget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "388e4d71-a6e0-40d0-a0e5-bad15e431687",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "34de9ce4-bbcf-4daf-bd25-d643731cdb9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.float_format = '{:,.2f}'.format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5847797f-a603-4b6d-b13c-0ed0f2b2240c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "482be014-88ca-4a21-80db-afbcaa4cebd1",
   "metadata": {},
   "source": [
    "# Dados de 2021 - até 15 de novembro"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "812be804-f230-4c97-81e0-2d0aa21849cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Abre arquivo\n",
    "with open(\"cota_parlamentar\\AnoAtual.xml\", 'r', encoding='utf-8') as f:\n",
    "    xmlString = f.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bc4fbdf4-5278-4fbf-baec-a9c66da1edcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Usa o xmltodict para tornar em formato de dicionário\n",
    "jsonString = json.dumps(xmltodict.parse(xmlString), indent=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0f08e7c6-8af4-4f78-bcb7-88fac07778f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Salva como JSON\n",
    "with open(\"output.json\", 'w') as f:\n",
    "    f.write(jsonString)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "07769edf-c444-4142-9e11-80e5346f1ce8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Abre arquivo JSON\n",
    "arquivo_json = open('output.json', 'r')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "aa76e2f4-c61b-499f-8d90-f8ae8e15f536",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Carrega dados do JSON\n",
    "dados_json = json.load(arquivo_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "053d9e6c-bcf3-4e24-b19a-e08cb3f0bd93",
   "metadata": {},
   "outputs": [],
   "source": [
    "#dados_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e346140e-3beb-47bc-b436-b684d4e6e71e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\n\\n{'xml': {'dados': {'despesa': [{'nomeParlamentar': 'LIDERANÃ\\x87A DO CIDADANIA',\\n     'cpf': None,\\n     'idDeputado': None,\\n     'numeroCarteiraParlamentar': None,\\n     'legislatura': '2019',\\n     'siglaUF': 'NA',\\n     'siglaPartido': None,\\n     'codigoLegislatura': '56',\\n     'numeroSubCota': '13',\\n     'descricao': 'FORNECIMENTO DE ALIMENTAÃ\\x87Ã\\x83O DO PARLAMENTAR',\\n     'numeroEspecificacaoSubCota': '0',\\n     'descricaoEspecificacao': None,\\n     'fornecedor': 'ARTESANAL SERVICOS DE ALIMENTACAO E BUFFET EIRELI',\\n     'cnpjCPF': '228.362.100/0015-7',\\n     'numero': '104',\\n     'tipoDocumento': '4',\\n     'dataEmissao': '2021-03-04T00:00:00',\\n     'valorDocumento': '999.96',\\n     'valorGlosa': '0',\\n     'valorLiquido': '999.96',\\n     'mes': '3',\\n     'ano': '2021',\\n     'parcela': '0',\\n     'passageiro': None,\\n     'trecho': None,\\n     'lote': '1751471',\\n     'ressarcimento': None,\\n     'restituicao': None,\\n     'numeroDeputadoID': '3453',\\n     'idDocumento': '7165735',\\n     'urlDocumento': 'http://camara.leg.br/cota-parlamentar/nota-fiscal-eletronica?ideDocumentoFiscal=7165735'},\\n\\n\""
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Exemplo dos dados\n",
    "'''\n",
    "\n",
    "{'xml': {'dados': {'despesa': [{'nomeParlamentar': 'LIDERANÃ\\x87A DO CIDADANIA',\n",
    "     'cpf': None,\n",
    "     'idDeputado': None,\n",
    "     'numeroCarteiraParlamentar': None,\n",
    "     'legislatura': '2019',\n",
    "     'siglaUF': 'NA',\n",
    "     'siglaPartido': None,\n",
    "     'codigoLegislatura': '56',\n",
    "     'numeroSubCota': '13',\n",
    "     'descricao': 'FORNECIMENTO DE ALIMENTAÃ\\x87Ã\\x83O DO PARLAMENTAR',\n",
    "     'numeroEspecificacaoSubCota': '0',\n",
    "     'descricaoEspecificacao': None,\n",
    "     'fornecedor': 'ARTESANAL SERVICOS DE ALIMENTACAO E BUFFET EIRELI',\n",
    "     'cnpjCPF': '228.362.100/0015-7',\n",
    "     'numero': '104',\n",
    "     'tipoDocumento': '4',\n",
    "     'dataEmissao': '2021-03-04T00:00:00',\n",
    "     'valorDocumento': '999.96',\n",
    "     'valorGlosa': '0',\n",
    "     'valorLiquido': '999.96',\n",
    "     'mes': '3',\n",
    "     'ano': '2021',\n",
    "     'parcela': '0',\n",
    "     'passageiro': None,\n",
    "     'trecho': None,\n",
    "     'lote': '1751471',\n",
    "     'ressarcimento': None,\n",
    "     'restituicao': None,\n",
    "     'numeroDeputadoID': '3453',\n",
    "     'idDocumento': '7165735',\n",
    "     'urlDocumento': 'http://camara.leg.br/cota-parlamentar/nota-fiscal-eletronica?ideDocumentoFiscal=7165735'},\n",
    "\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e41c58a7-7fc7-4243-a1d5-ccb3ad972645",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b4ee5f6-ff70-4884-b0f7-aad93cb06dd5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tranforma o JSON em lista, a partir da chave específica onde ficam as despesas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a07ad6e0-a097-4118-b290-be62005d35e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "lista = dados_json['xml']['dados']['despesa']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "04744464-e999-466b-8ce0-bcaae06799c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_despesas_2021 = pd.DataFrame(lista, columns = ['nomeParlamentar', 'cpf', 'idDeputado', 'numeroCarteiraParlamentar', 'legislatura', 'siglaUF', 'siglaPartido',      'codigoLegislatura', 'numeroSubCota', 'descricao', 'numeroEspecificacaoSubCota', 'descricaoEspecificacao',      'fornecedor', 'cnpjCPF', 'numero', 'tipoDocumento', 'dataEmissao', 'valorDocumento', 'valorGlosa', 'valorLiquido', 'mes',  'ano', 'parcela', 'passageiro', 'trecho', 'lote', 'ressarcimento', 'restituicao', 'numeroDeputadoID', 'idDocumento',      'urlDocumento'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ba089ecf-a5f4-4faa-8927-1c6c7b48f192",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 150237 entries, 0 to 150236\n",
      "Data columns (total 31 columns):\n",
      " #   Column                      Non-Null Count   Dtype \n",
      "---  ------                      --------------   ----- \n",
      " 0   nomeParlamentar             150237 non-null  object\n",
      " 1   cpf                         149587 non-null  object\n",
      " 2   idDeputado                  149587 non-null  object\n",
      " 3   numeroCarteiraParlamentar   149587 non-null  object\n",
      " 4   legislatura                 150237 non-null  object\n",
      " 5   siglaUF                     150237 non-null  object\n",
      " 6   siglaPartido                149587 non-null  object\n",
      " 7   codigoLegislatura           150237 non-null  object\n",
      " 8   numeroSubCota               150237 non-null  object\n",
      " 9   descricao                   150237 non-null  object\n",
      " 10  numeroEspecificacaoSubCota  150237 non-null  object\n",
      " 11  descricaoEspecificacao      43644 non-null   object\n",
      " 12  fornecedor                  150237 non-null  object\n",
      " 13  cnpjCPF                     135009 non-null  object\n",
      " 14  numero                      150237 non-null  object\n",
      " 15  tipoDocumento               150237 non-null  object\n",
      " 16  dataEmissao                 145134 non-null  object\n",
      " 17  valorDocumento              150237 non-null  object\n",
      " 18  valorGlosa                  150237 non-null  object\n",
      " 19  valorLiquido                150237 non-null  object\n",
      " 20  mes                         150237 non-null  object\n",
      " 21  ano                         150237 non-null  object\n",
      " 22  parcela                     150237 non-null  object\n",
      " 23  passageiro                  24426 non-null   object\n",
      " 24  trecho                      24262 non-null   object\n",
      " 25  lote                        150237 non-null  object\n",
      " 26  ressarcimento               38116 non-null   object\n",
      " 27  restituicao                 26 non-null      object\n",
      " 28  numeroDeputadoID            150237 non-null  object\n",
      " 29  idDocumento                 150237 non-null  object\n",
      " 30  urlDocumento                104061 non-null  object\n",
      "dtypes: object(31)\n",
      "memory usage: 35.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df_despesas_2021.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c57ae08b-24fd-4800-8b42-4d751b19cc82",
   "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>nomeParlamentar</th>\n",
       "      <th>cpf</th>\n",
       "      <th>idDeputado</th>\n",
       "      <th>numeroCarteiraParlamentar</th>\n",
       "      <th>legislatura</th>\n",
       "      <th>siglaUF</th>\n",
       "      <th>siglaPartido</th>\n",
       "      <th>codigoLegislatura</th>\n",
       "      <th>numeroSubCota</th>\n",
       "      <th>descricao</th>\n",
       "      <th>...</th>\n",
       "      <th>ano</th>\n",
       "      <th>parcela</th>\n",
       "      <th>passageiro</th>\n",
       "      <th>trecho</th>\n",
       "      <th>lote</th>\n",
       "      <th>ressarcimento</th>\n",
       "      <th>restituicao</th>\n",
       "      <th>numeroDeputadoID</th>\n",
       "      <th>idDocumento</th>\n",
       "      <th>urlDocumento</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LIDERANÇA DO CIDADANIA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR</td>\n",
       "      <td>...</td>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1751471</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3453</td>\n",
       "      <td>7165735</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LIDERANÇA DO CIDADANIA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR</td>\n",
       "      <td>...</td>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1752542</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3453</td>\n",
       "      <td>7167891</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LIDERANÇA DO CIDADANIA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR</td>\n",
       "      <td>...</td>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1753004</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3453</td>\n",
       "      <td>7168798</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LIDERANÇA DO CIDADANIA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR</td>\n",
       "      <td>...</td>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1758032</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3453</td>\n",
       "      <td>7178054</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LIDERANÇA DO CIDADANIA</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>13</td>\n",
       "      <td>FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR</td>\n",
       "      <td>...</td>\n",
       "      <td>2021</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1760213</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3453</td>\n",
       "      <td>7183245</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          nomeParlamentar   cpf idDeputado numeroCarteiraParlamentar  \\\n",
       "0  LIDERANÇA DO CIDADANIA  None       None                      None   \n",
       "1  LIDERANÇA DO CIDADANIA  None       None                      None   \n",
       "2  LIDERANÇA DO CIDADANIA  None       None                      None   \n",
       "3  LIDERANÇA DO CIDADANIA  None       None                      None   \n",
       "4  LIDERANÇA DO CIDADANIA  None       None                      None   \n",
       "\n",
       "  legislatura siglaUF siglaPartido codigoLegislatura numeroSubCota  \\\n",
       "0        2019      NA         None                56            13   \n",
       "1        2019      NA         None                56            13   \n",
       "2        2019      NA         None                56            13   \n",
       "3        2019      NA         None                56            13   \n",
       "4        2019      NA         None                56            13   \n",
       "\n",
       "                                    descricao  ...   ano parcela passageiro  \\\n",
       "0  FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR  ...  2021       0       None   \n",
       "1  FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR  ...  2021       0       None   \n",
       "2  FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR  ...  2021       0       None   \n",
       "3  FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR  ...  2021       0       None   \n",
       "4  FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR  ...  2021       0       None   \n",
       "\n",
       "  trecho     lote ressarcimento restituicao numeroDeputadoID idDocumento  \\\n",
       "0   None  1751471          None        None             3453     7165735   \n",
       "1   None  1752542          None        None             3453     7167891   \n",
       "2   None  1753004          None        None             3453     7168798   \n",
       "3   None  1758032          None        None             3453     7178054   \n",
       "4   None  1760213          None        None             3453     7183245   \n",
       "\n",
       "                                        urlDocumento  \n",
       "0  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "1  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "2  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "3  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "4  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_despesas_2021.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e35d1fc1-90f5-473f-b5d2-ea211fba8023",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "fd7261ef-b285-43a7-afec-1ea3cc1ae872",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tipos de despesas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7ed26956-05f5-4f3f-87a6-e7450fd6b23c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['13', '4', '5', '12', '1', '3', '120', '9', '10', '14', '122',\n",
       "       '11', '119', '999', '998', '8', '123', '121', '137'], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_despesas_2021['numeroSubCota'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b1966545-c393-4e7a-9699-69ec80cfabf8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR',\n",
       "       'CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.',\n",
       "       'DIVULGAÇÃO DA ATIVIDADE PARLAMENTAR.',\n",
       "       'ASSINATURA DE PUBLICAÇÕES',\n",
       "       'MANUTENÇÃO DE ESCRITÓRIO DE APOIO À ATIVIDADE PARLAMENTAR',\n",
       "       'COMBUSTÍVEIS E LUBRIFICANTES.',\n",
       "       'LOCAÇÃO OU FRETAMENTO DE VEÍCULOS AUTOMOTORES',\n",
       "       'PASSAGEM AÉREA - REEMBOLSO', 'TELEFONIA',\n",
       "       'HOSPEDAGEM ,EXCETO DO PARLAMENTAR NO DISTRITO FEDERAL.',\n",
       "       'SERVIÇO DE TÁXI, PEDÁGIO E ESTACIONAMENTO', 'SERVIÇOS POSTAIS',\n",
       "       'LOCAÇÃO OU FRETAMENTO DE AERONAVES', 'PASSAGEM AÉREA - RPA',\n",
       "       'PASSAGEM AÉREA - SIGEPA',\n",
       "       'SERVIÇO DE SEGURANÇA PRESTADO POR EMPRESA ESPECIALIZADA.',\n",
       "       'PASSAGENS TERRESTRES, MARÍTIMAS OU FLUVIAIS',\n",
       "       'LOCAÇÃO OU FRETAMENTO DE EMBARCAÇÕES',\n",
       "       'PARTICIPAÇÃO EM CURSO, PALESTRA OU EVENTO SIMILAR'], dtype=object)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_despesas_2021['descricao'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe717f08-3d1e-40f0-b562-e8fc39e72777",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acc6fc46-7fee-4a54-89b2-23cf160bebc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tamanho"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c64c7b04-d601-4036-8e28-c2d77030b2c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(150237, 31)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_despesas_2021.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8ff7b236-eb20-47ed-897a-c3e18c33c62a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Separa apenas documentos com PDFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "94fd654a-e3f7-46e8-b1a7-589d0edb9149",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(71219, 31)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "apenas_pdf_2021 = df_despesas_2021[pd.notnull(df_despesas_2021['urlDocumento'])]\n",
    "apenas_pdf_2021 = apenas_pdf_2021[apenas_pdf_2021['urlDocumento'].str.contains(\".pdf\")]\n",
    "apenas_pdf_2021.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02f91555-6164-4b99-ab8d-13c8867add88",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "46dc91ff-1ab3-4512-b82c-9fe8c262a3c4",
   "metadata": {},
   "source": [
    "### Faz algumas análises nos valores líquidos dos documentos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "af5c57b9-e5cb-4b40-a057-564b1bdbab12",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_despesas_2021['valorLiquido'] = df_despesas_2021['valorLiquido'].str.replace(',','.')\n",
    "df_despesas_2021['valorLiquido'] = df_despesas_2021['valorLiquido'].astype('float64') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca89d474-a730-4c2d-a0cb-21522ee87546",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f701d8f8-1dfb-40c9-99c3-99dfad84303f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "nomeParlamentar               0\n",
       "cpf                           0\n",
       "idDeputado                    0\n",
       "numeroCarteiraParlamentar     0\n",
       "legislatura                   0\n",
       "siglaUF                       0\n",
       "siglaPartido                  0\n",
       "codigoLegislatura             0\n",
       "numeroSubCota                 0\n",
       "descricao                     0\n",
       "numeroEspecificacaoSubCota    0\n",
       "descricaoEspecificacao        0\n",
       "fornecedor                    0\n",
       "cnpjCPF                       0\n",
       "numero                        0\n",
       "tipoDocumento                 0\n",
       "dataEmissao                   0\n",
       "valorDocumento                0\n",
       "valorGlosa                    0\n",
       "valorLiquido                  0\n",
       "mes                           0\n",
       "ano                           0\n",
       "parcela                       0\n",
       "passageiro                    0\n",
       "trecho                        0\n",
       "lote                          0\n",
       "ressarcimento                 0\n",
       "restituicao                   0\n",
       "numeroDeputadoID              0\n",
       "idDocumento                   0\n",
       "urlDocumento                  0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Mostra se há linhas vazias\n",
    "df_despesas_2021.apply(lambda x: sum(df_despesas_2021['valorLiquido'].isnull()),axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2dddc49b-06d5-468c-b5bf-e2e27f4142a7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "6d886cbf-8bab-4137-8de1-b3c0130e2fff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Separa gastos e média \n",
    "gastos = df_despesas_2021['valorLiquido']\n",
    "media_despesas = lambda gastos: (gastos.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "949239c2-e600-44bf-b0b7-31619f439eb3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "89a90b82-72c7-4c39-839e-4c9ffdc66f3b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "816.0008453310435\n"
     ]
    }
   ],
   "source": [
    "# Média\n",
    "print(media_despesas(gastos))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "a32d1607-e50e-404b-8d6c-483d7ee87ccd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "190.67\n"
     ]
    }
   ],
   "source": [
    "# Mediana\n",
    "print(gastos.median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "685b61d8-aa8f-4920-b893-19bd5e5e8a1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "83750.0\n"
     ]
    }
   ],
   "source": [
    "# Máximo\n",
    "print(df_despesas_2021['valorLiquido'].max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "afdefcbf-12a0-4681-9586-47b3a27a8cbe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-5784.2\n"
     ]
    }
   ],
   "source": [
    "# Mínimo\n",
    "print(df_despesas_2021['valorLiquido'].min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "7da5e212-288f-4f83-9705-b727078e31ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "89534.2\n"
     ]
    }
   ],
   "source": [
    "# Máximo menos Mínimo\n",
    "print(df_despesas_2021['valorLiquido'].max()-df_despesas_2021['valorLiquido'].min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "32e94cb0-6144-46df-b4b3-9e3da5b8f5bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    150237.000000\n",
      "mean        816.000845\n",
      "std        2452.906867\n",
      "min       -5784.200000\n",
      "25%          42.820000\n",
      "50%         190.670000\n",
      "75%         470.940000\n",
      "max       83750.000000\n",
      "Name: valorLiquido, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Estatísticas gerais\n",
    "print(df_despesas_2021['valorLiquido'].describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "025b63c6-6191-4f18-a8ee-09bed0b1832f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "2a9cb2a8-2ec7-4acb-95d4-a6b885350a0c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "122593518.99999999"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total de despesas\n",
    "df_despesas_2021['valorLiquido'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "9ff6cd37-6e75-41dc-817b-7264cebc9a91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   100.00\n",
       "dtype: float64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Valor que mais se repete\n",
    "df_despesas_2021['valorLiquido'].mode()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d829e541-b4e1-49c6-9301-f79b910a1bec",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "8e4c3c33-45a7-46eb-bef0-2f55c0eb922c",
   "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>nomeParlamentar</th>\n",
       "      <th>valorLiquido</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>353</th>\n",
       "      <td>Mara Rocha</td>\n",
       "      <td>455,451.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>Wilson Santiago</td>\n",
       "      <td>455,426.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>Silas Câmara</td>\n",
       "      <td>454,519.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>Jéssica Sales</td>\n",
       "      <td>445,790.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>Flaviano Melo</td>\n",
       "      <td>443,876.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>João Maia</td>\n",
       "      <td>433,226.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>Moses Rodrigues</td>\n",
       "      <td>426,135.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>Júlio Cesar</td>\n",
       "      <td>424,009.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Domingos Neto</td>\n",
       "      <td>422,926.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>Loester Trutis</td>\n",
       "      <td>419,625.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>557</th>\n",
       "      <td>Wellington Roberto</td>\n",
       "      <td>418,498.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Eli Borges</td>\n",
       "      <td>417,952.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>Eduardo Bismarck</td>\n",
       "      <td>411,835.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <td>Professora Dayane Pimentel</td>\n",
       "      <td>411,610.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>João Daniel</td>\n",
       "      <td>411,403.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Delegado Éder Mauro</td>\n",
       "      <td>411,024.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Aníbal Gomes</td>\n",
       "      <td>409,322.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>510</th>\n",
       "      <td>Sidney Leite</td>\n",
       "      <td>408,524.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>Hildo Rocha</td>\n",
       "      <td>408,348.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290</th>\n",
       "      <td>Juscelino Filho</td>\n",
       "      <td>407,523.66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                nomeParlamentar  valorLiquido\n",
       "353                  Mara Rocha    455,451.20\n",
       "558             Wilson Santiago    455,426.03\n",
       "511                Silas Câmara    454,519.86\n",
       "291               Jéssica Sales    445,790.67\n",
       "185               Flaviano Melo    443,876.32\n",
       "280                   João Maia    433,226.67\n",
       "388             Moses Rodrigues    426,135.96\n",
       "292                 Júlio Cesar    424,009.45\n",
       "134               Domingos Neto    422,926.57\n",
       "327              Loester Trutis    419,625.12\n",
       "557          Wellington Roberto    418,498.50\n",
       "160                  Eli Borges    417,952.92\n",
       "152            Eduardo Bismarck    411,835.62\n",
       "458  Professora Dayane Pimentel    411,610.32\n",
       "279                 João Daniel    411,403.13\n",
       "129         Delegado Éder Mauro    411,024.23\n",
       "41                 Aníbal Gomes    409,322.66\n",
       "510                Sidney Leite    408,524.82\n",
       "238                 Hildo Rocha    408,348.89\n",
       "290             Juscelino Filho    407,523.66"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 20 deputados com maiores gastos em cotas\n",
    "df_despesas_2021.groupby([\"nomeParlamentar\"]).valorLiquido.sum().reset_index().sort_values(\"valorLiquido\", ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96a186f2-432b-4727-b6fe-ac5e9d7b5139",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "24c54093-f135-4593-9012-aa9829208ab5",
   "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>fornecedor</th>\n",
       "      <th>cnpjCPF</th>\n",
       "      <th>valorLiquido</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6110</th>\n",
       "      <td>Cia Aérea - TAM</td>\n",
       "      <td>020.128.620/0016-0</td>\n",
       "      <td>4,706,219.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6109</th>\n",
       "      <td>Cia Aérea - GOL</td>\n",
       "      <td>075.756.510/0015-9</td>\n",
       "      <td>2,875,889.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13179</th>\n",
       "      <td>PANTANAL VEÍCULOS LTDA</td>\n",
       "      <td>073.193.230/0019-1</td>\n",
       "      <td>1,763,085.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6108</th>\n",
       "      <td>Cia Aérea - AZUL</td>\n",
       "      <td>092.962.950/0016-0</td>\n",
       "      <td>906,346.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4645</th>\n",
       "      <td>CELULAR FUNCIONAL</td>\n",
       "      <td>000.000.000/0000-1</td>\n",
       "      <td>830,329.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8365</th>\n",
       "      <td>GOLD CAR BRASILIA LOCADORA DE VEICULOS ME</td>\n",
       "      <td>267.352.270/0017-9</td>\n",
       "      <td>801,304.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19316</th>\n",
       "      <td>Telefônica Brasil S. A. VIVO</td>\n",
       "      <td>025.581.570/0016-2</td>\n",
       "      <td>634,200.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7593</th>\n",
       "      <td>FACEBOOK SERVIÇOS ONLINE DO BRASIL LTDA</td>\n",
       "      <td>133.470.160/0011-7</td>\n",
       "      <td>609,867.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6563</th>\n",
       "      <td>DMD Gestão Administrativa LTDA - ME</td>\n",
       "      <td>026.102.350/0012-0</td>\n",
       "      <td>568,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19577</th>\n",
       "      <td>VERTICE - SOCIEDADE CIVIL DE PROFISSIONAIS ASS...</td>\n",
       "      <td>054.434.490/0014-8</td>\n",
       "      <td>500,330.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9228</th>\n",
       "      <td>HPE AUTOMOTORES DO BRASIL LTDA</td>\n",
       "      <td>543.057.430/0311-4</td>\n",
       "      <td>404,619.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19004</th>\n",
       "      <td>TELEFÔNICA BRASIL S. A. VIVO</td>\n",
       "      <td>025.581.570/0016-2</td>\n",
       "      <td>361,600.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10930</th>\n",
       "      <td>LIBRE COMUNICAÇÃO E MARKETING EIRELI</td>\n",
       "      <td>083.456.310/0015-4</td>\n",
       "      <td>352,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16524</th>\n",
       "      <td>QUASAR LOCADORA DE VEICULOS EIRELI</td>\n",
       "      <td>380.246.750/0014-3</td>\n",
       "      <td>335,293.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12487</th>\n",
       "      <td>Marcelo de Barros Gaspar</td>\n",
       "      <td>115.850.400/0016-9</td>\n",
       "      <td>330,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5762</th>\n",
       "      <td>CONSUPORTE TRANSPORTES E SERVIÇOS LTDA - EPP</td>\n",
       "      <td>002.545.640/0015-0</td>\n",
       "      <td>325,962.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6041</th>\n",
       "      <td>CTA - CLEITON TAXI AEREO LTDA</td>\n",
       "      <td>049.844.000/0013-0</td>\n",
       "      <td>325,300.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9535</th>\n",
       "      <td>INOP - INSTITUTO NACIONAL DE ORCAMENTO PUBLICO...</td>\n",
       "      <td>326.927.080/0014-7</td>\n",
       "      <td>323,900.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11978</th>\n",
       "      <td>MB CONSULTORIA E MARKETING LTDA</td>\n",
       "      <td>378.598.500/0015-0</td>\n",
       "      <td>306,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1029</th>\n",
       "      <td>ANEXO 4 ASSESSORIA, CONSULTORIA, MARKETING E D...</td>\n",
       "      <td>083.229.980/0015-2</td>\n",
       "      <td>305,935.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              fornecedor             cnpjCPF  \\\n",
       "6110                                     Cia Aérea - TAM  020.128.620/0016-0   \n",
       "6109                                     Cia Aérea - GOL  075.756.510/0015-9   \n",
       "13179                             PANTANAL VEÍCULOS LTDA  073.193.230/0019-1   \n",
       "6108                                    Cia Aérea - AZUL  092.962.950/0016-0   \n",
       "4645                                   CELULAR FUNCIONAL  000.000.000/0000-1   \n",
       "8365           GOLD CAR BRASILIA LOCADORA DE VEICULOS ME  267.352.270/0017-9   \n",
       "19316                       Telefônica Brasil S. A. VIVO  025.581.570/0016-2   \n",
       "7593             FACEBOOK SERVIÇOS ONLINE DO BRASIL LTDA  133.470.160/0011-7   \n",
       "6563                 DMD Gestão Administrativa LTDA - ME  026.102.350/0012-0   \n",
       "19577  VERTICE - SOCIEDADE CIVIL DE PROFISSIONAIS ASS...  054.434.490/0014-8   \n",
       "9228                      HPE AUTOMOTORES DO BRASIL LTDA  543.057.430/0311-4   \n",
       "19004                       TELEFÔNICA BRASIL S. A. VIVO  025.581.570/0016-2   \n",
       "10930               LIBRE COMUNICAÇÃO E MARKETING EIRELI  083.456.310/0015-4   \n",
       "16524                 QUASAR LOCADORA DE VEICULOS EIRELI  380.246.750/0014-3   \n",
       "12487                           Marcelo de Barros Gaspar  115.850.400/0016-9   \n",
       "5762        CONSUPORTE TRANSPORTES E SERVIÇOS LTDA - EPP  002.545.640/0015-0   \n",
       "6041                       CTA - CLEITON TAXI AEREO LTDA  049.844.000/0013-0   \n",
       "9535   INOP - INSTITUTO NACIONAL DE ORCAMENTO PUBLICO...  326.927.080/0014-7   \n",
       "11978                    MB CONSULTORIA E MARKETING LTDA  378.598.500/0015-0   \n",
       "1029   ANEXO 4 ASSESSORIA, CONSULTORIA, MARKETING E D...  083.229.980/0015-2   \n",
       "\n",
       "       valorLiquido  \n",
       "6110   4,706,219.02  \n",
       "6109   2,875,889.68  \n",
       "13179  1,763,085.93  \n",
       "6108     906,346.33  \n",
       "4645     830,329.75  \n",
       "8365     801,304.00  \n",
       "19316    634,200.71  \n",
       "7593     609,867.42  \n",
       "6563     568,000.00  \n",
       "19577    500,330.00  \n",
       "9228     404,619.00  \n",
       "19004    361,600.63  \n",
       "10930    352,000.00  \n",
       "16524    335,293.00  \n",
       "12487    330,000.00  \n",
       "5762     325,962.00  \n",
       "6041     325,300.00  \n",
       "9535     323,900.00  \n",
       "11978    306,000.00  \n",
       "1029     305,935.00  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 20 fornecedores com maiores gastos em cotas\n",
    "df_despesas_2021.groupby([\"fornecedor\", \"cnpjCPF\"]).valorLiquido.sum().reset_index().sort_values(\"valorLiquido\", ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6cee3a2-45c8-4262-bd2c-5258a2b7527c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "a324bc60-ec74-4e20-bfbb-d4bf0a9b8665",
   "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>descricao</th>\n",
       "      <th>valorLiquido</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DIVULGAÇÃO DA ATIVIDADE PARLAMENTAR.</td>\n",
       "      <td>46,045,097.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LOCAÇÃO OU FRETAMENTO DE VEÍCULOS AUTOMOTORES</td>\n",
       "      <td>21,409,696.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MANUTENÇÃO DE ESCRITÓRIO DE APOIO À ATIVIDADE ...</td>\n",
       "      <td>19,204,010.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.</td>\n",
       "      <td>16,886,562.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COMBUSTÍVEIS E LUBRIFICANTES.</td>\n",
       "      <td>12,098,640.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PASSAGEM AÉREA - RPA</td>\n",
       "      <td>8,488,455.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>TELEFONIA</td>\n",
       "      <td>3,328,970.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HOSPEDAGEM ,EXCETO DO PARLAMENTAR NO DISTRITO ...</td>\n",
       "      <td>1,276,503.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>PASSAGEM AÉREA - REEMBOLSO</td>\n",
       "      <td>1,274,946.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>SERVIÇOS POSTAIS</td>\n",
       "      <td>1,116,928.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LOCAÇÃO OU FRETAMENTO DE AERONAVES</td>\n",
       "      <td>1,022,820.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>SERVIÇO DE SEGURANÇA PRESTADO POR EMPRESA ESPE...</td>\n",
       "      <td>698,813.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR</td>\n",
       "      <td>553,073.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>SERVIÇO DE TÁXI, PEDÁGIO E ESTACIONAMENTO</td>\n",
       "      <td>549,849.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LOCAÇÃO OU FRETAMENTO DE EMBARCAÇÕES</td>\n",
       "      <td>191,700.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASSINATURA DE PUBLICAÇÕES</td>\n",
       "      <td>131,621.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>PASSAGENS TERRESTRES, MARÍTIMAS OU FLUVIAIS</td>\n",
       "      <td>36,053.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>PARTICIPAÇÃO EM CURSO, PALESTRA OU EVENTO SIMILAR</td>\n",
       "      <td>15,383.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>PASSAGEM AÉREA - SIGEPA</td>\n",
       "      <td>-11,735,608.47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            descricao   valorLiquido\n",
       "3                DIVULGAÇÃO DA ATIVIDADE PARLAMENTAR.  46,045,097.68\n",
       "8       LOCAÇÃO OU FRETAMENTO DE VEÍCULOS AUTOMOTORES  21,409,696.70\n",
       "9   MANUTENÇÃO DE ESCRITÓRIO DE APOIO À ATIVIDADE ...  19,204,010.75\n",
       "2       CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.  16,886,562.74\n",
       "1                       COMBUSTÍVEIS E LUBRIFICANTES.  12,098,640.99\n",
       "12                               PASSAGEM AÉREA - RPA   8,488,455.03\n",
       "18                                          TELEFONIA   3,328,970.73\n",
       "5   HOSPEDAGEM ,EXCETO DO PARLAMENTAR NO DISTRITO ...   1,276,503.20\n",
       "11                         PASSAGEM AÉREA - REEMBOLSO   1,274,946.29\n",
       "17                                   SERVIÇOS POSTAIS   1,116,928.96\n",
       "6                  LOCAÇÃO OU FRETAMENTO DE AERONAVES   1,022,820.00\n",
       "15  SERVIÇO DE SEGURANÇA PRESTADO POR EMPRESA ESPE...     698,813.52\n",
       "4          FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR     553,073.17\n",
       "16          SERVIÇO DE TÁXI, PEDÁGIO E ESTACIONAMENTO     549,849.58\n",
       "7                LOCAÇÃO OU FRETAMENTO DE EMBARCAÇÕES     191,700.00\n",
       "0                           ASSINATURA DE PUBLICAÇÕES     131,621.09\n",
       "14        PASSAGENS TERRESTRES, MARÍTIMAS OU FLUVIAIS      36,053.73\n",
       "10  PARTICIPAÇÃO EM CURSO, PALESTRA OU EVENTO SIMILAR      15,383.31\n",
       "13                            PASSAGEM AÉREA - SIGEPA -11,735,608.47"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 20 tipos de despesas com mais gastos\n",
    "df_despesas_2021.groupby([\"descricao\"]).valorLiquido.sum().reset_index().sort_values(\"valorLiquido\", ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2322752-6658-4820-af89-3db809e7037d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "f2b1e18e-a573-4d51-912e-30dcf1e79e61",
   "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>nomeParlamentar</th>\n",
       "      <th>fornecedor</th>\n",
       "      <th>cnpjCPF</th>\n",
       "      <th>valorLiquido</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4679</th>\n",
       "      <td>Bilac Pinto</td>\n",
       "      <td>Marcelo de Barros Gaspar</td>\n",
       "      <td>115.850.400/0016-9</td>\n",
       "      <td>330,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27146</th>\n",
       "      <td>Nivaldo Albuquerque</td>\n",
       "      <td>FALPE PESQUISAS S/S LTDA</td>\n",
       "      <td>122.821.470/0010-9</td>\n",
       "      <td>280,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16661</th>\n",
       "      <td>Gustinho Ribeiro</td>\n",
       "      <td>LEITE, FIGUEIREDO &amp; SANTOS ADVOGADOS ASSOCIADOS</td>\n",
       "      <td>231.273.990/0017-1</td>\n",
       "      <td>274,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36459</th>\n",
       "      <td>Vicentinho Júnior</td>\n",
       "      <td>DMD Gestão Administrativa LTDA - ME</td>\n",
       "      <td>026.102.350/0012-0</td>\n",
       "      <td>261,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34114</th>\n",
       "      <td>Silas Câmara</td>\n",
       "      <td>joselito gonçalves silva</td>\n",
       "      <td>136.173.810/0010-2</td>\n",
       "      <td>239,790.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12365</th>\n",
       "      <td>Erika Kokay</td>\n",
       "      <td>WHD PESQUISA E ESTRATEGIA LTDA</td>\n",
       "      <td>322.341.900/0010-6</td>\n",
       "      <td>209,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29000</th>\n",
       "      <td>Paulo Teixeira</td>\n",
       "      <td>F.Y.SAKAI-ME</td>\n",
       "      <td>084.221.030/0015-1</td>\n",
       "      <td>201,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25250</th>\n",
       "      <td>Marreca Filho</td>\n",
       "      <td>VERTICE - SOCIEDADE CIVIL DE PROFISSIONAIS ASS...</td>\n",
       "      <td>054.434.490/0014-8</td>\n",
       "      <td>200,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34053</th>\n",
       "      <td>Sidney Leite</td>\n",
       "      <td>CTA - CLEITON TAXI AEREO LTDA</td>\n",
       "      <td>049.844.000/0013-0</td>\n",
       "      <td>184,250.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35486</th>\n",
       "      <td>Toninho Wandscheer</td>\n",
       "      <td>César Setti</td>\n",
       "      <td>792.030.970/0019-0</td>\n",
       "      <td>172,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14076</th>\n",
       "      <td>Frei Anastacio Ribeiro</td>\n",
       "      <td>ART &amp; VIDEO PRODUCOES E STUDIO FOTOGRAFICO LTD...</td>\n",
       "      <td>035.895.860/0016-0</td>\n",
       "      <td>162,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27589</th>\n",
       "      <td>Osires Damaso</td>\n",
       "      <td>DMD Gestão Administrativa LTDA - ME</td>\n",
       "      <td>026.102.350/0012-0</td>\n",
       "      <td>160,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35849</th>\n",
       "      <td>Valtenir Pereira</td>\n",
       "      <td>BRACHIO ASSESSORIA E PLANEJAMENTO LTDA</td>\n",
       "      <td>304.933.120/0014-5</td>\n",
       "      <td>147,634.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29709</th>\n",
       "      <td>Pedro Vilela</td>\n",
       "      <td>PRESS COMUNICACOES S/S</td>\n",
       "      <td>056.829.170/0013-7</td>\n",
       "      <td>147,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15232</th>\n",
       "      <td>Giacobo</td>\n",
       "      <td>GALZON EDITORA GRAFICA LTDA</td>\n",
       "      <td>074.362.650/0018-6</td>\n",
       "      <td>146,700.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23590</th>\n",
       "      <td>Magda Mofatto</td>\n",
       "      <td>Adm. Assis</td>\n",
       "      <td>250.032.450/0010-1</td>\n",
       "      <td>145,280.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2577</th>\n",
       "      <td>Amaro Neto</td>\n",
       "      <td>IXPERTSE MARKETING LTDA</td>\n",
       "      <td>405.389.740/0014-8</td>\n",
       "      <td>141,600.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16622</th>\n",
       "      <td>Gustavo Fruet</td>\n",
       "      <td>BOMBAI COMUNICACAO LTDA</td>\n",
       "      <td>278.143.090/0017-1</td>\n",
       "      <td>141,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1431</th>\n",
       "      <td>Alcides Rodrigues</td>\n",
       "      <td>COLEMAR MOURA SOCIEDADE DE ADVOGADOS S/S</td>\n",
       "      <td>137.611.650/0012-8</td>\n",
       "      <td>140,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34024</th>\n",
       "      <td>Shéridan</td>\n",
       "      <td>SOLUÇÃO CONTABILIDADE, AUDITORIA, ASSESSORIA E...</td>\n",
       "      <td>106.075.000/0014-0</td>\n",
       "      <td>140,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              nomeParlamentar  \\\n",
       "4679              Bilac Pinto   \n",
       "27146     Nivaldo Albuquerque   \n",
       "16661        Gustinho Ribeiro   \n",
       "36459       Vicentinho Júnior   \n",
       "34114            Silas Câmara   \n",
       "12365             Erika Kokay   \n",
       "29000          Paulo Teixeira   \n",
       "25250           Marreca Filho   \n",
       "34053            Sidney Leite   \n",
       "35486      Toninho Wandscheer   \n",
       "14076  Frei Anastacio Ribeiro   \n",
       "27589           Osires Damaso   \n",
       "35849        Valtenir Pereira   \n",
       "29709            Pedro Vilela   \n",
       "15232                 Giacobo   \n",
       "23590           Magda Mofatto   \n",
       "2577               Amaro Neto   \n",
       "16622           Gustavo Fruet   \n",
       "1431        Alcides Rodrigues   \n",
       "34024                Shéridan   \n",
       "\n",
       "                                              fornecedor             cnpjCPF  \\\n",
       "4679                            Marcelo de Barros Gaspar  115.850.400/0016-9   \n",
       "27146                           FALPE PESQUISAS S/S LTDA  122.821.470/0010-9   \n",
       "16661    LEITE, FIGUEIREDO & SANTOS ADVOGADOS ASSOCIADOS  231.273.990/0017-1   \n",
       "36459                DMD Gestão Administrativa LTDA - ME  026.102.350/0012-0   \n",
       "34114                           joselito gonçalves silva  136.173.810/0010-2   \n",
       "12365                     WHD PESQUISA E ESTRATEGIA LTDA  322.341.900/0010-6   \n",
       "29000                                       F.Y.SAKAI-ME  084.221.030/0015-1   \n",
       "25250  VERTICE - SOCIEDADE CIVIL DE PROFISSIONAIS ASS...  054.434.490/0014-8   \n",
       "34053                      CTA - CLEITON TAXI AEREO LTDA  049.844.000/0013-0   \n",
       "35486                                        César Setti  792.030.970/0019-0   \n",
       "14076  ART & VIDEO PRODUCOES E STUDIO FOTOGRAFICO LTD...  035.895.860/0016-0   \n",
       "27589                DMD Gestão Administrativa LTDA - ME  026.102.350/0012-0   \n",
       "35849             BRACHIO ASSESSORIA E PLANEJAMENTO LTDA  304.933.120/0014-5   \n",
       "29709                             PRESS COMUNICACOES S/S  056.829.170/0013-7   \n",
       "15232                        GALZON EDITORA GRAFICA LTDA  074.362.650/0018-6   \n",
       "23590                                         Adm. Assis  250.032.450/0010-1   \n",
       "2577                             IXPERTSE MARKETING LTDA  405.389.740/0014-8   \n",
       "16622                            BOMBAI COMUNICACAO LTDA  278.143.090/0017-1   \n",
       "1431            COLEMAR MOURA SOCIEDADE DE ADVOGADOS S/S  137.611.650/0012-8   \n",
       "34024  SOLUÇÃO CONTABILIDADE, AUDITORIA, ASSESSORIA E...  106.075.000/0014-0   \n",
       "\n",
       "       valorLiquido  \n",
       "4679     330,000.00  \n",
       "27146    280,000.00  \n",
       "16661    274,000.00  \n",
       "36459    261,000.00  \n",
       "34114    239,790.60  \n",
       "12365    209,500.00  \n",
       "29000    201,000.00  \n",
       "25250    200,000.00  \n",
       "34053    184,250.00  \n",
       "35486    172,000.00  \n",
       "14076    162,000.00  \n",
       "27589    160,000.00  \n",
       "35849    147,634.70  \n",
       "29709    147,000.00  \n",
       "15232    146,700.00  \n",
       "23590    145,280.42  \n",
       "2577     141,600.00  \n",
       "16622    141,500.00  \n",
       "1431     140,000.00  \n",
       "34024    140,000.00  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ordenação conjunta - 20 parlamentares + fornecedores com maiores gastos em cotas\n",
    "df_despesas_2021.groupby([\"nomeParlamentar\", \"fornecedor\", \"cnpjCPF\"]).valorLiquido.sum().reset_index().sort_values(\"valorLiquido\", ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62a75ae9-7702-475b-afeb-f19a6ead08df",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd571438-064b-4be0-9a76-e0e5f726ddff",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0eca59e-5939-4aac-b6b1-df8c7291f758",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rotinas para baixar os PDFs dos documentos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "094966cd-c459-46ff-91ee-e6a070611bb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Função para criar um nome de arquivo para as notas fiscais, com 2021 + nome do parlamentar + id interno do documento\n",
    "def limpeza1(linha):\n",
    "    link = str(linha[\"urlDocumento\"]).strip()\n",
    "    nome = str(linha[\"nomeParlamentar\"]).strip()\n",
    "    if link.endswith(\".pdf\"):\n",
    "        return \"2021\"+nome+link[-11:]\n",
    "    else:\n",
    "        return \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "84eef092-1fee-48f9-aaf5-215d0350a4a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_despesas_2021[\"nome_arquivo\"] = df_despesas_2021.apply(limpeza1, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "3f895289-3eee-4a9b-9b76-82ca90014f6d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                                   \n",
       "1                                   \n",
       "2                                   \n",
       "3                                   \n",
       "4                                   \n",
       "                     ...            \n",
       "150232    2021Jones Moura7267606.pdf\n",
       "150233    2021Jones Moura7267607.pdf\n",
       "150234    2021Jones Moura7267608.pdf\n",
       "150235    2021Jones Moura7267609.pdf\n",
       "150236    2021Jones Moura7267610.pdf\n",
       "Name: nome_arquivo, Length: 150237, dtype: object"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_despesas_2021[\"nome_arquivo\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d649a98c-48b9-45f5-aff6-3e0d6ef10bc1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99caa1d0-ac10-44f7-ba4c-092026e44535",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Salva arquivos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e3f63bf6-8665-4655-85c6-92bdcaf58bb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_despesas_2021.to_excel('resultados/gastos_cota_parlamentar_2021.xlsx',sheet_name='Sheet1',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "1c3fbf2c-8ad9-4d7e-b1cd-a49c0269f34b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_despesas_2021.to_csv('resultados/gastos_cota_parlamentar_2021.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa951f63-71ce-428b-995d-ff1108a5e443",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9d9633a-ffc5-4512-a11c-88e386755327",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Seta diretório para gravar arquivos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a1f1a75a-60b9-4ef8-931e-594b1a741c76",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "D:\\Code\\pinpoint\\repo\n"
     ]
    }
   ],
   "source": [
    "print(os.getcwd())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ac4c3992-71e8-4357-99ce-12accba70db9",
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir('D:\\\\Code\\\\pinpoint\\\\repo\\\\resultados\\\\cotas_2021')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "decb9257-6854-4d1a-a6f2-20b50f06623e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3b5f540-d137-4a06-b962-7eac28ded6ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Iteração em cada linha de despesas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e11dded-4b3a-42f4-ad57-00358f2925ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "for num, row in df_despesas_2021.iterrows():\n",
    "    link = row['urlDocumento']\n",
    "    nome = row['nome_arquivo']\n",
    "    #print(link)\n",
    "    \n",
    "    # Se a linha de despesa tiver algum link\n",
    "    if link:\n",
    "        # Testa se o link é direto com PDF\n",
    "        # Se for link com a chave do Portal da Nota Fiscal Eletrônica da Receita Federal não baixo\n",
    "        if link.endswith(\".pdf\"):\n",
    "            try:\n",
    "                # Faz o download\n",
    "                wget.download(link, nome)\n",
    "            except:\n",
    "                # Mostra erros, caso arquivo não exista ou erros na conexão\n",
    "                print(\"ERRO\", link, \"---\", nome)\n",
    "                pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de616982-a28b-40b5-85e2-05b270546816",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c05a987b-819a-41f9-84eb-0730f463bcd0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "0c1eed5f-4aee-45ea-af9d-1d9f995f0828",
   "metadata": {},
   "source": [
    "# Dados de 2020 - baixados 15 de novembro de 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "a415f5e7-f753-4225-9aff-e4bfe8e4010d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Abre arquivo\n",
    "with open(\"cota_parlamentar\\AnoAnterior.xml\", 'r', encoding='utf-8') as f:\n",
    "    xmlString = f.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "d6a9bb10-a7f1-4bad-abf9-9179f51677a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "jsonString = json.dumps(xmltodict.parse(xmlString), indent=4)\n",
    "with open(\"output.json\", 'w') as f:\n",
    "    f.write(jsonString)\n",
    "arquivo_json = open('output.json', 'r')\n",
    "dados_json = json.load(arquivo_json)\n",
    "lista = dados_json['xml']['dados']['despesa']\n",
    "\n",
    "df_despesas_2020 = pd.DataFrame(lista, columns = ['nomeParlamentar', 'cpf', 'idDeputado', 'numeroCarteiraParlamentar', 'legislatura', 'siglaUF', 'siglaPartido',      'codigoLegislatura', 'numeroSubCota', 'descricao', 'numeroEspecificacaoSubCota', 'descricaoEspecificacao',      'fornecedor', 'cnpjCPF', 'numero', 'tipoDocumento', 'dataEmissao', 'valorDocumento', 'valorGlosa', 'valorLiquido', 'mes',  'ano', 'parcela', 'passageiro', 'trecho', 'lote', 'ressarcimento', 'restituicao', 'numeroDeputadoID', 'idDocumento',      'urlDocumento'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "2cf55a01-1e4a-4f26-8814-2d8371f6c4aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 164911 entries, 0 to 164910\n",
      "Data columns (total 31 columns):\n",
      " #   Column                      Non-Null Count   Dtype \n",
      "---  ------                      --------------   ----- \n",
      " 0   nomeParlamentar             164911 non-null  object\n",
      " 1   cpf                         164519 non-null  object\n",
      " 2   idDeputado                  164519 non-null  object\n",
      " 3   numeroCarteiraParlamentar   164519 non-null  object\n",
      " 4   legislatura                 164911 non-null  object\n",
      " 5   siglaUF                     164911 non-null  object\n",
      " 6   siglaPartido                164519 non-null  object\n",
      " 7   codigoLegislatura           164911 non-null  object\n",
      " 8   numeroSubCota               164911 non-null  object\n",
      " 9   descricao                   164911 non-null  object\n",
      " 10  numeroEspecificacaoSubCota  164911 non-null  object\n",
      " 11  descricaoEspecificacao      39954 non-null   object\n",
      " 12  fornecedor                  164893 non-null  object\n",
      " 13  cnpjCPF                     149606 non-null  object\n",
      " 14  numero                      164911 non-null  object\n",
      " 15  tipoDocumento               164911 non-null  object\n",
      " 16  dataEmissao                 158995 non-null  object\n",
      " 17  valorDocumento              164911 non-null  object\n",
      " 18  valorGlosa                  164911 non-null  object\n",
      " 19  valorLiquido                164911 non-null  object\n",
      " 20  mes                         164911 non-null  object\n",
      " 21  ano                         164911 non-null  object\n",
      " 22  parcela                     164911 non-null  object\n",
      " 23  passageiro                  32874 non-null   object\n",
      " 24  trecho                      32645 non-null   object\n",
      " 25  lote                        164911 non-null  object\n",
      " 26  ressarcimento               49076 non-null   object\n",
      " 27  restituicao                 108 non-null     object\n",
      " 28  numeroDeputadoID            164911 non-null  object\n",
      " 29  idDocumento                 164911 non-null  object\n",
      " 30  urlDocumento                106486 non-null  object\n",
      "dtypes: object(31)\n",
      "memory usage: 39.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_despesas_2020.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "83c1ecd9-983f-427f-99b4-7634b0c6b247",
   "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>nomeParlamentar</th>\n",
       "      <th>cpf</th>\n",
       "      <th>idDeputado</th>\n",
       "      <th>numeroCarteiraParlamentar</th>\n",
       "      <th>legislatura</th>\n",
       "      <th>siglaUF</th>\n",
       "      <th>siglaPartido</th>\n",
       "      <th>codigoLegislatura</th>\n",
       "      <th>numeroSubCota</th>\n",
       "      <th>descricao</th>\n",
       "      <th>...</th>\n",
       "      <th>ano</th>\n",
       "      <th>parcela</th>\n",
       "      <th>passageiro</th>\n",
       "      <th>trecho</th>\n",
       "      <th>lote</th>\n",
       "      <th>ressarcimento</th>\n",
       "      <th>restituicao</th>\n",
       "      <th>numeroDeputadoID</th>\n",
       "      <th>idDocumento</th>\n",
       "      <th>urlDocumento</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LIDERANÇA DO DEMOCRATAS</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>4</td>\n",
       "      <td>CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.</td>\n",
       "      <td>...</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1693866</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2713</td>\n",
       "      <td>7050172</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LIDERANÇA DO DEMOCRATAS</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>4</td>\n",
       "      <td>CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.</td>\n",
       "      <td>...</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1698547</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2713</td>\n",
       "      <td>7058437</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LIDERANÇA DO DEMOCRATAS</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>4</td>\n",
       "      <td>CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.</td>\n",
       "      <td>...</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1709149</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2713</td>\n",
       "      <td>7078508</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LIDERANÇA DO DEMOCRATAS</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>4</td>\n",
       "      <td>CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.</td>\n",
       "      <td>...</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1730495</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2713</td>\n",
       "      <td>7123566</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LIDERANÇA DO DEMOCRATAS</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>NA</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>5</td>\n",
       "      <td>DIVULGAÇÃO DA ATIVIDADE PARLAMENTAR.</td>\n",
       "      <td>...</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1703569</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2713</td>\n",
       "      <td>7067718</td>\n",
       "      <td>http://camara.leg.br/cota-parlamentar/nota-fis...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           nomeParlamentar   cpf idDeputado numeroCarteiraParlamentar  \\\n",
       "0  LIDERANÇA DO DEMOCRATAS  None       None                      None   \n",
       "1  LIDERANÇA DO DEMOCRATAS  None       None                      None   \n",
       "2  LIDERANÇA DO DEMOCRATAS  None       None                      None   \n",
       "3  LIDERANÇA DO DEMOCRATAS  None       None                      None   \n",
       "4  LIDERANÇA DO DEMOCRATAS  None       None                      None   \n",
       "\n",
       "  legislatura siglaUF siglaPartido codigoLegislatura numeroSubCota  \\\n",
       "0        2019      NA         None                56             4   \n",
       "1        2019      NA         None                56             4   \n",
       "2        2019      NA         None                56             4   \n",
       "3        2019      NA         None                56             4   \n",
       "4        2019      NA         None                56             5   \n",
       "\n",
       "                                       descricao  ...   ano parcela  \\\n",
       "0  CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.  ...  2020       0   \n",
       "1  CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.  ...  2020       0   \n",
       "2  CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.  ...  2020       0   \n",
       "3  CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.  ...  2020       0   \n",
       "4           DIVULGAÇÃO DA ATIVIDADE PARLAMENTAR.  ...  2020       0   \n",
       "\n",
       "  passageiro trecho     lote ressarcimento restituicao numeroDeputadoID  \\\n",
       "0       None   None  1693866          None        None             2713   \n",
       "1       None   None  1698547          None        None             2713   \n",
       "2       None   None  1709149          None        None             2713   \n",
       "3       None   None  1730495          None        None             2713   \n",
       "4       None   None  1703569          None        None             2713   \n",
       "\n",
       "  idDocumento                                       urlDocumento  \n",
       "0     7050172  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "1     7058437  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "2     7078508  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "3     7123566  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "4     7067718  http://camara.leg.br/cota-parlamentar/nota-fis...  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_despesas_2020.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a2af737-09f1-4f76-ad81-9fba6e186a03",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "d7fb333a-7eb4-4545-8f41-ed940e7d2a9e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(164911, 31)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Tamanho\n",
    "df_despesas_2020.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04d33b29-595b-4e01-b19e-6bf34262ea2b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "3977bc96-29ac-4ad8-8f24-459bd628ca09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(78004, 31)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Separa apenas documentos com PDFs\n",
    "apenas_pdf_2020 = df_despesas_2020[pd.notnull(df_despesas_2020['urlDocumento'])]\n",
    "apenas_pdf_2020 = apenas_pdf_2020[apenas_pdf_2020['urlDocumento'].str.contains(\".pdf\")]\n",
    "apenas_pdf_2020.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ce7aaad-3305-4486-9776-d266335dc3a3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "08349f86-d98b-495c-ba0e-8f68f6ba7edd",
   "metadata": {},
   "source": [
    "### Faz algumas análises nos valores líquidos dos documentos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "b40d46c9-4df4-486d-9195-e512f373132c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_despesas_2020['valorLiquido'] = df_despesas_2020['valorLiquido'].str.replace(',','.')\n",
    "df_despesas_2020['valorLiquido'] = df_despesas_2020['valorLiquido'].astype('float64') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "260eaa87-3cf7-4fdf-b5b9-e7ba6e8c7816",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "4ed95655-95b2-4888-b814-a2463ed90bea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "nomeParlamentar               0\n",
       "cpf                           0\n",
       "idDeputado                    0\n",
       "numeroCarteiraParlamentar     0\n",
       "legislatura                   0\n",
       "siglaUF                       0\n",
       "siglaPartido                  0\n",
       "codigoLegislatura             0\n",
       "numeroSubCota                 0\n",
       "descricao                     0\n",
       "numeroEspecificacaoSubCota    0\n",
       "descricaoEspecificacao        0\n",
       "fornecedor                    0\n",
       "cnpjCPF                       0\n",
       "numero                        0\n",
       "tipoDocumento                 0\n",
       "dataEmissao                   0\n",
       "valorDocumento                0\n",
       "valorGlosa                    0\n",
       "valorLiquido                  0\n",
       "mes                           0\n",
       "ano                           0\n",
       "parcela                       0\n",
       "passageiro                    0\n",
       "trecho                        0\n",
       "lote                          0\n",
       "ressarcimento                 0\n",
       "restituicao                   0\n",
       "numeroDeputadoID              0\n",
       "idDocumento                   0\n",
       "urlDocumento                  0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Mostra se há linhas vazias\n",
    "df_despesas_2020.apply(lambda x: sum(df_despesas_2020['valorLiquido'].isnull()),axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c70fb433-380a-4d7d-8090-bbbc147cb6b2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "6bd50657-b224-46fe-999f-ce08ce55071f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Separa gastos e média \n",
    "gastos = df_despesas_2020['valorLiquido']\n",
    "media_despesas = lambda gastos: (gastos.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38191413-d782-4a5d-99cf-b2adf6030425",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "3f6ad3bc-6426-410f-ab74-9ec7555fd1cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "911.5705036656135\n"
     ]
    }
   ],
   "source": [
    "# Média\n",
    "print(media_despesas(gastos))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "8063c1bc-ea8a-48e4-97a9-b6e3eff2fa19",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "174.37\n"
     ]
    }
   ],
   "source": [
    "# Mediana\n",
    "print(gastos.median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "ecc2eeaf-94df-4813-b5b5-834debaa001e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "151260.0\n"
     ]
    }
   ],
   "source": [
    "# Máximo\n",
    "print(df_despesas_2020['valorLiquido'].max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "74dfdd7b-1e4a-4d1b-a621-990b58cb9c83",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-4484.58\n"
     ]
    }
   ],
   "source": [
    "# Mínimo\n",
    "print(df_despesas_2020['valorLiquido'].min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "0393552c-5968-4fa2-9f42-3568041aea41",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "155744.58\n"
     ]
    }
   ],
   "source": [
    "# Máximo menos Mínimo\n",
    "print(df_despesas_2020['valorLiquido'].max()-df_despesas_2020['valorLiquido'].min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "40112f37-ca4d-4dd7-8473-690975357275",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count   164,911.00\n",
      "mean        911.57\n",
      "std       2,984.41\n",
      "min      -4,484.58\n",
      "25%          40.00\n",
      "50%         174.37\n",
      "75%         599.46\n",
      "max     151,260.00\n",
      "Name: valorLiquido, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Estatísticas gerais\n",
    "print(df_despesas_2020['valorLiquido'].describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2df29092-e2ad-4a5e-a0db-132fdc37ebfb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "bdb43f94-4dda-465a-84a2-5b8c1eaa11c7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150328003.32999998"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total de despesas\n",
    "df_despesas_2020['valorLiquido'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "810f4b27-b268-4574-a08b-0c10eeef55b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   100.00\n",
       "dtype: float64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Valor que mais se repete\n",
    "df_despesas_2020['valorLiquido'].mode()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13a8c24e-3a43-473b-a723-a63d44c473cb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "eacb426c-84cb-44d7-99e7-0d592afd2b4d",
   "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>nomeParlamentar</th>\n",
       "      <th>valorLiquido</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>Jesus Sérgio</td>\n",
       "      <td>543,433.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>Marcelo Ramos</td>\n",
       "      <td>538,354.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>João Maia</td>\n",
       "      <td>528,165.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>Jéssica Sales</td>\n",
       "      <td>527,506.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>Wellington Roberto</td>\n",
       "      <td>524,422.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>Sidney Leite</td>\n",
       "      <td>516,413.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>Silas Câmara</td>\n",
       "      <td>505,819.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>Juscelino Filho</td>\n",
       "      <td>503,591.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>Frei Anastacio Ribeiro</td>\n",
       "      <td>502,525.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>Ruy Carneiro</td>\n",
       "      <td>499,665.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>Sebastião Oliveira</td>\n",
       "      <td>496,707.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Edilázio Júnior</td>\n",
       "      <td>492,992.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Beto Faro</td>\n",
       "      <td>492,517.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>Nereu Crispim</td>\n",
       "      <td>491,631.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>Dra. Vanda Milani</td>\n",
       "      <td>491,452.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>Flaviano Melo</td>\n",
       "      <td>488,051.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>Júlio Cesar</td>\n",
       "      <td>485,851.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>Luiz Carlos</td>\n",
       "      <td>485,407.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>Fábio Henrique</td>\n",
       "      <td>480,790.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>496</th>\n",
       "      <td>Sergio Toledo</td>\n",
       "      <td>480,130.62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            nomeParlamentar  valorLiquido\n",
       "258            Jesus Sérgio    543,433.87\n",
       "359           Marcelo Ramos    538,354.87\n",
       "282               João Maia    528,165.91\n",
       "292           Jéssica Sales    527,506.10\n",
       "542      Wellington Roberto    524,422.42\n",
       "500            Sidney Leite    516,413.14\n",
       "501            Silas Câmara    505,819.34\n",
       "291         Juscelino Filho    503,591.99\n",
       "199  Frei Anastacio Ribeiro    502,525.59\n",
       "488            Ruy Carneiro    499,665.65\n",
       "494      Sebastião Oliveira    496,707.42\n",
       "150         Edilázio Júnior    492,992.86\n",
       "53                Beto Faro    492,517.77\n",
       "395           Nereu Crispim    491,631.58\n",
       "148       Dra. Vanda Milani    491,452.22\n",
       "191           Flaviano Melo    488,051.78\n",
       "293             Júlio Cesar    485,851.40\n",
       "335             Luiz Carlos    485,407.28\n",
       "201          Fábio Henrique    480,790.06\n",
       "496           Sergio Toledo    480,130.62"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 20 deputados com maiores gastos em cotas\n",
    "df_despesas_2020.groupby([\"nomeParlamentar\"]).valorLiquido.sum().reset_index().sort_values(\"valorLiquido\", ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6889945-3415-427c-8f67-1becc1834e2d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "adf807fa-e90b-408f-b404-37c9cd20d1b2",
   "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>fornecedor</th>\n",
       "      <th>cnpjCPF</th>\n",
       "      <th>valorLiquido</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5570</th>\n",
       "      <td>Cia Aérea - TAM</td>\n",
       "      <td>020.128.620/0016-0</td>\n",
       "      <td>5,365,519.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5568</th>\n",
       "      <td>Cia Aérea - GOL</td>\n",
       "      <td>075.756.510/0015-9</td>\n",
       "      <td>5,233,774.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12010</th>\n",
       "      <td>PANTANAL VEÍCULOS LTDA</td>\n",
       "      <td>073.193.230/0019-1</td>\n",
       "      <td>2,487,036.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5567</th>\n",
       "      <td>Cia Aérea - AZUL</td>\n",
       "      <td>092.962.950/0016-0</td>\n",
       "      <td>1,495,227.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7694</th>\n",
       "      <td>GOLD CAR BRASILIA LOCADORA DE VEICULOS ME</td>\n",
       "      <td>267.352.270/0017-9</td>\n",
       "      <td>1,123,325.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4223</th>\n",
       "      <td>CELULAR FUNCIONAL</td>\n",
       "      <td>000.000.000/0000-1</td>\n",
       "      <td>1,043,235.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6007</th>\n",
       "      <td>DMD Gestão Administrativa LTDA - ME</td>\n",
       "      <td>026.102.350/0012-0</td>\n",
       "      <td>1,005,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17668</th>\n",
       "      <td>Telefônica Brasil S. A. VIVO</td>\n",
       "      <td>025.581.570/0016-2</td>\n",
       "      <td>792,641.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17918</th>\n",
       "      <td>VERTICE - SOCIEDADE CIVIL DE PROFISSIONAIS ASS...</td>\n",
       "      <td>054.434.490/0014-8</td>\n",
       "      <td>741,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17674</th>\n",
       "      <td>Telefônica Brasil S.A. VIVO</td>\n",
       "      <td>025.581.570/0016-2</td>\n",
       "      <td>629,988.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7020</th>\n",
       "      <td>FACEBOOK SERVIÇOS ONLINE DO BRASIL LTDA</td>\n",
       "      <td>133.470.160/0011-7</td>\n",
       "      <td>604,139.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17281</th>\n",
       "      <td>T2 COMUNICACAO, VIDEO E PRODUCOES EIRELI - EPP</td>\n",
       "      <td>237.210.170/0013-3</td>\n",
       "      <td>500,657.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808</th>\n",
       "      <td>ANEXO 4 ASSESSORIA, CONSULTORIA, MARKETING E D...</td>\n",
       "      <td>083.229.980/0015-2</td>\n",
       "      <td>487,210.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9961</th>\n",
       "      <td>LINDERLANDIO BARBOSA EIRELI</td>\n",
       "      <td>116.303.180/0017-2</td>\n",
       "      <td>417,796.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15118</th>\n",
       "      <td>QUASAR LOCADORA DE VEICULOS EIRELI</td>\n",
       "      <td>380.246.750/0014-3</td>\n",
       "      <td>415,326.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9930</th>\n",
       "      <td>LIBRE COMUNICAÇÃO E MARKETING EIRELI</td>\n",
       "      <td>083.456.310/0015-4</td>\n",
       "      <td>392,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18269</th>\n",
       "      <td>WORKING ASSOCIACAO DE INTEGRACAO PROFISSIONAL</td>\n",
       "      <td>088.656.150/0019-2</td>\n",
       "      <td>389,300.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8421</th>\n",
       "      <td>HPE AUTOMOTORES DO BRASIL LTDA</td>\n",
       "      <td>543.057.430/0117-0</td>\n",
       "      <td>382,892.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5739</th>\n",
       "      <td>DALETH VEICULOS LTDA EPP</td>\n",
       "      <td>045.159.630/0018-9</td>\n",
       "      <td>364,019.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18548</th>\n",
       "      <td>joselito gonçalves silva</td>\n",
       "      <td>136.173.810/0010-2</td>\n",
       "      <td>359,743.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              fornecedor             cnpjCPF  \\\n",
       "5570                                     Cia Aérea - TAM  020.128.620/0016-0   \n",
       "5568                                     Cia Aérea - GOL  075.756.510/0015-9   \n",
       "12010                             PANTANAL VEÍCULOS LTDA  073.193.230/0019-1   \n",
       "5567                                    Cia Aérea - AZUL  092.962.950/0016-0   \n",
       "7694           GOLD CAR BRASILIA LOCADORA DE VEICULOS ME  267.352.270/0017-9   \n",
       "4223                                   CELULAR FUNCIONAL  000.000.000/0000-1   \n",
       "6007                 DMD Gestão Administrativa LTDA - ME  026.102.350/0012-0   \n",
       "17668                       Telefônica Brasil S. A. VIVO  025.581.570/0016-2   \n",
       "17918  VERTICE - SOCIEDADE CIVIL DE PROFISSIONAIS ASS...  054.434.490/0014-8   \n",
       "17674                        Telefônica Brasil S.A. VIVO  025.581.570/0016-2   \n",
       "7020             FACEBOOK SERVIÇOS ONLINE DO BRASIL LTDA  133.470.160/0011-7   \n",
       "17281     T2 COMUNICACAO, VIDEO E PRODUCOES EIRELI - EPP  237.210.170/0013-3   \n",
       "808    ANEXO 4 ASSESSORIA, CONSULTORIA, MARKETING E D...  083.229.980/0015-2   \n",
       "9961                         LINDERLANDIO BARBOSA EIRELI  116.303.180/0017-2   \n",
       "15118                 QUASAR LOCADORA DE VEICULOS EIRELI  380.246.750/0014-3   \n",
       "9930                LIBRE COMUNICAÇÃO E MARKETING EIRELI  083.456.310/0015-4   \n",
       "18269      WORKING ASSOCIACAO DE INTEGRACAO PROFISSIONAL  088.656.150/0019-2   \n",
       "8421                      HPE AUTOMOTORES DO BRASIL LTDA  543.057.430/0117-0   \n",
       "5739                            DALETH VEICULOS LTDA EPP  045.159.630/0018-9   \n",
       "18548                           joselito gonçalves silva  136.173.810/0010-2   \n",
       "\n",
       "       valorLiquido  \n",
       "5570   5,365,519.43  \n",
       "5568   5,233,774.62  \n",
       "12010  2,487,036.33  \n",
       "5567   1,495,227.72  \n",
       "7694   1,123,325.00  \n",
       "4223   1,043,235.58  \n",
       "6007   1,005,000.00  \n",
       "17668    792,641.78  \n",
       "17918    741,500.00  \n",
       "17674    629,988.07  \n",
       "7020     604,139.67  \n",
       "17281    500,657.22  \n",
       "808      487,210.00  \n",
       "9961     417,796.00  \n",
       "15118    415,326.02  \n",
       "9930     392,000.00  \n",
       "18269    389,300.00  \n",
       "8421     382,892.50  \n",
       "5739     364,019.00  \n",
       "18548    359,743.71  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 20 fornecedores com maiores gastos em cotas\n",
    "df_despesas_2020.groupby([\"fornecedor\", \"cnpjCPF\"]).valorLiquido.sum().reset_index().sort_values(\"valorLiquido\", ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fbfb7441-565b-412e-8729-6c207cae9620",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "6383dbe7-b1c8-4639-b8bb-f7c0d39d5a72",
   "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>descricao</th>\n",
       "      <th>valorLiquido</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DIVULGAÇÃO DA ATIVIDADE PARLAMENTAR.</td>\n",
       "      <td>54,647,440.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LOCAÇÃO OU FRETAMENTO DE VEÍCULOS AUTOMOTORES</td>\n",
       "      <td>25,067,835.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MANUTENÇÃO DE ESCRITÓRIO DE APOIO À ATIVIDADE ...</td>\n",
       "      <td>24,255,062.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.</td>\n",
       "      <td>23,085,178.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PASSAGEM AÉREA - RPA</td>\n",
       "      <td>12,179,915.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COMBUSTÍVEIS E LUBRIFICANTES.</td>\n",
       "      <td>10,756,800.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>TELEFONIA</td>\n",
       "      <td>4,388,846.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LOCAÇÃO OU FRETAMENTO DE AERONAVES</td>\n",
       "      <td>1,532,057.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>PASSAGEM AÉREA - REEMBOLSO</td>\n",
       "      <td>1,281,126.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>SERVIÇOS POSTAIS</td>\n",
       "      <td>1,273,417.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>SERVIÇO DE SEGURANÇA PRESTADO POR EMPRESA ESPE...</td>\n",
       "      <td>995,533.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HOSPEDAGEM ,EXCETO DO PARLAMENTAR NO DISTRITO ...</td>\n",
       "      <td>975,110.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>SERVIÇO DE TÁXI, PEDÁGIO E ESTACIONAMENTO</td>\n",
       "      <td>480,901.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR</td>\n",
       "      <td>325,557.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASSINATURA DE PUBLICAÇÕES</td>\n",
       "      <td>190,519.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LOCAÇÃO OU FRETAMENTO DE EMBARCAÇÕES</td>\n",
       "      <td>181,550.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>PASSAGENS TERRESTRES, MARÍTIMAS OU FLUVIAIS</td>\n",
       "      <td>29,567.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>PARTICIPAÇÃO EM CURSO, PALESTRA OU EVENTO SIMILAR</td>\n",
       "      <td>16,777.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>PASSAGEM AÉREA - SIGEPA</td>\n",
       "      <td>-11,335,193.72</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            descricao   valorLiquido\n",
       "3                DIVULGAÇÃO DA ATIVIDADE PARLAMENTAR.  54,647,440.55\n",
       "8       LOCAÇÃO OU FRETAMENTO DE VEÍCULOS AUTOMOTORES  25,067,835.57\n",
       "9   MANUTENÇÃO DE ESCRITÓRIO DE APOIO À ATIVIDADE ...  24,255,062.62\n",
       "2       CONSULTORIAS, PESQUISAS E TRABALHOS TÉCNICOS.  23,085,178.74\n",
       "12                               PASSAGEM AÉREA - RPA  12,179,915.06\n",
       "1                       COMBUSTÍVEIS E LUBRIFICANTES.  10,756,800.81\n",
       "18                                          TELEFONIA   4,388,846.20\n",
       "6                  LOCAÇÃO OU FRETAMENTO DE AERONAVES   1,532,057.00\n",
       "11                         PASSAGEM AÉREA - REEMBOLSO   1,281,126.52\n",
       "17                                   SERVIÇOS POSTAIS   1,273,417.42\n",
       "15  SERVIÇO DE SEGURANÇA PRESTADO POR EMPRESA ESPE...     995,533.59\n",
       "5   HOSPEDAGEM ,EXCETO DO PARLAMENTAR NO DISTRITO ...     975,110.29\n",
       "16          SERVIÇO DE TÁXI, PEDÁGIO E ESTACIONAMENTO     480,901.38\n",
       "4          FORNECIMENTO DE ALIMENTAÇÃO DO PARLAMENTAR     325,557.84\n",
       "0                           ASSINATURA DE PUBLICAÇÕES     190,519.03\n",
       "7                LOCAÇÃO OU FRETAMENTO DE EMBARCAÇÕES     181,550.00\n",
       "14        PASSAGENS TERRESTRES, MARÍTIMAS OU FLUVIAIS      29,567.27\n",
       "10  PARTICIPAÇÃO EM CURSO, PALESTRA OU EVENTO SIMILAR      16,777.16\n",
       "13                            PASSAGEM AÉREA - SIGEPA -11,335,193.72"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 20 tipos de despesas com mais gastos\n",
    "df_despesas_2020.groupby([\"descricao\"]).valorLiquido.sum().reset_index().sort_values(\"valorLiquido\", ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85330df5-33ee-4106-9acd-e37042f0079a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "8ad41047-13d2-4207-ad2c-e7b67d250a36",
   "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>nomeParlamentar</th>\n",
       "      <th>fornecedor</th>\n",
       "      <th>cnpjCPF</th>\n",
       "      <th>valorLiquido</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>34371</th>\n",
       "      <td>Vicentinho Júnior</td>\n",
       "      <td>DMD Gestão Administrativa LTDA - ME</td>\n",
       "      <td>026.102.350/0012-0</td>\n",
       "      <td>435,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13675</th>\n",
       "      <td>Frei Anastacio Ribeiro</td>\n",
       "      <td>ART &amp; VIDEO PRODUCOES E STUDIO FOTOGRAFICO LTD...</td>\n",
       "      <td>035.895.860/0016-0</td>\n",
       "      <td>344,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16162</th>\n",
       "      <td>Gustinho Ribeiro</td>\n",
       "      <td>LEITE, FIGUEIREDO &amp; SANTOS ADVOGADOS ASSOCIADOS</td>\n",
       "      <td>231.273.990/0017-1</td>\n",
       "      <td>316,200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27705</th>\n",
       "      <td>Paulo Teixeira</td>\n",
       "      <td>F.Y.SAKAI-ME</td>\n",
       "      <td>084.221.030/0015-1</td>\n",
       "      <td>272,300.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32386</th>\n",
       "      <td>Silvia Cristina</td>\n",
       "      <td>CRIATTO PUBLICIDADE LTDA ME</td>\n",
       "      <td>056.254.420/0014-7</td>\n",
       "      <td>269,856.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26356</th>\n",
       "      <td>Osires Damaso</td>\n",
       "      <td>DMD Gestão Administrativa LTDA - ME</td>\n",
       "      <td>026.102.350/0012-0</td>\n",
       "      <td>240,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6578</th>\n",
       "      <td>Celso Sabino</td>\n",
       "      <td>PINHEIRO &amp; MELO ADVOGADOS ASSOCIADOS S/S</td>\n",
       "      <td>073.334.770/0013-8</td>\n",
       "      <td>240,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30402</th>\n",
       "      <td>Rodrigo Maia</td>\n",
       "      <td>LARA GARCIA MUELLER COSTA - ME</td>\n",
       "      <td>174.909.260/0010-5</td>\n",
       "      <td>240,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16694</th>\n",
       "      <td>Hermes Parcianello</td>\n",
       "      <td>ARTGRAFICA PREMIO LTDA - ME</td>\n",
       "      <td>089.365.840/0011-3</td>\n",
       "      <td>233,905.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24135</th>\n",
       "      <td>Marreca Filho</td>\n",
       "      <td>VERTICE - SOCIEDADE CIVIL DE PROFISSIONAIS ASS...</td>\n",
       "      <td>054.434.490/0014-8</td>\n",
       "      <td>229,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1872</th>\n",
       "      <td>Alice Portugal</td>\n",
       "      <td>ANEXO 4 ASSESSORIA, CONSULTORIA, MARKETING E D...</td>\n",
       "      <td>083.229.980/0015-2</td>\n",
       "      <td>213,760.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1346</th>\n",
       "      <td>Alcides Rodrigues</td>\n",
       "      <td>COLEMAR MOURA SOCIEDADE DE ADVOGADOS S/S</td>\n",
       "      <td>137.611.650/0012-8</td>\n",
       "      <td>210,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5128</th>\n",
       "      <td>Capitão Alberto Neto</td>\n",
       "      <td>EMA Serviços Técnicos de Comunicações e Inform...</td>\n",
       "      <td>173.650.020/0018-7</td>\n",
       "      <td>202,408.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32357</th>\n",
       "      <td>Silas Câmara</td>\n",
       "      <td>joselito gonçalves silva</td>\n",
       "      <td>136.173.810/0010-2</td>\n",
       "      <td>201,764.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35305</th>\n",
       "      <td>Wellington Roberto</td>\n",
       "      <td>ELLITE COMERCIO E SERVICOS GRAFICOS LTDA - ME</td>\n",
       "      <td>022.653.720/0017-5</td>\n",
       "      <td>200,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32298</th>\n",
       "      <td>Sidney Leite</td>\n",
       "      <td>CTA - CLEITON TAXI AEREO LTDA</td>\n",
       "      <td>049.844.000/0013-0</td>\n",
       "      <td>196,300.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4573</th>\n",
       "      <td>Bilac Pinto</td>\n",
       "      <td>Marcelo de Barros Gaspar</td>\n",
       "      <td>115.850.400/0016-9</td>\n",
       "      <td>195,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>Acácio Favacho</td>\n",
       "      <td>TRAVEL TO AMAZON EIRELI - EPP</td>\n",
       "      <td>239.824.840/0011-7</td>\n",
       "      <td>192,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33577</th>\n",
       "      <td>Toninho Wandscheer</td>\n",
       "      <td>César Setti</td>\n",
       "      <td>792.030.970/0019-0</td>\n",
       "      <td>192,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7990</th>\n",
       "      <td>Damião Feliciano</td>\n",
       "      <td>MAIS PROPAGANDA LTDA.</td>\n",
       "      <td>027.737.230/0015-9</td>\n",
       "      <td>190,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              nomeParlamentar  \\\n",
       "34371       Vicentinho Júnior   \n",
       "13675  Frei Anastacio Ribeiro   \n",
       "16162        Gustinho Ribeiro   \n",
       "27705          Paulo Teixeira   \n",
       "32386         Silvia Cristina   \n",
       "26356           Osires Damaso   \n",
       "6578             Celso Sabino   \n",
       "30402            Rodrigo Maia   \n",
       "16694      Hermes Parcianello   \n",
       "24135           Marreca Filho   \n",
       "1872           Alice Portugal   \n",
       "1346        Alcides Rodrigues   \n",
       "5128     Capitão Alberto Neto   \n",
       "32357            Silas Câmara   \n",
       "35305      Wellington Roberto   \n",
       "32298            Sidney Leite   \n",
       "4573              Bilac Pinto   \n",
       "395            Acácio Favacho   \n",
       "33577      Toninho Wandscheer   \n",
       "7990         Damião Feliciano   \n",
       "\n",
       "                                              fornecedor             cnpjCPF  \\\n",
       "34371                DMD Gestão Administrativa LTDA - ME  026.102.350/0012-0   \n",
       "13675  ART & VIDEO PRODUCOES E STUDIO FOTOGRAFICO LTD...  035.895.860/0016-0   \n",
       "16162    LEITE, FIGUEIREDO & SANTOS ADVOGADOS ASSOCIADOS  231.273.990/0017-1   \n",
       "27705                                       F.Y.SAKAI-ME  084.221.030/0015-1   \n",
       "32386                        CRIATTO PUBLICIDADE LTDA ME  056.254.420/0014-7   \n",
       "26356                DMD Gestão Administrativa LTDA - ME  026.102.350/0012-0   \n",
       "6578            PINHEIRO & MELO ADVOGADOS ASSOCIADOS S/S  073.334.770/0013-8   \n",
       "30402                     LARA GARCIA MUELLER COSTA - ME  174.909.260/0010-5   \n",
       "16694                        ARTGRAFICA PREMIO LTDA - ME  089.365.840/0011-3   \n",
       "24135  VERTICE - SOCIEDADE CIVIL DE PROFISSIONAIS ASS...  054.434.490/0014-8   \n",
       "1872   ANEXO 4 ASSESSORIA, CONSULTORIA, MARKETING E D...  083.229.980/0015-2   \n",
       "1346            COLEMAR MOURA SOCIEDADE DE ADVOGADOS S/S  137.611.650/0012-8   \n",
       "5128   EMA Serviços Técnicos de Comunicações e Inform...  173.650.020/0018-7   \n",
       "32357                           joselito gonçalves silva  136.173.810/0010-2   \n",
       "35305      ELLITE COMERCIO E SERVICOS GRAFICOS LTDA - ME  022.653.720/0017-5   \n",
       "32298                      CTA - CLEITON TAXI AEREO LTDA  049.844.000/0013-0   \n",
       "4573                            Marcelo de Barros Gaspar  115.850.400/0016-9   \n",
       "395                        TRAVEL TO AMAZON EIRELI - EPP  239.824.840/0011-7   \n",
       "33577                                        César Setti  792.030.970/0019-0   \n",
       "7990                               MAIS PROPAGANDA LTDA.  027.737.230/0015-9   \n",
       "\n",
       "       valorLiquido  \n",
       "34371    435,000.00  \n",
       "13675    344,000.00  \n",
       "16162    316,200.00  \n",
       "27705    272,300.00  \n",
       "32386    269,856.30  \n",
       "26356    240,000.00  \n",
       "6578     240,000.00  \n",
       "30402    240,000.00  \n",
       "16694    233,905.00  \n",
       "24135    229,000.00  \n",
       "1872     213,760.00  \n",
       "1346     210,000.00  \n",
       "5128     202,408.50  \n",
       "32357    201,764.53  \n",
       "35305    200,000.00  \n",
       "32298    196,300.00  \n",
       "4573     195,000.00  \n",
       "395      192,500.00  \n",
       "33577    192,000.00  \n",
       "7990     190,000.00  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ordenação conjunta - 20 parlamentares + fornecedores com maiores gastos em cotas\n",
    "df_despesas_2020.groupby([\"nomeParlamentar\", \"fornecedor\", \"cnpjCPF\"]).valorLiquido.sum().reset_index().sort_values(\"valorLiquido\", ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79fea0fb-c2e3-4bb7-88a0-72762b946bcc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "673d3640-3f42-4d56-815e-c0a4a05cc0a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rotinas para baixar os PDFs dos documentos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "e911808c-8677-4bb1-942e-8a5afb7aeb5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Função para criar um nome de arquivo para as notas fiscais, com 2020 + nome do parlamentar + id interno do documento\n",
    "def limpeza1(linha):\n",
    "    link = str(linha[\"urlDocumento\"]).strip()\n",
    "    nome = str(linha[\"nomeParlamentar\"]).strip()\n",
    "    if link.endswith(\".pdf\"):\n",
    "        return \"2020\"+nome+link[-11:]\n",
    "    else:\n",
    "        return \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "a0d135fe-b0b5-472c-b668-09eb71d918d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_despesas_2020[\"nome_arquivo\"] = df_despesas_2020.apply(limpeza1, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ae7824d-49a4-4406-af3a-9e960cf76ba6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "112f95d9-97a3-494b-aeb5-42d57b992269",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Salva arquivos\n",
    "df_despesas_2020.to_excel('resultados/gastos_cota_parlamentar_2020.xlsx',sheet_name='Sheet1',index=False)\n",
    "df_despesas_2020.to_csv('resultados/gastos_cota_parlamentar_2020.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fa24d6b-aaf9-4102-b81f-69311365783c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Iteração em cada linha de despesas\n",
    "for num, row in df_despesas_2020.iterrows():\n",
    "    link = row['urlDocumento']\n",
    "    nome = row['nome_arquivo']\n",
    "    #print(link)\n",
    "    \n",
    "    # Se a linha de despesa tiver algum link\n",
    "    if link:\n",
    "        # Testa se o link é direto com PDF\n",
    "        # Se for link com a chave do Portal da Nota Fiscal Eletrônica da Receita Federal não baixo\n",
    "        if link.endswith(\".pdf\"):\n",
    "            try:\n",
    "                # Faz o download\n",
    "                wget.download(link, nome)\n",
    "            except:\n",
    "                # Mostra erros, caso arquivo não exista ou erros na conexão\n",
    "                print(\"ERRO\", link, \"---\", nome)\n",
    "                pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6cb16fa2-22b7-40c8-89d0-869a0f074295",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}