6 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 #import openpyxl # para levantar excel de otros paqueres (no office)

# 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']) #df_articles #Vendedores df_sellers = pd.read_excel('/work/data/sellers.xlsx', index_col=0) #df_sellers #Ordenes df_orders = pd.read_csv('/work/data/orders.csv') #df_orders #sql_query df_articles

article_idint64

20015 - 20045

article_nameobject

Smartphone3.2%

Full Pc3.2%

29 others93.5%

20045

Mesh Wi-Fi X 2

# Exploración del df de artículos print('Muestra de datos') print(df_articles.head()) print('\nForma del dataframe') print(df_articles.shape) print('\nBuscar valores nulos') print(df_articles.isnull().sum()) print('\nFormato de los datos del dataframe') 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

Forma del dataframe
(31, 3)

Buscar valores nulos
article_id      0
article_name    0
unit_price      0
dtype: int64

Formato de los datos del dataframe
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()) print('\nForma del dataframe') print(df_sellers.shape) print('\nBuscar valores nulos') print(df_sellers.isnull().sum()) print('\nFormato de los datos del dataframe') 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

Forma del dataframe
(15, 1)

Buscar valores nulos
seller_name    0
dtype: int64

Formato de los datos del dataframe
seller_name    object
dtype: object

# Exploración del df de órdenes print('Muestra de datos') print(df_orders.head()) print('\nForma del dataframe') print(df_orders.shape) print('\nBuscar valores nulos') print(df_orders.isnull().sum()) print('\nFormato de los datos del dataframe') 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

Forma del dataframe
(1000, 6)

Buscar valores nulos
order_id        0
week            0
article_id      0
quantity        0
seller_id       0
country_name    0
dtype: int64

Formato de los datos del dataframe
order_id         int64
week             int64
article_id       int64
quantity         int64
seller_id        int64
country_name    object
dtype: object

# Cambar a float los precios unitarios 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

#Creo una copia del df_orders my_df = df_orders.copy() # Cambio el indice del df_articles df_articles.set_index('article_id', inplace=True) # Agrego 3 columnas y pongo el campo que me va a servir de "ancla" para buscar la información real. 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)

                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
20020                  Chair      335.64
20021                  Modem       67.50
20022         Range Extender       20.45
20023               Notebook     1000.00
20024                Netbook      145.00
20025                    HDD       54.62
20026                    SDD       22.00
20027             Ram Memory       35.95
20028            Motherboard      138.38
20029                  Mouse       30.30
20030             Fan Cooler        4.25
20031                 Webcam       20.07
20032               Keyboard       22.60
20033             Headphones       23.30
20034                Scanner      185.00
20035                   Case       37.90
20036             Video Card      131.50
20037                    CPU      139.62
20038           Power Supply       43.95
20039          Water Cooling       67.50
20040               Heatsink       10.00
20041              Usb Cable        2.95
20042             Sata Cable        2.14
20043       Pci Express Port       10.12
20044             Wi-Fi Card       59.61
20045         Mesh Wi-Fi X 2       32.50

#Reemplazar los valores reales en el df for i in range (len(my_df.index)): #len(my_df.index) devuelva la cantidad de filas (registros) #reemplazo el nombre del articulo usando el id guardado en my_df article = df_articles.loc[my_df.loc[i]['article_id']]['article_name'] my_df.loc[i, 'article_name'] = article #reemplazo el nombre del vendedor usando el id guardado en my_df my_df.loc[i, 'seller_name'] = df_sellers.loc[my_df.loc[i]['seller_id']]['seller_name'] #busco el pecio unitario y lo multiplico por la cantidad de unidades vendidas. my_df.loc[i, 'total_amount'] = df_articles.loc[my_df.loc[i]['article_id']]['unit_price'] * my_df.loc[i, 'quantity'] #elimino las columnas que no utilizo my_df.drop(['article_id', 'seller_id', 'order_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

# RESOLUCIÓN ANALÍTICA my_df2 = my_df.groupby('article_name').sum() por_cantidad = my_df2.sort_values('quantity', ascending=False) print(por_cantidad['quantity'].head(1))

article_name
HDD    413
Name: quantity, dtype: int64

# Otra RESOLUCIÓN ANALÍTICA my_df2 = my_df.groupby('article_name').sum() print(my_df2['quantity'].max())

# RESOLUCIÓN GRÁFICA sns.barplot(x=my_df2.index, y=my_df2['quantity'], data = my_df2, order=my_df2.sort_values('quantity', ascending=False).index).set(title='Ventas por articulo') plt.xlabel('Cantidad vendida') plt.ylabel('Producto') plt.xticks(rotation=90) plt.show()

# RESOLUCIÓN ANALÍTICA my_df2 = my_df.groupby('article_name').sum() mas_ingresos = my_df2.sort_values('total_amount', ascending=False).head() print(mas_ingresos['total_amount'])

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 plt.pie(x=mas_ingresos['total_amount'], labels=mas_ingresos.index) plt.show()

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

                quantity  total_amount
seller_name                           
Janel O'Curran       703     192832.47

# RESOLUCIÓN GRÁFICA plt.bar(df3.index,df3['total_amount']) plt.xticks(rotation=75) plt.show()

# RESOLUCIÓN ANALÍTICA df4 = my_df.groupby('week').sum() print(df4)

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

# RESOLUCIÓN GRÁFICA plt.bar(df4.index,df4['total_amount']) plt.show()

# RESOLUCIÓN ANALITICA df5 = (my_df.groupby(by='country_name').sum()).sort_values('total_amount',ascending=False) print('El ranking de ventas por pais es:') print(df5['total_amount'])

El ranking de ventas por pais es:
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

#RESOLUCION GRAFICA sns.barplot(y=df5['total_amount'], x=df5.index,palette=("Blues")).set(title='Ventas por país',xlabel='Países',ylabel='Ventas totales') plt.xticks(rotation=90) plt.show()

# RESOLUCIÓN df6 = (my_df.groupby(by='week').sum()).sort_values('quantity',ascending=False) print('La lista de ventas en unidades por semana, iniciando por la semana con más unidades es:') print(df6['quantity'])

La lista de ventas en unidades por semana, iniciando por la semana con más unidades es:
week
1    2449
2    2444
3    2114
4    1058
Name: quantity, dtype: int64

#RESOLUCION GRAFICA plt.pie(x=df6['quantity'], labels=df6.index) plt.show()

# RESOLUCIÓN ANALITICA df7 = my_df.groupby('article_name').sum() mis_ventas = df7.sort_values('total_amount', ascending=True).head() print(mis_ventas['total_amount'])

article_name
Sata Cable           564.96
Usb Cable            805.35
Fan Cooler           871.25
Heatsink            2800.00
Pci Express Port    2944.92
Name: total_amount, dtype: float64

#RESOLUCION GRAFICA sns.barplot(y=df7['total_amount'], x=df7.index,palette=("Pastel1")).set(title='Ventas por articulo',xlabel='Nombre de articulo',ylabel='Ventas por articulos') plt.xticks(rotation=90) plt.show()