Ficha_3_otm_gti.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "09231fc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importação das bibliotecas a serem utilizadas durante todo o processo\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "import time\n",
    "import math\n",
    "import random\n",
    "import sys\n",
    "import pytz\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "91d2b4f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Definicção de qual loja e período irá rodar o script\n",
    "\n",
    "loja = 1\n",
    "periodo = 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c41e9547",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\3957445940.py:4: DtypeWarning: Columns (22,28) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}\\\\loja_{loja}_tabela_2_1_p{periodo}_v1.csv', encoding='utf-8')\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>CHV_DOC</th>\n",
       "      <th>DATA</th>\n",
       "      <th>CFOP</th>\n",
       "      <th>NUM_ITEM</th>\n",
       "      <th>cod novo</th>\n",
       "      <th>tipo</th>\n",
       "      <th>COD_ITEM</th>\n",
       "      <th>IND_OPER</th>\n",
       "      <th>SUB_TIPO</th>\n",
       "      <th>...</th>\n",
       "      <th>DESCRICAO</th>\n",
       "      <th>CODIGO_BARRA</th>\n",
       "      <th>UNIDADE</th>\n",
       "      <th>N C M</th>\n",
       "      <th>CEST</th>\n",
       "      <th>CNPJ EMITENTE</th>\n",
       "      <th>CNPJ DESTINATARIO</th>\n",
       "      <th>Valor Base Cálculo ICMS Substituição Tributária</th>\n",
       "      <th>vBCST</th>\n",
       "      <th>FONTE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>35191003129126000159550020000027291157936550</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>1411</td>\n",
       "      <td>1</td>\n",
       "      <td>94771</td>\n",
       "      <td>grupo</td>\n",
       "      <td>94771</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>Nimesulida Novaq 100mg 12S</td>\n",
       "      <td>SEM GTIN</td>\n",
       "      <td>UN</td>\n",
       "      <td>30049079.0</td>\n",
       "      <td>1300200.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>entrada_oprprp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>35191003129126000159550020000027301158021920</td>\n",
       "      <td>2019-10-02</td>\n",
       "      <td>1411</td>\n",
       "      <td>1</td>\n",
       "      <td>148098</td>\n",
       "      <td>grupo</td>\n",
       "      <td>148098</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>Kit Concare Manicure</td>\n",
       "      <td>SEM GTIN</td>\n",
       "      <td>UN</td>\n",
       "      <td>82142000.0</td>\n",
       "      <td>2802600.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>entrada_oprprp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>35191003129126000159550020000027311158028019</td>\n",
       "      <td>2019-10-02</td>\n",
       "      <td>1411</td>\n",
       "      <td>1</td>\n",
       "      <td>155283</td>\n",
       "      <td>grupo</td>\n",
       "      <td>155283</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>Creme Prev Assadur 45g Le Blue</td>\n",
       "      <td>SEM GTIN</td>\n",
       "      <td>UN</td>\n",
       "      <td>33049910.0</td>\n",
       "      <td>2800800.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>entrada_oprprp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>35191003129126000159550020000027311158028019</td>\n",
       "      <td>2019-10-02</td>\n",
       "      <td>1411</td>\n",
       "      <td>2</td>\n",
       "      <td>155283</td>\n",
       "      <td>grupo</td>\n",
       "      <td>155283</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>Creme Prev Assadur 45g Le Blue</td>\n",
       "      <td>SEM GTIN</td>\n",
       "      <td>UN</td>\n",
       "      <td>33049910.0</td>\n",
       "      <td>2800800.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>entrada_oprprp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>35191003129126000159550020000027321158122292</td>\n",
       "      <td>2019-10-03</td>\n",
       "      <td>1411</td>\n",
       "      <td>1</td>\n",
       "      <td>78550</td>\n",
       "      <td>grupo</td>\n",
       "      <td>78550</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>Ibuprofeno Prati 600mg 20S</td>\n",
       "      <td>SEM GTIN</td>\n",
       "      <td>UN</td>\n",
       "      <td>30049029.0</td>\n",
       "      <td>1300200.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>entrada_oprprp</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493477</th>\n",
       "      <td>493477</td>\n",
       "      <td>35200503129126000159550020000031271171064100</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>5411</td>\n",
       "      <td>1</td>\n",
       "      <td>211409</td>\n",
       "      <td>grupo</td>\n",
       "      <td>211409</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>DULCOGAS 125MG CAP GEL 10 S</td>\n",
       "      <td>7891058022495.0</td>\n",
       "      <td>UN</td>\n",
       "      <td>30049099.0</td>\n",
       "      <td>1300301.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>saida_nfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493478</th>\n",
       "      <td>493478</td>\n",
       "      <td>35200703129126000159550020000032161174195310</td>\n",
       "      <td>2020-07-04</td>\n",
       "      <td>5411</td>\n",
       "      <td>1</td>\n",
       "      <td>224790</td>\n",
       "      <td>grupo</td>\n",
       "      <td>224790</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>AGUA MICELAR LE BLUE 150ML</td>\n",
       "      <td>7898623355309.0</td>\n",
       "      <td>UN</td>\n",
       "      <td>33049990.0</td>\n",
       "      <td>2001500.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>saida_nfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493479</th>\n",
       "      <td>493479</td>\n",
       "      <td>35200703129126000159550020000032171174195334</td>\n",
       "      <td>2020-07-04</td>\n",
       "      <td>5411</td>\n",
       "      <td>1</td>\n",
       "      <td>224790</td>\n",
       "      <td>grupo</td>\n",
       "      <td>224790</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>AGUA MICELAR LE BLUE 150ML</td>\n",
       "      <td>7898623355309.0</td>\n",
       "      <td>UN</td>\n",
       "      <td>33049990.0</td>\n",
       "      <td>2001500.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>saida_nfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493480</th>\n",
       "      <td>493480</td>\n",
       "      <td>35201003129126000159550020000033711179926296</td>\n",
       "      <td>2020-10-08</td>\n",
       "      <td>5411</td>\n",
       "      <td>1</td>\n",
       "      <td>44831</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44831</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>ALDIJET AMP 1ML</td>\n",
       "      <td>7899420500732.0</td>\n",
       "      <td>UN</td>\n",
       "      <td>30066000.0</td>\n",
       "      <td>1300500.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>5.651966e+12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>saida_nfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493481</th>\n",
       "      <td>493481</td>\n",
       "      <td>35201103129126000159550030000000121173973232</td>\n",
       "      <td>2020-11-04</td>\n",
       "      <td>5411</td>\n",
       "      <td>1</td>\n",
       "      <td>226571</td>\n",
       "      <td>grupo</td>\n",
       "      <td>226571</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>Simeticona Conlife Gts 15ml</td>\n",
       "      <td>7898623354258.0</td>\n",
       "      <td>UN</td>\n",
       "      <td>30049099.0</td>\n",
       "      <td>1300301.0</td>\n",
       "      <td>3.129126e+12</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>saida_nfe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>493482 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unnamed: 0                                       CHV_DOC        DATA  \\\n",
       "0                0  35191003129126000159550020000027291157936550  2019-10-01   \n",
       "1                1  35191003129126000159550020000027301158021920  2019-10-02   \n",
       "2                2  35191003129126000159550020000027311158028019  2019-10-02   \n",
       "3                3  35191003129126000159550020000027311158028019  2019-10-02   \n",
       "4                4  35191003129126000159550020000027321158122292  2019-10-03   \n",
       "...            ...                                           ...         ...   \n",
       "493477      493477  35200503129126000159550020000031271171064100  2020-05-09   \n",
       "493478      493478  35200703129126000159550020000032161174195310  2020-07-04   \n",
       "493479      493479  35200703129126000159550020000032171174195334  2020-07-04   \n",
       "493480      493480  35201003129126000159550020000033711179926296  2020-10-08   \n",
       "493481      493481  35201103129126000159550030000000121173973232  2020-11-04   \n",
       "\n",
       "        CFOP  NUM_ITEM cod novo   tipo  COD_ITEM  IND_OPER  SUB_TIPO  ...  \\\n",
       "0       1411         1    94771  grupo     94771         0        -1  ...   \n",
       "1       1411         1   148098  grupo    148098         0        -1  ...   \n",
       "2       1411         1   155283  grupo    155283         0        -1  ...   \n",
       "3       1411         2   155283  grupo    155283         0        -1  ...   \n",
       "4       1411         1    78550  grupo     78550         0        -1  ...   \n",
       "...      ...       ...      ...    ...       ...       ...       ...  ...   \n",
       "493477  5411         1   211409  grupo    211409         1        -1  ...   \n",
       "493478  5411         1   224790  grupo    224790         1        -1  ...   \n",
       "493479  5411         1   224790  grupo    224790         1        -1  ...   \n",
       "493480  5411         1    44831    NaN     44831         1        -1  ...   \n",
       "493481  5411         1   226571  grupo    226571         1        -1  ...   \n",
       "\n",
       "                             DESCRICAO     CODIGO_BARRA  UNIDADE       N C M  \\\n",
       "0           Nimesulida Novaq 100mg 12S   SEM GTIN         UN      30049079.0   \n",
       "1                 Kit Concare Manicure   SEM GTIN         UN      82142000.0   \n",
       "2       Creme Prev Assadur 45g Le Blue   SEM GTIN         UN      33049910.0   \n",
       "3       Creme Prev Assadur 45g Le Blue   SEM GTIN         UN      33049910.0   \n",
       "4           Ibuprofeno Prati 600mg 20S   SEM GTIN         UN      30049029.0   \n",
       "...                                ...              ...      ...         ...   \n",
       "493477     DULCOGAS 125MG CAP GEL 10 S  7891058022495.0   UN      30049099.0   \n",
       "493478      AGUA MICELAR LE BLUE 150ML  7898623355309.0   UN      33049990.0   \n",
       "493479      AGUA MICELAR LE BLUE 150ML  7898623355309.0   UN      33049990.0   \n",
       "493480                 ALDIJET AMP 1ML  7899420500732.0   UN      30066000.0   \n",
       "493481     Simeticona Conlife Gts 15ml  7898623354258.0   UN      30049099.0   \n",
       "\n",
       "             CEST  CNPJ EMITENTE  CNPJ DESTINATARIO  \\\n",
       "0       1300200.0   3.129126e+12       0.000000e+00   \n",
       "1       2802600.0   3.129126e+12       0.000000e+00   \n",
       "2       2800800.0   3.129126e+12       0.000000e+00   \n",
       "3       2800800.0   3.129126e+12       0.000000e+00   \n",
       "4       1300200.0   3.129126e+12       0.000000e+00   \n",
       "...           ...            ...                ...   \n",
       "493477  1300301.0   3.129126e+12       2.784996e+13   \n",
       "493478  2001500.0   3.129126e+12       2.784996e+13   \n",
       "493479  2001500.0   3.129126e+12       2.784996e+13   \n",
       "493480  1300500.0   3.129126e+12       5.651966e+12   \n",
       "493481  1300301.0   3.129126e+12       2.784996e+13   \n",
       "\n",
       "        Valor Base Cálculo ICMS Substituição Tributária  vBCST           FONTE  \n",
       "0                                                   NaN    0.0  entrada_oprprp  \n",
       "1                                                   NaN    0.0  entrada_oprprp  \n",
       "2                                                   NaN    0.0  entrada_oprprp  \n",
       "3                                                   NaN    0.0  entrada_oprprp  \n",
       "4                                                   NaN    0.0  entrada_oprprp  \n",
       "...                                                 ...    ...             ...  \n",
       "493477                                              NaN    0.0       saida_nfe  \n",
       "493478                                              NaN    0.0       saida_nfe  \n",
       "493479                                              NaN    0.0       saida_nfe  \n",
       "493480                                              NaN    0.0       saida_nfe  \n",
       "493481                                              NaN    0.0       saida_nfe  \n",
       "\n",
       "[493482 rows x 31 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Leitura do arquivo da tabela 2\n",
    "\n",
    "start = time.time()\n",
    "df = pd.read_csv(f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}\\\\loja_{loja}_tabela_2_1_p{periodo}_v1.csv', encoding='utf-8')\n",
    "df['cod novo'] = df['cod novo'].astype(str)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a3cdb3c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calcular_ressarcimento(df):\n",
    "    '''\n",
    "    Função para cálculo do valor de ressarcimento\n",
    "    para a loja e de outras informações para o\n",
    "    arquivo TXT final\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    df = df.drop('Unnamed: 0', axis=1)\n",
    "    df = df.sort_values(by=['cod novo', 'DATA', 'IND_OPER', 'SUB_TIPO'], \n",
    "                               ascending=[True, True, True, True])\n",
    "    \n",
    "    # Criação de dataset para cálculo do ICMS inicial\n",
    "\n",
    "    data = df[['cod novo', 'DATA', 'QTD_CAT', 'IND_OPER']]\n",
    "    \n",
    "    # Transformação da coluna 'DATA' para o tipo correto\n",
    "    data['DATA'] = pd.to_datetime(data['DATA'], format='%Y-%m-%d')\n",
    "    \n",
    "    # Ordenação do dataset para cálculo correto do ICMS inicial\n",
    "\n",
    "    data['IND_OPER'] = data['IND_OPER'].astype(int)\n",
    "    data = data.sort_values(by=['cod novo','DATA', 'IND_OPER'])\n",
    "    \n",
    "    # Cáculo dos saldos das operações para definição da Quantidade Inicial\n",
    "\n",
    "    data['OPER'] = np.where(data['IND_OPER'] == 0, data['QTD_CAT'], -data['QTD_CAT'])\n",
    "    data['SALDO'] = data.groupby('cod novo')['OPER'].cumsum()\n",
    "    \n",
    "    # Criação da coluna para as informações da Quantidade Inicial\n",
    "\n",
    "    min_saldo = data.groupby('cod novo')['SALDO'].transform('min')\n",
    "\n",
    "    # Set 'QTD_INI' to the minimum 'SALDO' value if it's less than or equal to 0,\n",
    "    # otherwise set it to 0\n",
    "    data['QTD_INI'] = np.where(min_saldo <= 0, min_saldo.abs(), 0)\n",
    "\n",
    "    # Get the first rows within each group\n",
    "    first_rows = data.groupby('cod novo').head(1)\n",
    "\n",
    "    # Set 'QTD_INI' to NaN for all rows except the last rows within each group\n",
    "    data['QTD_INI'] = np.where(data.index.isin(first_rows.index), data['QTD_INI'], np.nan)\n",
    "\n",
    "    # Update 'QTD_INI' with calculated values for the last rows\n",
    "    data.loc[first_rows.index, 'QTD_INI'] = first_rows['QTD_INI']\n",
    "    \n",
    "    # Cálculo do ICMS inicial \n",
    "\n",
    "    data['SUB_TIPO'] = df['SUB_TIPO']\n",
    "    data['ICMS_TOT_0'] = df['ICMS_TOT']\n",
    "    data['VALOR_UNIT'] = np.where((data['SUB_TIPO'] == 1) & (data['IND_OPER'] == 0),\n",
    "                                  data['ICMS_TOT_0'] / data['QTD_CAT'],\n",
    "                                  np.nan)\n",
    "\n",
    "    grouped_data = data.groupby('cod novo').agg({\n",
    "        'VALOR_UNIT': 'mean',\n",
    "        'QTD_INI': 'first'\n",
    "    })\n",
    "\n",
    "    # Vectorized calculation for icms_init\n",
    "    grouped_data['ICMS_INI'] = grouped_data['VALOR_UNIT'] * (1 - 0.3 * np.random.random(size=len(grouped_data))) * grouped_data['QTD_INI']\n",
    "    data = data.merge(grouped_data[['ICMS_INI']], how='left', left_on='cod novo', right_index=True)\n",
    "\n",
    "    mask = data.duplicated(subset='cod novo', keep='first')\n",
    "\n",
    "    # Assign 'ICMS_INI' values to NaN where mask is True, indicating it's not the first row for the 'COD_ITEM'\n",
    "    data['ICMS_INI'] = np.where(mask, np.nan, data['ICMS_INI'])\n",
    "    \n",
    "    tabela_2 = df[['CHV_DOC', 'DATA', 'CFOP', 'NUM_ITEM', 'cod novo', 'IND_OPER', 'SUB_TIPO', 'QTD_CAT', 'ALIQUOTA', 'FONTE']]\n",
    "    \n",
    "    tabela_2['DATA'] = pd.to_datetime(tabela_2['DATA'], format='%Y-%m-%d')\n",
    "    tabela_2 = tabela_2.sort_values(by=['cod novo', 'DATA', 'IND_OPER', 'SUB_TIPO'], \n",
    "                               ascending=[True, True, True, True])\n",
    "    \n",
    "    tabela_2['QTD_INI'] = data['QTD_INI']\n",
    "    tabela_2['ICMS_INI'] = data['ICMS_INI']\n",
    "    tabela_2['ICMS_INI'] = np.where((tabela_2['QTD_INI'] == 0) | (tabela_2['ICMS_INI'].isnull() & tabela_2['QTD_INI'].notnull()),\n",
    "                                    0,\n",
    "                                    tabela_2['ICMS_INI'])\n",
    "    tabela_2 = tabela_2[['CHV_DOC', 'DATA', 'CFOP', 'NUM_ITEM', 'cod novo', 'IND_OPER', \n",
    "                        'SUB_TIPO', 'QTD_CAT','QTD_INI','ICMS_INI', 'ALIQUOTA', 'FONTE']]\n",
    "    \n",
    "    mask_condition1 = (tabela_2['IND_OPER'] == 0) & (tabela_2['SUB_TIPO'] == 1)\n",
    "    mask_condition2 = (tabela_2['IND_OPER'] != 0) & (tabela_2['SUB_TIPO'] == -1)\n",
    "\n",
    "    qtd_ent1_devolv_ent = list(np.where(mask_condition1, tabela_2['QTD_CAT'], 0) + np.where(mask_condition2, -tabela_2['QTD_CAT'], 0))\n",
    "    tabela_2['QTD_ent1_devolv_ent'] = qtd_ent1_devolv_ent\n",
    "    \n",
    "    tabela_2['ICMS_TOT'] = df['ICMS_TOT']\n",
    "    tabela_2['ICMS_TOT'] = tabela_2['ICMS_TOT'].fillna(0)\n",
    "    \n",
    "    mask = tabela_2['QTD_ent1_devolv_ent'] > 0\n",
    "\n",
    "    tabela_2['ICMS_TOT_ent_unit'] = np.where(mask, tabela_2['ICMS_TOT'] / tabela_2['QTD_ent1_devolv_ent'], 0)\n",
    "    \n",
    "    tabela_2['ULT_ICMS_TOT_ent_unit'] = np.nan\n",
    "\n",
    "    # Calculate the ULT_ICMS_TOT_ent_unit using vectorized operations\n",
    "    values = np.where(\n",
    "        (tabela_2['QTD_INI'] != 0) & \n",
    "        (tabela_2['cod novo'] == tabela_2['cod novo'].shift()) & \n",
    "        (tabela_2['QTD_ent1_devolv_ent'].shift() > 0),\n",
    "        tabela_2['ICMS_TOT_ent_unit'].shift(),\n",
    "        np.where(\n",
    "            (tabela_2['QTD_INI'] == 0),\n",
    "            0.01,\n",
    "            tabela_2['ICMS_INI'] / tabela_2['QTD_INI']\n",
    "        )\n",
    "    )\n",
    "    \n",
    "    tabela_2['tipo'] = df['tipo']\n",
    "    \n",
    "    # Assign values to the 'ULT_ICMS_TOT_ent_unit' column\n",
    "    tabela_2['ULT_ICMS_TOT_ent_unit'] = values\n",
    "\n",
    "    # Forward fill NaN values in 'ULT_ICMS_TOT_ent_unit' column\n",
    "    tabela_2['ULT_ICMS_TOT_ent_unit'] = tabela_2['ULT_ICMS_TOT_ent_unit'].ffill()\n",
    "    \n",
    "    mask_condition1 = (tabela_2['IND_OPER'] == 1) & (tabela_2['SUB_TIPO'] == 1)\n",
    "    mask_condition2 = (tabela_2['IND_OPER'] != 1) & (tabela_2['SUB_TIPO'] == -1)\n",
    "\n",
    "    qtd_saida_1_devolv_saida_t = np.where(mask_condition1, tabela_2['QTD_CAT'], 0) + np.where(mask_condition2, -tabela_2['QTD_CAT'], 0)\n",
    "\n",
    "    tabela_2['qtd_saida_1_devolv_saida'] = qtd_saida_1_devolv_saida_t\n",
    "    \n",
    "    tabela_2['QTD_SALDO'] = tabela_2.groupby('cod novo').apply(lambda x: (\n",
    "    x['QTD_INI'].iloc[0] + (x['QTD_ent1_devolv_ent'] - x['qtd_saida_1_devolv_saida']).cumsum())).reset_index(level=0, drop=True)\n",
    "    \n",
    "    tabela_2['REF'] = tabela_2['DATA'].astype(str)\n",
    "    tabela_2['REF'] = [datetime.strptime(x, '%Y-%m-%d').strftime('%m-%Y') for x in tabela_2['REF']]\n",
    "\n",
    "    # Definição do primeiro valor de ICMS_SAIDA_UNI\n",
    "    icms_saida_uni = [] \n",
    "\n",
    "    # Definição do primeiro valor de ULT_ICMS_SAIDA_UNI\n",
    "    ult_icms_saida_uni = [] \n",
    "\n",
    "    # Definição do primeiro valor de ICMS_SAIDA\n",
    "    icms_saida = [] \n",
    "\n",
    "    # Definição do primeiro valor de ICMS_TOT_SALDO\n",
    "    icms_tot_saldo = [] \n",
    "\n",
    "    # Definição do primeiro valor de ICMS_TOT_1\n",
    "    icms_tot_1 = []\n",
    "\n",
    "    # Definição de todos os outros valores para cada campo acima\n",
    "    for code in tabela_2['cod novo'].unique():\n",
    "\n",
    "        icms_saida_uni_int = []\n",
    "        ult_icms_saida_uni_int = []\n",
    "        icms_saida_int = []\n",
    "        icms_tot_saldo_int = []\n",
    "        icms_tot_1_int = []\n",
    "        qtd_saida_1_devolv_saida = list(tabela_2[tabela_2['cod novo'] == code]['qtd_saida_1_devolv_saida'])\n",
    "        qtd_saldo = list(tabela_2[tabela_2['cod novo'] == code]['QTD_SALDO'])\n",
    "        qtd_cat = list(tabela_2[tabela_2['cod novo'] == code]['QTD_CAT'])\n",
    "        ref = list(tabela_2[tabela_2['cod novo'] == code]['REF'])\n",
    "\n",
    "        for i,row in tabela_2[tabela_2['cod novo']==code].reset_index(drop=True).iterrows():\n",
    "\n",
    "        # ICMS_SAIDA_UNI\n",
    "\n",
    "            if i == 0:\n",
    "                if qtd_saida_1_devolv_saida[0] > 0:\n",
    "                    if row['QTD_INI'] == 0:\n",
    "                        icms_saida_uni_int.append(0)\n",
    "                    else:  \n",
    "                        icms_saida_uni_int.append(row['ICMS_INI'] / row['QTD_INI'])\n",
    "                else:\n",
    "                    icms_saida_uni_int.append(0)\n",
    "\n",
    "            else:\n",
    "                if (qtd_saida_1_devolv_saida[i] != 0) and (qtd_saldo[i-1] != 0):  \n",
    "                    icms_saida_uni_int.append(icms_tot_saldo_int[i-1] / qtd_saldo[i-1])               \n",
    "                else:      \n",
    "                    icms_saida_uni_int.append(0)\n",
    "\n",
    "        # ULT_ICMS_SAIDA_UNI\n",
    "            if i== 0:\n",
    "                if row['QTD_INI'] == 0:\n",
    "                    ult_icms_saida_uni_int.append(0.01)\n",
    "                else:           \n",
    "                    ult_icms_saida_uni_int.append(\n",
    "                        row['ICMS_INI'] / row['QTD_INI'] if row['QTD_INI'] != 0 else float(\"nan\"))\n",
    "            else:\n",
    "                if qtd_saida_1_devolv_saida[i-1] > 0:\n",
    "                    ult_icms_saida_uni_int.append(icms_saida_uni_int[i-1])\n",
    "                else:\n",
    "                    ult_icms_saida_uni_int.append(ult_icms_saida_uni_int[i-1])\n",
    "\n",
    "\n",
    "        # ICMS_SAIDA\n",
    "            if qtd_saida_1_devolv_saida[i] < 0:\n",
    "                icms_saida_int.append(qtd_saida_1_devolv_saida[i]*ult_icms_saida_uni_int[i])\n",
    "            else:\n",
    "                icms_saida_int.append(icms_saida_uni_int[i]*qtd_saida_1_devolv_saida[i])\n",
    "\n",
    "        # ICMS_TOT_1\n",
    "            if i == 0:\n",
    "                if (row['QTD_ent1_devolv_ent'] < 0) & (row['QTD_CAT'] == row['QTD_INI']) :\n",
    "                    icms_tot_1_int.append(-row['ICMS_INI'])\n",
    "                else:\n",
    "                    icms_tot_1_int.append(row['ICMS_TOT'])\n",
    "            else:\n",
    "                if row['QTD_ent1_devolv_ent'] < 0:\n",
    "                    if (qtd_cat[i] == qtd_saldo[i-1]):\n",
    "                        icms_tot_1_int.append(-1*icms_tot_saldo_int[i-1])\n",
    "                    else:\n",
    "                        icms_tot_1_int.append(max(row['QTD_ent1_devolv_ent'] * row['ULT_ICMS_TOT_ent_unit'], -1*icms_tot_saldo_int[i-1]))\n",
    "                else:\n",
    "                    icms_tot_1_int.append(row['ICMS_TOT']) \n",
    "\n",
    "        # ICMS_TOT_SALDO\n",
    "            if i == 0:\n",
    "                icms_tot_saldo_int.append(row['ICMS_INI'] + icms_tot_1_int[i] - icms_saida_int[i])\n",
    "            else:\n",
    "                icms_tot_saldo_int.append(icms_tot_saldo_int[i-1] + icms_tot_1_int[i] - icms_saida_int[i])\n",
    "\n",
    "\n",
    "        icms_saida_uni += icms_saida_uni_int\n",
    "        ult_icms_saida_uni += ult_icms_saida_uni_int\n",
    "        icms_saida += icms_saida_int\n",
    "        icms_tot_saldo += icms_tot_saldo_int\n",
    "        icms_tot_1 += icms_tot_1_int\n",
    "\n",
    "    tabela_2['ICMS_SAIDA_UNI'] = icms_saida_uni\n",
    "    tabela_2['ULT_ICMS_SAIDA_UNI'] = ult_icms_saida_uni\n",
    "    tabela_2['ICMS_SAIDA'] = icms_saida\n",
    "    tabela_2['ICMS_TOT_SALDO'] = icms_tot_saldo\n",
    "    tabela_2['ICMS_TOT_SALDO'] = np.where((tabela_2['ICMS_TOT_SALDO'] < 0.01) & (tabela_2['ICMS_TOT_SALDO'] > -0.01),\n",
    "                                         0,\n",
    "                                         tabela_2['ICMS_TOT_SALDO'])\n",
    "    tabela_2['ICMS_TOT_1'] = icms_tot_1\n",
    "    \n",
    "    tabela_2['ICMS_TOT_PCAT'] = np.where((tabela_2['qtd_saida_1_devolv_saida'] < 0),\n",
    "                                     np.abs(tabela_2['ICMS_SAIDA_UNI']),\n",
    "                                     np.where((tabela_2['QTD_ent1_devolv_ent'] != 0),\n",
    "                                              np.abs(tabela_2['ICMS_TOT_1']),\n",
    "                                              np.nan))\n",
    "    \n",
    "    if tabela_2[tabela_2['ICMS_TOT_PCAT'] < 0].shape[0] > 0:\n",
    "        mensagem('EXISTE ICMS TOT PCAT NEGATIVO. FAVOR CHECAR!!')\n",
    "        print(mensagem)\n",
    "    \n",
    "    tabela_2['VLR_CONF_0'] = df['VL_CONFR_0']\n",
    "    \n",
    "    mask = tabela_2['qtd_saida_1_devolv_saida'] > 0\n",
    "    vlr_confr_unit = np.where(mask, tabela_2['VLR_CONF_0']/tabela_2['qtd_saida_1_devolv_saida'], np.nan)\n",
    "\n",
    "    tabela_2['VLR_CONFR_UNIT'] = vlr_confr_unit\n",
    "    \n",
    "    ult_vlr_confr_unit = [0]\n",
    "    for i in range(1, tabela_2.shape[0]):\n",
    "        if tabela_2.iloc[i]['cod novo'] != tabela_2.iloc[i-1]['cod novo']:\n",
    "            ult_vlr_confr_unit.append(0)\n",
    "        else:\n",
    "            if math.isnan(tabela_2.iloc[i-1]['VLR_CONFR_UNIT']):\n",
    "                ult_vlr_confr_unit.append(ult_vlr_confr_unit[i-1])\n",
    "            else:\n",
    "                ult_vlr_confr_unit.append(tabela_2.iloc[i-1]['VLR_CONFR_UNIT'])\n",
    "\n",
    "    tabela_2['ULT_VLR_CONFR_UNIT'] = ult_vlr_confr_unit\n",
    "    \n",
    "    tabela_2['VLR_CONFR_1'] = np.where(tabela_2['qtd_saida_1_devolv_saida'] > 0,\n",
    "                                   tabela_2['VLR_CONF_0'],\n",
    "                                   np.where(tabela_2['qtd_saida_1_devolv_saida'] < 0,\n",
    "                                            tabela_2['ULT_VLR_CONFR_UNIT'] * tabela_2['qtd_saida_1_devolv_saida'],\n",
    "                                            0))\n",
    "    \n",
    "    tabela_2['VLR_CONFR_1'] = np.where(tabela_2['VLR_CONFR_1'] <= 0.01,\n",
    "                                              0.01,\n",
    "                                              tabela_2['VLR_CONFR_1'])\n",
    "    \n",
    "    tabela_2['COD_LEGAL'] = df['COD_LEGAL']\n",
    "    \n",
    "    mask = np.abs(tabela_2['ICMS_SAIDA']) > np.abs(tabela_2['VLR_CONFR_1'])\n",
    "    vlr_ressarc = np.where(tabela_2['COD_LEGAL'] == 1, (np.where(mask, tabela_2['ICMS_SAIDA']-tabela_2['VLR_CONFR_1'], 0)),0)\n",
    "\n",
    "    tabela_2['VLR_RESSARCIMENTO'] = vlr_ressarc\n",
    "    \n",
    "    mask = np.abs(tabela_2['ICMS_SAIDA']) < np.abs(tabela_2['VLR_CONFR_1'])\n",
    "    vlr_compl = np.where(tabela_2['COD_LEGAL'] == 1, np.where(mask, -tabela_2['ICMS_SAIDA']+tabela_2['VLR_CONFR_1'], 0),0)\n",
    "\n",
    "    tabela_2['VLR_COMPLEMENTO'] = vlr_compl\n",
    "    \n",
    "    tabela_2['COD_LEGAL_PCAT'] = np.where((tabela_2['COD_LEGAL'] == 1) & (tabela_2['VLR_RESSARCIMENTO'] != 0) & (tabela_2['tipo'] != 'outros2') & (tabela_2['ALIQUOTA'] != 0), 1, 0)\n",
    "    tabela_2.loc[pd.isna(tabela_2['COD_LEGAL']), 'COD_LEGAL_PCAT'] = np.nan\n",
    "    \n",
    "    tabela_2['VLR_CONFR_PCAT'] = np.where(tabela_2['COD_LEGAL_PCAT'] == 1, np.abs(tabela_2['VLR_CONFR_1']), np.nan)\n",
    "    if tabela_2[(tabela_2['COD_LEGAL'] == 1) & (tabela_2['VLR_CONFR_PCAT'] <= 0)].shape[0] > 0:\n",
    "        mensagem = 'Há valores de confronto inconsistentes. Favor checar.'\n",
    "        print(mensagem)\n",
    "        with open(os.path.join(diretorio_principal, f'Error_loja_{loja}_p{periodo}'), 'w') as arquivo:\n",
    "            arquivo.write(mensagem)\n",
    "        sys.exit()\n",
    "    \n",
    "    mask = (tabela_2['cod novo'].shift(-1) != tabela_2['cod novo']) | (tabela_2['REF'].shift(-1) != tabela_2['REF'])\n",
    "    saldo_final_mes_qtd = np.where(mask, tabela_2['QTD_SALDO'], np.nan)\n",
    "\n",
    "    tabela_2['SALDO_FINAL_MES_QTD'] = saldo_final_mes_qtd\n",
    "    \n",
    "    mask = (tabela_2['cod novo'].shift(-1) != tabela_2['cod novo']) | (tabela_2['REF'].shift(-1) != tabela_2['REF'])\n",
    "    saldo_final_mes_icms = np.where(mask, tabela_2['ICMS_TOT_SALDO'], np.nan)\n",
    "\n",
    "    tabela_2['SALDO_FINAL_MES_ICMS'] = saldo_final_mes_icms\n",
    "    \n",
    "    tabela_2['VALOR'] = df['VALOR']\n",
    "    tabela_2['Valor Base Cálculo ICMS ST Retido Operação Anterior'] = df['Valor Base Cálculo ICMS ST Retido Operação Anterior']\n",
    "    tabela_2['Valor Complementar'] = df['Valor Complementar']\n",
    "    tabela_2['vBCST'] = df['vBCST']\n",
    "    tabela_2['Valor ICMS Substituição Tributária'] = df['Valor ICMS Substituição Tributária']\n",
    "    tabela_2['Valor ICMS Operação'] = df['Valor ICMS Operação']\n",
    "    tabela_2['CNPJ EMITENTE'] = df['CNPJ EMITENTE']\n",
    "    tabela_2['COD_ITEM'] = df['COD_ITEM']\n",
    "\n",
    "\n",
    "    tabela_2.rename(columns={'ICMS_TOT': 'ICMS_TOT_0',}, inplace=True)\n",
    "    tabela_2['ICMS_TOT'] = df['ICMS_TOT']\n",
    "    \n",
    "    tabela_2 = tabela_2[['CHV_DOC', 'DATA', 'CFOP', 'NUM_ITEM', 'cod novo', 'IND_OPER', 'SUB_TIPO', 'QTD_CAT',\n",
    "                        'QTD_INI', 'ICMS_INI', 'QTD_ent1_devolv_ent', 'ICMS_TOT_0', 'ICMS_TOT_ent_unit',\n",
    "                        'ULT_ICMS_TOT_ent_unit', 'ICMS_TOT_1', 'qtd_saida_1_devolv_saida', 'ICMS_SAIDA_UNI',\n",
    "                        'ULT_ICMS_SAIDA_UNI', 'ICMS_SAIDA', 'ICMS_TOT_PCAT', 'VLR_CONF_0', 'VLR_CONFR_UNIT',\n",
    "                        'ULT_VLR_CONFR_UNIT', 'VLR_CONFR_1', 'QTD_SALDO', 'ICMS_TOT_SALDO', 'VLR_RESSARCIMENTO',\n",
    "                        'VLR_COMPLEMENTO', 'COD_LEGAL', 'COD_LEGAL_PCAT', 'VLR_CONFR_PCAT', 'SALDO_FINAL_MES_QTD',\n",
    "                        'SALDO_FINAL_MES_ICMS', 'ALIQUOTA', 'VALOR', 'Valor Base Cálculo ICMS ST Retido Operação Anterior',\n",
    "                        'Valor Complementar', 'Valor ICMS Substituição Tributária', 'Valor ICMS Operação', \n",
    "                        'ICMS_TOT', 'COD_ITEM', 'CNPJ EMITENTE', 'tipo', 'vBCST', \n",
    "                        'FONTE']]\n",
    "    \n",
    "    return tabela_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cfa81e13",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2000691713.py:18: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data['DATA'] = pd.to_datetime(data['DATA'], format='%Y-%m-%d')\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2000691713.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data['IND_OPER'] = data['IND_OPER'].astype(int)\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2000691713.py:71: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  tabela_2['DATA'] = pd.to_datetime(tabela_2['DATA'], format='%Y-%m-%d')\n"
     ]
    }
   ],
   "source": [
    "tabela_2 = calcular_ressarcimento(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "37e094db",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CHV_DOC</th>\n",
       "      <th>DATA</th>\n",
       "      <th>CFOP</th>\n",
       "      <th>NUM_ITEM</th>\n",
       "      <th>cod novo</th>\n",
       "      <th>IND_OPER</th>\n",
       "      <th>SUB_TIPO</th>\n",
       "      <th>QTD_CAT</th>\n",
       "      <th>QTD_INI</th>\n",
       "      <th>ICMS_INI</th>\n",
       "      <th>...</th>\n",
       "      <th>Valor Base Cálculo ICMS ST Retido Operação Anterior</th>\n",
       "      <th>Valor Complementar</th>\n",
       "      <th>Valor ICMS Substituição Tributária</th>\n",
       "      <th>Valor ICMS Operação</th>\n",
       "      <th>ICMS_TOT</th>\n",
       "      <th>COD_ITEM</th>\n",
       "      <th>CNPJ EMITENTE</th>\n",
       "      <th>tipo</th>\n",
       "      <th>vBCST</th>\n",
       "      <th>FONTE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>209619</th>\n",
       "      <td>35191003129126000159590006031131098680305954</td>\n",
       "      <td>2019-10-24</td>\n",
       "      <td>5405</td>\n",
       "      <td>3</td>\n",
       "      <td>100073</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.541354</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100073</td>\n",
       "      <td>NaN</td>\n",
       "      <td>grupo</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9117</th>\n",
       "      <td>35191027849963000110550010005964131005945510</td>\n",
       "      <td>2019-10-29</td>\n",
       "      <td>1403</td>\n",
       "      <td>20</td>\n",
       "      <td>100073</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>63.62</td>\n",
       "      <td>11.96</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.6044</td>\n",
       "      <td>100073</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</td>\n",
       "      <td>75.582468</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14104</th>\n",
       "      <td>35191127849963000110550010006084971006065827</td>\n",
       "      <td>2019-11-09</td>\n",
       "      <td>1403</td>\n",
       "      <td>7</td>\n",
       "      <td>100073</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>63.62</td>\n",
       "      <td>11.96</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.6044</td>\n",
       "      <td>100073</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</td>\n",
       "      <td>75.582468</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209620</th>\n",
       "      <td>35191203129126000159590006031131212496179820</td>\n",
       "      <td>2019-12-05</td>\n",
       "      <td>5405</td>\n",
       "      <td>1</td>\n",
       "      <td>100073</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100073</td>\n",
       "      <td>NaN</td>\n",
       "      <td>grupo</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24064</th>\n",
       "      <td>35191227849963000110550010006364621006344734</td>\n",
       "      <td>2019-12-08</td>\n",
       "      <td>1403</td>\n",
       "      <td>36</td>\n",
       "      <td>100073</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>63.72</td>\n",
       "      <td>11.86</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.6044</td>\n",
       "      <td>100073</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</td>\n",
       "      <td>75.582468</td>\n",
       "      <td>entrada_sp</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162453</th>\n",
       "      <td>35201203129126000159590007086090676940106578</td>\n",
       "      <td>2020-12-23</td>\n",
       "      <td>5405</td>\n",
       "      <td>3</td>\n",
       "      <td>99234</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99234</td>\n",
       "      <td>NaN</td>\n",
       "      <td>grupo</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162450</th>\n",
       "      <td>35201203129126000159590005812821041405820715</td>\n",
       "      <td>2020-12-24</td>\n",
       "      <td>5405</td>\n",
       "      <td>5</td>\n",
       "      <td>99234</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99234</td>\n",
       "      <td>NaN</td>\n",
       "      <td>grupo</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141676</th>\n",
       "      <td>35201227849963000110550010010262221010226748</td>\n",
       "      <td>2020-12-29</td>\n",
       "      <td>1403</td>\n",
       "      <td>19</td>\n",
       "      <td>99234</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1.88</td>\n",
       "      <td>3.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.4075</td>\n",
       "      <td>99234</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</td>\n",
       "      <td>5.632534</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241965</th>\n",
       "      <td>35201203129126000159590007086090696680045137</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>5405</td>\n",
       "      <td>4</td>\n",
       "      <td>99234</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99234</td>\n",
       "      <td>NaN</td>\n",
       "      <td>grupo</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358131</th>\n",
       "      <td>35201203129126000159590007086090637396048776</td>\n",
       "      <td>2020-12-05</td>\n",
       "      <td>5405</td>\n",
       "      <td>2</td>\n",
       "      <td>99655</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99655</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>493482 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             CHV_DOC       DATA  CFOP  \\\n",
       "209619  35191003129126000159590006031131098680305954 2019-10-24  5405   \n",
       "9117    35191027849963000110550010005964131005945510 2019-10-29  1403   \n",
       "14104   35191127849963000110550010006084971006065827 2019-11-09  1403   \n",
       "209620  35191203129126000159590006031131212496179820 2019-12-05  5405   \n",
       "24064   35191227849963000110550010006364621006344734 2019-12-08  1403   \n",
       "...                                              ...        ...   ...   \n",
       "162453  35201203129126000159590007086090676940106578 2020-12-23  5405   \n",
       "162450  35201203129126000159590005812821041405820715 2020-12-24  5405   \n",
       "141676  35201227849963000110550010010262221010226748 2020-12-29  1403   \n",
       "241965  35201203129126000159590007086090696680045137 2020-12-31  5405   \n",
       "358131  35201203129126000159590007086090637396048776 2020-12-05  5405   \n",
       "\n",
       "        NUM_ITEM cod novo  IND_OPER  SUB_TIPO  QTD_CAT  QTD_INI   ICMS_INI  \\\n",
       "209619         3   100073         1         1        1      1.0  12.541354   \n",
       "9117          20   100073         0         1        1      NaN        NaN   \n",
       "14104          7   100073         0         1        1      NaN        NaN   \n",
       "209620         1   100073         1         1        1      NaN        NaN   \n",
       "24064         36   100073         0         1        1      NaN        NaN   \n",
       "...          ...      ...       ...       ...      ...      ...        ...   \n",
       "162453         3    99234         1         1        1      NaN        NaN   \n",
       "162450         5    99234         1         1        1      NaN        NaN   \n",
       "141676        19    99234         0         1        1      NaN        NaN   \n",
       "241965         4    99234         1         1        1      NaN        NaN   \n",
       "358131         2    99655         1         1        1      1.0   0.000000   \n",
       "\n",
       "        ...  Valor Base Cálculo ICMS ST Retido Operação Anterior  \\\n",
       "209619  ...                                               0.00     \n",
       "9117    ...                                              63.62     \n",
       "14104   ...                                              63.62     \n",
       "209620  ...                                               0.00     \n",
       "24064   ...                                              63.72     \n",
       "...     ...                                                ...     \n",
       "162453  ...                                               0.00     \n",
       "162450  ...                                               0.00     \n",
       "141676  ...                                               1.88     \n",
       "241965  ...                                               0.00     \n",
       "358131  ...                                               0.00     \n",
       "\n",
       "        Valor Complementar  Valor ICMS Substituição Tributária  \\\n",
       "209619                 NaN                                 0.0   \n",
       "9117                 11.96                                 0.0   \n",
       "14104                11.96                                 0.0   \n",
       "209620                 NaN                                 0.0   \n",
       "24064                11.86                                 0.0   \n",
       "...                    ...                                 ...   \n",
       "162453                 NaN                                 0.0   \n",
       "162450                 NaN                                 0.0   \n",
       "141676                3.75                                 0.0   \n",
       "241965                 NaN                                 0.0   \n",
       "358131                 NaN                                 0.0   \n",
       "\n",
       "        Valor ICMS Operação  ICMS_TOT  COD_ITEM  CNPJ EMITENTE   tipo  \\\n",
       "209619                  0.0       NaN    100073            NaN  grupo   \n",
       "9117                    0.0   13.6044    100073   2.784996e+13  grupo   \n",
       "14104                   0.0   13.6044    100073   2.784996e+13  grupo   \n",
       "209620                  0.0       NaN    100073            NaN  grupo   \n",
       "24064                   0.0   13.6044    100073   2.784996e+13  grupo   \n",
       "...                     ...       ...       ...            ...    ...   \n",
       "162453                  0.0       NaN     99234            NaN  grupo   \n",
       "162450                  0.0       NaN     99234            NaN  grupo   \n",
       "141676                  0.0    1.4075     99234   2.784996e+13  grupo   \n",
       "241965                  0.0       NaN     99234            NaN  grupo   \n",
       "358131                  0.0       NaN     99655            NaN    NaN   \n",
       "\n",
       "            vBCST       FONTE  \n",
       "209619   0.000000         dfe  \n",
       "9117    75.582468  entrada_sp  \n",
       "14104   75.582468  entrada_sp  \n",
       "209620   0.000000         dfe  \n",
       "24064   75.582468  entrada_sp  \n",
       "...           ...         ...  \n",
       "162453   0.000000         dfe  \n",
       "162450   0.000000         dfe  \n",
       "141676   5.632534  entrada_sp  \n",
       "241965   0.000000         dfe  \n",
       "358131   0.000000         dfe  \n",
       "\n",
       "[493482 rows x 45 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabela_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fa1edb6d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.04427852529652348"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabela_2['VLR_RESSARCIMENTO'].sum() / tabela_2[tabela_2['CFOP'] == 5405]['VALOR'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "59e68a16",
   "metadata": {},
   "outputs": [],
   "source": [
    "meta_por_loja = pd.read_excel(os.path.join('C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Referencias', 'GTI_12.xlsx'))\n",
    "try:\n",
    "    meta = meta_por_loja[(meta_por_loja['Loja'] == loja) & (meta_por_loja['periodo'] == f'p{periodo}')]['ESTIMATI3'].values[0]\n",
    "except:\n",
    "    meta = 0.085\n",
    "            \n",
    "def gti_pra_cima(tabela_2, meta):\n",
    "    \n",
    "    '''\n",
    "    Função para aplicação de GTI para lojas\n",
    "    com ressarcimento abaixo da porcentagem estabelecida\n",
    "    como meta\n",
    "    \n",
    "    '''\n",
    "\n",
    "    valor = tabela_2[tabela_2['CFOP'] == 5405]['VALOR'].sum()\n",
    "    ressarc = tabela_2['VLR_RESSARCIMENTO'].sum()\n",
    "    fator = 0.1\n",
    "    \n",
    "    # 1ª Iteração\n",
    "    \n",
    "    tabela_2['cod novo'] = tabela_2['cod novo'].astype(str)\n",
    "    pivot_ressarc = tabela_2[tabela_2['tipo'] == 'grupo'].pivot_table(index='cod novo', values='VLR_RESSARCIMENTO', aggfunc='sum')\n",
    "    pivot_ressarc = pivot_ressarc[pivot_ressarc['VLR_RESSARCIMENTO'] > 0]\n",
    "    pivot_ressarc = pivot_ressarc.sort_values(by='VLR_RESSARCIMENTO', ascending=False)  \n",
    "    \n",
    "    incr_max_ref = pivot_ressarc.iloc[54].values[0]\n",
    "    incr_max = pivot_ressarc.iloc[55:]\n",
    "    incr_max['Diferenças'] = incr_max_ref - incr_max['VLR_RESSARCIMENTO']\n",
    "    incr_max = incr_max.sort_values(by='cod novo')\n",
    "    \n",
    "    qtd_saida = tabela_2[tabela_2['CFOP'] == 5405].pivot_table(index='cod novo', values='QTD_CAT', aggfunc='sum')\n",
    "    incr_max_unit = incr_max.merge(qtd_saida, on='cod novo', how='left')\n",
    "    incr_max_unit['Incremento maximo unitario'] = incr_max_unit['Diferenças'] / incr_max_unit['QTD_CAT']\n",
    "    incr_max_unit = incr_max_unit.reset_index()\n",
    "    \n",
    "    tabela_2 = tabela_2.merge(incr_max_unit[['cod novo', 'Incremento maximo unitario']], on='cod novo', how='left')\n",
    "    tabela_2['Incremento maximo unitario'] = np.where(tabela_2['ICMS_TOT'].fillna(0) == 0,\n",
    "                                                 0,\n",
    "                                                 tabela_2['Incremento maximo unitario'])\n",
    "    \n",
    "    tabela_2['Incremento maximo unitario'] = tabela_2['Incremento maximo unitario'].fillna(0)\n",
    "    \n",
    "    tabela_2['ICMS_TOT_FINAL'] = np.where(tabela_2['ICMS_TOT'].fillna(0) != 0,\n",
    "                                     tabela_2['ICMS_TOT'] + (tabela_2['Incremento maximo unitario']*fator)*tabela_2['QTD_CAT'],\n",
    "                                     0)\n",
    "    \n",
    "    tabela_2['bc ST op ant'] = np.where(tabela_2['ALIQUOTA'] != 0,\n",
    "                                   tabela_2['ICMS_TOT_FINAL'] / (tabela_2['ALIQUOTA']/100),\n",
    "                                   tabela_2['Valor Base Cálculo ICMS ST Retido Operação Anterior'])\n",
    "    \n",
    "    tabela_2['bc ST op ant comple'] = tabela_2['bc ST op ant'] - tabela_2['Valor Base Cálculo ICMS ST Retido Operação Anterior']\n",
    "    \n",
    "    df_final = df.copy()\n",
    "    df_final = df_final.sort_values(by=['cod novo', 'DATA', 'IND_OPER', 'SUB_TIPO'], \n",
    "                                   ascending=[True, True, True, True]).reset_index().drop(['index'], axis=1)\n",
    "    \n",
    "    df_final['bc ST op ant comple'] = np.where(tabela_2['tipo'] == 'grupo',\n",
    "                                               tabela_2['bc ST op ant comple'],\n",
    "                                               tabela_2['Valor Complementar'])\n",
    "    \n",
    "    df_final.rename(columns={'ICMS_TOT': 'ICMS_TOT_ORIG',\n",
    "                            'Valor Complementar': 'Complementar Original',\n",
    "                            'bc ST op ant comple': 'Valor Complementar'}, inplace=True)\n",
    "    \n",
    "    df_final['ICMS_TOT'] = np.where(df_final['Valor Base Cálculo ICMS ST Retido Operação Anterior'] <= df_final['vBCST'],\n",
    "             np.maximum(df_final['Valor ICMS Operação'].fillna(0) + df_final['Valor ICMS Substituição Tributária'].fillna(0), (df_final['Valor Base Cálculo ICMS ST Retido Operação Anterior'] + df_final['Valor Complementar'].fillna(0)) * df_final['ALIQUOTA'].fillna(0).astype(float)/100),\n",
    "             np.maximum(df_final['Valor ICMS Operação'].fillna(0) + df_final['Valor ICMS Substituição Tributária'].fillna(0), (df_final['vBCST'] + df_final['Valor Complementar'].fillna(0)) * df_final['ALIQUOTA'].fillna(0).astype(float)/100))\n",
    "\n",
    "    \n",
    "    tabela_2_final = calcular_ressarcimento(df_final)\n",
    "    \n",
    "    # 2ª Iteração\n",
    "    ressarc_tot_0_1 = tabela_2_final['VLR_RESSARCIMENTO'].sum()\n",
    "    delta_fator_0_1 = ressarc_tot_0_1 - ressarc\n",
    "    meta = meta\n",
    "    meta_ressarc = valor * meta\n",
    "    meta_delta = meta_ressarc - ressarc\n",
    "    meta_fator_0_1 = meta_delta/delta_fator_0_1\n",
    "    meta_fator = meta_fator_0_1*0.1*0.95\n",
    "    \n",
    "    tabela_2_final['cod novo'] = tabela_2_final['cod novo'].astype(str)\n",
    "    pivot_ressarc_final = tabela_2_final[tabela_2_final['tipo'] == 'grupo'].pivot_table(index='cod novo', values='VLR_RESSARCIMENTO', aggfunc='sum')\n",
    "    pivot_ressarc_final = pivot_ressarc_final[pivot_ressarc_final['VLR_RESSARCIMENTO'] > 0]\n",
    "    pivot_ressarc_final = pivot_ressarc_final.sort_values(by='VLR_RESSARCIMENTO', ascending=False)\n",
    "    \n",
    "    \n",
    "    incr_max_ref_final = pivot_ressarc_final.iloc[54].values[0]\n",
    "    incr_max_final = pivot_ressarc_final.iloc[55:]\n",
    "    incr_max_final['Diferenças'] = incr_max_ref_final - incr_max_final['VLR_RESSARCIMENTO']\n",
    "    incr_max_final = incr_max_final.sort_values(by='cod novo')\n",
    "    \n",
    "    qtd_saida_final = tabela_2_final[tabela_2_final['CFOP'] == 5405].pivot_table(index='cod novo', values='QTD_CAT', aggfunc='sum')\n",
    "    incr_max_unit_final = incr_max_final.merge(qtd_saida_final, on='cod novo', how='left')\n",
    "    incr_max_unit_final['Incremento maximo unitario'] = incr_max_unit_final['Diferenças'] / incr_max_unit_final['QTD_CAT']\n",
    "    \n",
    "    incr_max_unit_final = incr_max_unit_final.reset_index()\n",
    "    \n",
    "    tabela_2_final['ICMS_TOT_ORIG'] = df_final['ICMS_TOT_ORIG']\n",
    "    tabela_2_final = tabela_2_final.merge(incr_max_unit_final[['cod novo', 'Incremento maximo unitario']], on='cod novo', how='left')\n",
    "    tabela_2_final['Incremento maximo unitario'] = np.where(tabela_2_final['ICMS_TOT_ORIG'].fillna(0) == 0,\n",
    "                                                 0,\n",
    "                                                 tabela_2_final['Incremento maximo unitario'])\n",
    "    tabela_2_final['Incremento maximo unitario'] = tabela_2_final['Incremento maximo unitario'].fillna(0)\n",
    "    \n",
    "    tabela_2_final['ICMS_TOT_FINAL'] = np.where(tabela_2_final['ICMS_TOT_ORIG'].fillna(0) != 0,\n",
    "                                     tabela_2_final['ICMS_TOT_ORIG'] + (tabela_2_final['Incremento maximo unitario']*meta_fator)*tabela_2_final['QTD_CAT'],\n",
    "                                     0)\n",
    "    \n",
    "    tabela_2_final['bc ST op ant'] = np.where(tabela_2_final['ALIQUOTA'] != 0,\n",
    "                                   tabela_2_final['ICMS_TOT_FINAL'] / (tabela_2_final['ALIQUOTA']/100),\n",
    "                                   tabela_2_final['Valor Base Cálculo ICMS ST Retido Operação Anterior'])\n",
    "    \n",
    "    tabela_2_final['bc ST op ant comple'] = tabela_2_final['bc ST op ant'] - tabela_2_final['Valor Base Cálculo ICMS ST Retido Operação Anterior']\n",
    "    \n",
    "    \n",
    "    df_fim = df.copy()\n",
    "    df_fim = df_fim.sort_values(by=['cod novo', 'DATA', 'IND_OPER', 'SUB_TIPO'], \n",
    "                                   ascending=[True, True, True, True]).reset_index().drop(['index'], axis=1)\n",
    "    \n",
    "    df_fim['bc ST op ant comple'] = np.where(tabela_2_final['tipo'] == 'grupo',\n",
    "                                               tabela_2_final['bc ST op ant comple'],\n",
    "                                               tabela_2_final['Valor Complementar'])\n",
    "    \n",
    "    df_fim.rename(columns={'ICMS_TOT': 'ICMS_TOT_ORIG',\n",
    "                            'Valor Complementar': 'Complementar Original',\n",
    "                            'bc ST op ant comple': 'Valor Complementar'}, inplace=True)\n",
    "    \n",
    "    df_fim['ICMS_TOT'] = np.where(df_fim['Valor Base Cálculo ICMS ST Retido Operação Anterior'] <= df_fim['vBCST'],\n",
    "             np.maximum(df_fim['Valor ICMS Operação'].fillna(0) + df_fim['Valor ICMS Substituição Tributária'].fillna(0), (df_fim['Valor Base Cálculo ICMS ST Retido Operação Anterior'] + df_fim['Valor Complementar'].fillna(0)) * df_fim['ALIQUOTA'].fillna(0).astype(float)/100),\n",
    "             np.maximum(df_fim['Valor ICMS Operação'].fillna(0) + df_fim['Valor ICMS Substituição Tributária'].fillna(0), (df_fim['vBCST'] + df_fim['Valor Complementar'].fillna(0)) * df_fim['ALIQUOTA'].fillna(0).astype(float)/100))\n",
    "    \n",
    "    tabela_2_fim = calcular_ressarcimento(df_fim)\n",
    "    \n",
    "    return tabela_2_fim\n",
    "\n",
    "def gti_pra_baixo(tabela_2, meta):\n",
    "    '''\n",
    "    Função para aplicação de GTI para lojas\n",
    "    com ressarcimento acima da porcentagem estabelecida\n",
    "    como meta\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    ressarc = tabela_2['VLR_RESSARCIMENTO'].sum()\n",
    "    meta_ressarc = tabela_2[tabela_2['CFOP'] == 5405]['VALOR'].sum() * meta\n",
    "    diferenca = ressarc-meta_ressarc\n",
    "    pivot_ressarc = tabela_2.pivot_table(index='cod novo', values='VLR_RESSARCIMENTO', aggfunc='sum')\n",
    "    pivot_ressarc = pivot_ressarc[pivot_ressarc['VLR_RESSARCIMENTO'] > 0].sort_values(by='VLR_RESSARCIMENTO',\n",
    "                                                                                     ascending=True)\n",
    "    soma = 0\n",
    "    i = 0\n",
    "    cods = []\n",
    "    while i < len(pivot_ressarc) and (soma + pivot_ressarc['VLR_RESSARCIMENTO'].iloc[i]) <= diferenca:\n",
    "        soma += pivot_ressarc['VLR_RESSARCIMENTO'].iloc[i]\n",
    "        cods.append(pivot_ressarc.index[i])\n",
    "        i += 1\n",
    "    \n",
    "    df['COD_LEGAL'] = np.where(df['cod novo'].isin(cods),\n",
    "                              np.where(df['COD_LEGAL'].isnull(), df['COD_LEGAL'], 0),\n",
    "                              df['COD_LEGAL'])\n",
    "    \n",
    "    df['VL_CONFR_0'] = np.where(df['cod novo'].isin(cods),\n",
    "                               np.nan,\n",
    "                               df['VL_CONFR_0'])\n",
    "    \n",
    "    tabela_2_final = calcular_ressarcimento(df)\n",
    "    \n",
    "    return tabela_2_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9283b174",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2738891347.py:29: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  incr_max['Diferenças'] = incr_max_ref - incr_max['VLR_RESSARCIMENTO']\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2000691713.py:18: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data['DATA'] = pd.to_datetime(data['DATA'], format='%Y-%m-%d')\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2000691713.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data['IND_OPER'] = data['IND_OPER'].astype(int)\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2000691713.py:71: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  tabela_2['DATA'] = pd.to_datetime(tabela_2['DATA'], format='%Y-%m-%d')\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2738891347.py:90: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  incr_max_final['Diferenças'] = incr_max_ref_final - incr_max_final['VLR_RESSARCIMENTO']\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2000691713.py:18: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data['DATA'] = pd.to_datetime(data['DATA'], format='%Y-%m-%d')\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2000691713.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data['IND_OPER'] = data['IND_OPER'].astype(int)\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_10208\\2000691713.py:71: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  tabela_2['DATA'] = pd.to_datetime(tabela_2['DATA'], format='%Y-%m-%d')\n"
     ]
    }
   ],
   "source": [
    "if (tabela_2['VLR_RESSARCIMENTO'].sum() / tabela_2[tabela_2['CFOP'] == 5405]['VALOR'].sum()) < meta:\n",
    "    tabela_2_def = gti_pra_cima(tabela_2, meta)\n",
    "#     with open(os.path.join('C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}', f'Log_GTI_{loja}_p{periodo}'), 'w') as arquivo:\n",
    "#         arquivo.write('LOJA COM GTI PARA CIMA')\n",
    "            \n",
    "elif (tabela_2['VLR_RESSARCIMENTO'].sum() / tabela_2[tabela_2['CFOP'] == 5405]['VALOR'].sum()) > meta:\n",
    "#     with open(os.path.join('C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}', f'Log_GTI_{loja}_p{periodo}'), 'w') as arquivo:\n",
    "#         arquivo.write('LOJA COM GTI PARA BAIXO')\n",
    "    print('ATENÇÃO. LOJA COM PORCENTAGEM DE RESSARCIMENTO ACIMA DA MARGEM')\n",
    "    print('+'*50)\n",
    "    print('\\n')\n",
    "    print('REPETINDO. ATENÇÃO. LOJA COM PORCENTAGEM DE RESSARCIMENTO ACIMA DA MARGEM')\n",
    "    tabela_2_def = gti_pra_baixo(tabela_2, meta)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "eccfe875",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.08127309938956044"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabela_2_def['VLR_RESSARCIMENTO'].sum() / tabela_2_def[tabela_2_def['CFOP'] == 5405]['VALOR'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "33392741",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Criação das funções para geração dos arquivos Excel\n",
    "\n",
    "def gerar_excel_lojas_pequenas(tabela_2):\n",
    "    '''\n",
    "    Função para exportação da Ficha 3 em arquivo excel\n",
    "    para o caso de o arquivo de ficha 3 da loja tenha\n",
    "    menos de 1 milhão de linhas\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    path = f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}\\\\ficha_3_loja_{loja}_p{periodo}_v1.xlsx'\n",
    "    with pd.ExcelWriter(path, engine='xlsxwriter') as writer:\n",
    "        tabela_2.to_excel(writer, sheet_name='ficha3', index=False)\n",
    "        \n",
    "def gerar_excel_lojas_grandes(tabela_2):\n",
    "    '''\n",
    "    Função para exportação da Ficha 3 em arquivo excel\n",
    "    para o caso de o arquivo de ficha 3 da loja tenha\n",
    "    mais de 1 milhão de linhas\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    cods = list(tabela_2['cod novo'].unique())\n",
    "    l = len(cods)\n",
    "\n",
    "    fat_div = 2\n",
    "\n",
    "    while True:\n",
    "\n",
    "        cods_p1 = cods[:int(l/fat_div)]\n",
    "        cods_p2 = cods[int(l/fat_div):]\n",
    "\n",
    "        tabela_2_1 = tabela_2[tabela_2['cod novo'].isin(cods_p1)]\n",
    "        tabela_2_2 = tabela_2[tabela_2['cod novo'].isin(cods_p2)]\n",
    "\n",
    "        if tabela_2_1.shape[0] > 1000000:\n",
    "            fat_div += 1\n",
    "\n",
    "        else:\n",
    "            break\n",
    "    \n",
    "    # Geração do arquivo final referente ao dataframe 1\n",
    "    path = f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}\\\\ficha_3_loja_{loja}_p{periodo}_parte1_v1.xlsx'\n",
    "    with pd.ExcelWriter(path, engine='xlsxwriter') as writer:\n",
    "        tabela_2_1.to_excel(writer, sheet_name='ficha3', index=False)\n",
    "        \n",
    "    # Geração do arquivo final referente ao dataframe 2\n",
    "    path = f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}\\\\ficha_3_loja_{loja}_p{periodo}_parte2_v1.xlsx'\n",
    "    with pd.ExcelWriter(path, engine='xlsxwriter') as writer:\n",
    "        tabela_2_2.to_excel(writer, sheet_name='ficha3', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b0167a6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "if tabela_2.shape[0] > 1000000:\n",
    "    print('+'*50)\n",
    "    print('ATENÇÃO, LOJA GRANDE. ATENÇÃO, LOJA GRANDE')\n",
    "    print('+'*50)\n",
    "    \n",
    "    gerar_excel_lojas_grandes(tabela_2_def)\n",
    "    \n",
    "else:\n",
    "    gerar_excel_lojas_pequenas(tabela_2_def)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ef8ebb01",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VLR_COMPLEMENTO</th>\n",
       "      <th>VLR_RESSARCIMENTO</th>\n",
       "      <th>VALOR</th>\n",
       "      <th>Data</th>\n",
       "      <th>CNPJ</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>REF</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-10</th>\n",
       "      <td>9230.090117</td>\n",
       "      <td>17677.241305</td>\n",
       "      <td>234641.62</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11</th>\n",
       "      <td>9212.106706</td>\n",
       "      <td>20318.730147</td>\n",
       "      <td>237611.47</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12</th>\n",
       "      <td>11462.443252</td>\n",
       "      <td>24525.429181</td>\n",
       "      <td>300875.03</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01</th>\n",
       "      <td>12094.439101</td>\n",
       "      <td>26224.592812</td>\n",
       "      <td>322002.67</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02</th>\n",
       "      <td>8883.442604</td>\n",
       "      <td>20864.995076</td>\n",
       "      <td>243830.59</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03</th>\n",
       "      <td>9928.760149</td>\n",
       "      <td>24099.254649</td>\n",
       "      <td>274375.99</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04</th>\n",
       "      <td>7816.793614</td>\n",
       "      <td>22429.278518</td>\n",
       "      <td>237437.15</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05</th>\n",
       "      <td>8688.637205</td>\n",
       "      <td>18820.611922</td>\n",
       "      <td>231115.28</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-06</th>\n",
       "      <td>7354.646377</td>\n",
       "      <td>17605.169081</td>\n",
       "      <td>208860.49</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07</th>\n",
       "      <td>8882.599170</td>\n",
       "      <td>19560.655443</td>\n",
       "      <td>247294.52</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-08</th>\n",
       "      <td>8152.730484</td>\n",
       "      <td>22162.130699</td>\n",
       "      <td>240503.28</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-09</th>\n",
       "      <td>9042.556647</td>\n",
       "      <td>21136.627363</td>\n",
       "      <td>261295.12</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10</th>\n",
       "      <td>10008.550196</td>\n",
       "      <td>18664.527169</td>\n",
       "      <td>266334.34</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-11</th>\n",
       "      <td>10166.522151</td>\n",
       "      <td>19443.573792</td>\n",
       "      <td>270287.87</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-12</th>\n",
       "      <td>11688.112519</td>\n",
       "      <td>22919.472965</td>\n",
       "      <td>317225.02</td>\n",
       "      <td>2024-02-28 17:41:37</td>\n",
       "      <td>03129126000159</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         VLR_COMPLEMENTO  VLR_RESSARCIMENTO      VALOR                 Data  \\\n",
       "REF                                                                           \n",
       "2019-10      9230.090117       17677.241305  234641.62  2024-02-28 17:41:37   \n",
       "2019-11      9212.106706       20318.730147  237611.47  2024-02-28 17:41:37   \n",
       "2019-12     11462.443252       24525.429181  300875.03  2024-02-28 17:41:37   \n",
       "2020-01     12094.439101       26224.592812  322002.67  2024-02-28 17:41:37   \n",
       "2020-02      8883.442604       20864.995076  243830.59  2024-02-28 17:41:37   \n",
       "2020-03      9928.760149       24099.254649  274375.99  2024-02-28 17:41:37   \n",
       "2020-04      7816.793614       22429.278518  237437.15  2024-02-28 17:41:37   \n",
       "2020-05      8688.637205       18820.611922  231115.28  2024-02-28 17:41:37   \n",
       "2020-06      7354.646377       17605.169081  208860.49  2024-02-28 17:41:37   \n",
       "2020-07      8882.599170       19560.655443  247294.52  2024-02-28 17:41:37   \n",
       "2020-08      8152.730484       22162.130699  240503.28  2024-02-28 17:41:37   \n",
       "2020-09      9042.556647       21136.627363  261295.12  2024-02-28 17:41:37   \n",
       "2020-10     10008.550196       18664.527169  266334.34  2024-02-28 17:41:37   \n",
       "2020-11     10166.522151       19443.573792  270287.87  2024-02-28 17:41:37   \n",
       "2020-12     11688.112519       22919.472965  317225.02  2024-02-28 17:41:37   \n",
       "\n",
       "                   CNPJ  \n",
       "REF                      \n",
       "2019-10  03129126000159  \n",
       "2019-11  03129126000159  \n",
       "2019-12  03129126000159  \n",
       "2020-01  03129126000159  \n",
       "2020-02  03129126000159  \n",
       "2020-03  03129126000159  \n",
       "2020-04  03129126000159  \n",
       "2020-05  03129126000159  \n",
       "2020-06  03129126000159  \n",
       "2020-07  03129126000159  \n",
       "2020-08  03129126000159  \n",
       "2020-09  03129126000159  \n",
       "2020-10  03129126000159  \n",
       "2020-11  03129126000159  \n",
       "2020-12  03129126000159  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabela_2_def['REF'] = [datetime.strptime(x, '%Y-%m-%d').strftime('%Y-%m') for x in tabela_2_def['DATA'].astype(str)]\n",
    "pivot = tabela_2_def.pivot_table(index='REF', values=['VLR_RESSARCIMENTO', 'VLR_COMPLEMENTO'], aggfunc='sum').sort_values(by='REF')\n",
    "pivot_fat = tabela_2_def[tabela_2_def['CFOP'] == 5405].pivot_table(index='REF', values='VALOR', aggfunc='sum').sort_values(by='REF')\n",
    "fuso_horario_brasilia = pytz.timezone('America/Sao_Paulo')\n",
    "data_geracao_brasilia = datetime.now(fuso_horario_brasilia).strftime('%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "pivot['VALOR'] = pivot_fat['VALOR']\n",
    "pivot['Data'] = data_geracao_brasilia\n",
    "cnpj = tabela_2_def[tabela_2_def['FONTE'] == 'dfe']['CHV_DOC'].str.slice(6,20).unique()\n",
    "pivot['CNPJ'] = list(cnpj)*len(pivot)\n",
    "\n",
    "pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "041fdade",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3893690.44"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot['VALOR'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "58786aab",
   "metadata": {},
   "outputs": [],
   "source": [
    "pivot.rename(columns={'REF': 'DATA'})\n",
    "pivot.to_excel(f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}\\\\relatorio_mensal_ressarcimento_complemento_{loja}_p{periodo}_v1.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "45604308",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4903.111194849014\n"
     ]
    }
   ],
   "source": [
    "end = time.time()\n",
    "print(end - start)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ad619587",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabela_2_def[tabela_2_def['CFOP'].isin([1403, 5411])]['COD_LEGAL_PCAT'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b47f64e8",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}