TP Final Integrador 22614

# 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 3.2 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.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('\nBúsqueda de valores null 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 null 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 null 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 null 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 null 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 null 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

# https://pandas.pydata.org/docs/user_guide/indexing.html # https://towardsdatascience.com/how-to-use-loc-and-iloc-for-selecting-data-in-pandas-bd09cb4c3d79 # https://stackoverflow.com/questions/28754603/indexing-pandas-data-frames-integer-rows-named-columns my_df = df_orders.copy() # shallow copy # Cambio el indice 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']) # print(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 (tabla) # 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' de 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'] #my_df # Columna de seller name my_df.loc[i, 'seller_name'] = df_sellers.loc[my_df.loc[i ,'seller_id']]['seller_name'] # elimino las columnas que no necesito de my_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]

# my_df.loc[:, 'quantity'] # my_df.loc[:, ['quantity','country_name']] # my_df.loc[0:5, ['quantity','country_name']] # my_df.iloc[:, 1] # my_df.iloc[:, [1,2]] # my_df.iloc[0:6, [1,2]] my_df.iloc[0:5, [1,2]]

quantityint64

country_nameobject

Peru

Bolivia

Brazil

Mexico

# RESOLUCIÓN ANALÍTICA df7 = my_df.groupby(by='article_name').sum().sort_values('quantity', ascending=False).head() pd.options.display.float_format= '$ {:,.2f}'.format print(df7[['quantity', 'total_amount']])

              quantity  total_amount
article_name                        
HDD                413   $ 22,558.06
Tablet             374   $ 48,620.00
SDD                372    $ 8,184.00
Mouse              322    $ 9,756.60
Netbook            320   $ 46,400.00

# RESOLUCIÓN GRÁFICA sns.barplot(data=df7, x=df7.index, y='quantity') 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
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, autopct='%1.2f%%') 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(df4[['quantity'] + ['total_amount']].head(5))

                   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=90) 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[['quantity'] + ['total_amount']])

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

# Filtramos el df por cada semana y agrupamos por 'country_name' para sumar los valores df_s1 = my_df[my_df['week'] == 1].groupby(by='country_name').sum().sort_values('total_amount', ascending=False).head(3) pd.options.display.float_format= '$ {:,.2f}'.format # Seteamos el formato para type float print("Semana 1") # Traigo todas las filas de la columna 2 (total_amount) print(df_s1.iloc[:,2:3]) print() df_s2 = my_df[my_df['week'] == 2].groupby(by='country_name').sum().sort_values('total_amount', ascending=False).head(3) pd.options.display.float_format= '$ {:,.2f}'.format print("Semana 2") print(df_s2.iloc[:,2:3]) print() df_s3 = my_df[my_df['week'] == 3].groupby(by='country_name').sum().sort_values('total_amount', ascending=False).head(3) pd.options.display.float_format= '$ {:,.2f}'.format print("Semana 3") print(df_s3.iloc[:,2:3]) print() df_s4 = my_df[my_df['week'] == 4].groupby(by='country_name').sum().sort_values('total_amount', ascending=False).head(3) pd.options.display.float_format= '$ {:,.2f}'.format print("Semana 4") print(df_s4.iloc[:,2:3]) print()

Semana 1
              total_amount
country_name              
Brazil        $ 165,289.90
Argentina      $ 63,760.48
Mexico         $ 58,549.25

Semana 2
              total_amount
country_name              
Brazil        $ 106,803.85
Argentina      $ 96,789.13
Peru           $ 56,591.80

Semana 3
              total_amount
country_name              
Brazil         $ 79,341.50
Colombia       $ 69,473.90
Peru           $ 51,855.31

Semana 4
              total_amount
country_name              
Brazil         $ 89,836.60
Mexico         $ 39,534.32
Colombia       $ 29,169.06

