1"""F1-conf-02: balanced-panel mean life expectancy 2000-2022.2Reads only data/life_expectancy_panel.csv; writes out/results.json, out/analytic.csv, out/exclusions.csv.3Deterministic; no randomness used."""4import json, os5import pandas as pd67ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))8IN = os.path.join(ROOT, "data", "life_expectancy_panel.csv")9OUT = os.path.join(ROOT, "out")10YEARS = list(range(2000, 2023))1112raw = pd.read_csv(IN, dtype={"iso3": str, "country_name": str},13 keep_default_na=False, na_values=[""])14raw["iso3"] = raw["iso3"].str.strip()15n_raw = len(raw)16issues, excl = [], []1718def step(name, rule, before, after, why):19 excl.append(dict(step=name, rule=rule, n_before=before, n_after=after,20 n_removed=before - after, rationale=why))2122# 1. Duplicate (iso3, year) rows23d = raw.copy()24dup_mask = d.duplicated(["iso3", "year"], keep=False)25dups = d[dup_mask]26for iso, g in dups.groupby("iso3"):27 conflicting = g.groupby("year")["life_expectancy"].nunique(dropna=False).gt(1).sum()28 if conflicting:29 raise RuntimeError(f"Conflicting duplicate values for {iso}; manual resolution needed")30 names = sorted(g["country_name"].unique())31 issues.append({32 "issue": f"{iso} appears twice for each of {g['year'].nunique()} years under names {names} "33 f"(assembly of two extractions); duplicate values are identical",34 "handling": "Kept one row per (iso3, year) (identity is iso3); dropped the duplicate rows; "35 "country_name harmonised to the last-listed spelling in the file"})36# keep the last occurrence (later, wider extraction) -> name 'Turkiye'37d = d.drop_duplicates(["iso3", "year"], keep="last")38# harmonise country_name per iso3 to the name of its last row39last_name = d.groupby("iso3")["country_name"].last()40d["country_name"] = d["iso3"].map(last_name)41step("1_dedupe", "drop duplicate (iso3, year) rows; keep one per country-year",42 n_raw, len(d), "each country contributes at most one observation per year; duplicates had identical values")4344# 2. Missing values45miss = d[d["life_expectancy"].isna()]46for _, r in miss.iterrows():47 issues.append({"issue": f"{r.iso3} {r.year}: empty life_expectancy cell",48 "handling": "Treated as missing (no imputation); country excluded from the balanced set"})49n0 = len(d)50d = d[d["life_expectancy"].notna()]51step("2_drop_missing", "drop rows with empty life_expectancy", n0, len(d),52 "no imputation allowed; a country-year without a recorded value is missing")5354# 3. Year range check55n0 = len(d)56d = d[d["year"].between(2000, 2022)]57step("3_year_range", "keep years 2000-2022", n0, len(d), "study window")5859# 4. Balanced panel60yrs = d.groupby("iso3")["year"].apply(lambda s: set(s))61balanced = sorted([i for i, s in yrs.items() if set(YEARS) <= s])62all_iso = sorted(raw["iso3"].unique())63incomplete = [i for i in all_iso if i not in balanced]64for i in incomplete:65 have = sorted(yrs.get(i, set()))66 if have and have == list(range(have[0], have[-1] + 1)) and have[0] > 2000:67 issues.append({"issue": f"{i}: rows only for {have[0]}-{have[-1]} (years 2000-{have[0]-1} absent from file)",68 "handling": "Not imputed; country excluded from the balanced set (still counted in 2022 all-available baseline)"})69n0 = len(d)70a = d[d["iso3"].isin(balanced)].sort_values(["iso3", "year"]).reset_index(drop=True)71step("4_balanced_panel", "keep countries with a recorded value in every year 2000-2022", n0, len(a),72 f"balanced-panel estimand; excluded countries: {', '.join(incomplete)}")73assert len(a) == len(balanced) * len(YEARS)74assert not a.duplicated(["iso3", "year"]).any()7576a[["iso3", "country_name", "year", "life_expectancy"]].to_csv(os.path.join(OUT, "analytic.csv"), index=False)77ex = pd.DataFrame(excl)78ex.to_csv(os.path.join(OUT, "exclusions.csv"), index=False)79assert ex["n_removed"].sum() == n_raw - len(a)8081m00 = float(a.loc[a.year == 2000, "life_expectancy"].mean())82m22 = float(a.loc[a.year == 2022, "life_expectancy"].mean())83avail22 = d[d.year == 2022].drop_duplicates("iso3") # already deduplicated84m22_all = float(avail22["life_expectancy"].mean())8586chg = (a[a.year == 2022].set_index("iso3")["life_expectancy"]87 - a[a.year == 2000].set_index("iso3")["life_expectancy"])88res = {89 "task_id": "F1-conf-02",90 "n_countries_total": len(all_iso),91 "n_countries_balanced": len(balanced),92 "n_countries_incomplete": len(incomplete),93 "balanced_countries": balanced,94 "n_analysed": int(len(a)),95 "mean_2000_balanced": round(m00, 6),96 "mean_2022_balanced": round(m22, 6),97 "change_2000_2022_balanced": round(m22 - m00, 6),98 "mean_2022_all_available": round(m22_all, 6),99 "n_countries_2022_available": int(avail22["iso3"].nunique()),100 "incomplete_countries": incomplete,101 "gap_2022_all_available_minus_balanced": round(m22_all - m22, 6),102 "per_country_change_min": round(float(chg.min()), 6),103 "per_country_change_min_iso3": str(chg.idxmin()),104 "per_country_change_max": round(float(chg.max()), 6),105 "per_country_change_max_iso3": str(chg.idxmax()),106 "per_country_change_sd": round(float(chg.std(ddof=1)), 6),107 "feasibility": "complete",108 "feasibility_reason": None,109 "data_issues": issues,110 "n_input_rows": {"life_expectancy_panel.csv": int(n_raw)},111 "random_seed": None,112}113with open(os.path.join(OUT, "results.json"), "w") as f:114 json.dump(res, f, indent=2, ensure_ascii=False)115print(json.dumps({k: res[k] for k in ["n_countries_balanced", "n_analysed", "mean_2000_balanced",116 "mean_2022_balanced", "change_2000_2022_balanced",117 "mean_2022_all_available"]}, indent=1))118