{ "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\\QDB\\BW\\tabela de pedido\\Pedidos Semanais Especiais - QDB - 202513.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": [ { "data": { "text/plain": [ "Código\n", "47196 1\n", "49901 1\n", "49909 1\n", "53958 1\n", "51021 1\n", " ..\n", "79483 1\n", "51890 1\n", "56323 1\n", "56324 1\n", "56321 1\n", "Name: count, Length: 108, dtype: int64" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_tabela['Código'].value_counts()" ] }, { "cell_type": "code", "execution_count": 6, "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", "
CicloRegiãoCanalCódigoDescriçãoIAFTipo de pedidoFocoUnidade de negócioMarca...CatálogoTipo de produtoAção consumidorPercentual de desconto consumidorAção revendedorPercentual de desconto revendedorSortimento PSortimento MSortimento GMATCH
0202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...SimREGULAR[QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ...50,71VD - FAVORITOS PARA INÍCIOS BIG BANG 360 - C1335,00SortidoSortidoSortido1
1202513NNETodos49901QDB PINCEL SOMBRANãoSemanalNãoQDBQUEM DISSE BERENICE...SimREGULAR[QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ...30,30 | 51,28[QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ...0,00SortidoSortidoSortido1
2202513NNETodos49909QDB PINCEL SUPERACAB PELE PERFEITANãoSemanalNãoQDBQUEM DISSE BERENICE...SimREGULAR[QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ...30,70[QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ...0,00SortidoSortidoSortido1
3202513NNETodos53958QDB MALETA MAQUIAGEM G PUNãoSemanalNãoQDBQUEM DISSE BERENICE...NãoREGULAR[QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS EXAUSTÃ...25,00 | 30,01[QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS EXAUSTÃ...0,00SortidoSortidoSortido1
4202513NNETodos51021QDB BLUSH LIQ GUARANA 6,8mlNãoEspecialSimQDBQUEM DISSE BERENICE...SimEDICAO LIMITADA[QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS EXAUSTÃ...51,22[QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS EXAUSTÃ...0,00SortidoSortidoSortido1
\n", "

5 rows × 24 columns

\n", "
" ], "text/plain": [ " Ciclo Região Canal Código Descrição IAF \\\n", "0 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "1 202513 NNE Todos 49901 QDB PINCEL SOMBRA Não \n", "2 202513 NNE Todos 49909 QDB PINCEL SUPERACAB PELE PERFEITA Não \n", "3 202513 NNE Todos 53958 QDB MALETA MAQUIAGEM G PU Não \n", "4 202513 NNE Todos 51021 QDB BLUSH LIQ GUARANA 6,8ml Não \n", "\n", " Tipo de pedido Foco Unidade de negócio Marca ... Catálogo \\\n", "0 Especial Sim QDB QUEM DISSE BERENICE ... Sim \n", "1 Semanal Não QDB QUEM DISSE BERENICE ... Sim \n", "2 Semanal Não QDB QUEM DISSE BERENICE ... Sim \n", "3 Semanal Não QDB QUEM DISSE BERENICE ... Não \n", "4 Especial Sim QDB QUEM DISSE BERENICE ... Sim \n", "\n", " Tipo de produto Ação consumidor \\\n", "0 REGULAR [QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ... \n", "1 REGULAR [QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ... \n", "2 REGULAR [QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ... \n", "3 REGULAR [QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS EXAUSTÃ... \n", "4 EDICAO LIMITADA [QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS EXAUSTÃ... \n", "\n", " Percentual de desconto consumidor \\\n", "0 50,71 \n", "1 30,30 | 51,28 \n", "2 30,70 \n", "3 25,00 | 30,01 \n", "4 51,22 \n", "\n", " Ação revendedor \\\n", "0 VD - FAVORITOS PARA INÍCIOS BIG BANG 360 - C13 \n", "1 [QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ... \n", "2 [QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS ESTRATÉ... \n", "3 [QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS EXAUSTÃ... \n", "4 [QDB - LOJA/ECOMM] BEAUTY WEEK - ISCAS EXAUSTÃ... \n", "\n", " Percentual de desconto revendedor Sortimento P Sortimento M Sortimento G \\\n", "0 35,00 Sortido Sortido Sortido \n", "1 0,00 Sortido Sortido Sortido \n", "2 0,00 Sortido Sortido Sortido \n", "3 0,00 Sortido Sortido Sortido \n", "4 0,00 Sortido Sortido Sortido \n", "\n", " MATCH \n", "0 1 \n", "1 1 \n", "2 1 \n", "3 1 \n", "4 1 \n", "\n", "[5 rows x 24 columns]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_tabela.head()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_43676\\3661949602.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 = 'QDB'\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": 8, "metadata": {}, "outputs": [], "source": [ "dfi['SKU2'] = dfi['SKU2'].fillna(\"-\")" ] }, { "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", "
PDVSKUSKU2SKU_FINALDESCRICAOVENDAS_CICLOCiclo
0354647960-47960BOTI SUN PROT CPO SPR ACQ/FL FPS50 180ml5.0C202413
12099549680-49680LILY DES COL LEAU DE LILY SOLEIL 75ml356.0C202413
22425319734-19734THATY DES COL BOTICOLL 100ML5.0C202508
42099849218-49218MAKE B ESTJ PO COMP HYALURONIC3.0C202505
71466851129-51129CBEM DES ANTIT AER NUVEM V3 75g5.0C202508
\n", "
" ], "text/plain": [ " PDV SKU SKU2 SKU_FINAL DESCRICAO \\\n", "0 3546 47960 - 47960 BOTI SUN PROT CPO SPR ACQ/FL FPS50 180ml \n", "1 20995 49680 - 49680 LILY DES COL LEAU DE LILY SOLEIL 75ml \n", "2 24253 19734 - 19734 THATY DES COL BOTICOLL 100ML \n", "4 20998 49218 - 49218 MAKE B ESTJ PO COMP HYALURONIC \n", "7 14668 51129 - 51129 CBEM DES ANTIT AER NUVEM V3 75g \n", "\n", " VENDAS_CICLO Ciclo \n", "0 5.0 C202413 \n", "1 356.0 C202413 \n", "2 5.0 C202508 \n", "4 3.0 C202505 \n", "7 5.0 C202508 " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dfi = dfi[dfi['SKU2'] == \"-\"]\n", "\n", "dfi.head()" ] }, { "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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_FINALDESCRICAOC202411C202412C202413C202414C202501C202502C202503C202504C202505C202506C202507C202508C202509C202510C202511
0125221004FLORATTA DES COL MY BLUE 75ml1.05.056.06.01.011.01.05.09.05.08.01.07.03.00.0
1125221080INTENSE GLOS LAB BUBBAL TUT/FRUT 5ml0.04.010.02.02.01.02.03.02.00.010.014.00.00.00.0
2125221296PMPCK THE BLEND DES ANTIT AER 2x75g0.06.02.00.00.00.00.00.00.00.00.00.00.00.02.0
3125221302PMPCK LILY DES ANTIT AER 2x75g0.08.06.00.00.00.00.00.00.00.04.03.06.01.05.0
4125221314PMPCK ZAAD DES ANTIT AER 2x75g0.04.01.00.00.00.00.00.00.00.00.00.01.01.01.0
\n", "
" ], "text/plain": [ " PDV SKU_FINAL DESCRICAO C202411 C202412 \\\n", "0 12522 1004 FLORATTA DES COL MY BLUE 75ml 1.0 5.0 \n", "1 12522 1080 INTENSE GLOS LAB BUBBAL TUT/FRUT 5ml 0.0 4.0 \n", "2 12522 1296 PMPCK THE BLEND DES ANTIT AER 2x75g 0.0 6.0 \n", "3 12522 1302 PMPCK LILY DES ANTIT AER 2x75g 0.0 8.0 \n", "4 12522 1314 PMPCK ZAAD DES ANTIT AER 2x75g 0.0 4.0 \n", "\n", " C202413 C202414 C202501 C202502 C202503 C202504 C202505 C202506 \\\n", "0 56.0 6.0 1.0 11.0 1.0 5.0 9.0 5.0 \n", "1 10.0 2.0 2.0 1.0 2.0 3.0 2.0 0.0 \n", "2 2.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "3 6.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "4 1.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "\n", " C202507 C202508 C202509 C202510 C202511 \n", "0 8.0 1.0 7.0 3.0 0.0 \n", "1 10.0 14.0 0.0 0.0 0.0 \n", "2 0.0 0.0 0.0 0.0 2.0 \n", "3 4.0 3.0 6.0 1.0 5.0 \n", "4 0.0 0.0 1.0 1.0 1.0 " ] }, "execution_count": 10, "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": 11, "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", "
PDVSKU_FINALDESCRICAOC202411C202412C202413C202414C202501C202502C202503C202504C202505C202506C202507C202508C202509C202510C202511
75335209981004FLORATTA DES COL MY BLUE 75ml5.037.0479.0131.016.0252.096.039.0170.0142.0101.017.031.020.010.0
75336209981005DEM FLORATTA DES COL MY BLUE 4ml0.00.00.01.00.00.00.02.02.00.01.00.02.03.01.0
75337209981062NIINA SECRETS DES COL 100ml0.053.05.09.03.04.019.03.05.014.05.038.00.03.09.0
75338209981073NIINA SECRETS LOC DES HID CPO 200ml0.02.01.08.05.01.013.00.01.011.03.06.00.01.00.0
75339209981080INTENSE GLOS LAB BUBBAL TUT/FRUT 5ml2.07.058.014.019.02.07.023.045.01.02.04.00.00.00.0
\n", "
" ], "text/plain": [ " PDV SKU_FINAL DESCRICAO C202411 \\\n", "75335 20998 1004 FLORATTA DES COL MY BLUE 75ml 5.0 \n", "75336 20998 1005 DEM FLORATTA DES COL MY BLUE 4ml 0.0 \n", "75337 20998 1062 NIINA SECRETS DES COL 100ml 0.0 \n", "75338 20998 1073 NIINA SECRETS LOC DES HID CPO 200ml 0.0 \n", "75339 20998 1080 INTENSE GLOS LAB BUBBAL TUT/FRUT 5ml 2.0 \n", "\n", " C202412 C202413 C202414 C202501 C202502 C202503 C202504 C202505 \\\n", "75335 37.0 479.0 131.0 16.0 252.0 96.0 39.0 170.0 \n", "75336 0.0 0.0 1.0 0.0 0.0 0.0 2.0 2.0 \n", "75337 53.0 5.0 9.0 3.0 4.0 19.0 3.0 5.0 \n", "75338 2.0 1.0 8.0 5.0 1.0 13.0 0.0 1.0 \n", "75339 7.0 58.0 14.0 19.0 2.0 7.0 23.0 45.0 \n", "\n", " C202506 C202507 C202508 C202509 C202510 C202511 \n", "75335 142.0 101.0 17.0 31.0 20.0 10.0 \n", "75336 0.0 1.0 0.0 2.0 3.0 1.0 \n", "75337 14.0 5.0 38.0 0.0 3.0 9.0 \n", "75338 11.0 3.0 6.0 0.0 1.0 0.0 \n", "75339 1.0 2.0 4.0 0.0 0.0 0.0 " ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivotadowawa[ (df_pivotadowawa['PDV'] == '20998')].head()" ] }, { "cell_type": "code", "execution_count": 12, "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)|(df_pdv['CANAL']==\"VD\")|(df_pdv['CANAL']==\"HIB\")|(df_pdv['CANAL']==\"CD\")]\n", "\n" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20968 1\n", "20969 1\n", "20970 1\n", "20986 1\n", "20988 1\n", "20989 1\n", "20991 1\n", "20992 1\n", "20993 1\n", "20994 1\n", "20995 1\n", "20996 1\n", "20997 1\n", "20998 1\n", "20999 1\n", "21000 1\n", "21001 1\n", "21278 1\n", "21375 1\n", "21383 1\n", "21495 1\n", "22541 1\n", "23703 1\n", "23704 1\n", "23708 1\n", "23711 1\n", "23712 1\n", "24255 1\n", "24257 1\n", "24269 1\n", "24293 1\n", "910173 1\n", "910291 1\n", "23813 1\n", "Name: count, dtype: int64" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pdv['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "df_pdv = df_pdv.drop(columns=['PDV DESC'])" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_43676\\2995648402.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 = 'QDB'\n", "'''\n", "df_draft_ = pd.read_sql(query_est_draft, conn)\n", "conn.close()\n", "\n", "#FALTA ATUALIZAR QUERY" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [], "source": [ "df_draft = df_draft_" ] }, { "cell_type": "code", "execution_count": 17, "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", "
PDVSKUDESCRICAOcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTEDDV PREVISTOpreço
02099858191QDB BAT EF MAT BERE 430 4,0gEMAQUIAGEM5.00.036000,747.44
12099887476QDB BAT LIQ CR DOS SONHOS MARRONZ V2 4mlBMAQUIAGEM1.00.03000,0711.92
22099887476QDB BAT LIQ CREM DOS SONHOS MARRONZ 4mlBMAQUIAGEM1.00.0300None11.92
32099856945QDB BAT LIQ INSTAMATTE AVELALI 4mlBMAQUIAGEM1.00.01100,0511.92
42099856855QDB BAT LIQ INSTAMATTE BORDOLI 4mlBMAQUIAGEM0.00.0100011.92
\n", "
" ], "text/plain": [ " PDV SKU DESCRICAO curva categoria \\\n", "0 20998 58191 QDB BAT EF MAT BERE 430 4,0g E MAQUIAGEM \n", "1 20998 87476 QDB BAT LIQ CR DOS SONHOS MARRONZ V2 4ml B MAQUIAGEM \n", "2 20998 87476 QDB BAT LIQ CREM DOS SONHOS MARRONZ 4ml B MAQUIAGEM \n", "3 20998 56945 QDB BAT LIQ INSTAMATTE AVELALI 4ml B MAQUIAGEM \n", "4 20998 56855 QDB BAT LIQ INSTAMATTE BORDOLI 4ml B MAQUIAGEM \n", "\n", " proj_mar proj_mar+1 ESTOQUE ATUAL ESTOQUE EM TRANSITO PEDIDO PENDENTE \\\n", "0 5.0 0.0 36 0 0 \n", "1 1.0 0.0 3 0 0 \n", "2 1.0 0.0 3 0 0 \n", "3 1.0 0.0 1 1 0 \n", "4 0.0 0.0 1 0 0 \n", "\n", " DDV PREVISTO preço \n", "0 0,74 7.44 \n", "1 0,07 11.92 \n", "2 None 11.92 \n", "3 0,05 11.92 \n", "4 0 11.92 " ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.head()" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(39857, 12)" ] }, "execution_count": 18, "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": 19, "metadata": {}, "outputs": [ { "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 object\n", "preço float64\n", "dtype: object" ] }, "execution_count": 19, "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": 20, "metadata": {}, "outputs": [], "source": [ "df_draft['DDV PREVISTO'] = df_draft['DDV PREVISTO'].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": 21, "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", "
PDVSKUDESCRICAOcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTEDDV PREVISTOpreço
398522096859167CJ SCH QDB BAS TINT 290F/292O/300N 3x2mlNoneNoneNaNNaN0.00.00.00.0NaN
398532370442973QDB BOLSA TRANSVERSAL PVC C/ANoneNoneNaNNaN0.00.00.00.0NaN
3985491029153283QDB BATOM VOLUMAO MARSALE 3,4G RPCKNoneNoneNaNNaN0.00.00.00.0NaN
398552370488602QDB BAT HID COBRETE RPCK V2 3,8gNoneNoneNaNNaN0.00.00.00.0NaN
398562096970795QDB LAPIS OLHOS BERINJELITO 1,2gNoneNoneNaNNaN0.00.00.00.0NaN
\n", "
" ], "text/plain": [ " PDV SKU DESCRICAO curva \\\n", "39852 20968 59167 CJ SCH QDB BAS TINT 290F/292O/300N 3x2ml None \n", "39853 23704 42973 QDB BOLSA TRANSVERSAL PVC C/A None \n", "39854 910291 53283 QDB BATOM VOLUMAO MARSALE 3,4G RPCK None \n", "39855 23704 88602 QDB BAT HID COBRETE RPCK V2 3,8g None \n", "39856 20969 70795 QDB LAPIS OLHOS BERINJELITO 1,2g None \n", "\n", " categoria proj_mar proj_mar+1 ESTOQUE ATUAL ESTOQUE EM TRANSITO \\\n", "39852 None NaN NaN 0.0 0.0 \n", "39853 None NaN NaN 0.0 0.0 \n", "39854 None NaN NaN 0.0 0.0 \n", "39855 None NaN NaN 0.0 0.0 \n", "39856 None NaN NaN 0.0 0.0 \n", "\n", " PEDIDO PENDENTE DDV PREVISTO preço \n", "39852 0.0 0.0 NaN \n", "39853 0.0 0.0 NaN \n", "39854 0.0 0.0 NaN \n", "39855 0.0 0.0 NaN \n", "39856 0.0 0.0 NaN " ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.tail()" ] }, { "cell_type": "code", "execution_count": 22, "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() #TALVEZ TROLL" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [], "source": [ "df_tabela = df_tabela.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 24, "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": 25, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 1004\n", "1 1080\n", "2 1296\n", "3 1302\n", "4 1314\n", "Name: SKU_FINAL, dtype: object" ] }, "execution_count": 25, "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": 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", "
PDVSKU_FINALDESCRICAOC202411C202412C202413C202414C202501C202502C202503C202504C202505C202506C202507C202508C202509C202510C202511
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [PDV, SKU_FINAL, DESCRICAO, C202411, C202412, C202413, C202414, C202501, C202502, C202503, C202504, C202505, C202506, C202507, C202508, C202509, C202510, C202511]\n", "Index: []" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivotadowawa[df_pivotadowawa['PDV']=='910173']" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": [ "df_draft = pd.merge(df_draft,df_pivotadowawa,left_on=['PDV','SKU'],right_on=['PDV','SKU_FINAL'],how='left')" ] }, { "cell_type": "code", "execution_count": 28, "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", "
PDVSKUDESCRICAO_xcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTE...C202502C202503C202504C202505C202506C202507C202508C202509C202510C202511
1131191017374192DESOBEDIENCIA CREME HID DES CPO 200gBCUIDADOS COM A PELE6.03.01.02.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
1140391017388722QDB BALM LAB CREAM VERM TOD/FLORZINHA 5gAMAQUIAGEM7.06.00.00.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
1156791017356324QDB BALM LAB HOGWARTS SORV/LIMAO 3,2gBMAQUIAGEM5.03.011.00.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
1159591017388738QDB BALM LAB HOGW LESMAS/GEL/VERM 3,2gEMAQUIAGEM6.03.0170.00.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
1161891017388739QDB BALM LAB HOGW PENAS/ACUCAR/ROSA 3,2gEMAQUIAGEM6.03.097.00.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
..................................................................
4116191017375242QDB BAT LIQ SUPERMATE NUDELILI 4mlNoneNoneNaNNaN1.00.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
4117891017353000QDB SHAMP SOLIDO RECARREGA 90gNoneNoneNaNNaN6.00.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
4125191017342115QDB PINCEL PO VS2NoneNoneNaNNaN0.00.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
4125691017350133QDB BAT LIQ MATE FINERR ROSA TO/JOGO 5mlNoneNoneNaNNaN2.00.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
4137691017379775QDB CORRET FAC AQUA HID COR 6 9mlNoneNoneNaNNaN0.00.00.0...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
\n", "

906 rows × 29 columns

\n", "
" ], "text/plain": [ " PDV SKU DESCRICAO_x curva \\\n", "11311 910173 74192 DESOBEDIENCIA CREME HID DES CPO 200g B \n", "11403 910173 88722 QDB BALM LAB CREAM VERM TOD/FLORZINHA 5g A \n", "11567 910173 56324 QDB BALM LAB HOGWARTS SORV/LIMAO 3,2g B \n", "11595 910173 88738 QDB BALM LAB HOGW LESMAS/GEL/VERM 3,2g E \n", "11618 910173 88739 QDB BALM LAB HOGW PENAS/ACUCAR/ROSA 3,2g E \n", "... ... ... ... ... \n", "41161 910173 75242 QDB BAT LIQ SUPERMATE NUDELILI 4ml None \n", "41178 910173 53000 QDB SHAMP SOLIDO RECARREGA 90g None \n", "41251 910173 42115 QDB PINCEL PO VS2 None \n", "41256 910173 50133 QDB BAT LIQ MATE FINERR ROSA TO/JOGO 5ml None \n", "41376 910173 79775 QDB CORRET FAC AQUA HID COR 6 9ml None \n", "\n", " categoria proj_mar proj_mar+1 ESTOQUE ATUAL \\\n", "11311 CUIDADOS COM A PELE 6.0 3.0 1.0 \n", "11403 MAQUIAGEM 7.0 6.0 0.0 \n", "11567 MAQUIAGEM 5.0 3.0 11.0 \n", "11595 MAQUIAGEM 6.0 3.0 170.0 \n", "11618 MAQUIAGEM 6.0 3.0 97.0 \n", "... ... ... ... ... \n", "41161 None NaN NaN 1.0 \n", "41178 None NaN NaN 6.0 \n", "41251 None NaN NaN 0.0 \n", "41256 None NaN NaN 2.0 \n", "41376 None NaN NaN 0.0 \n", "\n", " ESTOQUE EM TRANSITO PEDIDO PENDENTE ... C202502 C202503 C202504 \\\n", "11311 2.0 0.0 ... NaN NaN NaN \n", "11403 0.0 0.0 ... NaN NaN NaN \n", "11567 0.0 0.0 ... NaN NaN NaN \n", "11595 0.0 0.0 ... NaN NaN NaN \n", "11618 0.0 0.0 ... NaN NaN NaN \n", "... ... ... ... ... ... ... \n", "41161 0.0 0.0 ... NaN NaN NaN \n", "41178 0.0 0.0 ... NaN NaN NaN \n", "41251 0.0 0.0 ... NaN NaN NaN \n", "41256 0.0 0.0 ... NaN NaN NaN \n", "41376 0.0 0.0 ... NaN NaN NaN \n", "\n", " C202505 C202506 C202507 C202508 C202509 C202510 C202511 \n", "11311 NaN NaN NaN NaN NaN NaN NaN \n", "11403 NaN NaN NaN NaN NaN NaN NaN \n", "11567 NaN NaN NaN NaN NaN NaN NaN \n", "11595 NaN NaN NaN NaN NaN NaN NaN \n", "11618 NaN NaN NaN NaN NaN NaN NaN \n", "... ... ... ... ... ... ... ... \n", "41161 NaN NaN NaN NaN NaN NaN NaN \n", "41178 NaN NaN NaN NaN NaN NaN NaN \n", "41251 NaN NaN NaN NaN NaN NaN NaN \n", "41256 NaN NaN NaN NaN NaN NaN NaN \n", "41376 NaN NaN NaN NaN NaN NaN NaN \n", "\n", "[906 rows x 29 columns]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft[df_draft['PDV']=='910173']" ] }, { "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", "
PDVSKUDESCRICAO_xcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTE...C202502C202503C202504C202505C202506C202507C202508C202509C202510C202511
\n", "

0 rows × 29 columns

\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [PDV, SKU, DESCRICAO_x, curva, categoria, proj_mar, proj_mar+1, ESTOQUE ATUAL, ESTOQUE EM TRANSITO, PEDIDO PENDENTE, DDV PREVISTO, preço, SKU_FINAL, DESCRICAO_y, C202411, C202412, C202413, C202414, C202501, C202502, C202503, C202504, C202505, C202506, C202507, C202508, C202509, C202510, C202511]\n", "Index: []\n", "\n", "[0 rows x 29 columns]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft[df_draft['SKU']=='1634']" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(3672, 33)" ] }, "execution_count": 30, "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": 31, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20968 1\n", "20969 1\n", "20970 1\n", "20986 1\n", "20988 1\n", "20989 1\n", "20991 1\n", "20992 1\n", "20993 1\n", "20994 1\n", "20995 1\n", "20996 1\n", "20997 1\n", "20998 1\n", "20999 1\n", "21000 1\n", "21001 1\n", "21278 1\n", "21375 1\n", "21383 1\n", "21495 1\n", "22541 1\n", "23703 1\n", "23704 1\n", "23708 1\n", "23711 1\n", "23712 1\n", "24255 1\n", "24257 1\n", "24269 1\n", "24293 1\n", "910173 1\n", "910291 1\n", "23813 1\n", "Name: count, dtype: int64" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pdv['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20968 108\n", "20969 108\n", "20970 108\n", "20986 108\n", "20988 108\n", "20989 108\n", "20991 108\n", "20992 108\n", "20993 108\n", "20994 108\n", "20995 108\n", "20996 108\n", "20997 108\n", "20998 108\n", "20999 108\n", "21000 108\n", "21001 108\n", "21278 108\n", "21375 108\n", "21383 108\n", "21495 108\n", "22541 108\n", "23703 108\n", "23704 108\n", "23708 108\n", "23711 108\n", "23712 108\n", "24255 108\n", "24257 108\n", "24269 108\n", "24293 108\n", "910173 108\n", "910291 108\n", "23813 108\n", "Name: count, dtype: int64" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['PDV', 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar',\n", " 'proj_mar+1', 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', 'DESCRICAO_y', 'C202411',\n", " 'C202412', 'C202413', 'C202414', 'C202501', 'C202502', 'C202503',\n", " 'C202504', 'C202505', 'C202506', 'C202507', 'C202508', 'C202509',\n", " 'C202510', 'C202511'],\n", " dtype='object')" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.columns" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['PDV', 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar',\n", " 'proj_mar+1', 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', 'C202411', 'C202412', 'C202413',\n", " 'C202414', 'C202501', 'C202502', 'C202503', 'C202504', 'C202505',\n", " 'C202506', 'C202507', 'C202508', 'C202509', 'C202510', 'C202511'],\n", " dtype='object')" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft = df_draft.drop(columns='DESCRICAO_y')\n", "\n", "df_draft.columns" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['C202411', 'C202412', 'C202413', 'C202414', 'C202501', 'C202502',\n", " 'C202503', 'C202504', 'C202505', 'C202506', 'C202507', 'C202508',\n", " 'C202509', 'C202510', 'C202511'],\n", " dtype='object')" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.columns[-15:]" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20998 448\n", "20997 423\n", "23711 420\n", "20993 390\n", "20996 374\n", "21278 363\n", "20994 315\n", "22541 308\n", "20995 306\n", "20970 287\n", "20989 278\n", "24255 277\n", "20992 275\n", "21375 254\n", "23704 251\n", "20999 250\n", "21495 245\n", "20969 245\n", "21383 245\n", "21000 239\n", "20991 235\n", "21001 230\n", "20988 211\n", "20968 205\n", "23703 191\n", "20986 183\n", "23712 148\n", "24257 146\n", "24269 116\n", "24293 96\n", "23813 52\n", "23708 14\n", "Name: count, dtype: int64" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "numericos = df_draft.columns[-15:]\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": 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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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
1030591017374192DESOBEDIENCIA CREME HID DES CPO 200gBCUIDADOS COM A PELE6.03.01200,1413.47
1039191017388722QDB BALM LAB CREAM VERM TOD/FLORZINHA 5gAMAQUIAGEM7.06.00000,1816.06
1053691017356324QDB BALM LAB HOGWARTS SORV/LIMAO 3,2gBMAQUIAGEM5.03.011000,1914.48
1056091017388738QDB BALM LAB HOGW LESMAS/GEL/VERM 3,2gEMAQUIAGEM6.03.0170000,2313.16
1058391017388739QDB BALM LAB HOGW PENAS/ACUCAR/ROSA 3,2gEMAQUIAGEM6.03.097000,2313.16
.......................................
3958091017375242QDB BAT LIQ SUPERMATE NUDELILI 4mlNoneNoneNaNNaN100NoneNaN
3959791017353000QDB SHAMP SOLIDO RECARREGA 90gNoneNoneNaNNaN600NoneNaN
3967091017342115QDB PINCEL PO VS2NoneNoneNaNNaN000NoneNaN
3967591017350133QDB BAT LIQ MATE FINERR ROSA TO/JOGO 5mlNoneNoneNaNNaN200NoneNaN
3979391017379775QDB CORRET FAC AQUA HID COR 6 9mlNoneNoneNaNNaN000NoneNaN
\n", "

906 rows × 12 columns

\n", "
" ], "text/plain": [ " PDV SKU DESCRICAO curva \\\n", "10305 910173 74192 DESOBEDIENCIA CREME HID DES CPO 200g B \n", "10391 910173 88722 QDB BALM LAB CREAM VERM TOD/FLORZINHA 5g A \n", "10536 910173 56324 QDB BALM LAB HOGWARTS SORV/LIMAO 3,2g B \n", "10560 910173 88738 QDB BALM LAB HOGW LESMAS/GEL/VERM 3,2g E \n", "10583 910173 88739 QDB BALM LAB HOGW PENAS/ACUCAR/ROSA 3,2g E \n", "... ... ... ... ... \n", "39580 910173 75242 QDB BAT LIQ SUPERMATE NUDELILI 4ml None \n", "39597 910173 53000 QDB SHAMP SOLIDO RECARREGA 90g None \n", "39670 910173 42115 QDB PINCEL PO VS2 None \n", "39675 910173 50133 QDB BAT LIQ MATE FINERR ROSA TO/JOGO 5ml None \n", "39793 910173 79775 QDB CORRET FAC AQUA HID COR 6 9ml None \n", "\n", " categoria proj_mar proj_mar+1 ESTOQUE ATUAL \\\n", "10305 CUIDADOS COM A PELE 6.0 3.0 1 \n", "10391 MAQUIAGEM 7.0 6.0 0 \n", "10536 MAQUIAGEM 5.0 3.0 11 \n", "10560 MAQUIAGEM 6.0 3.0 170 \n", "10583 MAQUIAGEM 6.0 3.0 97 \n", "... ... ... ... ... \n", "39580 None NaN NaN 1 \n", "39597 None NaN NaN 6 \n", "39670 None NaN NaN 0 \n", "39675 None NaN NaN 2 \n", "39793 None NaN NaN 0 \n", "\n", " ESTOQUE EM TRANSITO PEDIDO PENDENTE DDV PREVISTO preço \n", "10305 2 0 0,14 13.47 \n", "10391 0 0 0,18 16.06 \n", "10536 0 0 0,19 14.48 \n", "10560 0 0 0,23 13.16 \n", "10583 0 0 0,23 13.16 \n", "... ... ... ... ... \n", "39580 0 0 None NaN \n", "39597 0 0 None NaN \n", "39670 0 0 None NaN \n", "39675 0 0 None NaN \n", "39793 0 0 None NaN \n", "\n", "[906 rows x 12 columns]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft_[df_draft_['PDV']=='910173']" ] }, { "cell_type": "code", "execution_count": 38, "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", "
PDVSKUDESCRICAO_xcurvacategoriaproj_marproj_mar+1ESTOQUE ATUALESTOQUE EM TRANSITOPEDIDO PENDENTE...C202502C202503C202504C202505C202506C202507C202508C202509C202510C202511
\n", "

0 rows × 28 columns

\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [PDV, SKU, DESCRICAO_x, curva, categoria, proj_mar, proj_mar+1, ESTOQUE ATUAL, ESTOQUE EM TRANSITO, PEDIDO PENDENTE, DDV PREVISTO, preço, SKU_FINAL, C202411, C202412, C202413, C202414, C202501, C202502, C202503, C202504, C202505, C202506, C202507, C202508, C202509, C202510, C202511]\n", "Index: []\n", "\n", "[0 rows x 28 columns]" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft[df_draft['SKU']=='1634']" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [], "source": [ "df_draft1 = df_draft_agg" ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['PDV', 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar',\n", " 'proj_mar+1', 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', 'C202411', 'C202412', 'C202413',\n", " 'C202414', 'C202501', 'C202502', 'C202503', 'C202504', 'C202505',\n", " 'C202506', 'C202507', 'C202508', 'C202509', 'C202510', 'C202511'],\n", " dtype='object')" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft1.columns" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [], "source": [ "df_draft1.to_clipboard()" ] }, { "cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(3771, 60)" ] }, "execution_count": 42, "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": 43, "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...C202502C202503C202504C202505C202506C202507C202508C202509C202510C202511
\n", "

0 rows × 60 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, SUP, ANALISTA EUD, SKU, DESCRICAO_x, curva, categoria, proj_mar, proj_mar+1, ESTOQUE ATUAL, ESTOQUE EM TRANSITO, PEDIDO PENDENTE, DDV PREVISTO, preço, SKU_FINAL, C202411, C202412, C202413, C202414, C202501, C202502, C202503, C202504, C202505, C202506, C202507, C202508, C202509, C202510, C202511]\n", "Index: []\n", "\n", "[0 rows x 60 columns]" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final[df_final['Código']=='1634'].head()" ] }, { "cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20997 117\n", "20970 116\n", "23711 116\n", "20998 115\n", "20993 115\n", "20996 114\n", "21278 113\n", "20969 112\n", "20995 112\n", "20992 112\n", "21383 112\n", "21000 112\n", "21001 112\n", "20994 112\n", "22541 112\n", "20989 111\n", "20999 110\n", "20991 110\n", "21495 110\n", "20986 110\n", "20988 110\n", "21375 110\n", "20968 109\n", "23712 109\n", "23704 108\n", "23703 108\n", "23708 108\n", "24255 108\n", "24257 108\n", "24269 108\n", "24293 108\n", "910173 108\n", "910291 108\n", "23813 108\n", "Name: count, dtype: int64" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.drop(columns=['Sortimento P', 'Sortimento M',\n", " 'Sortimento G','MARCA'])" ] }, { "cell_type": "code", "execution_count": 46, "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": 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", "
CicloINICIO CICLOFIM CICLODURAÇÃODateNUM_CICLOANO_CICLOCICLOMAIS2dias_ate_inicio
2409C2025152025-10-132025-11-02212025-10-1315C2025C20251724
\n", "
" ], "text/plain": [ " Ciclo INICIO CICLO FIM CICLO DURAÇÃO Date NUM_CICLO \\\n", "2409 C202515 2025-10-13 2025-11-02 21 2025-10-13 15 \n", "\n", " ANO_CICLO CICLOMAIS2 dias_ate_inicio \n", "2409 C2025 C202517 24 " ] }, "execution_count": 47, "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": 48, "metadata": {}, "outputs": [], "source": [ "filtered_calendario['MATCH'] = 1" ] }, { "cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [], "source": [ "df_pdv['UF'] = np.where(df_pdv['UF'] == 'VDC','BA',df_pdv['UF'])" ] }, { "cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [], "source": [ "df_draft['PDV'] = df_draft['PDV'].astype(str)" ] }, { "cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20997 2749\n", "20992 2646\n", "20969 2639\n", "21007 1592\n", "20998 1430\n", " ... \n", "20006 57\n", "24258 2\n", "24254 2\n", "24253 2\n", "24268 2\n", "Name: count, Length: 80, dtype: int64" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 52, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(3771, 61)" ] }, "execution_count": 52, "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": 53, "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": 54, "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": 54, "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": 55, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(3771, 68)" ] }, "execution_count": 55, "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": 56, "metadata": {}, "outputs": [], "source": [ "df_bi_preco['SKU1'] = df_bi_preco['SKU1'].fillna(0)" ] }, { "cell_type": "code", "execution_count": 57, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(3771, 72)\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": 58, "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": 59, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "CANAL\n", "TODOS 1973\n", "VD 1798\n", "Name: count, dtype: int64" ] }, "execution_count": 59, "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": 60, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 61, "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": 62, "metadata": {}, "outputs": [], "source": [ "df_final['UFPRODUTO'] = df_final['UF'].astype(str) + df_final['Código'].astype(str)\n" ] }, { "cell_type": "code", "execution_count": 63, "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": 64, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "np.int64(0)" ] }, "execution_count": 64, "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": 65, "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', 'SUP', 'ANALISTA EUD',\n", " 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', 'C202411', 'C202412', 'C202413',\n", " 'C202414', 'C202501', 'C202502', 'C202503', 'C202504', 'C202505',\n", " 'C202506', 'C202507', 'C202508', 'C202509', 'C202510', 'C202511',\n", " 'Ciclo_y', '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'],\n", " dtype='object')" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 66, "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": 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", " \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": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_vdc[df_vdc['PRODUTO']== 74043].head()" ] }, { "cell_type": "code", "execution_count": 68, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.fillna(0)\n" ] }, { "cell_type": "code", "execution_count": 69, "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', 'SUP', 'ANALISTA EUD',\n", " 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', 'C202411', 'C202412', 'C202413',\n", " 'C202414', 'C202501', 'C202502', 'C202503', 'C202504', 'C202505',\n", " 'C202506', 'C202507', 'C202508', 'C202509', 'C202510', 'C202511',\n", " 'Ciclo_y', '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', '202401',\n", " '202402', '202403', '202404', '202405', '202406', '202407', '202408',\n", " '202409', '202410', '202411', '202412', '202413', '202414', '202415',\n", " '202416'],\n", " dtype='object')" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 70, "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": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[-16:]" ] }, { "cell_type": "code", "execution_count": 71, "metadata": {}, "outputs": [], "source": [ "df_final.to_clipboard()" ] }, { "cell_type": "code", "execution_count": 72, "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": 73, "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', 'SUP', 'ANALISTA EUD',\n", " 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', 'C202411', 'C202412', 'C202413',\n", " 'C202414', 'C202501', 'C202502', 'C202503', 'C202504', 'C202505',\n", " 'C202506', 'C202507', 'C202508', 'C202509', 'C202510', 'C202511',\n", " 'Ciclo_y', '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": 73, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 74, "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": 74, "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": 75, "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": 76, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['preço', 'SKU_FINAL', 'C202411', 'C202412', 'C202413', 'C202414',\n", " 'C202501', 'C202502', 'C202503', 'C202504', 'C202505', 'C202506',\n", " 'C202507', 'C202508', 'C202509', 'C202510', 'C202511', 'Ciclo_y'],\n", " dtype='object')" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[37:55]" ] }, { "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', 'SUP', 'ANALISTA EUD',\n", " 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', '202411', '202412', '202413',\n", " '202414', '202501', '202502', '202503', '202504', '202505', '202506',\n", " '202507', '202508', '202509', '202510', '202511', '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": 77, "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": 78, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20997 117\n", "20970 116\n", "23711 116\n", "20998 115\n", "20993 115\n", "20996 114\n", "21278 113\n", "20969 112\n", "20995 112\n", "20992 112\n", "21383 112\n", "21000 112\n", "21001 112\n", "20994 112\n", "22541 112\n", "20989 111\n", "20999 110\n", "20991 110\n", "21495 110\n", "20986 110\n", "20988 110\n", "21375 110\n", "20968 109\n", "23712 109\n", "23704 108\n", "23703 108\n", "23708 108\n", "24255 108\n", "24257 108\n", "24269 108\n", "24293 108\n", "910173 108\n", "910291 108\n", "23813 108\n", "Name: count, dtype: Int64" ] }, "execution_count": 78, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 79, "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
0202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...0.00.00.00.00.00.00.00.00.00.0
1202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...0.00.00.00.00.00.00.00.00.00.0
2202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...0.00.00.00.00.00.00.00.00.00.0
3202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...0.00.00.00.00.00.00.00.00.00.0
4202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...0.00.00.00.00.00.00.00.00.00.0
\n", "

5 rows × 91 columns

\n", "
" ], "text/plain": [ " Ciclo_x Região Canal Código Descrição_x IAF \\\n", "0 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "1 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "2 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "3 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "4 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "\n", " Tipo de pedido Foco Unidade de negócio Marca ... C202415 \\\n", "0 Especial Sim QDB QUEM DISSE BERENICE ... 0.0 \n", "1 Especial Sim QDB QUEM DISSE BERENICE ... 0.0 \n", "2 Especial Sim QDB QUEM DISSE BERENICE ... 0.0 \n", "3 Especial Sim QDB QUEM DISSE BERENICE ... 0.0 \n", "4 Especial Sim QDB QUEM DISSE BERENICE ... 0.0 \n", "\n", " C202416 C202417 C202501 C202502 C202503 C202504 C202505 C202506 C202507 \n", "0 0.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 0.0 0.0 0.0 0.0 \n", "2 0.0 0.0 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 0.0 0.0 \n", "4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n", "\n", "[5 rows x 91 columns]" ] }, "execution_count": 79, "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": 80, "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', 'SUP', 'ANALISTA EUD',\n", " 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', '202411', '202412', '202413',\n", " '202414', '202501', '202502', '202503', '202504', '202505', '202506',\n", " '202507', '202508', '202509', '202510', '202511', '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": 80, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 81, "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": 82, "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', 'SUP', 'ANALISTA EUD',\n", " 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', '202411', '202412', '202413',\n", " '202414', '202501', '202502', '202503', '202504', '202505', '202506',\n", " '202507', '202508', '202509', '202510', '202511', '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": 82, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 83, "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": 84, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.drop(columns=['Ciclo_y'])" ] }, { "cell_type": "code", "execution_count": 85, "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', 'SUP', 'ANALISTA EUD',\n", " 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', 'SKU_FINAL', '202411', '202412', '202413',\n", " '202414', '202501', '202502', '202503', '202504', '202505', '202506',\n", " '202507', '202508', '202509', '202510', '202511', '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": 85, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 86, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(3771, 66)" ] }, "execution_count": 86, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final = df_final.drop_duplicates()\n", "df_final.shape" ] }, { "cell_type": "code", "execution_count": 87, "metadata": {}, "outputs": [], "source": [ "df_final['chave'] = df_final['PDV'].astype('str') + df_final['Código'].astype('str')" ] }, { "cell_type": "code", "execution_count": 88, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.fillna(0)\n" ] }, { "cell_type": "code", "execution_count": 89, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202411', '202412', '202413', '202414', '202501', '202502', '202503',\n", " '202504', '202505', '202506', '202507', '202508', '202509', '202510',\n", " '202511'],\n", " dtype='object')" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[39:54]" ] }, { "cell_type": "code", "execution_count": 90, "metadata": {}, "outputs": [], "source": [ "# Define list of target columns\n", "sales_2024_cols = df_final.columns[39:54]\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": 91, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202505', '202506', '202507', '202508', '202509', '202510', '202511'], dtype='object')" ] }, "execution_count": 91, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[47:54]" ] }, { "cell_type": "code", "execution_count": 92, "metadata": {}, "outputs": [], "source": [ "vendas_6_meses = df_final.columns[47:54]\n", "\n", "df_final['Pico Vendas Ultimos 6 ciclos'] = df_final[vendas_6_meses].max(axis=1)" ] }, { "cell_type": "code", "execution_count": 93, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202411', '202412', '202413', '202414', '202501', '202502', '202503',\n", " '202504', '202505', '202506', '202507', '202508', '202509', '202510',\n", " '202511'],\n", " dtype='object')" ] }, "execution_count": 93, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[39:54]" ] }, { "cell_type": "code", "execution_count": 94, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_43676\\3012255654.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[39:54]\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": 95, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "np.float64(0.2164)" ] }, "execution_count": 95, "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[39:54]\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": 96, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['202411', '202412', '202413', '202414', '202501', '202502', '202503',\n", " '202504', '202505', '202506', '202507', '202508', '202509', '202510',\n", " '202511'],\n", " dtype='object')" ] }, "execution_count": 96, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[39:54]" ] }, { "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", "
CANALUFCódigomed_por_canal
0TODOSAL469380.0
1TODOSAL471960.0
2TODOSAL498990.0
3TODOSAL499010.0
4TODOSAL499020.0
...............
1075VDVDC847530.0
1076VDVDC847541.0
1077VDVDC861150.0
1078VDVDC869750.0
1079VDVDC875200.0
\n", "

