!pip install openpyxl==3.0.10
# imports
import numpy as np
import pandas as pd
import sqlite3 as sql3
import openpyxl # levantar excel
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('\nBusqueda de valores nulls 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('\nBusqueda de valores nulls 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('\nBusqueda de valores nulls 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)
my_df = df_orders.copy() # shallow copy
# Cambio el índice 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'])
for i in range(max(my_df.count())):
article = df_articles.loc[my_df.loc[i]['article_id']]['article_name']
# print(article)
# Asignar a cada valor id de la columna 'article_name' (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']
# 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 del df
my_df.drop(['order_id', 'article_id', 'seller_id'], axis='columns', inplace=True)
print(my_df)
# my_df.loc[:, 'quantity']
# my_df.loc[0, ['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]]
# RESOLUCIÓN ANALÍTICA
df7 = my_df.groupby(by='article_name').sum().sort_values('quantity', ascending=False)
pd.options.display.float_format= '$ {:,.2f}'.format
print(df7[['quantity', 'total_amount']].head())
# RESOLUCIÓN GRÁFICA
sns.barplot(data=df7, x=df7.index, y='quantity')
plt.xticks(rotation=90)
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)
print(df2['total_amount'])
# RESOLUCIÓN GRÁFICA
my_explode = [0.02, 0, 0, 0, 0]
plt.pie(x=df2['total_amount'], labels=df2.index, autopct='%1.2f%%', explode = my_explode)
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.barh(df4.index, df4['total_amount'], align='center', color = ['red' if x in df4.index[0] else 'blue' for x in df4.index], edgecolor='none')
vendedores = df4.index
cantidad = df4['total_amount'].values
for i in range(len(vendedores)):
etiqueta = '$' + str(int(cantidad[i]))
plt.text( s=etiqueta, x=cantidad[i] + 15000 ,y=vendedores[i], ha='center')
plt.gca().spines['bottom'].set_visible(False)
plt.gca().spines['left'].set_visible(False)
plt.gca().spines['top'].set_visible(False)
plt.gca().spines['right'].set_visible(False)
plt.gca().invert_yaxis()
plt.xlabel('')
plt.xticks([])
plt.ylabel('')
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.figure()
plt.bar(df5.index, df5['total_amount'], align='center')
ing_week = df5['total_amount'].values
for i,n in enumerate(ing_week):
etiqueta = '$' + str(int(n))
plt.text( s=etiqueta, x=(i+1) ,y=df5['total_amount'][i+1] + 10000, ha='center')
plt.gca().spines['bottom'].set_visible(False)
plt.gca().spines['left'].set_visible(False)
plt.gca().spines['top'].set_visible(False)
plt.gca().spines['right'].set_visible(False)
plt.xlabel('')
plt.xticks(range(1, 5, 1))
plt.yticks([])
plt.ylabel(ylabel='')
plt.suptitle('Variaciones de ventas a lo largo del mes')
plt.show()
# RESOLUCIÓN ANALÍTICA
df6 = my_df.groupby(by='country_name').sum().sort_values('total_amount', ascending=False).head(5)
pd.options.display.float_format = '$ {:,.2f}'.format
print(df6[['quantity'] + ['total_amount']])
# RESOLUCIÓN ANALÍTICA
df_brazil = my_df[my_df['country_name'] == 'Brazil']
df_argentina = my_df[my_df['country_name'] == 'Argentina']
df_art_bra = df_brazil.groupby(by='article_name').sum().sort_values('quantity', ascending=False).head(5)
df_art_arg = df_argentina.groupby(by='article_name').sum().sort_values('quantity', ascending=False).head(5)
print("Artículos más vendidos en Argentina:\n", df_art_arg[['quantity']])
print("\n\n")
print("Artículos más vendidos en Brazil:\n",df_art_bra[['quantity']])
# RESOLUCIÓN
df_week_bra = df_brazil[df_brazil['article_name'] == 'Tablet'].groupby(by='week').sum()
df_week_arg = df_argentina[df_argentina['article_name'] == 'CPU'].groupby(by='week').sum()
labels = df_week_arg.index
print(labels)
x = np.arange(len(labels))
width = 0.35
fig, ax = plt.subplots()
rects1 = ax.bar(x - width/2, df_week_arg['quantity'], width, label='Argentina')
rects2 = ax.bar(x + width/2, df_week_bra['quantity'], width, label='Brazil')
ax.set_ylabel('quantity')
ax.set_title('Variación de ventas del artículo más vendido a lo largo del mes')
ax.set_xticks(x, labels)
ax.legend()
ax.bar_label(rects1, padding=3)
ax.bar_label(rects2, padding=3)
fig.tight_layout()
plt.show()