# Definimos los subplots con las dimensiones fig, ax = plt.subplots(2, 2, figsize=(9,9)) fig.subplots_adjust(bottom=0.3, right=1.7, top=1.2) # Se crean los subplots ax[0, 0].bar(df_s1.index, df_s1['total_amount'],width=0.5, color=['mediumblue', 'slateblue', 'mediumpurple']) ax[0, 1].bar(df_s2.index, df_s2['total_amount'],width=0.5, color=['mediumblue', 'slateblue', 'hotpink']) ax[1, 0].bar(df_s3.index, df_s3['total_amount'],width=0.5, color=['mediumblue', 'blueviolet', 'hotpink']) ax[1, 1].bar(df_s4.index, df_s4['total_amount'],width=0.5, color=['mediumblue', 'mediumpurple', 'blueviolet']) # Se agrega titulos y ejes ax[0,0].set_title("Semana 1") ax[0,1].set_title("Semana 2") ax[1,0].set_title("Semana 3") ax[1,1].set_title("Semana 4") ax[0,0].set_ylabel("Monto total") ax[0,1].set_ylabel("Monto total") ax[1,0].set_ylabel("Monto total") ax[1,1].set_ylabel("Monto total") plt.show()

# Agrupamos el df por pais y, ordenando por cantidad total df_d1 = my_df.groupby(by='country_name').sum().sort_values('total_amount', ascending=True).head(1).reset_index() df_d2 = my_df.groupby(by='country_name').sum().sort_values('total_amount', ascending=False).head(1).reset_index() print('Pais con menor cantidad de ventas') print(df_d1.iloc[0,0]) print() print('Pais con mayor cantidad de ventas') print(df_d2.iloc[0,0]) print() # Filtramos el df con el pais con menor ventas df_menor = my_df[my_df['country_name'] == df_d1.iloc[0,0]] # Con df_d1.iloc[0,0] localizamos el elemento requerido (nombre país) # Filtramos el df con el pais con mayor ventas df_mayor = my_df[my_df['country_name'] == df_d2.iloc[0,0]] # Con df_d2.iloc[0,0] localizamos el elemento requerido (nombre país) print() df_menor_lin = df_menor[['week','total_amount', 'quantity']].groupby('week').sum('total_amount', 'quantity').sort_values('week', ascending=True) print(df_menor_lin) df_mayor_lin = df_mayor[['week','total_amount', 'quantity']].groupby('week').sum('total_amount', 'quantity').sort_values('week', ascending=True) print(df_mayor_lin)

Pais con menor cantidad de ventas
Puerto Rico

Pais con mayor cantidad de ventas
Brazil


      total_amount  quantity
week                        
1       $ 1,256.58         9
2           $ 8.85         3
      total_amount  quantity
week                        
1     $ 165,289.90       765
2     $ 106,803.85       664
3      $ 79,341.50       646
4      $ 89,836.60       440

# Creamos el lienzo fig, ax1 = plt.subplots() ax2 = ax1.twinx() fig , ax3 = plt.subplots() ax4 = ax3.twinx() # Creamos los graficos sns.lineplot(data=df_mayor_lin, x = df_mayor_lin.index, y='total_amount', ax=ax1, color='r') sns.lineplot(data=df_mayor_lin, x = df_mayor_lin.index, y='quantity', ax=ax2, color='b') sns.lineplot(data=df_menor_lin, x = df_menor_lin.index, y='total_amount', ax=ax3, color='y') sns.lineplot(data=df_menor_lin, x = df_menor_lin.index, y='quantity', ax=ax4, color='g') # Definimos títulos y ejes ax1.set_title("Mayor vendedor") ax1.set_xlabel("Semanas") ax1.set_ylabel("Monto total (en rojo)") ax2.set_ylabel("Cantidad de ventas (en azul)") ax3.set_title("Menor vendedor") ax3.set_xlabel("Semanas") ax3.set_ylabel("Monto total (en amarillo)") ax4.set_ylabel("Cantidad de ventas (en verde)") plt.show()

# Filtramos el DF por la cuarta semana y agrupamos por 'article_name' para sumar los valores df_art = my_df[my_df['week'] == 4].groupby(by='article_name').sum().sort_values('quantity', ascending=False).head(5) # Mostramos la cantidad de articulos vendidos print(df_art['quantity'])

article_name
Notebook            78
Pci Express Port    78
Usb Cable           72
Netbook             57
Tablet              55
Name: quantity, dtype: int64

plt.pie(x=df_art['quantity'], labels=df_art.index, autopct='%1.2f%%') plt.show()