{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np \n", "import glob\n", "import os \n", "from openpyxl import load_workbook\n", "from openpyxl.styles import PatternFill, Font\n", "from datetime import datetime" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "hoje = datetime.today().strftime('%Y-%m-%d')" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "#precisa ver duplicidade de desc e vendas irece." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "df_tabela = pd.read_excel(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\SUPRIMENTOS\\DB_PROMOÇÕES\\BOTICARIO\\C17\\TABELA DE PEDIDO\\Pedidos Semanais Especiais - BOT - 202517.xlsx\")\n", "\n", "df_tabela = df_tabela[df_tabela['Ação revendedor'].notna() | df_tabela['Ação consumidor'].notna()]\n", "\n", "df_tabela = df_tabela[df_tabela['Região'] == 'NNE'] \n", "\n", "df_tabela = df_tabela[(df_tabela['Canal'] != 'Ecomm')]\n", "\n", "#df_tabela['Canal'] = np.where((df_tabela['Canal'] == \"Loja\") | (df_tabela['Canal'] == \"Todos\") | (df_tabela['Canal'] == \"Loja | VD\"),\"TODOS\",\"VD\")\n", "\n", "df_tabela = df_tabela[(df_tabela['Categoria'] != \"EMBALAGENS\") | (df_tabela['Categoria'] != \"SUPORTE À VENDA\")]\n", "\n", "#df_tabela = df_tabela[df_tabela['Tipo de pedido'] == 'Semanal']\n", "\n", "df_tabela = df_tabela[~df_tabela['Descrição'].str.contains('PRM')]\n", "\n", "#df_tabela = df_tabela[df_tabela['Tipo de produto']!= 'EDICAO LIMITADA']\n", "\n", "df_tabela['Ação revendedor'] = np.where(df_tabela['Ação revendedor'].isna(),df_tabela['Ação consumidor'],df_tabela['Ação revendedor'])\n", "\n", "df_tabela['Percentual de desconto revendedor'] = np.where(df_tabela['Percentual de desconto revendedor'].isna(),df_tabela['Percentual de desconto consumidor'],df_tabela['Percentual de desconto revendedor'])\n", "\n", "df_tabela['MATCH'] = 1\n", "\n" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "df_tabela['Código'].value_counts().to_clipboard()" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_45000\\2225685327.py:39: 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", " dfi = pd.read_sql(query, conn)\n" ] } ], "source": [ "import pyodbc\n", "import configparser\n", "\n", "#query de vendas por ciclo\n", "\n", "config = configparser.ConfigParser()\n", "config.read(r\"C:\\Users\\joao.herculano\\Documents\\Enviador de email\\credenciais.ini\")\n", "\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", "\n", "query = f'''\n", "SELECT \n", " B.PDV, \n", " B.SKU,\n", " bd.SKU2, \n", " COALESCE(bd.SKU2, b.SKU) AS SKU_FINAL,\n", " B.DESCRICAO,\n", " SUM(CAST(b.VENDAS AS DECIMAL(18,2))) AS VENDAS_CICLO,\n", " C.Ciclo\n", "FROM base_vendas_bi b\n", "INNER JOIN ciclos_data_2025 c \n", " ON CAST(b.[DATA] AS DATE) = CONVERT(DATE, c.[Date], 103) AND C.MARCA = 'BOT'\n", "LEFT JOIN base_depara bd on b.SKU = bd.SKU \n", "WHERE CAST(b.[DATA] AS DATE) >= DATEADD(YEAR, -1, GETDATE())\n", "GROUP BY\n", " B.PDV, \n", " B.SKU,\n", " bd.SKU2,\n", " B.DESCRICAO,\n", " C.Ciclo\n", "HAVING SUM(CAST(b.VENDAS AS DECIMAL(18,2))) > 0\n", "'''\n", "dfi = pd.read_sql(query, conn)\n", "conn.close()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "dfi['SKU2'] = dfi['SKU2'].fillna(\"-\")" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVSKUSKU2SKU_FINALDESCRICAOVENDAS_CICLOCiclo
12137554879-54879ESTJ BOTI BABY NINHO V24.0C202508
21283081331-81331ELYSEE EDP SUCCES 50ml1.0C202501
41252252021-52021REF BOTI BABY SHAMP 350ml2.0C202507
52099730207-30207SIAGE LEAV CAP HIDRATACAO MICELAR 100ml2.0C202511
62138385124-85124CBEM LOC DES HID CPO DELEITE V3 400ml206.0C202512
\n", "
" ], "text/plain": [ " PDV SKU SKU2 SKU_FINAL DESCRICAO \\\n", "1 21375 54879 - 54879 ESTJ BOTI BABY NINHO V2 \n", "2 12830 81331 - 81331 ELYSEE EDP SUCCES 50ml \n", "4 12522 52021 - 52021 REF BOTI BABY SHAMP 350ml \n", "5 20997 30207 - 30207 SIAGE LEAV CAP HIDRATACAO MICELAR 100ml \n", "6 21383 85124 - 85124 CBEM LOC DES HID CPO DELEITE V3 400ml \n", "\n", " VENDAS_CICLO Ciclo \n", "1 4.0 C202508 \n", "2 1.0 C202501 \n", "4 2.0 C202507 \n", "5 2.0 C202511 \n", "6 206.0 C202512 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dfi = dfi[dfi['SKU2'] == \"-\"]\n", "\n", "dfi.head()" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVSKU_FINALDESCRICAOC202415C202416C202417C202501C202502C202503C202504...C202506C202507C202508C202509C202510C202511C202512C202513C202514C202515
0125221080INTENSE GLOS LAB BUBBAL TUT/FRUT 5ml0.010.02.02.01.02.03.0...7.017.00.00.00.00.00.00.00.00.0
1125221296PMPCK THE BLEND DES ANTIT AER 2x75g1.02.00.00.00.00.00.0...0.00.00.00.00.00.00.02.01.00.0
2125221302PMPCK LILY DES ANTIT AER 2x75g3.06.00.00.00.00.00.0...3.04.00.04.04.02.01.06.00.00.0
3125221314PMPCK ZAAD DES ANTIT AER 2x75g1.01.00.00.00.00.00.0...0.00.00.00.00.01.01.01.00.00.0
4125221317PMPCK MALBEC DES ANTIT AER 2x75g1.05.00.01.03.00.00.0...1.01.00.02.02.03.02.05.00.00.0
\n", "

5 rows × 21 columns

\n", "
" ], "text/plain": [ " PDV SKU_FINAL DESCRICAO C202415 C202416 \\\n", "0 12522 1080 INTENSE GLOS LAB BUBBAL TUT/FRUT 5ml 0.0 10.0 \n", "1 12522 1296 PMPCK THE BLEND DES ANTIT AER 2x75g 1.0 2.0 \n", "2 12522 1302 PMPCK LILY DES ANTIT AER 2x75g 3.0 6.0 \n", "3 12522 1314 PMPCK ZAAD DES ANTIT AER 2x75g 1.0 1.0 \n", "4 12522 1317 PMPCK MALBEC DES ANTIT AER 2x75g 1.0 5.0 \n", "\n", " C202417 C202501 C202502 C202503 C202504 ... C202506 C202507 \\\n", "0 2.0 2.0 1.0 2.0 3.0 ... 7.0 17.0 \n", "1 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 \n", "2 0.0 0.0 0.0 0.0 0.0 ... 3.0 4.0 \n", "3 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 \n", "4 0.0 1.0 3.0 0.0 0.0 ... 1.0 1.0 \n", "\n", " C202508 C202509 C202510 C202511 C202512 C202513 C202514 C202515 \n", "0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "1 0.0 0.0 0.0 0.0 0.0 2.0 1.0 0.0 \n", "2 0.0 4.0 4.0 2.0 1.0 6.0 0.0 0.0 \n", "3 0.0 0.0 0.0 1.0 1.0 1.0 0.0 0.0 \n", "4 0.0 2.0 2.0 3.0 2.0 5.0 0.0 0.0 \n", "\n", "[5 rows x 21 columns]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\n", "# 1. Agrupamento\n", "df_agrupado = dfi.groupby(['PDV', 'SKU_FINAL', 'DESCRICAO', 'Ciclo'], as_index=False)['VENDAS_CICLO'].sum()\n", "\n", "# 2. Pivotar o DataFrame\n", "df_pivotado = df_agrupado.pivot_table(\n", " index=['PDV', 'SKU_FINAL', 'DESCRICAO'],\n", " columns='Ciclo',\n", " values='VENDAS_CICLO',\n", " fill_value=0 # Substitui NaN por 0\n", ")\n", "\n", "# 3. Resetar o índice para deixar como DataFrame normal (opcional)\n", "df_pivotadowawa = df_pivotado.reset_index()\n", "\n", "# 4. (Opcional) Renomear colunas com prefixo \"Ciclo_\"\n", "df_pivotadowawa.columns.name = None\n", "df_pivotadowawa = df_pivotadowawa.rename(columns=lambda x: f'Ciclo_{x}' if isinstance(x, (int, str)) and str(x).isdigit() else x)\n", "\n", "# Resultado final\n", "df_pivotadowawa.head()\n" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVSKU_FINALDESCRICAOC202415C202416C202417C202501C202502C202503C202504...C202506C202507C202508C202509C202510C202511C202512C202513C202514C202515
\n", "

0 rows × 21 columns

\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [PDV, SKU_FINAL, DESCRICAO, C202415, C202416, C202417, C202501, C202502, C202503, C202504, C202505, C202506, C202507, C202508, C202509, C202510, C202511, C202512, C202513, C202514, C202515]\n", "Index: []\n", "\n", "[0 rows x 21 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivotadowawa[(df_pivotadowawa['SKU_FINAL'] == '1634') & (df_pivotadowawa['PDV'] == '20998')]" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "\n", "\n", "df_pdv = pd.read_excel(r\"C:\\Users\\joao.herculano\\Documents\\PDV_ATT.xlsx\")\n", "\n", "df_pdv_origi = pd.read_excel(r\"C:\\Users\\joao.herculano\\Documents\\PDV_ATT.xlsx\")\n", "\n", "df_pdv = df_pdv.rename(columns={'DESCRIÇÃO':'DESCRIÇÃO PDV'})\n", "\n", "df_pdv = df_pdv[df_pdv['GESTÃO']!=\"Inativa\"]\n", "\n", "df_pdv = df_pdv.drop(columns=['REGIÃO', 'ESTADO','CIDADE','GESTÃO', 'STATUS'])\n", "\n", "df_pdv = df_pdv[df_pdv['CANAL']!='MTZ']\n", "\n", "#df_pdv['CANAL'] = np.where((df_pdv['CANAL']=='LJ')|(df_pdv['CANAL']=='HIB')|(df_pdv['CANAL']=='CD'),'TODOS','VD')\n", "\n", "df_pdv['MATCH'] = 1\n", "\n", "df_pdv = df_pdv[(df_pdv['PDV']!=910291)&(df_pdv['PDV']!=910173)]\n", "\n" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVCANALDESCRIÇÃO PDVPDV DESCUFMARCAANALISTASUPERVISORANALISTA EUDMATCH
4321007LJMOREIRA 221007-MOREIRA 2ALO BOTICARIOLUANMaxwell VieiraLOJA1
\n", "
" ], "text/plain": [ " PDV CANAL DESCRIÇÃO PDV PDV DESC UF MARCA ANALISTA \\\n", "43 21007 LJ MOREIRA 2 21007-MOREIRA 2 AL O BOTICARIO LUAN \n", "\n", " SUPERVISOR ANALISTA EUD MATCH \n", "43 Maxwell Vieira LOJA 1 " ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pdv[df_pdv['PDV']==21007]" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "df_pdv = df_pdv.drop(columns=['PDV DESC'])" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_45000\\268247862.py:25: 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_est_draft, 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", "\n", "query_est_draft = f'''\n", "select em.PDV ,\n", "COALESCE(em.SKU_PARA , em.SKU) as SKU,\n", "em.DESCRICAO,\n", "d.salescurve as curva,\n", "d.codcategory as categoria,\n", "d.nextcycleprojection as proj_mar\n", ",d.secondtonextcycleprojection as 'proj_mar+1',\n", "em.[ESTOQUE ATUAL],em.[ESTOQUE EM TRANSITO],\n", "em.[PEDIDO PENDENTE],\n", "em.[DDV PREVISTO] ,\n", "d.pricesellin as 'preço'\n", "from estoque_mar em \n", "left join draft d on d.code = COALESCE(em.SKU_PARA , em.SKU) and d.loja_id = em.PDV \n", "where em.ORIGEM = 'BOT'\n", "'''\n", "df_draft_ = pd.read_sql(query_est_draft, conn)\n", "conn.close()\n", "\n", "#FALTA ATUALIZAR QUERY" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": [ "df_draft = df_draft_" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVSKUDESCRICAOcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTEDDV PREVISTOpreço
165128171634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN46.00.00.00.63NaN
166200061634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN3.00.00.00.09NaN
167208581634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN1.00.00.00.03NaN
168212771634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN7.00.00.00.05NaN
290209941634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN3.00.00.00.04NaN
.......................................
3495216241634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN9.00.00.00.06NaN
3496225411634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN6.00.00.00.02NaN
3497237011634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN27.00.00.00.17NaN
3498237031634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN0.00.00.00.00NaN
3499237041634NSPA GEL ESF CPO MORANGO RUBY 175GNoneNoneNaNNaN5.00.00.00.02NaN
\n", "

74 rows × 12 columns

\n", "
" ], "text/plain": [ " PDV SKU DESCRICAO curva categoria \\\n", "165 12817 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "166 20006 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "167 20858 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "168 21277 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "290 20994 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "... ... ... ... ... ... \n", "3495 21624 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "3496 22541 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "3497 23701 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "3498 23703 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "3499 23704 1634 NSPA GEL ESF CPO MORANGO RUBY 175G None None \n", "\n", " proj_mar proj_mar+1 ESTOQUE ATUAL ESTOQUE EM TRANSITO \\\n", "165 NaN NaN 46.0 0.0 \n", "166 NaN NaN 3.0 0.0 \n", "167 NaN NaN 1.0 0.0 \n", "168 NaN NaN 7.0 0.0 \n", "290 NaN NaN 3.0 0.0 \n", "... ... ... ... ... \n", "3495 NaN NaN 9.0 0.0 \n", "3496 NaN NaN 6.0 0.0 \n", "3497 NaN NaN 27.0 0.0 \n", "3498 NaN NaN 0.0 0.0 \n", "3499 NaN NaN 5.0 0.0 \n", "\n", " PEDIDO PENDENTE DDV PREVISTO preço \n", "165 0.0 0.63 NaN \n", "166 0.0 0.09 NaN \n", "167 0.0 0.03 NaN \n", "168 0.0 0.05 NaN \n", "290 0.0 0.04 NaN \n", "... ... ... ... \n", "3495 0.0 0.06 NaN \n", "3496 0.0 0.02 NaN \n", "3497 0.0 0.17 NaN \n", "3498 0.0 0.00 NaN \n", "3499 0.0 0.02 NaN \n", "\n", "[74 rows x 12 columns]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft[df_draft['SKU']=='1634']" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(280603, 12)" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft = df_draft[~df_draft['DESCRICAO'].str.contains('OUI', na=False)]\n", "\n", "df_draft.shape" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_45000\\1765304903.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_draft['ESTOQUE ATUAL'] = df_draft['ESTOQUE ATUAL'].astype(float)\n", "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_45000\\1765304903.py:3: 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_draft['ESTOQUE EM TRANSITO'] = df_draft['ESTOQUE EM TRANSITO'].astype(float)\n", "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_45000\\1765304903.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_draft['PEDIDO PENDENTE'] = df_draft['PEDIDO PENDENTE'].astype(float)\n" ] }, { "data": { "text/plain": [ "PDV object\n", "SKU object\n", "DESCRICAO object\n", "curva object\n", "categoria object\n", "proj_mar float64\n", "proj_mar+1 float64\n", "ESTOQUE ATUAL float64\n", "ESTOQUE EM TRANSITO float64\n", "PEDIDO PENDENTE float64\n", "DDV PREVISTO float64\n", "preço float64\n", "dtype: object" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft['ESTOQUE ATUAL'] = df_draft['ESTOQUE ATUAL'].astype(float)\n", "\n", "df_draft['ESTOQUE EM TRANSITO'] = df_draft['ESTOQUE EM TRANSITO'].astype(float)\n", "\n", "df_draft['PEDIDO PENDENTE'] = df_draft['PEDIDO PENDENTE'].astype(float)\n", "\n", "df_draft.dtypes" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_45000\\772623431.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_draft['DDV PREVISTO'] = df_draft['DDV PREVISTO'].astype('str').str.replace(',','.')\n", "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_45000\\772623431.py:3: 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_draft['DDV PREVISTO'] = df_draft['DDV PREVISTO'].astype(float)\n", "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_45000\\772623431.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_draft['DDV PREVISTO'] = df_draft['DDV PREVISTO'].fillna(0)\n" ] } ], "source": [ "df_draft['DDV PREVISTO'] = df_draft['DDV PREVISTO'].astype('str').str.replace(',','.')\n", "\n", "df_draft['DDV PREVISTO'] = df_draft['DDV PREVISTO'].astype(float)\n", "\n", "df_draft['DDV PREVISTO'] = df_draft['DDV PREVISTO'].fillna(0)" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [], "source": [ "df_draft = df_draft.groupby(['PDV','SKU','curva','categoria'])[['proj_mar','proj_mar+1','ESTOQUE ATUAL','ESTOQUE EM TRANSITO','PEDIDO PENDENTE','DDV PREVISTO','preço']].max().reset_index()" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [], "source": [ "df_tabela = df_tabela.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [], "source": [ "df_bi_preco = pd.read_excel(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\SUPRIMENTOS\\DB_PROMOÇÕES\\BOTICARIO\\C13\\preço BI\\TABELA DE PREÇOS (2).xlsx\")\n", "\n", "df_bi_preco = df_bi_preco.drop(columns=['Tipo Preço','CATEGORIA','LINHA','MARCA'])\n", "\n" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 1080\n", "1 1296\n", "2 1302\n", "3 1314\n", "4 1317\n", "Name: SKU_FINAL, dtype: object" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\n", "df_pivotadowawa['SKU_FINAL'] = df_pivotadowawa['SKU_FINAL'].astype(str)\n", "\n", "df_pivotadowawa['SKU_FINAL'].head()" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVSKUcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTEDDV PREVISTOpreço
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [PDV, SKU, curva, categoria, proj_mar, proj_mar+1, ESTOQUE ATUAL, ESTOQUE EM TRANSITO, PEDIDO PENDENTE, DDV PREVISTO, preço]\n", "Index: []" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft[df_draft['SKU']=='1634']" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [], "source": [ "df_draft = pd.merge(df_draft,df_pivotadowawa,left_on=['PDV','SKU'],right_on=['PDV','SKU_FINAL'],how='inner')" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVSKUcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTEDDV PREVISTO...C202506C202507C202508C202509C202510C202511C202512C202513C202514C202515
\n", "

0 rows × 31 columns

\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [PDV, SKU, curva, categoria, proj_mar, proj_mar+1, ESTOQUE ATUAL, ESTOQUE EM TRANSITO, PEDIDO PENDENTE, DDV PREVISTO, preço, SKU_FINAL, DESCRICAO, C202415, C202416, C202417, C202501, C202502, C202503, C202504, C202505, C202506, C202507, C202508, C202509, C202510, C202511, C202512, C202513, C202514, C202515]\n", "Index: []\n", "\n", "[0 rows x 31 columns]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft[df_draft['SKU']=='1634']" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(56364, 32)" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final = pd.merge(left=df_tabela,right=df_pdv,on='MATCH',how='left')\n", "\n", "df_final = df_final.drop_duplicates()\n", "\n", "df_final.shape" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "12522 732\n", "12817 732\n", "12818 732\n", "12820 732\n", "12823 732\n", " ... \n", "24257 732\n", "24268 732\n", "24269 732\n", "24293 732\n", "23813 732\n", "Name: count, Length: 77, dtype: int64" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVSKUcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTEDDV PREVISTO...C202506C202507C202508C202509C202510C202511C202512C202513C202514C202515
74143354652627BCUIDADOS FACIAIS4.03.00.00.03.00.13...0.00.00.00.00.00.00.00.00.00.0
74144354652627BCUIDADOS FACIAIS4.03.00.00.03.00.13...3.00.03.04.01.03.03.02.00.00.0
\n", "

2 rows × 31 columns

\n", "
" ], "text/plain": [ " PDV SKU curva categoria proj_mar proj_mar+1 \\\n", "74143 3546 52627 B CUIDADOS FACIAIS 4.0 3.0 \n", "74144 3546 52627 B CUIDADOS FACIAIS 4.0 3.0 \n", "\n", " ESTOQUE ATUAL ESTOQUE EM TRANSITO PEDIDO PENDENTE DDV PREVISTO ... \\\n", "74143 0.0 0.0 3.0 0.13 ... \n", "74144 0.0 0.0 3.0 0.13 ... \n", "\n", " C202506 C202507 C202508 C202509 C202510 C202511 C202512 C202513 \\\n", "74143 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "74144 3.0 0.0 3.0 4.0 1.0 3.0 3.0 2.0 \n", "\n", " C202514 C202515 \n", "74143 0.0 0.0 \n", "74144 0.0 0.0 \n", "\n", "[2 rows x 31 columns]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft[(df_draft['SKU'] == '52627') & (df_draft['PDV'] == '3546') ].head()" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['PDV', 'SKU', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', 'C202415', 'C202416', 'C202417',\n", " 'C202501', 'C202502', 'C202503', 'C202504', 'C202505', 'C202506',\n", " 'C202507', 'C202508', 'C202509', 'C202510', 'C202511', 'C202512',\n", " 'C202513', 'C202514', 'C202515'],\n", " dtype='object')" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft = df_draft.drop(columns='DESCRICAO')\n", "\n", "df_draft.columns" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20998 1207\n", "20997 1180\n", "20996 1154\n", "23711 1150\n", "20993 1135\n", " ... \n", "24269 553\n", "24253 546\n", "24268 529\n", "24254 476\n", "24258 392\n", "Name: count, Length: 77, dtype: int64" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "numericos = df_draft.columns[-18:]\n", "\n", "chaves = [col for col in df_draft.columns if col not in numericos]\n", "\n", "# 2. Fazer o groupby\n", "df_draft_agg = (\n", " df_draft\n", " .groupby(chaves, as_index=False)[numericos]\n", " .max()\n", ")\n", "\n", "df_draft_agg['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVSKUcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTEDDV PREVISTO...C202506C202507C202508C202509C202510C202511C202512C202513C202514C202515
\n", "

0 rows × 30 columns

\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [PDV, SKU, curva, categoria, proj_mar, proj_mar+1, ESTOQUE ATUAL, ESTOQUE EM TRANSITO, PEDIDO PENDENTE, DDV PREVISTO, preço, SKU_FINAL, C202415, C202416, C202417, C202501, C202502, C202503, C202504, C202505, C202506, C202507, C202508, C202509, C202510, C202511, C202512, C202513, C202514, C202515]\n", "Index: []\n", "\n", "[0 rows x 30 columns]" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft[df_draft['SKU']==1634]" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [], "source": [ "df_draft1 = df_draft_agg" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['PDV', 'SKU', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', 'C202415', 'C202416', 'C202417',\n", " 'C202501', 'C202502', 'C202503', 'C202504', 'C202505', 'C202506',\n", " 'C202507', 'C202508', 'C202509', 'C202510', 'C202511', 'C202512',\n", " 'C202513', 'C202514', 'C202515'],\n", " dtype='object')" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft1.columns" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [], "source": [ "df_draft1.to_clipboard()" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(56364, 61)" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['PDV'] = df_final['PDV'].astype('str')\n", "df_final['Código'] = df_final['Código'].astype('str')\n", "\n", "\n", "df_final = pd.merge(left=df_final,right=df_draft1,left_on=['PDV','Código'],right_on=['PDV','SKU'],how='left')\n", "df_final.shape" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CicloRegiãoCanalCódigoDescriçãoIAFTipo de pedidoFocoUnidade de negócioMarca...C202506C202507C202508C202509C202510C202511C202512C202513C202514C202515
\n", "

0 rows × 61 columns

\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [Ciclo, Região, Canal, Código, Descrição, IAF, Tipo de pedido, Foco, Unidade de negócio, Marca, Categoria, Subcategoria, Quantidade por caixa, Tipo de promoção, Catálogo, Tipo de produto, Ação consumidor, Percentual de desconto consumidor, Ação revendedor, Percentual de desconto revendedor, Sortimento P, Sortimento M, Sortimento G, MATCH, PDV, CANAL, DESCRIÇÃO PDV, UF, MARCA, ANALISTA, SUPERVISOR, ANALISTA EUD, SKU, curva, categoria, proj_mar, proj_mar+1, ESTOQUE ATUAL, ESTOQUE EM TRANSITO, PEDIDO PENDENTE, DDV PREVISTO, preço, SKU_FINAL, C202415, C202416, C202417, C202501, C202502, C202503, C202504, C202505, C202506, C202507, C202508, C202509, C202510, C202511, C202512, C202513, C202514, C202515]\n", "Index: []\n", "\n", "[0 rows x 61 columns]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final[df_final['Código']=='1634'].head()" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "12522 732\n", "12817 732\n", "12818 732\n", "12820 732\n", "12823 732\n", " ... \n", "24257 732\n", "24268 732\n", "24269 732\n", "24293 732\n", "23813 732\n", "Name: count, Length: 77, dtype: int64" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.drop(columns=['Sortimento P', 'Sortimento M',\n", " 'Sortimento G','MARCA'])" ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [], "source": [ "calendario = pd.read_excel(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\SUPRIMENTOS\\BD_LANÇAMENTOS\\BASE DE DADOS LANÇAMENTO\\BOT\\CICLO 9\\CALENDARIO_CICLO\\Ciclo_Expandido_com_Datas.xlsx\")\n" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CicloINICIO CICLOFIM CICLODURAÇÃODateNUM_CICLOANO_CICLOCICLOMAIS2dias_ate_inicio
2507C2025172025-12-012025-12-25252025-12-0117C2025C20251940
\n", "
" ], "text/plain": [ " Ciclo INICIO CICLO FIM CICLO DURAÇÃO Date NUM_CICLO \\\n", "2507 C202517 2025-12-01 2025-12-25 25 2025-12-01 17 \n", "\n", " ANO_CICLO CICLOMAIS2 dias_ate_inicio \n", "2507 C2025 C202519 40 " ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "calendario['Date'] = pd.to_datetime(calendario['Date'])\n", "\n", "# Get today (normalized to midnight)\n", "today = pd.Timestamp(\"today\").normalize()\n", "\n", "calendario['NUM_CICLO'] = calendario['Ciclo'].str[-2:].astype(int)\n", "calendario['ANO_CICLO'] = calendario['Ciclo'].str[0:5]\n", "\n", "\n", "calendario = calendario[calendario['MARCA'] == \"BOTICARIO\"]\n", "\n", "calendario = calendario.drop(columns='MARCA')\n", "\n", "calendario['CICLOMAIS2'] = calendario['ANO_CICLO'].astype(str) + (calendario['NUM_CICLO'].astype(int) + 2).astype(str).str.zfill(2) # >>>>>>>>> MUDAR PRA CICLO CORRETO \n", "\n", "ciclo_mais2 = calendario[calendario['Date'].dt.normalize() == today]['CICLOMAIS2'].iloc[0]\n", "\n", "\n", "# Filter rows where date matches today\n", "filtered_calendario = calendario[calendario['Ciclo'] == ciclo_mais2][:1]\n", "\n", "\n", "filtered_calendario['dias_ate_inicio'] = filtered_calendario['INICIO CICLO'].iloc[0] - today\n", "\n", "filtered_calendario['dias_ate_inicio'] = filtered_calendario['dias_ate_inicio'].dt.days.astype(int)\n", "\n", "filtered_calendario\n" ] }, { "cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [], "source": [ "filtered_calendario['MATCH'] = 1" ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [], "source": [ "df_pdv['UF'] = np.where(df_pdv['UF'] == 'VDC','BA',df_pdv['UF'])" ] }, { "cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [], "source": [ "df_draft['PDV'] = df_draft['PDV'].astype(str)" ] }, { "cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(56364, 62)" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final = pd.merge(left=df_final, right=filtered_calendario[['Ciclo','INICIO CICLO','FIM CICLO','DURAÇÃO','MATCH','dias_ate_inicio']], on='MATCH',how='left')\n", "df_final.shape" ] }, { "cell_type": "code", "execution_count": 46, "metadata": {}, "outputs": [], "source": [ "df_final['Código'] = df_final['Código'].astype('Int64') \n", "df_final['PDV'] = df_final['PDV'].astype('Int64') " ] }, { "cell_type": "code", "execution_count": 47, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SKU1SKU2DescriçãoUFPCPVchave sku2chave sku1
08491884918ESTJ BOTI CALENDARIO ADVENTO NAT/24AL393.701300.0AL84918AL84918
18491884918ESTJ BOTI CALENDARIO ADVENTO NAT/24BA436.731300.0BA84918BA84918
28491884918ESTJ BOTI CALENDARIO ADVENTO NAT/24SE379.941300.0SE84918SE84918
35423054230MALBEC DES COL 20 ANOS 100mlAL138.54499.9AL54230AL54230
45423054230MALBEC DES COL 20 ANOS 100mlBA148.66499.9BA54230BA54230
\n", "
" ], "text/plain": [ " SKU1 SKU2 Descrição UF PC PV \\\n", "0 84918 84918 ESTJ BOTI CALENDARIO ADVENTO NAT/24 AL 393.70 1300.0 \n", "1 84918 84918 ESTJ BOTI CALENDARIO ADVENTO NAT/24 BA 436.73 1300.0 \n", "2 84918 84918 ESTJ BOTI CALENDARIO ADVENTO NAT/24 SE 379.94 1300.0 \n", "3 54230 54230 MALBEC DES COL 20 ANOS 100ml AL 138.54 499.9 \n", "4 54230 54230 MALBEC DES COL 20 ANOS 100ml BA 148.66 499.9 \n", "\n", " chave sku2 chave sku1 \n", "0 AL84918 AL84918 \n", "1 BA84918 BA84918 \n", "2 SE84918 SE84918 \n", "3 AL54230 AL54230 \n", "4 BA54230 BA54230 " ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_bi_preco['SKU2'] = df_bi_preco['SKU2'].astype('Int64')\n", "df_bi_preco.head()" ] }, { "cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(56364, 69)" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\n", "\n", "df_final = pd.merge(left=df_final,right=df_bi_preco,right_on=['UF','SKU2'],left_on=['UF','Código'],how='left')\n", "df_final.shape " ] }, { "cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [], "source": [ "df_bi_preco['SKU1'] = df_bi_preco['SKU1'].fillna(0)" ] }, { "cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(56420, 73)\n" ] } ], "source": [ "# Conversão segura para Int64 (nullable)\n", "df_bi_preco['SKU1'] = pd.to_numeric(df_bi_preco['SKU1'], errors='coerce').astype('Int64')\n", "\n", "# Realiza o merge\n", "df_final = pd.merge(\n", " left=df_final,\n", " right=df_bi_preco[['SKU1', 'SKU2', 'UF', 'PC', 'PV']],\n", " right_on=['UF', 'SKU1'],\n", " left_on=['UF', 'Código'],\n", " how='left'\n", ")\n", "\n", "# Verifica o resultado\n", "print(df_final.shape)\n" ] }, { "cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [], "source": [ "df_final['PRECO DE COMPRA'] = np.where(~df_final['PC_x'].isna(),df_final['PC_x'],df_final['PC_y'])\n", "\n", "df_final['PRECO DE VENDA'] = np.where(~df_final['PV_x'].isna(),df_final['PV_x'],df_final['PV_y'])\n", "\n" ] }, { "cell_type": "code", "execution_count": 52, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "CANAL\n", "TODOS 44697\n", "VD 11723\n", "Name: count, dtype: int64" ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['CANAL'] = np.where((df_final['CANAL'] == 'LJ') | (df_final['CANAL'] == 'HIB'), \"TODOS\" , np.where((df_final['CANAL'] == 'CD') | (df_final['CANAL'] == 'VD'), \"VD\", df_final['CANAL']))\n", "\n", "df_final['CANAL'].value_counts()" ] }, { "cell_type": "code", "execution_count": 53, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 54, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.drop(columns=['SKU1_x','SKU1_y','SKU2_x','SKU2_y','PC_x', 'PV_x','PC_y', 'PV_y','Subcategoria',\n", "'Quantidade por caixa'\n", "])" ] }, { "cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [], "source": [ "df_final['UFPRODUTO'] = df_final['UF'].astype(str) + df_final['Código'].astype(str)\n" ] }, { "cell_type": "code", "execution_count": 56, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.rename(columns={'Compra inteligente Próximo Ciclo + 1':'Compra inteligente Próximo Ciclo','Arquivo_Origem': 'MARCA'})" ] }, { "cell_type": "code", "execution_count": 57, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "np.int64(0)" ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final = df_final[~df_final['Marca'].isna()]\n", "df_final['Marca'].isna().sum()" ] }, { "cell_type": "code", "execution_count": 58, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição_x', 'IAF',\n", " 'Tipo de pedido', 'Foco', 'Unidade de negócio', 'Marca', 'Categoria',\n", " 'Tipo de promoção', 'Catálogo', 'Tipo de produto', 'Ação consumidor',\n", " 'Percentual de desconto consumidor', 'Ação revendedor',\n", " 'Percentual de desconto revendedor', 'MATCH', 'PDV', 'CANAL',\n", " 'DESCRIÇÃO PDV', 'UF', 'ANALISTA', 'SUPERVISOR', 'ANALISTA EUD', 'SKU',\n", " 'curva', 'categoria', 'proj_mar', 'proj_mar+1', 'ESTOQUE ATUAL',\n", " 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DDV PREVISTO', 'preço',\n", " 'SKU_FINAL', 'C202415', 'C202416', 'C202417', 'C202501', 'C202502',\n", " 'C202503', 'C202504', 'C202505', 'C202506', 'C202507', 'C202508',\n", " 'C202509', 'C202510', 'C202511', 'C202512', 'C202513', 'C202514',\n", " 'C202515', 'Ciclo_y', 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO',\n", " 'dias_ate_inicio', 'Descrição_y', 'chave sku2', 'chave sku1',\n", " 'PRECO DE COMPRA', 'PRECO DE VENDA', 'UFPRODUTO'],\n", " dtype='object')" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 59, "metadata": {}, "outputs": [], "source": [ "df_vdc = pd.read_csv(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\SUPRIMENTOS\\DB_PROMOÇÕES\\BOTICARIO\\C10\\VENDA VITORIA 2024\\VENDA VITORIA.csv\")\n", "\n", "df_vdc['PRODUTO'] = df_vdc['PRODUTO'].astype('Int64')\n", "\n", "df_final['Código'] = df_final['Código'].astype('Int64')\n", "\n", "df_final =pd.merge(left=df_final,right=df_vdc,left_on= ['PDV','Código'],right_on= ['PDV GINSENG','PRODUTO'],how='left' )\n", "\n", "df_final[df_final.columns[-1]] = df_final[df_final.columns[-1]].fillna(0)" ] }, { "cell_type": "code", "execution_count": 60, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDV GINSENGPRODUTO202401202402202403202404202405202406202407202408202409202410202411202412202413202414202415202416
1235237017404312241216814614120810442014
30332370274043222822101414101282261436100
51712370374043101861862200102224226216
\n", "
" ], "text/plain": [ " PDV GINSENG PRODUTO 202401 202402 202403 202404 202405 202406 \\\n", "1235 23701 74043 12 24 12 16 8 14 \n", "3033 23702 74043 2 22 8 22 10 14 \n", "5171 23703 74043 10 18 6 18 6 2 \n", "\n", " 202407 202408 202409 202410 202411 202412 202413 202414 202415 \\\n", "1235 6 14 12 0 8 10 4 42 0 \n", "3033 14 10 12 8 22 6 14 36 10 \n", "5171 20 0 10 2 22 4 2 26 2 \n", "\n", " 202416 \n", "1235 14 \n", "3033 0 \n", "5171 16 " ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_vdc[df_vdc['PRODUTO']== 74043].head()" ] }, { "cell_type": "code", "execution_count": 61, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.fillna(0)\n" ] }, { "cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição_x', 'IAF',\n", " 'Tipo de pedido', 'Foco', 'Unidade de negócio', 'Marca', 'Categoria',\n", " 'Tipo de promoção', 'Catálogo', 'Tipo de produto', 'Ação consumidor',\n", " 'Percentual de desconto consumidor', 'Ação revendedor',\n", " 'Percentual de desconto revendedor', 'MATCH', 'PDV', 'CANAL',\n", " 'DESCRIÇÃO PDV', 'UF', 'ANALISTA', 'SUPERVISOR', 'ANALISTA EUD', 'SKU',\n", " 'curva', 'categoria', 'proj_mar', 'proj_mar+1', 'ESTOQUE ATUAL',\n", " 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DDV PREVISTO', 'preço',\n", " 'SKU_FINAL', 'C202415', 'C202416', 'C202417', 'C202501', 'C202502',\n", " 'C202503', 'C202504', 'C202505', 'C202506', 'C202507', 'C202508',\n", " 'C202509', 'C202510', 'C202511', 'C202512', 'C202513', 'C202514',\n", " 'C202515', 'Ciclo_y', 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO',\n", " 'dias_ate_inicio', 'Descrição_y', 'chave sku2', 'chave sku1',\n", " 'PRECO DE COMPRA', 'PRECO DE VENDA', 'UFPRODUTO', 'PDV GINSENG',\n", " 'PRODUTO', '202401', '202402', '202403', '202404', '202405', '202406',\n", " '202407', '202408', '202409', '202410', '202411', '202412', '202413',\n", " '202414', '202415', '202416'],\n", " dtype='object')" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 63, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202401', '202402', '202403', '202404', '202405', '202406', '202407',\n", " '202408', '202409', '202410', '202411', '202412', '202413', '202414',\n", " '202415', '202416'],\n", " dtype='object')" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[-16:]" ] }, { "cell_type": "code", "execution_count": 64, "metadata": {}, "outputs": [], "source": [ "df_final.to_clipboard()" ] }, { "cell_type": "code", "execution_count": 65, "metadata": {}, "outputs": [], "source": [ "import re\n", "\n", "# identificar colunas de ciclo puro (ex: '202407')\n", "colunas_ciclo_puro = [col for col in df_final.columns[-16:]]\n", "\n", "for col in colunas_ciclo_puro:\n", " col_hist = f'C{col}'\n", " if col_hist in df_final.columns:\n", " # mantém o maior valor entre a coluna histórica e a pura\n", " df_final[col_hist] = df_final[[col_hist, col]].max(axis=1)\n", "\n", "# remove as colunas puras\n", "if colunas_ciclo_puro:\n", " df_final.drop(columns=colunas_ciclo_puro, inplace=True)\n" ] }, { "cell_type": "code", "execution_count": 66, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição_x', 'IAF',\n", " 'Tipo de pedido', 'Foco', 'Unidade de negócio', 'Marca', 'Categoria',\n", " 'Tipo de promoção', 'Catálogo', 'Tipo de produto', 'Ação consumidor',\n", " 'Percentual de desconto consumidor', 'Ação revendedor',\n", " 'Percentual de desconto revendedor', 'MATCH', 'PDV', 'CANAL',\n", " 'DESCRIÇÃO PDV', 'UF', 'ANALISTA', 'SUPERVISOR', 'ANALISTA EUD', 'SKU',\n", " 'curva', 'categoria', 'proj_mar', 'proj_mar+1', 'ESTOQUE ATUAL',\n", " 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DDV PREVISTO', 'preço',\n", " 'SKU_FINAL', 'C202415', 'C202416', 'C202417', 'C202501', 'C202502',\n", " 'C202503', 'C202504', 'C202505', 'C202506', 'C202507', 'C202508',\n", " 'C202509', 'C202510', 'C202511', 'C202512', 'C202513', 'C202514',\n", " 'C202515', 'Ciclo_y', 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO',\n", " 'dias_ate_inicio', 'Descrição_y', 'chave sku2', 'chave sku1',\n", " 'PRECO DE COMPRA', 'PRECO DE VENDA', 'UFPRODUTO', 'PDV GINSENG',\n", " 'PRODUTO'],\n", " dtype='object')" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 67, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DATA_VENDAPDVpdvs cpDESC PDVCódigoDescriçãoQuantidadeFaturamentoCicloDate
02025-04-011299324253PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA1004FLORATTA DES COL MY BLUE 75ml198,99C2025052025-04-01
12025-04-011299324253PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA4539BOTIK CR FAC FIRMADOR AC/HIAL 40g V21129,05C2025052025-04-01
22025-04-011299324253PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA29046BOTICOLL CONNEX DES BDY SPR 100ml V6 PCK138,9C2025052025-04-01
32025-04-011299324253PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA47154CBEM DES ROLL S/ALUM 55ml121,98C2025052025-04-01
42025-04-011299324253PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA47411ARBO NECESS LONA157,26C2025052025-04-01
\n", "
" ], "text/plain": [ " DATA_VENDA PDV pdvs cp DESC PDV \\\n", "0 2025-04-01 12993 24253 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA \n", "1 2025-04-01 12993 24253 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA \n", "2 2025-04-01 12993 24253 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA \n", "3 2025-04-01 12993 24253 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA \n", "4 2025-04-01 12993 24253 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA \n", "\n", " Código Descrição Quantidade Faturamento \\\n", "0 1004 FLORATTA DES COL MY BLUE 75ml 1 98,99 \n", "1 4539 BOTIK CR FAC FIRMADOR AC/HIAL 40g V2 1 129,05 \n", "2 29046 BOTICOLL CONNEX DES BDY SPR 100ml V6 PCK 1 38,9 \n", "3 47154 CBEM DES ROLL S/ALUM 55ml 1 21,98 \n", "4 47411 ARBO NECESS LONA 1 57,26 \n", "\n", " Ciclo Date \n", "0 C202505 2025-04-01 \n", "1 C202505 2025-04-01 \n", "2 C202505 2025-04-01 \n", "3 C202505 2025-04-01 \n", "4 C202505 2025-04-01 " ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_irece = pd.read_csv(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\DADOS DOURADO\\VENDA\\COMPILADO_VENDAS.csv\", sep=';')\n", "\n", "df_irece['DATA_VENDA'] = pd.to_datetime(df_irece['DATA_VENDA'], dayfirst=True)\n", "\n", "df_irece = pd.merge(df_irece,calendario[['Ciclo','Date']],how='left', left_on='DATA_VENDA',right_on='Date')\n", "\n", "df_irece.head()" ] }, { "cell_type": "code", "execution_count": 68, "metadata": {}, "outputs": [], "source": [ "df_irece_agrupado = df_irece.groupby(['PDV','Código','Ciclo'])['Quantidade'].sum().reset_index()\n", "\n", "df_pivotado = df_irece_agrupado.pivot(index=['PDV', 'Código'], columns='Ciclo', values='Quantidade').reset_index()\n", "\n", "df_pivotado = df_pivotado.fillna(0)" ] }, { "cell_type": "code", "execution_count": 69, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['C202415', 'C202416', 'C202417', 'C202501', 'C202502', 'C202503',\n", " 'C202504', 'C202505', 'C202506', 'C202507', 'C202508', 'C202509',\n", " 'C202510', 'C202511', 'C202512', 'C202513', 'C202514', 'C202515'],\n", " dtype='object')" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[37:55]" ] }, { "cell_type": "code", "execution_count": 70, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição_x', 'IAF',\n", " 'Tipo de pedido', 'Foco', 'Unidade de negócio', 'Marca', 'Categoria',\n", " 'Tipo de promoção', 'Catálogo', 'Tipo de produto', 'Ação consumidor',\n", " 'Percentual de desconto consumidor', 'Ação revendedor',\n", " 'Percentual de desconto revendedor', 'MATCH', 'PDV', 'CANAL',\n", " 'DESCRIÇÃO PDV', 'UF', 'ANALISTA', 'SUPERVISOR', 'ANALISTA EUD', 'SKU',\n", " 'curva', 'categoria', 'proj_mar', 'proj_mar+1', 'ESTOQUE ATUAL',\n", " 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DDV PREVISTO', 'preço',\n", " 'SKU_FINAL', '202415', '202416', '202417', '202501', '202502', '202503',\n", " '202504', '202505', '202506', '202507', '202508', '202509', '202510',\n", " '202511', '202512', '202513', '202514', '202515', 'Ciclo_y',\n", " 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO', 'dias_ate_inicio',\n", " 'Descrição_y', 'chave sku2', 'chave sku1', 'PRECO DE COMPRA',\n", " 'PRECO DE VENDA', 'UFPRODUTO', 'PDV GINSENG', 'PRODUTO'],\n", " dtype='object')" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "for col_c in df_final.columns[37:55]:\n", " sufixo = col_c[1:] # Remove o 'C' do início, ex: 'C202407' -> '202407'\n", " df_final.columns = df_final.columns.str.replace(\"C20\", \"20\", regex=True)\n", "\n", "\n", "df_final.columns" ] }, { "cell_type": "code", "execution_count": 71, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Ciclo_xRegiãoCanalCódigoDescrição_xIAFTipo de pedidoFocoUnidade de negócioMarca...C202415C202416C202417C202501C202502C202503C202504C202505C202506C202507
0202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...0.00.00.00.00.00.00.00.00.00.0
1202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...0.00.00.00.00.00.00.00.00.00.0
2202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...0.00.00.00.00.00.00.00.00.00.0
3202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...0.00.00.00.00.00.00.00.00.00.0
4202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...0.00.00.00.00.00.00.00.00.00.0
\n", "

5 rows × 92 columns

\n", "
" ], "text/plain": [ " Ciclo_x Região Canal Código Descrição_x IAF \\\n", "0 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "1 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "2 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "3 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "4 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "\n", " Tipo de pedido Foco Unidade de negócio Marca ... C202415 C202416 C202417 \\\n", "0 Semanal Não BOT ARBO ... 0.0 0.0 0.0 \n", "1 Semanal Não BOT ARBO ... 0.0 0.0 0.0 \n", "2 Semanal Não BOT ARBO ... 0.0 0.0 0.0 \n", "3 Semanal Não BOT ARBO ... 0.0 0.0 0.0 \n", "4 Semanal Não BOT ARBO ... 0.0 0.0 0.0 \n", "\n", " C202501 C202502 C202503 C202504 C202505 C202506 C202507 \n", "0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "\n", "[5 rows x 92 columns]" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final = pd.merge(df_final,df_pivotado,how='left',on=['PDV','Código'])\n", "\n", "df_final = df_final.fillna(0)\n", "\n", "df_final.head()" ] }, { "cell_type": "code", "execution_count": 72, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição_x', 'IAF',\n", " 'Tipo de pedido', 'Foco', 'Unidade de negócio', 'Marca', 'Categoria',\n", " 'Tipo de promoção', 'Catálogo', 'Tipo de produto', 'Ação consumidor',\n", " 'Percentual de desconto consumidor', 'Ação revendedor',\n", " 'Percentual de desconto revendedor', 'MATCH', 'PDV', 'CANAL',\n", " 'DESCRIÇÃO PDV', 'UF', 'ANALISTA', 'SUPERVISOR', 'ANALISTA EUD', 'SKU',\n", " 'curva', 'categoria', 'proj_mar', 'proj_mar+1', 'ESTOQUE ATUAL',\n", " 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DDV PREVISTO', 'preço',\n", " 'SKU_FINAL', '202415', '202416', '202417', '202501', '202502', '202503',\n", " '202504', '202505', '202506', '202507', '202508', '202509', '202510',\n", " '202511', '202512', '202513', '202514', '202515', 'Ciclo_y',\n", " 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO', 'dias_ate_inicio',\n", " 'Descrição_y', 'chave sku2', 'chave sku1', 'PRECO DE COMPRA',\n", " 'PRECO DE VENDA', 'UFPRODUTO', 'PDV GINSENG', 'PRODUTO', 'C202401',\n", " 'C202402', 'C202403', 'C202404', 'C202405', 'C202406', 'C202407',\n", " 'C202408', 'C202409', 'C202410', 'C202411', 'C202412', 'C202413',\n", " 'C202414', 'C202415', 'C202416', 'C202417', 'C202501', 'C202502',\n", " 'C202503', 'C202504', 'C202505', 'C202506', 'C202507'],\n", " dtype='object')" ] }, "execution_count": 72, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 73, "metadata": {}, "outputs": [], "source": [ "\n", "\n", "# 1. Identificar colunas no formato 'C2024xx', 'C2025xx', etc.\n", "colunas_ciclo_c = [col for col in df_pivotado.columns if re.fullmatch(r'C20\\d{4}', col)]\n", "\n", "for col_c in colunas_ciclo_c:\n", " sufixo = col_c[1:] # Remove o 'C' do início, ex: 'C202407' -> '202407'\n", " col_hist = col_c\n", " \n", " if col_hist in df_final.columns:\n", " # Pega o maior valor entre a coluna de histórico e a de ciclo com \"C\"\n", " df_final[col_hist] = df_final[[col_hist, col_c]].max(axis=1)\n", "\n", "# (Opcional) Remover as colunas 'C20xxxx' após o merge\n", "df_final.drop(columns=colunas_ciclo_c, inplace=True)\n" ] }, { "cell_type": "code", "execution_count": 74, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição_x', 'IAF',\n", " 'Tipo de pedido', 'Foco', 'Unidade de negócio', 'Marca', 'Categoria',\n", " 'Tipo de promoção', 'Catálogo', 'Tipo de produto', 'Ação consumidor',\n", " 'Percentual de desconto consumidor', 'Ação revendedor',\n", " 'Percentual de desconto revendedor', 'MATCH', 'PDV', 'CANAL',\n", " 'DESCRIÇÃO PDV', 'UF', 'ANALISTA', 'SUPERVISOR', 'ANALISTA EUD', 'SKU',\n", " 'curva', 'categoria', 'proj_mar', 'proj_mar+1', 'ESTOQUE ATUAL',\n", " 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DDV PREVISTO', 'preço',\n", " 'SKU_FINAL', '202415', '202416', '202417', '202501', '202502', '202503',\n", " '202504', '202505', '202506', '202507', '202508', '202509', '202510',\n", " '202511', '202512', '202513', '202514', '202515', 'Ciclo_y',\n", " 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO', 'dias_ate_inicio',\n", " 'Descrição_y', 'chave sku2', 'chave sku1', 'PRECO DE COMPRA',\n", " 'PRECO DE VENDA', 'UFPRODUTO', 'PDV GINSENG', 'PRODUTO'],\n", " dtype='object')" ] }, "execution_count": 74, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 75, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "202401 C202401\n", "202402 C202402\n", "202403 C202403\n", "202404 C202404\n", "202405 C202405\n", "202406 C202406\n", "202407 C202407\n", "202408 C202408\n", "202409 C202409\n", "202410 C202410\n", "202411 C202411\n", "202412 C202412\n", "202413 C202413\n", "202414 C202414\n", "202415 C202415\n", "202416 C202416\n", "202417 C202417\n", "202501 C202501\n", "202502 C202502\n", "202503 C202503\n", "202504 C202504\n", "202505 C202505\n", "202506 C202506\n", "202507 C202507\n" ] } ], "source": [ "colunas_ciclo_c = [col for col in df_pivotado.columns if re.fullmatch(r'C20\\d{4}', col)]\n", "\n", "for col_c in colunas_ciclo_c:\n", " sufixo = col_c[1:] # Remove o 'C' do início, ex: 'C202407' -> '202407'\n", " col_hist = col_c\n", " print(sufixo,col_c)\n" ] }, { "cell_type": "code", "execution_count": 76, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.drop(columns=['Ciclo_y'])" ] }, { "cell_type": "code", "execution_count": 77, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição_x', 'IAF',\n", " 'Tipo de pedido', 'Foco', 'Unidade de negócio', 'Marca', 'Categoria',\n", " 'Tipo de promoção', 'Catálogo', 'Tipo de produto', 'Ação consumidor',\n", " 'Percentual de desconto consumidor', 'Ação revendedor',\n", " 'Percentual de desconto revendedor', 'MATCH', 'PDV', 'CANAL',\n", " 'DESCRIÇÃO PDV', 'UF', 'ANALISTA', 'SUPERVISOR', 'ANALISTA EUD', 'SKU',\n", " 'curva', 'categoria', 'proj_mar', 'proj_mar+1', 'ESTOQUE ATUAL',\n", " 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DDV PREVISTO', 'preço',\n", " 'SKU_FINAL', '202415', '202416', '202417', '202501', '202502', '202503',\n", " '202504', '202505', '202506', '202507', '202508', '202509', '202510',\n", " '202511', '202512', '202513', '202514', '202515', 'INICIO CICLO',\n", " 'FIM CICLO', 'DURAÇÃO', 'dias_ate_inicio', 'Descrição_y', 'chave sku2',\n", " 'chave sku1', 'PRECO DE COMPRA', 'PRECO DE VENDA', 'UFPRODUTO',\n", " 'PDV GINSENG', 'PRODUTO'],\n", " dtype='object')" ] }, "execution_count": 77, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 78, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(56364, 67)" ] }, "execution_count": 78, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final = df_final.drop_duplicates()\n", "df_final.shape" ] }, { "cell_type": "code", "execution_count": 79, "metadata": {}, "outputs": [], "source": [ "df_final['chave'] = df_final['PDV'].astype('str') + df_final['Código'].astype('str')" ] }, { "cell_type": "code", "execution_count": 80, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.fillna(0)\n" ] }, { "cell_type": "code", "execution_count": 81, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['SKU_FINAL', '202415', '202416', '202417', '202501', '202502', '202503',\n", " '202504', '202505', '202506', '202507', '202508', '202509', '202510',\n", " '202511', '202512', '202513', '202514'],\n", " dtype='object')" ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[36:54]" ] }, { "cell_type": "code", "execution_count": 82, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202415', '202416', '202417', '202501', '202502', '202503', '202504',\n", " '202505', '202506', '202507', '202508', '202509', '202510', '202511',\n", " '202512', '202513', '202514', '202515'],\n", " dtype='object')" ] }, "execution_count": 82, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[37:55]" ] }, { "cell_type": "code", "execution_count": 83, "metadata": {}, "outputs": [], "source": [ "# Define list of target columns\n", "sales_2024_cols = df_final.columns[37:55]\n", "# Create a new column with the row-wise max\n", "df_final['PICO DE VENDAS 2024'] = df_final[sales_2024_cols].max(axis=1)\n", "\n" ] }, { "cell_type": "code", "execution_count": 84, "metadata": {}, "outputs": [], "source": [ "vendas_6_meses = df_final.columns[49:55]\n", "\n", "df_final['Pico Vendas Ultimos 6 ciclos'] = df_final[vendas_6_meses].max(axis=1)" ] }, { "cell_type": "code", "execution_count": 85, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202415', '202416', '202417', '202501', '202502', '202503', '202504',\n", " '202505', '202506', '202507', '202508', '202509', '202510', '202511',\n", " '202512', '202513', '202514', '202515'],\n", " dtype='object')" ] }, "execution_count": 85, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[37:55]" ] }, { "cell_type": "code", "execution_count": 86, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202415', '202416', '202417', '202501', '202502', '202503', '202504',\n", " '202505', '202506', '202507', '202508', '202509', '202510', '202511',\n", " '202512', '202513', '202514', '202515'],\n", " dtype='object')" ] }, "execution_count": 86, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[37:55]" ] }, { "cell_type": "code", "execution_count": 87, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Ciclo_xRegiãoCanalCódigoDescrição_xIAFTipo de pedidoFocoUnidade de negócioMarca...chave sku2chave sku1PRECO DE COMPRAPRECO DE VENDAUFPRODUTOPDV GINSENGPRODUTOchavePICO DE VENDAS 2024Pico Vendas Ultimos 6 ciclos
0202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...AL83961AL7340015.1547.9AL839610.00125228396141.010.0
1202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...AL83961AL7340015.1547.9AL839610.00128178396197.046.0
2202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...AL83961AL7340015.1547.9AL839610.00128188396127.016.0
3202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...AL83961AL7340015.1547.9AL839610.00128208396124.09.0
4202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...AL83961AL7340015.1547.9AL839610.00128238396120.06.0
\n", "

