Use Case #1 — Load and Inspect Messy City Service Data

python --version
python -c "import pandas as pd; print(pd.__version__)"

import pandas as pd
from pathlib import Path

file_path = (
    Path.home()
    / "Documents"
    / "Pandas_Use_Cases"
    / "messy_city_service_requests.csv"
)

df = pd.read_csv(file_path)

print("\n" + "=" * 70)
print("DATASET LOADED SUCCESSFULLY")
print("=" * 70)

print(f"File: {file_path}")
print(f"Rows: {df.shape[0]:,}")
print(f"Columns: {df.shape[1]}")

print("\n" + "=" * 70)
print("COLUMN NAMES")
print("=" * 70)

for position, column in enumerate(df.columns, start=1):
    print(f"{position:02}. {column}")

print("\n" + "=" * 70)
print("FIRST 10 ROWS")
print("=" * 70)

print(df.head(10).to_string(index=False))

print("\n" + "=" * 70)
print("DATA TYPES")
print("=" * 70)

print(df.dtypes)

print("\n" + "=" * 70)
print("MISSING VALUES BY COLUMN")
print("=" * 70)

missing_summary = pd.DataFrame({
    "Missing_Count": df.isna().sum(),
    "Missing_Percent": (df.isna().mean() * 100).round(2)
})

missing_summary = missing_summary.sort_values(
    by="Missing_Count",
    ascending=False
)

print(missing_summary.to_string())

print("\n" + "=" * 70)
print("EXACT DUPLICATE ROWS")
print("=" * 70)

exact_duplicates = df.duplicated().sum()
print(f"Exact duplicate rows: {exact_duplicates}")

print("\n" + "=" * 70)
print("DUPLICATED REQUEST IDs")
print("=" * 70)

duplicated_request_ids = df["Request_ID"].duplicated(
    keep=False
).sum()

unique_duplicated_ids = df.loc[
    df["Request_ID"].duplicated(keep=False),
    "Request_ID"
].nunique()

print(f"Rows with duplicated Request_ID: {duplicated_request_ids}")
print(f"Unique duplicated Request_ID values: {unique_duplicated_ids}")

print("\n" + "=" * 70)
print("UNIQUE VALUES BY COLUMN")
print("=" * 70)

unique_summary = df.nunique(dropna=False).sort_values()

print(unique_summary.to_string())

print("\n" + "=" * 70)
print("DUPLICATED REQUEST ID SAMPLE")
print("=" * 70)

duplicate_id_sample = (
    df[df["Request_ID"].duplicated(keep=False)]
    .sort_values("Request_ID")
    .head(20)
)

print(
    duplicate_id_sample[
        [
            "Request_ID",
            "Created_Date",
            "Service_Type",
            "Department",
            "Status",
            "Priority",
            "Resolution_Days",
            "Satisfaction_Score"
        ]
    ].to_string(index=False)
)

print("\n" + "=" * 70)
print("RAW CATEGORY VALUES")
print("=" * 70)

category_columns = [
    "Service_Type",
    "Department",
    "Status",
    "Priority",
    "Request_Channel",
    "City",
    "State"
]

for column in category_columns:
    print(f"\n{column}:")
    print(df[column].value_counts(dropna=False).to_string())

print("\n" + "=" * 70)
print("INITIAL DATA QUALITY SUMMARY")
print("=" * 70)

total_rows = len(df)
total_columns = len(df.columns)
total_missing = int(df.isna().sum().sum())
exact_duplicates = int(df.duplicated().sum())
duplicate_request_rows = int(
    df["Request_ID"].duplicated(keep=False).sum()
)

summary = {
    "Total Rows": total_rows,
    "Total Columns": total_columns,
    "Total Missing Values": total_missing,
    "Exact Duplicate Rows": exact_duplicates,
    "Rows With Duplicated Request_ID": duplicate_request_rows,
    "Unique Request_ID Values": int(df["Request_ID"].nunique())
}

for item, value in summary.items():
    print(f"{item:<35}: {value:,}")

print("\n" + "=" * 70)
print("POTENTIAL DATA QUALITY ISSUES")
print("=" * 70)

issues = [
    "Created_Date and Closed_Date were loaded as text.",
    "Some records contain missing values.",
    "Exact duplicate rows exist.",
    "Some Request_ID values appear more than once.",
    "Text categories contain spelling, case, and spacing variations.",
    "Satisfaction_Score contains values outside the expected 1-5 range.",
    "Resolution_Days may contain negative or extreme values.",
    "Latitude and Longitude may contain invalid coordinates.",
    "Zip_Code, Email, and Phone require format validation."
]

for number, issue in enumerate(issues, start=1):
    print(f"{number:02}. {issue}")