Data Cleanup

Introduction to Data Cleanup

Raw data is rarely ready for analysis straight away. In the real world, datasets are messy: they contain missing values, duplicate records, formatting inconsistencies, and erroneous entries. Data cleanup — also called data cleaning or data wrangling — is the process of identifying and correcting these problems so that the data is accurate, consistent, and ready for analysis.

Data cleanup is often cited as the most time-consuming part of a data analyst's job, yet it is also among the most important. Poor data quality leads directly to poor analysis. This article covers the core techniques every data analyst needs to master.

Handling Missing Values

Missing data is one of the most common issues in real-world datasets. A value can be missing for many reasons: a user skipped a form field, a sensor failed to record, or data was lost during transfer. Missing values are typically represented as NaN (Not a Number) in pandas or NULL in SQL.

There are several strategies for handling missing values. First, you can drop rows or columns with missing data if the proportion is small and the missing data is not systematic. Second, you can impute missing values — replacing them with a calculated substitute such as the mean, median, or mode of the column. Third, for time-series data, forward fill or backward fill can propagate the nearest known value.

In Python with pandas:

import pandas as pd

df = pd.read_csv('data.csv')

# Drop rows where any value is missing
df_clean = df.dropna()

# Fill missing values in a column with the median
df['age'].fillna(df['age'].median(), inplace=True)

# Forward fill for time series
df['temperature'].fillna(method='ffill', inplace=True)

The right strategy depends on the context. Dropping data reduces sample size and can introduce bias. Imputation preserves sample size but introduces assumptions. Always understand why data is missing before deciding how to handle it.

Removing Duplicates

Duplicate records occur when the same entity appears more than once in a dataset. This can happen due to data entry errors, system bugs during data migration, or combining datasets from multiple sources. Duplicates inflate counts, distort statistics, and can skew model training.

In pandas, finding and removing duplicates is straightforward:

# Find duplicates
print(df.duplicated().sum())

# Remove duplicates, keeping the first occurrence
df = df.drop_duplicates()

# Remove duplicates based on specific columns
df = df.drop_duplicates(subset=['customer_id', 'order_date'])

In SQL, you can identify duplicates using GROUP BY and HAVING:

SELECT customer_id, COUNT(*) AS count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

Before removing duplicates, confirm that they are true duplicates and not legitimate repeated records. For example, a customer can place multiple orders — those are not duplicates. Always use meaningful unique identifiers to guide your deduplication logic.

Detecting and Handling Outliers

Outliers are data points that differ significantly from the rest of the dataset. They can result from genuine extreme values (a billionaire in a salary dataset), data entry errors (a height recorded as 1500 cm), or measurement errors. Outliers can have a large effect on statistical measures like the mean and standard deviation.

Common methods for detecting outliers include the Z-score method, the IQR (Interquartile Range) method, and visual inspection using box plots or scatter plots.

The IQR method:

Q1 = df['salary'].quantile(0.25)
Q3 = df['salary'].quantile(0.75)
IQR = Q3 - Q1

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

# Filter out outliers
df_filtered = df[(df['salary'] >= lower_bound) & (df['salary'] <= upper_bound)]

Once detected, you can handle outliers by removing them (if they are errors), capping them at a threshold (winsorization), or transforming the data (e.g., log transformation to reduce skew). The right approach depends on the analysis goals and the nature of the data.

Standardizing and Transforming Data

Raw data often comes in inconsistent formats that need to be standardized before analysis. Common transformation tasks include converting date formats (e.g., "01/04/2024" vs. "2024-04-01"), normalizing text case (making all strings lowercase or title case), parsing composite fields (splitting a "Full Name" column into "First Name" and "Last Name"), and converting units (miles to kilometers, Fahrenheit to Celsius).

In pandas, string standardization is easy: