joao.herculano/Ruptura_Projetada
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Ruptura_Projetada/Ruptura_Eudora/Ruptura_Eudora.ipynb
João Herculano 7221f41ebb mudaças pré formatação do pc
2025-10-24 15:54:54 -03:00

3367 lines
112 KiB
Plaintext
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

{
"cells": [
{
"cell_type": "code",
"execution_count": 2,
"id": "69d095a9",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_17416\\1591507404.py:26: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
" df_base = pd.read_sql(query_base_dados, conn)\n"
]
}
],
"source": [
"import pyodbc\n",
"import configparser\n",
"import pandas as pd\n",
"import numpy as np\n",
"from datetime import datetime, time\n",
" \n",
" \n",
"config = configparser.ConfigParser()\n",
"config.read(\"credenciais_banco.ini\")\n",
" \n",
"# Conexão com o banco\n",
"conn = pyodbc.connect(\n",
" f\"DRIVER={{SQL Server}};\"\n",
" f\"SERVER={config['banco']['host']},1433;\"\n",
" f\"DATABASE=GINSENG;\"\n",
" f\"UID={config['banco']['user']};\"\n",
" f\"PWD={config['banco']['password']}\")\n",
" \n",
"#gerar query\n",
"query_base_dados= '''\n",
"select * from draft_historico dh \n",
"left join ciclos_data_2025 cd on cd.[Date] = dh.data\n",
"where dh.brandgroupcode = 'EUD' and dh.codcategory not in ('SUPORTE À VENDA','EMBALAGENS', 'GIFTS') \n",
"and dh.description not like ('PRM') and dh.data in ('2025-06-25','2025-07-16','2025-08-06','2025-08-27','2025-09-17') and cd.MARCA ='EUD'\n",
"'''\n",
"df_base = pd.read_sql(query_base_dados, conn)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "98add86f",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_17416\\40623521.py:29: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
" df_ddv2 = pd.read_sql(query_DDV, conn)\n"
]
}
],
"source": [
"import pyodbc\n",
"import configparser\n",
"import pandas as pd\n",
"import numpy as np\n",
"from datetime import datetime, time\n",
" \n",
" \n",
"config = configparser.ConfigParser()\n",
"config.read(\"credenciais_banco.ini\")\n",
" \n",
"# Conexão com o banco\n",
"conn = pyodbc.connect(\n",
" f\"DRIVER={{SQL Server}};\"\n",
" f\"SERVER={config['banco']['host']},1433;\"\n",
" f\"DATABASE=GINSENG;\"\n",
" f\"UID={config['banco']['user']};\"\n",
" f\"PWD={config['banco']['password']}\")\n",
" \n",
"#gerar query\n",
"query_DDV= '''\n",
"select cd.Ciclo,emh.PDV ,emh.SKU ,emh.DESCRICAO, emh.CATEGORIA, replace(emh.[DDV PREVISTO],',','.') DDV_PREVISTO from estoque_mar_historico emh \n",
"inner join ciclos_data_2025 cd on cd.[Date] = emh.data_estoque\n",
"inner join (\n",
"select distinct data,loja_id, code from draft_historico\n",
") dh on dh.[data] = emh.data_estoque and dh.code = emh.SKU and emh.pdv = dh.loja_id\n",
"where emh.CATEGORIA not in ('SUPORTE À VENDA','EMBALAGENS', 'GIFTS') AND emh.DESCRICAO NOT LIKE ('PRM') and emh.ORIGEM = 'EUD'\n",
"and emh.data_estoque in('2025-06-26','2025-07-16','2025-08-06','2025-08-27','2025-09-17') and cd.MARCA ='EUD'\n",
"'''\n",
"df_ddv2 = pd.read_sql(query_DDV, conn)"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "023a5bd7",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_17416\\3026141127.py:18: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
" df_rpt = pd.read_sql(query_RPT, conn)\n"
]
}
],
"source": [
"conn = pyodbc.connect(\n",
" f\"DRIVER={{SQL Server}};\"\n",
" f\"SERVER={config['banco']['host']},1433;\"\n",
" f\"DATABASE=GINSENG;\"\n",
" f\"UID={config['banco']['user']};\"\n",
" f\"PWD={config['banco']['password']}\")\n",
" \n",
"#gerar query\n",
"query_RPT= '''\n",
"select \n",
"cd.Ciclo ,emh.PDV ,emh.SKU,count(emh.[ESTOQUE ATUAL] ) dias_em_rpt\n",
"FROM estoque_mar_historico emh \n",
"left join ciclos_data_2025 cd on cd.[Date] = emh.data_estoque\n",
"where emh.[ESTOQUE ATUAL] =0 and cd.MARCA = 'EUD' and emh.data_estoque > '2025-06-25' and emh.ORIGEM = 'EUD'\n",
"group by \n",
"cd.Ciclo ,emh.PDV ,emh.SKU\n",
"'''\n",
"df_rpt = pd.read_sql(query_RPT, conn)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "9ede5fd9",
"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>Ciclo</th>\n",
" <th>PDV</th>\n",
" <th>SKU</th>\n",
" <th>dias_em_rpt</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>C202511</td>\n",
" <td>20997</td>\n",
" <td>72793</td>\n",
" <td>21</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>C202510</td>\n",
" <td>21278</td>\n",
" <td>59936</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>C202513</td>\n",
" <td>20993</td>\n",
" <td>55484</td>\n",
" <td>21</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>C202515</td>\n",
" <td>12820</td>\n",
" <td>55956</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>C202513</td>\n",
" <td>21007</td>\n",
" <td>53848</td>\n",
" <td>21</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Ciclo PDV SKU dias_em_rpt\n",
"0 C202511 20997 72793 21\n",
"1 C202510 21278 59936 1\n",
"2 C202513 20993 55484 21\n",
"3 C202515 12820 55956 7\n",
"4 C202513 21007 53848 21"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_rpt.head()"
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "1fa50331",
"metadata": {},
"outputs": [],
"source": [
"df_base= df_base.rename(columns={'loja_id':'PDV','code':'SKU'})"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "e9f7e8ca",
"metadata": {},
"outputs": [],
"source": [
"df_ddv = pd.merge(df_base,df_ddv2,on=['Ciclo','PDV','SKU'],how='left')"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "a13a2916",
"metadata": {},
"outputs": [],
"source": [
"df_ddv['DDV_PREVISTO'] = df_ddv['DDV_PREVISTO'].astype('float')"
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "222cfc13",
"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>id</th>\n",
" <th>PDV</th>\n",
" <th>SKU</th>\n",
" <th>description</th>\n",
" <th>launch</th>\n",
" <th>deactivation</th>\n",
" <th>thirdtolastcyclesales</th>\n",
" <th>secondtolastcyclesales</th>\n",
" <th>lastcyclesales</th>\n",
" <th>currentcyclesales</th>\n",
" <th>...</th>\n",
" <th>INICIO CICLO</th>\n",
" <th>FIM CICLO</th>\n",
" <th>DURAÇÃO</th>\n",
" <th>MARCA</th>\n",
" <th>Date</th>\n",
" <th>id</th>\n",
" <th>DESCRICAO</th>\n",
" <th>CATEGORIA</th>\n",
" <th>DDV_PREVISTO</th>\n",
" <th>dias_em_rpt</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>667507</td>\n",
" <td>21495</td>\n",
" <td>53398</td>\n",
" <td>SOUL BAT MATE MARROM TENDENCIA V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>15/07/2025</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>667512</td>\n",
" <td>21495</td>\n",
" <td>70882</td>\n",
" <td>SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml</td>\n",
" <td></td>\n",
" <td>C12</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>15/07/2025</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>667513</td>\n",
" <td>21495</td>\n",
" <td>73687</td>\n",
" <td>SOUL DEMAQ CREME 60ML</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>15/07/2025</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>1.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>667527</td>\n",
" <td>20997</td>\n",
" <td>51775</td>\n",
" <td>EUD BABY SHAMP 200ml V2</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>15/07/2025</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>667550</td>\n",
" <td>21495</td>\n",
" <td>52332</td>\n",
" <td>SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>15/07/2025</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 46 columns</p>\n",
"</div>"
],
"text/plain": [
" id PDV SKU description launch \\\n",
"0 667507 21495 53398 SOUL BAT MATE MARROM TENDENCIA V2 3,7g \n",
"1 667512 21495 70882 SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml \n",
"2 667513 21495 73687 SOUL DEMAQ CREME 60ML \n",
"3 667527 20997 51775 EUD BABY SHAMP 200ml V2 \n",
"4 667550 21495 52332 SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g \n",
"\n",
" deactivation thirdtolastcyclesales secondtolastcyclesales lastcyclesales \\\n",
"0 0 0 0 \n",
"1 C12 0 0 0 \n",
"2 0 0 0 \n",
"3 0 0 0 \n",
"4 0 0 0 \n",
"\n",
" currentcyclesales ... INICIO CICLO FIM CICLO DURAÇÃO MARCA \\\n",
"0 0 ... 25/06/2025 15/07/2025 21 EUD \n",
"1 0 ... 25/06/2025 15/07/2025 21 EUD \n",
"2 0 ... 25/06/2025 15/07/2025 21 EUD \n",
"3 0 ... 25/06/2025 15/07/2025 21 EUD \n",
"4 0 ... 25/06/2025 15/07/2025 21 EUD \n",
"\n",
" Date id DESCRICAO CATEGORIA DDV_PREVISTO dias_em_rpt \n",
"0 25/06/2025 2379 NaN NaN NaN 0.0 \n",
"1 25/06/2025 2379 NaN NaN NaN 0.0 \n",
"2 25/06/2025 2379 NaN NaN NaN 1.0 \n",
"3 25/06/2025 2379 NaN NaN NaN 0.0 \n",
"4 25/06/2025 2379 NaN NaN NaN 0.0 \n",
"\n",
"[5 rows x 46 columns]"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_ruptura = pd.merge(df_ddv,df_rpt,on=['Ciclo','PDV','SKU'],how='left')\n",
"\n",
"df_ruptura['dias_em_rpt'] = df_ruptura['dias_em_rpt'].fillna(0)\n",
"\n",
"df_ruptura.head()"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "176f81a3",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"np.float64(16025.960000000001)"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_ruptura['DDV_PREVISTO'] = df_ruptura['DDV_PREVISTO'].replace('None','0')\n",
"\n",
"df_ruptura['DDV_PREVISTO'] = df_ruptura['DDV_PREVISTO'].astype('float')\n",
"\n",
"df_ruptura['DDV_PREVISTO'].sum()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "34513deb",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_17416\\1097998051.py:28: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
" df_vendas2 = pd.read_sql(query_vendas, conn)\n"
]
}
],
"source": [
"import pyodbc\n",
"import configparser\n",
"import pandas as pd\n",
"import numpy as np\n",
"from datetime import datetime, time\n",
" \n",
" \n",
"config = configparser.ConfigParser()\n",
"config.read(\"credenciais_banco.ini\")\n",
" \n",
"# Conexão com o banco\n",
"conn = pyodbc.connect(\n",
" f\"DRIVER={{SQL Server}};\"\n",
" f\"SERVER={config['banco']['host']},1433;\"\n",
" f\"DATABASE=GINSENG;\"\n",
" f\"UID={config['banco']['user']};\"\n",
" f\"PWD={config['banco']['password']}\")\n",
"\n",
"query_vendas = '''\n",
"select \n",
"cd.Ciclo ,bvb.PDV ,bvb.sku,replace(bvb.vendas,'.','') as vendas_ciclo\n",
"FROM base_vendas_bi bvb\n",
"inner join ciclos_data_2025 cd on cd.[Date] = cast(bvb.[DATA] as date)\n",
"left join estoque_mar em on em.pdv = bvb.pdv and bvb.sku=em.SKU \n",
"where cd.MARCA = 'EUD' and bvb.[DATA] > '2025-06-24' and em.ORIGEM = 'EUD'\n",
"\n",
"'''\n",
"df_vendas2 = pd.read_sql(query_vendas, conn)"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "655d9bb7",
"metadata": {},
"outputs": [],
"source": [
"df_vendas2['vendas_ciclo'] = df_vendas2['vendas_ciclo'].astype('Int64')"
]
},
{
"cell_type": "code",
"execution_count": 13,
"id": "94df55b2",
"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>Ciclo</th>\n",
" <th>PDV</th>\n",
" <th>sku</th>\n",
" <th>vendas_ciclo</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>C202510</td>\n",
" <td>20968</td>\n",
" <td>59313</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>C202510</td>\n",
" <td>20968</td>\n",
" <td>91715</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>C202510</td>\n",
" <td>20969</td>\n",
" <td>91715</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>C202510</td>\n",
" <td>20991</td>\n",
" <td>91448</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>C202510</td>\n",
" <td>20991</td>\n",
" <td>91540</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Ciclo PDV sku vendas_ciclo\n",
"0 C202510 20968 59313 4\n",
"1 C202510 20968 91715 2\n",
"2 C202510 20969 91715 2\n",
"3 C202510 20991 91448 0\n",
"4 C202510 20991 91540 0"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_vendas = df_vendas2.groupby(['Ciclo','PDV','sku'])['vendas_ciclo'].sum().reset_index()\n",
"\n",
"df_vendas.head()"
]
},
{
"cell_type": "code",
"execution_count": 14,
"id": "bbb0cd45",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"np.int64(20754)"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_vendas2['vendas_ciclo'].sum()"
]
},
{
"cell_type": "code",
"execution_count": 15,
"id": "5e68452d",
"metadata": {},
"outputs": [],
"source": [
"df_preco = pd.read_excel(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\SUPRIMENTOS\\Tabela_Preco\\PREÇO VENDA EUDORA.xlsx\")\n",
"\n",
"df_preco = df_preco[['Codigo','Venda']]\n",
"\n",
"df_preco = df_preco.rename(columns={'Codigo':'Código','Venda':'Valor'})\n",
"\n",
"df_preco = df_preco.groupby('Código')['Valor'].max().reset_index()"
]
},
{
"cell_type": "code",
"execution_count": 16,
"id": "76c57096",
"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>Código</th>\n",
" <th>Valor</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>1004</td>\n",
" <td>159.90</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1062</td>\n",
" <td>226.79</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>1063</td>\n",
" <td>1.99</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>1073</td>\n",
" <td>226.79</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>1078</td>\n",
" <td>67.90</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Código Valor\n",
"0 1004 159.90\n",
"1 1062 226.79\n",
"2 1063 1.99\n",
"3 1073 226.79\n",
"4 1078 67.90"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_preco.head()"
]
},
{
"cell_type": "code",
"execution_count": 17,
"id": "015eecee",
"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>Ciclo</th>\n",
" <th>PDV</th>\n",
" <th>sku</th>\n",
" <th>vendas_ciclo</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>C202510</td>\n",
" <td>20968</td>\n",
" <td>59313</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>C202510</td>\n",
" <td>20968</td>\n",
" <td>91715</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>C202510</td>\n",
" <td>20969</td>\n",
" <td>91715</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>C202510</td>\n",
" <td>20991</td>\n",
" <td>91448</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>C202510</td>\n",
" <td>20991</td>\n",
" <td>91540</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Ciclo PDV sku vendas_ciclo\n",
"0 C202510 20968 59313 4\n",
"1 C202510 20968 91715 2\n",
"2 C202510 20969 91715 2\n",
"3 C202510 20991 91448 0\n",
"4 C202510 20991 91540 0"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_vendas['sku'] = df_vendas['sku'].astype('str')\n",
"\n",
"df_vendas.head()"
]
},
{
"cell_type": "code",
"execution_count": 18,
"id": "8b898919",
"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>id</th>\n",
" <th>PDV</th>\n",
" <th>SKU</th>\n",
" <th>description</th>\n",
" <th>launch</th>\n",
" <th>deactivation</th>\n",
" <th>thirdtolastcyclesales</th>\n",
" <th>secondtolastcyclesales</th>\n",
" <th>lastcyclesales</th>\n",
" <th>currentcyclesales</th>\n",
" <th>...</th>\n",
" <th>DURAÇÃO</th>\n",
" <th>MARCA</th>\n",
" <th>Date</th>\n",
" <th>id</th>\n",
" <th>DESCRICAO</th>\n",
" <th>CATEGORIA</th>\n",
" <th>DDV_PREVISTO</th>\n",
" <th>dias_em_rpt</th>\n",
" <th>sku</th>\n",
" <th>vendas_ciclo</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>667507</td>\n",
" <td>21495</td>\n",
" <td>53398</td>\n",
" <td>SOUL BAT MATE MARROM TENDENCIA V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>667512</td>\n",
" <td>21495</td>\n",
" <td>70882</td>\n",
" <td>SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml</td>\n",
" <td></td>\n",
" <td>C12</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>667513</td>\n",
" <td>21495</td>\n",
" <td>73687</td>\n",
" <td>SOUL DEMAQ CREME 60ML</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>1.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>667527</td>\n",
" <td>20997</td>\n",
" <td>51775</td>\n",
" <td>EUD BABY SHAMP 200ml V2</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>667550</td>\n",
" <td>21495</td>\n",
" <td>52332</td>\n",
" <td>SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</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>86800</th>\n",
" <td>12391049</td>\n",
" <td>24269</td>\n",
" <td>92676</td>\n",
" <td>SOUL PO COMP FAC ULT MATE COR 90 10g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>17/09/2025</td>\n",
" <td>2463</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>86801</th>\n",
" <td>12391050</td>\n",
" <td>24269</td>\n",
" <td>92677</td>\n",
" <td>SOUL PO COMP FAC ULT MATE COR 95 10g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>17/09/2025</td>\n",
" <td>2463</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>86802</th>\n",
" <td>9235561</td>\n",
" <td>21495</td>\n",
" <td>49166</td>\n",
" <td>VOLPE DES COL DNA 100ml</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>27/08/2025</td>\n",
" <td>2442</td>\n",
" <td>VOLPE DES COL DNA 100ml</td>\n",
" <td>PERFUMARIA</td>\n",
" <td>0.02</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>86803</th>\n",
" <td>9235562</td>\n",
" <td>21495</td>\n",
" <td>52299</td>\n",
" <td>VOLPE DES COL NEXT 100ml</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>27/08/2025</td>\n",
" <td>2442</td>\n",
" <td>VOLPE DES COL NEXT 100ml</td>\n",
" <td>PERFUMARIA</td>\n",
" <td>0.05</td>\n",
" <td>0.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</td>\n",
" </tr>\n",
" <tr>\n",
" <th>86804</th>\n",
" <td>9235563</td>\n",
" <td>21495</td>\n",
" <td>56231</td>\n",
" <td>VOLPE DES COL TRIUMPH 100ml</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>21</td>\n",
" <td>EUD</td>\n",
" <td>27/08/2025</td>\n",
" <td>2442</td>\n",
" <td>VOLPE DES COL TRIUMPH 100ml</td>\n",
" <td>PERFUMARIA</td>\n",
" <td>0.13</td>\n",
" <td>13.0</td>\n",
" <td>NaN</td>\n",
" <td>&lt;NA&gt;</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>86805 rows × 48 columns</p>\n",
"</div>"
],
"text/plain": [
" id PDV SKU description \\\n",
"0 667507 21495 53398 SOUL BAT MATE MARROM TENDENCIA V2 3,7g \n",
"1 667512 21495 70882 SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml \n",
"2 667513 21495 73687 SOUL DEMAQ CREME 60ML \n",
"3 667527 20997 51775 EUD BABY SHAMP 200ml V2 \n",
"4 667550 21495 52332 SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g \n",
"... ... ... ... ... \n",
"86800 12391049 24269 92676 SOUL PO COMP FAC ULT MATE COR 90 10g \n",
"86801 12391050 24269 92677 SOUL PO COMP FAC ULT MATE COR 95 10g \n",
"86802 9235561 21495 49166 VOLPE DES COL DNA 100ml \n",
"86803 9235562 21495 52299 VOLPE DES COL NEXT 100ml \n",
"86804 9235563 21495 56231 VOLPE DES COL TRIUMPH 100ml \n",
"\n",
" launch deactivation thirdtolastcyclesales secondtolastcyclesales \\\n",
"0 0 0 \n",
"1 C12 0 0 \n",
"2 0 0 \n",
"3 0 0 \n",
"4 0 0 \n",
"... ... ... ... ... \n",
"86800 0 0 \n",
"86801 0 0 \n",
"86802 0 0 \n",
"86803 0 0 \n",
"86804 0 0 \n",
"\n",
" lastcyclesales currentcyclesales ... DURAÇÃO MARCA Date \\\n",
"0 0 0 ... 21 EUD 25/06/2025 \n",
"1 0 0 ... 21 EUD 25/06/2025 \n",
"2 0 0 ... 21 EUD 25/06/2025 \n",
"3 0 0 ... 21 EUD 25/06/2025 \n",
"4 0 0 ... 21 EUD 25/06/2025 \n",
"... ... ... ... ... ... ... \n",
"86800 0 0 ... 21 EUD 17/09/2025 \n",
"86801 0 0 ... 21 EUD 17/09/2025 \n",
"86802 0 0 ... 21 EUD 27/08/2025 \n",
"86803 0 0 ... 21 EUD 27/08/2025 \n",
"86804 0 0 ... 21 EUD 27/08/2025 \n",
"\n",
" id DESCRICAO CATEGORIA DDV_PREVISTO \\\n",
"0 2379 NaN NaN NaN \n",
"1 2379 NaN NaN NaN \n",
"2 2379 NaN NaN NaN \n",
"3 2379 NaN NaN NaN \n",
"4 2379 NaN NaN NaN \n",
"... ... ... ... ... \n",
"86800 2463 NaN NaN NaN \n",
"86801 2463 NaN NaN NaN \n",
"86802 2442 VOLPE DES COL DNA 100ml PERFUMARIA 0.02 \n",
"86803 2442 VOLPE DES COL NEXT 100ml PERFUMARIA 0.05 \n",
"86804 2442 VOLPE DES COL TRIUMPH 100ml PERFUMARIA 0.13 \n",
"\n",
" dias_em_rpt sku vendas_ciclo \n",
"0 0.0 NaN <NA> \n",
"1 0.0 NaN <NA> \n",
"2 1.0 NaN <NA> \n",
"3 0.0 NaN <NA> \n",
"4 0.0 NaN <NA> \n",
"... ... ... ... \n",
"86800 0.0 NaN <NA> \n",
"86801 0.0 NaN <NA> \n",
"86802 0.0 NaN <NA> \n",
"86803 0.0 NaN <NA> \n",
"86804 13.0 NaN <NA> \n",
"\n",
"[86805 rows x 48 columns]"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado = pd.merge(df_ruptura,df_vendas,left_on=['Ciclo','PDV','SKU'],right_on=['Ciclo','PDV','sku'],how='left')\n",
"\n",
"df_unificado"
]
},
{
"cell_type": "code",
"execution_count": 19,
"id": "23f09de2",
"metadata": {},
"outputs": [],
"source": [
"df_preco['Código'] = df_preco['Código'].astype('str')"
]
},
{
"cell_type": "code",
"execution_count": 20,
"id": "ef12cc46",
"metadata": {},
"outputs": [],
"source": [
"df_unificado = pd.merge(df_unificado,df_preco,left_on=['SKU'],right_on='Código',how='left')\n"
]
},
{
"cell_type": "code",
"execution_count": 21,
"id": "122f3a97",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_17416\\2458800028.py:1: FutureWarning: Downcasting object dtype arrays on .fillna, .ffill, .bfill is deprecated and will change in a future version. Call result.infer_objects(copy=False) instead. To opt-in to the future behavior, set `pd.set_option('future.no_silent_downcasting', True)`\n",
" df_unificado=df_unificado.fillna(0)\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>id</th>\n",
" <th>PDV</th>\n",
" <th>SKU</th>\n",
" <th>description</th>\n",
" <th>launch</th>\n",
" <th>deactivation</th>\n",
" <th>thirdtolastcyclesales</th>\n",
" <th>secondtolastcyclesales</th>\n",
" <th>lastcyclesales</th>\n",
" <th>currentcyclesales</th>\n",
" <th>...</th>\n",
" <th>Date</th>\n",
" <th>id</th>\n",
" <th>DESCRICAO</th>\n",
" <th>CATEGORIA</th>\n",
" <th>DDV_PREVISTO</th>\n",
" <th>dias_em_rpt</th>\n",
" <th>sku</th>\n",
" <th>vendas_ciclo</th>\n",
" <th>Código</th>\n",
" <th>Valor</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>667507</td>\n",
" <td>21495</td>\n",
" <td>53398</td>\n",
" <td>SOUL BAT MATE MARROM TENDENCIA V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>53398</td>\n",
" <td>35.99</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>667512</td>\n",
" <td>21495</td>\n",
" <td>70882</td>\n",
" <td>SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml</td>\n",
" <td></td>\n",
" <td>C12</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>70882</td>\n",
" <td>39.99</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>667513</td>\n",
" <td>21495</td>\n",
" <td>73687</td>\n",
" <td>SOUL DEMAQ CREME 60ML</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>73687</td>\n",
" <td>32.99</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>667527</td>\n",
" <td>20997</td>\n",
" <td>51775</td>\n",
" <td>EUD BABY SHAMP 200ml V2</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>51775</td>\n",
" <td>72.98</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>667550</td>\n",
" <td>21495</td>\n",
" <td>52332</td>\n",
" <td>SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>25/06/2025</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>52332</td>\n",
" <td>35.99</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 50 columns</p>\n",
"</div>"
],
"text/plain": [
" id PDV SKU description launch \\\n",
"0 667507 21495 53398 SOUL BAT MATE MARROM TENDENCIA V2 3,7g \n",
"1 667512 21495 70882 SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml \n",
"2 667513 21495 73687 SOUL DEMAQ CREME 60ML \n",
"3 667527 20997 51775 EUD BABY SHAMP 200ml V2 \n",
"4 667550 21495 52332 SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g \n",
"\n",
" deactivation thirdtolastcyclesales secondtolastcyclesales lastcyclesales \\\n",
"0 0 0 0 \n",
"1 C12 0 0 0 \n",
"2 0 0 0 \n",
"3 0 0 0 \n",
"4 0 0 0 \n",
"\n",
" currentcyclesales ... Date id DESCRICAO CATEGORIA \\\n",
"0 0 ... 25/06/2025 2379 0 0 \n",
"1 0 ... 25/06/2025 2379 0 0 \n",
"2 0 ... 25/06/2025 2379 0 0 \n",
"3 0 ... 25/06/2025 2379 0 0 \n",
"4 0 ... 25/06/2025 2379 0 0 \n",
"\n",
" DDV_PREVISTO dias_em_rpt sku vendas_ciclo Código Valor \n",
"0 0.0 0.0 0 0 53398 35.99 \n",
"1 0.0 0.0 0 0 70882 39.99 \n",
"2 0.0 1.0 0 0 73687 32.99 \n",
"3 0.0 0.0 0 0 51775 72.98 \n",
"4 0.0 0.0 0 0 52332 35.99 \n",
"\n",
"[5 rows x 50 columns]"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado=df_unificado.fillna(0)\n",
"\n",
"df_unificado.head()"
]
},
{
"cell_type": "code",
"execution_count": 22,
"id": "a3fa8b15",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_17416\\4239392508.py:14: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
" df_crit = pd.read_sql(query_crit, conn)\n"
]
}
],
"source": [
"query_crit = '''SELECT\n",
"cd.Ciclo , \n",
"dh.loja_id AS PDV,\n",
"dh.code as Produto,\n",
"COUNT(dh.criticalitem_iscritical ) AS dias_critico\n",
"FROM draft_historico dh\n",
"INNER JOIN ciclos_data_2025 cd ON cd.[Date] = dh.[data] AND cd.MARCA ='EUD'\n",
"WHERE dh.criticalitem_iscritical = 1\n",
"GROUP BY\n",
"cd.Ciclo ,\n",
"dh.loja_id,\n",
"dh.code'''\n",
"\n",
"df_crit = pd.read_sql(query_crit, conn)"
]
},
{
"cell_type": "code",
"execution_count": 23,
"id": "3e488ef9",
"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>Ciclo</th>\n",
" <th>PDV</th>\n",
" <th>Produto</th>\n",
" <th>dias_critico</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>C202509</td>\n",
" <td>22541</td>\n",
" <td>40121</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>C202509</td>\n",
" <td>21001</td>\n",
" <td>77773</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>C202510</td>\n",
" <td>21068</td>\n",
" <td>86756</td>\n",
" <td>11</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>C202510</td>\n",
" <td>21001</td>\n",
" <td>77899</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>C202515</td>\n",
" <td>20997</td>\n",
" <td>79483</td>\n",
" <td>10</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Ciclo PDV Produto dias_critico\n",
"0 C202509 22541 40121 1\n",
"1 C202509 21001 77773 5\n",
"2 C202510 21068 86756 11\n",
"3 C202510 21001 77899 1\n",
"4 C202515 20997 79483 10"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_crit.head()"
]
},
{
"cell_type": "code",
"execution_count": 24,
"id": "e8505191",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(138472, 4)"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_crit.shape"
]
},
{
"cell_type": "code",
"execution_count": 25,
"id": "a31e0221",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Index(['id', 'PDV', 'SKU', 'description', 'launch', 'deactivation',\n",
" 'thirdtolastcyclesales', 'secondtolastcyclesales', 'lastcyclesales',\n",
" 'currentcyclesales', 'nextcycleprojection',\n",
" 'secondtonextcycleprojection', 'stock_actual', 'stock_intransit',\n",
" 'purchasesuggestion', 'smartpurchase_purchasesuggestioncycle',\n",
" 'smartpurchase_nextcyclepurchasesuggestion', 'pendingorder',\n",
" 'salescurve', 'promotions_description', 'promotions_discountpercent',\n",
" 'pricesellin', 'businessunit', 'codcategory',\n",
" 'criticalitem_dtprovidedregularization', 'criticalitem_blockedwallet',\n",
" 'criticalitem_iscritical', 'codsubcategory', 'isproductdeactivated',\n",
" 'brandgroupcode', 'dayswithoutsales', 'coveragedays', 'hascoverage',\n",
" 'TRIAL949', 'data', 'Ciclo', 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO',\n",
" 'MARCA', 'Date', 'id', 'DESCRICAO', 'CATEGORIA', 'DDV_PREVISTO',\n",
" 'dias_em_rpt', 'sku', 'vendas_ciclo', 'Código', 'Valor'],\n",
" dtype='object')"
]
},
"execution_count": 25,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado.columns"
]
},
{
"cell_type": "code",
"execution_count": 26,
"id": "2e643812",
"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>id</th>\n",
" <th>PDV</th>\n",
" <th>SKU</th>\n",
" <th>description</th>\n",
" <th>launch</th>\n",
" <th>deactivation</th>\n",
" <th>thirdtolastcyclesales</th>\n",
" <th>secondtolastcyclesales</th>\n",
" <th>lastcyclesales</th>\n",
" <th>currentcyclesales</th>\n",
" <th>...</th>\n",
" <th>id</th>\n",
" <th>DESCRICAO</th>\n",
" <th>CATEGORIA</th>\n",
" <th>DDV_PREVISTO</th>\n",
" <th>dias_em_rpt</th>\n",
" <th>sku</th>\n",
" <th>vendas_ciclo</th>\n",
" <th>Código</th>\n",
" <th>Valor</th>\n",
" <th>dias_critico</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>667507</td>\n",
" <td>21495</td>\n",
" <td>53398</td>\n",
" <td>SOUL BAT MATE MARROM TENDENCIA V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>53398</td>\n",
" <td>35.99</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>667512</td>\n",
" <td>21495</td>\n",
" <td>70882</td>\n",
" <td>SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml</td>\n",
" <td></td>\n",
" <td>C12</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>70882</td>\n",
" <td>39.99</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>667513</td>\n",
" <td>21495</td>\n",
" <td>73687</td>\n",
" <td>SOUL DEMAQ CREME 60ML</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>73687</td>\n",
" <td>32.99</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>667527</td>\n",
" <td>20997</td>\n",
" <td>51775</td>\n",
" <td>EUD BABY SHAMP 200ml V2</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>51775</td>\n",
" <td>72.98</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>667550</td>\n",
" <td>21495</td>\n",
" <td>52332</td>\n",
" <td>SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>2379</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>52332</td>\n",
" <td>35.99</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 51 columns</p>\n",
"</div>"
],
"text/plain": [
" id PDV SKU description launch \\\n",
"0 667507 21495 53398 SOUL BAT MATE MARROM TENDENCIA V2 3,7g \n",
"1 667512 21495 70882 SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml \n",
"2 667513 21495 73687 SOUL DEMAQ CREME 60ML \n",
"3 667527 20997 51775 EUD BABY SHAMP 200ml V2 \n",
"4 667550 21495 52332 SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g \n",
"\n",
" deactivation thirdtolastcyclesales secondtolastcyclesales lastcyclesales \\\n",
"0 0 0 0 \n",
"1 C12 0 0 0 \n",
"2 0 0 0 \n",
"3 0 0 0 \n",
"4 0 0 0 \n",
"\n",
" currentcyclesales ... id DESCRICAO CATEGORIA DDV_PREVISTO \\\n",
"0 0 ... 2379 0 0 0.0 \n",
"1 0 ... 2379 0 0 0.0 \n",
"2 0 ... 2379 0 0 0.0 \n",
"3 0 ... 2379 0 0 0.0 \n",
"4 0 ... 2379 0 0 0.0 \n",
"\n",
" dias_em_rpt sku vendas_ciclo Código Valor dias_critico \n",
"0 0.0 0 0 53398 35.99 0.0 \n",
"1 0.0 0 0 70882 39.99 0.0 \n",
"2 1.0 0 0 73687 32.99 0.0 \n",
"3 0.0 0 0 51775 72.98 0.0 \n",
"4 0.0 0 0 52332 35.99 0.0 \n",
"\n",
"[5 rows x 51 columns]"
]
},
"execution_count": 26,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado2 = pd.merge(df_unificado,df_crit, left_on = ['Ciclo','PDV','SKU'], right_on=['Ciclo','PDV','Produto'] ,how='left')\n",
"\n",
"df_unificado2 = df_unificado2.drop(columns='Produto')\n",
"\n",
"df_unificado2['dias_critico'] = df_unificado2['dias_critico'].fillna(0)\n",
"\n",
"df_unificado2.head()"
]
},
{
"cell_type": "code",
"execution_count": 27,
"id": "361f871a",
"metadata": {},
"outputs": [],
"source": [
"df_pdv = pd.read_excel('PDV_ATT.xlsx')"
]
},
{
"cell_type": "code",
"execution_count": 28,
"id": "a71fdeaf",
"metadata": {},
"outputs": [],
"source": [
"df_pdv['PDV'] = df_pdv['PDV'].astype('str')\n"
]
},
{
"cell_type": "code",
"execution_count": 29,
"id": "424ecbef",
"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>id</th>\n",
" <th>PDV</th>\n",
" <th>SKU</th>\n",
" <th>description</th>\n",
" <th>launch</th>\n",
" <th>deactivation</th>\n",
" <th>thirdtolastcyclesales</th>\n",
" <th>secondtolastcyclesales</th>\n",
" <th>lastcyclesales</th>\n",
" <th>currentcyclesales</th>\n",
" <th>...</th>\n",
" <th>CATEGORIA</th>\n",
" <th>DDV_PREVISTO</th>\n",
" <th>dias_em_rpt</th>\n",
" <th>sku</th>\n",
" <th>vendas_ciclo</th>\n",
" <th>Código</th>\n",
" <th>Valor</th>\n",
" <th>dias_critico</th>\n",
" <th>UF</th>\n",
" <th>CANAL</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>667507</td>\n",
" <td>21495</td>\n",
" <td>53398</td>\n",
" <td>SOUL BAT MATE MARROM TENDENCIA V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>53398</td>\n",
" <td>35.99</td>\n",
" <td>0.0</td>\n",
" <td>SE</td>\n",
" <td>HIB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>667512</td>\n",
" <td>21495</td>\n",
" <td>70882</td>\n",
" <td>SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml</td>\n",
" <td></td>\n",
" <td>C12</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>70882</td>\n",
" <td>39.99</td>\n",
" <td>0.0</td>\n",
" <td>SE</td>\n",
" <td>HIB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>667513</td>\n",
" <td>21495</td>\n",
" <td>73687</td>\n",
" <td>SOUL DEMAQ CREME 60ML</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>73687</td>\n",
" <td>32.99</td>\n",
" <td>0.0</td>\n",
" <td>SE</td>\n",
" <td>HIB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>667527</td>\n",
" <td>20997</td>\n",
" <td>51775</td>\n",
" <td>EUD BABY SHAMP 200ml V2</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>51775</td>\n",
" <td>72.98</td>\n",
" <td>0.0</td>\n",
" <td>AL</td>\n",
" <td>VD</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>667550</td>\n",
" <td>21495</td>\n",
" <td>52332</td>\n",
" <td>SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>52332</td>\n",
" <td>35.99</td>\n",
" <td>0.0</td>\n",
" <td>SE</td>\n",
" <td>HIB</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 53 columns</p>\n",
"</div>"
],
"text/plain": [
" id PDV SKU description launch \\\n",
"0 667507 21495 53398 SOUL BAT MATE MARROM TENDENCIA V2 3,7g \n",
"1 667512 21495 70882 SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml \n",
"2 667513 21495 73687 SOUL DEMAQ CREME 60ML \n",
"3 667527 20997 51775 EUD BABY SHAMP 200ml V2 \n",
"4 667550 21495 52332 SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g \n",
"\n",
" deactivation thirdtolastcyclesales secondtolastcyclesales lastcyclesales \\\n",
"0 0 0 0 \n",
"1 C12 0 0 0 \n",
"2 0 0 0 \n",
"3 0 0 0 \n",
"4 0 0 0 \n",
"\n",
" currentcyclesales ... CATEGORIA DDV_PREVISTO dias_em_rpt sku \\\n",
"0 0 ... 0 0.0 0.0 0 \n",
"1 0 ... 0 0.0 0.0 0 \n",
"2 0 ... 0 0.0 1.0 0 \n",
"3 0 ... 0 0.0 0.0 0 \n",
"4 0 ... 0 0.0 0.0 0 \n",
"\n",
" vendas_ciclo Código Valor dias_critico UF CANAL \n",
"0 0 53398 35.99 0.0 SE HIB \n",
"1 0 70882 39.99 0.0 SE HIB \n",
"2 0 73687 32.99 0.0 SE HIB \n",
"3 0 51775 72.98 0.0 AL VD \n",
"4 0 52332 35.99 0.0 SE HIB \n",
"\n",
"[5 rows x 53 columns]"
]
},
"execution_count": 29,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado_uf = pd.merge(df_unificado2,df_pdv[['PDV','UF','CANAL']],on='PDV', how='left')\n",
"df_unificado_uf.head()"
]
},
{
"cell_type": "code",
"execution_count": 30,
"id": "beb45ba8",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Index(['id', 'PDV', 'SKU', 'description', 'launch', 'deactivation',\n",
" 'thirdtolastcyclesales', 'secondtolastcyclesales', 'lastcyclesales',\n",
" 'currentcyclesales', 'nextcycleprojection',\n",
" 'secondtonextcycleprojection', 'stock_actual', 'stock_intransit',\n",
" 'purchasesuggestion', 'smartpurchase_purchasesuggestioncycle',\n",
" 'smartpurchase_nextcyclepurchasesuggestion', 'pendingorder',\n",
" 'salescurve', 'promotions_description', 'promotions_discountpercent',\n",
" 'pricesellin', 'businessunit', 'codcategory',\n",
" 'criticalitem_dtprovidedregularization', 'criticalitem_blockedwallet',\n",
" 'criticalitem_iscritical', 'codsubcategory', 'isproductdeactivated',\n",
" 'brandgroupcode', 'dayswithoutsales', 'coveragedays', 'hascoverage',\n",
" 'TRIAL949', 'data', 'Ciclo', 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO',\n",
" 'MARCA', 'Date', 'id', 'DESCRICAO', 'CATEGORIA', 'DDV_PREVISTO',\n",
" 'dias_em_rpt', 'sku', 'vendas_ciclo', 'Código', 'Valor', 'dias_critico',\n",
" 'REGIAO', 'CANAL'],\n",
" dtype='object')"
]
},
"execution_count": 30,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado_uf = df_unificado_uf.rename(columns={'UF':'REGIAO'})\n",
"df_unificado_uf.columns"
]
},
{
"cell_type": "code",
"execution_count": 31,
"id": "c0eb8df8",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf['UF'] = np.where((df_unificado_uf['REGIAO'] == 'BA3')|(df_unificado_uf['REGIAO'] == 'VDC'),'BA',df_unificado_uf['REGIAO'])"
]
},
{
"cell_type": "code",
"execution_count": 32,
"id": "f3e11e49",
"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>id</th>\n",
" <th>PDV</th>\n",
" <th>SKU</th>\n",
" <th>description</th>\n",
" <th>launch</th>\n",
" <th>deactivation</th>\n",
" <th>thirdtolastcyclesales</th>\n",
" <th>secondtolastcyclesales</th>\n",
" <th>lastcyclesales</th>\n",
" <th>currentcyclesales</th>\n",
" <th>...</th>\n",
" <th>DDV_PREVISTO</th>\n",
" <th>dias_em_rpt</th>\n",
" <th>sku</th>\n",
" <th>vendas_ciclo</th>\n",
" <th>Código</th>\n",
" <th>Valor</th>\n",
" <th>dias_critico</th>\n",
" <th>REGIAO</th>\n",
" <th>CANAL</th>\n",
" <th>UF</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>667507</td>\n",
" <td>21495</td>\n",
" <td>53398</td>\n",
" <td>SOUL BAT MATE MARROM TENDENCIA V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>53398</td>\n",
" <td>35.99</td>\n",
" <td>0.0</td>\n",
" <td>SE</td>\n",
" <td>HIB</td>\n",
" <td>SE</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>667512</td>\n",
" <td>21495</td>\n",
" <td>70882</td>\n",
" <td>SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml</td>\n",
" <td></td>\n",
" <td>C12</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>70882</td>\n",
" <td>39.99</td>\n",
" <td>0.0</td>\n",
" <td>SE</td>\n",
" <td>HIB</td>\n",
" <td>SE</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>667513</td>\n",
" <td>21495</td>\n",
" <td>73687</td>\n",
" <td>SOUL DEMAQ CREME 60ML</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>73687</td>\n",
" <td>32.99</td>\n",
" <td>0.0</td>\n",
" <td>SE</td>\n",
" <td>HIB</td>\n",
" <td>SE</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>667527</td>\n",
" <td>20997</td>\n",
" <td>51775</td>\n",
" <td>EUD BABY SHAMP 200ml V2</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>51775</td>\n",
" <td>72.98</td>\n",
" <td>0.0</td>\n",
" <td>AL</td>\n",
" <td>VD</td>\n",
" <td>AL</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>667550</td>\n",
" <td>21495</td>\n",
" <td>52332</td>\n",
" <td>SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>...</td>\n",
" <td>0.0</td>\n",
" <td>0.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>52332</td>\n",
" <td>35.99</td>\n",
" <td>0.0</td>\n",
" <td>SE</td>\n",
" <td>HIB</td>\n",
" <td>SE</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 54 columns</p>\n",
"</div>"
],
"text/plain": [
" id PDV SKU description launch \\\n",
"0 667507 21495 53398 SOUL BAT MATE MARROM TENDENCIA V2 3,7g \n",
"1 667512 21495 70882 SOUL DELIN LIQ OLHO PRET ESSENCIAL 2,5ml \n",
"2 667513 21495 73687 SOUL DEMAQ CREME 60ML \n",
"3 667527 20997 51775 EUD BABY SHAMP 200ml V2 \n",
"4 667550 21495 52332 SOUL K/M BAT MATE NUDE AUTORAL V2 3,7g \n",
"\n",
" deactivation thirdtolastcyclesales secondtolastcyclesales lastcyclesales \\\n",
"0 0 0 0 \n",
"1 C12 0 0 0 \n",
"2 0 0 0 \n",
"3 0 0 0 \n",
"4 0 0 0 \n",
"\n",
" currentcyclesales ... DDV_PREVISTO dias_em_rpt sku vendas_ciclo \\\n",
"0 0 ... 0.0 0.0 0 0 \n",
"1 0 ... 0.0 0.0 0 0 \n",
"2 0 ... 0.0 1.0 0 0 \n",
"3 0 ... 0.0 0.0 0 0 \n",
"4 0 ... 0.0 0.0 0 0 \n",
"\n",
" Código Valor dias_critico REGIAO CANAL UF \n",
"0 53398 35.99 0.0 SE HIB SE \n",
"1 70882 39.99 0.0 SE HIB SE \n",
"2 73687 32.99 0.0 SE HIB SE \n",
"3 51775 72.98 0.0 AL VD AL \n",
"4 52332 35.99 0.0 SE HIB SE \n",
"\n",
"[5 rows x 54 columns]"
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado_uf.head()"
]
},
{
"cell_type": "code",
"execution_count": 33,
"id": "4e2b26d6",
"metadata": {},
"outputs": [],
"source": [
"preco_vendas = pd.read_excel(r\"compilado preços.xlsx\")"
]
},
{
"cell_type": "code",
"execution_count": 34,
"id": "09c4946c",
"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>Nome da Origem</th>\n",
" <th>chave</th>\n",
" <th>chave sku1</th>\n",
" <th>SKU1</th>\n",
" <th>SKU2</th>\n",
" <th>Descrição</th>\n",
" <th>MARCA</th>\n",
" <th>CATEGORIA</th>\n",
" <th>LINHA</th>\n",
" <th>UF</th>\n",
" <th>Tipo Preço</th>\n",
" <th>chave2</th>\n",
" <th>chave sku2</th>\n",
" <th>PC</th>\n",
" <th>PV</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>TABELA DE PREÇOS (1).xlsx</td>\n",
" <td>1594AL</td>\n",
" <td>1594AL</td>\n",
" <td>1594</td>\n",
" <td>1594</td>\n",
" <td>QDB MASC CILIO COLEC ROSE 10g</td>\n",
" <td>QDB</td>\n",
" <td>MAQUIAGEM</td>\n",
" <td>QDB</td>\n",
" <td>AL</td>\n",
" <td>COMPRA</td>\n",
" <td>AL1594</td>\n",
" <td>AL1594</td>\n",
" <td>13.55</td>\n",
" <td>49.9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>TABELA DE PREÇOS (1).xlsx</td>\n",
" <td>1594BA</td>\n",
" <td>1594BA</td>\n",
" <td>1594</td>\n",
" <td>1594</td>\n",
" <td>QDB MASC CILIO COLEC ROSE 10g</td>\n",
" <td>QDB</td>\n",
" <td>MAQUIAGEM</td>\n",
" <td>QDB</td>\n",
" <td>BA</td>\n",
" <td>COMPRA</td>\n",
" <td>BA1594</td>\n",
" <td>BA1594</td>\n",
" <td>15.26</td>\n",
" <td>49.9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>TABELA DE PREÇOS (1).xlsx</td>\n",
" <td>1594SE</td>\n",
" <td>1594SE</td>\n",
" <td>1594</td>\n",
" <td>1594</td>\n",
" <td>QDB MASC CILIO COLEC ROSE 10g</td>\n",
" <td>QDB</td>\n",
" <td>MAQUIAGEM</td>\n",
" <td>QDB</td>\n",
" <td>SE</td>\n",
" <td>COMPRA</td>\n",
" <td>SE1594</td>\n",
" <td>SE1594</td>\n",
" <td>8.26</td>\n",
" <td>49.9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>TABELA DE PREÇOS (1).xlsx</td>\n",
" <td>17912AL</td>\n",
" <td>17912AL</td>\n",
" <td>17912</td>\n",
" <td>17912</td>\n",
" <td>QDB BATOM CORALICE 3,8g</td>\n",
" <td>QDB</td>\n",
" <td>MAQUIAGEM</td>\n",
" <td>QDB</td>\n",
" <td>AL</td>\n",
" <td>COMPRA</td>\n",
" <td>AL17912</td>\n",
" <td>AL17912</td>\n",
" <td>8.12</td>\n",
" <td>33.9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>TABELA DE PREÇOS (1).xlsx</td>\n",
" <td>17912BA</td>\n",
" <td>17912BA</td>\n",
" <td>17912</td>\n",
" <td>17912</td>\n",
" <td>QDB BATOM CORALICE 3,8g</td>\n",
" <td>QDB</td>\n",
" <td>MAQUIAGEM</td>\n",
" <td>QDB</td>\n",
" <td>BA</td>\n",
" <td>COMPRA</td>\n",
" <td>BA17912</td>\n",
" <td>BA17912</td>\n",
" <td>9.14</td>\n",
" <td>33.9</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Nome da Origem chave chave sku1 SKU1 SKU2 \\\n",
"0 TABELA DE PREÇOS (1).xlsx 1594AL 1594AL 1594 1594 \n",
"1 TABELA DE PREÇOS (1).xlsx 1594BA 1594BA 1594 1594 \n",
"2 TABELA DE PREÇOS (1).xlsx 1594SE 1594SE 1594 1594 \n",
"3 TABELA DE PREÇOS (1).xlsx 17912AL 17912AL 17912 17912 \n",
"4 TABELA DE PREÇOS (1).xlsx 17912BA 17912BA 17912 17912 \n",
"\n",
" Descrição MARCA CATEGORIA LINHA UF Tipo Preço \\\n",
"0 QDB MASC CILIO COLEC ROSE 10g QDB MAQUIAGEM QDB AL COMPRA \n",
"1 QDB MASC CILIO COLEC ROSE 10g QDB MAQUIAGEM QDB BA COMPRA \n",
"2 QDB MASC CILIO COLEC ROSE 10g QDB MAQUIAGEM QDB SE COMPRA \n",
"3 QDB BATOM CORALICE 3,8g QDB MAQUIAGEM QDB AL COMPRA \n",
"4 QDB BATOM CORALICE 3,8g QDB MAQUIAGEM QDB BA COMPRA \n",
"\n",
" chave2 chave sku2 PC PV \n",
"0 AL1594 AL1594 13.55 49.9 \n",
"1 BA1594 BA1594 15.26 49.9 \n",
"2 SE1594 SE1594 8.26 49.9 \n",
"3 AL17912 AL17912 8.12 33.9 \n",
"4 BA17912 BA17912 9.14 33.9 "
]
},
"execution_count": 34,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"preco_vendas.head()"
]
},
{
"cell_type": "code",
"execution_count": 35,
"id": "270f5ee3",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Index(['Nome da Origem', 'chave', 'chave sku1', 'SKU1', 'Produto', 'Descrição',\n",
" 'MARCA', 'CATEGORIA', 'LINHA', 'UF', 'Tipo Preço', 'chave2',\n",
" 'chave sku2', 'PC', 'PV'],\n",
" dtype='object')"
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"preco_vendas = preco_vendas.rename(columns={'SKU2':'Produto'})\n",
"preco_vendas.columns"
]
},
{
"cell_type": "code",
"execution_count": 36,
"id": "4b23dc4e",
"metadata": {},
"outputs": [],
"source": [
"preco_vendas['Produto'] = preco_vendas['Produto'].astype('str')"
]
},
{
"cell_type": "code",
"execution_count": 37,
"id": "16e909bc",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf2 = pd.merge(df_unificado_uf,preco_vendas,left_on= ['SKU','UF'],right_on=['Produto','UF'],how='left')\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": 38,
"id": "bd64fd7b",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Index(['id', 'PDV', 'SKU', 'description', 'launch', 'deactivation',\n",
" 'thirdtolastcyclesales', 'secondtolastcyclesales', 'lastcyclesales',\n",
" 'currentcyclesales', 'nextcycleprojection',\n",
" 'secondtonextcycleprojection', 'stock_actual', 'stock_intransit',\n",
" 'purchasesuggestion', 'smartpurchase_purchasesuggestioncycle',\n",
" 'smartpurchase_nextcyclepurchasesuggestion', 'pendingorder',\n",
" 'salescurve', 'promotions_description', 'promotions_discountpercent',\n",
" 'pricesellin', 'businessunit', 'codcategory',\n",
" 'criticalitem_dtprovidedregularization', 'criticalitem_blockedwallet',\n",
" 'criticalitem_iscritical', 'codsubcategory', 'isproductdeactivated',\n",
" 'brandgroupcode', 'dayswithoutsales', 'coveragedays', 'hascoverage',\n",
" 'TRIAL949', 'data', 'Ciclo', 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO',\n",
" 'MARCA_x', 'Date', 'id', 'DESCRICAO', 'CATEGORIA_x', 'DDV_PREVISTO',\n",
" 'dias_em_rpt', 'sku', 'vendas_ciclo', 'Código', 'Valor', 'dias_critico',\n",
" 'REGIAO', 'CANAL', 'UF', 'Nome da Origem', 'chave', 'chave sku1',\n",
" 'SKU1', 'Produto', 'Descrição', 'MARCA_y', 'CATEGORIA_y', 'LINHA',\n",
" 'Tipo Preço', 'chave2', 'chave sku2', 'PC', 'PV'],\n",
" dtype='object')"
]
},
"execution_count": 38,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado_uf2.columns"
]
},
{
"cell_type": "code",
"execution_count": 39,
"id": "f2d51db0",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf2 = df_unificado_uf2.drop(columns=['Nome da Origem', 'chave', 'chave sku1', 'SKU1', 'Descrição','CATEGORIA_x','CATEGORIA_y', 'LINHA', 'Tipo Preço', 'chave2', 'chave sku2'])"
]
},
{
"cell_type": "code",
"execution_count": 40,
"id": "424a08ca",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Index(['id', 'PDV', 'SKU', 'description', 'launch', 'deactivation',\n",
" 'thirdtolastcyclesales', 'secondtolastcyclesales', 'lastcyclesales',\n",
" 'currentcyclesales', 'nextcycleprojection',\n",
" 'secondtonextcycleprojection', 'stock_actual', 'stock_intransit',\n",
" 'purchasesuggestion', 'smartpurchase_purchasesuggestioncycle',\n",
" 'smartpurchase_nextcyclepurchasesuggestion', 'pendingorder',\n",
" 'salescurve', 'promotions_description', 'promotions_discountpercent',\n",
" 'pricesellin', 'businessunit', 'codcategory',\n",
" 'criticalitem_dtprovidedregularization', 'criticalitem_blockedwallet',\n",
" 'criticalitem_iscritical', 'codsubcategory', 'isproductdeactivated',\n",
" 'brandgroupcode', 'dayswithoutsales', 'coveragedays', 'hascoverage',\n",
" 'TRIAL949', 'data', 'Ciclo', 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO',\n",
" 'MARCA_x', 'Date', 'id', 'DESCRICAO', 'DDV_PREVISTO', 'dias_em_rpt',\n",
" 'sku', 'vendas_ciclo', 'Código', 'Valor', 'dias_critico', 'REGIAO',\n",
" 'CANAL', 'UF', 'Produto', 'MARCA_y', 'PC', 'PV'],\n",
" dtype='object')"
]
},
"execution_count": 40,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado_uf2.columns"
]
},
{
"cell_type": "code",
"execution_count": 41,
"id": "04af3570",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf2['PRODUTOS_ROMPIDOS_CICLO'] = df_unificado_uf2['DDV_PREVISTO'] * df_unificado_uf2['dias_em_rpt']"
]
},
{
"cell_type": "code",
"execution_count": 42,
"id": "1ddfb89e",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf2['vendas_ciclo'] = df_unificado_uf2['vendas_ciclo'].astype('Int64')"
]
},
{
"cell_type": "code",
"execution_count": 43,
"id": "567ade2d",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf2['RECEITA'] = df_unificado_uf2['Valor'] * df_unificado_uf2['vendas_ciclo']"
]
},
{
"cell_type": "code",
"execution_count": 44,
"id": "82595067",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf2.to_clipboard()"
]
},
{
"cell_type": "code",
"execution_count": 45,
"id": "686be9b3",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Index(['id', 'PDV', 'SKU', 'description', 'launch', 'deactivation',\n",
" 'thirdtolastcyclesales', 'secondtolastcyclesales', 'lastcyclesales',\n",
" 'currentcyclesales', 'nextcycleprojection',\n",
" 'secondtonextcycleprojection', 'stock_actual', 'stock_intransit',\n",
" 'purchasesuggestion', 'smartpurchase_purchasesuggestioncycle',\n",
" 'smartpurchase_nextcyclepurchasesuggestion', 'pendingorder',\n",
" 'salescurve', 'promotions_description', 'promotions_discountpercent',\n",
" 'pricesellin', 'businessunit', 'codcategory',\n",
" 'criticalitem_dtprovidedregularization', 'criticalitem_blockedwallet',\n",
" 'criticalitem_iscritical', 'codsubcategory', 'isproductdeactivated',\n",
" 'brandgroupcode', 'dayswithoutsales', 'coveragedays', 'hascoverage',\n",
" 'TRIAL949', 'data', 'Ciclo', 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO',\n",
" 'MARCA_x', 'Date', 'id', 'DESCRICAO', 'DDV_PREVISTO', 'dias_em_rpt',\n",
" 'sku', 'vendas_ciclo', 'Código', 'Valor', 'dias_critico', 'REGIAO',\n",
" 'CANAL', 'UF', 'Produto', 'MARCA_y', 'PC', 'PV',\n",
" 'PRODUTOS_ROMPIDOS_CICLO', 'RECEITA'],\n",
" dtype='object')"
]
},
"execution_count": 45,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado_uf2.columns"
]
},
{
"cell_type": "code",
"execution_count": 46,
"id": "43474c1f",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf2['CAUSA MACRO'] = np.where(df_unificado_uf2['dias_critico'] > 0, \"CAUSA INDUSTRIA\",\"\")\n",
"\n",
"df_unificado_uf2['Produto_Critico'] = np.where(df_unificado_uf2['dias_critico'] > 0, 1,0)"
]
},
{
"cell_type": "markdown",
"id": "d1557bcb",
"metadata": {},
"source": [
"ANALISAR ITEM CRITICO JUNTO AO MAR.\n",
" - PROCURAR EM PERENE E FAZER O CONCATENADO. :'(\n",
" "
]
},
{
"cell_type": "code",
"execution_count": 47,
"id": "ca4254ab",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_17416\\2029767949.py:28: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
" df_fne = pd.read_sql(query_fne, conn)\n",
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_17416\\2029767949.py:45: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
" df_pedidos = pd.read_sql(query_pedidos, conn)\n",
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_17416\\2029767949.py:52: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
" df_draft = pd.read_sql(query_draft, conn)\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>Ciclo</th>\n",
" <th>PDV</th>\n",
" <th>cod_produto</th>\n",
" <th>pedido_id</th>\n",
" <th>data_emissao_nf</th>\n",
" <th>data_pedido</th>\n",
" <th>data_prevista_atendimento</th>\n",
" <th>faturado_industria</th>\n",
" <th>quantidade_pedida</th>\n",
" <th>status</th>\n",
" <th>salescurve</th>\n",
" <th>valor_total_produtos</th>\n",
" <th>notas</th>\n",
" <th>pedidos</th>\n",
" <th>atendimento</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>C202507</td>\n",
" <td>12522</td>\n",
" <td>52757</td>\n",
" <td>1515922</td>\n",
" <td>2025-07-09</td>\n",
" <td>2025-05-22 00:00:00.0000000</td>\n",
" <td>2025-06-06 00:00:00.0000000</td>\n",
" <td>5</td>\n",
" <td>35</td>\n",
" <td>Parcialmente Atendido</td>\n",
" <td>C</td>\n",
" <td>45.36</td>\n",
" <td>31953127</td>\n",
" <td>0165202706</td>\n",
" <td>0.142857</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>C202507</td>\n",
" <td>12817</td>\n",
" <td>52757</td>\n",
" <td>1515918</td>\n",
" <td>2025-07-09</td>\n",
" <td>2025-05-22 00:00:00.0000000</td>\n",
" <td>2025-06-06 00:00:00.0000000</td>\n",
" <td>24</td>\n",
" <td>90</td>\n",
" <td>Parcialmente Atendido</td>\n",
" <td>C</td>\n",
" <td>217.71</td>\n",
" <td>31754122</td>\n",
" <td>0165202491</td>\n",
" <td>0.266667</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>C202507</td>\n",
" <td>12817</td>\n",
" <td>87066</td>\n",
" <td>1517306</td>\n",
" <td>2025-07-09</td>\n",
" <td>2025-05-22 00:00:00.0000000</td>\n",
" <td>2025-06-13 00:00:00.0000000</td>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>Concluído</td>\n",
" <td>C</td>\n",
" <td>6.11</td>\n",
" <td>31754122</td>\n",
" <td>0165206643</td>\n",
" <td>0.111111</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>C202507</td>\n",
" <td>12818</td>\n",
" <td>52757</td>\n",
" <td>1515924</td>\n",
" <td>2025-07-09</td>\n",
" <td>2025-05-22 00:00:00.0000000</td>\n",
" <td>2025-06-06 00:00:00.0000000</td>\n",
" <td>4</td>\n",
" <td>25</td>\n",
" <td>Parcialmente Atendido</td>\n",
" <td>C</td>\n",
" <td>36.29</td>\n",
" <td>76077782</td>\n",
" <td>0165202707</td>\n",
" <td>0.16</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>C202507</td>\n",
" <td>12820</td>\n",
" <td>52757</td>\n",
" <td>1515943</td>\n",
" <td>2025-07-09</td>\n",
" <td>2025-05-22 00:00:00.0000000</td>\n",
" <td>2025-06-06 00:00:00.0000000</td>\n",
" <td>3</td>\n",
" <td>20</td>\n",
" <td>Parcialmente Atendido</td>\n",
" <td>C</td>\n",
" <td>27.21</td>\n",
" <td>29403846</td>\n",
" <td>0165202369</td>\n",
" <td>0.15</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Ciclo PDV cod_produto pedido_id data_emissao_nf \\\n",
"0 C202507 12522 52757 1515922 2025-07-09 \n",
"1 C202507 12817 52757 1515918 2025-07-09 \n",
"2 C202507 12817 87066 1517306 2025-07-09 \n",
"3 C202507 12818 52757 1515924 2025-07-09 \n",
"4 C202507 12820 52757 1515943 2025-07-09 \n",
"\n",
" data_pedido data_prevista_atendimento \\\n",
"0 2025-05-22 00:00:00.0000000 2025-06-06 00:00:00.0000000 \n",
"1 2025-05-22 00:00:00.0000000 2025-06-06 00:00:00.0000000 \n",
"2 2025-05-22 00:00:00.0000000 2025-06-13 00:00:00.0000000 \n",
"3 2025-05-22 00:00:00.0000000 2025-06-06 00:00:00.0000000 \n",
"4 2025-05-22 00:00:00.0000000 2025-06-06 00:00:00.0000000 \n",
"\n",
" faturado_industria quantidade_pedida status salescurve \\\n",
"0 5 35 Parcialmente Atendido C \n",
"1 24 90 Parcialmente Atendido C \n",
"2 1 9 Concluído C \n",
"3 4 25 Parcialmente Atendido C \n",
"4 3 20 Parcialmente Atendido C \n",
"\n",
" valor_total_produtos notas pedidos atendimento \n",
"0 45.36 31953127 0165202706 0.142857 \n",
"1 217.71 31754122 0165202491 0.266667 \n",
"2 6.11 31754122 0165206643 0.111111 \n",
"3 36.29 76077782 0165202707 0.16 \n",
"4 27.21 29403846 0165202369 0.15 "
]
},
"execution_count": 47,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"conn = pyodbc.connect(\n",
" f\"DRIVER={{SQL Server}};\"\n",
" f\"SERVER={config['banco']['host']},1433;\"\n",
" f\"DATABASE=GINSENG;\"\n",
" f\"UID={config['banco']['user']};\"\n",
" f\"PWD={config['banco']['password']}\")\n",
"\n",
"\n",
"# 1. Puxa fne_pdv (já filtrado)\n",
"query_fne = \"\"\"\n",
"SELECT\n",
" fne.cnpj_destinatario,\n",
" fne.cnf AS Nota_Fiscal,\n",
" CAST(fne.data_emissao AS date) AS data_emissao,\n",
" fnei.codigo_pedido,\n",
" fnei.n_item,\n",
" fnei.cod_produto,\n",
" fnei.produto,\n",
" fnei.quantidade,\n",
" fnei.valor_unitario,\n",
" fnei.valor_total_produtos,\n",
" b.PDV\n",
"FROM fato_notas_entrada fne\n",
"INNER JOIN fato_notas_entrada_itens fnei ON fne.chave = fnei.chave\n",
"INNER JOIN base_pdvs b ON fne.cnpj_destinatario = b.CNPJ\n",
"WHERE fne.data_emissao > '2025-07-01'\n",
"\"\"\"\n",
"df_fne = pd.read_sql(query_fne, conn)\n",
"\n",
"query_pedidos = \"\"\"\n",
"SELECT\n",
" sellOrders,\n",
" sku,\n",
" storeCode,\n",
" MAX(quantity_accepted) AS quantity_accepted,\n",
" MAX(status) AS status,\n",
" MAX(pedido_id) AS pedido_id,\n",
" MAX(p.[date]) AS [date],\n",
" MAX(deliveryDate) AS deliveryDate,\n",
" cd.Ciclo \n",
"FROM produtos_pedidos p\n",
"INNER JOIN ciclos_data_2025 cd on cd.[Date] = p.[date] and cd.MARCA ='BOT'\n",
"GROUP BY sellOrders, sku, storeCode, cd.Ciclo \n",
"\"\"\"\n",
"df_pedidos = pd.read_sql(query_pedidos, conn)\n",
"\n",
"# 3. Puxa draft\n",
"query_draft = \"\"\"\n",
"SELECT loja_id, code, salescurve\n",
"FROM draft\n",
"\"\"\"\n",
"df_draft = pd.read_sql(query_draft, conn)\n",
"\n",
"# ---- Fazendo os JOINs e agregações em Pandas ----\n",
"\n",
"# JOIN fne com pedidos\n",
"df_join = df_fne.merge(df_pedidos, left_on=['codigo_pedido','cod_produto','PDV'],\n",
" right_on=['sellOrders','sku','storeCode'], how=\"inner\")\n",
"\n",
"# JOIN com draft\n",
"df_join = df_join.merge(df_draft, left_on=['PDV','cod_produto'],\n",
" right_on=['loja_id','code'], how=\"inner\")\n",
"\n",
"# Agrupamento final\n",
"df_result = (\n",
" df_join.groupby(['Ciclo','PDV','cod_produto','pedido_id'])\n",
" .agg(\n",
" data_emissao_nf=('data_emissao','max'),\n",
" data_pedido=('date','max'),\n",
" data_prevista_atendimento=('deliveryDate','max'),\n",
" faturado_industria=('quantidade','sum'),\n",
" quantidade_pedida=('quantity_accepted','max'),\n",
" status=('status','max'),\n",
" salescurve=('salescurve','max'),\n",
" valor_total_produtos=('valor_total_produtos','sum'),\n",
" notas=('Nota_Fiscal', lambda x: ', '.join(sorted(set(x)))),\n",
" pedidos=('codigo_pedido', lambda x: ', '.join(sorted(set(x))))\n",
" )\n",
" .reset_index()\n",
")\n",
"\n",
"# Calculando atendimento\n",
"df_result['atendimento'] = (\n",
" df_result['faturado_industria'] / df_result['quantidade_pedida'].replace(0, pd.NA)\n",
").round(2)\n",
"\n",
"df_result.head()"
]
},
{
"cell_type": "code",
"execution_count": 48,
"id": "0c49561c",
"metadata": {},
"outputs": [],
"source": [
"df_result['PDV'] = df_result['PDV'] .astype('Int64')\n",
"\n",
"df_result['cod_produto'] = df_result['cod_produto'] .astype('Int64')"
]
},
{
"cell_type": "code",
"execution_count": 49,
"id": "fbb22bbb",
"metadata": {},
"outputs": [],
"source": [
"df_result['PDV'] = df_result['PDV'].astype('str')\n",
"\n",
"df_result['cod_produto'] = df_result['cod_produto'].astype('str')\n"
]
},
{
"cell_type": "code",
"execution_count": 50,
"id": "30702295",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf3 = pd.merge(df_unificado_uf2,df_result, left_on= ['Ciclo', 'PDV', 'SKU'],right_on=['Ciclo',\t'PDV','cod_produto'],how='left')"
]
},
{
"cell_type": "code",
"execution_count": 51,
"id": "82b616d3",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"np.int64(86149)"
]
},
"execution_count": 51,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado_uf3['atendimento'].isna().sum()"
]
},
{
"cell_type": "code",
"execution_count": 52,
"id": "52627dee",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf3['atendimento'] = df_unificado_uf3['atendimento'].astype(float)"
]
},
{
"cell_type": "code",
"execution_count": 53,
"id": "a46dd133",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf3['VALOR_RPT'] = df_unificado_uf2['PRODUTOS_ROMPIDOS_CICLO'] * df_unificado_uf2['Valor']"
]
},
{
"cell_type": "code",
"execution_count": 54,
"id": "2a92373d",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"VALOR_RPT float64\n",
"atendimento float64\n",
"atendimento float64\n",
"dtype: object"
]
},
"execution_count": 54,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado_uf3[[ 'VALOR_RPT' ,\n",
" 'atendimento',\n",
" 'atendimento']].dtypes"
]
},
{
"cell_type": "code",
"execution_count": 55,
"id": "b390f4e7",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Index(['id', 'PDV', 'SKU', 'description', 'launch', 'deactivation',\n",
" 'thirdtolastcyclesales', 'secondtolastcyclesales', 'lastcyclesales',\n",
" 'currentcyclesales', 'nextcycleprojection',\n",
" 'secondtonextcycleprojection', 'stock_actual', 'stock_intransit',\n",
" 'purchasesuggestion', 'smartpurchase_purchasesuggestioncycle',\n",
" 'smartpurchase_nextcyclepurchasesuggestion', 'pendingorder',\n",
" 'salescurve_x', 'promotions_description', 'promotions_discountpercent',\n",
" 'pricesellin', 'businessunit', 'codcategory',\n",
" 'criticalitem_dtprovidedregularization', 'criticalitem_blockedwallet',\n",
" 'criticalitem_iscritical', 'codsubcategory', 'isproductdeactivated',\n",
" 'brandgroupcode', 'dayswithoutsales', 'coveragedays', 'hascoverage',\n",
" 'TRIAL949', 'data', 'Ciclo', 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO',\n",
" 'MARCA_x', 'Date', 'id', 'DESCRICAO', 'DDV_PREVISTO', 'dias_em_rpt',\n",
" 'sku', 'vendas_ciclo', 'Código', 'Valor', 'dias_critico', 'REGIAO',\n",
" 'CANAL', 'UF', 'Produto', 'MARCA_y', 'PC', 'PV',\n",
" 'PRODUTOS_ROMPIDOS_CICLO', 'RECEITA', 'CAUSA MACRO', 'Produto_Critico',\n",
" 'cod_produto', 'pedido_id', 'data_emissao_nf', 'data_pedido',\n",
" 'data_prevista_atendimento', 'faturado_industria', 'quantidade_pedida',\n",
" 'status', 'salescurve_y', 'valor_total_produtos', 'notas', 'pedidos',\n",
" 'atendimento', 'VALOR_RPT'],\n",
" dtype='object')"
]
},
"execution_count": 55,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_unificado_uf3.columns"
]
},
{
"cell_type": "code",
"execution_count": 56,
"id": "a55b0364",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"linhas com diferença entre métodos: 28133\n"
]
}
],
"source": [
"\n",
"# file: scripts/causa_macro_assignment.py\n",
"import numpy as np\n",
"import pandas as pd\n",
"\n",
"# 1) Sanitize: garantir tipos numéricos coerentes (prevenir strings, vírgulas, etc.)\n",
"for c in ['dias_em_rpt', 'VALOR_RPT', 'Produto_Critico', 'atendimento']:\n",
" df_unificado_uf3[c] = pd.to_numeric(df_unificado_uf3[c], errors='coerce')\n",
"\n",
"# 2) Atribuição sequencial (vetorizada, Excel-like)\n",
"default = 'Indefinido'\n",
"df_unificado_uf3['CAUSA MACRO'] = default\n",
"\n",
"conds_and_values = [\n",
" (df_unificado_uf3['dias_em_rpt'] == 0, 'não houve ruptura'),\n",
" (df_unificado_uf3['dias_critico'] > 0, 'Causa Industria'),\n",
" (df_unificado_uf3['Produto_Critico'] == 1, 'Causa Industria'),\n",
" (df_unificado_uf3['atendimento'].isna(), 'Causa Franqueado'),\n",
" (df_unificado_uf3['atendimento'] < 0.86, 'Causa Industria'),\n",
" (df_unificado_uf3['dias_em_rpt'] > 0, 'Causa Franqueado') \n",
"]\n",
"\n",
"for cond, val in conds_and_values:\n",
" mask = (df_unificado_uf3['CAUSA MACRO'] == default) & cond\n",
" df_unificado_uf3.loc[mask, 'CAUSA MACRO'] = val\n",
"\n",
"# 3) (Opcional) Verificação com apply — igual lógica, mais lenta, útil para debug\n",
"def causa_macro_row(row):\n",
" # checagens em ordem — primeira que bater retorna\n",
" if row['dias_em_rpt'] == 0:\n",
" return 'não houve ruptura'\n",
" if row['VALOR_RPT'] == 0:\n",
" return 'não houve ruptura'\n",
" if row['Produto_Critico'] == 1:\n",
" return 'Causa Industria'\n",
" if row['dias_em_rpt'] > 0:\n",
" return 'Causa Franqueado'\n",
" if pd.isna(row['atendimento']):\n",
" return 'Causa Franqueado'\n",
" if row['atendimento'] < 0.86:\n",
" return 'Causa Industria'\n",
" return default\n",
"\n",
"# criar coluna de verificação e comparar (remova após checagem)\n",
"df_unificado_uf3['CAUSA_MACRO_check'] = df_unificado_uf3.apply(causa_macro_row, axis=1)\n",
"mismatch = df_unificado_uf3[df_unificado_uf3['CAUSA MACRO'] != df_unificado_uf3['CAUSA_MACRO_check']]\n",
"print(f\"linhas com diferença entre métodos: {len(mismatch)}\")\n",
"# opcional: df_unificado_uf3.drop(columns=['CAUSA_MACRO_check'], inplace=True)\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": 57,
"id": "c628ccc6",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"CAUSA MACRO\n",
"não houve ruptura 54455\n",
"Causa Franqueado 31433\n",
"Causa Industria 929\n",
"Name: count, dtype: int64"
]
},
"execution_count": 57,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"\n",
"df_unificado_uf3['CAUSA MACRO'].value_counts()"
]
},
{
"cell_type": "code",
"execution_count": 58,
"id": "8bca9477",
"metadata": {},
"outputs": [],
"source": [
"df_unificado_uf3.to_excel(r'C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\CODIGOS\\Ruptura_Eudora\\Rpt_eud-23-10.xlsx',index=False)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1e5370af",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.13.2"
}
},
"nbformat": 4,
"nbformat_minor": 5
}
Reference in New Issue View Git Blame Copy Permalink