Geração_1050_lojas_grandes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "645ec24e",
   "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 math\n",
    "import sys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b1c1d0ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Definição da loja a ser utilizada\n",
    "\n",
    "loja = 15\n",
    "periodo = 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e0a20518",
   "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>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</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>FONTE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35191027849963000110550010005774841005756440</td>\n",
       "      <td>2019-10-08</td>\n",
       "      <td>1403</td>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9.0</td>\n",
       "      <td>38.759676</td>\n",
       "      <td>...</td>\n",
       "      <td>139.05</td>\n",
       "      <td>153.09</td>\n",
       "      <td>158.98</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>56.1726</td>\n",
       "      <td>18</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35191027849963000110550010005914521005895993</td>\n",
       "      <td>2019-10-23</td>\n",
       "      <td>1403</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>61.80</td>\n",
       "      <td>68.04</td>\n",
       "      <td>70.66</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.9660</td>\n",
       "      <td>18</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35191006161357001029590001319661938836612389</td>\n",
       "      <td>2019-10-24</td>\n",
       "      <td>5405</td>\n",
       "      <td>9</td>\n",
       "      <td>18</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>19.24</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>grupo</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35191006161357001029590001319661938836612389</td>\n",
       "      <td>2019-10-24</td>\n",
       "      <td>5405</td>\n",
       "      <td>10</td>\n",
       "      <td>18</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>19.24</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>grupo</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35191027849963000110550010005962641005944032</td>\n",
       "      <td>2019-10-29</td>\n",
       "      <td>1403</td>\n",
       "      <td>6</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>30.90</td>\n",
       "      <td>34.02</td>\n",
       "      <td>35.33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.4830</td>\n",
       "      <td>18</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</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>816435</th>\n",
       "      <td>35200106161357001029590000458021832055491263</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>5405</td>\n",
       "      <td>1</td>\n",
       "      <td>940044</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.695967</td>\n",
       "      <td>...</td>\n",
       "      <td>19.14</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>99150</td>\n",
       "      <td>NaN</td>\n",
       "      <td>outros2</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816436</th>\n",
       "      <td>35200106161357001371550020000254681165494190</td>\n",
       "      <td>2020-01-29</td>\n",
       "      <td>1409</td>\n",
       "      <td>2</td>\n",
       "      <td>940044</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>17.08</td>\n",
       "      <td>29.50</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.5400</td>\n",
       "      <td>99150</td>\n",
       "      <td>6.161357e+12</td>\n",
       "      <td>outros2</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816437</th>\n",
       "      <td>35200106161357001029590000461292710905840465</td>\n",
       "      <td>2020-01-29</td>\n",
       "      <td>5405</td>\n",
       "      <td>1</td>\n",
       "      <td>940044</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>12.76</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>99150</td>\n",
       "      <td>NaN</td>\n",
       "      <td>outros2</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816438</th>\n",
       "      <td>35201271605265006283550010000016371010231769</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>1403</td>\n",
       "      <td>77</td>\n",
       "      <td>940045</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.46</td>\n",
       "      <td>1.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.4700</td>\n",
       "      <td>99234</td>\n",
       "      <td>7.160527e+13</td>\n",
       "      <td>outros2</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816439</th>\n",
       "      <td>35201271605265006283550010000042411010257800</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>1403</td>\n",
       "      <td>102</td>\n",
       "      <td>940045</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>3.46</td>\n",
       "      <td>1.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.4700</td>\n",
       "      <td>99234</td>\n",
       "      <td>7.160527e+13</td>\n",
       "      <td>outros2</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>816440 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             CHV_DOC       DATA  CFOP  \\\n",
       "0       35191027849963000110550010005774841005756440 2019-10-08  1403   \n",
       "1       35191027849963000110550010005914521005895993 2019-10-23  1403   \n",
       "2       35191006161357001029590001319661938836612389 2019-10-24  5405   \n",
       "3       35191006161357001029590001319661938836612389 2019-10-24  5405   \n",
       "4       35191027849963000110550010005962641005944032 2019-10-29  1403   \n",
       "...                                              ...        ...   ...   \n",
       "816435  35200106161357001029590000458021832055491263 2020-01-23  5405   \n",
       "816436  35200106161357001371550020000254681165494190 2020-01-29  1409   \n",
       "816437  35200106161357001029590000461292710905840465 2020-01-29  5405   \n",
       "816438  35201271605265006283550010000016371010231769 2020-12-31  1403   \n",
       "816439  35201271605265006283550010000042411010257800 2020-12-31  1403   \n",
       "\n",
       "        NUM_ITEM  cod novo  IND_OPER  SUB_TIPO  QTD_CAT  QTD_INI   ICMS_INI  \\\n",
       "0             11        18         0         1        9      9.0  38.759676   \n",
       "1              3        18         0         1        4      NaN        NaN   \n",
       "2              9        18         1         1        1      NaN        NaN   \n",
       "3             10        18         1         1        1      NaN        NaN   \n",
       "4              6        18         0         1        2      NaN        NaN   \n",
       "...          ...       ...       ...       ...      ...      ...        ...   \n",
       "816435         1    940044         1         1        1      1.0   1.695967   \n",
       "816436         2    940044         0         1        2      NaN        NaN   \n",
       "816437         1    940044         1         1        1      NaN        NaN   \n",
       "816438        77    940045         0         1        1      0.0   0.000000   \n",
       "816439       102    940045         0         1        1      NaN        NaN   \n",
       "\n",
       "        ...   VALOR  Valor Base Cálculo ICMS ST Retido Operação Anterior  \\\n",
       "0       ...  139.05                                             153.09     \n",
       "1       ...   61.80                                              68.04     \n",
       "2       ...   19.24                                               0.00     \n",
       "3       ...   19.24                                               0.00     \n",
       "4       ...   30.90                                              34.02     \n",
       "...     ...     ...                                                ...     \n",
       "816435  ...   19.14                                               0.00     \n",
       "816436  ...   17.08                                              29.50     \n",
       "816437  ...   12.76                                               0.00     \n",
       "816438  ...    3.46                                               1.88     \n",
       "816439  ...    3.46                                               1.88     \n",
       "\n",
       "        Valor Complementar  Valor ICMS Substituição Tributária  \\\n",
       "0                   158.98                                 0.0   \n",
       "1                    70.66                                 0.0   \n",
       "2                      NaN                                 0.0   \n",
       "3                      NaN                                 0.0   \n",
       "4                    35.33                                 0.0   \n",
       "...                    ...                                 ...   \n",
       "816435                0.00                                 0.0   \n",
       "816436                0.00                                 0.0   \n",
       "816437                0.00                                 0.0   \n",
       "816438                0.00                                 0.0   \n",
       "816439                0.00                                 0.0   \n",
       "\n",
       "        Valor ICMS Operação  ICMS_TOT  COD_ITEM  CNPJ EMITENTE     tipo  \\\n",
       "0                       0.0   56.1726        18   2.784996e+13    grupo   \n",
       "1                       0.0   24.9660        18   2.784996e+13    grupo   \n",
       "2                       0.0    0.0000        18            NaN    grupo   \n",
       "3                       0.0    0.0000        18            NaN    grupo   \n",
       "4                       0.0   12.4830        18   2.784996e+13    grupo   \n",
       "...                     ...       ...       ...            ...      ...   \n",
       "816435                  0.0    0.0000     99150            NaN  outros2   \n",
       "816436                  0.0    3.5400     99150   6.161357e+12  outros2   \n",
       "816437                  0.0    0.0000     99150            NaN  outros2   \n",
       "816438                  0.0    0.4700     99234   7.160527e+13  outros2   \n",
       "816439                  0.0    0.4700     99234   7.160527e+13  outros2   \n",
       "\n",
       "             FONTE  \n",
       "0       entrada_sp  \n",
       "1       entrada_sp  \n",
       "2              dfe  \n",
       "3              dfe  \n",
       "4       entrada_sp  \n",
       "...            ...  \n",
       "816435         dfe  \n",
       "816436  entrada_sp  \n",
       "816437         dfe  \n",
       "816438  entrada_sp  \n",
       "816439  entrada_sp  \n",
       "\n",
       "[816440 rows x 44 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1 = pd.read_excel(f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}\\\\ficha_3_loja_{loja}_p{periodo}_parte1_v1.xlsx')\n",
    "df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e53109eb",
   "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>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</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>FONTE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35190927849963000110550010005711631005693320</td>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>1403</td>\n",
       "      <td>38</td>\n",
       "      <td>195776</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>8.46</td>\n",
       "      <td>5.37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.3425</td>\n",
       "      <td>195776</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35191027849963000110550010005762761005744363</td>\n",
       "      <td>2019-10-05</td>\n",
       "      <td>1403</td>\n",
       "      <td>35</td>\n",
       "      <td>195776</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>8.46</td>\n",
       "      <td>5.37</td>\n",
       "      <td>8.31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.4200</td>\n",
       "      <td>195776</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35191006161357001029590001272802165696307606</td>\n",
       "      <td>2019-10-09</td>\n",
       "      <td>5405</td>\n",
       "      <td>3</td>\n",
       "      <td>195776</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>11.99</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>195776</td>\n",
       "      <td>NaN</td>\n",
       "      <td>grupo</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35191027849963000110550010005817661005799089</td>\n",
       "      <td>2019-10-11</td>\n",
       "      <td>1403</td>\n",
       "      <td>36</td>\n",
       "      <td>195776</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>8.46</td>\n",
       "      <td>5.37</td>\n",
       "      <td>8.31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.4200</td>\n",
       "      <td>195776</td>\n",
       "      <td>2.784996e+13</td>\n",
       "      <td>grupo</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35191006161357001029590001319661925878997570</td>\n",
       "      <td>2019-10-16</td>\n",
       "      <td>5405</td>\n",
       "      <td>19</td>\n",
       "      <td>195776</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>11.99</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>195776</td>\n",
       "      <td>NaN</td>\n",
       "      <td>grupo</td>\n",
       "      <td>dfe</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>580310</th>\n",
       "      <td>35191261940292000137550310004704221104408801</td>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>1403</td>\n",
       "      <td>11</td>\n",
       "      <td>939987</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>76.62</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>935528</td>\n",
       "      <td>6.194029e+13</td>\n",
       "      <td>outros2</td>\n",
       "      <td>entrada_sp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580311</th>\n",
       "      <td>35200206161357001029590000458021844947576615</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>5405</td>\n",
       "      <td>2</td>\n",
       "      <td>939987</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>100.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>935528</td>\n",
       "      <td>NaN</td>\n",
       "      <td>outros2</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580312</th>\n",
       "      <td>35200406161357001029590000461292846055933779</td>\n",
       "      <td>2020-04-13</td>\n",
       "      <td>5405</td>\n",
       "      <td>1</td>\n",
       "      <td>939987</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>100.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>935528</td>\n",
       "      <td>NaN</td>\n",
       "      <td>outros2</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580313</th>\n",
       "      <td>35200806161357001029590001272802676018284665</td>\n",
       "      <td>2020-08-30</td>\n",
       "      <td>5405</td>\n",
       "      <td>1</td>\n",
       "      <td>939987</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>90.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>935528</td>\n",
       "      <td>NaN</td>\n",
       "      <td>outros2</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580314</th>\n",
       "      <td>35201006161357001029590001311792474376495063</td>\n",
       "      <td>2020-10-26</td>\n",
       "      <td>5405</td>\n",
       "      <td>1</td>\n",
       "      <td>939987</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>100.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>935528</td>\n",
       "      <td>NaN</td>\n",
       "      <td>outros2</td>\n",
       "      <td>dfe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>580315 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             CHV_DOC       DATA  CFOP  \\\n",
       "0       35190927849963000110550010005711631005693320 2019-10-01  1403   \n",
       "1       35191027849963000110550010005762761005744363 2019-10-05  1403   \n",
       "2       35191006161357001029590001272802165696307606 2019-10-09  5405   \n",
       "3       35191027849963000110550010005817661005799089 2019-10-11  1403   \n",
       "4       35191006161357001029590001319661925878997570 2019-10-16  5405   \n",
       "...                                              ...        ...   ...   \n",
       "580310  35191261940292000137550310004704221104408801 2019-12-28  1403   \n",
       "580311  35200206161357001029590000458021844947576615 2020-02-04  5405   \n",
       "580312  35200406161357001029590000461292846055933779 2020-04-13  5405   \n",
       "580313  35200806161357001029590001272802676018284665 2020-08-30  5405   \n",
       "580314  35201006161357001029590001311792474376495063 2020-10-26  5405   \n",
       "\n",
       "        NUM_ITEM  cod novo  IND_OPER  SUB_TIPO  QTD_CAT  QTD_INI  ICMS_INI  \\\n",
       "0             38    195776         0         1        1      0.0       0.0   \n",
       "1             35    195776         0         1        1      NaN       NaN   \n",
       "2              3    195776         1         1        1      NaN       NaN   \n",
       "3             36    195776         0         1        1      NaN       NaN   \n",
       "4             19    195776         1         1        1      NaN       NaN   \n",
       "...          ...       ...       ...       ...      ...      ...       ...   \n",
       "580310        11    939987         0         1        1      NaN       NaN   \n",
       "580311         2    939987         1         1        1      NaN       NaN   \n",
       "580312         1    939987         1         1        1      NaN       NaN   \n",
       "580313         1    939987         1         1        1      NaN       NaN   \n",
       "580314         1    939987         1         1        1      NaN       NaN   \n",
       "\n",
       "        ...   VALOR  Valor Base Cálculo ICMS ST Retido Operação Anterior  \\\n",
       "0       ...    8.46                                               5.37     \n",
       "1       ...    8.46                                               5.37     \n",
       "2       ...   11.99                                               0.00     \n",
       "3       ...    8.46                                               5.37     \n",
       "4       ...   11.99                                               0.00     \n",
       "...     ...     ...                                                ...     \n",
       "580310  ...   76.62                                               0.00     \n",
       "580311  ...  100.90                                               0.00     \n",
       "580312  ...  100.90                                               0.00     \n",
       "580313  ...   90.10                                               0.00     \n",
       "580314  ...  100.90                                               0.00     \n",
       "\n",
       "        Valor Complementar  Valor ICMS Substituição Tributária  \\\n",
       "0                      NaN                                 0.0   \n",
       "1                     8.31                                 0.0   \n",
       "2                      NaN                                 0.0   \n",
       "3                     8.31                                 0.0   \n",
       "4                      NaN                                 0.0   \n",
       "...                    ...                                 ...   \n",
       "580310                0.00                                 0.0   \n",
       "580311                0.00                                 0.0   \n",
       "580312                0.00                                 0.0   \n",
       "580313                0.00                                 0.0   \n",
       "580314                0.00                                 0.0   \n",
       "\n",
       "        Valor ICMS Operação  ICMS_TOT  COD_ITEM  CNPJ EMITENTE     tipo  \\\n",
       "0                       0.0    1.3425    195776   2.784996e+13    grupo   \n",
       "1                       0.0    3.4200    195776   2.784996e+13    grupo   \n",
       "2                       0.0    0.0000    195776            NaN    grupo   \n",
       "3                       0.0    3.4200    195776   2.784996e+13    grupo   \n",
       "4                       0.0    0.0000    195776            NaN    grupo   \n",
       "...                     ...       ...       ...            ...      ...   \n",
       "580310                  0.0    0.0000    935528   6.194029e+13  outros2   \n",
       "580311                  0.0    0.0000    935528            NaN  outros2   \n",
       "580312                  0.0    0.0000    935528            NaN  outros2   \n",
       "580313                  0.0    0.0000    935528            NaN  outros2   \n",
       "580314                  0.0    0.0000    935528            NaN  outros2   \n",
       "\n",
       "             FONTE  \n",
       "0       entrada_sp  \n",
       "1       entrada_sp  \n",
       "2              dfe  \n",
       "3       entrada_sp  \n",
       "4              dfe  \n",
       "...            ...  \n",
       "580310  entrada_sp  \n",
       "580311         dfe  \n",
       "580312         dfe  \n",
       "580313         dfe  \n",
       "580314         dfe  \n",
       "\n",
       "[580315 rows x 44 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2 = pd.read_excel(f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}\\\\ficha_3_loja_{loja}_p{periodo}_parte2_v1.xlsx')\n",
    "df_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2e0d2a75",
   "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>DATA</th>\n",
       "      <th>cod novo</th>\n",
       "      <th>QTD_INI</th>\n",
       "      <th>ICMS_INI</th>\n",
       "      <th>SALDO_FINAL_MES_QTD</th>\n",
       "      <th>SALDO_FINAL_MES_ICMS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-10-08</td>\n",
       "      <td>18</td>\n",
       "      <td>9.0</td>\n",
       "      <td>38.759676</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-10-23</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-10-24</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-10-24</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-10-29</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>580310</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>25.97875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580311</th>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>20.78300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580312</th>\n",
       "      <td>2020-04-13</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.58725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580313</th>\n",
       "      <td>2020-08-30</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.39150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580314</th>\n",
       "      <td>2020-10-26</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.19575</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1396755 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             DATA  cod novo  QTD_INI   ICMS_INI  SALDO_FINAL_MES_QTD  \\\n",
       "0      2019-10-08        18      9.0  38.759676                  NaN   \n",
       "1      2019-10-23        18      NaN        NaN                  NaN   \n",
       "2      2019-10-24        18      NaN        NaN                  NaN   \n",
       "3      2019-10-24        18      NaN        NaN                  NaN   \n",
       "4      2019-10-29        18      NaN        NaN                  NaN   \n",
       "...           ...       ...      ...        ...                  ...   \n",
       "580310 2019-12-28    939987      NaN        NaN                  5.0   \n",
       "580311 2020-02-04    939987      NaN        NaN                  4.0   \n",
       "580312 2020-04-13    939987      NaN        NaN                  3.0   \n",
       "580313 2020-08-30    939987      NaN        NaN                  2.0   \n",
       "580314 2020-10-26    939987      NaN        NaN                  1.0   \n",
       "\n",
       "        SALDO_FINAL_MES_ICMS  \n",
       "0                        NaN  \n",
       "1                        NaN  \n",
       "2                        NaN  \n",
       "3                        NaN  \n",
       "4                        NaN  \n",
       "...                      ...  \n",
       "580310              25.97875  \n",
       "580311              20.78300  \n",
       "580312              15.58725  \n",
       "580313              10.39150  \n",
       "580314               5.19575  \n",
       "\n",
       "[1396755 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.concat([df_1, df_2])\n",
    "df = df[['DATA','cod novo', 'QTD_INI', 'ICMS_INI', 'SALDO_FINAL_MES_QTD', 'SALDO_FINAL_MES_ICMS']]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "01c295af",
   "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>DATA</th>\n",
       "      <th>cod novo</th>\n",
       "      <th>QTD_INI</th>\n",
       "      <th>ICMS_INI</th>\n",
       "      <th>SALDO_FINAL_MES_QTD</th>\n",
       "      <th>SALDO_FINAL_MES_ICMS</th>\n",
       "      <th>REF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-10-08</td>\n",
       "      <td>18</td>\n",
       "      <td>9.0</td>\n",
       "      <td>38.759676</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-10-23</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-10-24</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-10-24</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-10-29</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201910</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580310</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>25.97875</td>\n",
       "      <td>201912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580311</th>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>20.78300</td>\n",
       "      <td>202002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580312</th>\n",
       "      <td>2020-04-13</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.58725</td>\n",
       "      <td>202004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580313</th>\n",
       "      <td>2020-08-30</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.39150</td>\n",
       "      <td>202008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580314</th>\n",
       "      <td>2020-10-26</td>\n",
       "      <td>939987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.19575</td>\n",
       "      <td>202010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1396755 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             DATA  cod novo  QTD_INI   ICMS_INI  SALDO_FINAL_MES_QTD  \\\n",
       "0      2019-10-08        18      9.0  38.759676                  NaN   \n",
       "1      2019-10-23        18      NaN        NaN                  NaN   \n",
       "2      2019-10-24        18      NaN        NaN                  NaN   \n",
       "3      2019-10-24        18      NaN        NaN                  NaN   \n",
       "4      2019-10-29        18      NaN        NaN                  NaN   \n",
       "...           ...       ...      ...        ...                  ...   \n",
       "580310 2019-12-28    939987      NaN        NaN                  5.0   \n",
       "580311 2020-02-04    939987      NaN        NaN                  4.0   \n",
       "580312 2020-04-13    939987      NaN        NaN                  3.0   \n",
       "580313 2020-08-30    939987      NaN        NaN                  2.0   \n",
       "580314 2020-10-26    939987      NaN        NaN                  1.0   \n",
       "\n",
       "        SALDO_FINAL_MES_ICMS     REF  \n",
       "0                        NaN  201910  \n",
       "1                        NaN  201910  \n",
       "2                        NaN  201910  \n",
       "3                        NaN  201910  \n",
       "4                        NaN  201910  \n",
       "...                      ...     ...  \n",
       "580310              25.97875  201912  \n",
       "580311              20.78300  202002  \n",
       "580312              15.58725  202004  \n",
       "580313              10.39150  202008  \n",
       "580314               5.19575  202010  \n",
       "\n",
       "[1396755 rows x 7 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['REF'] = [datetime.strptime(x, '%Y-%m-%d').strftime('%Y%m') for x in df['DATA'].astype(str)]\n",
    "df['REF'] = df['REF'].astype(str)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4e8f3f55",
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped = df.groupby(['cod novo', 'REF']).agg(\n",
    "    QTD_INI=('QTD_INI', 'first'),\n",
    "    ICMS_INI=('ICMS_INI', 'first'),\n",
    "    SALDO_FINAL_MES_QTD=('SALDO_FINAL_MES_QTD', 'last'),\n",
    "    SALDO_FINAL_MES_ICMS=('SALDO_FINAL_MES_ICMS', 'last')\n",
    ").reset_index()\n",
    "\n",
    "df_1050 = grouped.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "70a08d2e",
   "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>cod novo</th>\n",
       "      <th>REF</th>\n",
       "      <th>QTD_INI</th>\n",
       "      <th>ICMS_INI</th>\n",
       "      <th>SALDO_FINAL_MES_QTD</th>\n",
       "      <th>SALDO_FINAL_MES_ICMS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>201910</td>\n",
       "      <td>9.0</td>\n",
       "      <td>38.759676</td>\n",
       "      <td>25.0</td>\n",
       "      <td>140.932749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18</td>\n",
       "      <td>201911</td>\n",
       "      <td>25.0</td>\n",
       "      <td>140.932749</td>\n",
       "      <td>15.0</td>\n",
       "      <td>87.323231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18</td>\n",
       "      <td>201912</td>\n",
       "      <td>15.0</td>\n",
       "      <td>87.323231</td>\n",
       "      <td>27.0</td>\n",
       "      <td>167.489934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18</td>\n",
       "      <td>202001</td>\n",
       "      <td>27.0</td>\n",
       "      <td>167.489934</td>\n",
       "      <td>23.0</td>\n",
       "      <td>142.676610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>202002</td>\n",
       "      <td>23.0</td>\n",
       "      <td>142.676610</td>\n",
       "      <td>11.0</td>\n",
       "      <td>68.663624</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>105308</th>\n",
       "      <td>940043</td>\n",
       "      <td>202010</td>\n",
       "      <td>17.0</td>\n",
       "      <td>58.567737</td>\n",
       "      <td>15.0</td>\n",
       "      <td>51.677415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105309</th>\n",
       "      <td>940043</td>\n",
       "      <td>202011</td>\n",
       "      <td>15.0</td>\n",
       "      <td>51.677415</td>\n",
       "      <td>5.0</td>\n",
       "      <td>17.225805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105310</th>\n",
       "      <td>940043</td>\n",
       "      <td>202012</td>\n",
       "      <td>5.0</td>\n",
       "      <td>17.225805</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105311</th>\n",
       "      <td>940044</td>\n",
       "      <td>202001</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.695967</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.770000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105312</th>\n",
       "      <td>940045</td>\n",
       "      <td>202012</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.940000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105313 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        cod novo     REF  QTD_INI    ICMS_INI  SALDO_FINAL_MES_QTD  \\\n",
       "0             18  201910      9.0   38.759676                 25.0   \n",
       "1             18  201911     25.0  140.932749                 15.0   \n",
       "2             18  201912     15.0   87.323231                 27.0   \n",
       "3             18  202001     27.0  167.489934                 23.0   \n",
       "4             18  202002     23.0  142.676610                 11.0   \n",
       "...          ...     ...      ...         ...                  ...   \n",
       "105308    940043  202010     17.0   58.567737                 15.0   \n",
       "105309    940043  202011     15.0   51.677415                  5.0   \n",
       "105310    940043  202012      5.0   17.225805                  3.0   \n",
       "105311    940044  202001      1.0    1.695967                  1.0   \n",
       "105312    940045  202012      0.0    0.000000                  2.0   \n",
       "\n",
       "        SALDO_FINAL_MES_ICMS  \n",
       "0                 140.932749  \n",
       "1                  87.323231  \n",
       "2                 167.489934  \n",
       "3                 142.676610  \n",
       "4                  68.663624  \n",
       "...                      ...  \n",
       "105308             51.677415  \n",
       "105309             17.225805  \n",
       "105310              0.000000  \n",
       "105311              1.770000  \n",
       "105312              0.940000  \n",
       "\n",
       "[105313 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1050['QTD_INI'] = np.where(df_1050['QTD_INI'].isnull(),\n",
    "                             df_1050['SALDO_FINAL_MES_QTD'].shift(),\n",
    "                             df_1050['QTD_INI'])\n",
    "\n",
    "df_1050['ICMS_INI'] = np.where(df_1050['ICMS_INI'].isnull(),\n",
    "                             df_1050['SALDO_FINAL_MES_ICMS'].shift(),\n",
    "                             df_1050['ICMS_INI'])\n",
    "\n",
    "df_1050['ICMS_INI'] = np.where(df_1050['QTD_INI'] == 0,\n",
    "                              0,\n",
    "                               df_1050['ICMS_INI'])\n",
    "df_1050"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b48ddc7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "if len(df_1050[df_1050['SALDO_FINAL_MES_ICMS'] < 0]) > 0:\n",
    "    print('ATENÇAO. EXISTE SALDO DE ICMS NEGATIVO')\n",
    "    print('ATENÇÃO. FAVOR CHECAR SOBRE VALOR DE SALDO DE ICMS NEGATIVO')\n",
    "    sys.exit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fc4993fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "if len(df_1050[df_1050['SALDO_FINAL_MES_QTD'] < 0]) > 0:\n",
    "    print('ATENÇAO. EXISTE SALDO DE QUANTIDADE NEGATIVO')\n",
    "    print('ATENÇÃO. FAVOR CHECAR SOBRE VALOR DE SALDO DE QUANTIDADE NEGATIVO')\n",
    "    sys.exit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0278a469",
   "metadata": {},
   "outputs": [],
   "source": [
    "if df_1050[(df_1050['QTD_INI'] == 0) & (df_1050['ICMS_INI'] != 0)].shape[0] > 0:\n",
    "    mensagem = 'ATENÇÂO. ICMS DIFERENTE DE ZERO PARA QUANTIDADE IGUAL A ZERO. FAVOR CHECAR'\n",
    "    print(mensagem)\n",
    "    sys.exit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "df359c65",
   "metadata": {},
   "outputs": [],
   "source": [
    "if df_1050[(df_1050['SALDO_FINAL_MES_QTD'] == 0) & (df_1050['SALDO_FINAL_MES_ICMS'] != 0)].shape[0] > 0:\n",
    "    mensagem = 'ATENÇÂO. ICMS DIFERENTE DE ZERO PARA QUANTIDADE IGUAL A ZERO. FAVOR CHECAR'\n",
    "    print(mensagem)\n",
    "    sys.exit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bd54d2be",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1050.to_excel(f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Loja_{loja}\\\\1050_loja_{loja}_p{periodo}_v1.xlsx')"
   ]
  }
 ],
 "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
}