TabelasCompostas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlalchemy as sq\n",
    "from sqlalchemy import text\n",
    "import pyodbc as po\n",
    "import pymysql as pm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:1: SyntaxWarning: invalid escape sequence '\\I'\n",
      "<>:1: SyntaxWarning: invalid escape sequence '\\I'\n",
      "C:\\Users\\Controladoria\\AppData\\Local\\Temp\\ipykernel_17084\\3766686350.py:1: SyntaxWarning: invalid escape sequence '\\I'\n",
      "  SERVER = 'SEVENTH\\INSIDEINS'\n"
     ]
    }
   ],
   "source": [
    "SERVER = 'SEVENTH\\INSIDEINS'\n",
    "DATABASE = 'Service'\n",
    "DRIVER = 'SQL Server Native Client 11.0'\n",
    "USERNAME = 'controladoria'\n",
    "PASSWORD = 'Brb1508.'\n",
    "DATABASE_CONNECTION = f'mssql://{USERNAME}:{PASSWORD}@{SERVER}/{DATABASE}?driver={DRIVER}'\n",
    "\n",
    "engine = sq.create_engine(DATABASE_CONNECTION)\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "metadata": {},
   "outputs": [],
   "source": [
    "CentroResultados = pd.read_sql_query('SELECT * FROM [Service].[dbo].[CentroResultados]',connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {},
   "outputs": [],
   "source": [
    "CentroResultados = CentroResultados.query(\"ApuraResultado == True\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "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>CodInterno</th>\n",
       "      <th>Descrição</th>\n",
       "      <th>ReceitaDespesa</th>\n",
       "      <th>Tipo</th>\n",
       "      <th>Rateia</th>\n",
       "      <th>Ordem</th>\n",
       "      <th>TpRateio</th>\n",
       "      <th>RatResultados</th>\n",
       "      <th>RateiaEmpresa</th>\n",
       "      <th>ApuraResultado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>87.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>False</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>88.0</td>\n",
       "      <td>Receita venda de produtos</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>False</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>224.0</td>\n",
       "      <td>Receita facilities</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>False</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>90.0</td>\n",
       "      <td>Recebimento de empréstimo</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>False</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91.0</td>\n",
       "      <td>Juros Recebidos</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>False</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>267.0</td>\n",
       "      <td>Antivírus</td>\n",
       "      <td>D</td>\n",
       "      <td>Despesa Operacional</td>\n",
       "      <td>False</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>270.0</td>\n",
       "      <td>Receita escolta armada</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>False</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>272.0</td>\n",
       "      <td>Cesta básica</td>\n",
       "      <td>D</td>\n",
       "      <td>Folha de Pagamento</td>\n",
       "      <td>False</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>276.0</td>\n",
       "      <td>Acordo Judicial</td>\n",
       "      <td>D</td>\n",
       "      <td>Folha de Pagamento</td>\n",
       "      <td>False</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>256.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>D</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>False</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>101 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CodInterno                         Descrição ReceitaDespesa  \\\n",
       "0          87.0             Receita monitoramento              R   \n",
       "1          88.0         Receita venda de produtos              R   \n",
       "2         224.0                Receita facilities              R   \n",
       "3          90.0         Recebimento de empréstimo              R   \n",
       "4          91.0                   Juros Recebidos              R   \n",
       "..          ...                               ...            ...   \n",
       "110       267.0                         Antivírus              D   \n",
       "111       270.0            Receita escolta armada              R   \n",
       "112       272.0                      Cesta básica              D   \n",
       "113       276.0                   Acordo Judicial              D   \n",
       "125       256.0  Encargos/Tarifas Polícia Federal              D   \n",
       "\n",
       "                    Tipo  Rateia  Ordem  TpRateio  RatResultados  \\\n",
       "0               Entradas   False      3         0              0   \n",
       "1               Entradas   False      3         0              0   \n",
       "2               Entradas   False      3         0              0   \n",
       "3               Entradas   False      3         0              0   \n",
       "4               Entradas   False      3         0              0   \n",
       "..                   ...     ...    ...       ...            ...   \n",
       "110  Despesa Operacional   False      4         0              0   \n",
       "111             Entradas   False      3         0              0   \n",
       "112   Folha de Pagamento   False      4         0              0   \n",
       "113   Folha de Pagamento   False      4         0              0   \n",
       "125             Impostos   False      4         0              0   \n",
       "\n",
       "     RateiaEmpresa  ApuraResultado  \n",
       "0              0.0            True  \n",
       "1              0.0            True  \n",
       "2              0.0            True  \n",
       "3              0.0            True  \n",
       "4              NaN            True  \n",
       "..             ...             ...  \n",
       "110            0.0            True  \n",
       "111            0.0            True  \n",
       "112            0.0            True  \n",
       "113            0.0            True  \n",
       "125            0.0            True  \n",
       "\n",
       "[101 rows x 10 columns]"
      ]
     },
     "execution_count": 292,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CentroResultados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "metadata": {},
   "outputs": [],
   "source": [
    "SubContas = pd.read_sql_query('SELECT * FROM [Service].[dbo].[SubContas]',connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 294,
   "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>CodInterno</th>\n",
       "      <th>Descrição</th>\n",
       "      <th>CentroResultados</th>\n",
       "      <th>Veículos</th>\n",
       "      <th>DespVeículos</th>\n",
       "      <th>Detalhada</th>\n",
       "      <th>ContaExportacao</th>\n",
       "      <th>Inativo</th>\n",
       "      <th>CentroVinculado</th>\n",
       "      <th>SomaBI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43.0</td>\n",
       "      <td>Receita monitoramento de alarme</td>\n",
       "      <td>87.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>8.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>47.0</td>\n",
       "      <td>Receita venda de produtos</td>\n",
       "      <td>88.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>7.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76.0</td>\n",
       "      <td>Receita posto de serviço porteiro</td>\n",
       "      <td>224.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>14.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>450.0</td>\n",
       "      <td>Recebimento de empréstimo</td>\n",
       "      <td>90.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>12.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1571.0</td>\n",
       "      <td>Juros Recebidos</td>\n",
       "      <td>91.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>12.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>101.0</td>\n",
       "      <td>Taxa maquina de cartão</td>\n",
       "      <td>240.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>15.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>102.0</td>\n",
       "      <td>Tarifas PIX/ TED/ DOC</td>\n",
       "      <td>129.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>15.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>168.0</td>\n",
       "      <td>Ajuda de custo</td>\n",
       "      <td>260.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>169.0</td>\n",
       "      <td>Pensão alimentícia</td>\n",
       "      <td>263.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>193.0</td>\n",
       "      <td>Técnico terceirizado para projetos energia solar</td>\n",
       "      <td>264.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>7.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>257 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CodInterno                                         Descrição  \\\n",
       "0          43.0                   Receita monitoramento de alarme   \n",
       "1          47.0                         Receita venda de produtos   \n",
       "2          76.0                 Receita posto de serviço porteiro   \n",
       "3         450.0                         Recebimento de empréstimo   \n",
       "4        1571.0                                   Juros Recebidos   \n",
       "..          ...                                               ...   \n",
       "252       101.0                            Taxa maquina de cartão   \n",
       "253       102.0                             Tarifas PIX/ TED/ DOC   \n",
       "254       168.0                                    Ajuda de custo   \n",
       "255       169.0                                Pensão alimentícia   \n",
       "256       193.0  Técnico terceirizado para projetos energia solar   \n",
       "\n",
       "     CentroResultados Veículos  DespVeículos Detalhada ContaExportacao  \\\n",
       "0                87.0    False           NaN     False            None   \n",
       "1                88.0    False           NaN     False            None   \n",
       "2               224.0    False           NaN      None            None   \n",
       "3                90.0    False           NaN     False            None   \n",
       "4                91.0    False           NaN     False            None   \n",
       "..                ...      ...           ...       ...             ...   \n",
       "252             240.0    False           NaN      None            None   \n",
       "253             129.0    False           NaN      None            None   \n",
       "254             260.0    False           NaN      None            None   \n",
       "255             263.0    False           NaN      None            None   \n",
       "256             264.0    False           NaN      None            None   \n",
       "\n",
       "    Inativo  CentroVinculado SomaBI  \n",
       "0     False              8.0  False  \n",
       "1     False              7.0  False  \n",
       "2     False             14.0  False  \n",
       "3     False             12.0  False  \n",
       "4     False             12.0  False  \n",
       "..      ...              ...    ...  \n",
       "252   False             15.0  False  \n",
       "253   False             15.0  False  \n",
       "254   False              NaN  False  \n",
       "255   False              NaN  False  \n",
       "256   False              7.0  False  \n",
       "\n",
       "[257 rows x 10 columns]"
      ]
     },
     "execution_count": 294,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SubContas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "metadata": {},
   "outputs": [],
   "source": [
    "SubContas = SubContas.rename(columns={'CodInterno':'CentroResultados','CentroResultados':'CodInterno'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "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>CentroResultados</th>\n",
       "      <th>Descrição</th>\n",
       "      <th>CodInterno</th>\n",
       "      <th>Veículos</th>\n",
       "      <th>DespVeículos</th>\n",
       "      <th>Detalhada</th>\n",
       "      <th>ContaExportacao</th>\n",
       "      <th>Inativo</th>\n",
       "      <th>CentroVinculado</th>\n",
       "      <th>SomaBI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43.0</td>\n",
       "      <td>Receita monitoramento de alarme</td>\n",
       "      <td>87.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>8.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>47.0</td>\n",
       "      <td>Receita venda de produtos</td>\n",
       "      <td>88.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>7.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76.0</td>\n",
       "      <td>Receita posto de serviço porteiro</td>\n",
       "      <td>224.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>14.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>450.0</td>\n",
       "      <td>Recebimento de empréstimo</td>\n",
       "      <td>90.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>12.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1571.0</td>\n",
       "      <td>Juros Recebidos</td>\n",
       "      <td>91.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>12.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>101.0</td>\n",
       "      <td>Taxa maquina de cartão</td>\n",
       "      <td>240.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>15.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>102.0</td>\n",
       "      <td>Tarifas PIX/ TED/ DOC</td>\n",
       "      <td>129.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>15.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>168.0</td>\n",
       "      <td>Ajuda de custo</td>\n",
       "      <td>260.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>169.0</td>\n",
       "      <td>Pensão alimentícia</td>\n",
       "      <td>263.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>193.0</td>\n",
       "      <td>Técnico terceirizado para projetos energia solar</td>\n",
       "      <td>264.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>7.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>257 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CentroResultados                                         Descrição  \\\n",
       "0                43.0                   Receita monitoramento de alarme   \n",
       "1                47.0                         Receita venda de produtos   \n",
       "2                76.0                 Receita posto de serviço porteiro   \n",
       "3               450.0                         Recebimento de empréstimo   \n",
       "4              1571.0                                   Juros Recebidos   \n",
       "..                ...                                               ...   \n",
       "252             101.0                            Taxa maquina de cartão   \n",
       "253             102.0                             Tarifas PIX/ TED/ DOC   \n",
       "254             168.0                                    Ajuda de custo   \n",
       "255             169.0                                Pensão alimentícia   \n",
       "256             193.0  Técnico terceirizado para projetos energia solar   \n",
       "\n",
       "     CodInterno Veículos  DespVeículos Detalhada ContaExportacao Inativo  \\\n",
       "0          87.0    False           NaN     False            None   False   \n",
       "1          88.0    False           NaN     False            None   False   \n",
       "2         224.0    False           NaN      None            None   False   \n",
       "3          90.0    False           NaN     False            None   False   \n",
       "4          91.0    False           NaN     False            None   False   \n",
       "..          ...      ...           ...       ...             ...     ...   \n",
       "252       240.0    False           NaN      None            None   False   \n",
       "253       129.0    False           NaN      None            None   False   \n",
       "254       260.0    False           NaN      None            None   False   \n",
       "255       263.0    False           NaN      None            None   False   \n",
       "256       264.0    False           NaN      None            None   False   \n",
       "\n",
       "     CentroVinculado SomaBI  \n",
       "0                8.0  False  \n",
       "1                7.0  False  \n",
       "2               14.0  False  \n",
       "3               12.0  False  \n",
       "4               12.0  False  \n",
       "..               ...    ...  \n",
       "252             15.0  False  \n",
       "253             15.0  False  \n",
       "254              NaN  False  \n",
       "255              NaN  False  \n",
       "256              7.0  False  \n",
       "\n",
       "[257 rows x 10 columns]"
      ]
     },
     "execution_count": 296,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SubContas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "metadata": {},
   "outputs": [],
   "source": [
    "Codigos = SubContas.filter(['CentroResultados','CodInterno'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "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>CentroResultados</th>\n",
       "      <th>CodInterno</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43.0</td>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>47.0</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76.0</td>\n",
       "      <td>224.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>450.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1571.0</td>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>101.0</td>\n",
       "      <td>240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>102.0</td>\n",
       "      <td>129.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>168.0</td>\n",
       "      <td>260.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>169.0</td>\n",
       "      <td>263.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>193.0</td>\n",
       "      <td>264.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>257 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CentroResultados  CodInterno\n",
       "0                43.0        87.0\n",
       "1                47.0        88.0\n",
       "2                76.0       224.0\n",
       "3               450.0        90.0\n",
       "4              1571.0        91.0\n",
       "..                ...         ...\n",
       "252             101.0       240.0\n",
       "253             102.0       129.0\n",
       "254             168.0       260.0\n",
       "255             169.0       263.0\n",
       "256             193.0       264.0\n",
       "\n",
       "[257 rows x 2 columns]"
      ]
     },
     "execution_count": 298,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Codigos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "metadata": {},
   "outputs": [],
   "source": [
    "Contas = CentroResultados.filter(['CodInterno','Descrição','ReceitaDespesa','Tipo'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "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>CodInterno</th>\n",
       "      <th>Descrição</th>\n",
       "      <th>ReceitaDespesa</th>\n",
       "      <th>Tipo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>87.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>88.0</td>\n",
       "      <td>Receita venda de produtos</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>224.0</td>\n",
       "      <td>Receita facilities</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>90.0</td>\n",
       "      <td>Recebimento de empréstimo</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91.0</td>\n",
       "      <td>Juros Recebidos</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>267.0</td>\n",
       "      <td>Antivírus</td>\n",
       "      <td>D</td>\n",
       "      <td>Despesa Operacional</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>270.0</td>\n",
       "      <td>Receita escolta armada</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>272.0</td>\n",
       "      <td>Cesta básica</td>\n",
       "      <td>D</td>\n",
       "      <td>Folha de Pagamento</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>276.0</td>\n",
       "      <td>Acordo Judicial</td>\n",
       "      <td>D</td>\n",
       "      <td>Folha de Pagamento</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>256.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>D</td>\n",
       "      <td>Impostos</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>101 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CodInterno                         Descrição ReceitaDespesa  \\\n",
       "0          87.0             Receita monitoramento              R   \n",
       "1          88.0         Receita venda de produtos              R   \n",
       "2         224.0                Receita facilities              R   \n",
       "3          90.0         Recebimento de empréstimo              R   \n",
       "4          91.0                   Juros Recebidos              R   \n",
       "..          ...                               ...            ...   \n",
       "110       267.0                         Antivírus              D   \n",
       "111       270.0            Receita escolta armada              R   \n",
       "112       272.0                      Cesta básica              D   \n",
       "113       276.0                   Acordo Judicial              D   \n",
       "125       256.0  Encargos/Tarifas Polícia Federal              D   \n",
       "\n",
       "                    Tipo  \n",
       "0               Entradas  \n",
       "1               Entradas  \n",
       "2               Entradas  \n",
       "3               Entradas  \n",
       "4               Entradas  \n",
       "..                   ...  \n",
       "110  Despesa Operacional  \n",
       "111             Entradas  \n",
       "112   Folha de Pagamento  \n",
       "113   Folha de Pagamento  \n",
       "125             Impostos  \n",
       "\n",
       "[101 rows x 4 columns]"
      ]
     },
     "execution_count": 300,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Contas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "metadata": {},
   "outputs": [],
   "source": [
    "Rubricas = pd.merge(Contas,Codigos,how='inner',on='CodInterno')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "metadata": {},
   "outputs": [],
   "source": [
    "Rubricas = Rubricas.rename(columns={'Descrição':'Nome','Tipo':'Classe'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "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>CodInterno</th>\n",
       "      <th>Nome</th>\n",
       "      <th>ReceitaDespesa</th>\n",
       "      <th>Classe</th>\n",
       "      <th>CentroResultados</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>87.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>87.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>129.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>87.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>217.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>87.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>218.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>87.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>R</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>216.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>276.0</td>\n",
       "      <td>Acordo Judicial</td>\n",
       "      <td>D</td>\n",
       "      <td>Folha de Pagamento</td>\n",
       "      <td>219.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>256.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>D</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>256.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>D</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>256.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>D</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>157.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>256.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>D</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>149.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>230 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CodInterno                              Nome ReceitaDespesa  \\\n",
       "0          87.0             Receita monitoramento              R   \n",
       "1          87.0             Receita monitoramento              R   \n",
       "2          87.0             Receita monitoramento              R   \n",
       "3          87.0             Receita monitoramento              R   \n",
       "4          87.0             Receita monitoramento              R   \n",
       "..          ...                               ...            ...   \n",
       "225       276.0                   Acordo Judicial              D   \n",
       "226       256.0  Encargos/Tarifas Polícia Federal              D   \n",
       "227       256.0  Encargos/Tarifas Polícia Federal              D   \n",
       "228       256.0  Encargos/Tarifas Polícia Federal              D   \n",
       "229       256.0  Encargos/Tarifas Polícia Federal              D   \n",
       "\n",
       "                 Classe  CentroResultados  \n",
       "0              Entradas              43.0  \n",
       "1              Entradas             129.0  \n",
       "2              Entradas             217.0  \n",
       "3              Entradas             218.0  \n",
       "4              Entradas             216.0  \n",
       "..                  ...               ...  \n",
       "225  Folha de Pagamento             219.0  \n",
       "226            Impostos             150.0  \n",
       "227            Impostos             153.0  \n",
       "228            Impostos             157.0  \n",
       "229            Impostos             149.0  \n",
       "\n",
       "[230 rows x 5 columns]"
      ]
     },
     "execution_count": 303,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Rubricas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 304,
   "metadata": {},
   "outputs": [],
   "source": [
    "Rubricas = Rubricas.filter(['CentroResultados','Nome','Classe','ReceitaDespesa'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 305,
   "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>CentroResultados</th>\n",
       "      <th>Nome</th>\n",
       "      <th>Classe</th>\n",
       "      <th>ReceitaDespesa</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>129.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>217.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>218.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>216.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>219.0</td>\n",
       "      <td>Acordo Judicial</td>\n",
       "      <td>Folha de Pagamento</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>150.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>153.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>157.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>149.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>230 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CentroResultados                              Nome              Classe  \\\n",
       "0                43.0             Receita monitoramento            Entradas   \n",
       "1               129.0             Receita monitoramento            Entradas   \n",
       "2               217.0             Receita monitoramento            Entradas   \n",
       "3               218.0             Receita monitoramento            Entradas   \n",
       "4               216.0             Receita monitoramento            Entradas   \n",
       "..                ...                               ...                 ...   \n",
       "225             219.0                   Acordo Judicial  Folha de Pagamento   \n",
       "226             150.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "227             153.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "228             157.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "229             149.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "\n",
       "    ReceitaDespesa  \n",
       "0                R  \n",
       "1                R  \n",
       "2                R  \n",
       "3                R  \n",
       "4                R  \n",
       "..             ...  \n",
       "225              D  \n",
       "226              D  \n",
       "227              D  \n",
       "228              D  \n",
       "229              D  \n",
       "\n",
       "[230 rows x 4 columns]"
      ]
     },
     "execution_count": 305,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Rubricas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "metadata": {},
   "outputs": [],
   "source": [
    "Classes = pd.read_csv('Classe.csv',sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 307,
   "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>CodClasse</th>\n",
       "      <th>Classe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Entradas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Administrativas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Despesa Operacional</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Despesas fixas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Financiamentos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Folha de Pagamento</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Fornecedores</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Impostos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Investimentos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Marketing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>Merc.Vendas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Outros</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CodClasse               Classe\n",
       "0           1             Entradas\n",
       "1           2      Administrativas\n",
       "2           3  Despesa Operacional\n",
       "3           4       Despesas fixas\n",
       "4           5       Financiamentos\n",
       "5           6   Folha de Pagamento\n",
       "6           7         Fornecedores\n",
       "7           8             Impostos\n",
       "8           9        Investimentos\n",
       "9          10            Marketing\n",
       "10         11          Merc.Vendas\n",
       "11         12               Outros"
      ]
     },
     "execution_count": 307,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "metadata": {},
   "outputs": [],
   "source": [
    "Rubricas = pd.merge(Rubricas,Classes,how='inner',on='Classe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "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>CentroResultados</th>\n",
       "      <th>Nome</th>\n",
       "      <th>Classe</th>\n",
       "      <th>ReceitaDespesa</th>\n",
       "      <th>CodClasse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>129.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>217.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>218.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>216.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>219.0</td>\n",
       "      <td>Acordo Judicial</td>\n",
       "      <td>Folha de Pagamento</td>\n",
       "      <td>D</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>150.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>153.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>157.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>149.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>230 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CentroResultados                              Nome              Classe  \\\n",
       "0                43.0             Receita monitoramento            Entradas   \n",
       "1               129.0             Receita monitoramento            Entradas   \n",
       "2               217.0             Receita monitoramento            Entradas   \n",
       "3               218.0             Receita monitoramento            Entradas   \n",
       "4               216.0             Receita monitoramento            Entradas   \n",
       "..                ...                               ...                 ...   \n",
       "225             219.0                   Acordo Judicial  Folha de Pagamento   \n",
       "226             150.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "227             153.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "228             157.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "229             149.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "\n",
       "    ReceitaDespesa  CodClasse  \n",
       "0                R          1  \n",
       "1                R          1  \n",
       "2                R          1  \n",
       "3                R          1  \n",
       "4                R          1  \n",
       "..             ...        ...  \n",
       "225              D          6  \n",
       "226              D          8  \n",
       "227              D          8  \n",
       "228              D          8  \n",
       "229              D          8  \n",
       "\n",
       "[230 rows x 5 columns]"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Rubricas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "metadata": {},
   "outputs": [],
   "source": [
    "Pe = pd.read_csv('AdPe.csv',sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "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>CentroResultados</th>\n",
       "      <th>Pe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>9856</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>10581</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>10582</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>10584</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>10585</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>210 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CentroResultados  Pe\n",
       "0                   7  10\n",
       "1                   8  10\n",
       "2                  10  30\n",
       "3                  14  10\n",
       "4                  16  10\n",
       "..                ...  ..\n",
       "205              9856  20\n",
       "206             10581  20\n",
       "207             10582  20\n",
       "208             10584  20\n",
       "209             10585  20\n",
       "\n",
       "[210 rows x 2 columns]"
      ]
     },
     "execution_count": 311,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Pe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 312,
   "metadata": {},
   "outputs": [],
   "source": [
    "Rubricas = pd.merge(Rubricas,Pe,how='inner',on='CentroResultados')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 313,
   "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>CentroResultados</th>\n",
       "      <th>Nome</th>\n",
       "      <th>Classe</th>\n",
       "      <th>ReceitaDespesa</th>\n",
       "      <th>CodClasse</th>\n",
       "      <th>Pe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>218.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>216.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>96.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>99.0</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>Entradas</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>219.0</td>\n",
       "      <td>Acordo Judicial</td>\n",
       "      <td>Folha de Pagamento</td>\n",
       "      <td>D</td>\n",
       "      <td>6</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>150.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>153.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>157.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>149.0</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>Impostos</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>192 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CentroResultados                              Nome              Classe  \\\n",
       "0                43.0             Receita monitoramento            Entradas   \n",
       "1               218.0             Receita monitoramento            Entradas   \n",
       "2               216.0             Receita monitoramento            Entradas   \n",
       "3                96.0             Receita monitoramento            Entradas   \n",
       "4                99.0             Receita monitoramento            Entradas   \n",
       "..                ...                               ...                 ...   \n",
       "187             219.0                   Acordo Judicial  Folha de Pagamento   \n",
       "188             150.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "189             153.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "190             157.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "191             149.0  Encargos/Tarifas Polícia Federal            Impostos   \n",
       "\n",
       "    ReceitaDespesa  CodClasse  Pe  \n",
       "0                R          1  10  \n",
       "1                R          1  10  \n",
       "2                R          1  10  \n",
       "3                R          1  10  \n",
       "4                R          1  10  \n",
       "..             ...        ...  ..  \n",
       "187              D          6  30  \n",
       "188              D          8  30  \n",
       "189              D          8  30  \n",
       "190              D          8  30  \n",
       "191              D          8  40  \n",
       "\n",
       "[192 rows x 6 columns]"
      ]
     },
     "execution_count": 313,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Rubricas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {},
   "outputs": [],
   "source": [
    "Receitas = Rubricas.query('ReceitaDespesa == \"R\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 315,
   "metadata": {},
   "outputs": [],
   "source": [
    "Receitas = Receitas.filter(['CentroResultados','Nome','CodClasse','ReceitaDespesa','Pe'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CentroResultados     int64\n",
       "Nome                object\n",
       "CodClasse            int64\n",
       "ReceitaDespesa      object\n",
       "Pe                   int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 316,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Receitas = Receitas.astype({'CentroResultados':'int64','Nome':'str','CodClasse':'int64','ReceitaDespesa':'str','Pe':'int64'})\n",
    "Receitas.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "metadata": {},
   "outputs": [],
   "source": [
    "Receitas = Receitas.rename(columns={'CentroResultados':'CodReceita','Nome':'Receita','ReceitaDespesa':'Tipo','Pe':'CodPE'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 318,
   "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>CodReceita</th>\n",
       "      <th>Receita</th>\n",
       "      <th>CodClasse</th>\n",
       "      <th>Tipo</th>\n",
       "      <th>CodPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>218</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>216</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>96</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>99</td>\n",
       "      <td>Receita monitoramento</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>47</td>\n",
       "      <td>Receita venda de produtos</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>76</td>\n",
       "      <td>Receita facilities</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>91</td>\n",
       "      <td>Receita facilities</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>92</td>\n",
       "      <td>Receita facilities</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>94</td>\n",
       "      <td>Receita facilities</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>227</td>\n",
       "      <td>Receita facilities</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>229</td>\n",
       "      <td>Receita facilities</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>450</td>\n",
       "      <td>Recebimento de empréstimo</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1571</td>\n",
       "      <td>Juros Recebidos</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>48</td>\n",
       "      <td>Outras Entradas</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>205</td>\n",
       "      <td>Outras Entradas</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>207</td>\n",
       "      <td>Receita limpeza e conservação</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>35</td>\n",
       "      <td>Receita backup de imagens (mensal)</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>85</td>\n",
       "      <td>Receita vigilância</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>587</td>\n",
       "      <td>Venda de ativo da empresa</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>54</td>\n",
       "      <td>Retorno de Investimentos</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>222</td>\n",
       "      <td>Retorno de Investimentos</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>7</td>\n",
       "      <td>Receita serviços técnicos</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>19</td>\n",
       "      <td>Receita GPRS</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>93</td>\n",
       "      <td>Receita locação de equipamentos</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>22</td>\n",
       "      <td>Receita manutenção prev. e reparativa</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>17</td>\n",
       "      <td>Receita portaria inteligente</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>97</td>\n",
       "      <td>Receita portaria inteligente</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>100</td>\n",
       "      <td>Receita portaria inteligente</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>16</td>\n",
       "      <td>Receita rastreamento</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>8</td>\n",
       "      <td>Receita monitoramento de imagens</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>34</td>\n",
       "      <td>Receita monitoramento de imagens</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>98</td>\n",
       "      <td>Receita monitoramento de imagens</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>14</td>\n",
       "      <td>Multa Contratual</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>165</td>\n",
       "      <td>Receita vigilância de eventos</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>166</td>\n",
       "      <td>Receita relógio de ponto eletrônico</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>182</td>\n",
       "      <td>Receita escolta armada</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     CodReceita                                Receita  CodClasse Tipo  CodPE\n",
       "0            43                  Receita monitoramento          1    R     10\n",
       "1           218                  Receita monitoramento          1    R     10\n",
       "2           216                  Receita monitoramento          1    R     10\n",
       "3            96                  Receita monitoramento          1    R     10\n",
       "4            99                  Receita monitoramento          1    R     10\n",
       "5            47              Receita venda de produtos          1    R     10\n",
       "6            76                     Receita facilities          1    R     10\n",
       "7            91                     Receita facilities          1    R     10\n",
       "8            92                     Receita facilities          1    R     10\n",
       "9            94                     Receita facilities          1    R     10\n",
       "10          227                     Receita facilities          1    R     10\n",
       "11          229                     Receita facilities          1    R     10\n",
       "12          450              Recebimento de empréstimo          1    R     10\n",
       "13         1571                        Juros Recebidos          1    R     10\n",
       "14           48                        Outras Entradas          1    R     10\n",
       "15          205                        Outras Entradas          1    R     30\n",
       "16          207          Receita limpeza e conservação          1    R     10\n",
       "20           35     Receita backup de imagens (mensal)          1    R     10\n",
       "27           85                     Receita vigilância          1    R     10\n",
       "30          587              Venda de ativo da empresa          1    R     10\n",
       "47           54               Retorno de Investimentos          1    R     40\n",
       "48          222               Retorno de Investimentos          1    R     10\n",
       "141           7              Receita serviços técnicos          1    R     10\n",
       "142          19                           Receita GPRS          1    R     10\n",
       "143          93        Receita locação de equipamentos          1    R     10\n",
       "144          22  Receita manutenção prev. e reparativa          1    R     10\n",
       "145          17           Receita portaria inteligente          1    R     10\n",
       "146          97           Receita portaria inteligente          1    R     10\n",
       "147         100           Receita portaria inteligente          1    R     10\n",
       "148          16                   Receita rastreamento          1    R     10\n",
       "149           8       Receita monitoramento de imagens          1    R     10\n",
       "150          34       Receita monitoramento de imagens          1    R     10\n",
       "151          98       Receita monitoramento de imagens          1    R     10\n",
       "159          14                       Multa Contratual          1    R     10\n",
       "179         165          Receita vigilância de eventos          1    R     10\n",
       "180         166    Receita relógio de ponto eletrônico          1    R     10\n",
       "185         182                 Receita escolta armada          1    R     10"
      ]
     },
     "execution_count": 318,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Receitas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "metadata": {},
   "outputs": [],
   "source": [
    "Gastos = Rubricas.query('ReceitaDespesa == \"D\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "metadata": {},
   "outputs": [],
   "source": [
    "Gastos = Gastos.filter(['CentroResultados','Nome','CodClasse','ReceitaDespesa','Pe'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 321,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CentroResultados     int64\n",
       "Nome                object\n",
       "CodClasse            int64\n",
       "ReceitaDespesa      object\n",
       "Pe                   int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 321,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Gastos = Gastos.astype({'CentroResultados':'int64','Nome':'str','CodClasse':'int64','ReceitaDespesa':'str','Pe':'int64'})\n",
    "Gastos.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "metadata": {},
   "outputs": [],
   "source": [
    "Gastos = Gastos.rename(columns={'CentroResultados':'CodGasto','Nome':'Gasto','ReceitaDespesa':'Tipo','Pe':'CodPE'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "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>CodGasto</th>\n",
       "      <th>Gasto</th>\n",
       "      <th>CodClasse</th>\n",
       "      <th>Tipo</th>\n",
       "      <th>CodPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>186</td>\n",
       "      <td>Folha de pagamento neoguard vigilância</td>\n",
       "      <td>6</td>\n",
       "      <td>D</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>95</td>\n",
       "      <td>Folha de pagamento neoguard vigilância</td>\n",
       "      <td>6</td>\n",
       "      <td>D</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>87</td>\n",
       "      <td>GRU</td>\n",
       "      <td>8</td>\n",
       "      <td>D</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>88</td>\n",
       "      <td>Desp. material de higiene e limpeza</td>\n",
       "      <td>3</td>\n",
       "      <td>D</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>112</td>\n",
       "      <td>Desp. material de higiene e limpeza</td>\n",
       "      <td>3</td>\n",
       "      <td>D</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>219</td>\n",
       "      <td>Acordo Judicial</td>\n",
       "      <td>6</td>\n",
       "      <td>D</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>150</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>8</td>\n",
       "      <td>D</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>153</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>8</td>\n",
       "      <td>D</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>157</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>8</td>\n",
       "      <td>D</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>149</td>\n",
       "      <td>Encargos/Tarifas Polícia Federal</td>\n",
       "      <td>8</td>\n",
       "      <td>D</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>155 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CodGasto                                   Gasto  CodClasse Tipo  CodPE\n",
       "17        186  Folha de pagamento neoguard vigilância          6    D     20\n",
       "18         95  Folha de pagamento neoguard vigilância          6    D     20\n",
       "19         87                                     GRU          8    D     20\n",
       "21         88     Desp. material de higiene e limpeza          3    D     30\n",
       "22        112     Desp. material de higiene e limpeza          3    D     30\n",
       "..        ...                                     ...        ...  ...    ...\n",
       "187       219                         Acordo Judicial          6    D     30\n",
       "188       150        Encargos/Tarifas Polícia Federal          8    D     30\n",
       "189       153        Encargos/Tarifas Polícia Federal          8    D     30\n",
       "190       157        Encargos/Tarifas Polícia Federal          8    D     30\n",
       "191       149        Encargos/Tarifas Polícia Federal          8    D     40\n",
       "\n",
       "[155 rows x 5 columns]"
      ]
     },
     "execution_count": 323,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Gastos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "metadata": {},
   "outputs": [],
   "source": [
    "Receitas.to_csv(\"Receita.csv\",encoding='utf-8',index=False)\n",
    "Gastos.to_csv(\"Gasto.csv\",encoding='utf-8',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}