Mathew K AnalyticsLesson 11 · Supply Chain Operations Analytics
Handling Missing and Dirty Operations Data
Learn how to detect, diagnose, and fix missing or dirty values in real operations datasets. Missing and dirty data is a common source of mistakes in supply…
- CourseSupply Chain Operations Analytics
- Lesson11 of 27
- Video23 min
- FormatJupyter notebook · 24 code cells
What you'll learn
Data
No separate download needed — the notebook creates or downloads everything it uses.
📓 Full notebook
Download .ipynbHandling Missing and Dirty Operations Data#
- Learn how to detect, diagnose, and fix missing or dirty values in real operations datasets.
- Missing and dirty data is a common source of mistakes in supply chain analytics.
- Clean data leads to better forecasting, ordering, fulfillment, and strategic decisions.
- This lesson teaches you how to identify real-world data problems and apply practical cleaning steps.
- No fake data - we will use live, public supply chain datasets to practice real analytics skills.
import pandas as pd
import openml
import warnings
warnings.filterwarnings('ignore')
Understanding Operations Data#
- Operations data includes records of sales, deliveries, inventory, and supplier performance.
- Data is structured as tables with columns such as order IDs, timestamps, quantities, and customer details.
- Beginners often do not check for missing values before running analyses.
- Dirty data can mean wrong date formats, bad numbers, or missing supplier names.
- Missing or dirty data can ruin demand forecasts or supplier scorecards.
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
retail_df = pd.read_excel(url, sheet_name='Year 2010-2011')
retail_df['InvoiceDate'] = pd.to_datetime(retail_df['InvoiceDate'])
print(retail_df.shape)
print(retail_df.head(3))
print(retail_df.isnull().sum())
missing_ratio = retail_df.isnull().mean()
print(missing_ratio.sort_values(ascending=False))
url_orders = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
orders_df = pd.read_csv(url_orders)
orders_df['order_purchase_timestamp'] = pd.to_datetime(orders_df['order_purchase_timestamp'])
orders_df['order_delivered_customer_date'] = pd.to_datetime(orders_df['order_delivered_customer_date'])
print(orders_df.shape)
print(orders_df.head(3))
print(orders_df.isnull().sum())
supplier_data = openml.datasets.get_dataset(42125)
supplier_df, _, _, _ = supplier_data.get_data(dataset_format='dataframe')
print(supplier_df.shape)
print(supplier_df.head(3))
print(supplier_df.isnull().sum())
# Drop rows where essential information is missing
cleaned_orders = orders_df.dropna(subset=['order_purchase_timestamp', 'order_delivered_customer_date'])
print(f'Removed {len(orders_df) - len(cleaned_orders)} incomplete rows.')
# Fill missing numerical fields with the median
retail_df['Quantity'] = retail_df['Quantity'].fillna(retail_df['Quantity'].median())
print('Missing Quantity filled with median:', retail_df['Quantity'].isnull().sum() == 0)
# Fill missing customer IDs as 'Unknown' in retail data
retail_df['Customer ID'] = retail_df['Customer ID'].fillna('Unknown')
print('All Customer IDs accounted for:', retail_df['Customer ID'].isnull().sum() == 0)
# Intermediate: Visualize missing data patterns
import matplotlib.pyplot as plt
import seaborn as sns
sns.heatmap(retail_df.isnull(), cbar=False)
plt.title('Missing Data Heatmap (Retail)')
plt.show()
# Find how many unique bad entries we have for StockCode
bad_stockcodes = retail_df['StockCode'].isnull().sum() + (retail_df['StockCode'] == '').sum()
print('Bad StockCode entries:', bad_stockcodes)
# Intermediate: Find outliers or dirty Quantity values
dirty_quantity = retail_df[(retail_df['Quantity'] <= 0) | (retail_df['Quantity'] > 10000)]
print(f'{len(dirty_quantity)} suspicious quantity values found.')
print(dirty_quantity[['Invoice', 'StockCode', 'Quantity']].head())
# Intermediate: Validate order and delivery timestamps
bad_time = orders_df[orders_df['order_purchase_timestamp'] > orders_df['order_delivered_customer_date']]
print(f'{len(bad_time)} orders have delivery dates before purchase dates!')
print(bad_time[['order_id', 'order_purchase_timestamp', 'order_delivered_customer_date']])
# Advanced: Aggregate missing by country in retail data
country_missing = retail_df.groupby('Country')['Customer ID'].apply(lambda x: (x == 'Unknown').mean())
print(country_missing.sort_values(ascending=False).head())
# Advanced: Merge orders and products, watch for join errors
url_order_items = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_order_items_dataset.csv'
items_df = pd.read_csv(url_order_items)
merged_df = pd.merge(orders_df, items_df, on='order_id', how='left', indicator=True)
bad_joins = merged_df[merged_df['_merge'] != 'both']
print(f'{len(bad_joins)} order rows could not be matched to items!')
print(bad_joins.head())
# Debugging: Investigate why joins failed
missing_items_orders = bad_joins['order_id'].unique()
print('Example unmatched order IDs:', missing_items_orders[:5])
# Find if any key data is missing in these orders
print(orders_df[orders_df['order_id'].isin(missing_items_orders)].head())
# Error handling: What if we try to use missing data?
try:
total_days = (orders_df['order_delivered_customer_date'] - orders_df['order_purchase_timestamp']).dt.days
print('Min delivery time:', total_days.min())
except Exception as e:
print('Error encountered:', e)
# Fill missing or negative delivery dates with a default lag
orders_df['order_delivered_customer_date'] = orders_df['order_delivered_customer_date'].fillna(orders_df['order_purchase_timestamp'] + pd.Timedelta(days=5))
orders_df.loc[orders_df['order_delivered_customer_date'] < orders_df['order_purchase_timestamp'], 'order_delivered_customer_date'] = orders_df['order_purchase_timestamp'] + pd.Timedelta(days=2)
print('Missing and backwards delivery dates now handled.')
# Best practice: Calculate fulfillment lead time and flag late orders
orders_df['lead_time_days'] = (orders_df['order_delivered_customer_date'] - orders_df['order_purchase_timestamp']).dt.days
late_threshold = 7
orders_df['is_late'] = orders_df['lead_time_days'] > late_threshold
late_orders = orders_df['is_late'].mean()
print(f'{late_orders*100:.1f}% of orders are late (>{late_threshold} days).')
# Aggregate: Calculate total orders per country (retail example)
country_counts = retail_df.groupby('Country')['Invoice'].count().reset_index()
country_counts = country_counts.sort_values('Invoice', ascending=False)
print(country_counts.head())
# Time series group: Order volume by month
retail_df['InvoiceMonth'] = retail_df['InvoiceDate'].dt.to_period('M')
monthly_orders = retail_df.groupby('InvoiceMonth')['Invoice'].count()
print(monthly_orders.tail())
# END-TO-END: Identify all orders for a specific country and their late delivery rate
country = 'United Kingdom'
uk_orders = pd.merge(orders_df, retail_df[retail_df['Country'] == country], left_on='customer_id', right_on='Customer ID', how='inner')
late_uk = uk_orders['is_late'].mean() if 'is_late' in uk_orders else 0
print(f'UK orders late rate: {late_uk*100:.1f}%')
Congratulations! You can now handle missing and dirty data for real operational analytics.#
- Try the cleaning techniques here on your own supply chain or operations data.
- Ready for more? Search YouTube for "operations analytics missing data" and expand your toolkit.
- Clean data is the foundation of reliable supply chain decisions.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
14 minLesson 18
Handling Missing Data in Time Series: Proven Techniques for Accurate Analysis
12 minLesson 16
Handling Missing Values and Data Imputation Techniques in Python for Machine Learning
17 minLesson 6
Data Cleaning Fundamentals: Managing Missing Values, Noise, and Outliers in Datasets
24 minLesson 11