# Debido a que existen algunas incompatibilidades, instalaremos la libreria:
!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 16.6 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.
# Se hacen las importaciones necesarias y se renombran por comodidad. Notese que se importa tambien
# openpyxl para que el archivo en excel no presente problemas.
# imports
import numpy as np
import pandas as pd
import sqlite3 as sql3
import openpyxl
import matplotlib.pyplot as plt
import seaborn as sns
import math
# Es necesario recuperar los datos de las diferentes fuentes.
# Como los articulos, los vendedores y las ordenes proceden de distintos origenes, se utilizan metodos
# diferentes:
# Artículos: Se crea una conexion a la base de datos virtuales que nos provee sqlite3 en el archivo de
# base de datos "articles.db". Luego, se hace la consulta. Cómo queremos traer todos los registros,
# hacemos un Select * from... Los guardamos en una nueva variable que seria el resultado de la consulta.
# Por ultimo, con pandas tomamos esa variable y la convertimos en un DataFrame por medio de la funcion
# que nos provee pandas.
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: En este caso, simplemente usamos la funcion que nos provee pandas para traer los datos de
# un archivo de excel. Solamente tenemos que indicarle en donde se encuentra el indice y obtenemos el
# DataFrame buscado.
df_sellers = pd.read_excel('/work/data/sellers.xlsx', index_col=0)
# Órdenes: Por ultimo el caso mas sencillo: Para leer las ordenes de un archivo con valores separados
# por coma, usamos la funcion que nos provee pandas y ya. Con eso se arma el DataFrame.
df_orders = pd.read_csv('/work/data/orders.csv')
# Exploración del df de artículos
print('Muestra de datos: Se seleccionan sólo 5 valores como referencia.')
print(df_articles.head())
print('\nFormato del dataframe: Se brinda la información de "(filas, columnas)".')
print(df_articles.shape)
print('\nBúsqueda de valores nulos por columna: Si existiera algun valor, nos mostraria la cantidad')
print('mayor a 0')
print(df_articles.isnull().sum())
print('\nFormato de los datos por columna: Se ve el tipo de dato, en donde se puede notar que por lo ')
print('menos article_id es esperado. Los otros se devuelven con el tipo object, mientras que se ')
print('se espera que article_name sea una cadena de caracteres y unit_price sea un numero con coma.')
print('El valor del precio unitario tiene que ser de tipo float, por lo cual deberá convertirse luego.')
print(df_articles.dtypes)
Muestra de datos: Se seleccionan sólo 5 valores como referencia.
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: Se brinda la información de "(filas, columnas)".
(31, 3)
Búsqueda de valores nulos por columna: Si existiera algun valor, nos mostraria la cantidad
mayor a 0
article_id 0
article_name 0
unit_price 0
dtype: int64
Formato de los datos por columna: Se ve el tipo de dato, en donde se puede notar que por lo
menos article_id es esperado. Los otros se devuelven con el tipo object, mientras que se
se espera que article_name sea una cadena de caracteres y unit_price sea un numero con coma.
El valor del precio unitario tiene que ser de tipo float, por lo cual deberá convertirse luego.
article_id int64
article_name object
unit_price object
dtype: object
# Exploración del df de vendedores
print('Muestra de datos: Se seleccionan solo 5 valores como referencia.')
print(df_sellers.head())
print('\nFormato del dataframe: Se brinda la información de "(filas, columnas)".')
print(df_sellers.shape)
print('\nBúsqueda de valores nulos por columna: Si existiera algun valor, nos mostraria la cantidad')
print(df_sellers.isnull().sum())
print('\nFormato de los datos por columna: Se ve el tipo de dato, en donde se puede notar que se ')
print('devuelven las columnas con el tipo object, mientras que se espera que seller_name sea una ')
print('cadena de caracteres.')
print(df_sellers.dtypes)
Muestra de datos: Se seleccionan solo 5 valores como referencia.
seller_name
seller_id
1 Aveline Swanwick
2 Jase Doy
3 Oliviero Charkham
4 Cornie Wynrehame
5 Ewell Peres
Formato del dataframe: Se brinda la información de "(filas, columnas)".
(15, 1)
Búsqueda de valores nulos por columna: Si existiera algun valor, nos mostraria la cantidad
seller_name 0
dtype: int64
Formato de los datos por columna: Se ve el tipo de dato, en donde se puede notar que se
devuelven las columnas con el tipo object, mientras que se espera que seller_name sea una
cadena de caracteres.
seller_name object
dtype: object
# Exploración del df de órdenes
print('Muestra de datos: Se seleccionan solo 5 valores como referencia.')
print(df_orders.head())
print('\nFormato del dataframe: Se brinda la información de "(filas, columnas)".')
print(df_orders.shape)
print('\nBúsqueda de valores nulos por columna: Si existiera algun, nos mostraria la cantidad')
print(df_orders.isnull().sum())
print('\nFormato de los datos por columna: Se ve el tipo de dato, en este caso todos coinciden con ')
print('el tipo esperado menos country_name que se supone sea una cadena de caracteres.')
print(df_orders.dtypes)
Muestra de datos: Se seleccionan solo 5 valores como referencia.
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: Se brinda la información de "(filas, columnas)".
(1000, 6)
Búsqueda de valores nulos por columna: Si existiera algun, nos mostraria la cantidad
order_id 0
week 0
article_id 0
quantity 0
seller_id 0
country_name 0
dtype: int64
Formato de los datos por columna: Se ve el tipo de dato, en este caso todos coinciden con
el tipo esperado menos country_name que se supone sea una cadena de caracteres.
order_id int64
week int64
article_id int64
quantity int64
seller_id int64
country_name object
dtype: object
#La conversion de tipo object a float se puede hacer con el metodo .astype(float).
df_articles['unit_price'] = df_articles['unit_price'].astype(float)
print('\nLa estructura del DataFrame corregido seria la siguiente:')
print(df_articles.dtypes)
La estructura del DataFrame corregido seria la siguiente:
article_id int64
article_name object
unit_price float64
dtype: object
# Se creará una copia del df_orders para utilizarlo de base en donde se introduciran los demas cambios.
# Estos cambios modelaran el DataFrame buscado (my_df).
my_df = df_orders.copy()
# Luego, es necesario hacer el cambio del índice del df_articles para utilizarlo luego.
df_articles.set_index('article_id',inplace = True)
# Se agregaran las columnas necesarias asociadas al campo que va a utilizarme como referencia.
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'])
# Ahora, se reemplazaran los valores en el nuevo DataFrame
# Primero se debe buscar el nombre del artículo y se guarda en una variable.
# Como ahora df_articles está indexado por article_id, se puede utilizar para conseguir
# el registro que buscado mas facilmente con ".loc".
# La sintaxis correcta sería: df_articles.loc[indice][columna]
# En donde [indice] será el dato que se obtiene de my_df.loc[i]['article_id']
# En otras palabras, se toma registro a registro el article_id y se utiliza para extraer
# el nombre del artículo. Para lograr esto se utiliza la funcion iterativa 'for'
for i in range(len(my_df.index)): #len(my_df.index) devuelve la cantidad de registros
idArticulo = my_df.loc[i]['article_id']
nombreArticulo = df_articles.loc[idArticulo]['article_name']
# Se le asigna a la columna correspondiente, llamada ahora como la variable nombreArticulo
my_df.loc[i,'article_name'] = nombreArticulo
# Se hace la misma operacion con precioArticulo y cantArticulo para obtener el paralelo
# de total_amount. En este caso será el resultado del producto de ambos.
precioArticulo = df_articles.loc[idArticulo]['unit_price']
cantArticulo = my_df.loc[i]['quantity']
my_df.loc[i,'total_amount'] = precioArticulo * cantArticulo
# La iteracion tambien debe obtener datos de la columna de seller_name, ahora llamada nombreVendedor
idVendedor = my_df.loc[i]['seller_id']
nombreVendedor = df_sellers.loc[idVendedor]['seller_name']
my_df.loc[i,'seller_name'] = nombreVendedor
# Para finalizar, se puede limpiar la tabla eliminando las columnas que no se necesitan, como pueden
# ser order_id, article_id y/o seller_id. Para esto se utiliza el metodo del Dataframe .drop.
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]
# Por otro lado, también es bueno saber que existe una funcion de pandas llamada .merge
# Con esta funcion se pueden unir dos DataFrames para obtener un unico DataFrame de resultado.
# El resultado es similar a hacer un 'join' en la base de datos con dos tablas.
# Por ejemplo, siendo d1:
d1=pd.DataFrame({'mes': ['ene','feb','mar','abr'], 'ventas':[10,20,30,15]})
print('\nDataFrame d1:')
print (d1)
# Y siendo d2:
d2=pd.DataFrame({'mes': ['ene','feb','mar','abr'], 'costos':[7,16,25,12]})
print('\nDataFrame d2:')
print (d2)
# El df resultado será:
print('\nDataFrame luego de aplicar merge sobre d1 y d2:')
print(pd.merge(d1,d2))
# Siendo 'mes': ['ene','feb','mar','abr'] la 'key' que tienen en común.
# Para mas informacion: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html
DataFrame d1:
mes ventas
0 ene 10
1 feb 20
2 mar 30
3 abr 15
DataFrame d2:
mes costos
0 ene 7
1 feb 16
2 mar 25
3 abr 12
DataFrame luego de aplicar merge sobre d1 y d2:
mes ventas costos
0 ene 10 7
1 feb 20 16
2 mar 30 25
3 abr 15 12
# RESOLUCIÓN ANALÍTICA: Se crea un nuevo DataFrame en donde primero se agrupara por article_name y luego
# se sumaran las cantidades de cada articulo.
my_df2=my_df.groupby('article_name').sum()
por_cant = my_df2.sort_values('quantity', ascending=False) #ascending=True traeria los menos vendidos.
print(por_cant['quantity'].head(1)) #variando el valor de .head(x) podriamos traer los x mas vendidos.
article_name
HDD 413
Name: quantity, dtype: int64
# RESOLUCIÓN GRÁFICA: Se utilizará seaborn para realizar el plot.
sns.barplot(y=por_cant["quantity"], x=por_cant.index)
plt.xticks(rotation=75) #Si no se le da una rotacion, las nombres se superponen haciendose ilegibles.
plt.show() # Muestra la grafica.
print('\nSe observa que el articulo "HDD" tuvo mas unidades vendidas.')
Se observa que el articulo "HDD" tuvo mas unidades vendidas.
# RESOLUCIÓN ANALÍTICA: Se crea un nuevo DataFrame en donde primero se agrupara por article_name y luego
# se ordenaran segun el monto final de forma descendente. Elegimos los primeros 10.
df2 = (my_df.groupby(by='article_name').sum()).sort_values('total_amount',ascending=False).head(10)
print(df2['total_amount'])
article_name
Full Pc 538335.93
Notebook 251000.00
Smartphone 152250.00
Chair 69477.48
Tablet 48620.00
Monitor 47840.00
Netbook 46400.00
Scanner 40885.00
Motherboard 40268.58
CPU 37138.92
Name: total_amount, dtype: float64
# RESOLUCIÓN GRÁFICA: Se opto por tomar sólo los 10 primeros artículos que más ingresos proporcionaron
# Pero no puede hacerse una comparativa ni porcentajes porque no se trabaja con el total de los datos.
print('\nTop 10 articulos que mas ingresos generaron vistos en gráfica de torta: ')
plt.pie(x=df2['total_amount'], labels=df2.index)
plt.show()
print('\nTop 10 articulos que mas ingresos generaron vistos en gráfica barras horizontales: ')
plt.barh(df2.index,df2['total_amount'] , height=0.7) # height permite hacer las barras mas finas y
plt.show() # asi resultan mas fáciles de distinguir.
print('\nEn ambos casos se ve claramente que el articulo mas vendido fue "Full Pc".')
Top 10 articulos que mas ingresos generaron vistos en gráfica de torta:
Top 10 articulos que mas ingresos generaron vistos en gráfica barras horizontales:
En ambos casos se ve claramente que el articulo mas vendido fue "Full Pc".
# RESOLUCIÓN ANALÍTICA: Se crea otro nuevo DataFrame como copia del original, esta vez agrupado por
# seller_name y se hará la suma de los montos totales. Luego, se ordenará el DataFrame de tal forma que
# los mejores vendedores queden arriba y siga de forma descendente.
df4 = (my_df.groupby(by='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: Se elige un grafico de barras verticales y se procede a graficar.
plt.bar(df4.index,df4['total_amount'])
plt.xticks(rotation=60, ha="right") # ha="right" corrige el orden de los nombres para que coincidan con
plt.show() # las columnas.
print('\nSe observa que el vendedor Janel O Curran vendió un monto total superior a otros vendedores.')
Se observa que el vendedor Janel O Curran vendió un monto total superior a otros vendedores.
# RESOLUCIÓN ANALÍTICA: Se agrupan los datos segun la semana, se suman y se acomodan de forma
# descendente. De esa manera se obtienen los valores totales por semana.
df5 = (my_df.groupby(by='week').sum()).sort_values('total_amount',ascending=False)
print(df5['total_amount'])
week
1 507458.81
2 415364.44
3 329140.03
4 223844.56
Name: total_amount, dtype: float64
# RESOLUCIÓN GRÁFICA
plt.bar(df5.index,df5['total_amount'])
plt.show()
plt.pie(x=df5['total_amount'], labels=df5.index)
plt.show()
print('Se observa que la semana que mas se vendió fue la numero 1.')
Se observa que la semana que mas se vendió fue la numero 1.
# RESOLUCIÓN ANALITICA:
# ¿Cuales son los 5 paises que menos compraron en el mes?
print('\nTop 5 paises que menos compraron:')
df6 = (my_df.groupby(by='country_name').sum()).sort_values('total_amount',ascending=True).head()
plt.bar(df6.index,df6['total_amount'])
plt.show()
print('\nEn el grafico se ve claramente que, de los ultimos 5, Puerto Rico fue el pais que menos compró')
Top 5 paises que menos compraron:
En el grafico se ve claramente que, de los ultimos 5, Puerto Rico fue el pais que menos compró
# RESOLUCIÓN ANALITICA:
# Previsualizar el DF final
# -------------------------
# "Pais" "Semana" "Ventas"
# Pais1 4 monto
# Pais2 3 monto
# ----------------------
# Paisn x monto
# 1) Se obtiene el total por semana por pais
df_pais_semana = my_df.groupby(['country_name', 'week']).sum()
#print(df_pais_semana) nos ayuda a visualizar lo obtenido
# 2) Se obtiene la lista de paises
paises = my_df.groupby('country_name').sum().index.values
# 3) Se genera el DataFrame final
df_final = pd.DataFrame(columns=['Semana', 'Ventas'], index=paises)
#print(df_final)
# 4) Se procesa el DataFrame df_pais_semana y se obtienen las ventas minimas por semana.
# Se guardan en df_final.
for p in paises:
df = df_pais_semana.loc[p]
v = df.min()['total_amount']
s = df[(df['total_amount'] == v)].index[0]
df_final.loc[p] = {'Semana': s, 'Ventas': v}
df_final['Semana'] = df_final['Semana'].astype(int)
print(df_final)
# RESOLUCION GRÁFICA:
g = sns.barplot(data = df_final, x = df_final.index, y = 'Ventas', hue = 'Semana')
g.set_xticklabels(labels=df_final.index, rotation=90)
print('\nEn el siguiente grafico de seaborn se utilizan colores para distinguir las semanas, y el alto de la columna')
print('ilustraria el monto vendido. Si bien responde a la pregunta, hay valores que se pierden por su bajo monto.')
plt.show()
plt.bar(df_final.index,df_final['Semana'])
plt.xticks(rotation=90)
print('\nEn este caso, se grafica la misma respuesta independientemente de los montos semanales.')
print('Responde de manera mas clara a la pregunta.')
plt.show()
Semana Ventas
Argentina 4 18681.2
Bolivia 4 411.44
Brazil 3 79341.5
Chile 1 2029.66
Colombia 4 29169.06
Costa Rica 4 1450.0
Ecuador 1 67.5
El Salvador 4 5579.99
Guatemala 2 1000.88
Honduras 4 1781.3
Mexico 3 19577.33
Paraguay 4 690.0
Peru 4 4166.04
Puerto Rico 2 8.85
Uruguay 4 164.98
Venezuela 4 1476.2
En el siguiente grafico de seaborn se utilizan colores para distinguir las semanas, y el alto de la columna
ilustraria el monto vendido. Si bien responde a la pregunta, hay valores que se pierden por su bajo monto.
En este caso, se grafica la misma respuesta independientemente de los montos semanales.
Responde de manera mas clara a la pregunta.
# RESOLUCIÓN ANALITICA
df_mejores = (my_df.groupby(by='seller_name').sum()).sort_values('total_amount',ascending=False).head(3)
print('\nDataFrame mejores vendedores')
print(df_mejores)
df_peores = (my_df.groupby(by='seller_name').sum()).sort_values('total_amount',ascending=True).head(3)
print('\nDataFrame peores vendedores')
print(df_peores)
plt.bar(df_mejores.index,df_mejores['total_amount'])
plt.xticks(rotation=90)
plt.bar(df_peores.index,df_peores['total_amount'])
plt.xticks(rotation=90)
print('\nMejores vendedores vs Peores vendedores')
plt.show()
print('\nTotal vendido entre mejores vendedores y peores vendedores:')
total_mejores = df_mejores.sum().total_amount
total_peores = df_peores.sum().total_amount
plt.bar('Mejores vendedores',total_mejores)
plt.bar('Peores vendedores',total_peores)
plt.bar('Diferencia',total_mejores-total_peores)
plt.show()
porcentual = math.trunc(((total_mejores/total_peores)-1)*100)
print('\nTeniendo en cuenta los datos, se estima una mejora de alrededor de ',porcentual,'% en las ventas de los ')
print('peores vendedores trabajando en conjunto con el mejor equipo')
DataFrame mejores vendedores
week quantity total_amount
seller_name
Janel O'Curran 174 703 192832.47
Brockie Patience 125 441 142709.88
Oliviero Charkham 167 555 141329.76
DataFrame peores vendedores
week quantity total_amount
seller_name
Cirilo Grandham 131 470 45009.40
Cornie Wynrehame 159 523 52253.57
Tobin Roselli 126 519 56984.42
Mejores vendedores vs Peores vendedores
Total vendido entre mejores vendedores y peores vendedores:
Teniendo en cuenta los datos, se estima una mejora de alrededor de 209 % en las ventas de los
peores vendedores trabajando en conjunto con el mejor equipo