# A veces necesitamos instalar nuevas librerías en nuestros proyectos
!pip install openpyxl==3.0.10
Requirement already satisfied: openpyxl==3.0.10 in /root/venv/lib/python3.9/site-packages (3.0.10)
Requirement already satisfied: et-xmlfile in /root/venv/lib/python3.9/site-packages (from openpyxl==3.0.10) (1.1.0)
WARNING: You are using pip version 22.0.4; however, version 22.3.1 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 openpyxl
import matplotlib.pyplot as plt
import seaborn as sns
# 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'])
# Vendedores
df_sellers = pd.read_excel('/work/data/sellers.xlsx', index_col=0)
# Ordenes
df_orders = pd.read_csv('/work/data/orders.csv')
# Exploración del df de artículos
print("Muestra de datos")
print(df_articles.head()) # head() 5 Filas por defecto
print('\nFormato del dataframe')
print(df_articles.shape)
print('\nBusqueda de valores nulls por columna')
print(df_articles.isnull().sum())
print('\nFormato de los datos por columna')
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
Formato del dataframe
(31, 3)
Busqueda de valores nulls por columna
article_id 0
article_name 0
unit_price 0
dtype: int64
Formato de los datos por columna
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()) # head() 5 Filas por defecto
print('\nFormato del dataframe')
print(df_sellers.shape)
print('\nBusqueda de valores nulls por columna')
print(df_sellers.isnull().sum())
print('\nFormato de los datos por columna')
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
Formato del dataframe
(15, 1)
Busqueda de valores nulls por columna
seller_name 0
dtype: int64
Formato de los datos por columna
seller_name object
dtype: object
# Exploración del df de órdenes
print('Muestra de datos')
print(df_orders.head()) # head() 5 Filas por defecto
print('\nFormato del dataframe')
print(df_orders.shape)
print('\nBusqueda de valores nulls por columna')
print(df_orders.isnull().sum())
print('\nFormato de los datos por columna')
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
Formato del dataframe
(1000, 6)
Busqueda de valores nulls por columna
order_id 0
week 0
article_id 0
quantity 0
seller_id 0
country_name 0
dtype: int64
Formato de los datos por columna
order_id int64
week int64
article_id int64
quantity int64
seller_id int64
country_name object
dtype: object
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
my_df = pd.merge(df_orders, df_articles, on = 'article_id', how = 'left').merge(df_sellers, on = 'seller_id', how = 'left')
my_df['total_amount'] = my_df['quantity']*my_df['unit_price']
my_df.drop(['order_id', 'article_id', 'seller_id','unit_price'], axis='columns', inplace=True)
my_df = my_df.reindex(columns=['week', 'quantity', 'article_name', 'total_amount','seller_name','country_name'])
print(my_df.head())
week quantity article_name total_amount seller_name country_name
0 1 10 Water Cooling 675.0 Cirilo Grandham Peru
1 1 15 Mouse 454.5 Ewell Peres Peru
2 1 5 Netbook 725.0 Janel O'Curran Bolivia
3 1 9 Tablet 1170.0 Janel O'Curran Brazil
4 1 6 Case 227.4 Daisie Slograve Mexico
# RESOLUCIÓN ANALÍTICA
df7 = my_df.groupby(by='article_name').sum().sort_values('quantity', ascending=False).rename(columns={'quantity':'Cantidad', 'total_amount' : 'Cantidad Total'})
#pd.options.display.float_format= '$ {:,.2f}'.format
# print(df7[['quantity']].head())
print(df7[['Cantidad','Cantidad Total']].head())
Cantidad Cantidad Total
article_name
HDD 413 22558.06
Tablet 374 48620.00
SDD 372 8184.00
Mouse 322 9756.60
Netbook 320 46400.00
df7graf = df7[['Cantidad','Cantidad Total']].head()
sns.barplot(data=df7graf, x=df7graf.index, y='Cantidad', palette='Paired_r')
plt.suptitle('Articulos mas vendidos por cantidad')
# RESOLUCIÓN ANALÍTICA
df2 = my_df[['article_name','total_amount','quantity']].groupby(by='article_name').sum().sort_values('total_amount', ascending=False).head()
pd.options.display.float_format= '$ {:,.2f}'.format
print(df2)
total_amount quantity
article_name
Full Pc $ 538,335.93 253
Notebook $ 251,000.00 251
Smartphone $ 152,250.00 290
Chair $ 69,477.48 207
Tablet $ 48,620.00 374
# RESOLUCIÓN GRÁFICA
exp = [0.05, 0, 0, 0, 0]
plt.pie(x=df2['total_amount'], labels=df2.index, autopct='%1.1f%%', startangle=280, explode = exp, pctdistance = 0.75)
plt.show()
# RESOLUCIÓN ANALÍTICA
df4 = my_df.groupby(by='seller_name').sum().sort_values('total_amount', ascending=False)
#pd.options.display.float_format= '$ {:,.2f}'.format
print(f"El bono para el mejor vendedor del mes con un total de ${df4.iloc[0,2]:.2f} en ventas es para {df4.index[0]} \n")
print(df4[['quantity'] + ['total_amount']].head())
El bono para el mejor vendedor del mes con un total de $192832.47 en ventas es para Janel O'Curran
quantity total_amount
seller_name
Janel O'Curran 703 $ 192,832.47
Brockie Patience 441 $ 142,709.88
Oliviero Charkham 555 $ 141,329.76
Vasily Danilyuk 521 $ 129,157.55
Daisie Slograve 554 $ 120,520.11
# RESOLUCIÓN GRÁFICA
plt.gca().xaxis.grid(True)
plt.barh(df4.index, df4['total_amount'],color = ['gold' if x in df4.index[0] else 'red' for x in df4.index])
plt.gca().invert_yaxis()
plt.xticks(rotation=0)
plt.show()
# RESOLUCIÓN ANALÍTICA
df5 = my_df.groupby(by='week').sum().sort_values('total_amount', ascending=False)
pd.options.display.float_format= '$ {:,.2f}'.format
print(df5[['total_amount'] +['quantity'] ])
total_amount quantity
week
1 $ 507,458.81 2449
2 $ 415,364.44 2444
3 $ 329,140.03 2114
4 $ 223,844.56 1058
# RESOLUCIÓN GRÁFICA
def show_values(axs, orient="v", space=.01):
def _single(ax):
if orient == "v":
for p in ax.patches:
_x = p.get_x() + p.get_width() / 2
_y = p.get_y() + p.get_height() + (p.get_height()*0.01)
value = '{:.2f}'.format(p.get_height())
ax.text(_x, _y, value, ha="center")
if isinstance(axs, np.ndarray):
for idx, ax in np.ndenumerate(axs):
_single(ax)
else:
_single(axs)
g= sns.barplot(x=df5.index, y=df5['total_amount'], palette='Paired')
show_values(g)
plt.show()
# RESOLUCIÓN ANALÍTICA
dfventasxpais = my_df.groupby(by='country_name').sum().sort_values('total_amount', ascending=False).rename(columns={'total_amount' : 'Cantidad Total','quantity':'Unidades Vendidas'})
dfventasxpais_v2 = my_df.groupby(by='country_name')['quantity'].agg(['count'])
dfventasxpais['Cantidad de ventas'] = dfventasxpais_v2
print(dfventasxpais[['Cantidad Total'] + ['Cantidad de ventas'] + ['Unidades Vendidas']])
Cantidad Total Cantidad de ventas Unidades Vendidas
country_name
Brazil $ 441,271.85 315 2515
Argentina $ 205,832.78 113 947
Colombia $ 177,514.29 111 881
Peru $ 161,421.12 125 1027
Mexico $ 138,619.99 101 846
Venezuela $ 77,684.52 37 320
El Salvador $ 57,391.26 16 111
Guatemala $ 52,579.25 29 202
Honduras $ 36,763.56 38 303
Costa Rica $ 34,606.50 19 145
Chile $ 24,660.98 28 231
Bolivia $ 22,682.80 19 181
Uruguay $ 17,843.09 12 92
Ecuador $ 17,475.30 18 129
Paraguay $ 8,195.12 17 123
Puerto Rico $ 1,265.43 2 12
# RESOLUCIÓN GRÁFICA
fig, ax1 = plt.subplots(figsize=(10,5))
dfventasxpais = dfventasxpais.head()
add_value_label=(dfventasxpais.index,dfventasxpais[['Cantidad Total']])
sns.barplot(data = dfventasxpais, x = dfventasxpais.index, y='Cantidad Total', ax=ax1, palette ='coolwarm')
plt.xticks(rotation=30)
ax2 = ax1.twinx()
sns.lineplot(data = dfventasxpais, x=dfventasxpais.index, y='Cantidad de ventas', ax=ax2, color='C2',linewidth = 3, marker="o")
plt.xticks(rotation=30)
plt.suptitle("Top 5 Paises" ,
fontsize = 'x-large' ,
fontweight = 'bold' )
ax1.set_ylabel("Ventas Totales")
ax2.set_ylabel("Cantidad de ventas (linea)")
plt.show()
# RESOLUCIÓN ANALÍTICA
df2paises = my_df[my_df['country_name'] == 'Brazil']
df_arg = my_df[my_df['country_name'] == 'Argentina']
df2paises = df2paises.append(df_arg)
df2paises = df2paises.groupby(['article_name','country_name'],as_index=False)['quantity'].sum().sort_values('quantity', ascending=False)
dftopar = df2paises[df2paises['country_name'] == 'Argentina'].head()
dftopbr = df2paises[df2paises['country_name'] == 'Brazil'].head()
print(df2paises[df2paises['country_name'] == 'Brazil'].head())
print(df2paises[df2paises['country_name'] == 'Argentina'].head())
article_name country_name quantity
49 Tablet Brazil 156
13 HDD Brazil 119
42 SDD Brazil 117
5 Chair Brazil 112
34 Pci Express Port Brazil 105
article_name country_name quantity
0 CPU Argentina 104
41 SDD Argentina 73
12 HDD Argentina 68
39 Range Extender Argentina 58
8 Fan Cooler Argentina 47
# RESOLUCIÓN GRÁFICA
plt.figure(figsize=(14, 6))
sns.set_style('darkgrid')
plt.subplot(1,2,2)
sns.barplot(data = dftopar,x='article_name', y='quantity', palette={'CPU':'m', 'HDD':'g', 'SDD':'r','Range Extender':'c', 'Fan Cooler':'y',})
plt.title("Argentina", fontsize=15)
plt.xlabel("Articulos", fontsize=12)
plt.ylabel("Cantidad de ventas")
plt.subplot(1,2,1)
sns.barplot(data = dftopbr,x='article_name', y='quantity', palette={'Tablet':'b', 'HDD':'g', 'SDD':'r','Chair':'crimson', 'Pci Express Port':'yellow', 'CPU':'k'})
plt.title("Brazil", fontsize=15)
plt.xlabel("Articulos", fontsize=12)
plt.ylabel("Cantidad de ventas")
plt.suptitle("Articulos mas vendidos de los paises con mas ventas", fontsize=20)
# RESOLUCIÓN
dfventasxprod = my_df[my_df.article_name.isin(['Full Pc','Notebook', 'Smartphone', 'Chair', 'Tablet'])]
dfventasxprod = dfventasxprod.groupby(['article_name','week'],as_index=False).sum().sort_values(['article_name', 'week'], ascending=True).head(20)
#print(dfventasxprod[['article_name']+ ['week']+['quantity']].sort_values(by = 'article_name'))
print()
print(dfventasxprod)
article_name week quantity total_amount
0 Chair 1 52 $ 17,453.28
1 Chair 2 80 $ 26,851.20
2 Chair 3 40 $ 13,425.60
3 Chair 4 35 $ 11,747.40
4 Full Pc 1 97 $ 206,397.57
5 Full Pc 2 76 $ 161,713.56
6 Full Pc 3 49 $ 104,262.69
7 Full Pc 4 31 $ 65,962.11
8 Notebook 1 95 $ 95,000.00
9 Notebook 2 37 $ 37,000.00
10 Notebook 3 41 $ 41,000.00
11 Notebook 4 78 $ 78,000.00
12 Smartphone 1 89 $ 46,725.00
13 Smartphone 2 93 $ 48,825.00
14 Smartphone 3 73 $ 38,325.00
15 Smartphone 4 35 $ 18,375.00
16 Tablet 1 101 $ 13,130.00
17 Tablet 2 70 $ 9,100.00
18 Tablet 3 148 $ 19,240.00
19 Tablet 4 55 $ 7,150.00
plt.figure(figsize=(14, 6))
sns.barplot(data=dfventasxprod, x='week',y='quantity', hue='article_name',errorbar=None, palette ='Spectral_r')
plt.xlabel('Semana', size = 18)
plt.ylabel('Unidades vendidas', size = 15)