1898 lines
65 KiB
Plaintext
1898 lines
65 KiB
Plaintext
{
|
||
"cells": [
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 66,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"import numpy as np\n",
|
||
"import pandas as pd\n",
|
||
"import os\n",
|
||
"import glob"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"PRÉ REQUISITOS PARA RODAR ESSE ESTUDO:\n",
|
||
"\n",
|
||
"\n",
|
||
" Arquivo Estoque\n",
|
||
" Arquivo draft\n",
|
||
" Tabelas de pedido do ciclo atual e ciclo atual +1\n",
|
||
" Tabela de preços (BI)\n",
|
||
" Planilha de PDVs"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 67,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"\n",
|
||
"\n",
|
||
"# Caminho onde estão as subpastas com os arquivos CSV\n",
|
||
"pasta_entrada = r\"C:\\Users\\roberto.alves\\Documents\\arquivos importantes\\ESTUDO_BAZAR_02.04.25\\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"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 73,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"(113638, 46)"
|
||
]
|
||
},
|
||
"execution_count": 73,
|
||
"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\\roberto.alves\\Documents\\arquivos importantes\\ESTUDO_BAZAR_02.04.25\\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",
|
||
"df_draft.shape\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 83,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_bi_preco = pd.read_excel(r\"C:\\Users\\roberto.alves\\Documents\\arquivos importantes\\ESTUDO_BAZAR_02.04.25\\PREÇO BI\\TABELA DE PREÇOS.xlsx\")"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 78,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_ciclo6= pd.read_excel(r\"C:\\Users\\roberto.alves\\Documents\\arquivos importantes\\ESTUDO_BAZAR_02.04.25\\TABELAS DE PEDIDO\\Pedidos Semanais Especiais - GKD - 202506.xlsx\")\n",
|
||
"df_ciclo7 = pd.read_excel(r\"C:\\Users\\roberto.alves\\Documents\\arquivos importantes\\ESTUDO_BAZAR_02.04.25\\TABELAS DE PEDIDO\\Pedidos Semanais Especiais - GKD - 202507.xlsx\")"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 86,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_pdv = pd.read_excel(r\"C:\\Users\\roberto.alves\\GRUPO GINSENG\\Assistência Suprimentos - BD_LANÇAMENTOS\\BASE DE DADOS LANÇAMENTO\\BOT\\PDV\\DB_PDV.xlsx\")\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 68,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"\n",
|
||
"df_estoque = df_estoque[~df_estoque['CATEGORIA'].isin([\"SUPORTE À VENDA\",\"EMBALAGENS\"])] #excluindo apenas o que possui categoria igual a \"SUPORTE À VENDA\" e \"EMBALAGENS\"\n",
|
||
"\n",
|
||
"df_estoque = df_estoque[df_estoque['ESTOQUE ATUAL']> 0.0 ]\n",
|
||
"\n",
|
||
"\n",
|
||
"df_estoque['SKU_FINAL'] = np.where(df_estoque['SKU_PARA'] == \"-\", df_estoque['SKU'], df_estoque['SKU_PARA'])\n",
|
||
"\n",
|
||
"df_estoque = df_estoque[df_estoque['Arquivo_Origem'] == \"EUD.csv\"]\n",
|
||
"\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_estoque['DDV PREVISTO'] = np.where(df_estoque['DDV PREVISTO'].isna(),0.001,df_estoque['DDV PREVISTO']) # em casos de DDV(dias de venda) faltante assumir que o ddv é 0.0001 \n",
|
||
"\n",
|
||
"df_estoque['PREVISAO_VENDAS_45D'] = df_estoque['DDV PREVISTO'] * 45\n",
|
||
"\n",
|
||
"df_estoque['COBERTURA_CALCULADA'] = df_estoque['ESTOQUE ATUAL'] / df_estoque['DDV PREVISTO']\n",
|
||
"\n",
|
||
"df_estoque['COBERTURA_CALCULADA'] = np.where(df_estoque['COBERTURA_CALCULADA']> 1000 ,1000 ,df_estoque['COBERTURA_CALCULADA'])\n",
|
||
"\n",
|
||
"df_estoque = df_estoque.reset_index()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"estoque_grouped = df_estoque.groupby(['PDV','DESCRICAO','SKU_FINAL','CATEGORIA','CLASSE']).agg({\n",
|
||
" 'ESTOQUE ATUAL': 'sum',\n",
|
||
" 'PREVISAO_VENDAS_45D': 'max',\n",
|
||
" 'COBERTURA_CALCULADA' : 'mean'\n",
|
||
"}).reset_index()\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 75,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_draft['Projeção Próximo Ciclo + 1'] = df_draft['Projeção Próximo Ciclo + 1'] - df_draft['Projeção Próximo Ciclo']"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 76,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_draft[\"PDV\"]= df_draft[\"PDV\"].astype(int)\n",
|
||
"df_draft[\"SKU\"]= df_draft[\"SKU\"].astype(int)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 77,
|
||
"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>Projeção Próximo Ciclo</th>\n",
|
||
" <th>Projeção Próximo Ciclo + 1</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>23701</td>\n",
|
||
" <td>49014</td>\n",
|
||
" <td>7</td>\n",
|
||
" <td>1</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>23702</td>\n",
|
||
" <td>49014</td>\n",
|
||
" <td>7</td>\n",
|
||
" <td>1</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>23705</td>\n",
|
||
" <td>49014</td>\n",
|
||
" <td>2</td>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>23706</td>\n",
|
||
" <td>49014</td>\n",
|
||
" <td>3</td>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>23703</td>\n",
|
||
" <td>49014</td>\n",
|
||
" <td>6</td>\n",
|
||
" <td>3</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" PDV SKU Projeção Próximo Ciclo Projeção Próximo Ciclo + 1\n",
|
||
"0 23701 49014 7 1\n",
|
||
"1 23702 49014 7 1\n",
|
||
"2 23705 49014 2 0\n",
|
||
"3 23706 49014 3 0\n",
|
||
"4 23703 49014 6 3"
|
||
]
|
||
},
|
||
"execution_count": 77,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_draft[['PDV','SKU','Projeção Próximo Ciclo','Projeção Próximo Ciclo + 1']].head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 79,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"name": "stdout",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"(3966, 23)\n",
|
||
"(2082, 23)\n"
|
||
]
|
||
}
|
||
],
|
||
"source": [
|
||
"print(df_ciclo6.shape)\n",
|
||
"print(df_ciclo7.shape)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 80,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"name": "stdout",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"(1322, 23)\n",
|
||
"(694, 23)\n"
|
||
]
|
||
}
|
||
],
|
||
"source": [
|
||
"df_ciclo6 = df_ciclo6[df_ciclo6['Região']=='NNE'] #filtrando apenas região NNE\n",
|
||
"df_ciclo7 = df_ciclo7[df_ciclo7['Região']=='NNE'] #filtrando apenas região NNE\n",
|
||
"\n",
|
||
"print(df_ciclo6.shape)\n",
|
||
"print(df_ciclo7.shape)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 81,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_ciclo6 = df_ciclo6.rename(columns= {'Ação consumidor':'Ação consumidor_Ciclo6'})\n",
|
||
"df_ciclo7 = df_ciclo7.rename(columns= {'Ação consumidor':'Ação consumidor_Ciclo7'})\n",
|
||
"\n",
|
||
"df_ciclo6 = df_ciclo6.rename(columns= {'Percentual de desconto consumidor':'Percentual de desconto consumidor_Ciclo6'})\n",
|
||
"df_ciclo7 = df_ciclo7.rename(columns= {'Percentual de desconto consumidor':'Percentual de desconto consumidor_Ciclo7'})\n",
|
||
"\n",
|
||
"df_ciclo6 = df_ciclo6.rename(columns= {'Ação revendedor':'Ação revendedor_Ciclo6'})\n",
|
||
"df_ciclo7 = df_ciclo7.rename(columns= {'Ação revendedor':'Ação revendedor_Ciclo7'})\n",
|
||
"\n",
|
||
"df_ciclo6 = df_ciclo6.rename(columns= {'Percentual de desconto revendedor':'Percentual de desconto revendedor_Ciclo6'})\n",
|
||
"df_ciclo7 = df_ciclo7.rename(columns= {'Percentual de desconto revendedor':'Percentual de desconto revendedor_Ciclo7'})\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 82,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"2 10,00\n",
|
||
"3 10,00\n",
|
||
"6 10,00\n",
|
||
"9 10,00\n",
|
||
"14 10,00\n",
|
||
" ... \n",
|
||
"2069 0,00\n",
|
||
"2071 10,00\n",
|
||
"2074 0,00\n",
|
||
"2076 10,00\n",
|
||
"2081 33,66 | 44,12\n",
|
||
"Name: percent_desconto0_7, Length: 694, dtype: object"
|
||
]
|
||
},
|
||
"execution_count": 82,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_ciclo6['Percentual de desconto consumidor_Ciclo6'].value_counts()\n",
|
||
"\n",
|
||
"df_ciclo6['percent_desconto0_6'] = np.where(df_ciclo6['Percentual de desconto consumidor_Ciclo6'] != 0 ,df_ciclo6['Percentual de desconto revendedor_Ciclo6'] ,df_ciclo6['Percentual de desconto consumidor_Ciclo6'])\n",
|
||
"\n",
|
||
"df_ciclo7['percent_desconto0_7'] = np.where(df_ciclo7['Percentual de desconto consumidor_Ciclo7'] != 0 ,df_ciclo7['Percentual de desconto revendedor_Ciclo7'] ,df_ciclo7['Percentual de desconto consumidor_Ciclo7'])\n",
|
||
"\n",
|
||
"\n",
|
||
"df_ciclo7['percent_desconto0_7']"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"\n",
|
||
"df_bi_preco['SKU2'] = df_bi_preco['SKU2'].astype(int) #.str.replace(r'\\.0$', '', regex=True)\n",
|
||
"df_bi_preco = df_bi_preco[df_bi_preco['MARCA'] == \"EUDORA\"] #MUDAR MARCA AQUI <<<<<<<<"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 85,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"(1406, 10)"
|
||
]
|
||
},
|
||
"execution_count": 85,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_bi_preco.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 87,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"(6887, 6)"
|
||
]
|
||
},
|
||
"execution_count": 87,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"estoque_grouped.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 88,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"(6887, 7)"
|
||
]
|
||
},
|
||
"execution_count": 88,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_final = pd.merge(estoque_grouped,df_pdv[['PDV','UF']], left_on=\"PDV\",right_on=\"PDV\",how=\"inner\")\n",
|
||
"df_final.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 89,
|
||
"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>DESCRICAO</th>\n",
|
||
" <th>SKU_FINAL</th>\n",
|
||
" <th>ESTOQUE ATUAL</th>\n",
|
||
" <th>PREVISAO_VENDAS_45D</th>\n",
|
||
" <th>COBERTURA_CALCULADA</th>\n",
|
||
" <th>UF</th>\n",
|
||
" <th>PC</th>\n",
|
||
" <th>PV</th>\n",
|
||
" <th>SKU2</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN DES COL GOLD 100ml V2</td>\n",
|
||
" <td>52969</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>2.250</td>\n",
|
||
" <td>340.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>52969.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN DES COL RUBRA 100ml V3</td>\n",
|
||
" <td>49496</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>5.400</td>\n",
|
||
" <td>141.666667</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>49496.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN LOC HID DES CPO RUBRA 200ml V3</td>\n",
|
||
" <td>49487</td>\n",
|
||
" <td>5.0</td>\n",
|
||
" <td>0.045</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN LOC ILUM DES CPO GOLD 200ml V2</td>\n",
|
||
" <td>49485</td>\n",
|
||
" <td>1.0</td>\n",
|
||
" <td>0.000</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>9.97</td>\n",
|
||
" <td>52.99</td>\n",
|
||
" <td>49485.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>CARBON DES COL SPEED 100ml</td>\n",
|
||
" <td>73857</td>\n",
|
||
" <td>8.0</td>\n",
|
||
" <td>0.045</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>...</th>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>6882</th>\n",
|
||
" <td>910291</td>\n",
|
||
" <td>SECRETS BAS LIQ HD/GLOW COR 90 30ml</td>\n",
|
||
" <td>93289</td>\n",
|
||
" <td>12.0</td>\n",
|
||
" <td>0.045</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>19.18</td>\n",
|
||
" <td>82.99</td>\n",
|
||
" <td>93289.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>6883</th>\n",
|
||
" <td>910291</td>\n",
|
||
" <td>SECRETS BAS LIQ HD/GLOW COR 95 30ml</td>\n",
|
||
" <td>93498</td>\n",
|
||
" <td>8.0</td>\n",
|
||
" <td>0.045</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>19.18</td>\n",
|
||
" <td>82.99</td>\n",
|
||
" <td>93498.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>6884</th>\n",
|
||
" <td>910291</td>\n",
|
||
" <td>SECRETS BASE LIQ HIDRA GLOW COR 00 30ml</td>\n",
|
||
" <td>93274</td>\n",
|
||
" <td>1.0</td>\n",
|
||
" <td>0.045</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>19.18</td>\n",
|
||
" <td>82.99</td>\n",
|
||
" <td>93274.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>6885</th>\n",
|
||
" <td>910291</td>\n",
|
||
" <td>SIAGE COND GLOW EXPT 200ml</td>\n",
|
||
" <td>47176</td>\n",
|
||
" <td>3.0</td>\n",
|
||
" <td>0.045</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>11.59</td>\n",
|
||
" <td>49.99</td>\n",
|
||
" <td>47176.0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>6886</th>\n",
|
||
" <td>910291</td>\n",
|
||
" <td>SIAGE COND HAIR PLASTIA 200ml</td>\n",
|
||
" <td>93047</td>\n",
|
||
" <td>2.0</td>\n",
|
||
" <td>0.045</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>11.59</td>\n",
|
||
" <td>49.99</td>\n",
|
||
" <td>93047.0</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"<p>6887 rows × 10 columns</p>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" PDV DESCRICAO SKU_FINAL \\\n",
|
||
"0 20968 AURIEN DES COL GOLD 100ml V2 52969 \n",
|
||
"1 20968 AURIEN DES COL RUBRA 100ml V3 49496 \n",
|
||
"2 20968 AURIEN LOC HID DES CPO RUBRA 200ml V3 49487 \n",
|
||
"3 20968 AURIEN LOC ILUM DES CPO GOLD 200ml V2 49485 \n",
|
||
"4 20968 CARBON DES COL SPEED 100ml 73857 \n",
|
||
"... ... ... ... \n",
|
||
"6882 910291 SECRETS BAS LIQ HD/GLOW COR 90 30ml 93289 \n",
|
||
"6883 910291 SECRETS BAS LIQ HD/GLOW COR 95 30ml 93498 \n",
|
||
"6884 910291 SECRETS BASE LIQ HIDRA GLOW COR 00 30ml 93274 \n",
|
||
"6885 910291 SIAGE COND GLOW EXPT 200ml 47176 \n",
|
||
"6886 910291 SIAGE COND HAIR PLASTIA 200ml 93047 \n",
|
||
"\n",
|
||
" ESTOQUE ATUAL PREVISAO_VENDAS_45D COBERTURA_CALCULADA UF PC \\\n",
|
||
"0 17.0 2.250 340.000000 SE 35.76 \n",
|
||
"1 17.0 5.400 141.666667 SE 35.76 \n",
|
||
"2 5.0 0.045 1000.000000 SE NaN \n",
|
||
"3 1.0 0.000 1000.000000 SE 9.97 \n",
|
||
"4 8.0 0.045 1000.000000 SE NaN \n",
|
||
"... ... ... ... .. ... \n",
|
||
"6882 12.0 0.045 1000.000000 AL 19.18 \n",
|
||
"6883 8.0 0.045 1000.000000 AL 19.18 \n",
|
||
"6884 1.0 0.045 1000.000000 AL 19.18 \n",
|
||
"6885 3.0 0.045 1000.000000 AL 11.59 \n",
|
||
"6886 2.0 0.045 1000.000000 AL 11.59 \n",
|
||
"\n",
|
||
" PV SKU2 \n",
|
||
"0 184.90 52969.0 \n",
|
||
"1 184.90 49496.0 \n",
|
||
"2 NaN NaN \n",
|
||
"3 52.99 49485.0 \n",
|
||
"4 NaN NaN \n",
|
||
"... ... ... \n",
|
||
"6882 82.99 93289.0 \n",
|
||
"6883 82.99 93498.0 \n",
|
||
"6884 82.99 93274.0 \n",
|
||
"6885 49.99 47176.0 \n",
|
||
"6886 49.99 93047.0 \n",
|
||
"\n",
|
||
"[6887 rows x 10 columns]"
|
||
]
|
||
},
|
||
"execution_count": 89,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_final = pd.merge(df_final,df_bi_preco[['PC','PV','SKU2','UF']], left_on=[\"SKU_FINAL\",'UF'],right_on=[\"SKU2\",'UF'],how=\"left\")\n",
|
||
"df_final"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 90,
|
||
"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>DESCRICAO</th>\n",
|
||
" <th>SKU_FINAL</th>\n",
|
||
" <th>ESTOQUE ATUAL</th>\n",
|
||
" <th>PREVISAO_VENDAS_45D</th>\n",
|
||
" <th>COBERTURA_CALCULADA</th>\n",
|
||
" <th>UF</th>\n",
|
||
" <th>PC</th>\n",
|
||
" <th>PV</th>\n",
|
||
" <th>SKU2</th>\n",
|
||
" <th>SKU</th>\n",
|
||
" <th>Projeção Próximo Ciclo</th>\n",
|
||
" <th>Projeção Próximo Ciclo + 1</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN DES COL GOLD 100ml V2</td>\n",
|
||
" <td>52969</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>2.25</td>\n",
|
||
" <td>340.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>52969.0</td>\n",
|
||
" <td>52969</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN DES COL RUBRA 100ml V3</td>\n",
|
||
" <td>49496</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>5.40</td>\n",
|
||
" <td>141.666667</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>49496.0</td>\n",
|
||
" <td>49496</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN LOC ILUM DES CPO GOLD 200ml V2</td>\n",
|
||
" <td>49485</td>\n",
|
||
" <td>1.0</td>\n",
|
||
" <td>0.00</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>9.97</td>\n",
|
||
" <td>52.99</td>\n",
|
||
" <td>49485.0</td>\n",
|
||
" <td>49485</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>CHIC DES ANTIT AEROSSOL 125ml V2</td>\n",
|
||
" <td>52885</td>\n",
|
||
" <td>18.0</td>\n",
|
||
" <td>0.00</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>6.78</td>\n",
|
||
" <td>35.99</td>\n",
|
||
" <td>52885.0</td>\n",
|
||
" <td>52885</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>CHIC DES COL 95ml V2</td>\n",
|
||
" <td>49800</td>\n",
|
||
" <td>5.0</td>\n",
|
||
" <td>2.70</td>\n",
|
||
" <td>83.333333</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>23.19</td>\n",
|
||
" <td>119.90</td>\n",
|
||
" <td>49800.0</td>\n",
|
||
" <td>49800</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>...</th>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5010</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>SOUL PO COMP FAC ULT MATE COR 95 10g</td>\n",
|
||
" <td>92677</td>\n",
|
||
" <td>4.0</td>\n",
|
||
" <td>0.00</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>11.62</td>\n",
|
||
" <td>49.99</td>\n",
|
||
" <td>92677.0</td>\n",
|
||
" <td>92677</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5011</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL 100ml</td>\n",
|
||
" <td>92219</td>\n",
|
||
" <td>2.0</td>\n",
|
||
" <td>9.45</td>\n",
|
||
" <td>9.523810</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>92219.0</td>\n",
|
||
" <td>92219</td>\n",
|
||
" <td>8</td>\n",
|
||
" <td>1</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5012</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL DNA 100ml</td>\n",
|
||
" <td>49166</td>\n",
|
||
" <td>6.0</td>\n",
|
||
" <td>0.45</td>\n",
|
||
" <td>600.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>49166.0</td>\n",
|
||
" <td>49166</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5013</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL NEXT 100ml</td>\n",
|
||
" <td>52299</td>\n",
|
||
" <td>10.0</td>\n",
|
||
" <td>6.75</td>\n",
|
||
" <td>66.666667</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>52299.0</td>\n",
|
||
" <td>52299</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>6</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5014</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL TRIUMPH 100ml</td>\n",
|
||
" <td>56231</td>\n",
|
||
" <td>25.0</td>\n",
|
||
" <td>2.25</td>\n",
|
||
" <td>500.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>56231.0</td>\n",
|
||
" <td>56231</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>1</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"<p>5015 rows × 13 columns</p>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" PDV DESCRICAO SKU_FINAL ESTOQUE ATUAL \\\n",
|
||
"0 20968 AURIEN DES COL GOLD 100ml V2 52969 17.0 \n",
|
||
"1 20968 AURIEN DES COL RUBRA 100ml V3 49496 17.0 \n",
|
||
"2 20968 AURIEN LOC ILUM DES CPO GOLD 200ml V2 49485 1.0 \n",
|
||
"3 20968 CHIC DES ANTIT AEROSSOL 125ml V2 52885 18.0 \n",
|
||
"4 20968 CHIC DES COL 95ml V2 49800 5.0 \n",
|
||
"... ... ... ... ... \n",
|
||
"5010 22541 SOUL PO COMP FAC ULT MATE COR 95 10g 92677 4.0 \n",
|
||
"5011 22541 VOLPE DES COL 100ml 92219 2.0 \n",
|
||
"5012 22541 VOLPE DES COL DNA 100ml 49166 6.0 \n",
|
||
"5013 22541 VOLPE DES COL NEXT 100ml 52299 10.0 \n",
|
||
"5014 22541 VOLPE DES COL TRIUMPH 100ml 56231 25.0 \n",
|
||
"\n",
|
||
" PREVISAO_VENDAS_45D COBERTURA_CALCULADA UF PC PV SKU2 \\\n",
|
||
"0 2.25 340.000000 SE 35.76 184.90 52969.0 \n",
|
||
"1 5.40 141.666667 SE 35.76 184.90 49496.0 \n",
|
||
"2 0.00 1000.000000 SE 9.97 52.99 49485.0 \n",
|
||
"3 0.00 1000.000000 SE 6.78 35.99 52885.0 \n",
|
||
"4 2.70 83.333333 SE 23.19 119.90 49800.0 \n",
|
||
"... ... ... .. ... ... ... \n",
|
||
"5010 0.00 1000.000000 AL 11.62 49.99 92677.0 \n",
|
||
"5011 9.45 9.523810 AL 34.55 154.90 92219.0 \n",
|
||
"5012 0.45 600.000000 AL 34.55 154.90 49166.0 \n",
|
||
"5013 6.75 66.666667 AL 34.55 154.90 52299.0 \n",
|
||
"5014 2.25 500.000000 AL 34.55 154.90 56231.0 \n",
|
||
"\n",
|
||
" SKU Projeção Próximo Ciclo Projeção Próximo Ciclo + 1 \n",
|
||
"0 52969 0 0 \n",
|
||
"1 49496 0 0 \n",
|
||
"2 49485 0 0 \n",
|
||
"3 52885 0 0 \n",
|
||
"4 49800 0 0 \n",
|
||
"... ... ... ... \n",
|
||
"5010 92677 0 0 \n",
|
||
"5011 92219 8 1 \n",
|
||
"5012 49166 0 0 \n",
|
||
"5013 52299 1 6 \n",
|
||
"5014 56231 1 1 \n",
|
||
"\n",
|
||
"[5015 rows x 13 columns]"
|
||
]
|
||
},
|
||
"execution_count": 90,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_final = pd.merge(df_final,df_draft[['PDV','SKU','Projeção Próximo Ciclo','Projeção Próximo Ciclo + 1']],left_on=[\"PDV\",\"SKU_FINAL\"],right_on=[\"PDV\",\"SKU\"],how=\"inner\")\n",
|
||
"df_final"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 91,
|
||
"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>DESCRICAO</th>\n",
|
||
" <th>SKU_FINAL</th>\n",
|
||
" <th>ESTOQUE ATUAL</th>\n",
|
||
" <th>PREVISAO_VENDAS_45D</th>\n",
|
||
" <th>COBERTURA_CALCULADA</th>\n",
|
||
" <th>UF</th>\n",
|
||
" <th>PC</th>\n",
|
||
" <th>PV</th>\n",
|
||
" <th>SKU2</th>\n",
|
||
" <th>SKU</th>\n",
|
||
" <th>Projeção Próximo Ciclo</th>\n",
|
||
" <th>Projeção Próximo Ciclo + 1</th>\n",
|
||
" <th>Código</th>\n",
|
||
" <th>percent_desconto0_6</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN DES COL GOLD 100ml V2</td>\n",
|
||
" <td>52969</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>2.25</td>\n",
|
||
" <td>340.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>52969.0</td>\n",
|
||
" <td>52969</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>52969.0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN DES COL RUBRA 100ml V3</td>\n",
|
||
" <td>49496</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>5.40</td>\n",
|
||
" <td>141.666667</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>49496.0</td>\n",
|
||
" <td>49496</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN LOC ILUM DES CPO GOLD 200ml V2</td>\n",
|
||
" <td>49485</td>\n",
|
||
" <td>1.0</td>\n",
|
||
" <td>0.00</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>9.97</td>\n",
|
||
" <td>52.99</td>\n",
|
||
" <td>49485.0</td>\n",
|
||
" <td>49485</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>49485.0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>CHIC DES ANTIT AEROSSOL 125ml V2</td>\n",
|
||
" <td>52885</td>\n",
|
||
" <td>18.0</td>\n",
|
||
" <td>0.00</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>6.78</td>\n",
|
||
" <td>35.99</td>\n",
|
||
" <td>52885.0</td>\n",
|
||
" <td>52885</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>CHIC DES COL 95ml V2</td>\n",
|
||
" <td>49800</td>\n",
|
||
" <td>5.0</td>\n",
|
||
" <td>2.70</td>\n",
|
||
" <td>83.333333</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>23.19</td>\n",
|
||
" <td>119.90</td>\n",
|
||
" <td>49800.0</td>\n",
|
||
" <td>49800</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>...</th>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5010</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>SOUL PO COMP FAC ULT MATE COR 95 10g</td>\n",
|
||
" <td>92677</td>\n",
|
||
" <td>4.0</td>\n",
|
||
" <td>0.00</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>11.62</td>\n",
|
||
" <td>49.99</td>\n",
|
||
" <td>92677.0</td>\n",
|
||
" <td>92677</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>92677.0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5011</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL 100ml</td>\n",
|
||
" <td>92219</td>\n",
|
||
" <td>2.0</td>\n",
|
||
" <td>9.45</td>\n",
|
||
" <td>9.523810</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>92219.0</td>\n",
|
||
" <td>92219</td>\n",
|
||
" <td>8</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>92219.0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5012</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL DNA 100ml</td>\n",
|
||
" <td>49166</td>\n",
|
||
" <td>6.0</td>\n",
|
||
" <td>0.45</td>\n",
|
||
" <td>600.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>49166.0</td>\n",
|
||
" <td>49166</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5013</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL NEXT 100ml</td>\n",
|
||
" <td>52299</td>\n",
|
||
" <td>10.0</td>\n",
|
||
" <td>6.75</td>\n",
|
||
" <td>66.666667</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>52299.0</td>\n",
|
||
" <td>52299</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>6</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5014</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL TRIUMPH 100ml</td>\n",
|
||
" <td>56231</td>\n",
|
||
" <td>25.0</td>\n",
|
||
" <td>2.25</td>\n",
|
||
" <td>500.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>56231.0</td>\n",
|
||
" <td>56231</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"<p>5015 rows × 15 columns</p>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" PDV DESCRICAO SKU_FINAL ESTOQUE ATUAL \\\n",
|
||
"0 20968 AURIEN DES COL GOLD 100ml V2 52969 17.0 \n",
|
||
"1 20968 AURIEN DES COL RUBRA 100ml V3 49496 17.0 \n",
|
||
"2 20968 AURIEN LOC ILUM DES CPO GOLD 200ml V2 49485 1.0 \n",
|
||
"3 20968 CHIC DES ANTIT AEROSSOL 125ml V2 52885 18.0 \n",
|
||
"4 20968 CHIC DES COL 95ml V2 49800 5.0 \n",
|
||
"... ... ... ... ... \n",
|
||
"5010 22541 SOUL PO COMP FAC ULT MATE COR 95 10g 92677 4.0 \n",
|
||
"5011 22541 VOLPE DES COL 100ml 92219 2.0 \n",
|
||
"5012 22541 VOLPE DES COL DNA 100ml 49166 6.0 \n",
|
||
"5013 22541 VOLPE DES COL NEXT 100ml 52299 10.0 \n",
|
||
"5014 22541 VOLPE DES COL TRIUMPH 100ml 56231 25.0 \n",
|
||
"\n",
|
||
" PREVISAO_VENDAS_45D COBERTURA_CALCULADA UF PC PV SKU2 \\\n",
|
||
"0 2.25 340.000000 SE 35.76 184.90 52969.0 \n",
|
||
"1 5.40 141.666667 SE 35.76 184.90 49496.0 \n",
|
||
"2 0.00 1000.000000 SE 9.97 52.99 49485.0 \n",
|
||
"3 0.00 1000.000000 SE 6.78 35.99 52885.0 \n",
|
||
"4 2.70 83.333333 SE 23.19 119.90 49800.0 \n",
|
||
"... ... ... .. ... ... ... \n",
|
||
"5010 0.00 1000.000000 AL 11.62 49.99 92677.0 \n",
|
||
"5011 9.45 9.523810 AL 34.55 154.90 92219.0 \n",
|
||
"5012 0.45 600.000000 AL 34.55 154.90 49166.0 \n",
|
||
"5013 6.75 66.666667 AL 34.55 154.90 52299.0 \n",
|
||
"5014 2.25 500.000000 AL 34.55 154.90 56231.0 \n",
|
||
"\n",
|
||
" SKU Projeção Próximo Ciclo Projeção Próximo Ciclo + 1 Código \\\n",
|
||
"0 52969 0 0 52969.0 \n",
|
||
"1 49496 0 0 NaN \n",
|
||
"2 49485 0 0 49485.0 \n",
|
||
"3 52885 0 0 NaN \n",
|
||
"4 49800 0 0 NaN \n",
|
||
"... ... ... ... ... \n",
|
||
"5010 92677 0 0 92677.0 \n",
|
||
"5011 92219 8 1 92219.0 \n",
|
||
"5012 49166 0 0 NaN \n",
|
||
"5013 52299 1 6 NaN \n",
|
||
"5014 56231 1 1 NaN \n",
|
||
"\n",
|
||
" percent_desconto0_6 \n",
|
||
"0 0,00 \n",
|
||
"1 NaN \n",
|
||
"2 0,00 \n",
|
||
"3 NaN \n",
|
||
"4 NaN \n",
|
||
"... ... \n",
|
||
"5010 0,00 \n",
|
||
"5011 0,00 \n",
|
||
"5012 NaN \n",
|
||
"5013 NaN \n",
|
||
"5014 NaN \n",
|
||
"\n",
|
||
"[5015 rows x 15 columns]"
|
||
]
|
||
},
|
||
"execution_count": 91,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_final = pd.merge(df_final,df_ciclo6[['Código','percent_desconto0_6']],left_on=\"SKU_FINAL\",right_on='Código',how=\"left\")\n",
|
||
"\n",
|
||
"df_final"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 92,
|
||
"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>DESCRICAO</th>\n",
|
||
" <th>SKU_FINAL</th>\n",
|
||
" <th>ESTOQUE ATUAL</th>\n",
|
||
" <th>PREVISAO_VENDAS_45D</th>\n",
|
||
" <th>COBERTURA_CALCULADA</th>\n",
|
||
" <th>UF</th>\n",
|
||
" <th>PC</th>\n",
|
||
" <th>PV</th>\n",
|
||
" <th>SKU2</th>\n",
|
||
" <th>SKU</th>\n",
|
||
" <th>Projeção Próximo Ciclo</th>\n",
|
||
" <th>Projeção Próximo Ciclo + 1</th>\n",
|
||
" <th>Código_x</th>\n",
|
||
" <th>percent_desconto0_6</th>\n",
|
||
" <th>Código_y</th>\n",
|
||
" <th>percent_desconto0_7</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN DES COL GOLD 100ml V2</td>\n",
|
||
" <td>52969</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>2.25</td>\n",
|
||
" <td>340.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>52969.0</td>\n",
|
||
" <td>52969</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>52969.0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" <td>52969.0</td>\n",
|
||
" <td>10,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN DES COL RUBRA 100ml V3</td>\n",
|
||
" <td>49496</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>5.40</td>\n",
|
||
" <td>141.666667</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>49496.0</td>\n",
|
||
" <td>49496</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>49496.0</td>\n",
|
||
" <td>10,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>AURIEN LOC ILUM DES CPO GOLD 200ml V2</td>\n",
|
||
" <td>49485</td>\n",
|
||
" <td>1.0</td>\n",
|
||
" <td>0.00</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>9.97</td>\n",
|
||
" <td>52.99</td>\n",
|
||
" <td>49485.0</td>\n",
|
||
" <td>49485</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>49485.0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>CHIC DES ANTIT AEROSSOL 125ml V2</td>\n",
|
||
" <td>52885</td>\n",
|
||
" <td>18.0</td>\n",
|
||
" <td>0.00</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>6.78</td>\n",
|
||
" <td>35.99</td>\n",
|
||
" <td>52885.0</td>\n",
|
||
" <td>52885</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>CHIC DES COL 95ml V2</td>\n",
|
||
" <td>49800</td>\n",
|
||
" <td>5.0</td>\n",
|
||
" <td>2.70</td>\n",
|
||
" <td>83.333333</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>23.19</td>\n",
|
||
" <td>119.90</td>\n",
|
||
" <td>49800.0</td>\n",
|
||
" <td>49800</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>49800.0</td>\n",
|
||
" <td>10,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>...</th>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5010</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>SOUL PO COMP FAC ULT MATE COR 95 10g</td>\n",
|
||
" <td>92677</td>\n",
|
||
" <td>4.0</td>\n",
|
||
" <td>0.00</td>\n",
|
||
" <td>1000.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>11.62</td>\n",
|
||
" <td>49.99</td>\n",
|
||
" <td>92677.0</td>\n",
|
||
" <td>92677</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>92677.0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5011</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL 100ml</td>\n",
|
||
" <td>92219</td>\n",
|
||
" <td>2.0</td>\n",
|
||
" <td>9.45</td>\n",
|
||
" <td>9.523810</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>92219.0</td>\n",
|
||
" <td>92219</td>\n",
|
||
" <td>8</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>92219.0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" <td>92219.0</td>\n",
|
||
" <td>10,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5012</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL DNA 100ml</td>\n",
|
||
" <td>49166</td>\n",
|
||
" <td>6.0</td>\n",
|
||
" <td>0.45</td>\n",
|
||
" <td>600.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>49166.0</td>\n",
|
||
" <td>49166</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>49166.0</td>\n",
|
||
" <td>10,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5013</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL NEXT 100ml</td>\n",
|
||
" <td>52299</td>\n",
|
||
" <td>10.0</td>\n",
|
||
" <td>6.75</td>\n",
|
||
" <td>66.666667</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>52299.0</td>\n",
|
||
" <td>52299</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>6</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>52299.0</td>\n",
|
||
" <td>10,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>5014</th>\n",
|
||
" <td>22541</td>\n",
|
||
" <td>VOLPE DES COL TRIUMPH 100ml</td>\n",
|
||
" <td>56231</td>\n",
|
||
" <td>25.0</td>\n",
|
||
" <td>2.25</td>\n",
|
||
" <td>500.000000</td>\n",
|
||
" <td>AL</td>\n",
|
||
" <td>34.55</td>\n",
|
||
" <td>154.90</td>\n",
|
||
" <td>56231.0</td>\n",
|
||
" <td>56231</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>1</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>NaN</td>\n",
|
||
" <td>56231.0</td>\n",
|
||
" <td>10,00 | 43,25</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"<p>5015 rows × 17 columns</p>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" PDV DESCRICAO SKU_FINAL ESTOQUE ATUAL \\\n",
|
||
"0 20968 AURIEN DES COL GOLD 100ml V2 52969 17.0 \n",
|
||
"1 20968 AURIEN DES COL RUBRA 100ml V3 49496 17.0 \n",
|
||
"2 20968 AURIEN LOC ILUM DES CPO GOLD 200ml V2 49485 1.0 \n",
|
||
"3 20968 CHIC DES ANTIT AEROSSOL 125ml V2 52885 18.0 \n",
|
||
"4 20968 CHIC DES COL 95ml V2 49800 5.0 \n",
|
||
"... ... ... ... ... \n",
|
||
"5010 22541 SOUL PO COMP FAC ULT MATE COR 95 10g 92677 4.0 \n",
|
||
"5011 22541 VOLPE DES COL 100ml 92219 2.0 \n",
|
||
"5012 22541 VOLPE DES COL DNA 100ml 49166 6.0 \n",
|
||
"5013 22541 VOLPE DES COL NEXT 100ml 52299 10.0 \n",
|
||
"5014 22541 VOLPE DES COL TRIUMPH 100ml 56231 25.0 \n",
|
||
"\n",
|
||
" PREVISAO_VENDAS_45D COBERTURA_CALCULADA UF PC PV SKU2 \\\n",
|
||
"0 2.25 340.000000 SE 35.76 184.90 52969.0 \n",
|
||
"1 5.40 141.666667 SE 35.76 184.90 49496.0 \n",
|
||
"2 0.00 1000.000000 SE 9.97 52.99 49485.0 \n",
|
||
"3 0.00 1000.000000 SE 6.78 35.99 52885.0 \n",
|
||
"4 2.70 83.333333 SE 23.19 119.90 49800.0 \n",
|
||
"... ... ... .. ... ... ... \n",
|
||
"5010 0.00 1000.000000 AL 11.62 49.99 92677.0 \n",
|
||
"5011 9.45 9.523810 AL 34.55 154.90 92219.0 \n",
|
||
"5012 0.45 600.000000 AL 34.55 154.90 49166.0 \n",
|
||
"5013 6.75 66.666667 AL 34.55 154.90 52299.0 \n",
|
||
"5014 2.25 500.000000 AL 34.55 154.90 56231.0 \n",
|
||
"\n",
|
||
" SKU Projeção Próximo Ciclo Projeção Próximo Ciclo + 1 Código_x \\\n",
|
||
"0 52969 0 0 52969.0 \n",
|
||
"1 49496 0 0 NaN \n",
|
||
"2 49485 0 0 49485.0 \n",
|
||
"3 52885 0 0 NaN \n",
|
||
"4 49800 0 0 NaN \n",
|
||
"... ... ... ... ... \n",
|
||
"5010 92677 0 0 92677.0 \n",
|
||
"5011 92219 8 1 92219.0 \n",
|
||
"5012 49166 0 0 NaN \n",
|
||
"5013 52299 1 6 NaN \n",
|
||
"5014 56231 1 1 NaN \n",
|
||
"\n",
|
||
" percent_desconto0_6 Código_y percent_desconto0_7 \n",
|
||
"0 0,00 52969.0 10,00 \n",
|
||
"1 NaN 49496.0 10,00 \n",
|
||
"2 0,00 NaN NaN \n",
|
||
"3 NaN NaN NaN \n",
|
||
"4 NaN 49800.0 10,00 \n",
|
||
"... ... ... ... \n",
|
||
"5010 0,00 NaN NaN \n",
|
||
"5011 0,00 92219.0 10,00 \n",
|
||
"5012 NaN 49166.0 10,00 \n",
|
||
"5013 NaN 52299.0 10,00 \n",
|
||
"5014 NaN 56231.0 10,00 | 43,25 \n",
|
||
"\n",
|
||
"[5015 rows x 17 columns]"
|
||
]
|
||
},
|
||
"execution_count": 92,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_final = pd.merge(df_final,df_ciclo7[['Código','percent_desconto0_7']],left_on=\"SKU_FINAL\",right_on='Código',how=\"left\")\n",
|
||
"\n",
|
||
"df_final"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 93,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.drop(columns=['SKU2','SKU','Código_x','Código_y'],inplace = True)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 94,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['COBERTURA_CALCULADA'] = round(df_final['COBERTURA_CALCULADA'],0)\n",
|
||
"\n",
|
||
"df_final['PREVISAO_VENDAS_45D'] = round(df_final['PREVISAO_VENDAS_45D'],0)\n",
|
||
"\n",
|
||
"\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 95,
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"Index(['PDV', 'DESCRICAO', 'SKU_FINAL', 'ESTOQUE ATUAL', 'PREVISAO_VENDAS_45D',\n",
|
||
" 'COBERTURA_CALCULADA', 'UF', 'PC', 'PV', 'Projeção Próximo Ciclo',\n",
|
||
" 'Projeção Próximo Ciclo + 1', 'percent_desconto0_6',\n",
|
||
" 'percent_desconto0_7'],\n",
|
||
" dtype='object')"
|
||
]
|
||
},
|
||
"execution_count": 95,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_final.columns"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 97,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final['percent_desconto0_6'] = df_final['percent_desconto0_6'].fillna(\"0,00\")\n",
|
||
"df_final['percent_desconto0_7'] = df_final['percent_desconto0_7'].fillna(\"0,00\")"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 100,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final = df_final.rename(columns= {'percent_desconto0_6':'% DESCONTO CICLO 06','percent_desconto0_7':'% DESCONTO CICLO 07','SKU_FINAL':'SKU'})"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"ordem = ['UF','PDV', 'SKU', 'DESCRICAO', 'PC', 'PV', 'ESTOQUE ATUAL', 'PREVISAO_VENDAS_45D','COBERTURA_CALCULADA', 'Projeção Próximo Ciclo','Projeção Próximo Ciclo + 1', '% DESCONTO CICLO 06','% DESCONTO CICLO 07']\n",
|
||
"\n",
|
||
"df_final = df_final[ordem]"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 107,
|
||
"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>UF</th>\n",
|
||
" <th>PC</th>\n",
|
||
" <th>PV</th>\n",
|
||
" <th>ESTOQUE ATUAL</th>\n",
|
||
" <th>PREVISAO_VENDAS_45D</th>\n",
|
||
" <th>COBERTURA_CALCULADA</th>\n",
|
||
" <th>Projeção Próximo Ciclo</th>\n",
|
||
" <th>Projeção Próximo Ciclo + 1</th>\n",
|
||
" <th>% DESCONTO CICLO 06</th>\n",
|
||
" <th>% DESCONTO CICLO 07</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>52969</td>\n",
|
||
" <td>AURIEN DES COL GOLD 100ml V2</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>2.0</td>\n",
|
||
" <td>340.0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" <td>10,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>49496</td>\n",
|
||
" <td>AURIEN DES COL RUBRA 100ml V3</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>35.76</td>\n",
|
||
" <td>184.90</td>\n",
|
||
" <td>17.0</td>\n",
|
||
" <td>5.0</td>\n",
|
||
" <td>142.0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" <td>10,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>49485</td>\n",
|
||
" <td>AURIEN LOC ILUM DES CPO GOLD 200ml V2</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>9.97</td>\n",
|
||
" <td>52.99</td>\n",
|
||
" <td>1.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>1000.0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>52885</td>\n",
|
||
" <td>CHIC DES ANTIT AEROSSOL 125ml V2</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>6.78</td>\n",
|
||
" <td>35.99</td>\n",
|
||
" <td>18.0</td>\n",
|
||
" <td>0.0</td>\n",
|
||
" <td>1000.0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>20968</td>\n",
|
||
" <td>49800</td>\n",
|
||
" <td>CHIC DES COL 95ml V2</td>\n",
|
||
" <td>SE</td>\n",
|
||
" <td>23.19</td>\n",
|
||
" <td>119.90</td>\n",
|
||
" <td>5.0</td>\n",
|
||
" <td>3.0</td>\n",
|
||
" <td>83.0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0</td>\n",
|
||
" <td>0,00</td>\n",
|
||
" <td>10,00</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" PDV SKU DESCRICAO UF PC PV \\\n",
|
||
"0 20968 52969 AURIEN DES COL GOLD 100ml V2 SE 35.76 184.90 \n",
|
||
"1 20968 49496 AURIEN DES COL RUBRA 100ml V3 SE 35.76 184.90 \n",
|
||
"2 20968 49485 AURIEN LOC ILUM DES CPO GOLD 200ml V2 SE 9.97 52.99 \n",
|
||
"3 20968 52885 CHIC DES ANTIT AEROSSOL 125ml V2 SE 6.78 35.99 \n",
|
||
"4 20968 49800 CHIC DES COL 95ml V2 SE 23.19 119.90 \n",
|
||
"\n",
|
||
" ESTOQUE ATUAL PREVISAO_VENDAS_45D COBERTURA_CALCULADA \\\n",
|
||
"0 17.0 2.0 340.0 \n",
|
||
"1 17.0 5.0 142.0 \n",
|
||
"2 1.0 0.0 1000.0 \n",
|
||
"3 18.0 0.0 1000.0 \n",
|
||
"4 5.0 3.0 83.0 \n",
|
||
"\n",
|
||
" Projeção Próximo Ciclo Projeção Próximo Ciclo + 1 % DESCONTO CICLO 06 \\\n",
|
||
"0 0 0 0,00 \n",
|
||
"1 0 0 0,00 \n",
|
||
"2 0 0 0,00 \n",
|
||
"3 0 0 0,00 \n",
|
||
"4 0 0 0,00 \n",
|
||
"\n",
|
||
" % DESCONTO CICLO 07 \n",
|
||
"0 10,00 \n",
|
||
"1 10,00 \n",
|
||
"2 0,00 \n",
|
||
"3 0,00 \n",
|
||
"4 10,00 "
|
||
]
|
||
},
|
||
"execution_count": 107,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df_final.head()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 108,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df_final.to_excel(r\"C:\\Users\\roberto.alves\\Documents\\arquivos importantes\\ESTUDO_BAZAR_02.04.25\\bazar_eudora.xlsx\",index=False)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"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": 2
|
||
}
|