1080 rows × 4 columns

\n", "
" ], "text/plain": [ " CANAL UF Código med_por_canal\n", "0 TODOS AL 46938 0.0\n", "1 TODOS AL 47196 0.0\n", "2 TODOS AL 49899 0.0\n", "3 TODOS AL 49901 0.0\n", "4 TODOS AL 49902 0.0\n", "... ... ... ... ...\n", "1075 VD VDC 84753 0.0\n", "1076 VD VDC 84754 1.0\n", "1077 VD VDC 86115 0.0\n", "1078 VD VDC 86975 0.0\n", "1079 VD VDC 87520 0.0\n", "\n", "[1080 rows x 4 columns]" ] }, "execution_count": 97, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vendas_todos_historicos = df_final.columns[39:54]\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": 98, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'202413'" ] }, "execution_count": 98, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[41]" ] }, { "cell_type": "code", "execution_count": 99, "metadata": {}, "outputs": [], "source": [ "mesmo_ciclo_ano_passado = df_final.columns[41]\n", "ciclo_ano_passado = df_final.columns[41]\n", "df_final[ciclo_ano_passado] = df_final[mesmo_ciclo_ano_passado]" ] }, { "cell_type": "code", "execution_count": 100, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "\"df_final['CRESCIMENTO_GERAL'] = 0.2\\ndf_final['CRESCIMENTO'] = 0.2\"" ] }, "execution_count": 100, "metadata": {}, "output_type": "execute_result" } ], "source": [ "'''df_final['CRESCIMENTO_GERAL'] = 0.2\n", "df_final['CRESCIMENTO'] = 0.2'''" ] }, { "cell_type": "code", "execution_count": 101, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(3771, 77)" ] }, "execution_count": 101, "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] \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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
0202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...1.01.00.71850.20.00.1333330.00.91850.1333330.0
1202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...1.01.00.62580.20.00.0666670.00.82580.0666670.0
2202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...8.08.00.76430.20.01.1333334.00.96431.1333332.0
3202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...2.01.01.67580.20.00.2666670.01.87580.2666676.0
4202513NNETodos47196QDB MASC CILIO BIG BANG 360 10gNãoEspecialSimQDBQUEM DISSE BERENICE...5.01.00.20770.20.00.6000000.00.40770.6000003.0
\n", "

