Validacao_TXT.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2c1c614b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importando bibliotecas necessárias\n",
    "\n",
    "import pandas as pd\n",
    "import os\n",
    "import glob\n",
    "import numpy as np\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "00fdc69c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Definindo informações importantes\n",
    "\n",
    "loja = 1\n",
    "periodo = 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6b1cec46",
   "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 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>0</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>23.422914</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</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>1</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>71.877277</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.389510</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>2</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>71.877277</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.389510</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>3</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>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</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>4</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>71.777277</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.389510</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>493477</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>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</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>493478</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>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</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>493479</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>8.606188</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.621547</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>493480</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>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</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>493481</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>0.000000</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",
       "0       35191003129126000159590006031131098680305954 2019-10-24  5405   \n",
       "1       35191027849963000110550010005964131005945510 2019-10-29  1403   \n",
       "2       35191127849963000110550010006084971006065827 2019-11-09  1403   \n",
       "3       35191203129126000159590006031131212496179820 2019-12-05  5405   \n",
       "4       35191227849963000110550010006364621006344734 2019-12-08  1403   \n",
       "...                                              ...        ...   ...   \n",
       "493477  35201203129126000159590007086090676940106578 2020-12-23  5405   \n",
       "493478  35201203129126000159590005812821041405820715 2020-12-24  5405   \n",
       "493479  35201227849963000110550010010262221010226748 2020-12-29  1403   \n",
       "493480  35201203129126000159590007086090696680045137 2020-12-31  5405   \n",
       "493481  35201203129126000159590007086090637396048776 2020-12-05  5405   \n",
       "\n",
       "        NUM_ITEM  cod novo  IND_OPER  SUB_TIPO  QTD_CAT  QTD_INI   ICMS_INI  \\\n",
       "0              3    100073         1         1        1      1.0  23.422914   \n",
       "1             20    100073         0         1        1      NaN        NaN   \n",
       "2              7    100073         0         1        1      NaN        NaN   \n",
       "3              1    100073         1         1        1      NaN        NaN   \n",
       "4             36    100073         0         1        1      NaN        NaN   \n",
       "...          ...       ...       ...       ...      ...      ...        ...   \n",
       "493477         3     99234         1         1        1      NaN        NaN   \n",
       "493478         5     99234         1         1        1      NaN        NaN   \n",
       "493479        19     99234         0         1        1      NaN        NaN   \n",
       "493480         4     99234         1         1        1      NaN        NaN   \n",
       "493481         2     99655         1         1        1      1.0   0.000000   \n",
       "\n",
       "        ...  Valor Base Cálculo ICMS ST Retido Operação Anterior  \\\n",
       "0       ...                                               0.00     \n",
       "1       ...                                              63.62     \n",
       "2       ...                                              63.62     \n",
       "3       ...                                               0.00     \n",
       "4       ...                                              63.72     \n",
       "...     ...                                                ...     \n",
       "493477  ...                                               0.00     \n",
       "493478  ...                                               0.00     \n",
       "493479  ...                                               1.88     \n",
       "493480  ...                                               0.00     \n",
       "493481  ...                                               0.00     \n",
       "\n",
       "        Valor Complementar  Valor ICMS Substituição Tributária  \\\n",
       "0                 0.000000                                 0.0   \n",
       "1                71.877277                                 0.0   \n",
       "2                71.877277                                 0.0   \n",
       "3                 0.000000                                 0.0   \n",
       "4                71.777277                                 0.0   \n",
       "...                    ...                                 ...   \n",
       "493477            0.000000                                 0.0   \n",
       "493478            0.000000                                 0.0   \n",
       "493479            8.606188                                 0.0   \n",
       "493480            0.000000                                 0.0   \n",
       "493481                 NaN                                 0.0   \n",
       "\n",
       "        Valor ICMS Operação   ICMS_TOT  COD_ITEM  CNPJ EMITENTE   tipo  \\\n",
       "0                       0.0   0.000000    100073            NaN  grupo   \n",
       "1                       0.0  24.389510    100073   2.784996e+13  grupo   \n",
       "2                       0.0  24.389510    100073   2.784996e+13  grupo   \n",
       "3                       0.0   0.000000    100073            NaN  grupo   \n",
       "4                       0.0  24.389510    100073   2.784996e+13  grupo   \n",
       "...                     ...        ...       ...            ...    ...   \n",
       "493477                  0.0   0.000000     99234            NaN  grupo   \n",
       "493478                  0.0   0.000000     99234            NaN  grupo   \n",
       "493479                  0.0   2.621547     99234   2.784996e+13  grupo   \n",
       "493480                  0.0   0.000000     99234            NaN  grupo   \n",
       "493481                  0.0   0.000000     99655            NaN    NaN   \n",
       "\n",
       "            vBCST       FONTE  \n",
       "0        0.000000         dfe  \n",
       "1       75.582468  entrada_sp  \n",
       "2       75.582468  entrada_sp  \n",
       "3        0.000000         dfe  \n",
       "4       75.582468  entrada_sp  \n",
       "...           ...         ...  \n",
       "493477   0.000000         dfe  \n",
       "493478   0.000000         dfe  \n",
       "493479   5.632534  entrada_sp  \n",
       "493480   0.000000         dfe  \n",
       "493481   0.000000         dfe  \n",
       "\n",
       "[493482 rows x 45 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ficha_3 = pd.read_excel(f'C:\\Bots\\Bot_transmissao\\Geracao CAT\\Loja_{loja}\\\\ficha_3_loja_{loja}_p{periodo}_v1.xlsx') \n",
    "ficha_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bfffaf56",
   "metadata": {},
   "outputs": [],
   "source": [
    "def recalcular_digito_verificador(nfe_completa):\n",
    "    # Verifica se a NF-e com o dígito verificador tem o tamanho correto\n",
    "    if pd.isnull(nfe_completa) or len(nfe_completa) != 44:\n",
    "        raise ValueError(f\"Valor inválido na coluna 'CHV_DOC': {nfe_completa}\")\n",
    "\n",
    "    # Remove o último caractere (dígito verificador)\n",
    "    nfe_sem_dv = nfe_completa[:-1]\n",
    "\n",
    "    # Restante da lógica de cálculo do dígito verificador\n",
    "    soma = 0\n",
    "    peso = 2\n",
    "    nfe_invertida = nfe_sem_dv[::-1]\n",
    "\n",
    "    for digito in nfe_invertida:\n",
    "        soma += int(digito) * peso\n",
    "        peso += 1\n",
    "        if peso > 9:\n",
    "            peso = 2\n",
    "\n",
    "    resto = soma % 11\n",
    "    dv = 0 if resto == 0 or resto == 1 else 11 - resto\n",
    "\n",
    "    return dv\n",
    "\n",
    "df_0000 = pd.read_excel(f'C:\\\\Bots\\\\Bot_transmissao\\\\Geracao CAT\\\\Referencias\\\\0000_0150_P{periodo}.xlsx', sheet_name='0000', dtype=str)\n",
    "\n",
    "df_0000['DATA'] = df_0000['DATA'].astype(str).str.slice(0, 8)\n",
    "df_0000['DATA'] = [datetime.strptime(x, '%Y%m%d').strftime('%m%Y') for x in df_0000['DATA']]\n",
    "df_0000['COD_VER'] = ['01'] * df_0000.shape[0]\n",
    "df_0000['COD_FIN'] = ['00'] * df_0000.shape[0]\n",
    "df_0000 = df_0000[['LOJA','REG', 'DATA', 'NOME', 'CNPJ', 'IE',\n",
    "        'COD_MUN', 'COD_VER', 'COD_FIN']]\n",
    "\n",
    "df_0000 = df_0000[df_0000['LOJA'] == f'Loja {loja}']\n",
    "\n",
    "\n",
    "cnpj_loja = df_0000[df_0000['LOJA'] == f'Loja {loja}']['CNPJ'].values[0]\n",
    "\n",
    "# ficha_3['CHV_DOC'] = np.where(ficha_3['FONTE'].isin(['entrada_oprprp', 'saida_nfe', 'dfe']),\n",
    "#                            ficha_3['CHV_DOC'].str.slice(0,6) + cnpj_loja + ficha_3['CHV_DOC'].str.slice(20,44),\n",
    "#                            ficha_3['CHV_DOC'])\n",
    "    \n",
    "# ficha_3['DV_NFe'] = ficha_3['CHV_DOC'].apply(recalcular_digito_verificador)\n",
    "# ficha_3['CHV_DOC'] = ficha_3['CHV_DOC'].str.slice(0,-1) + ficha_3['DV_NFe'].astype(str)\n",
    "# ficha_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e670f8ac",
   "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_reg</th>\n",
       "      <th>Chv_doc</th>\n",
       "      <th>data</th>\n",
       "      <th>num_item</th>\n",
       "      <th>ind_oper</th>\n",
       "      <th>cod_prod</th>\n",
       "      <th>cfop</th>\n",
       "      <th>qtd</th>\n",
       "      <th>icms_tot</th>\n",
       "      <th>vlr_confr</th>\n",
       "      <th>cod_legal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200171605265039297590006031131297186579609</td>\n",
       "      <td>01012020</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>100388</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,63</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200171605265039297590006031131301828748473</td>\n",
       "      <td>03012020</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>100388</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,63</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200171605265039297590005812820461046577284</td>\n",
       "      <td>03012020</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>100388</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,63</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200127849963000110550010006604811006584101</td>\n",
       "      <td>04012020</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>100388</td>\n",
       "      <td>1403</td>\n",
       "      <td>1,000</td>\n",
       "      <td>1,34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200171605265039297590006031131310043461524</td>\n",
       "      <td>06012020</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>100388</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,63</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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>1100</td>\n",
       "      <td>35201271605265039297590007086090676940106572</td>\n",
       "      <td>23122020</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>99234</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493478</th>\n",
       "      <td>1100</td>\n",
       "      <td>35201271605265039297590005812821041405820710</td>\n",
       "      <td>24122020</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>99234</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493479</th>\n",
       "      <td>1100</td>\n",
       "      <td>35201227849963000110550010010262221010226748</td>\n",
       "      <td>29122020</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>99234</td>\n",
       "      <td>1403</td>\n",
       "      <td>1,000</td>\n",
       "      <td>2,62</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493480</th>\n",
       "      <td>1100</td>\n",
       "      <td>35201271605265039297590007086090696680045131</td>\n",
       "      <td>31122020</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>99234</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493481</th>\n",
       "      <td>1100</td>\n",
       "      <td>35201271605265039297590007086090637396048770</td>\n",
       "      <td>05122020</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>99655</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>493482 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Cod_reg                                       Chv_doc      data  \\\n",
       "0         1100  35200171605265039297590006031131297186579609  01012020   \n",
       "1         1100  35200171605265039297590006031131301828748473  03012020   \n",
       "2         1100  35200171605265039297590005812820461046577284  03012020   \n",
       "3         1100  35200127849963000110550010006604811006584101  04012020   \n",
       "4         1100  35200171605265039297590006031131310043461524  06012020   \n",
       "...        ...                                           ...       ...   \n",
       "493477    1100  35201271605265039297590007086090676940106572  23122020   \n",
       "493478    1100  35201271605265039297590005812821041405820710  24122020   \n",
       "493479    1100  35201227849963000110550010010262221010226748  29122020   \n",
       "493480    1100  35201271605265039297590007086090696680045131  31122020   \n",
       "493481    1100  35201271605265039297590007086090637396048770  05122020   \n",
       "\n",
       "       num_item ind_oper cod_prod  cfop    qtd icms_tot vlr_confr cod_legal  \n",
       "0             2        1   100388  5405  1,000      NaN      0,63         1  \n",
       "1             3        1   100388  5405  1,000      NaN      0,63         1  \n",
       "2             1        1   100388  5405  1,000      NaN      0,63         1  \n",
       "3            67        0   100388  1403  1,000     1,34       NaN       NaN  \n",
       "4             2        1   100388  5405  1,000      NaN      0,63         1  \n",
       "...         ...      ...      ...   ...    ...      ...       ...       ...  \n",
       "493477        3        1    99234  5405  1,000      NaN      1,12         1  \n",
       "493478        5        1    99234  5405  1,000      NaN      1,12         1  \n",
       "493479       19        0    99234  1403  1,000     2,62       NaN       NaN  \n",
       "493480        4        1    99234  5405  1,000      NaN      1,12         1  \n",
       "493481        2        1    99655  5405  1,000      NaN       NaN         0  \n",
       "\n",
       "[493482 rows x 11 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lista todos os arquivos TXT na pasta\n",
    "pasta_arquivos = f'C:\\Bots\\Bot_transmissao\\Geracao CAT\\Loja_{loja}\\Teste_loja_{loja}'\n",
    "\n",
    "# Filtra apenas os arquivos que começam com '1100'\n",
    "arquivos_1100 = [arquivo for arquivo in os.listdir(pasta_arquivos) if arquivo.startswith('1100')]\n",
    "arquivos_1100\n",
    "# Lista para armazenar os dataframes de cada arquivo\n",
    "dataframes = []\n",
    "\n",
    "# Lê cada arquivo e adiciona ao dataframe\n",
    "for arquivo in arquivos_1100:\n",
    "    df = pd.read_csv(os.path.join(pasta_arquivos, arquivo), sep='|', header=None, \n",
    "                     names=['Cod_reg', 'Chv_doc', 'data', 'num_item', 'ind_oper', \n",
    "                             'cod_prod', 'cfop', 'qtd', 'icms_tot', 'vlr_confr', 'cod_legal'],\n",
    "                    dtype=str)  # Se o arquivo estiver em formato CSV, ajuste os parâmetros conforme necessário\n",
    "    dataframes.append(df)\n",
    "\n",
    "# Concatena todos os dataframes em um único dataframe\n",
    "df_final = pd.concat(dataframes, ignore_index=True)\n",
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a8c32e08",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_2896\\1545256236.py:4: 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",
      "  ficha_3_icms_tot['NUM_ITEM'] = ficha_3_icms_tot['NUM_ITEM'].astype(str)\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_2896\\1545256236.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_1100_icms_tot['num_item'] = df_1100_icms_tot['num_item'].astype(str)\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_2896\\1545256236.py:7: 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",
      "  df_1100_icms_tot['Chv_item'] = df_1100_icms_tot['Chv_doc'] + '-' + df_1100_icms_tot['num_item']\n",
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_2896\\1545256236.py:8: 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",
      "  ficha_3_icms_tot['CHV_ITEM'] = ficha_3_icms_tot['CHV_DOC'] + '-' + ficha_3_icms_tot['NUM_ITEM']\n"
     ]
    }
   ],
   "source": [
    "df_1100_icms_tot = df_final[['Chv_doc', 'num_item', 'icms_tot']]\n",
    "ficha_3_icms_tot = ficha_3[['CHV_DOC', 'NUM_ITEM', 'ICMS_TOT_PCAT']]\n",
    "\n",
    "ficha_3_icms_tot['NUM_ITEM'] = ficha_3_icms_tot['NUM_ITEM'].astype(str)\n",
    "df_1100_icms_tot['num_item'] = df_1100_icms_tot['num_item'].astype(str)\n",
    "\n",
    "df_1100_icms_tot['Chv_item'] = df_1100_icms_tot['Chv_doc'] + '-' + df_1100_icms_tot['num_item']\n",
    "ficha_3_icms_tot['CHV_ITEM'] = ficha_3_icms_tot['CHV_DOC'] + '-' + ficha_3_icms_tot['NUM_ITEM']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c18045ba",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\vinic\\AppData\\Local\\Temp\\ipykernel_2896\\1930534112.py:1: 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",
      "  df_1100_icms_tot['icms_tot'] = df_1100_icms_tot['icms_tot'].str.replace(',', '.').astype(float)\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>Chv_doc</th>\n",
       "      <th>num_item</th>\n",
       "      <th>icms_tot</th>\n",
       "      <th>Chv_item</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35200171605265039297590006031131297186579609</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590006031131297186579609-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35200171605265039297590006031131301828748473</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590006031131301828748473-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35200171605265039297590005812820461046577284</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590005812820461046577284-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35200127849963000110550010006604811006584101</td>\n",
       "      <td>67</td>\n",
       "      <td>1.34</td>\n",
       "      <td>35200127849963000110550010006604811006584101-67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35200171605265039297590006031131310043461524</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590006031131310043461524-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493477</th>\n",
       "      <td>35201271605265039297590007086090676940106572</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590007086090676940106572-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493478</th>\n",
       "      <td>35201271605265039297590005812821041405820710</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590005812821041405820710-5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493479</th>\n",
       "      <td>35201227849963000110550010010262221010226748</td>\n",
       "      <td>19</td>\n",
       "      <td>2.62</td>\n",
       "      <td>35201227849963000110550010010262221010226748-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493480</th>\n",
       "      <td>35201271605265039297590007086090696680045131</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590007086090696680045131-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493481</th>\n",
       "      <td>35201271605265039297590007086090637396048770</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590007086090637396048770-2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>493482 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Chv_doc num_item  icms_tot  \\\n",
       "0       35200171605265039297590006031131297186579609        2       NaN   \n",
       "1       35200171605265039297590006031131301828748473        3       NaN   \n",
       "2       35200171605265039297590005812820461046577284        1       NaN   \n",
       "3       35200127849963000110550010006604811006584101       67      1.34   \n",
       "4       35200171605265039297590006031131310043461524        2       NaN   \n",
       "...                                              ...      ...       ...   \n",
       "493477  35201271605265039297590007086090676940106572        3       NaN   \n",
       "493478  35201271605265039297590005812821041405820710        5       NaN   \n",
       "493479  35201227849963000110550010010262221010226748       19      2.62   \n",
       "493480  35201271605265039297590007086090696680045131        4       NaN   \n",
       "493481  35201271605265039297590007086090637396048770        2       NaN   \n",
       "\n",
       "                                               Chv_item  \n",
       "0        35200171605265039297590006031131297186579609-2  \n",
       "1        35200171605265039297590006031131301828748473-3  \n",
       "2        35200171605265039297590005812820461046577284-1  \n",
       "3       35200127849963000110550010006604811006584101-67  \n",
       "4        35200171605265039297590006031131310043461524-2  \n",
       "...                                                 ...  \n",
       "493477   35201271605265039297590007086090676940106572-3  \n",
       "493478   35201271605265039297590005812821041405820710-5  \n",
       "493479  35201227849963000110550010010262221010226748-19  \n",
       "493480   35201271605265039297590007086090696680045131-4  \n",
       "493481   35201271605265039297590007086090637396048770-2  \n",
       "\n",
       "[493482 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1100_icms_tot['icms_tot'] = df_1100_icms_tot['icms_tot'].str.replace(',', '.').astype(float)\n",
    "df_1100_icms_tot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5cfba4dd",
   "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>num_item</th>\n",
       "      <th>icms_tot</th>\n",
       "      <th>Chv_item</th>\n",
       "      <th>CHV_ITEM</th>\n",
       "      <th>ICMS_TOT_PCAT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35200171605265039297590006031131297186579609</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590006031131297186579609-2</td>\n",
       "      <td>35200171605265039297590006031131297186579609-2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35200171605265039297590006031131301828748473</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590006031131301828748473-3</td>\n",
       "      <td>35200171605265039297590006031131301828748473-3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35200171605265039297590005812820461046577284</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590005812820461046577284-1</td>\n",
       "      <td>35200171605265039297590005812820461046577284-1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35200127849963000110550010006604811006584101</td>\n",
       "      <td>67</td>\n",
       "      <td>1.34</td>\n",
       "      <td>35200127849963000110550010006604811006584101-67</td>\n",
       "      <td>35200127849963000110550010006604811006584101-67</td>\n",
       "      <td>1.335295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35200171605265039297590006031131310043461524</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590006031131310043461524-2</td>\n",
       "      <td>35200171605265039297590006031131310043461524-2</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>493477</th>\n",
       "      <td>35201271605265039297590007086090676940106572</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590007086090676940106572-3</td>\n",
       "      <td>35201271605265039297590007086090676940106572-3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493478</th>\n",
       "      <td>35201271605265039297590005812821041405820710</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590005812821041405820710-5</td>\n",
       "      <td>35201271605265039297590005812821041405820710-5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493479</th>\n",
       "      <td>35201227849963000110550010010262221010226748</td>\n",
       "      <td>19</td>\n",
       "      <td>2.62</td>\n",
       "      <td>35201227849963000110550010010262221010226748-19</td>\n",
       "      <td>35201227849963000110550010010262221010226748-19</td>\n",
       "      <td>2.621547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493480</th>\n",
       "      <td>35201271605265039297590007086090696680045131</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590007086090696680045131-4</td>\n",
       "      <td>35201271605265039297590007086090696680045131-4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493481</th>\n",
       "      <td>35201271605265039297590007086090637396048770</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590007086090637396048770-2</td>\n",
       "      <td>35201271605265039297590007086090637396048770-2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>493482 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Chv_doc num_item  icms_tot  \\\n",
       "0       35200171605265039297590006031131297186579609        2       NaN   \n",
       "1       35200171605265039297590006031131301828748473        3       NaN   \n",
       "2       35200171605265039297590005812820461046577284        1       NaN   \n",
       "3       35200127849963000110550010006604811006584101       67      1.34   \n",
       "4       35200171605265039297590006031131310043461524        2       NaN   \n",
       "...                                              ...      ...       ...   \n",
       "493477  35201271605265039297590007086090676940106572        3       NaN   \n",
       "493478  35201271605265039297590005812821041405820710        5       NaN   \n",
       "493479  35201227849963000110550010010262221010226748       19      2.62   \n",
       "493480  35201271605265039297590007086090696680045131        4       NaN   \n",
       "493481  35201271605265039297590007086090637396048770        2       NaN   \n",
       "\n",
       "                                               Chv_item  \\\n",
       "0        35200171605265039297590006031131297186579609-2   \n",
       "1        35200171605265039297590006031131301828748473-3   \n",
       "2        35200171605265039297590005812820461046577284-1   \n",
       "3       35200127849963000110550010006604811006584101-67   \n",
       "4        35200171605265039297590006031131310043461524-2   \n",
       "...                                                 ...   \n",
       "493477   35201271605265039297590007086090676940106572-3   \n",
       "493478   35201271605265039297590005812821041405820710-5   \n",
       "493479  35201227849963000110550010010262221010226748-19   \n",
       "493480   35201271605265039297590007086090696680045131-4   \n",
       "493481   35201271605265039297590007086090637396048770-2   \n",
       "\n",
       "                                               CHV_ITEM  ICMS_TOT_PCAT  \n",
       "0        35200171605265039297590006031131297186579609-2            NaN  \n",
       "1        35200171605265039297590006031131301828748473-3            NaN  \n",
       "2        35200171605265039297590005812820461046577284-1            NaN  \n",
       "3       35200127849963000110550010006604811006584101-67       1.335295  \n",
       "4        35200171605265039297590006031131310043461524-2            NaN  \n",
       "...                                                 ...            ...  \n",
       "493477   35201271605265039297590007086090676940106572-3            NaN  \n",
       "493478   35201271605265039297590005812821041405820710-5            NaN  \n",
       "493479  35201227849963000110550010010262221010226748-19       2.621547  \n",
       "493480   35201271605265039297590007086090696680045131-4            NaN  \n",
       "493481   35201271605265039297590007086090637396048770-2            NaN  \n",
       "\n",
       "[493482 rows x 6 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = df_1100_icms_tot.merge(ficha_3_icms_tot[['CHV_ITEM', 'ICMS_TOT_PCAT']],\n",
    "                               left_on=['Chv_item'],\n",
    "                               right_on=['CHV_ITEM'])\n",
    "\n",
    "merged = merged.drop_duplicates()\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d113232e",
   "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>num_item</th>\n",
       "      <th>icms_tot</th>\n",
       "      <th>Chv_item</th>\n",
       "      <th>CHV_ITEM</th>\n",
       "      <th>ICMS_TOT_PCAT</th>\n",
       "      <th>Diffs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35200171605265039297590006031131297186579609</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590006031131297186579609-2</td>\n",
       "      <td>35200171605265039297590006031131297186579609-2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35200171605265039297590006031131301828748473</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590006031131301828748473-3</td>\n",
       "      <td>35200171605265039297590006031131301828748473-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35200171605265039297590005812820461046577284</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590005812820461046577284-1</td>\n",
       "      <td>35200171605265039297590005812820461046577284-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35200127849963000110550010006604811006584101</td>\n",
       "      <td>67</td>\n",
       "      <td>1.34</td>\n",
       "      <td>35200127849963000110550010006604811006584101-67</td>\n",
       "      <td>35200127849963000110550010006604811006584101-67</td>\n",
       "      <td>1.335295</td>\n",
       "      <td>0.004705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35200171605265039297590006031131310043461524</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35200171605265039297590006031131310043461524-2</td>\n",
       "      <td>35200171605265039297590006031131310043461524-2</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493477</th>\n",
       "      <td>35201271605265039297590007086090676940106572</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590007086090676940106572-3</td>\n",
       "      <td>35201271605265039297590007086090676940106572-3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493478</th>\n",
       "      <td>35201271605265039297590005812821041405820710</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590005812821041405820710-5</td>\n",
       "      <td>35201271605265039297590005812821041405820710-5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493479</th>\n",
       "      <td>35201227849963000110550010010262221010226748</td>\n",
       "      <td>19</td>\n",
       "      <td>2.62</td>\n",
       "      <td>35201227849963000110550010010262221010226748-19</td>\n",
       "      <td>35201227849963000110550010010262221010226748-19</td>\n",
       "      <td>2.621547</td>\n",
       "      <td>0.001547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493480</th>\n",
       "      <td>35201271605265039297590007086090696680045131</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590007086090696680045131-4</td>\n",
       "      <td>35201271605265039297590007086090696680045131-4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493481</th>\n",
       "      <td>35201271605265039297590007086090637396048770</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35201271605265039297590007086090637396048770-2</td>\n",
       "      <td>35201271605265039297590007086090637396048770-2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>493482 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Chv_doc num_item  icms_tot  \\\n",
       "0       35200171605265039297590006031131297186579609        2       NaN   \n",
       "1       35200171605265039297590006031131301828748473        3       NaN   \n",
       "2       35200171605265039297590005812820461046577284        1       NaN   \n",
       "3       35200127849963000110550010006604811006584101       67      1.34   \n",
       "4       35200171605265039297590006031131310043461524        2       NaN   \n",
       "...                                              ...      ...       ...   \n",
       "493477  35201271605265039297590007086090676940106572        3       NaN   \n",
       "493478  35201271605265039297590005812821041405820710        5       NaN   \n",
       "493479  35201227849963000110550010010262221010226748       19      2.62   \n",
       "493480  35201271605265039297590007086090696680045131        4       NaN   \n",
       "493481  35201271605265039297590007086090637396048770        2       NaN   \n",
       "\n",
       "                                               Chv_item  \\\n",
       "0        35200171605265039297590006031131297186579609-2   \n",
       "1        35200171605265039297590006031131301828748473-3   \n",
       "2        35200171605265039297590005812820461046577284-1   \n",
       "3       35200127849963000110550010006604811006584101-67   \n",
       "4        35200171605265039297590006031131310043461524-2   \n",
       "...                                                 ...   \n",
       "493477   35201271605265039297590007086090676940106572-3   \n",
       "493478   35201271605265039297590005812821041405820710-5   \n",
       "493479  35201227849963000110550010010262221010226748-19   \n",
       "493480   35201271605265039297590007086090696680045131-4   \n",
       "493481   35201271605265039297590007086090637396048770-2   \n",
       "\n",
       "                                               CHV_ITEM  ICMS_TOT_PCAT  \\\n",
       "0        35200171605265039297590006031131297186579609-2            NaN   \n",
       "1        35200171605265039297590006031131301828748473-3            NaN   \n",
       "2        35200171605265039297590005812820461046577284-1            NaN   \n",
       "3       35200127849963000110550010006604811006584101-67       1.335295   \n",
       "4        35200171605265039297590006031131310043461524-2            NaN   \n",
       "...                                                 ...            ...   \n",
       "493477   35201271605265039297590007086090676940106572-3            NaN   \n",
       "493478   35201271605265039297590005812821041405820710-5            NaN   \n",
       "493479  35201227849963000110550010010262221010226748-19       2.621547   \n",
       "493480   35201271605265039297590007086090696680045131-4            NaN   \n",
       "493481   35201271605265039297590007086090637396048770-2            NaN   \n",
       "\n",
       "           Diffs  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3       0.004705  \n",
       "4            NaN  \n",
       "...          ...  \n",
       "493477       NaN  \n",
       "493478       NaN  \n",
       "493479  0.001547  \n",
       "493480       NaN  \n",
       "493481       NaN  \n",
       "\n",
       "[493482 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged['Diffs'] = np.abs(merged['icms_tot'] - merged['ICMS_TOT_PCAT'])\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d450731d",
   "metadata": {},
   "outputs": [],
   "source": [
    "if merged[merged['Diffs'] >= 0.1].shape[0] > 0:\n",
    "    print('Há erro na geração do TXT em relação ao ICMS_TOT_PCAT, favor verificar')\n",
    "    sys.exit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a327468e",
   "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_reg</th>\n",
       "      <th>Chv_doc</th>\n",
       "      <th>data</th>\n",
       "      <th>num_item</th>\n",
       "      <th>ind_oper</th>\n",
       "      <th>cod_prod</th>\n",
       "      <th>cfop</th>\n",
       "      <th>qtd</th>\n",
       "      <th>icms_tot</th>\n",
       "      <th>vlr_confr</th>\n",
       "      <th>cod_legal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200171605265039297590006031131297186579609</td>\n",
       "      <td>01012020</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>100388</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,63</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200171605265039297590006031131301828748473</td>\n",
       "      <td>03012020</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>100388</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,63</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200171605265039297590005812820461046577284</td>\n",
       "      <td>03012020</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>100388</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,63</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200127849963000110550010006604811006584101</td>\n",
       "      <td>04012020</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>100388</td>\n",
       "      <td>1403</td>\n",
       "      <td>1,000</td>\n",
       "      <td>1,34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1100</td>\n",
       "      <td>35200171605265039297590006031131310043461524</td>\n",
       "      <td>06012020</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>100388</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,63</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <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>1100</td>\n",
       "      <td>35201271605265039297590007086090676940106572</td>\n",
       "      <td>23122020</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>99234</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493478</th>\n",
       "      <td>1100</td>\n",
       "      <td>35201271605265039297590005812821041405820710</td>\n",
       "      <td>24122020</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>99234</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493479</th>\n",
       "      <td>1100</td>\n",
       "      <td>35201227849963000110550010010262221010226748</td>\n",
       "      <td>29122020</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>99234</td>\n",
       "      <td>1403</td>\n",
       "      <td>1,000</td>\n",
       "      <td>2,62</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493480</th>\n",
       "      <td>1100</td>\n",
       "      <td>35201271605265039297590007086090696680045131</td>\n",
       "      <td>31122020</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>99234</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493481</th>\n",
       "      <td>1100</td>\n",
       "      <td>35201271605265039297590007086090637396048770</td>\n",
       "      <td>05122020</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>99655</td>\n",
       "      <td>5405</td>\n",
       "      <td>1,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>493482 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Cod_reg                                       Chv_doc      data  \\\n",
       "0         1100  35200171605265039297590006031131297186579609  01012020   \n",
       "1         1100  35200171605265039297590006031131301828748473  03012020   \n",
       "2         1100  35200171605265039297590005812820461046577284  03012020   \n",
       "3         1100  35200127849963000110550010006604811006584101  04012020   \n",
       "4         1100  35200171605265039297590006031131310043461524  06012020   \n",
       "...        ...                                           ...       ...   \n",
       "493477    1100  35201271605265039297590007086090676940106572  23122020   \n",
       "493478    1100  35201271605265039297590005812821041405820710  24122020   \n",
       "493479    1100  35201227849963000110550010010262221010226748  29122020   \n",
       "493480    1100  35201271605265039297590007086090696680045131  31122020   \n",
       "493481    1100  35201271605265039297590007086090637396048770  05122020   \n",
       "\n",
       "       num_item ind_oper cod_prod  cfop    qtd icms_tot vlr_confr cod_legal  \n",
       "0             2        1   100388  5405  1,000      NaN      0,63         1  \n",
       "1             3        1   100388  5405  1,000      NaN      0,63         1  \n",
       "2             1        1   100388  5405  1,000      NaN      0,63         1  \n",
       "3            67        0   100388  1403  1,000     1,34       NaN       NaN  \n",
       "4             2        1   100388  5405  1,000      NaN      0,63         1  \n",
       "...         ...      ...      ...   ...    ...      ...       ...       ...  \n",
       "493477        3        1    99234  5405  1,000      NaN      1,12         1  \n",
       "493478        5        1    99234  5405  1,000      NaN      1,12         1  \n",
       "493479       19        0    99234  1403  1,000     2,62       NaN       NaN  \n",
       "493480        4        1    99234  5405  1,000      NaN      1,12         1  \n",
       "493481        2        1    99655  5405  1,000      NaN       NaN         0  \n",
       "\n",
       "[493482 rows x 11 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "35601343",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Checagem de existência de ICMS_TOT\n",
    "\n",
    "if df_final[(df_final['cfop'].isin(['1403', '1409', '1411', '5411'])) & (df_final['icms_tot'].isnull())].shape[0] > 0:\n",
    "    print('Existem entradas, devoluções de entrada e devoluções de saída com ICMS_TOT nulo. Favor checar')\n",
    "    sys.exit()\n",
    "    \n",
    "if df_final[(df_final['cfop'].isin(['5409', '5405', '5403', '5401'])) & (df_final['icms_tot'].notnull())].shape[0] > 0:\n",
    "    print('Existem saídas com campo ICMS_TOT preenchido. Favor checar')\n",
    "    sys.exit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "0464c544",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Checagem de Valor de confronto\n",
    "\n",
    "if df_final[(df_final['cfop'].isin(['1403', '1409', '5411'])) & (df_final['vlr_confr'].notnull())].shape[0] > 0:\n",
    "    print('Existem entradas e devoluções de entrada com Valor de Confronto não nulo. Favor, checar.')\n",
    "    sys.exit()\n",
    "    \n",
    "if df_final[(df_final['cfop'].isin(['5405', '1411', '5409', '5403', '5401'])) & (df_final['cod_legal'] == '1') & (df_final['vlr_confr'].isnull())].shape[0] > 0:\n",
    "    print('Existem sáidas e devoluções de saída com Valor de Confronto nulo. Favor, checar.')\n",
    "    sys.exit()\n",
    "    \n",
    "if df_final[(df_final['cfop'].isin(['5405', '1411', '5409', '5403', '5401'])) & (df_final['cod_legal'] == '0') & (df_final['vlr_confr'].notnull())].shape[0] > 0:\n",
    "    print('Existem saídas e devoluções de saída com Valor de Confronto não nulo quando Código Legal igual a 0. Favor, checar')\n",
    "    sys.exit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "87522990",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cheagem de código legal\n",
    "\n",
    "if df_final[(df_final['cfop'].isin(['1403', '1409', '5411'])) & (df_final['cod_legal'].notnull())].shape[0] > 0:\n",
    "    print('Existem entradas e devoluções de entrada com Código Legal não nulo. Favor, checar.')\n",
    "    sys.exit()\n",
    "    \n",
    "if df_final[(df_final['cfop'].isin(['5405', '1411', '5409', '5403', '5401'])) & (df_final['cod_legal'].isnull())].shape[0] > 0:\n",
    "    print('Existem sáidas e devoluções de saída com Código Legal nulo. Favor, checar.')\n",
    "    sys.exit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "8f81355e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0,01'], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final[(df_final['cfop'] == '1411') & (df_final['cod_legal'] == '1')]['vlr_confr'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c739ef40",
   "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
}