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.4No randomness is used (random_seed = null)."""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={"iso3": str, "country_name": str})14n_raw = len(raw)15for c in ["iso3", "country_name"]:16 raw[c] = raw[c].str.strip()17excl = []18issues = []1920def step(name, rule, before, after, why):21 excl.append(dict(step=name, rule=rule, n_before=len(before), n_after=len(after),22 n_removed=len(before) - len(after), rationale=why))2324# Step 1: restrict to study window25d0 = raw26d1 = d0[d0["year"].between(2000, 2022)].copy()27step("1_year_window", "keep 2000 <= year <= 2022", d0, d1, "study window defined by the task")2829# Step 2: duplicated (iso3, year) rows30dup_mask = d1.duplicated(["iso3", "year"], keep=False)31dups = d1[dup_mask]32conflicting = dups.groupby(["iso3", "year"])["life_expectancy"].nunique(dropna=False)33conflicting = conflicting[conflicting > 1]34if len(conflicting):35 raise SystemExit(f"Conflicting duplicate values found: {conflicting}")36for iso, g in dups.groupby("iso3"):37 names = sorted(g["country_name"].unique())38 issues.append({39 "issue": f"{iso}: {g[['iso3','year']].drop_duplicates().shape[0]} country-years appear "40 f"{len(g)} times in total under country names {names} (two extractions; API renamed the "41 f"country); duplicate values are identical",42 "handling": "country identified by iso3; kept one row per (iso3, year) (first occurrence); "43 "values identical so choice does not affect results"})44d2 = d1.drop_duplicates(["iso3", "year"], keep="first").copy()45step("2_dedup_iso3_year", "keep one row per (iso3, year); duplicates have identical values", d1, d2,46 "each country contributes at most one observation per year (assembly duplicates)")47# harmonise display name to the most recent spelling (last occurrence in file) per iso348latest_name = d1.groupby("iso3")["country_name"].last()49d2["country_name"] = d2["iso3"].map(latest_name)5051# Completeness per country52miss_rows = d2[d2["life_expectancy"].isna()]53for _, r in miss_rows.iterrows():54 issues.append({"issue": f"{r.iso3} {int(r.year)}: empty life_expectancy cell",55 "handling": "treated as missing (no imputation); country excluded from balanced set"})56obs = d2.dropna(subset=["life_expectancy"])57years_by = obs.groupby("iso3")["year"].apply(set)58all_iso = sorted(d2["iso3"].unique())59balanced = sorted(i for i in all_iso if set(YEARS) <= years_by.get(i, set()))60incomplete = [i for i in all_iso if i not in balanced]61for i in incomplete:62 present = years_by.get(i, set())63 missing_years = sorted(set(YEARS) - present)64 if len(missing_years) > 1 and missing_years == list(range(missing_years[0], missing_years[-1] + 1)):65 desc = f"{missing_years[0]}-{missing_years[-1]}"66 else:67 desc = ",".join(map(str, missing_years))68 if not (len(missing_years) == 1 and i in set(miss_rows.iso3)):69 issues.append({"issue": f"{i}: no rows/values for years {desc} ({len(missing_years)} of 23 years missing)",70 "handling": "treated as missing (no imputation); country excluded from balanced set"})7172# Step 3: drop rows with empty value73d3 = d2.dropna(subset=["life_expectancy"])74step("3_drop_missing_value", "drop rows with empty life_expectancy", d2, d3,75 "a country-year without a recorded value is missing; no imputation")76# Step 4: drop countries not observed in all 23 years77d4 = d3[d3["iso3"].isin(balanced)]78step("4_balanced_panel", "keep only iso3 with a recorded value in every year 2000-2022", d3, d4,79 f"balanced panel; excluded countries: {', '.join(incomplete)}")8081analytic = d4[["iso3", "country_name", "year", "life_expectancy"]].sort_values(["iso3", "year"]).reset_index(drop=True)82assert len(analytic) == len(balanced) * len(YEARS)83assert not analytic.duplicated(["iso3", "year"]).any()84analytic.to_csv(f"{OUT}/analytic.csv", index=False)85pd.DataFrame(excl).to_csv(f"{OUT}/exclusions.csv", index=False)8687m = analytic.groupby("year")["life_expectancy"].mean()88m2000, m2022 = float(m[2000]), float(m[2022])89avail22 = d3[d3["year"] == 2022] # already one row per iso390results = {91 "task_id": "F1-conf-02",92 "n_countries_total": len(all_iso),93 "n_countries_balanced": len(balanced),94 "n_countries_incomplete": len(incomplete),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(float(avail22["life_expectancy"].mean()), 6),101 "n_countries_2022_available": int(avail22["iso3"].nunique()),102 "incomplete_countries": incomplete,103 "balanced_means_by_year": {str(y): round(float(v), 6) for y, v in m.items()},104 "feasibility": "complete",105 "feasibility_reason": None,106 "data_issues": issues,107 "n_input_rows": {"life_expectancy_panel.csv": int(n_raw)},108 "random_seed": None,109}110with open(f"{OUT}/results.json", "w") as f:111 json.dump(results, f, indent=2)112print(json.dumps({k: results[k] for k in ["n_countries_total", "n_countries_balanced", "n_analysed",113 "mean_2000_balanced", "mean_2022_balanced", "change_2000_2022_balanced", "mean_2022_all_available"]}, indent=1))114