2578 lines
77 KiB
Plaintext
2578 lines
77 KiB
Plaintext
{
|
||
"cells": [
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 1,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"name": "stderr",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_10928\\419701015.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",
|
||
" df0 = pd.read_sql(query, conn)\n"
|
||
]
|
||
}
|
||
],
|
||
"source": [
|
||
"import pyodbc\n",
|
||
"import configparser\n",
|
||
"import pandas as pd\n",
|
||
"import numpy as np \n",
|
||
"from datetime import datetime, time\n",
|
||
"\n",
|
||
"\n",
|
||
"config = configparser.ConfigParser()\n",
|
||
"config.read(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\CODIGOS\\relatório_improdutivo\\credenciais.ini\")\n",
|
||
"\n",
|
||
"# Conexão com o banco\n",
|
||
"conn = pyodbc.connect(\n",
|
||
" f\"DRIVER={{SQL Server}};\"\n",
|
||
" f\"SERVER={config['banco']['host']},1433;\"\n",
|
||
" f\"DATABASE=GINSENG;\"\n",
|
||
" f\"UID={config['banco']['user']};\"\n",
|
||
" f\"PWD={config['banco']['password']}\")\n",
|
||
"\n",
|
||
"#gerar query\n",
|
||
"query = '''\n",
|
||
"SELECT \n",
|
||
" e.pdv,\n",
|
||
" e.SKU,\n",
|
||
" COALESCE(e.SKU_PARA, e.SKU) AS SKU_FINAL,\n",
|
||
" coalesce(e.[ESTOQUE ATUAL],d.stock_actual) as 'ESTOQUE ATUAL',\n",
|
||
" coalesce(e.[ESTOQUE EM TRANSITO],d.stock_intransit) as 'ESTOQUE EM TRANSITO' ,\n",
|
||
" coalesce(e.[PEDIDO PENDENTE],d.pendingorder) as 'PEDIDO PENDENTE' , \n",
|
||
" d.salescurve AS Curva,\n",
|
||
" e.CATEGORIA,\n",
|
||
" d.dayswithoutsales AS DiasSemVenda,\n",
|
||
" d.nextcycleprojection,\n",
|
||
" d.secondtonextcycleprojection\n",
|
||
"FROM \n",
|
||
" estoque_mar e \n",
|
||
"inner JOIN draft d\n",
|
||
" ON d.loja_id = e.PDV \n",
|
||
" AND d.code = COALESCE(e.SKU_PARA, e.SKU)\n",
|
||
"'''\n",
|
||
"df0 = pd.read_sql(query, conn)\n",
|
||
"\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 2,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"pdv\n",
|
||
"20997 3772\n",
|
||
"20969 3723\n",
|
||
"20992 3716\n",
|
||
"20995 3304\n",
|
||
"20998 3296\n",
|
||
" ... \n",
|
||
"24268 935\n",
|
||
"24258 804\n",
|
||
"23156 704\n",
|
||
"910173 495\n",
|
||
"910291 495\n",
|
||
"Name: count, Length: 80, dtype: int64"
|
||
]
|
||
},
|
||
"execution_count": 2,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df0['pdv'].value_counts()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 5,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"name": "stderr",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"C:\\Users\\joao.herculano\\AppData\\Local\\Temp\\ipykernel_10928\\1692316838.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",
|
||
" dfv0 = pd.read_sql(query_vendas, conn)\n"
|
||
]
|
||
}
|
||
],
|
||
"source": [
|
||
"query_vendas = '''\n",
|
||
"SELECT \n",
|
||
"\tB.PDV, \n",
|
||
"\tB.SKU,\n",
|
||
"\tB.DESCRICAO,\n",
|
||
"\tSUM(CAST(b.VENDAS AS DECIMAL(18,2))) AS VENDAS_CICLO,\n",
|
||
"\tC.Ciclo\n",
|
||
"FROM base_vendas_bi b\n",
|
||
"INNER JOIN ciclos_data_2025 c \n",
|
||
" ON CAST(b.[DATA] AS DATE) = CONVERT(DATE, c.[Date], 103) AND C.MARCA = 'BOT'\n",
|
||
"LEFT JOIN (\n",
|
||
" SELECT SKU, MAX(ORIGEM) AS ORIGEM\n",
|
||
" FROM estoque_mar\n",
|
||
" GROUP BY SKU\n",
|
||
") e ON b.SKU = e.SKU\n",
|
||
"WHERE b.[DATA] > '2024-07-28'\n",
|
||
"GROUP BY\n",
|
||
"B.PDV, \n",
|
||
"\tB.SKU,\n",
|
||
"\tB.DESCRICAO,\n",
|
||
"\tC.Ciclo,\n",
|
||
"\tE.ORIGEM \n",
|
||
"'''\n",
|
||
"\n",
|
||
"dfv0 = pd.read_sql(query_vendas, conn)\n",
|
||
"\n",
|
||
"conn.close()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 6,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df=df0"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 10,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/html": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>PDV</th>\n",
|
||
" <th>SKU</th>\n",
|
||
" <th>DESCRICAO</th>\n",
|
||
" <th>C202411</th>\n",
|
||
" <th>C202412</th>\n",
|
||
" <th>C202413</th>\n",
|
||
" <th>C202414</th>\n",
|
||
" <th>C202415</th>\n",
|
||
" <th>C202416</th>\n",
|
||
" <th>C202417</th>\n",
|
||
" <th>...</th>\n",
|
||
" <th>C202504</th>\n",
|
||
" <th>C202505</th>\n",
|
||
" <th>C202506</th>\n",
|
||
" <th>C202507</th>\n",
|
||
" <th>C202508</th>\n",
|
||
" <th>C202509</th>\n",
|
||
" <th>C202510</th>\n",
|
||
" <th>C202511</th>\n",
|
||
" <th>C202512</th>\n",
|
||
" <th>C202513</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>11201</td>\n",
|
||
" <td>1004</td>\n",
|
||
" <td>FLORATTA DES COL MY BLUE 75ml</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>11201</td>\n",
|
||
" <td>1005</td>\n",
|
||
" <td>DEM FLORATTA DES COL MY BLUE 4ml</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>11201</td>\n",
|
||
" <td>1078</td>\n",
|
||
" <td>MATCH SRUM CAP POS QUIMICA 50ml</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>11201</td>\n",
|
||
" <td>1296</td>\n",
|
||
" <td>PMPCK THE BLEND DES ANTIT AER 2x75g</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>11201</td>\n",
|
||
" <td>1302</td>\n",
|
||
" <td>PMPCK LILY DES ANTIT AER 2x75g</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"<p>5 rows × 23 columns</p>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" PDV SKU DESCRICAO C202411 C202412 \\\n",
|
||
"0 11201 1004 FLORATTA DES COL MY BLUE 75ml 0.0 0.0 \n",
|
||
"1 11201 1005 DEM FLORATTA DES COL MY BLUE 4ml 0.0 0.0 \n",
|
||
"2 11201 1078 MATCH SRUM CAP POS QUIMICA 50ml 0.0 0.0 \n",
|
||
"3 11201 1296 PMPCK THE BLEND DES ANTIT AER 2x75g 0.0 0.0 \n",
|
||
"4 11201 1302 PMPCK LILY DES ANTIT AER 2x75g 0.0 0.0 \n",
|
||
"\n",
|
||
" C202413 C202414 C202415 C202416 C202417 ... C202504 C202505 \\\n",
|
||
"0 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 \n",
|
||
"1 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 \n",
|
||
"2 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 \n",
|
||
"3 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 \n",
|
||
"4 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 \n",
|
||
"\n",
|
||
" C202506 C202507 C202508 C202509 C202510 C202511 C202512 C202513 \n",
|
||
"0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n",
|
||
"1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n",
|
||
"2 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 \n",
|
||
"4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 \n",
|
||
"\n",
|
||
"[5 rows x 23 columns]"
|
||
]
|
||
},
|
||
"execution_count": 10,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"\n",
|
||
"# 1. Agrupamento\n",
|
||
"df_agrupado = dfv0.groupby(['PDV', 'SKU', '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', '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_vendas = df_pivotado.reset_index()\n",
|
||
"\n",
|
||
"# 4. (Opcional) Renomear colunas com prefixo \"Ciclo_\"\n",
|
||
"df_vendas.columns.name = None\n",
|
||
"df_vendas = df_vendas.rename(columns=lambda x: f'Ciclo_{x}' if isinstance(x, (int, str)) and str(x).isdigit() else x)\n",
|
||
"\n",
|
||
"# Resultado final\n",
|
||
"df_vendas.head()\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"# O QUE PRECISA PRA RODAR ESSE CÓDIGO:\n",
|
||
"\n",
|
||
"# Arquivo Draft\n",
|
||
"# Arquivo Estoque\n",
|
||
"# Arquivo BI preço\n",
|
||
"# Arquivo pdv\n",
|
||
"# Arquivo Calendario\n",
|
||
"# Arquivo tabela de compra\n",
|
||
"\n",
|
||
"#Atualizar o nome da marca no filtro do estoque."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"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\\SUPRIMENTOS\\BD_LANÇAMENTOS\\EUDORA\\C13\\DRAFT\" # 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",
|
||
"\n",
|
||
"#df_draft.shape\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"\n",
|
||
"\n",
|
||
"#Caminho onde estão as subpastas com os arquivos CSV\n",
|
||
"#pasta_entrada = r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\SUPRIMENTOS\\BD_LANÇAMENTOS\\EUDORA\\C13\\estoque\"\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": 11,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"name": "stderr",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"c:\\Users\\joao.herculano\\AppData\\Local\\Programs\\Python\\Python313\\Lib\\site-packages\\openpyxl\\styles\\stylesheet.py:237: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
|
||
" warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
|
||
]
|
||
}
|
||
],
|
||
"source": [
|
||
"df_bi_preco = pd.read_excel(r\"C:\\Users\\joao.herculano\\GRUPO GINSENG\\Assistência Suprimentos - 2025\\SUPRIMENTOS\\DB_PROMOÇÕES\\EUDORA\\202511\\preço BI\\TABELA DE PREÇOS (2).xlsx\")"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 12,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_pdv = pd.read_excel(r\"C:\\Users\\joao.herculano\\Documents\\PDV_ATT.xlsx\")\n",
|
||
"df_pdv = df_pdv.rename(columns={'DESCRIÇÃO':'DESCRIÇÃO PDV'})\n",
|
||
"df_pdv = df_pdv.drop(columns=['REGIÃO', 'ESTADO','CIDADE','GESTÃO', 'SUPERVISOR'])\n",
|
||
"df_pdv = df_pdv[~df_pdv['CANAL'].isin(['MTZ','LJ'])]\n",
|
||
"df_pdv = df_pdv[df_pdv['STATUS']!= \"INATIVO\"]\n",
|
||
"\n",
|
||
"df_pdv = df_pdv.drop(columns=['STATUS'])\n",
|
||
"\n",
|
||
"\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 15,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_pdv['MATCH'] = 1"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"**ALTERAR NOME DA COLUNA \"ARQUIVO_ORIGEM\" PARA UMA DAS OPÇÕES ABAIXO:**\n",
|
||
"\n",
|
||
"*BOT.csv* \n",
|
||
"\n",
|
||
"*EUD.csv*\n",
|
||
"\n",
|
||
"*QDB.csv*"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_estoque = df_estoque[df_estoque['Arquivo_Origem']== \"EUD.csv\"]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 16,
|
||
"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\")"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 17,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"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'] == \"EUDORA\"]\n",
|
||
"\n",
|
||
"calendario['CICLOMAIS2'] = calendario['ANO_CICLO'].astype(str) + (calendario['NUM_CICLO'].astype(int) + 2).astype(str).str.zfill(2)\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"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 18,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/html": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>Ciclo</th>\n",
|
||
" <th>INICIO CICLO</th>\n",
|
||
" <th>FIM CICLO</th>\n",
|
||
" <th>DURAÇÃO</th>\n",
|
||
" <th>MARCA</th>\n",
|
||
" <th>Date</th>\n",
|
||
" <th>NUM_CICLO</th>\n",
|
||
" <th>ANO_CICLO</th>\n",
|
||
" <th>CICLOMAIS2</th>\n",
|
||
" <th>dias_ate_inicio</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>2479</th>\n",
|
||
" <td>C202516</td>\n",
|
||
" <td>2025-10-29</td>\n",
|
||
" <td>2025-11-25</td>\n",
|
||
" <td>28</td>\n",
|
||
" <td>EUDORA</td>\n",
|
||
" <td>2025-10-29</td>\n",
|
||
" <td>16</td>\n",
|
||
" <td>C2025</td>\n",
|
||
" <td>C202518</td>\n",
|
||
" <td>42</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" Ciclo INICIO CICLO FIM CICLO DURAÇÃO MARCA Date NUM_CICLO \\\n",
|
||
"2479 C202516 2025-10-29 2025-11-25 28 EUDORA 2025-10-29 16 \n",
|
||
"\n",
|
||
" ANO_CICLO CICLOMAIS2 dias_ate_inicio \n",
|
||
"2479 C2025 C202518 42 "
|
||
]
|
||
},
|
||
"execution_count": 18,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"filtered_calendario"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 19,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"filtered_calendario['match'] = 1"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 20,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_pdv['UF'] = np.where(df_pdv['UF'] == 'VDC','BA',df_pdv['UF'])"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 21,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela = pd.read_excel(r\"C:\\Users\\joao.herculano\\Downloads\\Tabela-de-Promocoes_C16-1757516287454.xlsx.xlsx\",skiprows=1)\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 22,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela = df_tabela[df_tabela['Cód. Combo'].isna()]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 23,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela = df_tabela[[ 'Ciclo', 'Veiculo',\n",
|
||
" 'Tipo de Promoção', 'Estratégia de Promoção',\n",
|
||
" 'Tipo_mecanica', 'Promo Período Limitado?',\n",
|
||
" 'EAM', 'Categoria',\n",
|
||
" 'Cód. Combo', 'Código do Item',\n",
|
||
" 'Descrição do Item', 'Chamada Promocional',\n",
|
||
" 'Valor do Guia', 'Preço Promocionado',\n",
|
||
" '% de Desconto', 'RE compra por',\n",
|
||
" 'RE Vende por', 'RE lucra (R$)']]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 24,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela['Código do Item'] = df_tabela['Código do Item'].astype(str).str.replace(\".0\",\"\",regex=False)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 25,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/html": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>Ciclo</th>\n",
|
||
" <th>Veiculo</th>\n",
|
||
" <th>Tipo de Promoção</th>\n",
|
||
" <th>Estratégia de Promoção</th>\n",
|
||
" <th>Tipo_mecanica</th>\n",
|
||
" <th>Promo Período Limitado?</th>\n",
|
||
" <th>EAM</th>\n",
|
||
" <th>Categoria</th>\n",
|
||
" <th>Cód. Combo</th>\n",
|
||
" <th>Código do Item</th>\n",
|
||
" <th>Descrição do Item</th>\n",
|
||
" <th>Chamada Promocional</th>\n",
|
||
" <th>Valor do Guia</th>\n",
|
||
" <th>Preço Promocionado</th>\n",
|
||
" <th>% de Desconto</th>\n",
|
||
" <th>RE compra por</th>\n",
|
||
" <th>RE Vende por</th>\n",
|
||
" <th>RE lucra (R$)</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Escoamento</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>54511</td>\n",
|
||
" <td>NIINA SECRETS DELINEADOR CRYSTAL BRILHO ROSE G...</td>\n",
|
||
" <td>BW GUIA CF| NIINA SECRETS DELINEADOR CRYSTAL B...</td>\n",
|
||
" <td>67.99</td>\n",
|
||
" <td>24.9</td>\n",
|
||
" <td>0.634</td>\n",
|
||
" <td>17.43</td>\n",
|
||
" <td>24.9</td>\n",
|
||
" <td>7.47</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52584</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM ROSA 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM ROSA 3,2g | DE R...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Potencializa C.II</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>54506</td>\n",
|
||
" <td>NIINA SECRETS PRIMER HIDRA GLOW 15ml</td>\n",
|
||
" <td>BW | NIINA SECRETS PRIMER HIDRA GLOW 15ml | DE...</td>\n",
|
||
" <td>79.99</td>\n",
|
||
" <td>29.9</td>\n",
|
||
" <td>0.626</td>\n",
|
||
" <td>20.93</td>\n",
|
||
" <td>29.9</td>\n",
|
||
" <td>8.97</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Fora Guia</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Escoamento</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>SUPER BW - 28/11-31/11</td>\n",
|
||
" <td>Não</td>\n",
|
||
" <td>ROSTO</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52166</td>\n",
|
||
" <td>EUDORA NEO DERMO ACTIVE PRO SÉRUM DUPLA VITAMI...</td>\n",
|
||
" <td>FG CF | Super BW 28 a 31/nov EUDORA NEO DERMO ...</td>\n",
|
||
" <td>119.99</td>\n",
|
||
" <td>49.9</td>\n",
|
||
" <td>0.584</td>\n",
|
||
" <td>34.93</td>\n",
|
||
" <td>49.9</td>\n",
|
||
" <td>14.97</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" Ciclo Veiculo Tipo de Promoção Estratégia de Promoção Tipo_mecanica \\\n",
|
||
"0 202516 Guia CF BW Escoamento Desconto Direto \n",
|
||
"1 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"2 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"3 202516 Guia CF BW Potencializa C.II Desconto Direto \n",
|
||
"4 202516 Fora Guia BW Escoamento Desconto Direto \n",
|
||
"\n",
|
||
" Promo Período Limitado? EAM Categoria Cód. Combo Código do Item \\\n",
|
||
"0 NaN Sim MAQUIAGEM NaN 54511 \n",
|
||
"1 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"2 NaN Sim MAQUIAGEM NaN 52584 \n",
|
||
"3 NaN Sim MAQUIAGEM NaN 54506 \n",
|
||
"4 SUPER BW - 28/11-31/11 Não ROSTO NaN 52166 \n",
|
||
"\n",
|
||
" Descrição do Item \\\n",
|
||
"0 NIINA SECRETS DELINEADOR CRYSTAL BRILHO ROSE G... \n",
|
||
"1 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"2 NIINA SECRETS HIDRA BALM ROSA 3,2g \n",
|
||
"3 NIINA SECRETS PRIMER HIDRA GLOW 15ml \n",
|
||
"4 EUDORA NEO DERMO ACTIVE PRO SÉRUM DUPLA VITAMI... \n",
|
||
"\n",
|
||
" Chamada Promocional Valor do Guia \\\n",
|
||
"0 BW GUIA CF| NIINA SECRETS DELINEADOR CRYSTAL B... 67.99 \n",
|
||
"1 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"2 BW | NIINA SECRETS HIDRA BALM ROSA 3,2g | DE R... 39.99 \n",
|
||
"3 BW | NIINA SECRETS PRIMER HIDRA GLOW 15ml | DE... 79.99 \n",
|
||
"4 FG CF | Super BW 28 a 31/nov EUDORA NEO DERMO ... 119.99 \n",
|
||
"\n",
|
||
" Preço Promocionado % de Desconto RE compra por RE Vende por \\\n",
|
||
"0 24.9 0.634 17.43 24.9 \n",
|
||
"1 14.9 0.627 10.43 14.9 \n",
|
||
"2 14.9 0.627 10.43 14.9 \n",
|
||
"3 29.9 0.626 20.93 29.9 \n",
|
||
"4 49.9 0.584 34.93 49.9 \n",
|
||
"\n",
|
||
" RE lucra (R$) \n",
|
||
"0 7.47 \n",
|
||
"1 4.47 \n",
|
||
"2 4.47 \n",
|
||
"3 8.97 \n",
|
||
"4 14.97 "
|
||
]
|
||
},
|
||
"execution_count": 25,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_tabela.head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 26,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/html": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>Ciclo</th>\n",
|
||
" <th>Veiculo</th>\n",
|
||
" <th>Tipo de Promoção</th>\n",
|
||
" <th>Estratégia de Promoção</th>\n",
|
||
" <th>Tipo_mecanica</th>\n",
|
||
" <th>Promo Período Limitado?</th>\n",
|
||
" <th>EAM</th>\n",
|
||
" <th>Categoria</th>\n",
|
||
" <th>Cód. Combo</th>\n",
|
||
" <th>Código do Item</th>\n",
|
||
" <th>Descrição do Item</th>\n",
|
||
" <th>Chamada Promocional</th>\n",
|
||
" <th>Valor do Guia</th>\n",
|
||
" <th>Preço Promocionado</th>\n",
|
||
" <th>% de Desconto</th>\n",
|
||
" <th>RE compra por</th>\n",
|
||
" <th>RE Vende por</th>\n",
|
||
" <th>RE lucra (R$)</th>\n",
|
||
" <th>SKU</th>\n",
|
||
" <th>SKU_FINAL</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>52583</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>52583</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>52583</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>52583</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>52583</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" Ciclo Veiculo Tipo de Promoção Estratégia de Promoção Tipo_mecanica \\\n",
|
||
"0 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"1 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"2 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"3 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"4 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"\n",
|
||
" Promo Período Limitado? EAM Categoria Cód. Combo Código do Item \\\n",
|
||
"0 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"1 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"2 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"3 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"4 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"\n",
|
||
" Descrição do Item \\\n",
|
||
"0 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"1 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"2 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"3 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"4 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"\n",
|
||
" Chamada Promocional Valor do Guia \\\n",
|
||
"0 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"1 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"2 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"3 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"4 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"\n",
|
||
" Preço Promocionado % de Desconto RE compra por RE Vende por \\\n",
|
||
"0 14.9 0.627 10.43 14.9 \n",
|
||
"1 14.9 0.627 10.43 14.9 \n",
|
||
"2 14.9 0.627 10.43 14.9 \n",
|
||
"3 14.9 0.627 10.43 14.9 \n",
|
||
"4 14.9 0.627 10.43 14.9 \n",
|
||
"\n",
|
||
" RE lucra (R$) SKU SKU_FINAL \n",
|
||
"0 4.47 52583 52583 \n",
|
||
"1 4.47 52583 52583 \n",
|
||
"2 4.47 52583 52583 \n",
|
||
"3 4.47 52583 52583 \n",
|
||
"4 4.47 52583 52583 "
|
||
]
|
||
},
|
||
"execution_count": 26,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_tabela = pd.merge(left=df_tabela,right=df0[['SKU','SKU_FINAL']],left_on='Código do Item',right_on='SKU',how='inner')\n",
|
||
"df_tabela.head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 27,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela['Código do Item'] = np.where(df_tabela['SKU_FINAL'].notna(),df_tabela['SKU_FINAL'],df_tabela['Código do Item'])"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 28,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela = df_tabela.drop(columns=['SKU','SKU_FINAL'])"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 29,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/html": [
|
||
"<div>\n",
|
||
"<style scoped>\n",
|
||
" .dataframe tbody tr th:only-of-type {\n",
|
||
" vertical-align: middle;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe tbody tr th {\n",
|
||
" vertical-align: top;\n",
|
||
" }\n",
|
||
"\n",
|
||
" .dataframe thead th {\n",
|
||
" text-align: right;\n",
|
||
" }\n",
|
||
"</style>\n",
|
||
"<table border=\"1\" class=\"dataframe\">\n",
|
||
" <thead>\n",
|
||
" <tr style=\"text-align: right;\">\n",
|
||
" <th></th>\n",
|
||
" <th>Ciclo</th>\n",
|
||
" <th>Veiculo</th>\n",
|
||
" <th>Tipo de Promoção</th>\n",
|
||
" <th>Estratégia de Promoção</th>\n",
|
||
" <th>Tipo_mecanica</th>\n",
|
||
" <th>Promo Período Limitado?</th>\n",
|
||
" <th>EAM</th>\n",
|
||
" <th>Categoria</th>\n",
|
||
" <th>Cód. Combo</th>\n",
|
||
" <th>Código do Item</th>\n",
|
||
" <th>Descrição do Item</th>\n",
|
||
" <th>Chamada Promocional</th>\n",
|
||
" <th>Valor do Guia</th>\n",
|
||
" <th>Preço Promocionado</th>\n",
|
||
" <th>% de Desconto</th>\n",
|
||
" <th>RE compra por</th>\n",
|
||
" <th>RE Vende por</th>\n",
|
||
" <th>RE lucra (R$)</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>202516</td>\n",
|
||
" <td>Guia CF</td>\n",
|
||
" <td>BW</td>\n",
|
||
" <td>Movimentar Flow</td>\n",
|
||
" <td>Desconto Direto</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>Sim</td>\n",
|
||
" <td>MAQUIAGEM</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52583</td>\n",
|
||
" <td>NIINA SECRETS HIDRA BALM VINHO 3,2g</td>\n",
|
||
" <td>BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ...</td>\n",
|
||
" <td>39.99</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>0.627</td>\n",
|
||
" <td>10.43</td>\n",
|
||
" <td>14.9</td>\n",
|
||
" <td>4.47</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" Ciclo Veiculo Tipo de Promoção Estratégia de Promoção Tipo_mecanica \\\n",
|
||
"0 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"1 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"2 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"3 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"4 202516 Guia CF BW Movimentar Flow Desconto Direto \n",
|
||
"\n",
|
||
" Promo Período Limitado? EAM Categoria Cód. Combo Código do Item \\\n",
|
||
"0 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"1 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"2 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"3 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"4 NaN Sim MAQUIAGEM NaN 52583 \n",
|
||
"\n",
|
||
" Descrição do Item \\\n",
|
||
"0 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"1 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"2 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"3 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"4 NIINA SECRETS HIDRA BALM VINHO 3,2g \n",
|
||
"\n",
|
||
" Chamada Promocional Valor do Guia \\\n",
|
||
"0 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"1 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"2 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"3 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"4 BW | NIINA SECRETS HIDRA BALM VINHO 3,2g | DE ... 39.99 \n",
|
||
"\n",
|
||
" Preço Promocionado % de Desconto RE compra por RE Vende por \\\n",
|
||
"0 14.9 0.627 10.43 14.9 \n",
|
||
"1 14.9 0.627 10.43 14.9 \n",
|
||
"2 14.9 0.627 10.43 14.9 \n",
|
||
"3 14.9 0.627 10.43 14.9 \n",
|
||
"4 14.9 0.627 10.43 14.9 \n",
|
||
"\n",
|
||
" RE lucra (R$) \n",
|
||
"0 4.47 \n",
|
||
"1 4.47 \n",
|
||
"2 4.47 \n",
|
||
"3 4.47 \n",
|
||
"4 4.47 "
|
||
]
|
||
},
|
||
"execution_count": 29,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_tabela.head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela['MATCH'] = 1"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela = pd.merge(left=df_tabela,right=df_pdv[['PDV','MATCH','UF','DESCRIÇÃO PDV','ANALISTA']],on='MATCH',how='inner')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela.head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela = df_tabela.drop_duplicates()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df.head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df['pdv'] = df['pdv'].astype(str)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df['SKU_FINAL'] = df['SKU_FINAL'].astype(str)\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela['Código do Item'] = df_tabela['Código do Item'].astype('str')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_tabela['PDV'] = df_tabela['PDV'].astype(str)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df[['SKU_FINAL','pdv']].head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df[(df['SKU_FINAL']=='93403')]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final = pd.merge(left=df,right=df_tabela,right_on=['Código do Item','PDV'],left_on=['SKU_FINAL','pdv'],how='inner')\n",
|
||
"df_final.shape "
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final = pd.merge(left=df,right=df_tabela,right_on=['Código do Item','PDV'],left_on=['SKU','pdv'],how='inner')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['pdv'] = df_final['PDV']\n",
|
||
"df_final['SKU_FINAL'] = df_final['Código do Item']"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['Descrição do Item']"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.head(20)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['match'] = 1"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"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='inner')\n",
|
||
"df_final.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.PDV.value_counts()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final = pd.merge(left=df_final,right=df_pdv[['PDV', 'CANAL', 'DESCRIÇÃO PDV', 'PDV DESC','UF', 'MARCA', 'ANALISTA']],on = 'PDV',how='inner')\n",
|
||
"df_final.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final['SKU'] = df_final['SKU'].astype(str) \n",
|
||
"#df_final['PDV'] = df_final['PDV'].astype(str) \n",
|
||
"#df_final = pd.merge(left=df_final,right=df_estoque[['SKU_FINAL', 'DDV PREVISTO', 'COBERTURA ATUAL','PDV']],right_on=['PDV','SKU_FINAL'],left_on=['PDV','SKU'],how='left')\n",
|
||
"#df_final.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"\n",
|
||
"r'''df_bi_preco['SKU2'] = df_bi_preco['SKU2'].astype(str).str.replace('.0','',regex=False) \n",
|
||
"\n",
|
||
"df_final = pd.merge(left=df_final,right=df_bi_preco[['SKU2', 'UF','Tipo Preço', 'PC', 'PV']],right_on=['UF','SKU2'],left_on=['UF','SKU_FINAL'],how='left')\n",
|
||
"df_final.shape ''' "
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['pdv'].value_counts()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_bi_preco['SKU1'] = df_bi_preco['SKU1'].astype(str).str.replace('.0','',regex=False) \n",
|
||
"\n",
|
||
"#df_final = pd.merge(left=df_final,right=df_bi_preco[['SKU1', 'SKU2', 'UF', 'PC', 'PV']],right_on=['UF','SKU1'],left_on=['UF','SKU'],how='left')\n",
|
||
"#df_final.shape "
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_bi_preco = df_bi_preco[df_bi_preco['MARCA']=='EUDORA']"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"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": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_estoque = df_estoque.rename(columns={'SKU_FINAL':'SKU_PARA_VALIDACAO'})\n",
|
||
"#df_final = pd.merge( left= df_final, right = df_estoque[['SKU_PARA_VALIDACAO','Arquivo_Origem']], left_on= 'SKU', right_on='SKU_PARA_VALIDACAO', how='left')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final = df_final.drop(columns=['Desativação','Lançamento','Promoção Próximo Ciclo','Compra inteligente semanal/Sugestão de compra','Planograma','Carteira Bloqueada Para Novos Pedidos','Preço Sell In','Quantidade','Item analisado','Promoção Próximo Ciclo + 1','SKU_PARA_VALIDACAO','Ciclo_y'])"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['PDV_SKU'] = df_final['PDV'].astype(str) + df_final['SKU_FINAL'].astype(str) \n",
|
||
"df_final['UFPRODUTO'] = df_final['UF'].astype(str) + df_final['SKU_FINAL'].astype(str)\n",
|
||
"\n",
|
||
"df_final.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['PDV']"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['nextcycleprojection'].value_counts()\n",
|
||
"\n",
|
||
"df_final['secondtonextcycleprojection'].value_counts()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final['PROJEÇÃO DO CICLO PROMOCIONADO'] =df_final['Projeção Próximo Ciclo + 1'] - df_final['Projeção Próximo Ciclo'] # projeção do ciclo em estudo"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final['PROJEÇÃO DO CICLO PROMOCIONADO'].value_counts()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final['Data Prevista Regularização'] = df_final['Data Prevista Regularização'].astype(str).replace('0','REGULAR')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final = df_final.rename(columns={'Arquivo_Origem': 'MARCA'})"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final = df_final[~df_final['MARCA'].isna()]\n",
|
||
"#df_final['MARCA'].isna().sum()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final = df_final.drop_duplicates()\n",
|
||
"df_final.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_vendas['PDV'] = df_vendas['PDV'].astype(str)\n",
|
||
"df_vendas['SKU'] = df_vendas['SKU'].astype(str)\n",
|
||
"\n",
|
||
"df_vendas.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_vendas.head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final = pd.merge(df_final,df_vendas,left_on=['PDV','SKU'],right_on=['PDV','SKU'],how='left')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.iloc[:, -18:-1] = df_final.iloc[:, -18:-1].fillna(0)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"# Define list of target columns\n",
|
||
"sales_2024_cols = df_final.columns[-18:-1]\n",
|
||
"# Create a new column with the row-wise max\n",
|
||
"df_final['PICO DE VENDAS 17CICLOS'] = df_final[sales_2024_cols].max(axis=1)\n",
|
||
"\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.PDV.value_counts()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"sales_2024_cols"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"vendas_6_meses = df_final.columns[-8:-2]\n",
|
||
"\n",
|
||
"df_final['Pico Vendas Ultimos 6 ciclos'] = df_final[vendas_6_meses].max(axis=1)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns[-19]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"CRESCIMENTO = (df_final[df_final.columns[-18]].sum() - df_final[df_final.columns[-3]].sum())/df_final[df_final.columns[-18]].sum() \n",
|
||
"print(CRESCIMENTO)\n",
|
||
"\n",
|
||
"df_final['CRESCIMENTO'] = .2"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns[-18:-17].str.split(\" \")[0][-1]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"mesmo_ciclo_ano_passado = df_final.columns[-18]\n",
|
||
"ciclo_ano_passado = df_final.columns[-18:-17].str.split(\" \")[0][-1]\n",
|
||
"df_final[ciclo_ano_passado] = df_final[mesmo_ciclo_ano_passado]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns[-19:-3]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"vendas_todos_historicos = df_final.columns[-19:-3]\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",
|
||
"df_final['MEDIANA DO HISTÓRICO'] = np.where(df_final['MEDIANA DO HISTÓRICO'] <1,df_final['MEDIA DO HISTÓRICO'],df_final['MEDIANA DO HISTÓRICO'])\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"CRESCIMENTO"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['PV GINSENG'] = np.where(df_final['CRESCIMENTO'] * df_final[ciclo_ano_passado] + df_final[ciclo_ano_passado] < df_final['MEDIANA DO HISTÓRICO'],\n",
|
||
" np.ceil(df_final['CRESCIMENTO'] * df_final['MEDIANA DO HISTÓRICO']+ df_final['MEDIANA DO HISTÓRICO']), \n",
|
||
" np.ceil(df_final['CRESCIMENTO']*df_final[ciclo_ano_passado]+df_final[ciclo_ano_passado]))"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final['PV GINSENG'] = np.where(df_final['PV GINSENG']<1, df_final['Projeção Próximo Ciclo + 1'],df_final['PV GINSENG'])"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns[50:59]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final = df_final.rename(columns={df_final.columns[59]: \"C-4\", df_final.columns[60]: \"C-3\",df_final.columns[61]: \"C-2\",df_final.columns[62]: \"C-1\"})"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.drop(columns=df_final.columns[43:48], inplace=True)\n",
|
||
"\n",
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns[44:54]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.drop(columns=df_final.columns[44:54], inplace=True)\n",
|
||
"\n",
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"# List all columns except the two\n",
|
||
"cols_to_group_by = df_final.columns.difference(['DDV PREVISTO', 'COBERTURA ATUAL'])\n",
|
||
"\n",
|
||
"# Group and aggregate\n",
|
||
"df_final_dedup = df_final\n",
|
||
" "
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final['Compra inteligente Próximo Ciclo']\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final['DDV PREVISTO'] = df_final['DDV PREVISTO'].fillna(0.01)\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup['DDV PREVISTO'] = np.where(\n",
|
||
"# df_final_dedup['DDV PREVISTO'] == 0,\n",
|
||
"# 0.01,\n",
|
||
"# df_final_dedup['DDV PREVISTO']\n",
|
||
"#)\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup['DDV PREVISTO'].value_counts()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup['EST PROJE FINAL CICLO ATUAL'] = (df_final_dedup['Estoque Atual'] + df_final_dedup['Estoque em Transito']) - round(df_final_dedup['dias_ate_inicio'] * df_final_dedup['DDV PREVISTO'],0)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup['EST PROJE FINAL CICLO ATUAL'] = np.where(df_final_dedup['EST PROJE FINAL CICLO ATUAL']<0,0,df_final_dedup['EST PROJE FINAL CICLO ATUAL'])"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup['VENDAS R$ PV GINSENG'] = df_final_dedup['PRECO DE VENDA'] * df_final_dedup['PV GINSENG']"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup = df_final_dedup.rename(columns={'Ciclo_x':'Ciclo'})"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final_dedup['SUGESTÃO ABASTECIMENTO'] = ''\n",
|
||
"df_final_dedup['VENDAS R$ ABASTECIMENTO'] = ''\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup['RBV 202406'] = df_final_dedup['PRECO DE VENDA'] * df_final_dedup[ciclo_ano_passado] "
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup['COB PROJETADA'] = np.where(\n",
|
||
"# df_final_dedup['DDV PREVISTO'] != 0,\n",
|
||
"# (df_final_dedup['EST PROJE FINAL CICLO ATUAL'] + df_final_dedup['PV GINSENG']) / df_final_dedup['DDV PREVISTO'],\n",
|
||
"# 999)\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup.drop(columns=['dias_ate_inicio','SKU_FINAL','Projeção Próximo Ciclo + 1',\n",
|
||
"# 'CRESCIMENTO'],inplace=True)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup['MARCA'] = df_final_dedup['MARCA'].str.replace('.csv','',regex=False)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup['COB PROJETADA'] = df_final_dedup['COB PROJETADA'].fillna(999)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_final_dedup.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#marca_promo = df_estoque['Arquivo_Origem'].iloc[0].replace('.csv','')\n",
|
||
"#marca_promo"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"# Passo 1: ordenar\n",
|
||
"df_sorted = df_final.sort_values(by='% de Desconto', ascending=False)\n",
|
||
"\n",
|
||
"# Passo 2: obter até 3 chamadas promocionais por grupo\n",
|
||
"promo_grouped = (\n",
|
||
" df_sorted.groupby(['SKU_FINAL', 'PDV'])['Chamada Promocional']\n",
|
||
" .apply(lambda x: x.dropna().unique()[:3])\n",
|
||
" .apply(pd.Series)\n",
|
||
" .rename(columns={0: 'MECÂNICA 1', 1: 'MECÂNICA 2', 2: 'MECÂNICA 3'})\n",
|
||
" .reset_index()\n",
|
||
")\n",
|
||
"\n",
|
||
"# Passo 3: base com maior % de desconto\n",
|
||
"df_base = df_sorted.drop_duplicates(subset=['SKU_FINAL', 'PDV'], keep='first').reset_index(drop=True)\n",
|
||
"\n",
|
||
"# Passo 4: merge com chamadas pivotadas\n",
|
||
"df_merged = df_base.merge(promo_grouped, on=['SKU_FINAL', 'PDV'], how='left')\n",
|
||
"\n",
|
||
"# Corrigir dicionário: agrupar e pegar o primeiro valor válido por (SKU_FINAL, PDV)\n",
|
||
"# Passo 1: dicionário com Cód. Combo único por SKU_FINAL+PDV\n",
|
||
"cod_combo_map = (\n",
|
||
" df_final\n",
|
||
" .dropna(subset=['Cód. Combo'])\n",
|
||
" .groupby(['SKU_FINAL', 'PDV'])['Cód. Combo']\n",
|
||
" .first()\n",
|
||
" .to_dict()\n",
|
||
")\n",
|
||
"\n",
|
||
"# Passo 2: gerar Series com os valores mapeados\n",
|
||
"cod_combo_series = df_merged.set_index(['SKU_FINAL', 'pdv']).index.map(cod_combo_map)\n",
|
||
"\n",
|
||
"# Passo 3: substituir os valores diretamente\n",
|
||
"df_merged['Cód. Combo'] = cod_combo_series.where(cod_combo_series.notna(), df_merged['Cód. Combo'])\n",
|
||
"\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged.PDV.value_counts()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"# Columns to bring up front\n",
|
||
"priority_cols = [\n",
|
||
" 'PDV_SKU',\t'SKU',\t'MARCA','INICIO CICLO',\n",
|
||
" 'FIM CICLO',\t'DURAÇÃO','dias_ate_inicio',\t\n",
|
||
" 'UFPRODUTO',\t'Item Desativado',\t'Data Prevista Regularização',\n",
|
||
" 'ANALISTA',\t'UF',\t'PDV',\t'DESCRIÇÃO PDV',\t'Classe',\n",
|
||
" 'Descrição','MECÂNICA 1',\n",
|
||
" 'MECÂNICA 2', 'MECÂNICA 3','Estoque Atual',\t'COBERTURA ATUAL',\n",
|
||
" 'Estoque em Transito',\t'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_merged.columns if col not in priority_cols]\n",
|
||
"\n",
|
||
"# Reorder\n",
|
||
"df_merged = df_merged[priority_cols + other_cols]\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged['INICIO CICLO'] = pd.to_datetime(df_merged['INICIO CICLO'], dayfirst=True).dt.strftime('%d/%m/%Y')\n",
|
||
"\n",
|
||
"df_merged['FIM CICLO'] = pd.to_datetime(df_merged['FIM CICLO'], dayfirst=True).dt.strftime('%d/%m/%Y')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged = df_merged.loc[:, ~df_merged.columns.str.endswith('_y')]\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged.columns = df_merged.columns.str.replace('_x$', '', regex=True)\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged['DESCRICAO'] = df_merged['Descrição do Item']"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged['SKU'] = np.where(df_merged['SKU'].isna(),df_merged['SKU_FINAL'],df_merged['SKU'])"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged = df_merged.drop(columns=['Veiculo',\t'Tipo de Promoção',\t'Estratégia de Promoção','Tipo_mecanica'\n",
|
||
" ,'Chamada Promocional','MATCH','match','Descrição do Item','CRESCIMENTO',\n",
|
||
" 'Cód. Combo'])"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged = df_merged.drop(columns=['Código do Item','PDV','RE compra por',\t'RE Vende por',\t'RE lucra (R$)','INICIO CICLO',\n",
|
||
" 'FIM CICLO','Ciclo','CATEGORIA','ANALISTA','dias_ate_inicio','PDV_SKU','UFPRODUTO',\n",
|
||
" 'VENDAS R$ ABASTECIMENTO','DURAÇÃO','MEDIANA DO HISTÓRICO','DURAÇÃO'])"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged = df_merged.rename(columns={'nextcycleprojection':'PROJEÇÃO PROX CICLO'\t,'secondtonextcycleprojection':'PROJEÇÃO PROX CICLO +1'})"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged.columns = df_merged.columns.str.upper()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#df_merged = df_merged[['UF','PDV','DESCRIÇÃO PDV','SKU','DESCRICAO','EAM','CATEGORIA','CURVA','ESTOQUE ATUAL','ESTOQUE EM TRANSITO','PEDIDO PENDENTE','DIASSEMVENDA','PROJEÇÃO PROX CICLO','PROJEÇÃO PROX CICLO +1','PROMO PERÍODO LIMITADO?','VALOR DO GUIA','PREÇO PROMOCIONADO','% DE DESCONTO','PICO DE VENDAS 17CICLOS','PICO VENDAS ULTIMOS 6 CICLOS','MEDIA DO HISTÓRICO']]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged['MEDIA DO HISTÓRICO'] = round(df_merged['MEDIA DO HISTÓRICO'],0)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged[df_merged.columns[19:26]] = df_merged[df_merged.columns[19:26]].fillna(0)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"# Colunas que devem estar na frente\n",
|
||
"colunas_inicio = [\n",
|
||
" 'UF', 'PDV', 'DESCRIÇÃO PDV', 'SKU', 'DESCRICAO', 'EAM', 'CATEGORIA', 'CURVA',\n",
|
||
" 'ESTOQUE ATUAL', 'ESTOQUE EM TRANSITO', 'PEDIDO PENDENTE', 'DIASSEMVENDA',\n",
|
||
" 'PROJEÇÃO PROX CICLO', 'PROJEÇÃO PROX CICLO +1', 'PROMO PERÍODO LIMITADO?',\n",
|
||
" 'VALOR DO GUIA', 'PREÇO PROMOCIONADO', '% DE DESCONTO',\n",
|
||
" 'PICO DE VENDAS 17CICLOS', 'PICO VENDAS ULTIMOS 6 CICLOS', 'MEDIA DO HISTÓRICO'\n",
|
||
"]\n",
|
||
"\n",
|
||
"# Colunas que devem estar no fim\n",
|
||
"colunas_fim = [\n",
|
||
" 'PV GINSENG', 'SUGESTÃO ABTASTECIMENTO\\t'\n",
|
||
"]\n",
|
||
"\n",
|
||
"# Todas as outras colunas (não estão nem no início nem no fim)\n",
|
||
"colunas_meio = [col for col in df_merged.columns if col not in colunas_inicio + colunas_fim]\n",
|
||
"\n",
|
||
"# Reordena o DataFrame\n",
|
||
"df_merged = df_merged[colunas_inicio + colunas_meio + colunas_fim]\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged = df_merged.drop_duplicates()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"hoje=datetime.today().strftime('%Y-%m-%d')\n",
|
||
"hoje"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_merged = df_merged.drop(columns='SKU_FINAL')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"\n",
|
||
"from openpyxl import load_workbook\n",
|
||
"from openpyxl.styles import PatternFill, Font\n",
|
||
"\n",
|
||
"\n",
|
||
"# Export to Excel\n",
|
||
"output_file = f'C:\\\\Users\\\\joao.herculano\\\\Documents\\\\EUD_promoção_{ciclo_mais2}_{hoje}.xlsx'\n",
|
||
"with pd.ExcelWriter(output_file, engine='openpyxl') as writer:\n",
|
||
" df_merged.to_excel(writer, index=False, sheet_name='Sheet1')\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"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"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": 2
|
||
}
|