merge_data.py

"""
Merge WHO Global Health Observatory datasets into one enriched CSV.
Outputs: merged_healthcare_data.csv
"""

import pandas as pd
import os

BASE = os.path.dirname(os.path.abspath(__file__))


# --- Load GHO-format files (data.csv, bedsdata.csv) ---

gho_files = {
    "UHC_Index": "data.csv",
    "Beds_per_10k": "bedsdata.csv"
}


def load_gho(filename, value_col_name):
    df = pd.read_csv(os.path.join(BASE, filename))
    df = df[df["Location type"] == "Country"].copy()
    df = df[["SpatialDimValueCode", "Location", "Period", "ParentLocationCode", "ParentLocation", "FactValueNumeric"]].copy()
    df = df.rename(columns={
        "SpatialDimValueCode": "CountryCode",
        "Location": "Country",
        "Period": "Year",
        "ParentLocationCode": "RegionCode",
        "ParentLocation": "Region",
        "FactValueNumeric": value_col_name
    })
    df["Year"] = df["Year"].astype(int)
    df = df.drop_duplicates(subset=["CountryCode", "Year"], keep="first")
    return df


gho_dfs = {}
for col_name, fname in gho_files.items():
    gho_dfs[col_name] = load_gho(fname, col_name)
    print(f"{col_name}: {gho_dfs[col_name].shape}")


# --- Load HWF files (health workforce) ---

hwf_indicators = {
    "HWF_0001": "Doctors_per_10k",
    "HWF_0006": "Nurses_per_10k",
    "HWF_0010": "Dentists_per_10k",
    "HWF_0014": "Pharmacists_per_10k",
    "HWF_0002": "Doctors_count",
    "HWF_0007": "Nurses_count",
    "HWF_0011": "Dentists_count",
    "HWF_0015": "Pharmacists_count",
    "HWF_0024": "CommunityHealthWorkers_count",
}

HWF_DIR = os.path.join(BASE, "health-workforce", "data")


def load_hwf(indicator_code, col_name):
    fpath = os.path.join(HWF_DIR, f"{indicator_code}.csv")
    df = pd.read_csv(fpath)
    df = df[df["SpatialDimension"] == "COUNTRY"].copy()
    df = df[["SpatialDimensionValueCode", "ParentLocationCode", "ParentLocation", "TimeDim", "NumericValue"]].copy()
    df = df.rename(columns={
        "SpatialDimensionValueCode": "CountryCode",
        "ParentLocationCode": "RegionCode",
        "ParentLocation": "Region",
        "TimeDim": "Year",
        "NumericValue": col_name
    })
    df["Year"] = df["Year"].astype(int)
    df = df.drop_duplicates(subset=["CountryCode", "Year"], keep="first")
    return df


hwf_dfs = {}
for code, col_name in hwf_indicators.items():
    hwf_dfs[col_name] = load_hwf(code, col_name)
    print(f"{col_name}: {hwf_dfs[col_name].shape}")


# --- Load World Bank Population Density ---

WB_DIR = os.path.join(BASE, "API_EN.POP.DNST_DS2_en_csv_v2_460")

# Main data: wide format with year columns 1960-2025
wb_density = pd.read_csv(os.path.join(WB_DIR, "API_EN.POP.DNST_DS2_en_csv_v2_460.csv"), skiprows=4)
year_cols = [c for c in wb_density.columns if c.isdigit()]
wb_long = wb_density.melt(
    id_vars=["Country Code"],
    value_vars=year_cols,
    var_name="Year",
    value_name="PopDensity"
).rename(columns={"Country Code": "CountryCode"})
wb_long["Year"] = wb_long["Year"].astype(int)
wb_long = wb_long.dropna(subset=["PopDensity"])
print(f"PopDensity: {wb_long.shape}")

# Metadata: income group per country
wb_meta = pd.read_csv(os.path.join(WB_DIR, "Metadata_Country_API_EN.POP.DNST_DS2_en_csv_v2_460.csv"))
wb_meta = wb_meta[["Country Code", "IncomeGroup"]].rename(columns={"Country Code": "CountryCode"})
wb_meta = wb_meta.dropna(subset=["IncomeGroup"])
wb_meta = wb_meta.drop_duplicates("CountryCode")
print(f"IncomeGroup lookup: {wb_meta.shape}")


# --- Merge all datasets ---

merged = hwf_dfs["Doctors_per_10k"][["CountryCode", "RegionCode", "Region", "Year", "Doctors_per_10k"]].copy()

for col_name, df in hwf_dfs.items():
    if col_name == "Doctors_per_10k":
        continue
    merged = merged.merge(
        df[["CountryCode", "Year", col_name]],
        on=["CountryCode", "Year"],
        how="outer"
    )

for col_name, df in gho_dfs.items():
    merged = merged.merge(
        df[["CountryCode", "Year", col_name]],
        on=["CountryCode", "Year"],
        how="outer"
    )

# Merge World Bank population density
merged = merged.merge(
    wb_long[["CountryCode", "Year", "PopDensity"]],
    on=["CountryCode", "Year"],
    how="outer"
)

# Merge World Bank income group
merged = merged.merge(wb_meta, on="CountryCode", how="left")

# Fill Region info for rows that came in via outer join
region_lookup = pd.concat([
    hwf_dfs["Doctors_per_10k"][["CountryCode", "RegionCode", "Region"]],
    gho_dfs["Beds_per_10k"][["CountryCode", "RegionCode", "Region"]],
]).drop_duplicates("CountryCode", keep="first").set_index("CountryCode")

country_lookup = gho_dfs["Beds_per_10k"][["CountryCode", "Country"]].drop_duplicates("CountryCode").set_index("CountryCode")

mask = merged["Region"].isna()
merged.loc[mask, "RegionCode"] = merged.loc[mask, "CountryCode"].map(region_lookup["RegionCode"])
merged.loc[mask, "Region"] = merged.loc[mask, "CountryCode"].map(region_lookup["Region"])

merged["Country"] = merged["CountryCode"].map(country_lookup["Country"])

# Reorder columns
id_cols = ["CountryCode", "Country", "RegionCode", "Region", "IncomeGroup", "Year"]
feature_cols = [c for c in merged.columns if c not in id_cols]
merged = merged[id_cols + sorted(feature_cols)]
merged = merged.sort_values(["CountryCode", "Year"]).reset_index(drop=True)


# --- Coverage report ---

print(f"\nMerged dataset shape: {merged.shape}")
print("=== Missing values per column ===")
total = len(merged)
for col in sorted(feature_cols):
    n_valid = merged[col].notna().sum()
    pct = 100 * n_valid / total
    print(f"  {col:40s}: {n_valid:5d} / {total} ({pct:.1f}%)")

print(f"\nUnique countries: {merged['CountryCode'].nunique()}")
print(f"Year range: {merged['Year'].min()} - {merged['Year'].max()}")
print(f"Regions: {merged['Region'].dropna().unique().tolist()}")


# --- Save ---

out_path = os.path.join(BASE, "merged_healthcare_data.csv")
merged.to_csv(out_path, index=False)
print(f"\nSaved to: {out_path}")