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. Deterministic; no randomness used."""4import json, os5import pandas as pd67IN = "data/life_expectancy_panel.csv"8OUT = "out"9YEARS = list(range(2000, 2023))10os.makedirs(OUT, exist_ok=True)1112raw = pd.read_csv(IN, dtype={"country_name": str, "iso3": str}, keep_default_na=True)13n_raw = len(raw)14issues, excl = [], []1516def step(name, rule, before, after, why):17 excl.append(dict(step=name, rule=rule, n_before=before, n_after=after,18 n_removed=before - after, rationale=why))1920df = raw.copy()21df["iso3"] = df["iso3"].str.strip()22df["year"] = df["year"].astype(int)2324# 1. Exact/conflicting duplicates of (iso3, year)25dup = df[df.duplicated(["iso3", "year"], keep=False)]26if len(dup):27 g = dup.groupby(["iso3", "year"])["life_expectancy"].nunique(dropna=False)28 conflicting = g[g > 1]29 if len(conflicting):30 raise SystemExit(f"Conflicting duplicate values: {conflicting}")31 for iso, sub in dup.groupby("iso3"):32 names = sorted(sub.country_name.unique())33 issues.append({34 "issue": f"{iso}: {sub.duplicated(['iso3','year']).sum()} duplicated (iso3, year) rows "35 f"from the two extractions, under country names {names}; values identical in every duplicated year",36 "handling": "Kept one row per (iso3, year) (first occurrence); country identified by iso3; "37 "removed rows logged in exclusions.csv"})38b = len(df)39df = df.drop_duplicates(["iso3", "year"], keep="first")40step("1_dedupe", "drop duplicate (iso3, year) rows with identical values", b, len(df),41 "each country contributes at most one observation per year (TASK rule); duplicates are assembly artefacts")4243# harmonise country name per iso3 (use most recent name in file for display)44name_map = {}45for iso, sub in raw.groupby("iso3"):46 names = list(dict.fromkeys(sub.country_name))47 name_map[iso.strip()] = names[-1]48 if len(names) > 1:49 issues.append({"issue": f"{iso}: multiple country_name spellings {names}",50 "handling": f"Identified by iso3; analytic.csv uses '{names[-1]}' (last-listed name)"})51df["country_name"] = df["iso3"].map(name_map)5253# out-of-window years54b = len(df)55df = df[df.year.between(2000, 2022)]56step("2_year_window", "keep years 2000-2022", b, len(df), "estimand window")5758# 3. Missing values59miss = df[df.life_expectancy.isna()]60for _, r in miss.iterrows():61 issues.append({"issue": f"{r.iso3} {r.year}: empty life_expectancy cell",62 "handling": "Treated as missing (no imputation); country excluded from balanced set"})63b = len(df)64df = df[df.life_expectancy.notna()]65step("3_drop_missing", "drop rows with empty life_expectancy", b, len(df),66 "missing values are not imputed (TASK rule)")6768# rows absent for countries69for iso, sub in df.groupby("iso3"):70 present = set(sub.year)71 absent = [y for y in YEARS if y not in present and not ((miss.iso3 == iso) & (miss.year == y)).any()]72 if absent:73 issues.append({"issue": f"{iso}: no rows at all for years {min(absent)}-{max(absent)} ({len(absent)} years); "74 f"likely from the narrower earlier extraction",75 "handling": "Treated as missing (no imputation); country excluded from balanced set; "76 "its 2022 value still enters the all-available baseline"})7778# plausibility79if not df.life_expectancy.between(20, 100).all():80 raise SystemExit("implausible values")8182all_countries = sorted(raw.iso3.str.strip().unique())83counts = df.groupby("iso3").year.nunique()84balanced = sorted(counts[counts == len(YEARS)].index)85incomplete = sorted(set(all_countries) - set(balanced))8687b = len(df)88ana = df[df.iso3.isin(balanced)].sort_values(["iso3", "year"])89step("4_balanced", "keep countries with a recorded value in every year 2000-2022", b, len(ana),90 f"balanced-panel estimand; excluded incomplete countries {incomplete}")91ana = ana[["iso3", "country_name", "year", "life_expectancy"]]92assert len(ana) == len(balanced) * len(YEARS)93assert not ana.duplicated(["iso3", "year"]).any()94ana.to_csv(f"{OUT}/analytic.csv", index=False)9596ex = pd.DataFrame(excl)97assert ex.n_before.iloc[0] == n_raw and ex.n_after.iloc[-1] == len(ana)98ex.to_csv(f"{OUT}/exclusions.csv", index=False)99100m00 = float(ana[ana.year == 2000].life_expectancy.mean())101m22 = float(ana[ana.year == 2022].life_expectancy.mean())102avail22 = df[df.year == 2022].drop_duplicates("iso3")103m22_all = float(avail22.life_expectancy.mean())104105# per-year balanced means (supplementary, not required)106per_year = ana.groupby("year").life_expectancy.mean().round(6)107per_year.to_csv(f"{OUT}/balanced_means_by_year.csv")108109res = {110 "task_id": "F1-conf-02",111 "n_countries_total": len(all_countries),112 "n_countries_balanced": len(balanced),113 "n_countries_incomplete": len(incomplete),114 "balanced_countries": balanced,115 "incomplete_countries": incomplete,116 "n_analysed": int(len(ana)),117 "mean_2000_balanced": round(m00, 6),118 "mean_2022_balanced": round(m22, 6),119 "change_2000_2022_balanced": round(m22 - m00, 6),120 "mean_2022_all_available": round(m22_all, 6),121 "n_countries_2022_available": int(len(avail22)),122 "feasibility": "complete",123 "feasibility_reason": None,124 "data_issues": issues,125 "n_input_rows": {"life_expectancy_panel.csv": int(n_raw)},126 "random_seed": None,127}128with open(f"{OUT}/results.json", "w") as f:129 json.dump(res, f, indent=2)130print(json.dumps({k: res[k] for k in ["n_countries_balanced", "n_analysed", "mean_2000_balanced",131 "mean_2022_balanced", "change_2000_2022_balanced",132 "mean_2022_all_available"]}, indent=1))133