Common Data Processing Practices

this post will describe common practices on processing data from raw to ready-to-use dataframe

Author

Sulthan A. Karimov

Published

July 2, 2024

Common Data Processing practices

Introduction

Real-world datasets are rarely clean. They arrive with missing values, duplicate records, inconsistent formats, outliers, and invalid entries. Before any meaningful analysis or modeling can take place, these issues must be identified and resolved. Data cleaning is often the most time-consuming step in a data science workflow — estimates suggest it can consume 60–80% of a project’s time — yet it is also the most critical. A model trained on dirty data will produce unreliable results, no matter how sophisticated the algorithm.

This tutorial walks through common data cleaning practices using Python, pandas, and NumPy. Each section introduces a problem, demonstrates how to detect it, and shows how to fix it using realistic sample data.

Setup

import pandas as pd
import numpy as np

# Seed for reproducibility
np.random.seed(42)

Handling Missing Values

Missing values are one of the most frequent issues in raw data. They can arise from sensor failures, incomplete surveys, or human error during data entry. Pandas provides several tools for detecting and dealing with them.

# Create a sample DataFrame with missing values
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'],
    'age': [25, np.nan, 35, 28, np.nan],
    'salary': [50000, 62000, np.nan, 48000, 55000],
    'department': ['Engineering', np.nan, 'Marketing', 'Engineering', 'Sales']
})

df

	name	age	salary	department
0	Alice	25.0	50000.0	Engineering
1	Bob	NaN	62000.0	NaN
2	Charlie	35.0	NaN	Marketing
3	Diana	28.0	48000.0	Engineering
4	Eve	NaN	55000.0	Sales

Detecting Missing Values

# Check which cells are null
print("Null mask:\n", df.isna())

# Count missing values per column
print("\nMissing per column:\n", df.isna().sum())

Null mask:
     name    age  salary  department
0  False  False   False       False
1  False   True   False        True
2  False  False    True       False
3  False  False   False       False
4  False   True   False       False

Missing per column:
 name          0
age           2
salary        1
department    1
dtype: int64

Filling Missing Values

# Fill numeric columns with the column median
df['age'] = df['age'].fillna(df['age'].median())
df['salary'] = df['salary'].fillna(df['salary'].median())

# Fill categorical columns with the most frequent value (mode)
df['department'] = df['department'].fillna(df['department'].mode()[0])

df

	name	age	salary	department
0	Alice	25.0	50000.0	Engineering
1	Bob	28.0	62000.0	Engineering
2	Charlie	35.0	52500.0	Marketing
3	Diana	28.0	48000.0	Engineering
4	Eve	28.0	55000.0	Sales

Dropping Rows or Columns

# Alternative approach: drop rows with any null value
df_dropped_rows = df.dropna()

# Drop columns where more than 50% of values are missing
df_dropped_cols = df.dropna(thresh=len(df) * 0.5, axis=1)

Removing Duplicates

Duplicate rows can inflate counts, bias aggregations, and mislead statistical tests. Identifying and removing them is straightforward with pandas.

# Create a DataFrame with intentional duplicates
df_dup = pd.DataFrame({
    'customer_id': [101, 102, 103, 101, 104, 102],
    'product': ['Laptop', 'Mouse', 'Keyboard', 'Laptop', 'Monitor', 'Mouse'],
    'price': [1200, 25, 80, 1200, 300, 25],
    'date': ['2024-01-15', '2024-01-16', '2024-01-16',
             '2024-01-15', '2024-01-17', '2024-01-16']
})

df_dup

	customer_id	product	price	date
0	101	Laptop	1200	2024-01-15
1	102	Mouse	25	2024-01-16
2	103	Keyboard	80	2024-01-16
3	101	Laptop	1200	2024-01-15
4	104	Monitor	300	2024-01-17
5	102	Mouse	25	2024-01-16

Detecting Duplicates

# Check which rows are duplicates (keep='first' marks subsequent occurrences)
df_dup.duplicated()

0    False
1    False
2    False
3     True
4    False
5     True
dtype: bool

# Show only the duplicate rows
df_dup[df_dup.duplicated(keep=False)]

	customer_id	product	price	date
0	101	Laptop	1200	2024-01-15
1	102	Mouse	25	2024-01-16
3	101	Laptop	1200	2024-01-15
5	102	Mouse	25	2024-01-16

Removing Duplicates

# Drop duplicate rows, keeping the first occurrence
df_clean = df_dup.drop_duplicates()

