EDA: Mexico Toys Sales
SELECT
s.Date,
s.Units,
p.Product_Name,
p.Product_Category,
p.Product_Cost,
p.Product_Price,
st.Store_Name,
st.Store_City,
st.Store_Location,
st.Store_Open_Date,
FROM 'sales.csv' s
LEFT JOIN 'products.csv' p ON s.Product_ID = p.Product_ID
LEFT JOIN 'stores.csv' st ON s.Store_ID = st.Store_ID
SELECT
i.Stock_On_Hand AS Stock
,p.Product_Name
,p.Product_Category
,p.Product_Cost
,p.Product_Price
,st.Store_Name
,st.Store_City
,st.Store_Location
FROM 'inventory.csv' i
LEFT JOIN 'products.csv' p ON i.Product_ID = p.Product_ID
LEFT JOIN 'stores.csv' st ON i.Store_ID = st.Store_ID
# Import libraries
import pandas as pd
import numpy as np
import seaborn as sns
sns.set_theme()
sns.set(font_scale=0.8)
sales.head()
Data Processing
Checking data types and null values
sales.info()
inventory.info()
Checking unique values of the categories
categoricals = [ 'Product_Category',
'Store_City', 'Store_Location']
for i in categoricals:
print(i,sales[i].unique())
categoricals = [ 'Product_Category',
'Store_City', 'Store_Location']
for i in categoricals:
print(i,sales[i].value_counts(normalize=True),'\n')
Changing data types
# Change the dates to datetime type
sales.Date = pd.to_datetime(sales.Date,infer_datetime_format=True)
sales.Store_Open_Date = pd.to_datetime(sales.Store_Open_Date,infer_datetime_format=True)
sales.info()
# Change cost and price columns to float
dollar_list = ['Product_Cost','Product_Price']
sales[dollar_list] = sales[dollar_list].applymap(lambda x: x.replace('$','')).astype(float)
inventory[dollar_list] = inventory[dollar_list].applymap(lambda x: x.replace('$','')).astype(float)
sales.head()
Creating new columns
sales['Total_Price'] = sales.Product_Price * sales.Units
sales['Profit_Per_Product'] = sales.Product_Price - sales.Product_Cost
sales['Profit_Percentage'] = (sales.Profit_Per_Product /sales.Product_Price) * 100
sales['Profit_Percentage'] = sales['Profit_Percentage'].astype(np.float16)
sales['Total_Profit'] = sales.Profit_Per_Product * sales.Units
sales.head()
inventory['Total_Price'] = inventory.Product_Price * inventory.Stock
inventory['Profit_Per_Product'] = inventory.Product_Price - inventory.Product_Cost
inventory['Profit_Percentage'] = (inventory.Profit_Per_Product /inventory.Product_Price) * 100
inventory['Profit_Percentage'] = inventory['Profit_Percentage'].astype(np.float16)
inventory['Total_Profit'] = inventory.Profit_Per_Product * inventory.Stock
inventory.head()
Which product categories drive the biggest profits? Is this the same across store locations?
df = sales.groupby('Product_Name')['Units'].sum().sort_values(ascending=False)
plt = sns.barplot(x=df.values,y=df.index,palette='viridis')
plt.set_title('Products by Units Sold')
df = sales.groupby('Product_Name')['Total_Profit'].sum().sort_values(ascending=False)
plt = sns.barplot(x=df.values,y=df.index,palette='viridis')
plt.set_title('Products by Total Profit')
profit_per_cat = (
sales
.groupby('Product_Category')['Total_Profit']
.sum()
.sort_values(ascending=False)
)
profit_per_location = (
sales
.groupby(['Store_Location','Product_Category'])['Total_Profit']
.sum()
.sort_values(ascending=False)
.reset_index()
)
profit_per_city = (
sales
.groupby('Store_City')['Total_Profit']
.sum()
.sort_values(ascending=False)
)
profit_per_store = (
sales
.groupby(['Store_Name','Product_Category'])['Total_Profit']
.sum()
.sort_values(ascending=False)
.reset_index()
)
plt = sns.barplot( x=profit_per_cat.index, y=profit_per_cat.values)
plt.set(title='Total Profit per Category')
plt.ticklabel_format(style='plain', axis='y',useOffset=False)
plt.bar_label(plt.containers[0])
Toys has been the most profitable category of products of the company with a total profit of 1 million $ aprox. Sports & Outdoors has been the least profitable category with a total profit of +500,000$.
plt = sns.barplot( x='Store_Location', y='Total_Profit',
data=profit_per_location, hue='Product_Category')
plt.set(title='Total Profit per Category per Location')
plt.ticklabel_format(style='plain', axis='y',useOffset=False)
#plt.bar_label(plt.containers[0])
Per category the Toys line maintain a considerable relevance driving the biggest profits in Downtown and Residential locations, while being second place in Commercial and Airports. With Electronics taking the first place in Commercial and Airports.
plt = sns.barplot( x=profit_per_city.values, y=profit_per_city.index, palette='rocket')
plt.set(title='Total Profit per City')
#plt.ticklabel_format(style='plain', axis='y',useOffset=False)
plt = plt.bar_label(plt.containers[0])
p1 = profit_per_store.groupby('Store_Name')['Total_Profit'].sum()
top10 = p1.nlargest(10).index
p2 = profit_per_store.query('Store_Name in @top10')
order = ['Toys','Electronics','Art & Crafts','Games','Sports & Outdoors']
plt = sns.histplot(data=p2, y='Store_Name', hue='Product_Category',
hue_order=order, weights='Total_Profit', multiple='stack')
plt.set(title='Top 10 Total Profit per Store')
Seasonal trends or patterns
sales['month'] = sales.Date.dt.month
sales['year'] = sales.Date.dt.year
bydate = sales.groupby(['month', 'year'])
per_month = bydate['Units'].sum()
per_month = per_month.reset_index().pivot(index='month',columns='year',values='Units')
plt = sns.lineplot(per_month)
plt.set_title('Units Sold by Month')
There seems to be an upward trend between February and April. And one downward between July and August.
It also seems that between April and July a similar amount of units sold is maintained.
input_1
Units
per_cat = sales.groupby(['month','year','Product_Category'])[input_1].sum()
per_cat = per_cat.reset_index()
#.pivot(index='month',columns='year',values='Units')
g = sns.FacetGrid(per_cat,col='Product_Category', hue='year',height=3, col_wrap=3)
g.map(sns.lineplot,'month',input_1)
input_2
Total_Profit
per_cat = sales.groupby(['month','year','Product_Category'])[input_2].sum()
per_cat = per_cat.reset_index()
g = sns.FacetGrid(per_cat,col='Product_Category', hue='year',height=3, col_wrap=3)
g.map(sns.lineplot,'month',input_2)
Status of Inventory
print('The total value of products in the inventory equals to ',int(inventory['Total_Price'].sum()),'$')
data = inventory.groupby('Product_Name')['Total_Price'].sum().sort_values(ascending=False).reset_index()
plt = sns.barplot( data, x='Total_Price', y='Product_Name', palette='rocket')
plt.set(title='Total Value of Inventory per Product')
plt = plt.bar_label(plt.containers[0])
data = inventory.groupby('Store_City')['Total_Price'].sum().sort_values(ascending=False).reset_index()
plt = sns.barplot( data, x='Total_Price', y='Store_City', palette='rocket')
plt.set(title='Total Value of Inventory per City')
#plt.ticklabel_format(style='plain', axis='y',useOffset=False)
plt = plt.bar_label(plt.containers[0])
data = inventory.groupby('Store_Name')['Total_Price'].sum().sort_values(ascending=False).nlargest(15).reset_index()
plt = sns.barplot( data, x='Total_Price', y='Store_Name', palette='rocket')
plt.set(title='Top 15 Stores by Inventory Value')
plt = plt.bar_label(plt.containers[0])