Mathew K AnalyticsLesson 20 · Supply Chain Operations Analytics
Introduction to Demand Forecasting Data
In this lesson, we explore how real companies use supply chain data to forecast product demand. Demand forecasting is critical in retail, manufacturing, and…
- CourseSupply Chain Operations Analytics
- Lesson20 of 27
- Video26 min
- FormatJupyter notebook · 23 code cells
What you'll learn
- Understanding Supply Chain Demand Data
- Columns in Retail Demand Dataset
- Example 1: Counting Total Transactions per Country (Retail Data)
- Example 2: Summing Product Demand over Time
- Example 3: Visualizing Daily Sales (Beginner using Retail Dataset)
- Example 4: Checking Missing Customer IDs (Intermediate)
- Example 5: Filtering Out Canceled Orders (Intermediate)
- Example 6: Combining Product and Date for Time Series (Intermediate)
Data
No separate download needed — the notebook creates or downloads everything it uses.
📓 Full notebook
Download .ipynbIntroduction to Demand Forecasting Data#
- In this lesson, we explore how real companies use supply chain data to forecast product demand.
- Demand forecasting is critical in retail, manufacturing, and logistics for planning inventory and meeting customer needs.
- You will understand different types of data used in demand forecasting, practice loading real-world datasets, and learn to avoid common pitfalls.
- By the end, you will be able to analyze, debug, and generate operational demand forecasts with Python.
import pandas as pd
import openml
import warnings
warnings.filterwarnings('ignore')
Understanding Supply Chain Demand Data#
- Demand data shows how much of each product customers want, and when.
- Sources include retail transactions, manufacturing orders, supplier shipments, and more.
- Columns often represent dates, quantities, locations, and product information.
- Common beginner mistakes include misreading date formats and confusing order status.
- High-quality demand data is essential for accurate forecasting and inventory planning.
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df_retail = pd.read_excel(url, sheet_name='Year 2010-2011')
df_retail['InvoiceDate'] = pd.to_datetime(df_retail['InvoiceDate'])
print(df_retail.shape)
print(df_retail.head(3))
Columns in Retail Demand Dataset#
- Invoice: Transaction identifier (string)
- StockCode: Product or item identifier (string)
- Description: Human-readable product name (string)
- Quantity: Number of units sold (integer)
- InvoiceDate: Date and time of transaction (datetime)
- Price: Price per item (float)
- Customer ID: Anonymous customer code (integer or null)
- Country: Customer country (string)
dataset = openml.datasets.get_dataset(4549)
X, y, _, _ = dataset.get_data(dataset_format='dataframe')
df_sales_forecast = pd.concat([X, y], axis=1)
print(df_sales_forecast.shape)
print(df_sales_forecast.head(3))
Example 1: Counting Total Transactions per Country (Retail Data)#
- We can answer basic questions like: Which country has the most transactions?
- This helps businesses identify their strongest geographic markets.
- Beginners often forget to handle missing country codes.
country_counts = df_retail['Country'].value_counts()
print(country_counts.head(5))
us_sales = df_retail[df_retail['Country'] == 'United States']
print(us_sales[['Invoice', 'StockCode', 'Quantity']].head(3))
Example 2: Summing Product Demand over Time#
- We often need to sum up how many units of a product sell over time.
- This helps measure trends and spot seasonality in demand.
- Beginners sometimes aggregate on the wrong field, such as mixing up invoice lines vs. product totals.
prod_demand = df_retail.groupby('StockCode')['Quantity'].sum().sort_values(ascending=False)
print(prod_demand.head(5))
Example 3: Visualizing Daily Sales (Beginner using Retail Dataset)#
- A simple line plot of daily total sales helps visualize demand over time.
- Consistent patterns often indicate regular customer behavior.
- Beginners may forget to resample data correctly by date.
import matplotlib.pyplot as plt
daily_sales = df_retail.set_index('InvoiceDate').resample('D')['Quantity'].sum()
daily_sales.plot(figsize=(10,4), title='Total Daily Units Sold')
plt.xlabel('Date')
plt.ylabel('Units Sold')
plt.tight_layout()
plt.show()
Example 4: Checking Missing Customer IDs (Intermediate)#
- Customer IDs may be missing, especially for guest purchases or old data.
- Downstream analytics like customer segmentation depend on clean IDs.
- Beginners sometimes ignore missing or null customer values.
missing_customers = df_retail['Customer ID'].isnull().sum()
print(f'Missing customer IDs: {missing_customers}')
Example 5: Filtering Out Canceled Orders (Intermediate)#
- Some order numbers in demand datasets may actually be canceled transactions.
- It is important not to count canceled or returned items in true demand calculations.
- Negative quantities often indicate returns or cancellations.
df_clean = df_retail[df_retail['Quantity'] > 0]
print(f'Removed {len(df_retail) - len(df_clean)} canceled or returned records.')
Example 6: Combining Product and Date for Time Series (Intermediate)#
- For forecasting, we often want daily demand per product.
- Grouping by both product and date creates a time series for each item.
- Beginners may accidentally group by only one variable.
df_clean['Day'] = df_clean['InvoiceDate'].dt.date
product_daily = df_clean.groupby(['StockCode', 'Day'])['Quantity'].sum().reset_index()
print(product_daily.head(3))
Example 7: Calculating Rolling Averages (Intermediate)#
- Rolling averages smooth out noisy daily data and reveal actual demand trends.
- This is commonly used for forecasting and planning.
- Beginners sometimes forget to sort by date first.
product = product_daily[product_daily['StockCode'] == product_daily['StockCode'].iloc[0]].sort_values('Day')
product['Rolling7'] = product['Quantity'].rolling(7, min_periods=1).mean()
print(product[['Day', 'Quantity', 'Rolling7']].head(10))
dataset_olist = openml.datasets.get_dataset(43900)
X_olist, y_olist, _, _ = dataset_olist.get_data(dataset_format='dataframe')
df_olist = X_olist.copy() if y_olist is None else pd.concat([X_olist, y_olist], axis=1)
print(df_olist.shape)
print(df_olist.head(3))
Example 8: Counting Completed Orders by Status (Advanced)#
- Not all records in order data represent completed orders.
- Businesses often track how many orders reach different fulfillment stages.
- Beginners may accidentally double-count by not filtering duplicates or only final states.
if 'order_status' in df_sales_forecast.columns:
order_counts = df_sales_forecast['order_status'].value_counts()
print(order_counts)
else:
print('order_status column missing in this dataset.')
delays = None
if 'order_purchase_timestamp' in df_sales_forecast.columns and 'order_delivered_customer_date' in df_sales_forecast.columns:
delays = (pd.to_datetime(df_sales_forecast['order_delivered_customer_date']) - pd.to_datetime(df_sales_forecast['order_purchase_timestamp'])).dt.days
sample_delays = delays.head(10)
print(sample_delays)
else:
print('Needed columns for delay calculation are not in this dataset.')
Example 9: Error Handling Missing Dates#
- Real datasets often have missing or badly formatted date fields.
- Business analysis fails if date fields are not correct.
- Always check for missing or invalid dates before further processing.
if 'InvoiceDate' in df_retail.columns:
missing_dates = df_retail['InvoiceDate'].isnull().sum()
print(f'Missing InvoiceDate values: {missing_dates}')
else:
print('No InvoiceDate column found.')
try:
bad_date = pd.to_datetime(['not_a_date', '2017-08-07', None], errors='raise')
except Exception as e:
print(f'Error converting date: {e}')
Example 10: Error Handling Incorrect Joins#
- Merging two operational datasets on the wrong key or with mismatched data types can break analysis.
- Always check data types and keys before joining sales or supplier data.
- Beginners sometimes join on name fields instead of unique identifiers.
left = pd.DataFrame({'order_id': [1, 2, 3], 'demand': [23, 45, 31]})
right = pd.DataFrame({'Order_ID': [1, 2, 4], 'supplier': ['A', 'B', 'C']})
try:
merged = pd.merge(left, right, left_on='order_id', right_on='Order_ID')
print(merged)
except Exception as e:
print(f'Join error: {e}')
Example 11: Error Handling Interpreting Quantities Incorrectly#
- Demand quantities may represent unit sales, weight, or even box counts depending on the dataset.
- Always read metadata and column descriptions for correct business meaning.
- It is easy to interpret the Quantity column incorrectly in supply chain analytics.
# Reading, interpreting, and reporting the demand statistic
unit_sum = df_retail['Quantity'].sum()
print(f'Total units (not boxes or cases) sold: {unit_sum}')
Example 12: Best Practice Aggregating and Grouping for Weekly KPIs#
- Weekly business summaries help uncover underlying demand patterns.
- Aggregating by week helps compare against sales targets or detect anomalies.
- Use business-standard periods, not arbitrary time windows.
weekly_sales = df_clean.set_index('InvoiceDate').resample('W')['Quantity'].sum()
print(weekly_sales.tail(10))
Example 13: Best Practice Calculating Fill Rate (Advanced Supply Chain KPI)#
- Fill rate measures the share of customer demand actually fulfilled on time.
- Calculating fill rate requires item-by-item delivered versus ordered quantities.
- High fill rate indicates strong supply chain performance.
# This is a template calculation for demonstration purposes
filled_orders = df_clean['Quantity'].sum()
total_orders = df_retail[df_retail['Quantity'] > 0]['Quantity'].sum()
fill_rate = filled_orders / total_orders if total_orders != 0 else None
print(f'Estimated fill rate: {fill_rate:.2%}' if fill_rate is not None else 'No data to calculate fill rate.')
Example 14: Grouping by Month to Reveal Seasonality#
- Monthly grouping helps visualize seasonality and longer business cycles.
- Seasonality is important for planning promotional campaigns and stock.
- Beginners sometimes use non-uniform periods or forget to localize dates.
monthly_sales = df_clean.set_index('InvoiceDate').resample('M')['Quantity'].sum()
print(monthly_sales)
Example 15: End-to-end Tiny Forecasting Scenario#
- Given last three months sales, estimate next month's likely demand.
- We use real data, not models yet this 'naive forecast' is a business baseline.
- This is a building block for more advanced forecasting in operations analytics.
recent_months = monthly_sales.tail(3)
naive_forecast = int(recent_months.mean()) if not recent_months.empty else None
next_month = monthly_sales.index[-1] + pd.offsets.MonthEnd() if len(monthly_sales) > 0 else None
print(f'Average of last 3 months: {list(recent_months.values)}')
if naive_forecast and next_month:
print(f'Naive forecast for {next_month.date()}: {naive_forecast} units')
else:
print('Not enough historical data for forecast.')
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
29 minLesson 5
Python Data Analytics #05: Sales Forecasting for Retail Demand in Python
12 minLesson 40
Comprehensive Time Series Forecasting Project: From Data Preparation to Deployment
13 minLesson 10
Introduction to Data Analysis with the Titanic Dataset Using Pandas
21 minLesson 9