Use Case #4 — Handle Missing Values Strategically

import pandas as pd
from pathlib import Path

base = Path.home() / "Documents" / "Pandas_Use_Cases"
input_file = base / "dates_cleaned_city_service_requests.csv"
output_file = base / "missing_values_treated_city_service_requests.csv"

df = pd.read_csv(input_file, parse_dates=["Created_Date", "Closed_Date"])

missing = pd.DataFrame({
    "Count": df.isna().sum(),
    "Percent": (df.isna().mean() * 100).round(2)
}).sort_values("Count", ascending=False)

print(missing.to_string())

categorical_defaults = {
    "State": "Unknown",
    "City": "Unknown",
    "Request_Channel": "Unknown",
    "Description": "No description provided"
}

for column, value in categorical_defaults.items():
    df[column] = df[column].fillna(value)

# Closed_Date may legitimately be missing for open requests.
open_mask = df["Status"].isin(["Open", "Pending", "In Progress"])
print("Open records without Closed_Date:",
      df.loc[open_mask, "Closed_Date"].isna().sum())

median_cost = df["Estimated_Cost"].median()
df["Estimated_Cost_Was_Missing"] = df["Estimated_Cost"].isna()
df["Estimated_Cost"] = df["Estimated_Cost"].fillna(median_cost)

print(f"Median cost used: {median_cost:,.2f}")

df.to_csv(output_file, index=False, encoding="utf-8-sig")
print(f"Saved: {output_file}")
print(f"Remaining missing cells: {int(df.isna().sum().sum()):,}")