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"6OUT = "out"7YEARS = list(range(2000, 2023))8os.makedirs(OUT, exist_ok=True)910raw = pd.read_csv(IN, dtype={"country_name": str, "iso3": str, "year": int})11raw["iso3"] = raw["iso3"].str.strip()12n_read = len(raw)13issues, ledger = [], []1415def step(name, rule, before, after, why):16 ledger.append(dict(step=len(ledger) + 1, rule=rule, n_before=before, n_after=after,17 n_removed=before - after, rationale=why))1819n_total = raw["iso3"].nunique()2021# 1. duplicate (iso3, year) rows22dup_mask = raw.duplicated(["iso3", "year"], keep=False)23dups = raw[dup_mask]24conflict = dups.groupby(["iso3", "year"])["life_expectancy"].nunique(dropna=False).gt(1)25if conflict.any():26 raise SystemExit(f"Conflicting duplicate values: {conflict[conflict].index.tolist()}")27for iso, g in dups.groupby("iso3"):28 names = sorted(g["country_name"].unique())29 issues.append({"issue": f"{iso}: every (iso3, year) for {g['year'].nunique()} years appears twice "30 f"under different country_name labels {names} (assembly of two extractions); "31 "values are identical across the copies.",32 "handling": "Identified countries by iso3 and kept one row per (iso3, year) "33 "(the later-listed copy, i.e. current API label); values unaffected."})34d = raw.drop_duplicates(["iso3", "year"], keep="last").copy()35step("deduplicate", "keep one row per (iso3, year)", n_read, len(d),36 "Exact-duplicate country-years from merging two extractions (same values, different name label).")37# harmonise name to one label per iso3 (last-listed)38d["country_name"] = d["iso3"].map(d.groupby("iso3")["country_name"].last())3940# 2. missing values41miss = d[d["life_expectancy"].isna()]42for _, r in miss.iterrows():43 issues.append({"issue": f"{r.iso3} {r.year}: empty life_expectancy cell (no recorded value).",44 "handling": "Treated as missing; not imputed. Country therefore not in balanced set."})45before = len(d)46d = d[d["life_expectancy"].notna()]47step("drop_missing", "drop rows with empty life_expectancy", before, len(d),48 "No recorded value; imputation/interpolation is prohibited.")4950# 3. restrict to study years (sanity)51before = len(d)52d = d[d["year"].between(2000, 2022)]53step("year_window", "keep years 2000-2022", before, len(d), "Study window.")5455# 4. balanced set56cov = d.groupby("iso3")["year"].apply(lambda s: set(s))57balanced = sorted(i for i, s in cov.items() if set(YEARS) <= s)58incomplete = sorted(set(raw["iso3"]) - set(balanced))59for iso in incomplete:60 have = cov.get(iso, set())61 missing_years = sorted(set(YEARS) - have)62 if missing_years and len(missing_years) > 1 and missing_years == list(range(missing_years[0], missing_years[-1] + 1)):63 desc = f"{missing_years[0]}-{missing_years[-1]}"64 else:65 desc = ",".join(map(str, missing_years))66 if iso not in set(miss["iso3"]):67 issues.append({"issue": f"{iso}: no rows at all for years {desc} (series only present "68 f"{min(have)}-{max(have)}; likely from the narrower extraction).",69 "handling": "Country excluded from balanced panel; still counted in the 2022 all-available baseline."})70before = len(d)71d = d[d["iso3"].isin(balanced)]72step("balanced_panel", "keep countries with a recorded value in every year 2000-2022", before, len(d),73 f"Excluded incomplete countries: {', '.join(incomplete)}.")7475analytic = d[["iso3", "country_name", "year", "life_expectancy"]].sort_values(["iso3", "year"])76assert len(analytic) == len(balanced) * len(YEARS)77assert not analytic.duplicated(["iso3", "year"]).any()78analytic.to_csv(f"{OUT}/analytic.csv", index=False)79pd.DataFrame(ledger).to_csv(f"{OUT}/exclusions.csv", index=False)8081m = analytic.groupby("year")["life_expectancy"].mean()82avail22 = raw.drop_duplicates(["iso3", "year"])83avail22 = avail22[(avail22["year"] == 2022) & avail22["life_expectancy"].notna()]84w = analytic.pivot(index="iso3", columns="year", values="life_expectancy")85chg = w[2022] - w[2000]86res = {87 "task_id": "F1-conf-02",88 "n_countries_total": int(n_total),89 "n_countries_balanced": len(balanced),90 "n_countries_incomplete": len(incomplete),91 "balanced_countries": balanced,92 "n_analysed": int(len(analytic)),93 "mean_2000_balanced": round(float(m[2000]), 4),94 "mean_2022_balanced": round(float(m[2022]), 4),95 "change_2000_2022_balanced": round(float(m[2022] - m[2000]), 4),96 "mean_2022_all_available": round(float(avail22["life_expectancy"].mean()), 4),97 "n_countries_2022_available": int(avail22["iso3"].nunique()),98 "descriptive_extra": {99 "country_change_min": round(float(chg.min()), 3), "country_change_min_iso3": chg.idxmin(),100 "country_change_max": round(float(chg.max()), 3), "country_change_max_iso3": chg.idxmax(),101 "country_change_sd": round(float(chg.std()), 4),102 "country_change_se_illustrative": round(float(chg.std() / len(chg) ** 0.5), 4),103 "mean_2022_balanced_minus_all_available": round(float(m[2022] - avail22["life_expectancy"].mean()), 4),104 },105 "feasibility": "complete",106 "feasibility_reason": None,107 "data_issues": issues,108 "n_input_rows": {"life_expectancy_panel.csv": int(n_read)},109 "random_seed": None,110}111with open(f"{OUT}/results.json", "w") as f:112 json.dump(res, f, indent=2, ensure_ascii=False)113print(json.dumps(res, indent=2, ensure_ascii=False))114print(pd.DataFrame(ledger).to_string())115