TP Final Integrador

# 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.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

# Levanto los datos en 3 diferentes dataframes # ARTÍCULOS 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']) print(df_articles)

    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
5        20020             Chair     335.64
6        20021             Modem      67.50
7        20022    Range Extender      20.45
8        20023          Notebook    1000.00
9        20024           Netbook     145.00
10       20025               HDD      54.62
11       20026               SDD      22.00
12       20027        Ram Memory      35.95
13       20028       Motherboard     138.38
14       20029             Mouse      30.30
15       20030        Fan Cooler       4.25
16       20031            Webcam      20.07
17       20032          Keyboard      22.60
18       20033        Headphones      23.30
19       20034           Scanner     185.00
20       20035              Case      37.90
21       20036        Video Card     131.50
22       20037               CPU     139.62
23       20038      Power Supply      43.95
24       20039     Water Cooling      67.50
25       20040          Heatsink      10.00
26       20041         Usb Cable       2.95
27       20042        Sata Cable       2.14
28       20043  Pci Express Port      10.12
29       20044        Wi-Fi Card      59.61
30       20045    Mesh Wi-Fi X 2      32.50

# VENDEDORES #df_sellers = pd.read_excel('/work/data/sellers.xlsx', index_col=0) #dormir print(df_sellers)

                 seller_name
seller_id                   
1           Aveline Swanwick
2                   Jase Doy
3          Oliviero Charkham
4           Cornie Wynrehame
5                Ewell Peres
6           Milly Christoffe
7                 Kati Innot
8              Tobin Roselli
9               Onida Cosely
10           Cirilo Grandham
11           Vasily Danilyuk
12          Brockie Patience
13           Arnold Kilkenny
14            Janel O'Curran
15           Daisie Slograve

# ÓRDENES df_orders=pd.read_csv('/work/data/orders.csv') print(df_orders)

     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
..        ...   ...         ...       ...        ...          ...
995     16019     4       20021         1          7       Brazil
996     16020     4       20040        15         15       Brazil
997     16021     4       20040         2         11     Colombia
998     16022     4       20018        14         11       Brazil
999     16023     4       20026        12          9       Brazil

[1000 rows x 6 columns]

# Exploración del df de artículos print('Muestra de los datos') print(df_articles.head()) print('\nFormato del dataframe') print(df_articles.shape) print('\nBúsqueda de valores nulos') print(df_articles.isnull().sum()) print('\nFormato de los datos por columna') print(df_articles.dtypes)

Muestra de los 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)

Búsqueda de valores nulos
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 los datos') print(df_sellers.head()) print('\nFormato del dataframe') print(df_sellers.shape) print('\nBúsqueda de valores nulos') print(df_sellers.isnull().sum()) print('\nFormato de los datos por columna') print(df_sellers.dtypes)

Muestra de los 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)

Búsqueda de valores nulos
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 los datos') print(df_orders.head()) print('\nFormato del dataframe') print(df_orders.shape) print('\nBúsqueda de valores nulos') print(df_orders.isnull().sum()) print('\nFormato de los datos por columna') print(df_orders.dtypes)

Muestra de los 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)

Búsqueda de valores nulos
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

# cambio el tipo de dato de 'unit_price' a float 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

# cambio el índice del df_articles por la columna article_id #df_articles.set_index('article_id', inplace=True) #dormir 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

# creo una copia del df_orders my_df = df_orders.copy() # agrego algunas columnas extra que necesito y las lleno con los códigos id que las vinculan con los otros df 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(my_df.head())

   order_id  week  article_id  quantity  seller_id country_name  article_name  \
0     15024     1       20039        10         10         Peru         20039   
1     15025     1       20029        15          5         Peru         20029   
2     15026     1       20024         5         14      Bolivia         20024   
3     15027     1       20018         9         14       Brazil         20018   
4     15028     1       20035         6         15       Mexico         20035   

   total_amount  seller_name  
0         20039           10  
1         20029            5  
2         20024           14  
3         20018           14  
4         20035           15

# Reemplazo los valores en las nuevas columnas del df for i in range(len(my_df.index)): #article = df_articles.loc[my_df.loc[i]['article_name']]['article_name'] #dormir #my_df.loc[i,'article_name'] = article #dormir #my_df.loc[i,'total_amount'] = my_df.loc[i,'quantity'] * df_articles.loc[my_df.loc[i]['total_amount']]['unit_price'] #dormir my_df.loc[i,'seller_name'] = df_sellers.loc[my_df.loc[i]['seller_name']]['seller_name'] print(my_df.head())

   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   

    article_name  total_amount      seller_name  
