1"""F1-conf-02: balanced-panel mean life expectancy 2000-2022.2Run from workspace root: ~/wp29/venv/bin/python out/analysis.py3Reads only data/life_expectancy_panel.csv; writes out/results.json, out/analytic.csv, out/exclusions.csv.4No randomness is used."""5import json, os6import pandas as pd78DATA = "data/life_expectancy_panel.csv"9OUT = "out"10YEARS = list(range(2000, 2023))11os.makedirs(OUT, exist_ok=True)1213raw = pd.read_csv(DATA, dtype={"country_name": str, "iso3": str})14raw["iso3"] = raw["iso3"].str.strip()15raw["country_name"] = raw["country_name"].str.strip()16n_raw = len(raw)17ledger, issues = [], []1819def step(name, rule, before, after, rationale):20 ledger.append(dict(step=name, rule=rule, n_before=len(before), n_after=len(after),21 n_removed=len(before) - len(after), rationale=rationale))2223# --- Step 1: restrict to 2000-2022 (scope check) ---24d0 = raw25d1 = d0[d0["year"].isin(YEARS)].copy()26step("1_year_scope", "keep rows with year in 2000..2022", d0, d1, "Task scope; all rows already in range")2728# --- Step 2: resolve duplicate (iso3, year) rows ---29dup_mask = d1.duplicated(["iso3", "year"], keep=False)30dups = d1[dup_mask]31conflicts = []32for (iso, yr), g in dups.groupby(["iso3", "year"]):33 vals = g["life_expectancy"].dropna().unique()34 if len(vals) > 1:35 conflicts.append((iso, yr, list(vals)))36if len(conflicts):37 raise SystemExit(f"Conflicting duplicate values, need a decision: {conflicts}")38names_by_iso = d1.groupby("iso3")["country_name"].unique()39multi_name = {k: sorted(v) for k, v in names_by_iso.items() if len(v) > 1}40# canonical name: current World Bank spelling for TUR; otherwise the unique name41canonical = {"TUR": "Turkiye"}42d1["country_name"] = [canonical.get(i, n) if i in multi_name else n43 for i, n in zip(d1["iso3"], d1["country_name"])]44d2 = d1.sort_values(["iso3", "year"]).drop_duplicates(["iso3", "year"], keep="first")45step("2_dedupe_iso3_year", "one row per (iso3, year); duplicates had identical values",46 d1, d2, "Country identified by iso3; each country counted at most once per year")47if len(dups):48 for iso, nm in multi_name.items():49 n_dup = int(dups[dups.iso3 == iso].shape[0] - dups[dups.iso3 == iso][["iso3", "year"]].drop_duplicates().shape[0])50 issues.append({"issue": f"{iso} appears under two country names {nm} (two extractions), giving {n_dup} duplicated (iso3, year) rows with identical life_expectancy values",51 "handling": f"Kept one row per (iso3, year) (values identical, so no conflict); country_name harmonised to '{canonical.get(iso)}'; country counted once"})5253# --- Step 3: drop countries lacking a row for some year ---54yrs_present = d2.groupby("iso3")["year"].nunique()55short = sorted(yrs_present[yrs_present < len(YEARS)].index)56d3 = d2[~d2["iso3"].isin(short)]57step("3_drop_countries_missing_year_rows", "drop countries without a row for every year 2000..2022",58 d2, d3, f"Not in balanced panel (no imputation allowed): {', '.join(short)}")59for iso in short:60 yy = d2[d2.iso3 == iso]["year"]61 issues.append({"issue": f"{iso} has rows only for {yy.min()}-{yy.max()} ({yy.nunique()} of 23 years)",62 "handling": "Excluded from the balanced set; no imputation. Included in the 2022 all-available baseline"})6364# --- Step 4: drop countries with an empty value in any year ---65nmiss = d3.groupby("iso3")["life_expectancy"].apply(lambda s: s.isna().sum())66withna = sorted(nmiss[nmiss > 0].index)67d4 = d3[~d3["iso3"].isin(withna)]68step("4_drop_countries_with_empty_value", "drop countries with an empty life_expectancy cell in any year",69 d3, d4, f"Missing country-year => not balanced (no interpolation): {', '.join(withna)}")70for iso in withna:71 yy = d3[(d3.iso3 == iso) & d3.life_expectancy.isna()]["year"].tolist()72 issues.append({"issue": f"{iso} has an empty life_expectancy cell for year(s) {yy}",73 "handling": "Treated as missing (no interpolation); country excluded from the balanced set"})7475analytic = d4[["iso3", "country_name", "year", "life_expectancy"]].sort_values(["iso3", "year"]).reset_index(drop=True)76balanced = sorted(analytic["iso3"].unique())77assert analytic.groupby("iso3")["year"].nunique().eq(len(YEARS)).all()78assert analytic["life_expectancy"].notna().all()79assert len(analytic) == len(balanced) * len(YEARS)8081ym = analytic.groupby("year")["life_expectancy"].mean()82m2000, m2022 = float(ym.loc[2000]), float(ym.loc[2022])8384# baseline: all distinct countries with a recorded 2022 value (after dedupe, each counted once)85b22 = d2[(d2.year == 2022) & d2.life_expectancy.notna()]86assert b22["iso3"].is_unique87base2022 = float(b22["life_expectancy"].mean())8889n_total = int(d2["iso3"].nunique())90res = {91 "task_id": "F1-conf-02",92 "n_countries_total": n_total,93 "n_countries_balanced": len(balanced),94 "n_countries_incomplete": n_total - len(balanced),95 "balanced_countries": balanced,96 "n_analysed": int(len(analytic)),97 "mean_2000_balanced": round(m2000, 6),98 "mean_2022_balanced": round(m2022, 6),99 "change_2000_2022_balanced": round(m2022 - m2000, 6),100 "mean_2022_all_available": round(base2022, 6),101 "n_countries_2022_all_available": int(b22["iso3"].nunique()),102 "feasibility": "complete",103 "feasibility_reason": None,104 "data_issues": issues,105 "n_input_rows": {"life_expectancy_panel.csv": int(n_raw)},106 "random_seed": None,107 "balanced_yearly_means": {str(k): round(float(v), 6) for k, v in ym.items()},108}109analytic.to_csv(f"{OUT}/analytic.csv", index=False)110pd.DataFrame(ledger).to_csv(f"{OUT}/exclusions.csv", index=False)111with open(f"{OUT}/results.json", "w") as f:112 json.dump(res, f, indent=2, ensure_ascii=False)113print(f"balanced countries={len(balanced)}, n_analysed={len(analytic)}, "114 f"mean2000={m2000:.4f}, mean2022={m2022:.4f}, change={m2022-m2000:.4f}, baseline2022={base2022:.4f}")115