Mathew K AnalyticsLesson 25 · Supply Chain Operations Analytics
Understanding Inventory Dynamics in Supply Chain Analytics
In this lesson, we will learn how to analyze and understand inventory behaviors using real-world supply chain datasets. Inventory management is crucial for…
- CourseSupply Chain Operations Analytics
- Lesson25 of 27
- Video28 min
- FormatJupyter notebook · 27 code cells
- Data1 dataset
What you'll learn
- Core Data Concepts: Transactions, Inventory, Demand, Orders
- Example: Calculating Simple Inventory Position
- Example: Filling In Missing Dates in Inventory Series
- Intermediate Example: Calculating Rolling Average Demand
- Intermediate Example: Aggregating Inventory by Line or Location
- Error Handling Example: Missing or Malformed Data
- Advanced Example: Join Inventory Movements to Supplier Performance
- Advanced Example: Predicting Stockouts with Lead Time Simulation
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
📓 Full notebook
Download .ipynbUnderstanding Inventory Dynamics in Supply Chain Analytics#
- In this lesson, we will learn how to analyze and understand inventory behaviors using real-world supply chain datasets.
- Inventory management is crucial for avoiding stockouts, reducing costs, and maintaining high service levels in supply chains.
- You will learn to transform raw transaction data into actionable insights about stock levels, demand variability, and replenishment needs.
- We will practice common analytics patterns using realistic retail and manufacturing data.
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
Core Data Concepts: Transactions, Inventory, Demand, Orders#
- Operational datasets in supply chain analytics include transactions, inventory snapshots, demand forecasts, and order histories.
- These datasets are often structured as transaction logs, time series, or event-based records.
- Beginners often misinterpret date columns, confuse order vs. delivery events, or neglect units of measure.
- Accurate inventory calculations depend on understanding how movements in and out of stock are recorded.
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df = pd.read_excel(url, sheet_name='Year 2010-2011')
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
print(df.shape)
print(df.head(3))
print(df.columns)
print(df[['StockCode', 'Description', 'Quantity', 'InvoiceDate']].head(5))
print('Unique stock keeping units (SKUs):', df['StockCode'].nunique())
daily_demand = df.groupby(['StockCode', df['InvoiceDate'].dt.date])['Quantity'].sum().reset_index()
print(daily_demand.head(5))
sku = daily_demand['StockCode'].iloc[0]
sku_demand = daily_demand[daily_demand['StockCode'] == sku]
sku_demand = sku_demand.set_index('InvoiceDate').sort_index()
print(sku_demand)
import matplotlib.pyplot as plt
plt.figure(figsize=(10,4))
sku_demand['Quantity'].plot(marker='o')
plt.title(f'Daily Demand for SKU {sku}')
plt.xlabel('Date')
plt.ylabel('Quantity Sold')
plt.tight_layout()
plt.show()
Example: Calculating Simple Inventory Position#
- The inventory position for a product is the on-hand stock after accounting for all inflows and outflows up to a given date.
- We can simulate inventory depletion by using daily sales as product outflows.
- Without replenishments, the inventory position only falls.
initial_stock = 300
sku_demand['inventory_position'] = initial_stock - sku_demand['Quantity'].cumsum()
print(sku_demand[['Quantity', 'inventory_position']])
plt.figure(figsize=(10,4))
sku_demand['inventory_position'].plot(marker='s', color='orange')
plt.title(f'Inventory Position for SKU {sku}')
plt.axhline(0, color='red', linestyle='--', label='Stockout Line')
plt.xlabel('Date')
plt.ylabel('Inventory Level')
plt.legend()
plt.tight_layout()
plt.show()
Example: Filling In Missing Dates in Inventory Series#
- Sales records may not appear on every day, so we must fill missing dates to produce a continuous inventory time series.
- Missing dates can lead to underestimating how long an item has been out of stock.
all_days = pd.date_range(start=sku_demand.index.min(), end=sku_demand.index.max())
sku_full = sku_demand.reindex(all_days, fill_value=0)
sku_full['inventory_position'] = initial_stock - sku_full['Quantity'].cumsum()
print(sku_full.head(10))
stockout_days = (sku_full['inventory_position'] <= 0).sum()
print('Number of stockout days:', stockout_days)
Intermediate Example: Calculating Rolling Average Demand#
- Smoother demand estimates help plan reorder points in volatile environments.
- Rolling averages filter out random spikes and dips.
sku_full['rolling_demand_7d'] = sku_full['Quantity'].rolling(window=7, min_periods=1).mean()
print(sku_full[['Quantity', 'rolling_demand_7d']].head(10))
plt.figure(figsize=(10,4))
sku_full['Quantity'].plot(marker='o', linestyle='-', alpha=0.5, label='Actual Demand')
sku_full['rolling_demand_7d'].plot(color='black', linewidth=2, label='7-Day Rolling Average')
plt.title('Actual vs. 7-Day Rolling Average Demand')
plt.xlabel('Date')
plt.ylabel('Units Sold')
plt.legend()
plt.tight_layout()
plt.show()
replenishment_point = int(sku_full['rolling_demand_7d'].mean() * 3)
print('Suggested reorder point (3x avg 7-day demand):', replenishment_point)
import openml
dataset = openml.datasets.get_dataset(43900)
X, y, _, _ = dataset.get_data(dataset_format='dataframe')
manuf_df = X.copy() if y is None else pd.concat([X, y], axis=1)
print(manuf_df.shape)
print(manuf_df.head(3))
Intermediate Example: Aggregating Inventory by Line or Location#
- Manufacturers often want to track inventory use by production line, workstation, or location.
- Grouping and aggregation are key analytics building blocks.
if 'line' in manuf_df.columns and 'quantity_used' in manuf_df.columns:
agg = manuf_df.groupby('line')['quantity_used'].sum()
print(agg)
else:
print('No appropriate columns found. Example only.')
safety_stock = sku_full['Quantity'].std() * 2
print('Estimated safety stock (2x daily std deviation):', round(safety_stock, 2))
Error Handling Example: Missing or Malformed Data#
- Inventory analyses can break if sales or supply logs have missing values, unexpected negative numbers, or date gaps.
- Always validate and clean operational data before analytics!
df.loc[42, 'Quantity'] = np.nan # Simulate a missing value
missing_qty = df['Quantity'].isna().sum()
print('Number of missing Quantity values:', missing_qty)
df['Quantity'] = df['Quantity'].fillna(0)
print('Missing quantities replaced with zero.')
bad_dates = df['InvoiceDate'].isna().sum()
print('Number of missing InvoiceDate values:', bad_dates)
bad_quantity = (df['Quantity'] < 0).sum()
print('Number of negative Quantity entries:', bad_quantity)
Advanced Example: Join Inventory Movements to Supplier Performance#
- For robust supply chain visibility, analysts often join stock records to supplier reliability metrics.
- This allows us to link inventory shortages to procurement and supplier risks directly.
supplier_dataset = openml.datasets.get_dataset(42125)
supplier_df, _, _, _ = supplier_dataset.get_data(dataset_format='dataframe')
print(supplier_df.shape)
print(supplier_df.head(3))
if 'StockCode' in supplier_df.columns:
enriched = pd.merge(sku_full.reset_index(), supplier_df, left_on='StockCode', right_on='StockCode', how='left')
print(enriched.head(2))
else:
print('No StockCode to join on in supplier data.')
Advanced Example: Predicting Stockouts with Lead Time Simulation#
- Predictive inventory analytics simulate when an item will run out, plus the earliest time replenishment is feasible.
- We can do a basic lead time simulation using expected delivery delays.
lead_time_days = 7
projected_runout_day = sku_full[sku_full['inventory_position'] <= 0].index.min()
today = sku_full.index[0]
if pd.notnull(projected_runout_day):
reorder_date = projected_runout_day - pd.Timedelta(days=lead_time_days)
print('Suggested reorder date (based on projected stockout and supplier lead time):', reorder_date)
else:
print('No projected stockout detected. Inventory is sufficient.' )
Best Practices: Inventory Aggregation, Time Series Grouping, KPI Calculation#
- Always aggregate inventory by business-relevant time intervals (day, week, or period).
- Grouping by SKU, category, or location exposes blind spots in stock coverage.
- Calculate KPIs such as stockout duration, replenishment delay, and service level to guide operational improvements.
sku_full['stockout'] = sku_full['inventory_position'] <= 0
service_level = 1 - sku_full['stockout'].mean()
print('Calculated service level: {:.2%}'.format(service_level))
file_path = 'daily_inventory_positions.csv'
sku_full[['inventory_position']].reset_index().rename(columns={'index':'Date'}).to_csv(file_path, index=False)
print(f'Inventory time series exported to {file_path}')
Mini Project: From Raw Retail Data to Final Inventory KPI#
- You now understand how to transform transaction logs into business-ready inventory insights.
- As an exercise, load any SKU, fill in missing days, simulate running out, and export the results.
- Use the patterns in this notebook to analyze your organizations products or a new dataset.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
25 minLesson 36
Understanding Click-Through Rate (CTR) in Social Media and Content Analytics
17 minLesson 12
Understanding Demographic Variables in Market Research Analytics with Python
35 minLesson 2
Understanding Financial Markets and Instruments: Key Concepts and Analytics
23 minLesson 12