TP Final Integrador - 22045 - Descalzo Maria Eugenia--

# 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']) # 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('\nBúsqueda 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)

Búsqueda 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('\nBúsqueda 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)

Búsqueda 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('\nBúsqueda 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)

Búsqueda 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 = df_orders.copy() # shallow copy o copia superficial (otro Objeto alojado en la memoria) # Cambio el índice del df de artículos df_articles.set_index('article_id', inplace=True) 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']) # df_articles # print() # my_df for i in range(max(my_df.count())): # print(i) # SINTAXIS: df_articles.loc[indice][columna] # [indice]: va a ser el dato que obtengo de [my_df.loc[i]['article_id']] # o sea, tomo registro a registro el article_id y lo uso para extraer el nombre del artículo (article_name) de df_articles # print(df_articles.loc[my_df.loc[i]['article_id']]['article_name']) article = df_articles.loc[my_df.loc[i]['article_id']]['article_name'] # print(article) # Asignar a cada valor id de la columna 'article_name' (my_df) el nombre del artículo my_df.loc[i, 'article_name'] = article # my_df # hacemos lo mismo con total_amount my_df.loc[i, 'total_amount'] = my_df.loc[i, 'quantity']*df_articles.loc[my_df.loc[i]['article_id']]['unit_price'] # Columna de seller name my_df.loc[i, 'seller_name'] = df_sellers.loc[my_df.loc[i]['seller_id']]['seller_name'] # elimimo las columnas que no necesito del df my_df.drop(['order_id', 'article_id', 'seller_id'], axis='columns', inplace=True) print(my_df)

     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
..    ...       ...          ...            ...           ...              ...
995     4         1       Brazil          Modem          67.5       Kati Innot
996     4        15       Brazil       Heatsink         150.0  Daisie Slograve
997     4         2     Colombia       Heatsink          20.0  Vasily Danilyuk
998     4        14       Brazil         Tablet        1820.0  Vasily Danilyuk
999     4        12       Brazil            SDD         264.0     Onida Cosely

[1000 rows x 6 columns]

# RESOLUCIÓN ANALÍTICA print(my_df['article_name'].value_counts()) # cuenta valores únicos

HDD                 47
SDD                 45
Netbook             45
Tablet              40
Usb Cable           39
Sata Cable          38
Pci Express Port    37
Range Extender      36
Smartphone          35
Full Pc             34
Heatsink            34
Power Supply        34
Headphones          34
Mouse               34
Motherboard         33
Water Cooling       32
Video Card          31
Ram Memory          31
Notebook            30
CPU                 29
Modem               29
Desk                28
Mesh Wi-Fi X 2      28
Webcam              28
Case                26
Monitor             26
Fan Cooler          25
Chair               24
Scanner             24
Wi-Fi Card          22
Keyboard            22
Name: article_name, dtype: int64

# RESOLUCIÓN GRÁFICA sns.displot(my_df, x='article_name') plt.xticks(rotation=90) # 'vertical' plt.show()

# RESOLUCIÓN ANALÍTICA df2=(my_df.groupby(by='article_name').sum()).sort_values('total_amount', ascending=False).head(5) pd.options.display.float_format= '$ {:,.2f}'.format print(df2['total_amount']) # 'article_name' es el index # print(df2) # agrupa (y suma) sólo datos numéricos # SINTAXIS ALTERNATIVA # pd.options.display.float_format= '$ {:,.2f}'.format # print(my_df[['article_name'] + ['total_amount']].groupby(['article_name']).sum().sort_values('total_amount', ascending=False).head(5))

article_name
Full Pc      $ 538,335.93
Notebook     $ 251,000.00
Smartphone   $ 152,250.00
Chair         $ 69,477.48
Tablet        $ 48,620.00
Name: total_amount, dtype: float64

# RESOLUCIÓN GRÁFICA plt.pie(x=df2['total_amount'], labels=df2.index) plt.show() # 'article_name' es el index

# 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(df4[['quantity'] + ['total_amount']]) print(df4[['quantity'] + ['total_amount']].head(5)) # 'seller_anme' es el index

                   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.bar(df4.index, df4['total_amount']) plt.xticks(rotation=60) plt.show() # 'seller_anme' es el index

# 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[['quantity'] + ['total_amount']]) # 'week' es el index

      quantity  total_amount
week                        
1         2449  $ 507,458.81
2         2444  $ 415,364.44
3         2114  $ 329,140.03
4         1058  $ 223,844.56

# RESOLUCIÓN GRÁFICA plt.bar(df5.index, df5['total_amount']) plt.show() # 'week' es el index

# RESOLUCIÓN # Top 10 de países por ventas. # Top 10 de productos de 2 países. # Resolución Analítica y Gráfica

#Top 10 de países por sus ventas df_top10 = (my_df[['country_name','total_amount','quantity']].groupby(by='country_name').sum()).sort_values('total_amount',ascending=False).head(10) print(df_top10)

              total_amount  quantity
country_name                        
Brazil        $ 441,271.85      2515
Argentina     $ 205,832.78       947
Colombia      $ 177,514.29       881
Peru          $ 161,421.12      1027
Mexico        $ 138,619.99       846
Venezuela      $ 77,684.52       320
El Salvador    $ 57,391.26       111
Guatemala      $ 52,579.25       202
Honduras       $ 36,763.56       303
Costa Rica     $ 34,606.50       145

plt.style.use('seaborn') plt.bar(df_top10.index, df_top10['total_amount'], color='C5') plt.xticks(rotation=60) plt.title('Ventas por país') plt.show()

#Top 10 de productos de Brasil brasil=my_df.loc[my_df['country_name']=='Brazil'] df6=(brasil.groupby(['article_name']).sum()).sort_values('total_amount',ascending=False).head(10) print(df6[['total_amount']+['quantity']])

              total_amount  quantity
article_name                        
Full Pc       $ 134,052.03        63
Notebook       $ 83,000.00        83
Smartphone     $ 41,475.00        79
Chair          $ 37,591.68       112
Tablet         $ 20,280.00       156
Scanner        $ 14,430.00        78
Motherboard    $ 13,976.38       101
Desk           $ 12,619.70        97
Netbook        $ 11,745.00        81
CPU            $ 11,448.84        82

plt.barh(df6.index,df6['total_amount']) plt.title('Brasil') plt.xlabel('Ingresos') plt.ylabel('Producto') plt.show()

#Top 10 de productos de Argentina argentina=my_df.loc[my_df['country_name']=='Argentina'] df7=(argentina.groupby(['article_name']).sum()).sort_values('total_amount',ascending=False).head(10) print(df7[['total_amount']+['quantity']])

              total_amount  quantity
article_name                        
Full Pc        $ 95,751.45        45
Notebook       $ 43,000.00        43
CPU            $ 14,520.48       104
Smartphone     $ 13,125.00        25
Netbook         $ 5,655.00        39
Motherboard     $ 5,396.82        39
HDD             $ 3,714.16        68
Monitor         $ 2,990.00        13
Scanner         $ 2,220.00        12
Chair           $ 2,013.84         6

plt.barh(df7.index,df7['total_amount']) plt.title('Argentina') plt.xlabel('Ingresos') plt.ylabel('Producto') plt.show()

# RESOLUCIÓN # Evolución de Ventas por producto. # Tomar 2 países y 'week' como variable categórica # Resolución Analítica y Gráfica

df8=(my_df.groupby(['article_name','week']).sum()).sort_values('quantity',ascending=False).head(10) print(df8[['quantity']])

                   quantity
article_name week          
HDD          2          158
SDD          1          153
Tablet       3          148
HDD          3          129
Mouse        2          125
Netbook      1          124
Desk         2          124
Power Supply 2          122
Ram Memory   1          113
Mouse        1          111

mexico=my_df.loc[my_df['country_name']=='Mexico'] df9=(mexico.groupby(['article_name','week']).sum()).sort_values('quantity',ascending=False).head(10) print(df9[['quantity']])

                     quantity
article_name   week          
SDD            1           37
Notebook       4           32
Case           3           30
Netbook        1           26
Range Extender 4           25
Motherboard    1           25
Water Cooling  2           25
HDD            2           25
               3           25
Smartphone     1           23

bolivia=my_df.loc[my_df['country_name']=='Bolivia'] df10=(bolivia.groupby(['article_name','week']).sum()).sort_values('quantity',ascending=False).head(10) print(df10[['quantity']])

                       quantity
article_name     week          
Pci Express Port 1           22
Usb Cable        3           21
Motherboard      2           16
Desk             3           15
Ram Memory       2           15
Scanner          3           15
Keyboard         3           13
Ram Memory       3           13
Mesh Wi-Fi X 2   3           12
Pci Express Port 4           12

my_df_mexbol=my_df[(my_df.country_name=='Mexico')| (my_df.country_name=='Bolivia')] sns.barplot(x='week', y='quantity', hue='country_name', data=my_df_mexbol)

# RESOLUCIÓN # Top 5 vendedores de un país. # Resolución Analítica y Gráfica.

#Top 5 de vendedores de Argentina argentina=my_df.loc[my_df['country_name']=='Argentina'] dftopVarg=(argentina.groupby(['seller_name']).sum()).sort_values('total_amount',ascending=False).head(5) print(dftopVarg[['total_amount']+['quantity']])

                   total_amount  quantity
seller_name                              
Janel O'Curran      $ 34,971.47        91
Brockie Patience    $ 32,553.20        44
Oliviero Charkham   $ 28,985.95        77
Vasily Danilyuk     $ 27,503.50        57
Onida Cosely        $ 17,216.35        71

plt.pie(x=dftopVarg['total_amount'], labels=dftopVarg.index) plt.show()