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})11n_read = len(raw)12for c in ["country_name", "iso3"]:13 raw[c] = raw[c].str.strip()14raw["year"] = raw["year"].astype(int)15d = raw[raw["year"].between(2000, 2022)].copy()16assert len(d) == n_read, "rows outside 2000-2022 found"1718issues, ledger = [], []19def step(rule, before, after, rationale):20 ledger.append(dict(step=len(ledger) + 1, rule=rule, n_before=before, n_after=after,21 n_removed=before - after, rationale=rationale))2223# 1. Duplicate (iso3, year) rows24names = d.groupby("iso3")["country_name"].unique()25multi = {k: sorted(v) for k, v in names.items() if len(v) > 1}26dupkey = d.duplicated(["iso3", "year"], keep=False)27dups = d[dupkey]28conflict = dups.groupby(["iso3", "year"])["life_expectancy"].nunique(dropna=False).gt(1).any()29assert not conflict, "conflicting duplicate values - needs manual decision"30# canonical name: the current World Bank spelling ("Turkiye") where alternatives exist31canon = {}32for k, v in multi.items():33 canon[k] = "Turkiye" if "Turkiye" in v else v[-1]34d["country_name"] = d.apply(lambda r: canon.get(r["iso3"], r["country_name"]), axis=1)35b = len(d)36d = d.drop_duplicates(["iso3", "year"], keep="first")37step("Drop duplicate (iso3, year) rows (identical values)", b, len(d),38 "Panel assembled from two extractions; some countries appear twice under different names. iso3 identifies the country; one observation per country-year.")39for k, v in multi.items():40 n = int(dups[dups.iso3 == k].shape[0] // 2)41 issues.append({"issue": f"{k} appears under two country names {v} with {n} duplicated country-years (identical values) — an assembly artifact of the two extractions.",42 "handling": f"Deduplicated by (iso3, year), keeping one row per year; values identical so no choice of value needed; name harmonised to '{canon[k]}'. Counted as one country."})4344# 2. Missing values45miss = d[d["life_expectancy"].isna()]46b = len(d)47d = d.dropna(subset=["life_expectancy"])48step("Drop country-years with empty life_expectancy", b, len(d),49 "Empty cell = no recorded value; no imputation allowed.")50for _, r in miss.iterrows():51 issues.append({"issue": f"{r.iso3} has an empty life_expectancy cell for {r.year} (row present, value missing).",52 "handling": "Treated as missing (not imputed/interpolated); country therefore excluded from the balanced set."})5354# 3. Restrict to balanced countries55cov = d.groupby("iso3")["year"].apply(lambda s: set(s))56balanced = sorted([k for k, s in cov.items() if set(YEARS) <= s])57all_iso = sorted(raw["iso3"].unique())58incomplete = [k for k in all_iso if k not in balanced]59for k in incomplete:60 have = sorted(cov.get(k, set()))61 missing_years = sorted(set(YEARS) - set(have))62 if k not in set(miss.iso3):63 issues.append({"issue": f"{k} has no rows for years {missing_years[0]}–{missing_years[-1]} ({len(missing_years)} years missing from the file; the country is present only from 2011)." if missing_years == list(range(missing_years[0], missing_years[-1] + 1)) else f"{k} missing years {missing_years}.",64 "handling": "Country excluded from the balanced panel; its available rows are used only in the 2022 all-available baseline."})65b = len(d)66an = d[d["iso3"].isin(balanced)].copy()67step("Keep only countries with a recorded value in every year 2000-2022", b, len(an),68 f"Balanced-panel estimand; excluded incomplete countries: {', '.join(incomplete)}.")6970an = an.sort_values(["iso3", "year"])[["iso3", "country_name", "year", "life_expectancy"]]71assert len(an) == len(balanced) * len(YEARS)72an.to_csv(f"{OUT}/analytic.csv", index=False)73pd.DataFrame(ledger).to_csv(f"{OUT}/exclusions.csv", index=False)7475m00 = an.loc[an.year == 2000, "life_expectancy"].mean()76m22 = an.loc[an.year == 2022, "life_expectancy"].mean()77all22 = d[d.year == 2022] # deduplicated, non-missing; one row per iso378assert all22.iso3.is_unique79w = an.pivot(index="iso3", columns="year", values="life_expectancy")80chg = w[2022] - w[2000]81from scipy import stats82t95 = stats.t.ppf(0.975, len(chg) - 1)83res = {84 "task_id": "F1-conf-02",85 "n_countries_total": len(all_iso),86 "n_countries_balanced": len(balanced),87 "n_countries_incomplete": len(incomplete),88 "balanced_countries": balanced,89 "n_analysed": len(an),90 "mean_2000_balanced": round(float(m00), 4),91 "mean_2022_balanced": round(float(m22), 4),92 "change_2000_2022_balanced": round(float(m22 - m00), 4),93 "mean_2022_all_available": round(float(all22.life_expectancy.mean()), 4),94 "n_countries_2022_all_available": int(all22.iso3.nunique()),95 "descriptive_country_change_sd": round(float(chg.std(ddof=1)), 4),96 "descriptive_country_change_min": {chg.idxmin(): round(float(chg.min()), 3)},97 "descriptive_country_change_max": {chg.idxmax(): round(float(chg.max()), 3)},98 "descriptive_change_t95_halfwidth_across_countries": round(float(t95 * chg.std(ddof=1) / len(chg) ** 0.5), 4),99 "descriptive_mean_2000_all_available": round(float(d.loc[d.year == 2000, "life_expectancy"].mean()), 4),100 "descriptive_n_countries_2000_all_available": int((d.year == 2000).sum()),101 "feasibility": "complete",102 "feasibility_reason": None,103 "data_issues": issues,104 "n_input_rows": {"life_expectancy_panel.csv": n_read},105 "random_seed": None,106}107with open(f"{OUT}/results.json", "w") as f:108 json.dump(res, f, indent=2, ensure_ascii=False)109print(json.dumps(res, indent=2, ensure_ascii=False))110