# A veces necesitamos instalar nuevas librerías en nuestros proyectos
!pip install openpyxl==3.0.10
Collecting openpyxl==3.0.10
Downloading openpyxl-3.0.10-py2.py3-none-any.whl (242 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 242.1/242.1 KB 12.8 MB/s eta 0:00:00
Collecting et-xmlfile
Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
Installing collected packages: et-xmlfile, openpyxl
Successfully installed et-xmlfile-1.1.0 openpyxl-3.0.10
WARNING: You are using pip version 22.0.4; however, version 22.1.2 is available.
You should consider upgrading via the '/root/venv/bin/python -m pip install --upgrade pip' command.
# imports
import numpy as np
import pandas as pd
import sqlite3 as sql3
import matplotlib.pyplot as plt
import seaborn as sns
import openpyxl #para levantar excel de otros paqueter no office
# Levanto los datos en 3 diferentes dataframes
#Articulos
conn = sql3.connect('/work/data/articles.db')
sql_query = pd.read_sql_query('SELECT * FROM articles',conn)
df_articles = pd.DataFrame(sql_query, columns= ['article_id', 'article_name', 'unit_price'])
#df_articles
#Vendedores
df_sellers = pd.read_excel('/work/data/sellers.xlsx', index_col=0)
#df_sellers
#Ordenes
df_orders = pd.read_csv('/work/data/orders.csv')
df_orders
order_idint64
15024 - 16023
weekint64
1 - 4
0
15024
1
1
15025
1
2
15026
1
3
15027
1
4
15028
1
5
15029
1
6
15030
1
7
15031
1
8
15032
1
9
15033
1
# Exploración del df de artículos
print('Muestra de datos')
print(df_articles.head())
print('\nForma del dataframe')
print(df_articles.shape)
print ('\nBuscar valores nulos')
print(df_articles.isnull().sum())
print('\nFormato de los datos del dataframe')
print(df_articles.dtypes)
Muestra de datos
article_id article_name unit_price
0 20015 Smartphone 525.00
1 20016 Full Pc 2127.81
2 20017 Monitor 230.00
3 20018 Tablet 130.00
4 20019 Desk 130.10
Forma del dataframe
(31, 3)
Buscar valores nulos
article_id 0
article_name 0
unit_price 0
dtype: int64
Formato de los datos del dataframe
article_id int64
article_name object
unit_price object
dtype: object
# Exploración del df de vendedores
print('Muestra de datos')
print(df_sellers.head())
print('\nForma del dataframe')
print(df_sellers.shape)
print ('\nBuscar valores nulos')
print(df_sellers.isnull().sum())
print('\nFormato de los datos del dataframe')
print(df_sellers.dtypes)
Muestra de datos
seller_name
seller_id
1 Aveline Swanwick
2 Jase Doy
3 Oliviero Charkham
4 Cornie Wynrehame
5 Ewell Peres
Forma del dataframe
(15, 1)
Buscar valores nulos
seller_name 0
dtype: int64
Formato de los datos del dataframe
seller_name object
dtype: object
# Exploración del df de órdenes
print('Muestra de datos')
print(df_orders.head())
print('\nForma del dataframe')
print(df_orders.shape)
print ('\nBuscar valores nulos')
print(df_orders.isnull().sum())
print('\nFormato de los datos del dataframe')
print(df_orders.dtypes)
Muestra de datos
order_id week article_id quantity seller_id country_name
0 15024 1 20039 10 10 Peru
1 15025 1 20029 15 5 Peru
2 15026 1 20024 5 14 Bolivia
3 15027 1 20018 9 14 Brazil
4 15028 1 20035 6 15 Mexico
Forma del dataframe
(1000, 6)
Buscar valores nulos
order_id 0
week 0
article_id 0
quantity 0
seller_id 0
country_name 0
dtype: int64
Formato de los datos del dataframe
order_id int64
week int64
article_id int64
quantity int64
seller_id int64
country_name object
dtype: object
#Cambiar a float los precios untitarios
df_articles['unit_price'] = df_articles['unit_price'].astype(float)
print(df_articles.dtypes)
article_id int64
article_name object
unit_price float64
dtype: object
#Creo una copia del df_orders
my_df = df_orders.copy()
# Cambio el indice del df_articles
df_articles.set_index('article_id', inplace=True)
# Agrego 3 columnas y pongo el campo que me va a servir de "ancla" para buscar la información real.
my_df = my_df.assign(article_name = my_df['article_id'])
my_df = my_df.assign(total_amount = my_df['article_id'])
my_df = my_df.assign(seller_name = my_df['seller_id'])
print(df_articles.head())
article_name unit_price
article_id
20015 Smartphone 525.00
20016 Full Pc 2127.81
20017 Monitor 230.00
20018 Tablet 130.00
20019 Desk 130.10
#Reemplazar los valores reales en el df
for i in range (len(my_df.index)): #len(my_df.index) devuelva la cantidad de filas (registros)
#reemplazo el nombre del articulo usando el id guardado en my_df
article = df_articles.loc[my_df.loc[i]['article_id']]['article_name']
my_df.loc[i, 'article_name'] = article
#reemplazo el nombre del vendedor usando el id guardado en my_df
my_df.loc[i, 'seller_name'] = df_sellers.loc[my_df.loc[i]['seller_id']]['seller_name']
#busco el pecio unitario y lo multiplico por la cantidad de unidades vendidas.
my_df.loc[i, 'total_amount'] = df_articles.loc[my_df.loc[i]['article_id']]['unit_price'] * my_df.loc[i, 'quantity']
#elimino las columnas que no utilizo
my_df.drop(['article_id', 'seller_id', 'order_id'], axis='columns', inplace=True)
print(my_df.head())
week quantity country_name article_name total_amount seller_name
0 1 10 Peru Water Cooling 675.0 Cirilo Grandham
1 1 15 Peru Mouse 454.5 Ewell Peres
2 1 5 Bolivia Netbook 725.0 Janel O'Curran
3 1 9 Brazil Tablet 1170.0 Janel O'Curran
4 1 6 Mexico Case 227.4 Daisie Slograve
# RESOLUCIÓN ANALÍTICA
my_df2 = my_df.groupby('article_name').sum()
por_cantidad = my_df2.sort_values('quantity', ascending=False)
print('***** Artículo que más se vendió *****\n')
print(por_cantidad['quantity'].head(1))
***** Artículo que más se vendió *****
article_name
HDD 413
Name: quantity, dtype: int64
# RESOLUCIÓN ANALÍTICA
my_df2 = my_df.groupby('article_name').sum()
por_ingresos = my_df2.sort_values('total_amount', ascending=False).head()
print('***** Artículo que más ingresos generó *****\n')
print(por_ingresos['total_amount'].head(1))
***** Artículo que más ingresos generó *****
article_name
Full Pc 538335.93
Name: total_amount, dtype: float64
por_ingresos2 = my_df2.sort_values('total_amount', ascending=False)
otros_acum= por_ingresos2.iloc[5:]['total_amount'].sum()
#otros_acum guarda el total de ingresos generados por los otros productos
#que no sean los primeros 5 que aparecen en por_ingresos
#Comprobando
print('Total de ingresos que generan los 5 primeros productos: ',por_ingresos['total_amount'].sum())
print('Total de ingresos que generan el resto de productors: ',otros_acum)
print('Sumando los dos valores anteriores: ',por_ingresos['total_amount'].sum()+otros_acum)
print('\nTotal de ingresos de todos los productos calculado \n desde el dataframe agrupado por productos: ',my_df2['total_amount'].sum())
Total de ingresos que generan los 5 primeros productos: 1059683.4099999997
Total de ingresos que generan el resto de productors: 416124.43
Sumando los dos valores anteriores: 1475807.8399999996
Total de ingresos de todos los productos calculado
desde el dataframe agrupado por productos: 1475807.8399999999
por_ingresos.loc['otros']=[ 0, 0, otros_acum ]
#Añado como fila a Otros con los ingresos que generaron el resto de los productos
#Solo para que los porcentajes de la grafica pie sean reales
print(por_ingresos['total_amount'])
article_name
Full Pc 538335.93
Notebook 251000.00
Smartphone 152250.00
Chair 69477.48
Tablet 48620.00
otros 416124.43
Name: total_amount, dtype: float64
# RESOLUCIÓN ANALÍTICA
print('***** Mejor vendedor del mes *****\n')
df3 =my_df.groupby('seller_name').sum().sort_values('total_amount',ascending=False)
print(df3[['quantity']+['total_amount']].head(1))
***** Mejor vendedor del mes *****
quantity total_amount
seller_name
Janel O'Curran 703 192832.47
# RESOLUCIÓN ANALÍTICA
print('***** Variación en las ventas a lo largo del mes *****\n')
df4 = my_df.groupby('week').sum()
print(df4)
***** Variación en las ventas a lo largo del mes *****
quantity total_amount
week
1 2449 507458.81
2 2444 415364.44
3 2114 329140.03
4 1058 223844.56
df4.index = df4.index.astype(str)
#Cambiamos el tipo de dato en index para graficar mejor
# RESOLUCIÓN ANALITICA
print('***** País al que se vende menos *****\n')
df5 =my_df.groupby('country_name').sum().sort_values('quantity',ascending=True)
print(df5[['quantity'] + ['total_amount']].head(1))
***** País al que se vende menos *****
quantity total_amount
country_name
Puerto Rico 12 1265.43
# RESOLUCIÓN ANALÍTICA
#creo el DF mart que contiene los 3 articulos que generaron más ingresos
mart = my_df2.sort_values('total_amount', ascending=False).head(3)
mart = mart.rename_axis('article_name').reset_index()
#creo el DF mvend que contiene los 5 vendedores que generaron mas ingresos
mvend = df3.head(5)
mvend = mvend.rename_axis('seller_name').reset_index()
#Creo el DF dmm extrayendo de my_df la información en donde se cumpla la condicione que el vendedor
#sea alguno de los TOP 5 y que ademmás haya vendido uno de los TOP 3 articulos
dfmm = pd.DataFrame(columns= ['seller_name', 'article_name','quantity'])
c=0
for i in range (len(my_df.index)): #len(my_df.index) devuelva la cantidad de filas (registros)
for j in range (len(mart.index)):
for k in range (len(mvend.index)):
if (my_df.loc[i]['seller_name'] == mvend.iloc[k]['seller_name']) & (my_df.loc[i]['article_name'] == mart.loc[j]['article_name']):
dfmm.loc[c] = [mvend.iloc[k]['seller_name'], mart.iloc[j]['article_name'], my_df.loc[i]['quantity']]
c= c+1
print(dfmm.head())
seller_name article_name quantity
0 Daisie Slograve Full Pc 2
1 Janel O'Curran Full Pc 9
2 Vasily Danilyuk Notebook 11
3 Vasily Danilyuk Smartphone 1
4 Daisie Slograve Full Pc 12
#RESOLUCIÓN ANALÍTICA
print('\n***** Top 3 de articulos que generan más ingresos *****\n')
print(mart[['article_name']+['quantity']+['total_amount']])
print('\n***** Top 5 de mejores vendedores *****\n')
print(mvend[['seller_name']+['quantity']+['total_amount']])
print('\n***** Unidades vendidas de los Top3 articulos por los top 3 vendedores*****\n')
df6 =dfmm.groupby(['seller_name','article_name']).sum()
print(df6)
***** Top 3 de articulos que generan más ingresos *****
article_name quantity total_amount
0 Full Pc 253 538335.93
1 Notebook 251 251000.00
2 Smartphone 290 152250.00
***** Top 5 de mejores vendedores *****
seller_name quantity total_amount
0 Janel O'Curran 703 192832.47
1 Brockie Patience 441 142709.88
2 Oliviero Charkham 555 141329.76
3 Vasily Danilyuk 521 129157.55
4 Daisie Slograve 554 120520.11
***** Unidades vendidas de los Top3 articulos por los top 3 vendedores*****
quantity
seller_name article_name
Brockie Patience Full Pc 46
Notebook 5
Smartphone 21
Daisie Slograve Full Pc 14
Notebook 31
Smartphone 20
Janel O'Curran Full Pc 56
Notebook 17
Smartphone 32
Oliviero Charkham Full Pc 35
Notebook 16
Smartphone 25
Vasily Danilyuk Full Pc 25
Notebook 25
Smartphone 40
# RESOLUCIÓN ANALITICA
print('***** Mejor vendedor a Brasil *****\n')
#Agrupamos los datos por país y en cada país por vendedor
df7 =my_df.groupby(['country_name','seller_name',]).sum()
#Seleccionamos solo los datos de Brazil para encontrar al mejor vendedor de ese país
de_brasil = df7.loc['Brazil'].sort_values('total_amount',ascending=False)
print(de_brasil[['total_amount']+['quantity']].head(1))
***** Mejor vendedor a Brasil *****
total_amount quantity
seller_name
Daisie Slograve 65283.28 226