5 rows × 70 columns

\n", "
" ], "text/plain": [ " Ciclo_x Região Canal Código Descrição_x IAF \\\n", "0 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "1 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "2 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "3 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "4 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "\n", " Tipo de pedido Foco Unidade de negócio Marca ... chave sku2 chave sku1 \\\n", "0 Semanal Não BOT ARBO ... AL83961 AL73400 \n", "1 Semanal Não BOT ARBO ... AL83961 AL73400 \n", "2 Semanal Não BOT ARBO ... AL83961 AL73400 \n", "3 Semanal Não BOT ARBO ... AL83961 AL73400 \n", "4 Semanal Não BOT ARBO ... AL83961 AL73400 \n", "\n", " PRECO DE COMPRA PRECO DE VENDA UFPRODUTO PDV GINSENG PRODUTO chave \\\n", "0 15.15 47.9 AL83961 0.0 0 1252283961 \n", "1 15.15 47.9 AL83961 0.0 0 1281783961 \n", "2 15.15 47.9 AL83961 0.0 0 1281883961 \n", "3 15.15 47.9 AL83961 0.0 0 1282083961 \n", "4 15.15 47.9 AL83961 0.0 0 1282383961 \n", "\n", " PICO DE VENDAS 2024 Pico Vendas Ultimos 6 ciclos \n", "0 41.0 10.0 \n", "1 97.0 46.0 \n", "2 27.0 16.0 \n", "3 24.0 9.0 \n", "4 20.0 6.0 \n", "\n", "[5 rows x 70 columns]" ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.head()" ] }, { "cell_type": "code", "execution_count": 88, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_45000\\3801733982.py:27: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.\n", " .apply(calcular_crescimento) # retorna índice com PDV\n" ] } ], "source": [ "# Define as colunas mensais\n", "colunas_mensais = df_final.columns[37:55]\n", "\n", "# Função de cálculo por grupo\n", "def calcular_crescimento(grupo):\n", " soma_mensal = grupo[colunas_mensais].sum()\n", " variacao_mensal = soma_mensal.pct_change().dropna()\n", " variacao_mensal = variacao_mensal[np.isfinite(variacao_mensal)]\n", "\n", " if len(variacao_mensal) == 0:\n", " return pd.Series({'CRESCIMENTO': np.nan})\n", "\n", " media = variacao_mensal.mean()\n", " desvio = variacao_mensal.std()\n", "\n", " limite_sup = media + 2 * desvio\n", " limite_inf = media - 2 * desvio\n", "\n", " variacoes_filtradas = variacao_mensal[variacao_mensal.between(limite_inf, limite_sup)]\n", " crescimento = round(variacoes_filtradas.mean(), 4)\n", " return pd.Series({'CRESCIMENTO': crescimento})\n", "\n", "# Aplica a função por PDV\n", "crescimento_por_pdv = (\n", " df_final\n", " .groupby('PDV')\n", " .apply(calcular_crescimento) # retorna índice com PDV\n", " .reset_index(level=0) # reseta só o nível PDV\n", ")\n", "\n", "# Merge do resultado de volta no dataframe original\n", "df_final = df_final.merge(crescimento_por_pdv, on='PDV', how='left')\n" ] }, { "cell_type": "code", "execution_count": 89, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "np.float64(-0.0156)" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Suponha que os meses estão nas colunas 10 a 26 (17 colunas = 17 meses)\n", "colunas_mensais = df_final.columns[37:55]\n", "\n", "# Passo 1: Soma todas as linhas (itens) por mês → resultado: total por mês\n", "soma_mensal = df_final[colunas_mensais].sum()\n", "\n", "# Passo 2: Calcula a variação percentual de um mês para o outro\n", "variacao_mensal = soma_mensal.pct_change()\n", "variacao_mensal = variacao_mensal.dropna()\n", "\n", "variacao_mensal = variacao_mensal[np.isfinite(variacao_mensal)]\n", "\n", "# Passo 3: Calcula a média da variação (ignorando o primeiro NaN)\n", "media_variacao = variacao_mensal[1:].mean()\n", "\n", "# Calcula média e desvio padrão\n", "media = variacao_mensal.mean()\n", "desvio = variacao_mensal.std()\n", "\n", "# Define limite (ex: 2 desvios padrão)\n", "limite_superior = media + 2 * desvio\n", "limite_inferior = media - 2 * desvio\n", "\n", "# Filtra dados dentro do limite\n", "filtro = variacao_mensal.between(limite_inferior, limite_superior)\n", "df_filtrado = variacao_mensal[filtro]\n", "CRESCIMENTO = round(df_filtrado.mean(),4)\n", "\n", "df_final['CRESCIMENTO_GERAL'] = 0.2\n", "\n", "CRESCIMENTO\n" ] }, { "cell_type": "code", "execution_count": 90, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202415', '202416', '202417', '202501', '202502', '202503', '202504',\n", " '202505', '202506', '202507', '202508', '202509', '202510', '202511',\n", " '202512', '202513', '202514', '202515'],\n", " dtype='object')" ] }, "execution_count": 90, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[37:55]" ] }, { "cell_type": "code", "execution_count": 91, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CANALUFCódigomed_por_canal
0TODOSAL16410.0
1TODOSAL16440.0
2TODOSAL20940.0
3TODOSAL21000.0
4TODOSAL21500.0
...............
7315VDVDC931030.0
7316VDVDC931040.0
7317VDVDC931050.0
7318VDVDC931060.0
7319VDVDC931310.0
\n", "

7320 rows × 4 columns

\n", "
" ], "text/plain": [ " CANAL UF Código med_por_canal\n", "0 TODOS AL 1641 0.0\n", "1 TODOS AL 1644 0.0\n", "2 TODOS AL 2094 0.0\n", "3 TODOS AL 2100 0.0\n", "4 TODOS AL 2150 0.0\n", "... ... ... ... ...\n", "7315 VD VDC 93103 0.0\n", "7316 VD VDC 93104 0.0\n", "7317 VD VDC 93105 0.0\n", "7318 VD VDC 93106 0.0\n", "7319 VD VDC 93131 0.0\n", "\n", "[7320 rows x 4 columns]" ] }, "execution_count": 91, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vendas_todos_historicos = df_final.columns[37:55]\n", "\n", "df_final['MEDIANA DO HISTÓRICO'] = df_final[vendas_todos_historicos].median(axis=1)\n", "\n", "df_final['MEDIA DO HISTÓRICO'] = df_final[vendas_todos_historicos].mean(axis=1)\n", "\n", "medi = df_final.groupby(['CANAL','UF','Código'])['MEDIANA DO HISTÓRICO'].max().reset_index()\n", "medi = medi.rename(columns={'MEDIANA DO HISTÓRICO':'med_por_canal'})\n", "\n", "df_final = pd.merge(left=df_final, right=medi,on=['CANAL','UF','Código'],how='left')\n", "\n", "medi" ] }, { "cell_type": "code", "execution_count": 92, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'202417'" ] }, "execution_count": 92, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[39]" ] }, { "cell_type": "code", "execution_count": 93, "metadata": {}, "outputs": [], "source": [ "mesmo_ciclo_ano_passado = df_final.columns[39]\n", "ciclo_ano_passado = df_final.columns[39]\n", "df_final[ciclo_ano_passado] = df_final[mesmo_ciclo_ano_passado]" ] }, { "cell_type": "code", "execution_count": 94, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "\"df_final['CRESCIMENTO_GERAL'] = 0.2\\ndf_final['CRESCIMENTO'] = 0.2\"" ] }, "execution_count": 94, "metadata": {}, "output_type": "execute_result" } ], "source": [ "'''df_final['CRESCIMENTO_GERAL'] = 0.2\n", "df_final['CRESCIMENTO'] = 0.2'''" ] }, { "cell_type": "code", "execution_count": 95, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(56364, 78)" ] }, "execution_count": 95, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['CRESCIMENTO_FINAL'] = df_final['CRESCIMENTO_GERAL'] + df_final['CRESCIMENTO'] #crescimento do pdv\n", "\n", "df_final['CRESCIMENTO_FINAL'] = np.where(df_final['CRESCIMENTO_GERAL'] + df_final['CRESCIMENTO']>0.8,0.8,df_final['CRESCIMENTO_GERAL'] + df_final['CRESCIMENTO'])\n", "\n", "df_final['CRESCIMENTO_FINAL'] = np.where(df_final['CRESCIMENTO_GERAL'] + df_final['CRESCIMENTO']<0,0,df_final['CRESCIMENTO_GERAL'] + df_final['CRESCIMENTO'])\n", "\n", "df_final['MEDIANA DO HISTÓRICO2'] = np.where(df_final['MEDIANA DO HISTÓRICO']==0,df_final['MEDIA DO HISTÓRICO'] ,df_final['MEDIANA DO HISTÓRICO'])\n", "\n", "# Primeiro cálculo intermediário\n", "df_final['PV GINSENG'] = np.where(df_final['CRESCIMENTO_FINAL'] * df_final[ciclo_ano_passado] + df_final[ciclo_ano_passado] <1,\n", " round(df_final['CRESCIMENTO_FINAL'] * df_final['MEDIANA DO HISTÓRICO2']+ df_final['MEDIANA DO HISTÓRICO2'],0), \n", " round(df_final['CRESCIMENTO_FINAL']*df_final[ciclo_ano_passado]+df_final[ciclo_ano_passado],0))\n", "\n", "\n", "df_final.shape" ] }, { "cell_type": "code", "execution_count": 96, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "12522 732\n", "12817 732\n", "12818 732\n", "12820 732\n", "12823 732\n", " ... \n", "24257 732\n", "24268 732\n", "24269 732\n", "24293 732\n", "23813 732\n", "Name: count, Length: 77, dtype: Int64" ] }, "execution_count": 96, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 97, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Ciclo_xRegiãoCanalCódigoDescrição_xIAFTipo de pedidoFocoUnidade de negócioMarca...PICO DE VENDAS 2024Pico Vendas Ultimos 6 ciclosCRESCIMENTOCRESCIMENTO_GERALMEDIANA DO HISTÓRICOMEDIA DO HISTÓRICOmed_por_canalCRESCIMENTO_FINALMEDIANA DO HISTÓRICO2PV GINSENG
0202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...41.010.0-0.02710.21.06.6111116.00.17291.01.0
1202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...97.046.0-0.04970.25.518.6666676.00.15035.520.0
2202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...27.016.0-0.02740.20.54.5555566.00.17260.51.0
3202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...24.09.0-0.08520.21.04.6666676.00.11481.01.0
4202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...20.06.0-0.02810.21.03.8333336.00.17191.011.0
\n", "

