Trabajo Practico Final JCC

# A veces necesitamos instalar nuevas librerías en nuestros proyectos !pip install openpyxl==3.0.10 # se instala para planillas de excel

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') query1 = pd.read_sql_query('SELECT * FROM articles', conn) df_articles = pd.DataFrame(query1, columns = ['article_id', 'article_name', 'unit_price']) # print(df_articles) # VENDEDORES df_sellers = pd.read_excel('/work/data/sellers.xlsx', index_col=0) # print(df_sellers) # Ó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 datos') print(df_articles.head()) print('\nFormato del dataframe') print(df_articles.shape) print('\nBúsqueda de valores nulos por columna') print(df_articles.isnull().sum()) print('\nFormato de los datos') 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 nulos por columna
article_id      0
article_name    0
unit_price      0
dtype: int64

Formato de los datos
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('\nFormato del dataframe') print(df_sellers.shape) print('\nBúsqueda de valores nulos por columna') print(df_sellers.isnull().sum()) print('\nFormato de los datos') 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 nulos por columna
seller_name    0
dtype: int64

Formato de los datos
seller_name    object
dtype: object

# Exploración del df de órdenes print('Muestra de datos') print(df_orders.head()) print('\nFormato del dataframe') print(df_orders.shape) print('\nBúsqueda de valores nulos por columna') print(df_orders.isnull().sum()) print('\nFormato de los datos') 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 nulos por columna
order_id        0
week            0
article_id      0
quantity        0
seller_id       0
country_name    0
dtype: int64

Formato de los datos
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

# Creo una copia del df base my_df = df_orders.copy() # Cambio el índice del df artículos df_articles.set_index('article_id', inplace=True) print(my_df) print(df_articles)

     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]
                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

# Agrego algunas columnas y pongo el campo que me va a servir de "ancla" my_df = my_df.assign(article_name = my_df['article_id']) my_df = my_df.assign(total_amount = my_df['article_id']) # voy a necesitar el unit_price my_df = my_df.assign(seller_name = my_df['seller_id']) # reeplazar los valores en el nuevo df # df_articles[?]['article_name'] for i in range(len(my_df.index)): # len... devuelve la cantidad de registros article = df_articles.loc[my_df.loc[i]['article_name']]['article_name'] # reemplazo en la columna 'article_name' my_df.loc[i,'article_name'] = article # modificar la columna total_amount my_df.loc[i,'total_amount'] = my_df.loc[i,'quantity'] * df_articles.loc[my_df.loc[i]['total_amount']]['unit_price'] # modificar la columna 'seller_name my_df.loc[i,'seller_name'] = df_sellers.loc[my_df.loc[i]['seller_name']]['seller_name'] # elimino las columnas que no necesito 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 my_df2 = my_df.groupby('article_name').sum() por_cant = my_df2.sort_values('quantity', ascending=False) print(por_cant['quantity'].head(1))

article_name
HDD    413
Name: quantity, dtype: int64

# RESOLUCIÓN GRÁFICA #plt.figure(figsize=(30,20)) sns.displot(my_df, x='article_name', color='Orange', kde=True) plt.xticks(rotation=90) plt.title('Producto mas Vendido', fontsize=20) plt.xlabel('Artículos', fontsize=15) plt.ylabel('Cantidad', fontsize=15) plt.figure(figsize=(30,20)) plt.show

# RESOLUCIÓN ANALÍTICA my_df3 =(my_df.groupby('article_name').sum()).sort_values('total_amount', ascending=False).head(5) print(my_df3['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 # ojo no puedo hacer analisis comparativo ni poner porcentajes porque no tengo todos los articulos en df3 separado=[0.1, 0, 0, 0, 0] plt.pie(x=my_df3['total_amount'], labels=my_df3.index, explode=separado) plt.title('Producto mas vendido') plt.show

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

                   quantity  total_amount
seller_name                              
Janel O'Curran          703     192832.47
Brockie Patience        441     142709.88
Oliviero Charkham       555     141329.76

# RESOLUCIÓN GRÁFICA plt.figure(figsize=(6,4)) plt.bar(my_df4.index, my_df4['total_amount'], color=['green', 'yellow', 'cyan'], width=0.3) plt.xlabel('Vendedor', fontsize=15) plt.ylabel('Total de Ventas $', fontsize=15) plt.title('Mejor Vendedor del Mes', fontsize=20) plt.show()

# RESOLUCIÓN ANALÍTICA my_df5 = (my_df.groupby('week').sum()).sort_values('total_amount', ascending=False) print(my_df5)

      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.figure(figsize=(7, 4)) plt.bar(my_df5.index, my_df5['total_amount'], color='maroon', width=0.45) plt.xlabel('Semana', fontsize=15) plt.ylabel('Total de Ventas $', fontsize=15) plt.title('Variación Ventas por Mes', fontsize=20) plt.show()

# RESOLUCIÓN