Exploratory Data Analysis on Superstore Dataset using Python

Exploratory data analysis, or EDA, is a critical first step in any data science project. EDA is the process of investigating and summarizing a dataset to uncover insights, spot irregularities, test assumptions, and determine next steps. Before jumping into building complicated machine learning models, a data scientist needs to develop a solid understanding of the data they‘re working with.

In this article, we‘ll walk through performing EDA on a retail Superstore dataset using Python. The Superstore dataset contains sales data for a fictitious store, including information on products, customers, orders, sales, and profits. Through EDA, we aim to better understand the factors impacting the store‘s performance and uncover opportunities for improvement.

Understanding the Superstore Dataset

Let‘s start by loading the required Python libraries and the Superstore dataset:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

df = pd.read_csv(‘SampleSuperstore.csv‘)

The dataset contains 9,994 rows and 13 columns:

• Ship Mode: Shipping method (Standard Class, First Class, Second Class, Same Day)
• Segment: Customer segment (Consumer, Corporate, Home Office)
• Country: United States
• City: City of customer location
• State: State of customer location
• Postal Code: Postal code of customer
• Region: Region of customer (Central, East, South, West)
• Category: Product category (Furniture, Office Supplies, Technology)
• Sub-Category: Product sub-category
• Sales: Revenue from order
• Quantity: Number of items in order
• Discount: % discount applied
• Profit: Profit earned on order

With this information, we hope to answer questions like:

• Which product categories and sub-categories are performing best? Worst?
• Which regions and customer segments are most profitable?
• How do discounts impact profits? Are there optimal discount levels?
• Are there any problem areas that are reducing profits and need attention?

Data Cleaning and Preparation

Before diving into analysis, we need to check data quality and clean the data if needed. Looking at the output of df.info(), we see the dataset has no missing values which is great. However, we notice a few columns that likely won‘t be useful for our analysis:

df = df.drop([‘Country‘, ‘Postal Code‘], axis=1)

Since all data is from the United States, the Country column provides no benefit. The Postal Code column is also too granular, so we‘ll remove both.

Univariate Analysis

Now we‘re ready to start exploring individual variables. We want to understand the central tendency, spread, and shape of each variable‘s distribution. Let‘s look at the numerical variables first.

Examining df.describe() shows:

• The average order profit is $229.86 with a standard deviation of $3,221.44, indicating very high variance
• -6,599.98 minimum profit and 8,399.98 maximum profit suggests some large unprofitable orders
• Average discount percentage of 15.6%
• Standard deviation of sales is nearly 7x the mean, so order sizes/revenues vary considerably
• Some orders with very high quantities up to 14 items

Already we see signs of a small number of large, unprofitable orders that may be skewing the overall numbers. We‘ll need to investigate those further.

Next let‘s visualize the distributions. Histograms are a great way to view the shape of a distribution:

plt.figure(figsize=(12,8))
plt.subplot(2,2,1)
sns.histplot(data=df, x=‘Sales‘, bins=30, kde=True)
plt.subplot(2,2,2)
sns.histplot(data=df, x=‘Profit‘, bins=30, kde=True)
plt.subplot(2,2,3)
sns.histplot(data=df, x=‘Quantity‘, bins=30, kde=True)
plt.subplot(2,2,4)
sns.histplot(data=df, x=‘Discount‘, bins=30, kde=True)
plt.tight_layout()
plt.show()

We observe:

• Sales has a right skew with most orders under $500 but a long tail of larger orders
• Profit is normally distributed and centered around 0, but with significant density below 0 (unprofitable orders)
• Quantity is heavily concentrated at 1-2 items with a very long right tail
• Discount is right skewed with most orders receiving no or low discount but some receiving deep discounts

Let‘s also look at the categorical variable distributions. Bar plots work well to show the frequency of categorical values:

plt.figure(figsize=(12,8))
plt.subplot(2,2,1)
sns.countplot(data=df, x=‘Ship Mode‘)
plt.subplot(2,2,2)
sns.countplot(data=df, x=‘Segment‘)
plt.subplot(2,2,3)
sns.countplot(data=df, x=‘Category‘)
plt.subplot(2,2,4)
sns.countplot(data=df, x=‘Region‘)
plt.tight_layout()
plt.show()

We see the most common shipping method is Standard Class, most customers are Consumer segment, Office Supplies is the largest product category, and the Central region has the highest volume.

Bivariate Analysis