5 rows × 78 columns

\n", "
" ], "text/plain": [ " Ciclo_x Região Canal Código Descrição_x IAF \\\n", "0 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "1 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "2 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "3 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "4 202517 NNE VD 83961 ARBO DES BDY SPR 100ml V6 Não \n", "\n", " Tipo de pedido Foco Unidade de negócio Marca ... PICO DE VENDAS 2024 \\\n", "0 Semanal Não BOT ARBO ... 41.0 \n", "1 Semanal Não BOT ARBO ... 97.0 \n", "2 Semanal Não BOT ARBO ... 27.0 \n", "3 Semanal Não BOT ARBO ... 24.0 \n", "4 Semanal Não BOT ARBO ... 20.0 \n", "\n", " Pico Vendas Ultimos 6 ciclos CRESCIMENTO CRESCIMENTO_GERAL \\\n", "0 10.0 -0.0271 0.2 \n", "1 46.0 -0.0497 0.2 \n", "2 16.0 -0.0274 0.2 \n", "3 9.0 -0.0852 0.2 \n", "4 6.0 -0.0281 0.2 \n", "\n", " MEDIANA DO HISTÓRICO MEDIA DO HISTÓRICO med_por_canal CRESCIMENTO_FINAL \\\n", "0 1.0 6.611111 6.0 0.1729 \n", "1 5.5 18.666667 6.0 0.1503 \n", "2 0.5 4.555556 6.0 0.1726 \n", "3 1.0 4.666667 6.0 0.1148 \n", "4 1.0 3.833333 6.0 0.1719 \n", "\n", " MEDIANA DO HISTÓRICO2 PV GINSENG \n", "0 1.0 1.0 \n", "1 5.5 20.0 \n", "2 0.5 1.0 \n", "3 1.0 1.0 \n", "4 1.0 11.0 \n", "\n", "[5 rows x 78 columns]" ] }, "execution_count": 97, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.head()" ] }, { "cell_type": "code", "execution_count": 98, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.drop(columns=['MEDIANA DO HISTÓRICO2'] )" ] }, { "cell_type": "code", "execution_count": 99, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202416', '202417', '202501', '202502', '202503', '202504', '202505'], dtype='object')" ] }, "execution_count": 99, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[38:45]" ] }, { "cell_type": "code", "execution_count": 100, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202510', '202511', '202512', '202513', '202514', '202515'], dtype='object')" ] }, "execution_count": 100, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[49:55]" ] }, { "cell_type": "code", "execution_count": 101, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.rename(columns={df_final.columns[50]: \"C-4\", df_final.columns[51]: \"C-3\",df_final.columns[52]: \"C-2\",df_final.columns[53]: \"C-1\",df_final.columns[54]: \"Atual\"})" ] }, { "cell_type": "code", "execution_count": 104, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202501', '202502', '202503', '202504', '202505', '202506', '202507',\n", " '202508', '202509'],\n", " dtype='object')" ] }, "execution_count": 104, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[40:49]" ] }, { "cell_type": "code", "execution_count": 105, "metadata": {}, "outputs": [], "source": [ "df_final.drop(columns=df_final.columns[40:49], inplace=True)\n", "df_final.drop(columns=df_final.columns[37:39], inplace=True)" ] }, { "cell_type": "code", "execution_count": 106, "metadata": {}, "outputs": [], "source": [ "df_final['COBERTURA ATUAL'] = df_final['ESTOQUE ATUAL'].astype(float) / df_final['DDV PREVISTO'].astype(float)" ] }, { "cell_type": "code", "execution_count": 107, "metadata": {}, "outputs": [], "source": [ "df_final['COBERTURA ATUAL'] = df_final['COBERTURA ATUAL'].replace([np.inf, -np.inf], 0)\n", "\n", "df_final['COBERTURA ATUAL'] = round(df_final['COBERTURA ATUAL'],0)" ] }, { "cell_type": "code", "execution_count": 108, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "COBERTURA ATUAL\n", "0.0 6975\n", "100.0 1041\n", "50.0 844\n", "25.0 483\n", "200.0 448\n", " ... \n", "590.0 1\n", "736.0 1\n", "835.0 1\n", "623.0 1\n", "1340.0 1\n", "Name: count, Length: 643, dtype: int64" ] }, "execution_count": 108, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['COBERTURA ATUAL'].value_counts()" ] }, { "cell_type": "code", "execution_count": 109, "metadata": {}, "outputs": [], "source": [ "df_final[\"DDV PREVISTO\"] = df_final[\"DDV PREVISTO\"].astype(float)\n", "\n", "df_final[\"DDV PREVISTO\"] = df_final[\"DDV PREVISTO\"].fillna(0)" ] }, { "cell_type": "code", "execution_count": 110, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "12522 732\n", "12817 732\n", "12818 732\n", "12820 732\n", "12823 732\n", " ... \n", "24257 732\n", "24268 732\n", "24269 732\n", "24293 732\n", "23813 732\n", "Name: count, Length: 77, dtype: Int64" ] }, "execution_count": 110, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 111, "metadata": {}, "outputs": [], "source": [ "r'''colunas_chave = [c for c in df_final.columns if c not in [\"DDV PREVISTO\", \"COBERTURA ATUAL\"]]\n", "\n", "\n", "# encontrar índice da linha com maior DDV e depois maior COBERTURA\n", "idx = (\n", "df_final\n", ".sort_values([\"DDV PREVISTO\", \"COBERTURA ATUAL\"], ascending=[False, False])\n", ".groupby(colunas_chave, as_index=False)\n", ".head(1)\n", ")\n", "\n", "\n", "# reordenar colunas como no original\n", "df_final_dedup = idx[df_final.columns.tolist()].reset_index(drop=True)'''\n", "\n", "df_final_dedup = df_final" ] }, { "cell_type": "code", "execution_count": 112, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição_x', 'IAF',\n", " 'Tipo de pedido', 'Foco', 'Unidade de negócio', 'Marca', 'Categoria',\n", " 'Tipo de promoção', 'Catálogo', 'Tipo de produto', 'Ação consumidor',\n", " 'Percentual de desconto consumidor', 'Ação revendedor',\n", " 'Percentual de desconto revendedor', 'MATCH', 'PDV', 'CANAL',\n", " 'DESCRIÇÃO PDV', 'UF', 'ANALISTA', 'SUPERVISOR', 'ANALISTA EUD', 'SKU',\n", " 'curva', 'categoria', 'proj_mar', 'proj_mar+1', 'ESTOQUE ATUAL',\n", " 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DDV PREVISTO', 'preço',\n", " 'SKU_FINAL', '202417', '202510', 'C-4', 'C-3', 'C-2', 'C-1', 'Atual',\n", " 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO', 'dias_ate_inicio',\n", " 'Descrição_y', 'chave sku2', 'chave sku1', 'PRECO DE COMPRA',\n", " 'PRECO DE VENDA', 'UFPRODUTO', 'PDV GINSENG', 'PRODUTO', 'chave',\n", " 'PICO DE VENDAS 2024', 'Pico Vendas Ultimos 6 ciclos', 'CRESCIMENTO',\n", " 'CRESCIMENTO_GERAL', 'MEDIANA DO HISTÓRICO', 'MEDIA DO HISTÓRICO',\n", " 'med_por_canal', 'CRESCIMENTO_FINAL', 'PV GINSENG', 'COBERTURA ATUAL'],\n", " dtype='object')" ] }, "execution_count": 112, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 113, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "('Ação consumidor',\n", " 'Percentual de desconto consumidor',\n", " 'Ação revendedor',\n", " 'Percentual de desconto revendedor',\n", " '202408')" ] }, "execution_count": 113, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Columns to bring up front\n", "priority_cols = [\n", " 'SKU',\t'Marca',\t'INICIO CICLO',\n", " 'FIM CICLO',\t'DURAÇÃO',\t'PRECO DE COMPRA',\t'PRECO DE VENDA',\n", " 'UFPRODUTO',\t'Item Desativado',\t'Data Prevista Regularização',\n", " 'ANALISTA',\t'UF',\t'CANAL',\t'PDV',\t'DESCRIÇÃO PDV',\t'Classe',\n", " 'Descrição',\t'Categoria',\n", "\t'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO',\t'COBERTURA ATUAL',\n", " 'Pedido Pendente',\t'PICO DE VENDAS 2024','Pico Vendas Ultimos 6 ciclos',\n", " 'C-4',\t'C-3',\t'C-2',\t'C-1',\t'Histórico de Vendas do Ciclo Atual',\n", " 'Dias sem venda'\n", "]\n", "\n", "# All remaining columns\n", "other_cols = [col for col in df_final_dedup.columns if col not in priority_cols]\n", "\n", "# Reorder\n", "'Ação consumidor', 'Percentual de desconto consumidor', 'Ação revendedor', 'Percentual de desconto revendedor', '202408'\n" ] }, { "cell_type": "code", "execution_count": 114, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Ciclo_xRegiãoCanalCódigoDescrição_xIAFTipo de pedidoFocoUnidade de negócioMarca...PICO DE VENDAS 2024Pico Vendas Ultimos 6 ciclosCRESCIMENTOCRESCIMENTO_GERALMEDIANA DO HISTÓRICOMEDIA DO HISTÓRICOmed_por_canalCRESCIMENTO_FINALPV GINSENGCOBERTURA ATUAL
39202517NNEVD83961ARBO DES BDY SPR 100ml V6NãoSemanalNãoBOTARBO...15.014.00.04430.20.02.3888896.00.24433.091.0
116202517NNEVD50052ARBO LOC DES HID CPO 75mlNãoSemanalNãoBOTARBO...1.01.00.04430.20.00.1111110.00.24430.00.0
193202517NNEVD57210AU MIGOS PETS BANHO SECO ADULTOS 240mlNãoSemanalNãoBOTAU.MIGOS PETS...0.00.00.04430.20.00.0000001.00.24430.0NaN
270202517NNEVD88301AU MIGOS PETS COL ADULT CHAMEGO 60mlNãoSemanalNãoBOTAU.MIGOS PETS...0.00.00.04430.20.00.0000000.00.24430.0NaN
347202517NNEVD57211AU MIGOS PETS COL ADULTOS 60mlNãoSemanalNãoBOTAU.MIGOS PETS...0.00.00.04430.20.00.0000001.00.24430.0NaN
..................................................................
56018202517NNETodos55975MEN SHAMP/COND 2/1 250mlNãoSemanalNãoBOTBOTICARIO MEN...0.00.00.04430.20.00.0000002.00.24430.0NaN
56095202517NNETodos58733BOTICA 214 EDP SEV/DOR FLOR/FRUT 75mlNãoSemanalNãoBOTBOTICA 214...12.01.00.04430.20.00.9444440.00.24431.0200.0
56172202517NNETodos86504REF CASA 214 AROMAT VAR GARD/IMPER 250mlNãoEspecialNãoBOTCASA 214...0.00.00.04430.20.00.0000000.00.24430.0NaN
56249202517NNETodos86505REF CASA 214 AROMAT VAR VAN/SUBLIM 250mlNãoEspecialNãoBOTCASA 214...0.00.00.04430.20.00.0000000.00.24430.0NaN
56326202517NNETodos88340REF CASA 214 SPR AROMAT VAN/SUBLIM 250mlNãoEspecialNãoBOTCASA 214...0.00.00.04430.20.00.0000000.00.24430.0NaN
\n", "

732 rows × 67 columns

\n", "
" ], "text/plain": [ " Ciclo_x Região Canal Código \\\n", "39 202517 NNE VD 83961 \n", "116 202517 NNE VD 50052 \n", "193 202517 NNE VD 57210 \n", "270 202517 NNE VD 88301 \n", "347 202517 NNE VD 57211 \n", "... ... ... ... ... \n", "56018 202517 NNE Todos 55975 \n", "56095 202517 NNE Todos 58733 \n", "56172 202517 NNE Todos 86504 \n", "56249 202517 NNE Todos 86505 \n", "56326 202517 NNE Todos 88340 \n", "\n", " Descrição_x IAF Tipo de pedido Foco \\\n", "39 ARBO DES BDY SPR 100ml V6 Não Semanal Não \n", "116 ARBO LOC DES HID CPO 75ml Não Semanal Não \n", "193 AU MIGOS PETS BANHO SECO ADULTOS 240ml Não Semanal Não \n", "270 AU MIGOS PETS COL ADULT CHAMEGO 60ml Não Semanal Não \n", "347 AU MIGOS PETS COL ADULTOS 60ml Não Semanal Não \n", "... ... ... ... ... \n", "56018 MEN SHAMP/COND 2/1 250ml Não Semanal Não \n", "56095 BOTICA 214 EDP SEV/DOR FLOR/FRUT 75ml Não Semanal Não \n", "56172 REF CASA 214 AROMAT VAR GARD/IMPER 250ml Não Especial Não \n", "56249 REF CASA 214 AROMAT VAR VAN/SUBLIM 250ml Não Especial Não \n", "56326 REF CASA 214 SPR AROMAT VAN/SUBLIM 250ml Não Especial Não \n", "\n", " Unidade de negócio Marca ... PICO DE VENDAS 2024 \\\n", "39 BOT ARBO ... 15.0 \n", "116 BOT ARBO ... 1.0 \n", "193 BOT AU.MIGOS PETS ... 0.0 \n", "270 BOT AU.MIGOS PETS ... 0.0 \n", "347 BOT AU.MIGOS PETS ... 0.0 \n", "... ... ... ... ... \n", "56018 BOT BOTICARIO MEN ... 0.0 \n", "56095 BOT BOTICA 214 ... 12.0 \n", "56172 BOT CASA 214 ... 0.0 \n", "56249 BOT CASA 214 ... 0.0 \n", "56326 BOT CASA 214 ... 0.0 \n", "\n", " Pico Vendas Ultimos 6 ciclos CRESCIMENTO CRESCIMENTO_GERAL \\\n", "39 14.0 0.0443 0.2 \n", "116 1.0 0.0443 0.2 \n", "193 0.0 0.0443 0.2 \n", "270 0.0 0.0443 0.2 \n", "347 0.0 0.0443 0.2 \n", "... ... ... ... \n", "56018 0.0 0.0443 0.2 \n", "56095 1.0 0.0443 0.2 \n", "56172 0.0 0.0443 0.2 \n", "56249 0.0 0.0443 0.2 \n", "56326 0.0 0.0443 0.2 \n", "\n", " MEDIANA DO HISTÓRICO MEDIA DO HISTÓRICO med_por_canal CRESCIMENTO_FINAL \\\n", "39 0.0 2.388889 6.0 0.2443 \n", "116 0.0 0.111111 0.0 0.2443 \n", "193 0.0 0.000000 1.0 0.2443 \n", "270 0.0 0.000000 0.0 0.2443 \n", "347 0.0 0.000000 1.0 0.2443 \n", "... ... ... ... ... \n", "56018 0.0 0.000000 2.0 0.2443 \n", "56095 0.0 0.944444 0.0 0.2443 \n", "56172 0.0 0.000000 0.0 0.2443 \n", "56249 0.0 0.000000 0.0 0.2443 \n", "56326 0.0 0.000000 0.0 0.2443 \n", "\n", " PV GINSENG COBERTURA ATUAL \n", "39 3.0 91.0 \n", "116 0.0 0.0 \n", "193 0.0 NaN \n", "270 0.0 NaN \n", "347 0.0 NaN \n", "... ... ... \n", "56018 0.0 NaN \n", "56095 1.0 200.0 \n", "56172 0.0 NaN \n", "56249 0.0 NaN \n", "56326 0.0 NaN \n", "\n", "[732 rows x 67 columns]" ] }, "execution_count": 114, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup[df_final_dedup['PDV'] == 21007]" ] }, { "cell_type": "code", "execution_count": 115, "metadata": {}, "outputs": [], "source": [ "df_final_dedup.drop(columns=['dias_ate_inicio','SKU_FINAL',\n", " 'CRESCIMENTO'],inplace=True)" ] }, { "cell_type": "code", "execution_count": 116, "metadata": {}, "outputs": [], "source": [ "df_final_dedup['INICIO CICLO'] = pd.to_datetime(df_final_dedup['INICIO CICLO'], dayfirst=True).dt.strftime('%d/%m/%Y')\n", "\n", "df_final_dedup['FIM CICLO'] = pd.to_datetime(df_final_dedup['FIM CICLO'], dayfirst=True).dt.strftime('%d/%m/%Y')" ] }, { "cell_type": "code", "execution_count": 117, "metadata": {}, "outputs": [], "source": [ "df_final_dedup['Percentual de desconto revendedor'] = np.where((df_final_dedup['Percentual de desconto revendedor'].isna()) & (~df_final_dedup['Percentual de desconto consumidor'].isna()),df_final_dedup['Percentual de desconto consumidor'],df_final_dedup['Percentual de desconto revendedor'])" ] }, { "cell_type": "code", "execution_count": 118, "metadata": {}, "outputs": [], "source": [ "df_pdv_origi['PDV'] = df_pdv_origi['PDV'].astype('Int64')\n", "df_final_dedup['PDV'] = df_final_dedup['PDV'].astype('Int64')\n", "\n", "\n", "df_final_dedup = pd.merge(left=df_final_dedup,right=df_pdv_origi[['PDV','CANAL','UF']],how='left',on='PDV')" ] }, { "cell_type": "code", "execution_count": 119, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "np.int64(0)" ] }, "execution_count": 119, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup['Código'].isna().sum()" ] }, { "cell_type": "code", "execution_count": 120, "metadata": {}, "outputs": [], "source": [ "df_irece_agrupado = df_irece.groupby(['PDV','Código','Ciclo'])['Quantidade'].sum().reset_index()" ] }, { "cell_type": "code", "execution_count": 121, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = df_final_dedup.rename(columns={'CANAL_x':'CANAL','UF_X':'UF','Marca':'LINHA'})\n" ] }, { "cell_type": "code", "execution_count": 122, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "12522 732\n", "12817 732\n", "12818 732\n", "12820 732\n", "12823 732\n", " ... \n", "24257 732\n", "24268 732\n", "24269 732\n", "24293 732\n", "23813 732\n", "Name: count, Length: 77, dtype: Int64" ] }, "execution_count": 122, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 123, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = df_final_dedup.rename(columns={'UF_x':'UF','DESCRICAO_y':'DESCRICAO'})" ] }, { "cell_type": "code", "execution_count": 124, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = df_final_dedup.drop(columns={'CANAL_y', 'UF_y'})\n" ] }, { "cell_type": "code", "execution_count": 125, "metadata": {}, "outputs": [], "source": [ "df_final_dedup['Estoque Total'] = df_final_dedup['ESTOQUE ATUAL'].astype(float) + df_final_dedup['ESTOQUE EM TRANSITO'].astype(float) + df_final_dedup['PEDIDO PENDENTE'].astype(float)" ] }, { "cell_type": "code", "execution_count": 126, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'202417'" ] }, "execution_count": 126, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ciclo_ano_passado" ] }, { "cell_type": "code", "execution_count": 127, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição_x', 'IAF',\n", " 'Tipo de pedido', 'Foco', 'Unidade de negócio', 'LINHA', 'Categoria',\n", " 'Tipo de promoção', 'Catálogo', 'Tipo de produto', 'Ação consumidor',\n", " 'Percentual de desconto consumidor', 'Ação revendedor',\n", " 'Percentual de desconto revendedor', 'MATCH', 'PDV', 'CANAL',\n", " 'DESCRIÇÃO PDV', 'UF', 'ANALISTA', 'SUPERVISOR', 'ANALISTA EUD', 'SKU',\n", " 'curva', 'categoria', 'proj_mar', 'proj_mar+1', 'ESTOQUE ATUAL',\n", " 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DDV PREVISTO', 'preço',\n", " '202417', '202510', 'C-4', 'C-3', 'C-2', 'C-1', 'Atual', 'INICIO CICLO',\n", " 'FIM CICLO', 'DURAÇÃO', 'Descrição_y', 'chave sku2', 'chave sku1',\n", " 'PRECO DE COMPRA', 'PRECO DE VENDA', 'UFPRODUTO', 'PDV GINSENG',\n", " 'PRODUTO', 'chave', 'PICO DE VENDAS 2024',\n", " 'Pico Vendas Ultimos 6 ciclos', 'CRESCIMENTO_GERAL',\n", " 'MEDIANA DO HISTÓRICO', 'MEDIA DO HISTÓRICO', 'med_por_canal',\n", " 'CRESCIMENTO_FINAL', 'PV GINSENG', 'COBERTURA ATUAL', 'Estoque Total'],\n", " dtype='object')" ] }, "execution_count": 127, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup.columns" ] }, { "cell_type": "code", "execution_count": 128, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = df_final_dedup.drop(columns='Descrição_y')\n", "\n", "df_final_dedup = df_final_dedup.rename(columns={'Descrição_x':'Descrição'})" ] }, { "cell_type": "code", "execution_count": 129, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = df_final_dedup[['DURAÇÃO','CANAL','ANALISTA','SUPERVISOR','UF','chave','PDV','DESCRIÇÃO PDV','Código','Descrição','LINHA','Categoria','curva','Percentual de desconto consumidor','Ação consumidor',\n", " 'Percentual de desconto revendedor','Ação revendedor','C-4', 'C-3', 'C-2', 'C-1','Atual',ciclo_ano_passado ,'ESTOQUE ATUAL','ESTOQUE EM TRANSITO','PEDIDO PENDENTE','Estoque Total',\n", " 'DDV PREVISTO','COBERTURA ATUAL','proj_mar', 'proj_mar+1','MEDIA DO HISTÓRICO','PICO DE VENDAS 2024','Pico Vendas Ultimos 6 ciclos','PRECO DE COMPRA',\n", " 'PRECO DE VENDA','PV GINSENG']]" ] }, { "cell_type": "code", "execution_count": 130, "metadata": {}, "outputs": [], "source": [ "df_final_dedup['Percentual de desconto revendedor'] = np.where(df_final_dedup['Percentual de desconto revendedor'] == '0,00', df_final_dedup['Percentual de desconto consumidor'],df_final_dedup['Percentual de desconto revendedor'])" ] }, { "cell_type": "code", "execution_count": 131, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'202417'" ] }, "execution_count": 131, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ciclo_ano_passado" ] }, { "cell_type": "code", "execution_count": 132, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = df_final_dedup.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 133, "metadata": {}, "outputs": [], "source": [ "df_final_dedup['Valor Sugestão'] = \"\"\n", "\n", "df_final_dedup['Sugestão Abastecimento'] = \"\"" ] }, { "cell_type": "code", "execution_count": 134, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "DDV PREVISTO float64\n", "COBERTURA ATUAL float64\n", "dtype: object" ] }, "execution_count": 134, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup[['DDV PREVISTO',\n", " 'COBERTURA ATUAL',]].dtypes" ] }, { "cell_type": "code", "execution_count": 135, "metadata": {}, "outputs": [], "source": [ "df_final_dedup[ 'DDV PREVISTO'] = df_final_dedup['DDV PREVISTO'].fillna(0)\n", "\n", "df_final_dedup = df_final_dedup.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 136, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DURAÇÃOCANALANALISTASUPERVISORUFchavePDVDESCRIÇÃO PDVCódigoDescrição...proj_marproj_mar+1MEDIA DO HISTÓRICOPICO DE VENDAS 2024Pico Vendas Ultimos 6 ciclosPRECO DE COMPRAPRECO DE VENDAPV GINSENGValor SugestãoSugestão Abastecimento
660725VDVAJACQUELINEVDC237118438723711ER VITORIA DA CONQUISTA84387MALBEC DES COL V6 100ml...2157.0381.0716.1111112734.01559.00.00.0502.0
\n", "