df_clean

	customer_id	product	price	date
0	101	Laptop	1200	2024-01-15
1	102	Mouse	25	2024-01-16
2	103	Keyboard	80	2024-01-16
4	104	Monitor	300	2024-01-17

# Remove duplicates based on specific columns only
df_dup.drop_duplicates(subset=['customer_id', 'product'])

	customer_id	product	price	date
0	101	Laptop	1200	2024-01-15
1	102	Mouse	25	2024-01-16
2	103	Keyboard	80	2024-01-16
4	104	Monitor	300	2024-01-17

Type Conversion

Data often arrives with incorrect types — numeric columns stored as strings, dates stored as text, or categorical values stored as integers. Converting types correctly is essential for downstream operations.

# Create a DataFrame with mixed types
df_types = pd.DataFrame({
    'id': ['001', '002', '003', '004'],
    'price': ['12.50', '8.00', 'invalid', '15.30'],
    'quantity': [3, 5, 2, 4],
    'date_string': ['2024-01-01', '2024-02-15', '2024-03-10', '2024-04-22'],
    'category_code': [1, 2, 1, 3]
})

df_types.dtypes

id                 str
price              str
quantity         int64
date_string        str
category_code    int64
dtype: object

Converting with `astype()`

# Convert 'id' from string to integer
df_types['id'] = df_types['id'].astype(int)

# Convert 'category_code' to categorical dtype
df_types['category_code'] = df_types['category_code'].astype('category')

df_types.dtypes

id                  int64
price                 str
quantity            int64
date_string           str
category_code    category
dtype: object

Safe Numeric Conversion with `to_numeric()`

# to_numeric() handles invalid values: coerce turns 'invalid' into NaN
df_types['price'] = pd.to_numeric(df_types['price'], errors='coerce')

# Fill the resulting NaN with the median
df_types['price'] = df_types['price'].fillna(df_types['price'].median())

df_types

	id	price	quantity	date_string	category_code
0	1	12.5	3	2024-01-01	1
1	2	8.0	5	2024-02-15	2
2	3	12.5	2	2024-03-10	1
3	4	15.3	4	2024-04-22	3

Converting to Datetime

# Parse string dates into proper datetime objects
df_types['purchase_date'] = pd.to_datetime(df_types['date_string'])

# Extract useful components
df_types['year'] = df_types['purchase_date'].dt.year
df_types['month'] = df_types['purchase_date'].dt.month
df_types['day_of_week'] = df_types['purchase_date'].dt.day_name()

df_types[['date_string', 'purchase_date', 'year', 'month', 'day_of_week']]

	date_string	purchase_date	year	month	day_of_week
0	2024-01-01	2024-01-01	2024	1	Monday
1	2024-02-15	2024-02-15	2024	2	Thursday
2	2024-03-10	2024-03-10	2024	3	Sunday
3	2024-04-22	2024-04-22	2024	4	Monday

Outlier Detection

Outliers are extreme values that deviate significantly from the rest of the data. They can result from measurement errors, data entry mistakes, or genuine rare events. Two common methods for detecting outliers are the IQR method and the Z-score method.

# Generate sample data with outliers
np.random.seed(42)
data = np.random.normal(loc=50, scale=10, size=100).tolist()
data.extend([5, 8, 95, 98])  # Inject outliers
df_out = pd.DataFrame({'value': data})

Interquartile Range (IQR) Method

The IQR method identifies values that fall outside 1.5 times the interquartile range below Q1 or above Q3. It is robust to skewed distributions because it relies on percentiles rather than the mean.

Q1 = df_out['value'].quantile(0.25)
Q3 = df_out['value'].quantile(0.75)
IQR = Q3 - Q1

lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Find outliers
outliers_iqr = df_out[(df_out['value'] < lower_bound) | (df_out['value'] > upper_bound)]
print(f"Lower bound: {lower_bound:.2f}, Upper bound: {upper_bound:.2f}")
print(f"Outliers detected: {len(outliers_iqr)}")
outliers_iqr.head()

Lower bound: 27.17, Upper bound: 71.71
Outliers detected: 5

	value
74	23.802549
100	5.000000
101	8.000000
102	95.000000
103	98.000000

Z-Score Method

The Z-score method measures how many standard deviations a value is from the mean. Values beyond a threshold (commonly 3) are flagged as outliers. This method assumes the data is approximately normally distributed.

from scipy import stats

z_scores = np.abs(stats.zscore(df_out['value']))
df_out['z_score'] = z_scores