5 rows × 77 columns

\n", "" ], "text/plain": [ " Ciclo_x Região Canal Código Descrição_x IAF \\\n", "0 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "1 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "2 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "3 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "4 202513 NNE Todos 47196 QDB MASC CILIO BIG BANG 360 10g Não \n", "\n", " Tipo de pedido Foco Unidade de negócio Marca ... \\\n", "0 Especial Sim QDB QUEM DISSE BERENICE ... \n", "1 Especial Sim QDB QUEM DISSE BERENICE ... \n", "2 Especial Sim QDB QUEM DISSE BERENICE ... \n", "3 Especial Sim QDB QUEM DISSE BERENICE ... \n", "4 Especial Sim QDB QUEM DISSE BERENICE ... \n", "\n", " PICO DE VENDAS 2024 Pico Vendas Ultimos 6 ciclos CRESCIMENTO \\\n", "0 1.0 1.0 0.7185 \n", "1 1.0 1.0 0.6258 \n", "2 8.0 8.0 0.7643 \n", "3 2.0 1.0 1.6758 \n", "4 5.0 1.0 0.2077 \n", "\n", " CRESCIMENTO_GERAL MEDIANA DO HISTÓRICO MEDIA DO HISTÓRICO med_por_canal \\\n", "0 0.2 0.0 0.133333 0.0 \n", "1 0.2 0.0 0.066667 0.0 \n", "2 0.2 0.0 1.133333 4.0 \n", "3 0.2 0.0 0.266667 0.0 \n", "4 0.2 0.0 0.600000 0.0 \n", "\n", " CRESCIMENTO_FINAL MEDIANA DO HISTÓRICO2 PV GINSENG \n", "0 0.9185 0.133333 0.0 \n", "1 0.8258 0.066667 0.0 \n", "2 0.9643 1.133333 2.0 \n", "3 1.8758 0.266667 6.0 \n", "4 0.4077 0.600000 3.0 \n", "\n", "[5 rows x 77 columns]" ] }, "execution_count": 103, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.head()" ] }, { "cell_type": "code", "execution_count": 104, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.drop(columns=['MEDIANA DO HISTÓRICO2'] )" ] }, { "cell_type": "code", "execution_count": 108, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['C-4', 'C-3', 'C-2', 'C-1', 'Atual'], dtype='object')" ] }, "execution_count": 108, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[49:54]" ] }, { "cell_type": "code", "execution_count": 106, "metadata": {}, "outputs": [], "source": [ "df_final = df_final.rename(columns={df_final.columns[49]: \"C-4\", df_final.columns[50]: \"C-3\",df_final.columns[51]: \"C-2\",df_final.columns[52]: \"C-1\",df_final.columns[53]: \"Atual\"})" ] }, { "cell_type": "code", "execution_count": 110, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['SKU_FINAL', '202411', '202412'], dtype='object')" ] }, "execution_count": 110, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns[38:41]" ] }, { "cell_type": "code", "execution_count": 111, "metadata": {}, "outputs": [], "source": [ "df_final.drop(columns=df_final.columns[42:49], inplace=True)\n", "df_final.drop(columns=df_final.columns[38:41], inplace=True)" ] }, { "cell_type": "code", "execution_count": 112, "metadata": {}, "outputs": [], "source": [ "df_final['COBERTURA ATUAL'] = df_final['ESTOQUE ATUAL'].astype(float) / df_final['DDV PREVISTO'].astype(float)" ] }, { "cell_type": "code", "execution_count": 113, "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": 114, "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": 115, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20997 117\n", "20970 116\n", "23711 116\n", "20998 115\n", "20993 115\n", "20996 114\n", "21278 113\n", "20969 112\n", "20995 112\n", "20992 112\n", "21383 112\n", "21000 112\n", "21001 112\n", "20994 112\n", "22541 112\n", "20989 111\n", "20999 110\n", "20991 110\n", "21495 110\n", "20986 110\n", "20988 110\n", "21375 110\n", "20968 109\n", "23712 109\n", "23704 108\n", "23703 108\n", "23708 108\n", "24255 108\n", "24257 108\n", "24269 108\n", "24293 108\n", "910173 108\n", "910291 108\n", "23813 108\n", "Name: count, dtype: Int64" ] }, "execution_count": 115, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 116, "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": 117, "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', 'SUP', 'ANALISTA EUD',\n", " 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', '202413', '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": 117, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final.columns" ] }, { "cell_type": "code", "execution_count": 118, "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": 118, "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": 119, "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", "
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
\n", "

0 rows × 67 columns

\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [Ciclo_x, Região, Canal, Código, Descrição_x, IAF, Tipo de pedido, Foco, Unidade de negócio, Marca, Categoria, Tipo de promoção, Catálogo, Tipo de produto, Ação consumidor, Percentual de desconto consumidor, Ação revendedor, Percentual de desconto revendedor, MATCH, PDV, CANAL, DESCRIÇÃO PDV, UF, ANALISTA, SUPERVISOR, SUP, ANALISTA EUD, SKU, DESCRICAO_x, curva, categoria, proj_mar, proj_mar+1, ESTOQUE ATUAL, ESTOQUE EM TRANSITO, PEDIDO PENDENTE, DDV PREVISTO, preço, 202413, C-4, C-3, C-2, C-1, Atual, INICIO CICLO, FIM CICLO, DURAÇÃO, dias_ate_inicio, Descrição_y, chave sku2, chave sku1, PRECO DE COMPRA, PRECO DE VENDA, UFPRODUTO, PDV GINSENG, PRODUTO, chave, PICO DE VENDAS 2024, Pico Vendas Ultimos 6 ciclos, CRESCIMENTO, CRESCIMENTO_GERAL, MEDIANA DO HISTÓRICO, MEDIA DO HISTÓRICO, med_por_canal, CRESCIMENTO_FINAL, PV GINSENG, COBERTURA ATUAL]\n", "Index: []\n", "\n", "[0 rows x 67 columns]" ] }, "execution_count": 119, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup[df_final_dedup['PDV'] == 21007]" ] }, { "cell_type": "code", "execution_count": 120, "metadata": {}, "outputs": [], "source": [ "df_final_dedup.drop(columns=['dias_ate_inicio', 'CRESCIMENTO'],inplace=True)" ] }, { "cell_type": "code", "execution_count": 121, "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": 122, "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": 123, "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": 124, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "np.int64(0)" ] }, "execution_count": 124, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup['Código'].isna().sum()" ] }, { "cell_type": "code", "execution_count": 125, "metadata": {}, "outputs": [], "source": [ "df_irece_agrupado = df_irece.groupby(['PDV','Código','Ciclo'])['Quantidade'].sum().reset_index()" ] }, { "cell_type": "code", "execution_count": 126, "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": 127, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20997 117\n", "20970 116\n", "23711 116\n", "20998 115\n", "20993 115\n", "20996 114\n", "21278 113\n", "20969 112\n", "20995 112\n", "20992 112\n", "21383 112\n", "21000 112\n", "21001 112\n", "20994 112\n", "22541 112\n", "20989 111\n", "20999 110\n", "20991 110\n", "21495 110\n", "20986 110\n", "20988 110\n", "21375 110\n", "20968 109\n", "23712 109\n", "23704 108\n", "23703 108\n", "23708 108\n", "24255 108\n", "24257 108\n", "24269 108\n", "24293 108\n", "910173 108\n", "910291 108\n", "23813 108\n", "Name: count, dtype: Int64" ] }, "execution_count": 127, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 128, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = df_final_dedup.rename(columns={'UF_x':'UF','DESCRICAO_y':'DESCRICAO'})" ] }, { "cell_type": "code", "execution_count": 129, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = df_final_dedup.drop(columns={'CANAL_y', 'UF_y'})\n" ] }, { "cell_type": "code", "execution_count": 130, "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": 131, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'202413'" ] }, "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(columns='Descrição_y')\n", "\n", "df_final_dedup = df_final_dedup.rename(columns={'Descrição_x':'Descrição'})" ] }, { "cell_type": "code", "execution_count": 133, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Ciclo_x', 'Região', 'Canal', 'Código', 'Descrição', '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', 'SUP', 'ANALISTA EUD',\n", " 'SKU', 'DESCRICAO_x', 'curva', 'categoria', 'proj_mar', 'proj_mar+1',\n", " 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE',\n", " 'DDV PREVISTO', 'preço', '202413', 'C-4', 'C-3', 'C-2', 'C-1', 'Atual',\n", " 'INICIO CICLO', 'FIM CICLO', 'DURAÇÃO', '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": 133, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup.columns" ] }, { "cell_type": "code", "execution_count": 134, "metadata": {}, "outputs": [], "source": [ "# colunas de agrupamento\n", "cols_group = [\n", "'CANAL','ANALISTA','SUPERVISOR','UF','chave','PDV','DESCRIÇÃO PDV','Código','Descrição','LINHA','Categoria'\n", "]\n", "\n", "\n", "# colunas para somar\n", "cols_sum = [\n", "'ESTOQUE ATUAL','ESTOQUE EM TRANSITO','PEDIDO PENDENTE','Estoque Total','DDV PREVISTO'\n", "]\n", "\n", "\n", "# construir dicionário de agregação\n", "agg_dict = {}\n", "for col in df_final_dedup.columns:\n", " if col in cols_group:\n", " continue\n", " elif col in cols_sum:\n", " agg_dict[col] = 'sum'\n", " else:\n", " agg_dict[col] = 'max'\n", "\n", "# aplicar groupby\n", "result = df_final_dedup.groupby(cols_group, as_index=False).agg(agg_dict)" ] }, { "cell_type": "code", "execution_count": 135, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = result" ] }, { "cell_type": "code", "execution_count": 136, "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": 137, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_43676\\3244855783.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_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'])\n" ] } ], "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": 138, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'202413'" ] }, "execution_count": 138, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ciclo_ano_passado" ] }, { "cell_type": "code", "execution_count": 139, "metadata": {}, "outputs": [], "source": [ "df_final_dedup = df_final_dedup.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 140, "metadata": {}, "outputs": [], "source": [ "df_final_dedup['Valor Sugestão'] = \"\"\n", "\n", "df_final_dedup['Sugestão Abastecimento'] = \"\"" ] }, { "cell_type": "code", "execution_count": 141, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "DDV PREVISTO float64\n", "COBERTURA ATUAL float64\n", "dtype: object" ] }, "execution_count": 141, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup[['DDV PREVISTO',\n", " 'COBERTURA ATUAL',]].dtypes" ] }, { "cell_type": "code", "execution_count": 142, "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": 143, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Series([], Name: DDV PREVISTO, dtype: float64)" ] }, "execution_count": 143, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup[df_final_dedup['chave'] == '2371184387']['DDV PREVISTO'].head()\n" ] }, { "cell_type": "code", "execution_count": 144, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20988 108\n", "21000 108\n", "21375 108\n", "910173 108\n", "910291 108\n", "21495 108\n", "23708 108\n", "20986 108\n", "21001 108\n", "23712 108\n", "20989 108\n", "20999 108\n", "23813 108\n", "24293 108\n", "20968 108\n", "20969 108\n", "20991 108\n", "24257 108\n", "20970 108\n", "20993 108\n", "21383 108\n", "20996 108\n", "22541 108\n", "20992 108\n", "21278 108\n", "24269 108\n", "24255 108\n", "23703 108\n", "20997 108\n", "20994 108\n", "23704 108\n", "20995 108\n", "20998 108\n", "23711 108\n", "Name: count, dtype: Int64" ] }, "execution_count": 144, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 145, "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', '202413', '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": 145, "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": 146, "metadata": {}, "outputs": [], "source": [ "df_final_dedup=df_final_dedup.drop(columns=['CANAL_CERTO'])" ] }, { "cell_type": "code", "execution_count": 147, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "PDV\n", "20988 108\n", "21000 108\n", "21375 108\n", "910173 108\n", "910291 108\n", "21495 108\n", "23708 108\n", "20986 108\n", "21001 108\n", "23712 108\n", "20989 108\n", "20999 108\n", "23813 108\n", "24293 108\n", "20968 108\n", "20969 108\n", "20991 108\n", "24257 108\n", "20970 108\n", "20993 108\n", "21383 108\n", "20996 108\n", "22541 108\n", "20992 108\n", "21278 108\n", "24269 108\n", "24255 108\n", "23703 108\n", "20997 108\n", "20994 108\n", "23704 108\n", "20995 108\n", "20998 108\n", "23711 108\n", "Name: count, dtype: Int64" ] }, "execution_count": 147, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_final_dedup['PDV'].value_counts()" ] }, { "cell_type": "code", "execution_count": 148, "metadata": {}, "outputs": [], "source": [ "# Export to Excel\n", "output_file = f'C:\\\\Users\\\\joao.herculano\\\\Documents\\\\promoção_QDB_{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 }