1 rows × 39 columns

\n", "
" ], "text/plain": [ " DURAÇÃO CANAL ANALISTA SUPERVISOR UF chave PDV \\\n", "6607 25 VD VA JACQUELINE VDC 2371184387 23711 \n", "\n", " DESCRIÇÃO PDV Código Descrição ... proj_mar \\\n", "6607 ER VITORIA DA CONQUISTA 84387 MALBEC DES COL V6 100ml ... 2157.0 \n", "\n", " proj_mar+1 MEDIA DO HISTÓRICO PICO DE VENDAS 2024 \\\n", "6607 381.0 716.111111 2734.0 \n", "\n", " Pico Vendas Ultimos 6 ciclos PRECO DE COMPRA PRECO DE VENDA PV GINSENG \\\n", "6607 1559.0 0.0 0.0 502.0 \n", "\n", " Valor Sugestão Sugestão Abastecimento \n", "6607 \n", "\n", "[1 rows x 39 columns]" ] }, "execution_count": 136, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup[df_final_dedup['chave'] == '2371184387'].head()" ] }, { "cell_type": "code", "execution_count": 137, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "6607 61.9\n", "Name: DDV PREVISTO, dtype: float64" ] }, "execution_count": 137, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup[df_final_dedup['chave'] == '2371184387']['DDV PREVISTO'].head()\n" ] }, { "cell_type": "code", "execution_count": 138, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DURAÇÃOCANALANALISTASUPERVISORUFchavePDVDESCRIÇÃO PDVCódigoDescrição...proj_marproj_mar+1MEDIA DO HISTÓRICOPICO DE VENDAS 2024Pico Vendas Ultimos 6 ciclosPRECO DE COMPRAPRECO DE VENDAPV GINSENGValor SugestãoSugestão Abastecimento
025TODOSLUANEfigênia HerculanoAL125228396112522MACEIO SHOP EXP83961ARBO DES BDY SPR 100ml V6...4.03.06.61111141.010.015.1547.91.0
125TODOSJEFFERSONMaxwell VieiraAL128178396112817SHOPPING PATIO83961ARBO DES BDY SPR 100ml V6...20.09.018.66666797.046.015.1547.920.0
225TODOSVINICIUSMaxwell VieiraAL128188396112818GB SERRARIA83961ARBO DES BDY SPR 100ml V6...5.02.04.55555627.016.015.1547.91.0
325TODOSADRIELYMaxwell VieiraAL128208396112820ATACADÃO83961ARBO DES BDY SPR 100ml V6...3.01.04.66666724.09.015.1547.91.0
425TODOSADRIELYEfigênia HerculanoAL128238396112823PONTA VERDE83961ARBO DES BDY SPR 100ml V6...2.01.03.83333320.06.015.1547.911.0
\n", "