# Flag values with |z-score| > 3
outliers_z = df_out[df_out['z_score'] > 3]
print(f"Outliers detected: {len(outliers_z)}")
outliers_z.head()

Outliers detected: 4

	value	z_score
100	5.0	3.519431
101	8.0	3.279792
102	95.0	3.669737
103	98.0	3.909376

Handling Outliers

# Option 1: Remove outliers (IQR method)
df_no_outliers = df_out[(df_out['value'] >= lower_bound) & (df_out['value'] <= upper_bound)]

# Option 2: Cap outliers at the bounds (winsorization)
df_out['value_capped'] = df_out['value'].clip(lower=lower_bound, upper=upper_bound)

print(f"Original rows: {len(df_out)}, After removal: {len(df_no_outliers)}")

Original rows: 104, After removal: 99

Data Validation

After cleaning, it is prudent to verify that the data meets expected constraints. Validation catches errors that may have been missed and ensures the dataset is ready for analysis.

# Create a validated dataset
df_val = pd.DataFrame({
    'age': [22, 34, 28, 120, 45, 19, -5],
    'email': ['[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]'],
    'score': [85, 92, 78, 95, 88, 76, 90],
    'status': ['active', 'inactive', 'active', 'pending', 'active', 'inactive', 'unknown']
})

df_val

	age	email	score	status
0	22	[email protected]	85	active
1	34	[email protected]	92	inactive
2	28	[email protected]	78	active
3	120	[email protected]	95	pending
4	45	[email protected]	88	active
5	19	[email protected]	76	inactive
6	-5	[email protected]	90	unknown

Range Checks

# Validate age is within a reasonable range (0 to 120)
invalid_age = df_val[(df_val['age'] < 0) | (df_val['age'] > 120)]
print(f"Invalid age entries: {len(invalid_age)}")
invalid_age

Invalid age entries: 1

	age	email	score	status
6	-5	[email protected]	90	unknown

# Fix: clip ages to valid range
df_val['age'] = df_val['age'].clip(lower=0, upper=120)

Unique and Domain Constraints

# Check for duplicate emails
duplicate_emails = df_val[df_val['email'].duplicated(keep=False)]
print(f"Duplicate emails: {len(duplicate_emails)}")

# Ensure status values belong to an allowed set
valid_statuses = {'active', 'inactive', 'pending'}
invalid_status = df_val[~df_val['status'].isin(valid_statuses)]
print(f"Invalid status entries: {len(invalid_status)}")
invalid_status

Duplicate emails: 4
Invalid status entries: 1

	age	email	score	status
6	0	[email protected]	90	unknown

# Fix: drop exact duplicates, replace invalid statuses with a default
df_val = df_val.drop_duplicates(subset=['email'])
df_val['status'] = df_val['status'].apply(
    lambda x: x if x in valid_statuses else 'inactive'
)

print("Cleaned dataset:")
df_val

Cleaned dataset:

	age	email	score	status
0	22	[email protected]	85	active
1	34	[email protected]	92	inactive
3	120	[email protected]	95	pending
5	19	[email protected]	76	inactive
6	0	[email protected]	90	inactive

Conclusion

Data cleaning is an iterative process, not a one-time task. The practices covered in this tutorial — handling missing values, removing duplicates, converting types, detecting outliers, and validating data — form the foundation of any robust data pipeline. Some guiding principles to keep in mind:

Inspect first, clean second. Always profile your data with .info(), .describe(), and .isna().sum() before modifying anything.
Document your cleaning steps. Whether in code comments or a pipeline log, reproducibility matters.
Know your domain. A value that looks like an outlier in one context may be perfectly valid in another — consult domain experts before discarding data.
Validate after cleaning. Re-run your checks after transformations to confirm the fixes were applied correctly.

Clean data leads to reliable insights. Invest the time upfront, and your analyses will be stronger for it.

Common Data Processing practices

Introduction

Setup

Handling Missing Values

Detecting Missing Values

Filling Missing Values

Dropping Rows or Columns

Removing Duplicates

Detecting Duplicates

Removing Duplicates

Type Conversion

Converting with astype()

Safe Numeric Conversion with to_numeric()

Converting to Datetime

Outlier Detection

Interquartile Range (IQR) Method

Z-Score Method

Handling Outliers

Data Validation

Range Checks

Unique and Domain Constraints

Conclusion

Converting with `astype()`

Safe Numeric Conversion with `to_numeric()`