0  Water Cooling         675.0  Cirilo Grandham  
1          Mouse         454.5      Ewell Peres  
2        Netbook         725.0   Janel O'Curran  
3         Tablet        1170.0   Janel O'Curran  
4           Case         227.4  Daisie Slograve

# Borrar las columnas que no voy a utilizar my_df.drop(['order_id','article_id','seller_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

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

#RESOLUCION ANALITICA df10=(my_df.groupby('article_name').sum()).sort_values('quantity', ascending=False) print("Cantidad de artículos vendidos:") print( df10['quantity'])

Cantidad de artículos vendidos:
article_name
HDD                 413
Tablet              374
SDD                 372
Mouse               322
Netbook             320
Ram Memory          293
Motherboard         291
Pci Express Port    291
Smartphone          290
Range Extender      281
Heatsink            280
Usb Cable           273
Power Supply        269
CPU                 266
Sata Cable          264
Full Pc             253
Water Cooling       252
Notebook            251
Modem               244
Headphones          239
Webcam              229
Desk                223
Scanner             221
Mesh Wi-Fi X 2      213
Video Card          209
Monitor             208
Chair               207
Case                206
Fan Cooler          205
Keyboard            165
Wi-Fi Card          141
Name: quantity, dtype: int64

# RESOLUCIÓN GRÁFICA sns.barplot(x=df10.index,y=df10['quantity'],data=df10,order=df10.sort_values('quantity', ascending=False).index, color= "salmon") plt.xticks(rotation=90) plt.tick_params(axis='x', colors='maroon', direction='out', length=9, width=3) plt.tick_params(axis='y', colors='maroon', direction='out', length=9, width=3) plt.title(label="Artículo más vendido en unidades", fontsize=18,color="rosybrown") plt.xlabel('Article Name', fontweight='bold', color = 'maroon', fontsize='10', horizontalalignment='center') plt.ylabel('Quantity', fontweight='bold', color = 'maroon', fontsize='10', horizontalalignment='center') plt.show()

# RESOLUCIÓN ANALÍTICA df33=(my_df.groupby('article_name').sum()).sort_values('total_amount', ascending=False).head(5) print ('Ingreso por artículo') print(df33['total_amount'])

Ingreso por artículo
article_name
Full Pc       538335.93
Notebook      251000.00
Smartphone    152250.00
Chair          69477.48
Tablet         48620.00
Name: total_amount, dtype: float64

# RESOLUCIÓN GRÁFICA x= df33['total_amount'] c= ['darkmagenta', 'yellowgreen', 'aquamarine', 'darkorange','yellow'] l= df33.index plt.pie(x,labels=l, colors=c, shadow=True) plt.title("Ingreso por artículo",fontsize=18,color="darkred") plt.show()

# RESOLUCIÓN ANALÍTICA df3=(my_df.groupby('seller_name').sum()).sort_values('total_amount', ascending=False) print(df3['total_amount'])

seller_name
Janel O'Curran       192832.47
Brockie Patience     142709.88
Oliviero Charkham    141329.76
Vasily Danilyuk      129157.55
Daisie Slograve      120520.11
Aveline Swanwick     118874.33
Arnold Kilkenny       94552.04
Kati Innot            83704.62
Jase Doy              80628.31
Ewell Peres           78144.32
Onida Cosely          77373.37
Milly Christoffe      61733.69
Tobin Roselli         56984.42
Cornie Wynrehame      52253.57
Cirilo Grandham       45009.40
Name: total_amount, dtype: float64

# RESOLUCIÓN GRÁFICA sns.barplot(x=df3.index,y=df3['total_amount'],data=df3,order=df3.sort_values('total_amount', ascending=False).index) plt.xticks(rotation=90) plt.tick_params(axis='x', colors='dimgrey', direction='out', length=9, width=3) plt.tick_params(axis='y', colors='dimgrey', direction='out', length=9, width=3) plt.title(label="Monto total de ventas por vendedor", fontsize=18,color="rosybrown") plt.xlabel('Seller', fontweight='bold', color = 'dimgrey', fontsize='10', horizontalalignment='center') plt.ylabel('Total Amount', fontweight='bold', color = 'dimgrey', fontsize='10', horizontalalignment='center') plt.show()

# RESOLUCIÓN ANALÍTICA df4=(my_df.groupby('week').sum()).sort_values('total_amount', ascending=False) print(df4[['total_amount']+ ['quantity']])

      total_amount  quantity
week                        
1        507458.81      2449
2        415364.44      2444
3        329140.03      2114
4        223844.56      1058

