Análisis de ventas - TP Final Integrador

# 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 13.9 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.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') sql_query = pd.read_sql_query('SELECT * FROM articles', conn) df_articles = pd.DataFrame(sql_query, columns=['article_id','article_name','unit_price']) print(df_articles)

    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
5        20020             Chair     335.64
6        20021             Modem      67.50
7        20022    Range Extender      20.45
8        20023          Notebook    1000.00
9        20024           Netbook     145.00
10       20025               HDD      54.62
11       20026               SDD      22.00
12       20027        Ram Memory      35.95
13       20028       Motherboard     138.38
14       20029             Mouse      30.30
15       20030        Fan Cooler       4.25
16       20031            Webcam      20.07
17       20032          Keyboard      22.60
18       20033        Headphones      23.30
19       20034           Scanner     185.00
20       20035              Case      37.90
21       20036        Video Card     131.50
22       20037               CPU     139.62
23       20038      Power Supply      43.95
24       20039     Water Cooling      67.50
25       20040          Heatsink      10.00
26       20041         Usb Cable       2.95
27       20042        Sata Cable       2.14
28       20043  Pci Express Port      10.12
29       20044        Wi-Fi Card      59.61
30       20045    Mesh Wi-Fi X 2      32.50

# VENDEDORES df_sellers = pd.read_excel('/work/data/sellers.xlsx', index_col=0) print(df_sellers)

                 seller_name
seller_id                   
1           Aveline Swanwick
2                   Jase Doy
3          Oliviero Charkham
4           Cornie Wynrehame
5                Ewell Peres
6           Milly Christoffe
7                 Kati Innot
8              Tobin Roselli
9               Onida Cosely
10           Cirilo Grandham
11           Vasily Danilyuk
12          Brockie Patience
13           Arnold Kilkenny
14            Janel O'Curran
15           Daisie Slograve

# Ó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') 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
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') 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
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') 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
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_orders my_df = df_orders.copy() # Cambio el índice del df_articles df_articles.set_index('article_id', inplace=True) print(df_articles.head())

           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

print(my_df.head())

   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

# agrego las columnas que me faltan 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(my_df.head())

   order_id  week  article_id  quantity  seller_id country_name  article_name  \
0     15024     1       20039        10         10         Peru         20039   
1     15025     1       20029        15          5         Peru         20029   
2     15026     1       20024         5         14      Bolivia         20024   
3     15027     1       20018         9         14       Brazil         20018   
4     15028     1       20035         6         15       Mexico         20035   

   total_amount  seller_name  
0         20039           10  
1         20029            5  
2         20024           14  
3         20018           14  
4         20035           15

# reemplazar los datos ne las nuevas columnas for i in range(len(my_df.index)): # columna article_name # cargo el nombre del artículo en una variable article = df_articles.loc[my_df.loc[i]['article_name']]['article_name'] # se lo asigno a la columna y registro que corresponde my_df.loc[i,'article_name']= article # columna 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_name']]['seller_name'] print(my_df.head())

   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   

    article_name  total_amount      seller_name  
0  Water Cooling         675.0  Cirilo Grandham  
1          Mouse         454.5      Ewell Peres  
2        Netbook         725.0   Janel O'Curran  
3         Tablet        1170.0   Janel O'Curran  
4           Case         227.4  Daisie Slograve

# elimino las columnas que no necesito my_df.drop(['order_id', 'article_id','seller_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

#my_df.to_csv("dataset_depurado_y_filtrado.csv")

d1=pd.DataFrame({'mes': ['ene','feb','mar','abr'], 'ventas':[10,20,30,15]}) d2=pd.DataFrame({'mes': ['ene','feb','mar','abr'], 'costos':[7,16,25,12]}) print(pd.merge(d1,d2))

   mes  ventas  costos
0  ene      10       7
1  feb      20      16
2  mar      30      25
3  abr      15      12

# 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 sns.displot(my_df, x='article_name', kde=True) plt.xticks(rotation=90) plt.title('Cantidad de artículos vendidos', size = 16) plt.ylabel("Cantidad en unidades") plt.xlabel("") plt.show()

# RESOLUCIÓN ANALÍTICA my_df3 = (my_df.groupby('article_name').sum()).sort_values('total_amount', ascending=False).head(5) rename_art_names = {"article_name": "Artículo", "total_amount": "Ganancia",} rename_art = my_df3.reset_index() rename_art = rename_art.rename(columns=rename_art_names) rename_art['Ganancia'] = rename_art['Ganancia'].apply(lambda x: f"${x:.1f}") print(rename_art[['Artículo', 'Ganancia']])

     Artículo   Ganancia
0     Full Pc  $538335.9
1    Notebook  $251000.0
2  Smartphone  $152250.0
3       Chair   $69477.5
4      Tablet   $48620.0

# RESOLUCIÓN GRÁFICA c=['steelblue','lightslategray','dodgerblue','lightskyblue', 'aliceblue'] plt.pie(x=my_df3['total_amount'],labels=my_df3.index, colors=c) plt.title(' Los 5 artículos que mas ingresos generaron', size = 16) plt.show()

# RESOLUCIÓN ANALÍTICA df4 = (my_df.groupby('seller_name').sum()).sort_values('total_amount', ascending=False) print(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
Vasily Danilyuk         521     129157.55
Daisie Slograve         554     120520.11
Aveline Swanwick        629     118874.33
Arnold Kilkenny         583      94552.04
Kati Innot              512      83704.62
Jase Doy                582      80628.31
Ewell Peres             496      78144.32
Onida Cosely            535      77373.37
Milly Christoffe        442      61733.69
Tobin Roselli           519      56984.42
Cornie Wynrehame        523      52253.57
Cirilo Grandham         470      45009.40

