1"""F1-conf-02: balanced-panel mean life expectancy 2000-2022. Run from workspace root."""2import json, os3import pandas as pd45IN = "data/life_expectancy_panel.csv"6YEARS = list(range(2000, 2023))7os.makedirs("out", exist_ok=True)89raw = pd.read_csv(IN, dtype={"country_name": str, "iso3": str})10for c in ["country_name", "iso3"]:11 raw[c] = raw[c].str.strip()12n_read = len(raw)13issues, ledger = [], []1415def step(rule, before, after, rationale):16 ledger.append(dict(step=len(ledger) + 1, rule=rule, n_before=before, n_after=after,17 n_removed=before - after, rationale=rationale))1819# --- integrity checks20assert raw["year"].between(2000, 2022).all()21names = raw.groupby("iso3")["country_name"].unique()22multi = names[names.apply(len) > 1]23for iso, nm in multi.items():24 issues.append({"issue": f"iso3 {iso} appears under several country names {sorted(nm)} (assembly of two extractions)",25 "handling": "Countries identified by iso3 only; a single name kept (the last listed in the file, i.e. the later spelling)."})2627# --- step 1: duplicate (iso3, year) rows28d = raw.copy()29dupmask = d.duplicated(["iso3", "year"], keep=False)30if dupmask.any():31 g = d[dupmask].groupby(["iso3", "year"])["life_expectancy"].nunique(dropna=False)32 conflicting = g[g > 1]33 if len(conflicting):34 raise SystemExit(f"Conflicting duplicate values: {conflicting}")35 n_dup_rows = int(d.duplicated(["iso3", "year"]).sum())36 issues.append({"issue": f"{n_dup_rows} duplicate (iso3, year) rows for {sorted(d[dupmask].iso3.unique())}; values identical across duplicates",37 "handling": "Kept one row per (iso3, year) (last occurrence); since values are identical the choice does not affect results."})38before = len(d)39d = d.drop_duplicates(["iso3", "year"], keep="last").copy()40step("Remove duplicate (iso3, year) rows", before, len(d),41 "Each country contributes at most one observation per year; duplicates had identical values.")42# harmonise name per iso3 (last listed)43d["country_name"] = d["iso3"].map(raw.groupby("iso3")["country_name"].last())4445# --- step 2: rows with missing value46miss = d[d["life_expectancy"].isna()]47for _, r in miss.iterrows():48 issues.append({"issue": f"Missing life_expectancy for {r.iso3} {r.year} (empty cell)",49 "handling": "Treated as missing (no imputation); country therefore excluded from the balanced set."})50before = len(d)51d = d.dropna(subset=["life_expectancy"])52step("Remove country-years with no recorded life_expectancy", before, len(d),53 "Empty cell = no recorded value; no imputation allowed.")5455# --- coverage gaps (absent rows)56cov = d.groupby("iso3")["year"].apply(lambda s: sorted(set(s)))57balanced = sorted([i for i, ys in cov.items() if ys == YEARS])58all_iso = sorted(raw["iso3"].unique())59for iso in all_iso:60 if iso in balanced:61 continue62 ys = cov.get(iso, [])63 missing_years = [y for y in YEARS if y not in ys]64 absent = [y for y in missing_years if not ((raw.iso3 == iso) & (raw.year == y)).any()]65 if absent:66 issues.append({"issue": f"{iso} has no rows for years {absent[0]}-{absent[-1]} ({len(absent)} years absent from file)",67 "handling": "Not imputed; country excluded from the balanced set (still counted in the 2022 all-available baseline if 2022 recorded)."})6869# --- step 3: restrict to balanced countries70before = len(d)71excluded = [i for i in all_iso if i not in balanced]72an = d[d["iso3"].isin(balanced)].sort_values(["iso3", "year"])[["iso3", "country_name", "year", "life_expectancy"]]73step(f"Keep only countries with a recorded value in every year 2000-2022 (drops {', '.join(excluded)})",74 before, len(an), "Balanced-panel estimand: fixed country set across all years.")75assert len(an) == len(balanced) * len(YEARS)76assert not an.duplicated(["iso3", "year"]).any()7778an.to_csv("out/analytic.csv", index=False)79pd.DataFrame(ledger).to_csv("out/exclusions.csv", index=False)8081m = an.groupby("year")["life_expectancy"].mean()82dedup = d # one row per iso3-year, non-missing83m22_all = dedup[dedup.year == 2022]["life_expectancy"].mean()84n22_all = int((dedup.year == 2022).sum())8586res = {87 "task_id": "F1-conf-02",88 "n_countries_total": len(all_iso),89 "n_countries_balanced": len(balanced),90 "n_countries_incomplete": len(all_iso) - len(balanced),91 "balanced_countries": balanced,92 "n_analysed": int(len(an)),93 "mean_2000_balanced": round(float(m[2000]), 6),94 "mean_2022_balanced": round(float(m[2022]), 6),95 "change_2000_2022_balanced": round(float(m[2022] - m[2000]), 6),96 "mean_2022_all_available": round(float(m22_all), 6),97 "feasibility": "complete",98 "feasibility_reason": None,99 "data_issues": issues,100 "n_input_rows": {"life_expectancy_panel.csv": int(n_read)},101 "random_seed": None,102}103with open("out/results.json", "w") as f:104 json.dump(res, f, indent=2, ensure_ascii=False)105print(json.dumps(res, indent=2, ensure_ascii=False))106print("n countries with 2022 value:", n22_all)107print(pd.DataFrame(ledger).to_string())108109# --- supplementary descriptives (not part of results.json schema)110import numpy as np111from scipy import stats112w = an.pivot(index="iso3", columns="year", values="life_expectancy")113ch = w[2022] - w[2000]114se = ch.std(ddof=1) / np.sqrt(len(ch))115tcrit = stats.t.ppf(0.975, len(ch) - 1)116supp = {117 "country_change_2000_2022": {k: round(float(v), 3) for k, v in ch.sort_values().items()},118 "sd_country_change": round(float(ch.std(ddof=1)), 4),119 "se_mean_change_superpopulation": round(float(se), 4),120 "ci95_mean_change_superpopulation": [round(float(ch.mean() - tcrit * se), 4), round(float(ch.mean() + tcrit * se), 4)],121 "n_countries_2022_all_available": n22_all,122 "mean_2000_all_available": round(float(d[d.year == 2000]["life_expectancy"].mean()), 6),123 "n_countries_2000_all_available": int((d.year == 2000).sum()),124 "mean_2022_raw_rows_with_duplicates": round(float(raw[raw.year == 2022]["life_expectancy"].mean()), 6),125}126with open("out/supplementary.json", "w") as f:127 json.dump(supp, f, indent=2)128print(json.dumps(supp, indent=2))129