1"""F1-conf-02: balanced-panel mean life expectancy 2000-2022.2Run from workspace root: ~/wp29/venv/bin/python out/analysis.py3Reads only data/; writes out/results.json, out/analytic.csv, out/exclusions.csv. No randomness."""4import json, os5import pandas as pd67DATA = "data/life_expectancy_panel.csv"8OUT = "out"9YEARS = list(range(2000, 2023))1011raw = pd.read_csv(DATA, dtype={"iso3": str, "country_name": str})12n_raw = len(raw)13raw["iso3"] = raw["iso3"].str.strip()14raw["country_name"] = raw["country_name"].str.strip()15issues, excl = [], []1617def log(step, rule, before, after, why):18 excl.append(dict(step=step, rule=rule, n_before=before, n_after=after,19 n_removed=before - after, rationale=why))2021# Step 1: duplicated (iso3, year) rows22dkey = raw[raw.duplicated(["iso3", "year"], keep=False)]23d = raw24if len(dkey):25 conflicts = dkey.groupby(["iso3", "year"])["life_expectancy"].nunique(dropna=False)26 if (conflicts > 1).any():27 raise SystemExit("Conflicting duplicate values found: " + str(conflicts[conflicts > 1]))28 for iso, g in dkey.groupby("iso3"):29 names = sorted(g["country_name"].unique())30 issues.append({"issue": f"{iso}: {g[['iso3','year']].drop_duplicates().shape[0]} country-years appear twice "31 f"under names {names} with identical life_expectancy values (assembly duplicate of two extractions)",32 "handling": "kept one row per (iso3, year) (last occurrence, name 'Turkiye' for TUR); "33 "country identified by iso3 as required"})34 d = raw.drop_duplicates(["iso3", "year"], keep="last")35log(1, "keep one row per (iso3, year); drop exact-value duplicates", n_raw, len(d),36 "duplicated country-years from two extractions (TUR as 'Turkey' and 'Turkiye'), identical values")3738# Step 2: missing values39miss = d[d["life_expectancy"].isna()]40for _, r in miss.iterrows():41 issues.append({"issue": f"{r.iso3} {int(r.year)}: empty life_expectancy cell",42 "handling": "treated as missing (no imputation); country therefore not in balanced set"})43d2 = d.dropna(subset=["life_expectancy"])44log(2, "drop rows with empty life_expectancy", len(d), len(d2), "no recorded value; imputation not allowed")4546# Step 3: restrict to balanced countries47d2 = d2[d2["year"].isin(YEARS)]48cover = d2.groupby("iso3")["year"].nunique()49all_iso = sorted(d["iso3"].unique())50balanced = sorted([c for c in all_iso if cover.get(c, 0) == len(YEARS)])51incomplete = [c for c in all_iso if c not in balanced]52for c in incomplete:53 yrs = sorted(d2.loc[d2.iso3 == c, "year"])54 if c in miss.iso3.values:55 continue56 issues.append({"issue": f"{c}: recorded only for {min(yrs)}-{max(yrs)} ({len(yrs)} of {len(YEARS)} years); rows for 2000-{min(yrs)-1} absent from file",57 "handling": "excluded from balanced set; included in 2022 all-available baseline"})58an = d2[d2["iso3"].isin(balanced)].sort_values(["iso3", "year"])59log(3, "keep only countries with a recorded value in every year 2000-2022", len(d2), len(an),60 f"balanced-panel definition; excluded {incomplete}")6162an = an[["iso3", "country_name", "year", "life_expectancy"]]63assert len(an) == len(balanced) * len(YEARS) and not an.duplicated(["iso3", "year"]).any()6465m = an.groupby("year")["life_expectancy"].mean()66m2000, m2022 = float(m[2000]), float(m[2022])67base = d2[d2.year == 2022].drop_duplicates("iso3")["life_expectancy"].mean()6869res = {70 "task_id": "F1-conf-02",71 "n_countries_total": len(all_iso),72 "n_countries_balanced": len(balanced),73 "n_countries_incomplete": len(incomplete),74 "balanced_countries": balanced,75 "n_analysed": int(len(an)),76 "mean_2000_balanced": round(m2000, 6),77 "mean_2022_balanced": round(m2022, 6),78 "change_2000_2022_balanced": round(m2022 - m2000, 6),79 "mean_2022_all_available": round(float(base), 6),80 "n_countries_2022_all_available": int(d2[d2.year == 2022]["iso3"].nunique()),81 "incomplete_countries": incomplete,82 "feasibility": "complete",83 "feasibility_reason": None,84 "data_issues": issues,85 "n_input_rows": {"life_expectancy_panel.csv": int(n_raw)},86 "random_seed": None,87}88os.makedirs(OUT, exist_ok=True)89an.to_csv(f"{OUT}/analytic.csv", index=False)90pd.DataFrame(excl).to_csv(f"{OUT}/exclusions.csv", index=False)91with open(f"{OUT}/results.json", "w") as f:92 json.dump(res, f, indent=2, ensure_ascii=False)93print(json.dumps({k: res[k] for k in ["n_countries_balanced", "n_analysed", "mean_2000_balanced",94 "mean_2022_balanced", "change_2000_2022_balanced", "mean_2022_all_available"]}, indent=1))95