Portfolio Project: Online Retail Exploratory Data Analysis with Python¶
Overview¶
In this project, you will step into the shoes of an entry-level data analyst at an online retail company, helping interpret real-world data to help make a key business decision.
Case Study¶
In this project, you will be working with transactional data from an online retail store. The dataset contains information about customer purchases, including product details, quantities, prices, and timestamps. Your task is to explore and analyze this dataset to gain insights into the store's sales trends, customer behavior, and popular products.
By conducting exploratory data analysis, you will identify patterns, outliers, and correlations in the data, allowing you to make data-driven decisions and recommendations to optimize the store's operations and improve customer satisfaction. Through visualizations and statistical analysis, you will uncover key trends, such as the busiest sales months, best-selling products, and the store's most valuable customers. Ultimately, this project aims to provide actionable insights that can drive strategic business decisions and enhance the store's overall performance in the competitive online retail market.
Prerequisites¶
Before starting this project, you should have some basic knowledge of Python programming and Pandas. In addition, you may want to use the following packages in your Python environment:
- pandas
- numpy
- seaborn
- matplotlib
These packages should already be installed in Coursera's Jupyter Notebook environment, however if you'd like to install additional packages that are not included in this environment or are working off platform you can install additional packages using !pip install packagename within a notebook cell such as:
!pip install pandas!pip install matplotlib
Project Objectives¶
- Describe data to answer key questions to uncover insights
- Gain valuable insights that will help improve online retail performance
- Provide analytic insights and data-driven recommendations
Dataset¶
The dataset you will be working with is the "Online Retail" dataset. It contains transactional data of an online retail store from 2010 to 2011. The dataset is available as a .xlsx file named Online Retail.xlsx. This data file is already included in the Coursera Jupyter Notebook environment, however if you are working off-platform it can also be downloaded here.
The dataset contains the following columns:
- InvoiceNo: Invoice number of the transaction
- StockCode: Unique code of the product
- Description: Description of the product
- Quantity: Quantity of the product in the transaction
- InvoiceDate: Date and time of the transaction
- UnitPrice: Unit price of the product
- CustomerID: Unique identifier of the customer
- Country: Country where the transaction occurred
Tasks¶
You may explore this dataset in any way you would like - however if you'd like some help getting started, here are a few ideas:
- Load the dataset into a Pandas DataFrame and display the first few rows to get an overview of the data.
- Perform data cleaning by handling missing values, if any, and removing any redundant or unnecessary columns.
- Explore the basic statistics of the dataset, including measures of central tendency and dispersion.
- Perform data visualization to gain insights into the dataset. Generate appropriate plots, such as histograms, scatter plots, or bar plots, to visualize different aspects of the data.
- Analyze the sales trends over time. Identify the busiest months and days of the week in terms of sales.
- Explore the top-selling products and countries based on the quantity sold.
- Identify any outliers or anomalies in the dataset and discuss their potential impact on the analysis.
- Draw conclusions and summarize your findings from the exploratory data analysis.
Task 1: Load the Data¶
In [1]:
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
df = pd.read_excel("Online Retail.xlsx")
print(df.info())
Null customer Id's are filled with '0' for now, to be filtered later. Also will make a revenue column, and remove non-product data from our set before checking out key statistics:¶
In [2]:
df["CustomerID"] = df["CustomerID"].fillna(0).astype("int32")
df['revenue'] = df['Quantity'] * df['UnitPrice']
#remove outlier orders with crazy price, must be mistake. this one basket has a price of 600 instead of its usual $3
df= df[~((df['StockCode'] == 22502) & (df['UnitPrice'] > 600))]
#remove postage, manuals, and bank charges, ect -- we just want to see sales data.
df = df[~df['StockCode'].isin(['M','DOT','BANK CHARGES', 'CRUK','AMAZONFEE','POST',
'PADS','B','D','C2','gift_0001_30','gift_0001_40',
'gift_0001_50','gift_0001_20','gift_0001_10','S'])]
unique_items = df['StockCode'].nunique()
unique_customers = df["CustomerID"].nunique()
unique_countries = df['Country'].nunique()
total_units_sold = df['Quantity'].sum()
total_revenue = df['revenue'].sum()
print(f"Unique products: {unique_items}")
print(f"Unique customers: {unique_customers}")
print(f"Unique countires: {unique_countries}")
print(f"total units sold: {total_units_sold}")
print(f"total revenue: ${total_revenue}")
# Check for duplicate rows
Below compares sales/revenue by day-of-week to see any patterns, and check month over month growth. Saturday has no sales data, and the monthly chart indicates a strong end of year growth:¶
In [3]:
df["DayOfWeek"] = df["InvoiceDate"].dt.day_name()
df["Month"] = df["InvoiceDate"].dt.month_name()
rev_by_day = df.groupby('DayOfWeek')['revenue'].sum()
rev_by_month = df.groupby('Month')['revenue'].sum()
# Sales by Day of the Week
plt.figure(figsize=(10, 4))
sales_by_day = rev_by_day.reindex(["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"])
sales_by_day.plot(kind="bar", color="skyblue")
plt.title("Revenue by Day of the Week")
plt.xlabel("Day")
plt.ylabel("Total Revenue")
plt.xticks(rotation=45)
plt.show()
# Sales by Month
plt.figure(figsize=(10, 4))
sales_by_month = rev_by_month.reindex(["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"])
sales_by_month.plot(kind="bar", color="lightgreen")
plt.title("Revenue by Month")
plt.xlabel("Month")
plt.ylabel("Total Revenue")
plt.xticks(rotation=45)
plt.show()
Which countries give the most business? The answer is UK by far. We can try to expand marketing to the other top countries:¶
In [4]:
countries = df.groupby('Country')['revenue'].sum().sort_values(ascending=False).head(5)
print(countries)
plt.figure(figsize=(12, 7))
# Plot the pie chart with labels
plt.figure(figsize=(12, 7))
countries.plot(kind="pie", autopct='%1.1f%%')
# Add title and show the chart
plt.title("Top 5 Countries by Revenue")
plt.ylabel("") # Removes default y-axis label
plt.show()
countries.plot(kind="bar")
plt.ylabel('Revenue')
plt.xlabel('Product ID')
Out[4]:
Below shows top products by quantity and revenue. The biggest quantity is not always the biggest revenue:¶
In [5]:
products = df.groupby('Description')['revenue'].sum().sort_values(ascending=False).head(15)
products2 = df.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(15)
productss = df.groupby('StockCode').agg({
'revenue': 'sum',
'Description': 'first', # Keeps the first description for each StockCode
'Quantity': 'sum'
}).sort_values(by='revenue', ascending=False).head(15)
print(productss)
# Plot the pie chart with labels
plt.figure(figsize=(12, 7))
products.plot(kind="pie", autopct='%1.1f%%')
# Add title and show the chart
plt.title("Top 15 Products by Revenue")
plt.ylabel("") # Removes default y-axis label
plt.show()
products.plot(kind="bar")
plt.ylabel('Revenue')
plt.xlabel('Product ID')
# Plot the pie chart with labels
plt.figure(figsize=(12, 7))
products2.plot(kind="pie", autopct='%1.1f%%')
# Add title and show the chart
plt.title("Top 15 Products by Quantity")
plt.ylabel("") # Removes default y-axis label
plt.show()
products2.plot(kind="bar")
plt.ylabel('Quantity')
plt.xlabel('Product ID')
Out[5]:
below takes a look at the outliers in quantity, unitprice, and revenue. negative values were due to returns and canceled out when summing:¶
In [6]:
plt.figure(figsize=(12, 5))
sns.boxplot(x=df["Quantity"])
plt.title("Outliers in Quantity")
plt.show()
plt.figure(figsize=(12, 5))
sns.boxplot(x=df["UnitPrice"])
plt.title("Outliers in Unit Price")
plt.show()
sns.boxplot(x=df["revenue"])
plt.title("Outliers in Revenue")
plt.show()
Look below to see the country UK's top 25 orders sorted by revenue¶
In [7]:
UK = df[df['Country'] == 'United Kingdom']
UK.sort_values(by='revenue', ascending = False).head(25)
#UK.sort_values(by='Quantity', ascending = True).head(25)
Out[7]:
Below explores the top 15 customers on a pie chart, including all of the null values previously filled with 0:¶
In [8]:
top_cust_rev = df.groupby('CustomerID')['revenue'].sum().sort_values(ascending=False).head(15)
top_cust_rev1 = df.groupby('CustomerID').agg({
'revenue':'sum',
'Quantity':'sum',
'Country':'first'}).sort_values(by = 'revenue',ascending = False).head(15)
print(top_cust_rev1)
plt.figure(figsize=(14, 10))
top_cust_rev.plot(kind='pie', autopct='%1.1f%%')
plt.title("Top 15 CustomerID's including all Nulls as 0")
plt.ylabel("")
Out[8]:
Filtering the null values out of the top customer chart:¶
In [9]:
tops_cust= df[~df['CustomerID'].isin([0])]
top_custs = tops_cust.groupby('CustomerID')['revenue'].sum().sort_values(ascending=False).head(15)
plt.figure(figsize=(12, 7))
top_custs.plot(kind='pie',autopct='%1.1f%%')
plt.title('Top 15 CustomerIDs Excluding all nulls')
plt.ylabel("")
#lets see where our top customers are from, and their favorite products
t_cust = tops_cust.groupby('CustomerID').agg({'revenue':'sum','Quantity':'sum','UnitPrice':'first','Country':'first', 'Description': lambda x: x.mode().iloc[0] }).sort_values(by='revenue', ascending = False).head(25)
print(t_cust)
Lets see a interactive map of the countries we do business with:¶
In [10]:
!pip install plotly
import plotly.express as px
#revenue by country
country_revenue = df.groupby("Country")["revenue"].sum().reset_index()
fig = px.choropleth(
country_revenue,
locations="Country",
locationmode="country names",
color="revenue",
color_continuous_scale="reds",
title="Revenue by Country"
)
fig.show()
Below we take a look at our best selling products, as well as the products which bring in the most revenue:¶
In [11]:
## analysis removing all customer null entries
products123 = tops_cust.groupby('Description')['revenue'].sum().sort_values(ascending=False).head(15)
products1234 = tops_cust.groupby('Description').agg({
'revenue':'sum',
'Country': 'max'}).sort_values(by ='revenue',ascending=False).head(15)
print(products1234)
# Plot the pie chart with labels
plt.figure(figsize=(12, 7))
products123.plot(kind="pie", autopct='%1.1f%%')
# Add title and show the chart
plt.title("Top 15 Products by Revenue")
plt.ylabel("") # Removes default y-axis label
plt.show()
products123.plot(kind="bar")
plt.ylabel('Revenue')
plt.xlabel('Product ID')
Out[11]:
Notice, the stock items have different pricepoints throughout the year. so it would be a good idea to see which price points proved to generate the most revenue. We need more data, such as profit, to really see the real optimal price. the below graph shows the top 20 products and their optimal pricing based on quantity sold: (see revenue graph in the next cell)¶
In [12]:
#we noticed prices for same products are different at times,
#we want to find out what price performed best, to see what prices we can get away with
#FIND MOST SALES PER PRICE POINT
price_performance = df.groupby(["StockCode", "UnitPrice"])["Quantity"].sum().reset_index()
best_prices = price_performance.loc[price_performance.groupby("StockCode")["Quantity"].idxmax()]
# Show first few rows
print(best_prices.head())
# Plot top 20 best-performing prices as a bar chart
plt.figure(figsize=(12, 6))
top_products = best_prices.sort_values(by="Quantity", ascending=False).head(20)
plt.bar(top_products["StockCode"].astype(str), top_products["UnitPrice"], color="skyblue")
plt.xlabel("Product StockCode")
plt.ylabel("Best Performing Price")
plt.title("Best Performing Prices for Top 20 Products")
plt.xticks(rotation=90)
plt.show()
Below is the graph filtering the different prices of our top 20 products BY REVENUE. It goes through sales at each pricepoint and finds the pricepoint that led to the greatest revenue for that item. the X axis is the total revenue generated at the optimal pricepoint in the data label.¶
In [13]:
# Find best price by revenue
price_performance = df.groupby(["StockCode", "UnitPrice"])["Quantity"].sum().reset_index()
price_performance["Revenue"] = price_performance["UnitPrice"] * price_performance["Quantity"]
best_prices = price_performance.loc[price_performance.groupby("StockCode")["Revenue"].idxmax()]
# Select top 20 revenue-generating products
top_products = best_prices.sort_values(by="Revenue", ascending=False).head(20)
# Create the bar chart
plt.figure(figsize=(13, 8))
bars = plt.bar(top_products["StockCode"].astype(str), top_products["Revenue"], color="green")
# Add labels above bars (show price instead of revenue)
for bar, price in zip(bars, top_products["UnitPrice"]):
yval = bar.get_height() # Get revenue (height of the bar)
plt.text(bar.get_x() + bar.get_width()/2, yval + (yval * 0.02), f"${price:.2f}", ha="center", va="bottom", fontsize=10)
# Formatting
plt.xlabel("Product StockCode")
plt.ylabel("Total Revenue")
plt.title("Best Performing Prices for Top 20 Products")
plt.xticks(rotation=90)
plt.show()
curious on that item (22423 stockcode) that produced the most revenue:¶
In [14]:
df['StockCode'] = df['StockCode'].astype(str)
top_item = df[df['StockCode'] == '22423']
prices = top_item.groupby('UnitPrice')['Quantity'].sum().sort_values(ascending=False)
print(prices.head(50))
In [ ]:
Maximum unit prices for items¶
In [15]:
ppp=df.groupby('StockCode').agg({'UnitPrice':'max','Description':'first','Quantity':'sum'}).sort_values(by = 'UnitPrice', ascending=False)
print(ppp.head(25))
Below I was curious about a top customer's (14646 on customer pie chart) favorite items:¶
In [16]:
df['CustomerID'] = df['CustomerID'].astype(str)
t = df[df['CustomerID'] == '14646']
tt = t.groupby('Description').agg({'Quantity':'sum','UnitPrice':'max','revenue':'sum'}).sort_values(by='Quantity',ascending=False)
print(tt.head(50))
In [ ]: