Data Cleaning — COVID-19 Data Analysis¶
Setup and Audit¶
import pandas as pd
import numpy as np
df = pd.read_csv("owid-covid-data.csv", parse_dates=["date"])
print(f"Shape: {df.shape}")
print(f"Date range: {df['date'].min().date()} to {df['date'].max().date()}")
print(f"Locations: {df['location'].nunique()}")
print(f"\nMissing in key columns:")
for col in ["new_cases", "new_deaths", "total_cases_per_million", "people_vaccinated"]:
print(f" {col}: {df[col].isna().mean():.1%}")
Step 1 — Separate Countries from Aggregates¶
# Aggregate rows (World, continents, income groups) have NULL continent
aggregates = df[df["continent"].isna()]["location"].unique()
print(f"Aggregate locations: {aggregates}")
# Keep a 'world' frame for global trends
world = df[df["location"] == "World"].copy()
# Country-level frame for comparisons
countries = df[df["continent"].notna()].copy()
print(f"Countries: {countries['location'].nunique()}")
Step 2 — Handle Missing Values Appropriately¶
# new_cases/new_deaths: missing usually means "no report that day" → 0 is reasonable
countries["new_cases"] = countries["new_cases"].fillna(0)
countries["new_deaths"] = countries["new_deaths"].fillna(0)
# Cumulative columns: forward-fill (a missing total = same as yesterday)
countries = countries.sort_values(["location", "date"])
countries["total_cases"] = countries.groupby("location")["total_cases"].ffill().fillna(0)
countries["total_deaths"] = countries.groupby("location")["total_deaths"].ffill().fillna(0)
# Vaccination data: only exists from 2021 — leave NaN before rollout, ffill within rollout
countries["people_vaccinated"] = (
countries.groupby("location")["people_vaccinated"].ffill()
)
Don't fill vaccination data with 0 before it existed
Vaccines didn't exist until late 2020. Filling pre-2021 vaccination with 0 is technically correct (nobody was vaccinated), but forward-filling within the rollout period handles reporting gaps. Be deliberate about which.
Step 3 — Compute Per-Capita Metrics (If Not Present)¶
# Cases per million (in case you need to recompute)
countries["cases_per_million_calc"] = (
countries["total_cases"] / countries["population"] * 1_000_000
)
# Case Fatality Rate (deaths / cases)
countries["cfr"] = np.where(
countries["total_cases"] > 0,
countries["total_deaths"] / countries["total_cases"] * 100,
np.nan
)
Step 4 — Compute Rolling Averages (Smoothing)¶
# 7-day rolling average to remove weekend reporting noise
countries["new_cases_7d"] = (
countries.groupby("location")["new_cases"]
.transform(lambda x: x.rolling(7, min_periods=1).mean())
)
countries["new_deaths_7d"] = (
countries.groupby("location")["new_deaths"]
.transform(lambda x: x.rolling(7, min_periods=1).mean())
)
Step 5 — Remove Data Errors¶
# Negative new cases happen when countries correct over-reporting — flag them
negative_cases = countries[countries["new_cases"] < 0]
print(f"Days with negative new_cases (data corrections): {len(negative_cases)}")
# For trend analysis, clip negatives to 0 (or leave and rely on smoothing)
countries["new_cases_clean"] = countries["new_cases"].clip(lower=0)
Negative cases are real
Sometimes countries revise their figures down, producing a negative "new cases" day. This is a legitimate data correction, not an error to delete — but it can distort charts. Clipping to 0 or relying on the 7-day average handles it.
Step 6 — Add Date Features¶
countries["year"] = countries["date"].dt.year
countries["year_month"] = countries["date"].dt.to_period("M").astype(str)
countries["week"] = countries["date"].dt.isocalendar().week
Step 7 — Export¶
# Select a manageable subset of countries for detailed analysis
focus_countries = ["United States", "United Kingdom", "India", "Brazil",
"Germany", "South Korea", "Italy", "South Africa"]
focus = countries[countries["location"].isin(focus_countries)].copy()
countries.to_csv("covid_countries_clean.csv", index=False)
focus.to_csv("covid_focus_clean.csv", index=False)
world.to_csv("covid_world_clean.csv", index=False)
print("Exported cleaned files.")