# Artículos
conexion = sql3.connect('/work/data/articles.db')
sql_query = pd.read_sql_query('SELECT * FROM articles', conexion)
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 los datos')
print(df_articles.head())
print('\nFormato del dataframe')
print(df_articles.shape)
Muestra de los 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)
# Exploración del df de vendedores
print('Muestra de los datos')
print(df_sellers.head())
print('\nFormato del dataframe')
print(df_sellers.shape)
Muestra de los 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)
# Exploración del df de órdenes
print('Muestra de los datos')
print(df_orders.head())
print('\nFormato del dataframe')
print(df_orders.shape)
Muestra de los 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)
# Cambio el tipo de dato de 'unit_price' a float
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
# Cambio el índice del df_articles por la columna article_id
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
# Creo una copia del df_orders
mi_df = df_orders.copy()
mi_df = mi_df.assign(article_name = mi_df['article_id'])
mi_df = mi_df.assign(total_amount = mi_df['article_id'])
mi_df = mi_df.assign(seller_name = mi_df['seller_id'])
print(mi_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
# Reemplazo los valores en las nuevas columnas del df
for i in range(len(mi_df.index)):
article = df_articles.loc[mi_df.loc[i]['article_name']]['article_name']
mi_df.loc[i,'article_name'] = article
mi_df.loc[i,'total_amount'] = mi_df.loc[i,'quantity'] * df_articles.loc[mi_df.loc[i]['total_amount']]['unit_price']
mi_df.loc[i,'seller_name'] = df_sellers.loc[mi_df.loc[i]['seller_name']]['seller_name']
print(mi_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
# Borrar las columnas que no voy a utilizar
mi_df.drop(['order_id','article_id','seller_id'], axis='columns', inplace=True)
print(mi_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
df = mi_df.groupby('article_name').sum()
orden_cantidad = df.sort_values('quantity', ascending=False)
print(orden_cantidad['quantity'].head(1))
article_name
HDD 413
Name: quantity, dtype: int64
# Resolución Gráfica
r1= sns.barplot(x=df.index,y=df['quantity'],data=df,order=df.sort_values('quantity', ascending=False).index)
r1.set_xlabel('Artículos')
r1.set_ylabel('Unidades vendidas')
plt.xticks(rotation=90)
plt.show()
# Resolución Analítica
df2 = (mi_df.groupby('article_name').sum()).sort_values('total_amount', ascending=False).head()
print(df2['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
e = (0.05,0,0,0,0)
c = ['gold','yellowgreen', 'lightcoral','lightskyblue','plum']
plt.pie(x=df2['total_amount'], explode=e, labels=df2.index, colors=c, startangle=90)
plt.show()
# Resolución Analítica
df3 = (mi_df.groupby('seller_name').sum()).sort_values('total_amount', ascending=False)
print('Respuesta:')
print(df3.head(1))
Respuesta:
week quantity total_amount
seller_name
Janel O'Curran 174 703 192832.47
# Resolución Gráfica
r3 = plt.bar(df3.index, df3['total_amount'])
plt.ylabel('Monto total de ventas')
plt.xlabel('Vendedores')
plt.xticks(rotation=60)
plt.show()
# Resolución Analítica
df4 = (mi_df.groupby('week').sum()).sort_values('total_amount',ascending=False)
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 Analítica
df5 = (mi_df.groupby('country_name').sum()).sort_values('total_amount', ascending=False).head()
print(df5['total_amount'])
country_name
Brazil 441271.85
Argentina 205832.78
Colombia 177514.29
Peru 161421.12
Mexico 138619.99
Name: total_amount, dtype: float64
# Resolución Gráfica
r5 = plt.bar(df5.index, df5['total_amount'])
plt.ylabel('Monto total de ventas')
plt.xlabel('Países')
plt.show()
# Resolución Gráfica
df6 = mi_df[(mi_df['article_name'] == 'Full Pc') | (mi_df['article_name'] == 'Notebook') | (mi_df['article_name'] == 'Smartphone')]
df6 = df6[['article_name','country_name','total_amount']]
df6 = df6.groupby(['article_name','country_name'])['total_amount'].sum().reset_index()
plt_order = df6.groupby('country_name')['total_amount'].sum().sort_values(ascending=False).index.values[0:5]
sns.catplot(y='country_name', x='total_amount', hue='article_name', data=df6, kind='bar', order=plt_order)
plt.xticks(rotation=90)
plt.title('Ingresos generados del top 3 de productos por países')
plt.ylabel('Países')
plt.xlabel('Ingresos')
plt.show()
# Resolución Analítica
df7 = (mi_df.groupby('country_name').sum()).sort_values('quantity', ascending=True).head()
print('Repuesta:')
print(df7['quantity'])
Repuesta:
country_name
Puerto Rico 12
Uruguay 92
El Salvador 111
Paraguay 123
Ecuador 129
Name: quantity, dtype: int64
# Resolución Gráfica
e = (0.05,0,0,0,0)
c = ['gold','yellowgreen', 'lightcoral','lightskyblue','plum']
plt.pie(x=df7['quantity'], explode=e, labels=df7.index, colors=c, startangle=90)
plt.show()