# A veces necesitamos instalar nuevas librerías en nuestros proyectos !pip install openpyxl==3.0.10

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

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

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

df_articles['unit_price'] = df_articles['unit_price'].astype(float) print(df_articles.dtypes)

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

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

# RESOLUCIÓN ANALÍTICA # Si no filtro por 'quantity' toma las series numéricas # df7 = my_df.groupby(by='article_name').sum().sort_values('quantity', ascending=False) # print(df7.head()) # print(df7[['quantity']].head()) # print() # Tomamos article_name como una columna más # df7 = my_df.groupby(by='article_name').sum().sort_values('quantity', ascending=False).reset_index() # print(df7.head()) # df7_2 = df7[['article_name', 'quantity']].groupby('article_name').sum('quantity').sort_values('quantity', ascending=False) # print(df7_2.head()) # print() # print(df7_2.head().index) # print(df7_2.index[0]) # toma correctamente el index # Vista tipo Serie # df7 = my_df.groupby(by='article_name')['quantity'].sum().sort_values(ascending=False).head() # df7 = my_df.groupby(by='article_name')[['quantity'] + ['total_amount']].sum().sort_values('quantity',ascending=False).head() # print(df7) # print(df7.head().index) # Vista tipo Serie # df7 = my_df.groupby(by='article_name')['quantity'].sum().sort_values(ascending=False) # print(df7.iloc[0:5]) # Vista tipo Serie (reset index) # df7 = my_df.groupby(by='article_name')['quantity'].sum().sort_values(ascending=False).reset_index() # print(df7.iloc[0:5]) # Vista tipo Serie # df7 = my_df.groupby(by='article_name')['quantity'].sum().sort_values(ascending=False) # df7 = my_df.groupby(by='article_name').sum().sort_values('quantity', ascending=False) # print(df7.loc['HDD':'Netbook']) # print(df7.loc['HDD':'Netbook']['quantity']) # print(df7.head()) # df7 = my_df.groupby(by='article_name').agg({'quantity':'sum'}).sort_values('quantity',ascending=False) # print(df7.head()) # df7 = my_df.groupby(by='article_name').agg({'quantity':'sum'}).rename(columns={'quantity':'Cantidad'}).sort_values('Cantidad',ascending=False) # print(df7.head()) # df7 = my_df.groupby(by='article_name').agg({'quantity':'sum'}).reset_index().rename(columns={'quantity':'Cantidad'}).sort_values('Cantidad',ascending=False) # print(df7.head()) df7 = my_df.groupby(by='article_name').sum().sort_values('quantity', ascending=False).head() pd.options.display.float_format= '$ {:,.2f}'.format # print(df7[['quantity']].head()) # print(df7[['quantity', 'total_amount']].head(5)) print(df7[['quantity', 'total_amount']])

# RESOLUCIÓN GRÁFICA # Opción 1 - Count # sns.countplot(my_df, x='article_name') # Opción - barplot sns.barplot(data=df7, x=df7.index, y='quantity') # Opción 3 - barplot # xs = ['HDD', 'Tablet', 'SDD', 'Mouse', 'Netbook'] # ys = [413, 374, 372, 322, 320] # sns.barplot(x=xs, y=ys) 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() # print(df2.index[0]) # print() # print(df2.reset_index().iloc[0]) # print() # for i in range(5): # # print(df2.index[i]) # print(df2.reset_index().iloc[i]) # print() # print() # print(df2.reset_index())

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

# 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']])

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

paises = pd.unique(my_df["country_name"]) #print(paises) vendedores = pd.unique(my_df["seller_name"]) #print(vendedores) productos = np.sort(pd.unique(my_df["article_name"])) print(productos)

# RESOLUCIÓN # Ventas filtrado por país. Comparar 2 países # Cuál es el producto que más se vende, en qué cantidad # Resolución Analítica y Gráfica

dfPreg5 = my_df[(my_df['country_name'] == Pais_1) | (my_df['country_name'] == Pais_2)] dfPreg5_1 = dfPreg5.groupby(by=['article_name', 'country_name']).sum().sort_values('quantity', ascending=False).reset_index() dfPreg5Pais1 = dfPreg5_1[dfPreg5_1['country_name'] == Pais_1].sort_values('quantity', ascending=False) dfPreg5Pais2 = dfPreg5_1[dfPreg5_1['country_name'] == Pais_2].sort_values('quantity', ascending=False) umbralPais1 = dfPreg5Pais1.head().iloc[4]['quantity'] umbralPais2 = dfPreg5Pais2.head().iloc[4]['quantity'] #import colorama #colorama.init() print(f'\nCantidad de ventas por producto') print(f'{"Producto" :<20}{Pais_1 :^10}{Pais_2 :^10}') for art in productos: if art in dfPreg5Pais1['article_name'].values: cantidadPais1 = dfPreg5Pais1[dfPreg5Pais1['article_name'] == art]['quantity'].iloc[0] else: cantidadPais1 = 0 if art in dfPreg5Pais2['article_name'].values: cantidadPais2 = dfPreg5Pais2[dfPreg5Pais2['article_name'] == art]['quantity'].iloc[0] else: cantidadPais2 = 0 if (cantidadPais1 >= umbralPais1) & (cantidadPais2 >= umbralPais2): print(f'{art :<20} {cantidadPais1 :>10} {cantidadPais2 :>10}') elif cantidadPais1 >= umbralPais1: print(f'{art :<20} {cantidadPais1 :>10}{cantidadPais2 :>10}') elif cantidadPais2 >= umbralPais2: print(f'{art :<20}{cantidadPais1 :>10} {cantidadPais2 :>10}') else: print(f'{art :<20}{cantidadPais1 :>10} {cantidadPais2 :>10}')

