TP Final Integrador Emmanuel Dopazo R

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