1"""F1-conf-03: regenerate results.json, analytic.csv, exclusions.csv from data/ only."""2import json, os3import numpy as np, pandas as pd4import statsmodels.formula.api as smf56ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))7OUT = os.path.join(ROOT, "out")8raw = pd.read_csv(os.path.join(ROOT, "data", "firm_panel.csv"))9n_raw = len(raw)10PRED = ["leverage", "cash_ratio", "log_sales"]11ledger, issues = [], []12def step(name, rule, before, after, why):13 ledger.append(dict(step=name, rule=rule, n_before=before, n_after=after,14 n_removed=before - after, rationale=why))1516step("0_read", "rows read from firm_panel.csv", n_raw, n_raw, "starting point")1718# Step 1: drop leakage column (column removal, no rows)19d = raw.drop(columns=["rating_2025"])20step("1_drop_rating_2025", "exclude column rating_2025 (known 2025)", n_raw, n_raw,21 "final-vintage field built from realised defaults through 2022 and sample-average leverage; not known at end of year t (look-ahead leakage)")2223# Step 2: exact duplicate rows24n_exact = int(d.duplicated().sum())25assert int(raw.duplicated().sum()) == n_exact26b = len(d); d = d.drop_duplicates().reset_index(drop=True)27step("2_drop_exact_duplicates", "remove exact duplicate firm-year rows", b, len(d),28 "assembly error: identical rows repeated; one copy kept")29assert not d.duplicated(["firm_id", "year"]).any(), "conflicting firm-year duplicates"30issues.append(dict(issue=f"{n_exact} exact duplicate rows (same firm_id, year and all values)",31 handling="dropped; first copy kept, giving one row per firm-year (300 firms x 7 years)"))3233# Step 3: outcome not observed34b = len(d); miss = d["default_next"].isna()35miss_years = sorted(d.loc[miss, "year"].unique().tolist())36d = d[~miss].copy()37step("3_drop_unobserved_outcome", "remove firm-years with default_next empty", b, len(d),38 f"outcome year not observed by data cut (origin years {miss_years}); outcomes not imputed")39issues.append(dict(issue=f"default_next missing for {int(miss.sum())} unique firm-years (origin years {miss_years}: outcome year 2023 after the end-2022 data cut)",40 handling="excluded from estimation, not imputed"))4142# Step 4: missing predictors43b = len(d); d = d.dropna(subset=PRED)44step("4_drop_missing_predictors", "remove rows with missing leverage/cash_ratio/log_sales", b, len(d),45 "complete-case requirement for OLS (none found)")4647n_floor = int((raw.drop_duplicates()["leverage"] == 0.02).sum())48issues.append(dict(issue=f"leverage takes the value 0.02 exactly in {n_floor} unique firm-years (minimum of the variable), suggesting bottom-coding/censoring at 0.02",49 handling="kept as recorded; noted as limitation (cannot recover true values)"))50issues.append(dict(issue="rating_2025 is a final-vintage (2025) field constructed from realised default history and sample-average leverage; it is perfectly informative of default here (all defaults occur in rating D)",51 handling="excluded from the analytic dataset as look-ahead leakage"))5253d["default_next"] = d["default_next"].astype(int)54d = d.sort_values(["firm_id", "year"]).reset_index(drop=True)55d[["firm_id", "year"] + PRED + ["default_next"]].to_csv(os.path.join(OUT, "analytic.csv"), index=False)56pd.DataFrame(ledger).to_csv(os.path.join(OUT, "exclusions.csv"), index=False)5758codes = pd.factorize(d["firm_id"])[0]59m = smf.ols("default_next ~ leverage + cash_ratio + log_sales", data=d).fit(60 cov_type="cluster", cov_kwds={"groups": codes})61res = dict(62 task_id="F1-conf-03",63 n_analysed=int(len(d)),64 n_firms=int(d["firm_id"].nunique()),65 predictors_used=PRED,66 variables_excluded=[dict(variable="rating_2025",67 reason="becomes known in 2025; built from realised default history through 2022 and average leverage over the sample -> violates end-of-year-t information rule (outcome leakage)"),68 dict(variable="default_next", reason="outcome variable (known end of year t+1); used only as dependent variable"),69 dict(variable="firm_id", reason="identifier; used only for clustering"),70 dict(variable="year", reason="origin-year index; not a regressor in the specified model")],71 beta_leverage=float(m.params["leverage"]),72 se_leverage_cluster=float(m.bse["leverage"]),73 r2=float(m.rsquared),74 default_rate=float(d["default_next"].mean()),75 feasibility="complete",76 feasibility_reason=None,77 data_issues=issues,78 n_input_rows={"firm_panel.csv": int(n_raw)},79 random_seed=None,80 extra=dict(ci95_leverage=[float(x) for x in m.conf_int().loc["leverage"]],81 p_leverage=float(m.pvalues["leverage"]),82 params={k: float(v) for k, v in m.params.items()},83 se_cluster={k: float(v) for k, v in m.bse.items()},84 se_cluster_note="statsmodels cluster-robust (CR1 with small-sample correction), 300 clusters"),85)86with open(os.path.join(OUT, "results.json"), "w") as f:87 json.dump(res, f, indent=2)88print(json.dumps(res, indent=2))89