Learn how to identify and correct data type mismatches and outliers in Pandas DataFrames to ensure accurate analysis.
What it is
Data cleaning involves transforming raw data into a structured format suitable for analysis. Two common issues are wrong formats (e.g., dates stored as strings) and wrong data (e.g., negative ages or extreme outliers). Correcting types ensures operations like sorting or arithmetic work correctly, while handling outliers prevents skewed statistical results. Related terms include type casting, data validation, and outlier detection.
Why it matters
- Accurate Calculations: You cannot perform date arithmetic on string objects.
- Memory Efficiency: Proper numeric types use less memory than object types.
- Reliable Visualizations: Outliers can distort chart scales, hiding trends.
- Model Performance: Machine learning models require consistent, clean input features.
Syntax or steps
- Inspect Types: Use
df.dtypesto check current column types. - Convert Formats: Use specific converters like
pd.to_datetime()or.astype(). - Detect Outliers: Calculate statistics (mean, std dev) or use IQR (Interquartile Range).
- Handle Outliers: Remove rows, cap values, or replace with median/NaN.
Example
import pandas as pd
import numpy as np
# Sample data with wrong format (date as string) and outlier (age 150)
data = {
"name": ["Alice", "Bob", "Charlie"],
"date_str": ["2023-01-01", "2023-02-15", "2023-03-20"],
"age": [25, 150, 30]
}
df = pd.DataFrame(data)
# 1. Fix Wrong Format: Convert string to datetime
df["date"] = pd.to_datetime(df["date_str"])
# 2. Identify Outliers using IQR method for 'age'
Q1 = df["age"].quantile(0.25)
Q3 = df["age"].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
# 3. Handle Wrong Data: Replace outlier age with NaN
df.loc[(df["age"] < lower_bound) | (df["age"] > upper_bound), "age"] = np.nan
print(df)This code first converts the date_str column into a proper datetime object. Then, it calculates the Interquartile Range (IQR) for ages. Any age outside the range defined by $1.5 \times IQR$ from the quartiles is considered an outlier and replaced with NaN.
Common mistakes
- Ignoring Errors: Using
errors='coerce'into_datetimesilently turns bad dates into NaT; always check how many were converted. - Hardcoding Thresholds: Assuming all datasets have the same valid ranges without checking distribution.
- Deleting Too Much: Removing entire rows for single-column outliers may lose valuable data; consider imputation instead.
- Forgetting Indexes: After filtering or replacing, indexes may become non-contiguous; use
reset_index(drop=True)if needed.
When to use it
| Scenario | Action | Reason |
|---|---|---|
| Date/Time Analysis | pd.to_datetime() | Enables time-based sorting and grouping. |
| Numeric Calculation | .astype(float) | Strings cannot be summed or averaged. |
| Extreme Values | IQR or Z-Score | Statistically identifies anomalies vs. normal variation. |
| Missing Data | Imputation/Drop | Prevents errors in algorithms that don't handle NaN. |
Practice
Guided Exercise: Create a DataFrame with a column "price" containing strings like "$100". Convert them to floats by removing the dollar sign.
Challenge: Given a list of temperatures [20, 22, 21, 100, 23], use the mean and standard deviation to identify the outlier. Hint: Calculate $z = \frac{x - \mu}{\sigma}$.
Quick check
Question: Why might you choose to replace an outlier with the median rather than deleting the row?
Answer: Deleting the row removes all other valid data in that record. Replacing with the median preserves the rest of the information while mitigating the skew caused by the extreme value.
Summary
Correcting data types and managing outliers are foundational steps in data preprocessing. Always validate your transformations to ensure they align with domain knowledge and statistical expectations.