# RESOLUCIÓN GRÁFICA plt.bar(df4.index, df4['total_amount']) plt.xticks(rotation=60) plt.title('Ganancia por vendedor', size = 16) plt.ylabel("Ganancia u$") plt.show()

# RESOLUCIÓN ANALÍTICA df5=(my_df.groupby('week').sum()).sort_values('total_amount',ascending=False) print(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.bar(df5.index,df5['total_amount']) plt.title('Ganancia por semana', size = 16) plt.ylabel("Ganancia u$") plt.xlabel('') index = ['', '1 semana', '2 semana', '3 semana', '4 semana'] indice = np.arange(len(index)) plt.xticks(indice, index) plt.xlabel("") plt.savefig('ganancia_semana.png', dpi=300) plt.show()

print(my_df.describe())

             week     quantity  total_amount
count  1000.00000  1000.000000   1000.000000
mean      2.22400     8.065000   1475.807840
std       1.01531     4.326603   3705.470337
min       1.00000     1.000000      2.140000
25%       1.00000     4.000000    121.440000
50%       2.00000     8.000000    354.550000
75%       3.00000    12.000000   1160.000000
max       4.00000    15.000000  31917.150000

Total = my_df['total_amount'].sum() print ("La ganancia total del mes en u$ es:",Total) Total2 = my_df['quantity'].sum() print ("La cantidad total de unidades vendidas es:",Total2) Total3 = my_df['country_name'].drop_duplicates().count() print ("La cantidad de países con los que se comercializó es :",Total3) Total4 = my_df['seller_name'].drop_duplicates().count() print ("La cantidad de vendedores es: ",Total4)

La ganancia total del mes en u$ es: 1475807.84
La cantidad total de unidades vendidas es: 8065
La cantidad de países con los que se comercializó es : 16
La cantidad de vendedores es:  15

# RESOLUCIÓN ANALÍTICA grouped = my_df.groupby('country_name').agg({ "quantity": 'sum', "article_name": 'count', "total_amount": 'sum', }).sort_values('total_amount',ascending=False) renaming = {"quantity": "Cantidad", "article_name": "N° artículos.", "total_amount": "Ganancia",} renamed = grouped.rename(columns=renaming) renamed['Ganancia'] = renamed['Ganancia'].apply(lambda x: f"${x:.1f}") print(renamed)

              Cantidad  N° artículos.   Ganancia
country_name                                    
Brazil            2515            315  $441271.9
Argentina          947            113  $205832.8
Colombia           881            111  $177514.3
Peru              1027            125  $161421.1
Mexico             846            101  $138620.0
Venezuela          320             37   $77684.5
El Salvador        111             16   $57391.3
Guatemala          202             29   $52579.2
Honduras           303             38   $36763.6
Costa Rica         145             19   $34606.5
Chile              231             28   $24661.0
Bolivia            181             19   $22682.8
Uruguay             92             12   $17843.1
Ecuador            129             18   $17475.3
Paraguay           123             17    $8195.1
Puerto Rico         12              2    $1265.4

# RESOLUCIÓN GRÁFICA df6=(my_df.groupby('country_name').sum()).sort_values('total_amount',ascending=False) plt.barh(df6.index,df6['total_amount']) plt.title('Ganancia en dolares u$', size = 16) plt.ylabel('') plt.xlabel('') plt.show()

# RESOLUCIÓN GRÁFICA grouped["quantity"].plot(kind="barh") plt.title('Cantidad de artículos vendidos (unidades)', size = 16) plt.ylabel('') plt.xlabel('') plt.show()

df42 = my_df[['article_name', 'quantity']] # ( article_name quantity # 0 Water Cooling 10 # 0 Other 10 # 0 Phone 10 article_quantity_by_article_name = df42.groupby(['article_name']).agg({ "quantity": 'sum' }).sort_values('quantity', ascending=False) top_articles = article_quantity_by_article_name.head(10) print('TOP ARTICLES') print(top_articles)

TOP ARTICLES
                  quantity
article_name              
HDD                    413
Tablet                 374
SDD                    372
Mouse                  322
Netbook                320
Ram Memory             293
Motherboard            291
Pci Express Port       291
Smartphone             290
Range Extender         281

plt.bar(top_articles.index,top_articles['quantity']) plt.xticks(rotation=90) plt.title('Artículos con mayor demanda', size = 16) plt.ylabel('Unidades') plt.xlabel('')

#grouped.to_csv("dataset_agrupado.csv")

print('BOTTOM ARTICLES') bottom_articles = article_quantity_by_article_name.tail(10) print(bottom_articles)

BOTTOM ARTICLES
                quantity
article_name            
Desk                 223
Scanner              221
Mesh Wi-Fi X 2       213
Video Card           209
Monitor              208
Chair                207
Case                 206
Fan Cooler           205
Keyboard             165
Wi-Fi Card           141

# RESOLUCIÓN GRÁFICA plt.bar(bottom_articles.index,bottom_articles['quantity']) plt.xticks(rotation=90) plt.title('Artículos a enforcar la campaña publicitario', size = 16) plt.ylabel('Unidades') plt.xlabel('') plt.show()