Let‘s investigate relationships between variables, like how customer segment, product category, and region relate to sales and profit.

Looking at the correlation matrix using sns.heatmap(df.corr()) shows Profit and Sales are moderately positively correlated while Discount and Profit have a moderately negative correlation. As discounts increase, profits tend to decrease.

Catplot is useful for comparing distributions of a numerical variable across levels of a categorical variable:

plt.figure(figsize=(12,6))
plt.subplot(1,2,1)
sns.catplot(data=df, x=‘Category‘, y=‘Sales‘, kind=‘box‘, height=4, aspect=1.5, order=df.Category.value_counts().index)
plt.subplot(1,2,2)
sns.catplot(data=df, x=‘Category‘, y=‘Profit‘, kind=‘box‘, height=4, aspect=1.5, order=df.Category.value_counts().index)
plt.tight_layout()
plt.show()

Technology has the highest average order size and profit, while Furniture has the lowest average profit with some very large unprofitable orders.

Let‘s compare profit across regions:

plt.figure(figsize=(12,6))
sns.catplot(data=df, x=‘Region‘, y=‘Profit‘, kind=‘box‘, height=5, aspect=2, order=df.Region.value_counts().index)
plt.tight_layout()
plt.show()

The South region has noticeably lower profit than the other three. It has more outlier unprofitable orders.

Segmented Analysis

To go deeper, we can segment the data and analyze the subsets. One way to segment is by product subcategory:

furniture_df = df[(df.Category == ‘Furniture‘)] office_df = df[(df.Category == ‘Office Supplies‘)] tech_df = df[(df.Category == ‘Technology‘)]

plt.figure(figsize=(12,6))
plt.subplot(1,3,1)
sns.barplot(data=furniture_df, x=‘Sub-Category‘, y=‘Profit‘, ci=None, color=‘salmon‘)
plt.xticks(rotation=45)
plt.title(‘Furniture‘)
plt.subplot(1,3,2)
sns.barplot(data=office_df, x=‘Sub-Category‘, y=‘Profit‘, ci=None, color=‘salmon‘)
plt.xticks(rotation=45)
plt.title(‘Office Supplies‘)
plt.subplot(1,3,3)
sns.barplot(data=tech_df, x=‘Sub-Category‘, y=‘Profit‘, ci=None, color=‘salmon‘)
plt.xticks(rotation=45)
plt.title(‘Technology‘)
plt.tight_layout()
plt.show()

Bookcases, Supplies, and Copiers are the least profitable subcategories in their respective categories. In fact, Bookcases and Supplies have negative average profits. This suggests these subcategories should be a focus area for improvement.

We also observe the Office Supplies category has much lower profits across all subcategories compared to Furniture and Technology. The gap in profitability may indicate Office Supplies are underpriced or costs are too high.

Key Takeaways and Recommendations

Through this exploratory analysis, we‘ve uncovered several key insights about Superstore‘s sales and profits:

1. A small number of large unprofitable orders, especially in Furniture and Office Supplies, are dramatically reducing overall profits. These should be investigated to understand the source of the losses (e.g. high production costs, underpricing, quality issues and returns, etc.) and determine if some orders should be avoided.

2. Office Supplies has much thinner margins than the other categories. Consider strategically increasing prices, negotiating lower costs, or promoting more profitable products over low margin ones.

3. The South region is underperforming the other three, with lower average profits and more money-losing orders. Focus efforts on improving profitability in South, perhaps by optimizing pricing and discounts, controlling costs, or adjusting product mix.

4. When giving discounts, be mindful that high discounts strongly correlate with lower profits. Aim to limit discounts to the 0-20% range unless strategically necessary. Consider A/B testing different discount levels to find the profit maximizing point.

5. Bookcases, Supplies, and Copiers are the least profitable product subcategories. Drill into these areas to diagnose issues and optimize the product lineup. Regularly prune underperforming products and replace them with popular, high-margin items.

This EDA has provided a strong foundation for the next phase of the project. Ideas for further analysis include:

• Examine sales and profit trends over time to understand seasonality and longer-term performance
• Calculate key performance metrics like profit margin, profit per order, cost per order to track over time
• Deep dive into the large unprofitable orders to determine the source of losses
• Investigate whether there are associations between region, product category, and customer segment that can inform a go-to-market strategy

With these insights, the data science team can begin building models to forecast demand, set optimal prices and discount offers, and prescribe profitable actions for the business to take. Leveraging data is the key to optimizing Superstore‘s performance.