You are a sales analyst for an e-commerce company. You have been tasked with analyzing the sales data to determine which regions and product categories are generating the most revenue. You have a dataset containing information about sales, including the region, product category, and sales amount.
Using Pandas, write a program to:
Hint: You can use the groupby function to group the data by region and product category, and calculate the total sales amount for each group. Then, use the pivot_table function to reshape the data and calculate the percentages.
Region,Product Category,Sales Amount
North,Electronics,1000
North,Clothing,800
South,Electronics,1200
South,Electronics,900
East,Clothing,1100
East,Electronics,700
West,Electronics,1000
West,Electronics,900
North,Clothing,600
South,Clothing,1000
import pandas as pd
import sys
print('Python version ' + sys.version)
print('Pandas version ' + pd.__version__)
Python version 3.11.7 | packaged by Anaconda, Inc. | (main, Dec 15 2023, 18:05:47) [MSC v.1916 64 bit (AMD64)] Pandas version 2.2.1
df = pd.read_clipboard(sep=",")
df
Region | Product Category | Sales Amount | |
---|---|---|---|
0 | North | Electronics | 1000 |
1 | North | Clothing | 800 |
2 | South | Electronics | 1200 |
3 | South | Electronics | 900 |
4 | East | Clothing | 1100 |
5 | East | Electronics | 700 |
6 | West | Electronics | 1000 |
7 | West | Electronics | 900 |
8 | North | Clothing | 600 |
9 | South | Clothing | 1000 |
# group the sales data by region and product category
group = df.groupby(['Region','Product Category'])
# total sales of group
group.sum()
Sales Amount | ||
---|---|---|
Region | Product Category | |
East | Clothing | 1100 |
Electronics | 700 | |
North | Clothing | 1400 |
Electronics | 1000 | |
South | Clothing | 1000 |
Electronics | 2100 | |
West | Electronics | 1900 |
# flatten the group
agg = group['Sales Amount'].sum().reset_index()
agg
Region | Product Category | Sales Amount | |
---|---|---|---|
0 | East | Clothing | 1100 |
1 | East | Electronics | 700 |
2 | North | Clothing | 1400 |
3 | North | Electronics | 1000 |
4 | South | Clothing | 1000 |
5 | South | Electronics | 2100 |
6 | West | Electronics | 1900 |
# calculate total sales amount
total_sales = agg['Sales Amount'].sum()
# calculate percentage of total sales for each region and product category
agg['Percentage of Total Sales'] = (agg['Sales Amount'] / total_sales) * 100
agg
Region | Product Category | Sales Amount | Percentage of Total Sales | |
---|---|---|---|---|
0 | East | Clothing | 1100 | 11.956522 |
1 | East | Electronics | 700 | 7.608696 |
2 | North | Clothing | 1400 | 15.217391 |
3 | North | Electronics | 1000 | 10.869565 |
4 | South | Clothing | 1000 | 10.869565 |
5 | South | Electronics | 2100 | 22.826087 |
6 | West | Electronics | 1900 | 20.652174 |
# get the top 3 regions
agg.sort_values(by='Percentage of Total Sales', ascending=False).head(3)
Region | Product Category | Sales Amount | Percentage of Total Sales | |
---|---|---|---|---|
5 | South | Electronics | 2100 | 22.826087 |
6 | West | Electronics | 1900 | 20.652174 |
2 | North | Clothing | 1400 | 15.217391 |
Here is an alternative solution using pivot tables.
# pivot the data to get sales amount by region and product category
pivot = pd.pivot_table(df, values='Sales Amount', index='Region', columns='Product Category', aggfunc='sum')
pivot
Product Category | Clothing | Electronics |
---|---|---|
Region | ||
East | 1100.0 | 700.0 |
North | 1400.0 | 1000.0 |
South | 1000.0 | 2100.0 |
West | NaN | 1900.0 |
# calculate total sales amount
total_sales = df['Sales Amount'].sum()
total_sales
9200
# calculate percentage of total sales for each region and product category
agg = (pivot / total_sales) * 100
agg
Product Category | Clothing | Electronics |
---|---|---|
Region | ||
East | 11.956522 | 7.608696 |
North | 15.217391 | 10.869565 |
South | 10.869565 | 22.826087 |
West | NaN | 20.652174 |
With the shape of this DataFrame ordering by percentage of total sales is not really possible.
# the stack function lets us reshape the data so we can sort by the percentages
agg.stack().sort_values(ascending=False).head(3)
Region Product Category South Electronics 22.826087 West Electronics 20.652174 North Clothing 15.217391 dtype: float64
This tutorial was created by HEDARO