# RESOLUCIÓN GRÁFICA sns.barplot(x=df4.index,y=df4['total_amount'],data=df4,order=df4.sort_values('total_amount', ascending=False).index, palette= "dark:salmon_r") plt.xticks(rotation=0) plt.tick_params(axis='x', colors='indianred', direction='out', length=9, width=3) plt.tick_params(axis='y', colors='indianred', direction='out', length=9, width=3) plt.title(label="Monto total de ventas por semana", fontsize=18,color="rosybrown") plt.xlabel('Week', fontweight='bold', color = 'indianred', fontsize='10', horizontalalignment='center') plt.ylabel('Total Amount', fontweight='bold', color = 'indianred', fontsize='10', horizontalalignment='center') plt.show()

# RESOLUCIÓN df5=(my_df.groupby('country_name').sum()).sort_values('total_amount', ascending=False) print(df5['total_amount'])

country_name
Brazil         441271.85
Argentina      205832.78
Colombia       177514.29
Peru           161421.12
Mexico         138619.99
Venezuela       77684.52
El Salvador     57391.26
Guatemala       52579.25
Honduras        36763.56
Costa Rica      34606.50
Chile           24660.98
Bolivia         22682.80
Uruguay         17843.09
Ecuador         17475.30
Paraguay         8195.12
Puerto Rico      1265.43
Name: total_amount, dtype: float64

# RESOLUCIÓN GRÁFICA sns.barplot(x=df5.index,y=df5['total_amount'],data=df5,order=df5.sort_values('total_amount', ascending=False).index, color="springgreen") plt.xticks(rotation=90) plt.tick_params(axis='x', colors='teal', direction='out', length=9, width=3) plt.tick_params(axis='y', colors='teal', direction='out', length=9, width=3) plt.title(label="Monto total de ventas por país", fontsize=18,color="steelblue") plt.xlabel('Country', fontweight='bold', color = 'teal', fontsize='10', horizontalalignment='center') plt.ylabel('Total Amount', fontweight='bold', color = 'teal', fontsize='10', horizontalalignment='center') plt.show()

# DATAFRAME CLASIFICANDO PAISES SEGUN MERCOSUR O NO df120=my_df.sort_values('country_name') df120['Mercosur']=df120['country_name'].isin(['Argentina', 'Brazil', 'Bolivia', 'Paraguay','Venezuela']) df120['big_ sellers']= df120['total_amount'] df120

weekint64

1 - 4

quantityint64

1 - 15

499

485

761

479

769

119

472

266

784

462

g=sns.catplot(x="week", y="quantity", hue="Mercosur", kind="boxen", data=df120, palette= "magma", saturation=0.5, width=0.9, dodge=True, linewidth=0.9) plt.tick_params(axis='x', colors='rebeccapurple', direction='out', length=9, width=3) plt.tick_params(axis='y', colors='rebeccapurple', direction='out', length=9, width=3) plt.title(label="Cantidad de ventas por semana", fontsize=18,color="mediumvioletred") plt.xlabel('Week', fontweight='bold', color = 'rebeccapurple', fontsize='10', horizontalalignment='center') plt.ylabel('Quantity', fontweight='bold', color = 'rebeccapurple', fontsize='10', horizontalalignment='center') plt.show(g)

df100=(my_df.groupby('country_name').mean()).sort_values('total_amount', ascending=False) print('Monto promedio de compras por pais:') print(df5['total_amount'])

Monto promedio de compras por pais:
country_name
Brazil         441271.85
Argentina      205832.78
Colombia       177514.29
Peru           161421.12
Mexico         138619.99
Venezuela       77684.52
El Salvador     57391.26
Guatemala       52579.25
Honduras        36763.56
Costa Rica      34606.50
Chile           24660.98
Bolivia         22682.80
Uruguay         17843.09
Ecuador         17475.30
Paraguay         8195.12
Puerto Rico      1265.43
Name: total_amount, dtype: float64

#SOLUCIÓN GRAFICA graf= sns.barplot(x=df100.index,y=df100['total_amount'],data=df100,order=df100.sort_values('total_amount', ascending=False).index, palette="viridis", saturation=0.6, errcolor='.26') plt.xticks(rotation=90) plt.tick_params(axis='x', colors='navy', direction='out', length=9, width=3) plt.tick_params(axis='y', colors='navy', direction='out', length=9, width=3) plt.title(label="Ventas Totales por país", fontsize=18,color="forestgreen") plt.xlabel('Country', fontweight='bold', color = 'navy', fontsize='10', horizontalalignment='center') plt.ylabel('Total Amount', fontweight='bold', color = 'navy', fontsize='10', horizontalalignment='center') plt.show(graf)