1"""Parse Seshat Polaris-2026 into (i) a long facts table with explicit uncertainty, (ii) polity-level2aggregates, (iii) the CrisisDB outcome table, (iv) polity metadata (macro-region, era, duration, succession).34Coding rules are those frozen in research/preregistration.md §4:5 P, IP -> 1 (IP flagged inferred); A, IA -> 0 (IA flagged inferred); A~P / P~A -> 0.5 (disputed);6 SU, U, unknown, blank, anything else -> NaN (logged in results/diagnostics/seshat_unparsed_values.csv).7Numeric ranges: midpoint (geometric for scale variables that are logged later; arithmetic for levels), bounds kept.8Nothing is forward-filled; nothing is imputed; undated facts are kept as "whole-polity" facts.9"""10from __future__ import annotations11import json12from pathlib import Path13import numpy as np14import pandas as pd15import yaml1617ROOT = Path(__file__).resolve().parents[3]18RAW = ROOT / "data" / "raw" / "seshat_polaris2026" / "Polaris2026.xlsx"19DIAG = ROOT / "results" / "diagnostics"2021# Variables that are numeric in Polaris (everything else in the SC/WF/RT sheets is a presence code)22GEOMETRIC = ["polity_population", "polity_territory", "population_of_the_largest_settlement"]23ARITHMETIC = ["settlement_hierarchy", "administrative_level", "military_level", "religious_level",24 "largest_communication_distance", "fastest_individual_communication"]25NUMERIC = GEOMETRIC + ARITHMETIC2627PRESENT = {"P": 1.0, "IP": 1.0, "present": 1.0, "inferred present": 1.0, "inferred P": 1.0}28ABSENT = {"A": 0.0, "IA": 0.0, "absent": 0.0, "inferred absent": 0.0, "inferred A": 0.0}29DISPUTED = {"A~P": 0.5, "P~A": 0.5, "IA~IP": 0.5, "IP~IA": 0.5, "A~IP": 0.5, "IP~A": 0.5, "P~IA": 0.5, "IA~P": 0.5}30INFERRED = {"IP", "IA", "inferred present", "inferred absent", "inferred P", "inferred A"}3132# CrisisDB items (pre-registration §3.5)33INSTABILITY_ITEMS = ["decline", "collapse", "downward_mobility", "extermination", "uprising", "revolution",34 "successful_revolution", "civil_war", "century_plus", "fragmentation", "capital", "conquest",35 "assassination", "depose"]36SEVERE_ITEMS = ["collapse", "fragmentation", "century_plus", "conquest", "civil_war"]37REFORM_ITEMS = ["constitution", "labor", "unfree_labor", "suffrage", "public_goods", "religion"]383940def load_sheets() -> dict[str, pd.DataFrame]:41 xl = pd.ExcelFile(RAW)42 return {s: xl.parse(s) for s in xl.sheet_names}434445def code_presence(v) -> tuple[float, bool, bool, bool]:46 """-> (value, inferred, disputed, unparsed)"""47 if v is None or (isinstance(v, float) and np.isnan(v)):48 return np.nan, False, False, False49 s = str(v).strip()50 if s in PRESENT:51 return 1.0, s in INFERRED, False, False52 if s in ABSENT:53 return 0.0, s in INFERRED, False, False54 if s in DISPUTED:55 return 0.5, False, True, False56 if s in {"SU", "U", "unknown", "suspected unknown", "SA", "SP", "DIS", "uncoded", ""}:57 return np.nan, False, False, False58 return np.nan, False, False, True596061def _num(v):62 try:63 x = float(str(v).replace(",", ""))64 return x if np.isfinite(x) else np.nan65 except Exception:66 return np.nan676869def build_facts(sheets: dict[str, pd.DataFrame], write_diag: bool = True) -> pd.DataFrame:70 frames = []71 for sheet in ["Social complexity", "Warfare", "Religion", "General"]:72 df = sheets[sheet].copy()73 df["sheet"] = sheet74 frames.append(df)75 f = pd.concat(frames, ignore_index=True)76 f = f.rename(columns={"variable_name": "variable"})77 f["dated"] = f.year_from.notna()78 f["year_from"] = pd.to_numeric(f.year_from, errors="coerce")79 f["year_to"] = pd.to_numeric(f.year_to, errors="coerce")80 f.loc[f.year_to.isna() & f.year_from.notna(), "year_to"] = f.year_from81 f["is_numeric"] = f.variable.isin(NUMERIC)82 # presence codes83 coded = f.loc[~f.is_numeric, "value_from"].map(code_presence)84 f["value"] = np.nan; f["inferred"] = False; f["disputed"] = False; f["unparsed"] = False85 f.loc[~f.is_numeric, "value"] = [c[0] for c in coded]86 f.loc[~f.is_numeric, "inferred"] = [c[1] for c in coded]87 f.loc[~f.is_numeric, "disputed"] = [c[2] for c in coded]88 f.loc[~f.is_numeric, "unparsed"] = [c[3] for c in coded]89 # General sheet is categorical text (centralization, succession...): keep raw text in `text`90 f["text"] = np.where(f.sheet == "General", f.value_from.astype(str), None)91 f.loc[f.sheet == "General", ["value", "unparsed"]] = [np.nan, False]92 # numeric93 lo = f.loc[f.is_numeric, "value_from"].map(_num)94 hi = f.loc[f.is_numeric, "value_to"].map(_num)95 hi = hi.where(hi.notna(), lo)96 lo2, hi2 = np.minimum(lo, hi), np.maximum(lo, hi)97 f.loc[f.is_numeric, "value_lo"] = lo298 f.loc[f.is_numeric, "value_hi"] = hi299 geo = f.is_numeric & f.variable.isin(GEOMETRIC)100 ari = f.is_numeric & f.variable.isin(ARITHMETIC)101 with np.errstate(divide="ignore", invalid="ignore"):102 f.loc[geo, "value"] = np.sqrt(f.loc[geo, "value_lo"].clip(lower=0) * f.loc[geo, "value_hi"].clip(lower=0))103 f.loc[ari, "value"] = (f.loc[ari, "value_lo"] + f.loc[ari, "value_hi"]) / 2104 # a strictly positive scale variable with value 0 is not a measurement -> NaN (logged)105 zero_scale = geo & (f.value <= 0)106 f.loc[zero_scale, "value"] = np.nan107 f["is_uncertain"] = f.is_uncertain.fillna(False).astype(bool)108 f["is_disputed"] = f.is_disputed.fillna(False).astype(bool)109 if write_diag:110 DIAG.mkdir(parents=True, exist_ok=True)111 f[f.unparsed][["sheet", "polity_id", "variable", "value_from"]].to_csv(DIAG / "seshat_unparsed_values.csv", index=False)112 f[zero_scale][["polity_id", "variable", "value_from", "value_to", "year_from"]].to_csv(DIAG / "seshat_zero_scale_values.csv", index=False)113 keep = ["sheet", "section", "seshat_region", "polity_number", "polity_id", "variable", "value", "value_lo", "value_hi",114 "text", "year_from", "year_to", "dated", "is_numeric", "inferred", "disputed", "unparsed", "is_uncertain", "is_disputed"]115 return f[keep]116117118def aggregate_polity(facts: pd.DataFrame, before_year: pd.Series | None = None) -> pd.DataFrame:119 """One row per polity x variable. If `before_year` (indexed by polity_id) is given, dated facts are kept only120 when year_to < before_year[polity]; undated facts are always kept (flagged via `n_undated`)."""121 f = facts[facts.value.notna()].copy()122 if before_year is not None:123 by = f.polity_id.map(before_year)124 f = f[(~f.dated) | (f.year_to < by)]125 f["logv"] = np.where(f.variable.isin(GEOMETRIC), np.log(f.value.where(f.value > 0)), np.nan)126 g = f.groupby(["polity_id", "variable"])127 out = g.agg(value_mean=("value", "mean"), value_min=("value", "min"), value_max=("value", "max"),128 logv_mean=("logv", "mean"), lo_min=("value_lo", "min"), hi_max=("value_hi", "max"),129 n_facts=("value", "size"), n_dated=("dated", "sum"), n_inferred=("inferred", "sum"),130 n_disputed=("disputed", "sum"), n_flag_uncertain=("is_uncertain", "sum")).reset_index()131 out["n_undated"] = out.n_facts - out.n_dated132 return out133134135def wide(agg: pd.DataFrame, stat: str = "value_mean") -> pd.DataFrame:136 return agg.pivot(index="polity_id", columns="variable", values=stat)137138139def crisis_table(sheets: dict[str, pd.DataFrame]) -> pd.DataFrame:140 cc = sheets["CrisisDB - Crisis consequences"].copy()141 cc["crisis_id"] = [f"{p}_{i}" for i, p in enumerate(cc.polity_id)]142 items = INSTABILITY_ITEMS + REFORM_ITEMS + ["epidemic"]143 for it in items:144 coded = cc[it].map(code_presence)145 cc[it + "_v"] = [c[0] for c in coded]146 inst = cc[[i + "_v" for i in INSTABILITY_ITEMS]]147 cc["y1_n_coded"] = inst.notna().sum(axis=1)148 cc["y1_n_present"] = (inst == 1).sum(axis=1)149 cc["y1_severity"] = np.where(cc.y1_n_coded > 0, cc.y1_n_present / cc.y1_n_coded, np.nan)150 sev = cc[[i + "_v" for i in SEVERE_ITEMS]]151 cc["y1b_severe"] = np.where(sev.notna().all(axis=1), (sev == 1).any(axis=1).astype(float), np.nan)152 ref = cc[[i + "_v" for i in REFORM_ITEMS]]153 cc["y_reform"] = np.where(ref.notna().sum(axis=1) > 0, (ref == 1).sum(axis=1) / ref.notna().sum(axis=1), np.nan)154 cc["epidemic_v"] = cc["epidemic_v"]155 cc["crisis_start"] = pd.to_numeric(cc.year_from, errors="coerce")156 cc["crisis_end"] = pd.to_numeric(cc.year_to, errors="coerce")157 keep = ["crisis_id", "polity_id", "seshat_region", "other_polity_id", "crisis_start", "crisis_end", "y1_n_coded",158 "y1_n_present", "y1_severity", "y1b_severe", "y_reform", "epidemic_v"] + [i + "_v" for i in INSTABILITY_ITEMS]159 return cc[keep]160161162def polity_metadata(sheets: dict[str, pd.DataFrame], facts: pd.DataFrame) -> pd.DataFrame:163 pol = sheets["Polities"].copy()164 cfg = yaml.safe_load((ROOT / "configs" / "regions.yaml").read_text())165 r2m = {r: m for m, rs in cfg["macro_regions"].items() for r in rs}166 pol["macro_region"] = pol.seshat_region.map(r2m)167 r2c = {r: m for m, rs in cfg["cultural_spheres"].items() for r in rs}168 pol["cultural_sphere"] = pol.seshat_region.map(r2c)169 pol["n_polities_in_nga"] = pol.groupby("homeNGA").polity_id.transform("count")170 pol["midpoint"] = (pol.start_year + pol.end_year) / 2171 def era(y):172 for b in cfg["era_blocks"]:173 if y >= b.get("min", -1e9) and y <= b.get("max", 1e9):174 return b["name"]175 pol["era"] = np.floor(pol.midpoint).map(era)176 pol["duration"] = pol.end_year - pol.start_year177 pol["censored"] = pol.end_year >= 1900178 gen = facts[facts.sheet == "General"]179 def first_text(var):180 s = gen[gen.variable == var].groupby("polity_id").text.first()181 return pol.polity_id.map(s)182 pol["centralization"] = first_text("polity_degree_of_centralization")183 pol["relationship_to_preceding"] = first_text("polity_relationship_to_preceding_entity")184 pol["preceding_entity"] = first_text("polity_preceding_entity")185 pol["succeeding_entity"] = first_text("polity_succeeding_entity")186 pol["suprapolity_relations"] = first_text("polity_suprapolity_relations")187 pol["utm_zone"] = first_text("polity_utm_zone")188 pol["language_family"] = first_text("polity_linguistic_family")189 # Y3: discontinuous succession = relationship_to_preceding of the successor polity (matched by name or id)190 name2id = {str(n).strip().lower(): i for n, i in zip(pol.long_name, pol.polity_id)}191 id_set = set(pol.polity_id)192 def succ_id(s):193 if s is None or str(s) == "nan" or str(s).strip() == "":194 return None195 key = str(s).strip()196 if key in id_set:197 return key198 return name2id.get(key.lower())199 pol["successor_id"] = pol.succeeding_entity.map(succ_id)200 # fallback: the polity that names this one as preceding entity201 prec_map = {}202 for pid, prec in zip(pol.polity_id, pol.preceding_entity):203 sid = succ_id(prec)204 if sid and sid not in prec_map:205 prec_map[sid] = pid206 pol["successor_id"] = pol.successor_id.where(pol.successor_id.notna(), pol.polity_id.map(prec_map))207 rel = dict(zip(pol.polity_id, pol.relationship_to_preceding))208 succ_rel = pol.successor_id.map(rel)209 cont = {"continuity", "continuation"}210 pol["successor_relationship"] = succ_rel211 pol["y3_discontinuous"] = np.where(succ_rel.isna() | succ_rel.isin(["SU", "suspected unknown", "nan", "None"]), np.nan,212 (~succ_rel.isin(cont)).astype(float))213 return pol214215216def build_all() -> dict[str, pd.DataFrame]:217 sheets = load_sheets()218 facts = build_facts(sheets)219 agg = aggregate_polity(facts)220 meta = polity_metadata(sheets, facts)221 crisis = crisis_table(sheets)222 # pre-crisis aggregates: one per crisis record223 pre = []224 for _, r in crisis.iterrows():225 sub = facts[facts.polity_id == r.polity_id]226 a = aggregate_polity(sub, before_year=pd.Series({r.polity_id: r.crisis_start}))227 a["crisis_id"] = r.crisis_id228 pre.append(a)229 pre_agg = pd.concat(pre, ignore_index=True) if pre else pd.DataFrame()230 return {"facts": facts, "polity_agg": agg, "polity_meta": meta, "crisis": crisis, "precrisis_agg": pre_agg}231