{ "cells": [ { "cell_type": "code", "execution_count": 32, "id": "a6f320df", "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 33, "id": "03c2c8a2", "metadata": {}, "outputs": [], "source": [ "df = pd.read_csv(r\"C:\\Users\\joao.herculano\\Downloads\\VENDAS_BAHIA_27_08.csv\",sep=';')" ] }, { "cell_type": "code", "execution_count": 34, "id": "df4d53ad", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nfdata_vendacanalPDVcoddescqtdvalor_unitvalor_total
03289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO52276KIT SACOLA P INST EUD 2022 C/10 VD16,376,37
13289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO49016ACCORDES DES BDY SPR 100ml V6140,7240,72
23289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO50481GLAM BAT MICRO PLUMP VERMELHO UNICO 1,8g124,4324,43
33289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO52334SOUL K/M BAT MATE VERM MODERNO V2 3,7g125,1925,19
43289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO50483GLAM BAT MICRO PLUMP ROSADO NOBRE 1,8g124,4324,43
\n", "
" ], "text/plain": [ " nf data_venda canal PDV cod \\\n", "0 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 52276 \n", "1 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 49016 \n", "2 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 50481 \n", "3 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 52334 \n", "4 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 50483 \n", "\n", " desc qtd valor_unit valor_total \n", "0 KIT SACOLA P INST EUD 2022 C/10 VD 1 6,37 6,37 \n", "1 ACCORDES DES BDY SPR 100ml V6 1 40,72 40,72 \n", "2 GLAM BAT MICRO PLUMP VERMELHO UNICO 1,8g 1 24,43 24,43 \n", "3 SOUL K/M BAT MATE VERM MODERNO V2 3,7g 1 25,19 25,19 \n", "4 GLAM BAT MICRO PLUMP ROSADO NOBRE 1,8g 1 24,43 24,43 " ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head()" ] }, { "cell_type": "code", "execution_count": 35, "id": "25155a2d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nfdata_vendacanalPDVSKUdescqtdvalor_unitvalor_total
03289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO52276KIT SACOLA P INST EUD 2022 C/10 VD16,376,37
13289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO49016ACCORDES DES BDY SPR 100ml V6140,7240,72
23289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO50481GLAM BAT MICRO PLUMP VERMELHO UNICO 1,8g124,4324,43
33289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO52334SOUL K/M BAT MATE VERM MODERNO V2 3,7g125,1925,19
43289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO50483GLAM BAT MICRO PLUMP ROSADO NOBRE 1,8g124,4324,43
\n", "
" ], "text/plain": [ " nf data_venda canal PDV SKU \\\n", "0 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 52276 \n", "1 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 49016 \n", "2 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 50481 \n", "3 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 52334 \n", "4 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 50483 \n", "\n", " desc qtd valor_unit valor_total \n", "0 KIT SACOLA P INST EUD 2022 C/10 VD 1 6,37 6,37 \n", "1 ACCORDES DES BDY SPR 100ml V6 1 40,72 40,72 \n", "2 GLAM BAT MICRO PLUMP VERMELHO UNICO 1,8g 1 24,43 24,43 \n", "3 SOUL K/M BAT MATE VERM MODERNO V2 3,7g 1 25,19 25,19 \n", "4 GLAM BAT MICRO PLUMP ROSADO NOBRE 1,8g 1 24,43 24,43 " ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = df.rename(columns={'cod':'SKU'})\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 36, "id": "59aad387", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 20970\n", "1 20970\n", "2 20970\n", "3 20970\n", "4 20970\n", "Name: pdv_tratado, dtype: object" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['pdv_tratado'] = df['PDV'].str.split('-').str[0]\n", "df['pdv_tratado'].head()" ] }, { "cell_type": "code", "execution_count": 37, "id": "3f7da7c8", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SKUorigem
072898BOT
149586BOT
25221BOT
328644BOT
485154BOT
\n", "
" ], "text/plain": [ " SKU origem\n", "0 72898 BOT\n", "1 49586 BOT\n", "2 5221 BOT\n", "3 28644 BOT\n", "4 85154 BOT" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_marca = pd.read_csv(r\"C:\\Users\\joao.herculano\\OneDrive - GRUPO GINSENG\\Documentos\\CONSULTAS BANCO DE DADOS\\skus e marcas 20250828.csv\",sep=';')\n", "\n", "df_marca.head()" ] }, { "cell_type": "code", "execution_count": 38, "id": "bd585612", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nfdata_vendacanalPDVSKUdescqtdvalor_unitvalor_totalpdv_tratadoorigem
03289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO52276KIT SACOLA P INST EUD 2022 C/10 VD16,376,3720970EUD
13289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO49016ACCORDES DES BDY SPR 100ml V6140,7240,7220970BOT
23289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO50481GLAM BAT MICRO PLUMP VERMELHO UNICO 1,8g124,4324,4320970EUD
33289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO52334SOUL K/M BAT MATE VERM MODERNO V2 3,7g125,1925,1920970EUD
43289227/08/2025Venda Direta20970-COMERCIO-ER SAO SEBASTIAO50483GLAM BAT MICRO PLUMP ROSADO NOBRE 1,8g124,4324,4320970EUD
\n", "
" ], "text/plain": [ " nf data_venda canal PDV SKU \\\n", "0 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 52276 \n", "1 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 49016 \n", "2 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 50481 \n", "3 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 52334 \n", "4 32892 27/08/2025 Venda Direta 20970-COMERCIO-ER SAO SEBASTIAO 50483 \n", "\n", " desc qtd valor_unit valor_total \\\n", "0 KIT SACOLA P INST EUD 2022 C/10 VD 1 6,37 6,37 \n", "1 ACCORDES DES BDY SPR 100ml V6 1 40,72 40,72 \n", "2 GLAM BAT MICRO PLUMP VERMELHO UNICO 1,8g 1 24,43 24,43 \n", "3 SOUL K/M BAT MATE VERM MODERNO V2 3,7g 1 25,19 25,19 \n", "4 GLAM BAT MICRO PLUMP ROSADO NOBRE 1,8g 1 24,43 24,43 \n", "\n", " pdv_tratado origem \n", "0 20970 EUD \n", "1 20970 BOT \n", "2 20970 EUD \n", "3 20970 EUD \n", "4 20970 EUD " ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = pd.merge(df,df_marca,on='SKU',how='left')\n", "\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 40, "id": "c6dd1a57", "metadata": {}, "outputs": [], "source": [ "\n", "df['valor_unit'] = df['valor_unit'].str.replace(',','.')\n", "\n", "df['valor_unit'] = df['valor_unit'].astype(float)\n" ] }, { "cell_type": "code", "execution_count": 41, "id": "c6fa06d5", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "nf int64\n", "data_venda object\n", "canal object\n", "PDV object\n", "SKU int64\n", "desc object\n", "qtd int64\n", "valor_unit float64\n", "valor_total float64\n", "pdv_tratado object\n", "origem object\n", "dtype: object" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df['valor_total'] = df['valor_unit']*df['qtd']\n", "\n", "df.dtypes" ] }, { "cell_type": "code", "execution_count": 43, "id": "9b76b521", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nfpdv_tratadoorigemvalor_total
0136724293BOT225.16
1136824293BOT2310.04
2136924293BOT1033.93
3136924293EUD67.82
4137024293BOT441.82
...............
423632422523711BOT216.80
423732422623711BOT267.40
423832422723711BOT7724.42
423932422723711EUD7332.19
424032422723711QDB31.36
\n", "

4241 rows × 4 columns

\n", "
" ], "text/plain": [ " nf pdv_tratado origem valor_total\n", "0 1367 24293 BOT 225.16\n", "1 1368 24293 BOT 2310.04\n", "2 1369 24293 BOT 1033.93\n", "3 1369 24293 EUD 67.82\n", "4 1370 24293 BOT 441.82\n", "... ... ... ... ...\n", "4236 324225 23711 BOT 216.80\n", "4237 324226 23711 BOT 267.40\n", "4238 324227 23711 BOT 7724.42\n", "4239 324227 23711 EUD 7332.19\n", "4240 324227 23711 QDB 31.36\n", "\n", "[4241 rows x 4 columns]" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_agg_marca = df.groupby(['nf','pdv_tratado','origem'])['valor_total'].sum().reset_index().sort_values('nf')\n", "df_agg_marca" ] }, { "cell_type": "code", "execution_count": 45, "id": "88f5968f", "metadata": {}, "outputs": [], "source": [ "caminho = r'C:\\Users\\joao.herculano\\Documents\\df_agg_vendaBA.xlsx'\n", "\n", "with pd.ExcelWriter(caminho, engine='xlsxwriter') as writer:\n", " df.to_excel(writer, sheet_name='df', index=False)\n", " df_agg_marca.to_excel(writer, sheet_name='df_agg_marca', index=False)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.2" } }, "nbformat": 4, "nbformat_minor": 5 }