5 rows × 39 columns

\n", "
" ], "text/plain": [ " DURAÇÃO CANAL ANALISTA SUPERVISOR UF chave PDV \\\n", "0 25 TODOS LUAN Efigênia Herculano AL 1252283961 12522 \n", "1 25 TODOS JEFFERSON Maxwell Vieira AL 1281783961 12817 \n", "2 25 TODOS VINICIUS Maxwell Vieira AL 1281883961 12818 \n", "3 25 TODOS ADRIELY Maxwell Vieira AL 1282083961 12820 \n", "4 25 TODOS ADRIELY Efigênia Herculano AL 1282383961 12823 \n", "\n", " DESCRIÇÃO PDV Código Descrição ... proj_mar \\\n", "0 MACEIO SHOP EXP 83961 ARBO DES BDY SPR 100ml V6 ... 4.0 \n", "1 SHOPPING PATIO 83961 ARBO DES BDY SPR 100ml V6 ... 20.0 \n", "2 GB SERRARIA 83961 ARBO DES BDY SPR 100ml V6 ... 5.0 \n", "3 ATACADÃO 83961 ARBO DES BDY SPR 100ml V6 ... 3.0 \n", "4 PONTA VERDE 83961 ARBO DES BDY SPR 100ml V6 ... 2.0 \n", "\n", " proj_mar+1 MEDIA DO HISTÓRICO PICO DE VENDAS 2024 \\\n", "0 3.0 6.611111 41.0 \n", "1 9.0 18.666667 97.0 \n", "2 2.0 4.555556 27.0 \n", "3 1.0 4.666667 24.0 \n", "4 1.0 3.833333 20.0 \n", "\n", " Pico Vendas Ultimos 6 ciclos PRECO DE COMPRA PRECO DE VENDA PV GINSENG \\\n", "0 10.0 15.15 47.9 1.0 \n", "1 46.0 15.15 47.9 20.0 \n", "2 16.0 15.15 47.9 1.0 \n", "3 9.0 15.15 47.9 1.0 \n", "4 6.0 15.15 47.9 11.0 \n", "\n", " Valor Sugestão Sugestão Abastecimento \n", "0 \n", "1 \n", "2 \n", "3 \n", "4 \n", "\n", "[5 rows x 39 columns]" ] }, "execution_count": 138, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup.head()" ] }, { "cell_type": "code", "execution_count": 139, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['DURAÇÃO', 'CANAL', 'ANALISTA', 'SUPERVISOR', 'UF', 'chave', 'PDV',\n", " 'DESCRIÇÃO PDV', 'Código', 'Descrição', 'LINHA', 'Categoria', 'curva',\n", " 'Percentual de desconto consumidor', 'Ação consumidor',\n", " 'Percentual de desconto revendedor', 'Ação revendedor', 'C-4', 'C-3',\n", " 'C-2', 'C-1', 'Atual', '202417', 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO',\n", " 'PEDIDO PENDENTE', 'Estoque Total', 'DDV PREVISTO', 'COBERTURA ATUAL',\n", " 'proj_mar', 'proj_mar+1', 'MEDIA DO HISTÓRICO', 'PICO DE VENDAS 2024',\n", " 'Pico Vendas Ultimos 6 ciclos', 'PRECO DE COMPRA', 'PRECO DE VENDA',\n", " 'PV GINSENG', 'Valor Sugestão', 'Sugestão Abastecimento',\n", " 'CANAL_CERTO'],\n", " dtype='object')" ] }, "execution_count": 139, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pdv_origi['PDV'] = df_pdv_origi['PDV'].astype('Int64')\n", "\n", "\n", "\n", "df_pdv_origi = df_pdv_origi.rename(columns={'CANAL':'CANAL_CERTO'})\n", "\n", "df_final_dedup= pd.merge(df_final_dedup,df_pdv_origi[['PDV','CANAL_CERTO']],on='PDV',how='inner')\n", "\n", "df_final_dedup['CANAL']= df_final_dedup['CANAL_CERTO']\n", "\n", "\n", "df_final_dedup.columns" ] }, { "cell_type": "code", "execution_count": 140, "metadata": {}, "outputs": [], "source": [ "# Export to Excel\n", "output_file = f'C:\\\\Users\\\\joao.herculano\\\\Documents\\\\promoção_BOTI_{ciclo_mais2}.{hoje}.xlsx'\n", "with pd.ExcelWriter(output_file, engine='openpyxl') as writer:\n", " df_final_dedup.to_excel(writer, index=False)\n", "\n", "# Apply styles\n", "wb = load_workbook(output_file)\n", "ws = wb['Sheet1']\n", "\n", "# Style header\n", "header_fill = PatternFill(start_color='ADD8E6', end_color='ADD8E6', fill_type='solid') # Light Blue\n", "header_font = Font(color='FFFFFF', bold=True) # White & Bold\n", "\n", "for cell in ws[1]:\n", " cell.fill = header_fill\n", " cell.font = header_font\n", "\n", "# Style rows: gray/white alternating\n", "gray_fill = PatternFill(start_color='DDDDDD', end_color='DDDDDD', fill_type='solid') # Light gray\n", "\n", "for i, row in enumerate(ws.iter_rows(min_row=2, max_row=ws.max_row), start=2):\n", " if i % 2 == 0:\n", " for cell in row:\n", " cell.fill = gray_fill\n", "\n", "# Save styled workbook\n", "wb.save(output_file)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "coisas q saem\n", "\n", "Lançamento\n", "Subcategoria\n", "Projeção Próximo Ciclo \n", "Promoção Próximo Ciclo\n", "Compra inteligente semanal/Sugestão de compra\n", "Compra inteligente Próximo Ciclo\n", "Planograma\n", "Carteira Bloqueada Para Novos Pedidos\n", "Quantidade por caixa\n", "Preço Sell In\n", "Quantidade\n", "Item analisado\n", "Tipo Preço\n", "\n", ">>>>>>>>>>>>>NAO ESTÁ PEGANDO O MERGE COM O DF_ESTOQUE\n", "\n", "\n", "CRIAR PROJEÇÃO DE VENDA DO CICLO ATUAL\n", "Compra inteligente Próximo Ciclo + 1 >>>>>>> RENAME PRA NOROMAL" ] } ], "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": 2 }