top5ProductosPais1 = dfPreg5Pais1.sort_values('quantity', ascending=False).head()["article_name"] top5ProductosPais2 = dfPreg5Pais2.sort_values('quantity', ascending=False).head()["article_name"] top5ProductosPais = pd.unique(top5ProductosPais1.append(top5ProductosPais2)) #print(top5ProductosPais1) #print(top5ProductosPais2) #print(top5ProductosPais) print(f'\nCantidad de ventas por producto') print(f'{"Producto" :<20}{Pais_1 :^10}{Pais_2 :^10}') for art in top5ProductosPais: if art in dfPreg5Pais1['article_name'].values: cantidadPais1 = dfPreg5Pais1[dfPreg5Pais1['article_name'] == art]['quantity'].iloc[0] else: cantidadPais1 = "-" if art in dfPreg5Pais2['article_name'].values: cantidadPais2 = dfPreg5Pais2[dfPreg5Pais2['article_name'] == art]['quantity'].iloc[0] else: cantidadPais2 = "-" print(f'{art :<20}{cantidadPais1 :>10} {cantidadPais2 :>10}')

figP5 = sns.catplot(data= dfPreg5_1, x='article_name', y = 'quantity', kind= 'bar', hue= 'country_name', width=1, aspect=1.5, legend_out=False) sns.move_legend(figP5,'upper right', title='País') figP5.set_xticklabels( rotation=90, horizontalalignment='center' )

# 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 top5VentasPais1 = dfPreg5Pais1.sort_values('total_amount', ascending=False).head()["article_name"] top5VentasPais2 = dfPreg5Pais2.sort_values('total_amount', ascending=False).head()["article_name"] top5VentasPais = pd.unique(top5VentasPais1.append(top5VentasPais2)) #print(top5VentasPais1) #print(top5VentasPais2) #print(top5VentasPais) print(f'\nCantidad de ventas por producto') print(f'{"Producto" :<20}{Pais_1 :^10}{Pais_2 :^10}') for art in top5VentasPais: if art in dfPreg5Pais1['article_name'].values: cantidadPais1 = dfPreg5Pais1[dfPreg5Pais1['article_name'] == art]['total_amount'].iloc[0].round() else: cantidadPais1 = "-" if art in dfPreg5Pais2['article_name'].values: cantidadPais2 = dfPreg5Pais2[dfPreg5Pais2['article_name'] == art]['total_amount'].iloc[0].round() else: cantidadPais2 = "-" print(f'{art :<20}{cantidadPais1 :>10} {cantidadPais2 :>10}')

Preg6 = my_df[(my_df['country_name'] == Pais_1) | (my_df['country_name'] == Pais_2)] dfPreg6_1 = dfPreg5.groupby(by=['article_name', 'country_name', 'week']).sum().reset_index() dfPreg6_2 = dfPreg6_1[dfPreg6_1['article_name'].isin(top5VentasPais1)] dataFig6 = dfPreg6_2[dfPreg6_2['country_name'] == Pais_1] figP6 = sns.lineplot(data=dataFig6, x='week', y='total_amount', hue='article_name').set(title=Pais_1) #sns.move_legend(figP6,'upper right') dfPreg6_2

dfPreg6_1 = dfPreg5.groupby(by=['article_name', 'country_name', 'week']).sum().reset_index() dfPreg6_2 = dfPreg6_1[dfPreg6_1['article_name'].isin(top5VentasPais2)] dataFig6 = dfPreg6_2[dfPreg6_2['country_name'] == Pais_2] figP6 = sns.lineplot(data=dataFig6, x='week', y='total_amount', hue='article_name').set(title=Pais_2) #sns.move_legend(figP6,'upper right')

# RESOLUCIÓN # Mejor vendedor/producto. Mostrar importe/cantidad. Comparar 2 países (variable categórica) # Resolución Analítica y Gráfica

df6 = my_df[my_df['country_name'] == 'Brazil'] #A partir del anterior, ordenar vendedores por total de ventas vendedores_pais = df6.groupby('seller_name').sum().sort_values('total_amount', ascending=False) vendedores_pais.head()

plt.bar(vendedores_pais.index,vendedores_pais['total_amount'], color = ['green' if x in vendedores_pais.index[0:5] else 'blue' for x in vendedores_pais.index]) #Color verde para los mejores 5 plt.xticks(rotation = 90) plt.title("Desempeño vendedores del país que más compra") plt.xlabel("Vendedores") plt.ylabel("Total de Ventas") plt.show()

df_brazil = my_df[my_df['country_name'] == 'Brazil'] print(df_brazil[['seller_name','total_amount', 'quantity']].groupby('seller_name').sum().sort_values('total_amount', ascending=False)) print() df_brazil_bars = df_brazil[['seller_name','total_amount']].groupby('seller_name').sum('total_amount').sort_values('total_amount', ascending=False) print(df_brazil_bars) print() df_brazil_line = df_brazil[['seller_name','quantity']].groupby('seller_name').sum('quantity').sort_values('quantity', ascending=False).reset_index() print(df_brazil_line) fig, ax1 = plt.subplots() # ax1: axes1 # sns.barplot(data = df_brazil_bars, x='seller_name', y='total_amount', ax=ax1, color='C3') sns.barplot(data=df_brazil_bars, x = df_brazil_bars.index, y='total_amount', ax=ax1, color='C3') # ax: axes plt.xticks(rotation=90) ax2 = ax1.twinx() # ax2: axes2 = ax1.twinx() sns.lineplot(data = df_brazil_line, x='seller_name', y='quantity', ax=ax2, color='C11') plt.xticks(rotation=90) plt.title('Top sellers in Brazil') ax1.set_xlabel("Seller Name") ax1.set_ylabel("Income ($)") ax2.set_ylabel("Quantity (line)") plt.show()