{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "3fc99dd1", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np \n", "import glob\n", "import os " ] }, { "cell_type": "code", "execution_count": 2, "id": "f5b91893", "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", "\n", "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", "\n", "calendario['ANO_CICLO'] = calendario['Ciclo'].str[0:5]\n", "\n", "calendario = calendario[calendario['MARCA'] == \"BOTICARIO\"]\n", "\n", "calendario['CICLOMAIS2'] = calendario['ANO_CICLO'].astype(str) + (calendario['NUM_CICLO'].astype(int) + 3).astype(str).str.zfill(2) #<<< MUDAR O \"4\" (CICLO ATUAL + 4 PARA ACHAR O CICLO DA SUGESTÃO) EX: C202505 -> C202509\n", "ciclo_mais2 = calendario[calendario['Date'].dt.normalize() == today]['CICLOMAIS2'].iloc[0]\n", "\n" ] }, { "cell_type": "code", "execution_count": 3, "id": "128a73f8", "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", "
CicloINICIO CICLOFIM CICLODURAÇÃOMARCADateNUM_CICLOANO_CICLOCICLOMAIS2
0C2022012021-12-262022-01-2329BOTICARIO2021-12-261C2022C202204
1C2022012021-12-262022-01-2329BOTICARIO2021-12-271C2022C202204
2C2022012021-12-262022-01-2329BOTICARIO2021-12-281C2022C202204
3C2022012021-12-262022-01-2329BOTICARIO2021-12-291C2022C202204
4C2022012021-12-262022-01-2329BOTICARIO2021-12-301C2022C202204
..............................
2527C2025172025-12-012025-12-2525BOTICARIO2025-12-2117C2025C202520
2528C2025172025-12-012025-12-2525BOTICARIO2025-12-2217C2025C202520
2529C2025172025-12-012025-12-2525BOTICARIO2025-12-2317C2025C202520
2530C2025172025-12-012025-12-2525BOTICARIO2025-12-2417C2025C202520
2531C2025172025-12-012025-12-2525BOTICARIO2025-12-2517C2025C202520
\n", "

1461 rows × 9 columns

\n", "
" ], "text/plain": [ " Ciclo INICIO CICLO FIM CICLO DURAÇÃO MARCA Date \\\n", "0 C202201 2021-12-26 2022-01-23 29 BOTICARIO 2021-12-26 \n", "1 C202201 2021-12-26 2022-01-23 29 BOTICARIO 2021-12-27 \n", "2 C202201 2021-12-26 2022-01-23 29 BOTICARIO 2021-12-28 \n", "3 C202201 2021-12-26 2022-01-23 29 BOTICARIO 2021-12-29 \n", "4 C202201 2021-12-26 2022-01-23 29 BOTICARIO 2021-12-30 \n", "... ... ... ... ... ... ... \n", "2527 C202517 2025-12-01 2025-12-25 25 BOTICARIO 2025-12-21 \n", "2528 C202517 2025-12-01 2025-12-25 25 BOTICARIO 2025-12-22 \n", "2529 C202517 2025-12-01 2025-12-25 25 BOTICARIO 2025-12-23 \n", "2530 C202517 2025-12-01 2025-12-25 25 BOTICARIO 2025-12-24 \n", "2531 C202517 2025-12-01 2025-12-25 25 BOTICARIO 2025-12-25 \n", "\n", " NUM_CICLO ANO_CICLO CICLOMAIS2 \n", "0 1 C2022 C202204 \n", "1 1 C2022 C202204 \n", "2 1 C2022 C202204 \n", "3 1 C2022 C202204 \n", "4 1 C2022 C202204 \n", "... ... ... ... \n", "2527 17 C2025 C202520 \n", "2528 17 C2025 C202520 \n", "2529 17 C2025 C202520 \n", "2530 17 C2025 C202520 \n", "2531 17 C2025 C202520 \n", "\n", "[1461 rows x 9 columns]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "calendario" ] }, { "cell_type": "code", "execution_count": 4, "id": "b5eaf29c", "metadata": {}, "outputs": [], "source": [ "df_vendas = pd.read_csv(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\DADOS DOURADO\\VENDA\\COMPILADO_VENDAS.csv\")" ] }, { "cell_type": "code", "execution_count": 5, "id": "dc65ff07", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(212119, 7)" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_vendas.shape" ] }, { "cell_type": "code", "execution_count": 6, "id": "3d6f0008", "metadata": {}, "outputs": [], "source": [ "de_para = pd.read_excel(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\DADOS DOURADO\\DEPARA NOVA AQUISIÇÃO.xlsx\")\n", "\n", "de_para1 = pd.read_excel(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\DADOS DOURADO\\DEPARA NOVA AQUISIÇÃO.xlsx\")\n", "\n", "de_para = de_para[['BPCS','NOVO BPCS']]\n" ] }, { "cell_type": "code", "execution_count": 7, "id": "71dee183", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['BPCS', 'NOVO BPCS'], dtype='object')" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "de_para.columns" ] }, { "cell_type": "code", "execution_count": 8, "id": "68c3515f", "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", "
BPCSNOVO BPCS
01299324253
11300424254
21342124255
32162324258
42119524257
\n", "
" ], "text/plain": [ " BPCS NOVO BPCS\n", "0 12993 24253\n", "1 13004 24254\n", "2 13421 24255\n", "3 21623 24258\n", "4 21195 24257" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "de_para['NOVO BPCS'] = de_para['NOVO BPCS'].astype('str').replace('.0','',regex=True)\n", "de_para" ] }, { "cell_type": "code", "execution_count": 9, "id": "cbf66c75", "metadata": {}, "outputs": [], "source": [ "df_vendas = pd.merge(left=df_vendas,right=de_para,left_on='PDV',right_on='BPCS',how='inner')" ] }, { "cell_type": "code", "execution_count": 10, "id": "f13bb431", "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", "
DATA_VENDAPDVDESC PDVCódigoDescriçãoQuantidadeFaturamentoBPCSNOVO BPCS
001/04/202512993PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA1004FLORATTA DES COL MY BLUE 75ml198.991299324253
101/04/202512993PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA4539BOTIK CR FAC FIRMADOR AC/HIAL 40g V21129.051299324253
201/04/202512993PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA29046BOTICOLL CONNEX DES BDY SPR 100ml V6 PCK138.901299324253
301/04/202512993PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA47154CBEM DES ROLL S/ALUM 55ml121.981299324253
401/04/202512993PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA47411ARBO NECESS LONA157.261299324253
\n", "
" ], "text/plain": [ " DATA_VENDA PDV DESC PDV Código \\\n", "0 01/04/2025 12993 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA 1004 \n", "1 01/04/2025 12993 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA 4539 \n", "2 01/04/2025 12993 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA 29046 \n", "3 01/04/2025 12993 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA 47154 \n", "4 01/04/2025 12993 PERFUMARIA E COSMETICOS QUEIROZ DOURADO LTDA 47411 \n", "\n", " Descrição Quantidade Faturamento BPCS \\\n", "0 FLORATTA DES COL MY BLUE 75ml 1 98.99 12993 \n", "1 BOTIK CR FAC FIRMADOR AC/HIAL 40g V2 1 129.05 12993 \n", "2 BOTICOLL CONNEX DES BDY SPR 100ml V6 PCK 1 38.90 12993 \n", "3 CBEM DES ROLL S/ALUM 55ml 1 21.98 12993 \n", "4 ARBO NECESS LONA 1 57.26 12993 \n", "\n", " NOVO BPCS \n", "0 24253 \n", "1 24253 \n", "2 24253 \n", "3 24253 \n", "4 24253 " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_vendas.head()" ] }, { "cell_type": "code", "execution_count": 11, "id": "062adae4", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_19528\\2715880413.py:10: DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.\n", " df_draft = pd.concat([pd.read_csv(file) for file in csv_files], ignore_index=True)\n" ] }, { "data": { "text/plain": [ "(114430, 47)" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Caminho onde estão as subpastas com os arquivos CSV\n", "\n", "# Set the path to the folder containing CSV files\n", "folder_path = r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\DADOS DOURADO\\DRAFT_PDVS_SEM\" # arquivo dos drafts\n", "\n", "# Pattern to match all CSV files\n", "csv_files = glob.glob(os.path.join(folder_path, '*.csv'))\n", "\n", "# Read and concat all CSVs\n", "df_draft = pd.concat([pd.read_csv(file) for file in csv_files], ignore_index=True)\n", "\n", "#df_draft = pd.read_csv(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\DADOS DOURADO\\DRAFT_PDVS_SEM\\0_26052025_0904_COMPILADO.csv\")\n", "\n", "df_draft['match'] = 1 \n", "\n", "df_draft.shape\n" ] }, { "cell_type": "code", "execution_count": 12, "id": "d4d9a11e", "metadata": {}, "outputs": [], "source": [ "# Caminho onde estão as subpastas com os arquivos CSV\n", "pasta_entrada = r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\DADOS DOURADO\\ESTOQUE\\ESTOQUE 06.03\"\n", "\n", "# Lista todas as subpastas dentro de \"ESTOQUE\"\n", "subpastas = [os.path.join(pasta_entrada, d) for d in os.listdir(pasta_entrada) if os.path.isdir(os.path.join(pasta_entrada, d))]\n", "\n", "df_list = []\n", "\n", "# Percorre todas as subpastas\n", "for subpasta in subpastas:\n", " arquivos = [f for f in os.listdir(subpasta) if f.endswith(\".csv\")]\n", " nome_pasta = os.path.basename(subpasta) # Obtém o nome da pasta\n", "\n", " for arquivo in arquivos:\n", " caminho_arquivo = os.path.join(subpasta, arquivo)\n", " try:\n", " df = pd.read_csv(caminho_arquivo, encoding=\"utf-8\", low_memory=False) # Melhor para grandes volumes de dados\n", " df[\"Arquivo_Origem\"] = arquivo # Adiciona o nome do arquivo de origem\n", " df[\"Pasta_Origem\"] = nome_pasta # Adiciona o nome da pasta de origem\n", " df_list.append(df)\n", " except Exception as e:\n", " print(f\"Erro ao ler o arquivo {arquivo}: {e}\")\n", "\n", "if df_list:\n", " df_estoque = pd.concat(df_list, ignore_index=True)\n", "\n", "df_estoque['PDV'] = df_estoque['PDV'].astype(str)\n", "\n", "df_estoque['SKU_FINAL'] = np.where(df_estoque['SKU_PARA'] == \"-\", df_estoque['SKU'], df_estoque['SKU_PARA'])\n", "\n", "df_estoque['SKU_FINAL']=df_estoque['SKU_FINAL'].astype(str)\n" ] }, { "cell_type": "code", "execution_count": 13, "id": "db5de072", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['Arquivo_Origem', 'SKU_FINAL'], dtype='object')" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_estoque = df_estoque[['Arquivo_Origem','SKU_FINAL']]\n", "\n", "df_estoque.columns" ] }, { "cell_type": "code", "execution_count": 14, "id": "28a5666c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(114430, 47)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.shape" ] }, { "cell_type": "code", "execution_count": 15, "id": "879e2cae", "metadata": {}, "outputs": [], "source": [ "df_draft['SKU'] = df_draft['SKU'].astype('str')\n", "\n", "df_estoque['SKU_FINAL'] = df_estoque['SKU_FINAL'].astype('str')\n", "\n", "df_draft = pd.merge(left=df_draft,right=df_estoque,right_on='SKU_FINAL',left_on='SKU',how='inner')" ] }, { "cell_type": "code", "execution_count": 16, "id": "292be50d", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['PDV', 'pdv como texto', 'CANAL', 'DESCRIÇÃO', 'PDV DESC', 'REGIÃO',\n", " 'ESTADO', 'CIDADE', 'UF', 'MARCA', 'ANALISTA', 'GESTÃO', 'SUPERVISOR',\n", " 'STATUS'],\n", " dtype='object')" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pdv = pd.read_excel(r'C:\\Users\\joao.herculano\\Documents\\PDV_ATT.xlsx')\n", "\n", "df_pdv.columns" ] }, { "cell_type": "code", "execution_count": 17, "id": "760bf109", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(8472317, 49)" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.shape" ] }, { "cell_type": "code", "execution_count": 18, "id": "2a361436", "metadata": {}, "outputs": [], "source": [ "df_draft = pd.merge(left=df_draft,right=df_pdv[['PDV','CANAL']],on='PDV', how='inner')\n", "\n", "df_draft = df_draft.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 19, "id": "d4ec2073", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(112979, 50)" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.shape" ] }, { "cell_type": "code", "execution_count": 20, "id": "51bc5c7c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['PDV', 'Classe', 'SKU', 'Descrição', 'Categoria', 'Subcategoria',\n", " 'Lançamento', 'Desativação', 'Histórico de Vendas do Ciclo 202408',\n", " 'Histórico de Vendas do Ciclo 202409',\n", " 'Histórico de Vendas do Ciclo 202410',\n", " 'Histórico de Vendas do Ciclo 202411',\n", " 'Histórico de Vendas do Ciclo 202412',\n", " 'Histórico de Vendas do Ciclo 202413',\n", " 'Histórico de Vendas do Ciclo 202414',\n", " 'Histórico de Vendas do Ciclo 202415',\n", " 'Histórico de Vendas do Ciclo 202416',\n", " 'Histórico de Vendas do Ciclo 202417',\n", " 'Histórico de Vendas do Ciclo 202501',\n", " 'Histórico de Vendas do Ciclo 202502',\n", " 'Histórico de Vendas do Ciclo 202503',\n", " 'Histórico de Vendas do Ciclo 202504',\n", " 'Histórico de Vendas do Ciclo 202505',\n", " 'Histórico de Vendas do Ciclo 202506',\n", " 'Histórico de Vendas do Ciclo 202507',\n", " 'Histórico de Vendas do Ciclo Atual', 'Dias sem venda',\n", " 'Projeção Próximo Ciclo', 'Projeção Próximo Ciclo + 1',\n", " 'Promoção Próximo Ciclo', 'Promoção Próximo Ciclo + 1', 'Estoque Atual',\n", " 'Estoque em Transito', 'Pedido Pendente',\n", " 'Compra inteligente semanal/Sugestão de compra',\n", " 'Compra inteligente Próximo Ciclo',\n", " 'Compra inteligente Próximo Ciclo + 1', 'Item Desativado',\n", " 'Data Prevista Regularização', 'Carteira Bloqueada Para Novos Pedidos',\n", " 'Planograma', 'Quantidade por caixa', 'Preço Sell In', 'Quantidade',\n", " 'Item analisado', 'Histórico de Vendas do Ciclo 202407', 'match',\n", " 'Arquivo_Origem', 'SKU_FINAL', 'CANAL'],\n", " dtype='object')" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.columns" ] }, { "cell_type": "code", "execution_count": 21, "id": "611478ea", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(112979, 50)" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.shape" ] }, { "cell_type": "code", "execution_count": 22, "id": "882f7ff4", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Arquivo_Origem\n", "BOT.csv 96151\n", "QDB.csv 9282\n", "Name: count, dtype: int64" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft = df_draft[df_draft['Arquivo_Origem']!='EUD.csv']\n", "\n", "df_draft['Arquivo_Origem'].value_counts()" ] }, { "cell_type": "code", "execution_count": 23, "id": "92a7aec9", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "DATA_VENDA datetime64[ns]\n", "PDV int64\n", "DESC PDV object\n", "Código int64\n", "Descrição object\n", "Quantidade int64\n", "Faturamento float64\n", "BPCS int64\n", "NOVO BPCS object\n", "dtype: object" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_vendas['DATA_VENDA'] = pd.to_datetime(df_vendas['DATA_VENDA'], format='%d/%m/%Y')\n", "\n", "df_vendas.dtypes" ] }, { "cell_type": "code", "execution_count": 24, "id": "caf7ed82", "metadata": {}, "outputs": [], "source": [ "calendario = calendario[['Date','Ciclo']]" ] }, { "cell_type": "code", "execution_count": 25, "id": "0400f918", "metadata": {}, "outputs": [], "source": [ "df_vendas = pd.merge(left=df_vendas , right= calendario, left_on= 'DATA_VENDA', right_on='Date' , how='inner')" ] }, { "cell_type": "code", "execution_count": 26, "id": "74a160d6", "metadata": {}, "outputs": [], "source": [ "# Primeiro, agrupa somando a quantidade por PDV, Código, Descrição e Ciclo\n", "df_agrupado = df_vendas.groupby(['NOVO BPCS', 'Código', 'Descrição', 'Ciclo'])['Quantidade'].sum().reset_index()\n", "\n", "# Faz o pivot para transformar os ciclos em colunas\n", "df_pivot = df_agrupado.pivot(index=['NOVO BPCS', 'Código', 'Descrição'], columns='Ciclo', values='Quantidade')\n", "\n", "# Preenche valores ausentes com 0 e reseta o índice\n", "df_pivot = df_pivot.fillna(0).reset_index()\n" ] }, { "cell_type": "code", "execution_count": 27, "id": "d54b531e", "metadata": {}, "outputs": [], "source": [ "estoque_att = pd.read_excel(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\DADOS DOURADO\\INVENTARIO\\ESTOQUE_ATUAL_INVENTARIO.xlsx\")\n" ] }, { "cell_type": "code", "execution_count": 28, "id": "0e6b3762", "metadata": {}, "outputs": [], "source": [ "estoque_att['NOVO PDV'] = estoque_att['NOVO PDV'].astype('str')" ] }, { "cell_type": "code", "execution_count": 29, "id": "5ef29b73", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(10338, 27)" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot.shape" ] }, { "cell_type": "code", "execution_count": 30, "id": "0097c617", "metadata": {}, "outputs": [], "source": [ "\n", "df_pivot = pd.merge(left=df_pivot , right= estoque_att, left_on= ['NOVO BPCS', 'Código'], right_on = ['NOVO PDV', 'SKU'], how='inner')" ] }, { "cell_type": "code", "execution_count": 31, "id": "fd8f5795", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(5087, 32)" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot.shape" ] }, { "cell_type": "code", "execution_count": 32, "id": "9a4c1f17", "metadata": {}, "outputs": [], "source": [ "df_pivot = df_pivot.drop(columns=['NOVO PDV', 'SKU', 'Descrição_y'])\n", "\n", "df_pivot = df_pivot.rename(columns={'Descrição_x':'Descrição'})" ] }, { "cell_type": "code", "execution_count": 33, "id": "edb2a039", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['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": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot.columns[4:27]" ] }, { "cell_type": "code", "execution_count": 34, "id": "b8cab623", "metadata": {}, "outputs": [], "source": [ "vendas_todos_historicos = df_pivot.columns[4:27]\n", "\n", "df_pivot['MEDIANA DO HISTÓRICO'] = df_pivot[vendas_todos_historicos].median(axis=1)\n", "\n", "df_pivot['MEDIA DO HISTÓRICO'] = df_pivot[vendas_todos_historicos].mean(axis=1)\n", "\n", "df_pivot['PICO DO HISTÓRICO'] = df_pivot[vendas_todos_historicos].max(axis=1)" ] }, { "cell_type": "code", "execution_count": 35, "id": "2c535ba0", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['NOVO BPCS', 'Código', 'Descrição', 'C202401', 'C202402', 'C202403',\n", " 'C202404', 'C202405', 'C202406', 'C202407', 'C202408', 'C202409',\n", " 'C202410', 'C202411', 'C202412', 'C202413', 'C202414', 'C202415',\n", " 'C202416', 'C202417', 'C202501', 'C202502', 'C202503', 'C202504',\n", " 'C202505', 'C202506', 'C202507', 'ESTOQUE', 'CURVA',\n", " 'MEDIANA DO HISTÓRICO', 'MEDIA DO HISTÓRICO', 'PICO DO HISTÓRICO'],\n", " dtype='object')" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot.columns" ] }, { "cell_type": "code", "execution_count": 36, "id": "34ed0fc1", "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", "
NOVO BPCSCódigoDescriçãoC202401C202402C202403C202404C202405C202406C202407...C202504C202505C202506C202507ESTOQUECURVAMEDIANA DO HISTÓRICOMEDIA DO HISTÓRICOPICO DO HISTÓRICOcobertura
0242531004FLORATTA DES COL MY BLUE 75ml1.012.02.03.01.02.02.0...3.03.04.00.03A2.03.47826127.018.0
1242531078MATCH SRUM CAP POS QUIMICA 50ml0.01.00.00.00.02.00.0...0.01.00.00.02C0.00.2173912.0184.0
2242531296PMPCK THE BLEND DES ANTIT AER 2x75g2.03.00.03.00.03.00.0...6.00.00.01.05C1.02.3913048.042.0
3242531317PMPCK MALBEC DES ANTIT AER 2x75g2.010.08.07.08.04.010.0...0.00.08.04.02B8.06.39130412.07.0
4242531428NSPA LOC ANTIOX DES HID CPO M/RUBY 400ml2.04.01.012.01.00.00.0...0.00.03.00.05C0.01.39130412.072.0
\n", "

5 rows × 33 columns

\n", "
" ], "text/plain": [ " NOVO BPCS Código Descrição C202401 \\\n", "0 24253 1004 FLORATTA DES COL MY BLUE 75ml 1.0 \n", "1 24253 1078 MATCH SRUM CAP POS QUIMICA 50ml 0.0 \n", "2 24253 1296 PMPCK THE BLEND DES ANTIT AER 2x75g 2.0 \n", "3 24253 1317 PMPCK MALBEC DES ANTIT AER 2x75g 2.0 \n", "4 24253 1428 NSPA LOC ANTIOX DES HID CPO M/RUBY 400ml 2.0 \n", "\n", " C202402 C202403 C202404 C202405 C202406 C202407 ... C202504 \\\n", "0 12.0 2.0 3.0 1.0 2.0 2.0 ... 3.0 \n", "1 1.0 0.0 0.0 0.0 2.0 0.0 ... 0.0 \n", "2 3.0 0.0 3.0 0.0 3.0 0.0 ... 6.0 \n", "3 10.0 8.0 7.0 8.0 4.0 10.0 ... 0.0 \n", "4 4.0 1.0 12.0 1.0 0.0 0.0 ... 0.0 \n", "\n", " C202505 C202506 C202507 ESTOQUE CURVA MEDIANA DO HISTÓRICO \\\n", "0 3.0 4.0 0.0 3 A 2.0 \n", "1 1.0 0.0 0.0 2 C 0.0 \n", "2 0.0 0.0 1.0 5 C 1.0 \n", "3 0.0 8.0 4.0 2 B 8.0 \n", "4 0.0 3.0 0.0 5 C 0.0 \n", "\n", " MEDIA DO HISTÓRICO PICO DO HISTÓRICO cobertura \n", "0 3.478261 27.0 18.0 \n", "1 0.217391 2.0 184.0 \n", "2 2.391304 8.0 42.0 \n", "3 6.391304 12.0 7.0 \n", "4 1.391304 12.0 72.0 \n", "\n", "[5 rows x 33 columns]" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot['cobertura'] = np.ceil(df_pivot['ESTOQUE']/((df_pivot['MEDIA DO HISTÓRICO'])/20))\n", "\n", "df_pivot.head()" ] }, { "cell_type": "code", "execution_count": 37, "id": "e429a3b9", "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", "
NOVO BPCSCódigoDescriçãoC202401C202402C202403C202404C202405C202406C202407...C202506C202507ESTOQUECURVAMEDIANA DO HISTÓRICOMEDIA DO HISTÓRICOPICO DO HISTÓRICOcoberturaPV GINSENG CICLO ATUALPV GINSENG CICLO +1
0242531004FLORATTA DES COL MY BLUE 75ml1.012.02.03.01.02.02.0...4.00.03A2.03.47826127.018.03.03.0
1242531078MATCH SRUM CAP POS QUIMICA 50ml0.01.00.00.00.02.00.0...0.00.02C0.00.2173912.0184.02.00.0
2242531296PMPCK THE BLEND DES ANTIT AER 2x75g2.03.00.03.00.03.00.0...0.01.05C1.02.3913048.042.05.02.0
3242531317PMPCK MALBEC DES ANTIT AER 2x75g2.010.08.07.08.04.010.0...8.04.02B8.06.39130412.07.010.03.0
4242531428NSPA LOC ANTIOX DES HID CPO M/RUBY 400ml2.04.01.012.01.00.00.0...3.00.05C0.01.39130412.072.03.06.0
\n", "

5 rows × 35 columns

\n", "
" ], "text/plain": [ " NOVO BPCS Código Descrição C202401 \\\n", "0 24253 1004 FLORATTA DES COL MY BLUE 75ml 1.0 \n", "1 24253 1078 MATCH SRUM CAP POS QUIMICA 50ml 0.0 \n", "2 24253 1296 PMPCK THE BLEND DES ANTIT AER 2x75g 2.0 \n", "3 24253 1317 PMPCK MALBEC DES ANTIT AER 2x75g 2.0 \n", "4 24253 1428 NSPA LOC ANTIOX DES HID CPO M/RUBY 400ml 2.0 \n", "\n", " C202402 C202403 C202404 C202405 C202406 C202407 ... C202506 \\\n", "0 12.0 2.0 3.0 1.0 2.0 2.0 ... 4.0 \n", "1 1.0 0.0 0.0 0.0 2.0 0.0 ... 0.0 \n", "2 3.0 0.0 3.0 0.0 3.0 0.0 ... 0.0 \n", "3 10.0 8.0 7.0 8.0 4.0 10.0 ... 8.0 \n", "4 4.0 1.0 12.0 1.0 0.0 0.0 ... 3.0 \n", "\n", " C202507 ESTOQUE CURVA MEDIANA DO HISTÓRICO MEDIA DO HISTÓRICO \\\n", "0 0.0 3 A 2.0 3.478261 \n", "1 0.0 2 C 0.0 0.217391 \n", "2 1.0 5 C 1.0 2.391304 \n", "3 4.0 2 B 8.0 6.391304 \n", "4 0.0 5 C 0.0 1.391304 \n", "\n", " PICO DO HISTÓRICO cobertura PV GINSENG CICLO ATUAL PV GINSENG CICLO +1 \n", "0 27.0 18.0 3.0 3.0 \n", "1 2.0 184.0 2.0 0.0 \n", "2 8.0 42.0 5.0 2.0 \n", "3 12.0 7.0 10.0 3.0 \n", "4 12.0 72.0 3.0 6.0 \n", "\n", "[5 rows x 35 columns]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot['PV GINSENG CICLO ATUAL'] = np.where(np.ceil(df_pivot['C202408']*1.2) < 2, np.ceil(df_pivot['MEDIANA DO HISTÓRICO'] * 1.2),np.ceil(df_pivot['C202408']*1.2))\n", "\n", "df_pivot['PV GINSENG CICLO +1'] = np.where(np.ceil(df_pivot['C202409']*1.2) < 2, np.ceil(df_pivot['MEDIANA DO HISTÓRICO'] * 1.2),np.ceil(df_pivot['C202409']*1.2))\n", "\n", "df_pivot.head()" ] }, { "cell_type": "code", "execution_count": 38, "id": "4483fe0b", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "NOVO BPCS\n", "24257 1664\n", "24255 1512\n", "24253 755\n", "24254 683\n", "24258 473\n", "Name: count, dtype: int64" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot['NOVO BPCS'].value_counts()" ] }, { "cell_type": "code", "execution_count": 39, "id": "94d82b0b", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(5087, 35)" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot.shape" ] }, { "cell_type": "code", "execution_count": 40, "id": "918b3c6c", "metadata": {}, "outputs": [], "source": [ "de_para1['NOVO BPCS'] = de_para1['NOVO BPCS'].astype('str').replace('.0','',regex=True)\n", "\n", "df_pivot = pd.merge(left=df_pivot, right=de_para1[['NOVO BPCS','TIPO']],on='NOVO BPCS',how='left')" ] }, { "cell_type": "code", "execution_count": 41, "id": "5dc95190", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(5087, 36)" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot.shape" ] }, { "cell_type": "code", "execution_count": 42, "id": "d750e999", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "NOVO BPCS\n", "24253 1\n", "24254 1\n", "24255 1\n", "24258 1\n", "24257 1\n", "Name: count, dtype: int64" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "de_para1['NOVO BPCS'].value_counts()" ] }, { "cell_type": "code", "execution_count": 43, "id": "180b6609", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['PDV', 'Classe', 'SKU', 'Descrição', 'Categoria', 'Subcategoria',\n", " 'Lançamento', 'Desativação', 'Histórico de Vendas do Ciclo 202408',\n", " 'Histórico de Vendas do Ciclo 202409',\n", " 'Histórico de Vendas do Ciclo 202410',\n", " 'Histórico de Vendas do Ciclo 202411',\n", " 'Histórico de Vendas do Ciclo 202412',\n", " 'Histórico de Vendas do Ciclo 202413',\n", " 'Histórico de Vendas do Ciclo 202414',\n", " 'Histórico de Vendas do Ciclo 202415',\n", " 'Histórico de Vendas do Ciclo 202416',\n", " 'Histórico de Vendas do Ciclo 202417',\n", " 'Histórico de Vendas do Ciclo 202501',\n", " 'Histórico de Vendas do Ciclo 202502',\n", " 'Histórico de Vendas do Ciclo 202503',\n", " 'Histórico de Vendas do Ciclo 202504',\n", " 'Histórico de Vendas do Ciclo 202505',\n", " 'Histórico de Vendas do Ciclo 202506',\n", " 'Histórico de Vendas do Ciclo 202507',\n", " 'Histórico de Vendas do Ciclo Atual', 'Dias sem venda',\n", " 'Projeção Próximo Ciclo', 'Projeção Próximo Ciclo + 1',\n", " 'Promoção Próximo Ciclo', 'Promoção Próximo Ciclo + 1', 'Estoque Atual',\n", " 'Estoque em Transito', 'Pedido Pendente',\n", " 'Compra inteligente semanal/Sugestão de compra',\n", " 'Compra inteligente Próximo Ciclo',\n", " 'Compra inteligente Próximo Ciclo + 1', 'Item Desativado',\n", " 'Data Prevista Regularização', 'Carteira Bloqueada Para Novos Pedidos',\n", " 'Planograma', 'Quantidade por caixa', 'Preço Sell In', 'Quantidade',\n", " 'Item analisado', 'Histórico de Vendas do Ciclo 202407', 'match',\n", " 'Arquivo_Origem', 'SKU_FINAL', 'CANAL'],\n", " dtype='object')" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_draft.columns" ] }, { "cell_type": "code", "execution_count": 44, "id": "cf39cbe9", "metadata": {}, "outputs": [], "source": [ "df_pivot['NOVO BPCS'] = df_pivot['NOVO BPCS'].astype('str')\n", "df_draft['PDV'] = df_draft['PDV'].astype('str')\n", "\n", "df_pivot['Código'] = df_pivot['Código'].astype('str')\n", "df_draft['SKU'] = df_draft['SKU'].astype('str')\n", "\n", "df_pivot = pd.merge(left=df_pivot,right=df_draft[['CANAL', 'SKU','Promoção Próximo Ciclo', 'Promoção Próximo Ciclo + 1']]\n", " ,left_on=['TIPO', 'Código'],right_on=['CANAL', 'SKU'],how='left')" ] }, { "cell_type": "code", "execution_count": 45, "id": "f279d39d", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(18140, 40)" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot.shape" ] }, { "cell_type": "markdown", "id": "22255f9a", "metadata": {}, "source": [ "ERRO ESTÁ AQUI EM CIMA /\\" ] }, { "cell_type": "code", "execution_count": 46, "id": "08c2d03a", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(5087, 40)" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot = df_pivot.drop_duplicates()\n", "\n", "df_pivot.shape" ] }, { "cell_type": "code", "execution_count": 47, "id": "b1eed19f", "metadata": {}, "outputs": [], "source": [ "df_pivot = df_pivot.drop(columns=['CANAL','SKU'])" ] }, { "cell_type": "code", "execution_count": 48, "id": "83ebfa4b", "metadata": {}, "outputs": [], "source": [ "df_pivot = df_pivot.drop_duplicates()" ] }, { "cell_type": "code", "execution_count": 49, "id": "00a6cde6", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "TIPO\n", "LOJA 1911\n", "HÍBRIDA 1664\n", "VD 1512\n", "Name: count, dtype: int64" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_pivot['TIPO'].value_counts()" ] }, { "cell_type": "code", "execution_count": 50, "id": "874e2aec", "metadata": {}, "outputs": [], "source": [ "df_pivot['SUGESTAO ANALISTA'] = \"\"" ] }, { "cell_type": "code", "execution_count": 51, "id": "6a283caf", "metadata": {}, "outputs": [], "source": [ "# lista de colunas na ordem desejada\n", "colunas_ordenadas = [\n", " 'NOVO BPCS', 'TIPO', 'Código', 'Descrição', 'Promoção Próximo Ciclo', 'Promoção Próximo Ciclo + 1',\n", " 'C202401', 'C202402', 'C202403', 'C202404', 'C202405', 'C202406', 'C202407', 'C202408', 'C202409',\n", " 'C202410', 'C202411', 'C202412', 'C202413', 'C202414', 'C202415', 'C202416', 'C202417',\n", " 'C202501', 'C202502', 'C202503', 'C202504', 'C202505', 'C202506', 'C202507',\n", " 'ESTOQUE', 'CURVA', 'MEDIANA DO HISTÓRICO', 'MEDIA DO HISTÓRICO', 'PICO DO HISTÓRICO',\n", " 'cobertura', 'PV GINSENG CICLO ATUAL','PV GINSENG CICLO +1', 'SUGESTAO ANALISTA'\n", "]\n", "\n", "# reordenar colunas\n", "df_pivot = df_pivot[colunas_ordenadas]\n" ] }, { "cell_type": "code", "execution_count": 52, "id": "229d180f", "metadata": {}, "outputs": [], "source": [ "df_pivot.to_excel(r'C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\DADOS DOURADO\\Sugest_douradov2.xlsx',index=False)" ] }, { "cell_type": "code", "execution_count": null, "id": "26711ec3", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.2" } }, "nbformat": 4, "nbformat_minor": 5 }