1"""F1-conf-03: information-respecting analytic dataset and LPM of next-year default.2Run from workspace root: ~/wp29/venv/bin/python out/analysis.py3Reads only data/firm_panel.csv; writes out/results.json, out/analytic.csv, out/exclusions.csv.4Deterministic; no randomness used (random_seed = null)."""5import json, hashlib6from pathlib import Path7import numpy as np8import pandas as pd9import statsmodels.api as sm1011DATA = Path("data/firm_panel.csv")12OUT = Path("out"); OUT.mkdir(exist_ok=True)13PREDICTORS = ["leverage", "cash_ratio", "log_sales"]1415raw = pd.read_csv(DATA, dtype={"firm_id": str})16n_raw = len(raw)17ledger, issues = [], []18def step(name, rule, before, after, why):19 ledger.append(dict(step=name, rule=rule, n_before=before, n_after=after,20 n_removed=before - after, rationale=why))2122step("0_read", "all data rows of data/firm_panel.csv", n_raw, n_raw, "rows read (header excluded)")2324# Column exclusion: rating_2025 known only in 2025 and built from realised defaults (leakage)25excluded = [{"variable": "rating_2025",26 "reason": "Final-vintage field assigned in 2025 using the firm's complete realised default "27 "history through 2022 and sample-average leverage; not known at end of origin year t "28 "(look-ahead / outcome leakage). Dropped from the analytic dataset."}]29df = raw.drop(columns=["rating_2025"])30step("1_drop_column_rating_2025", "drop columns not known at end of year t", n_raw, len(df),31 "rating_2025 becomes known in 2025 and encodes the outcome; column removal, no rows removed")32issues.append({"issue": "rating_2025 is a final-vintage (2025) field derived from realised defaults through 2022 "33 "(in the raw file only D-rated firms ever default) - look-ahead leakage",34 "handling": "excluded from predictors and from out/analytic.csv"})3536# Exact duplicate rows37n_dup = int(raw.duplicated().sum())38n_keydup = int(raw.duplicated(["firm_id", "year"]).sum())39b = len(df); df = df.drop_duplicates(); 40assert df.duplicated(["firm_id", "year"]).sum() == 041step("2_drop_exact_duplicates", "remove exact duplicate rows (identical on all columns, incl. rating_2025)",42 b, len(df), "assembly error: firm-years appearing twice with identical values")43issues.append({"issue": f"{n_dup} exact duplicate rows ({n_keydup} duplicated firm_id-year keys; all duplicates identical "44 f"on every column); file has {n_raw} rows vs 300 firms x 7 years = 2100 intended",45 "handling": "kept the first occurrence of each duplicated row; after de-duplication every firm has exactly 7 years"})4647# Missing outcome48n_miss = int(df["default_next"].isna().sum())49miss_years = sorted(df.loc[df["default_next"].isna(), "year"].unique().tolist())50b = len(df); df = df[df["default_next"].notna()].copy()51step("3_drop_unobserved_outcome", "remove firm-years with default_next missing (not imputed)",52 b, len(df), f"outcome year not observed at data cut (end 2022); missing origin years: {miss_years}")53issues.append({"issue": f"{n_miss} unique firm-years have unobserved default_next (origin years {miss_years}: outcome year 2023 after the data cut)",54 "handling": "excluded from estimation; outcomes not imputed"})5556# Missing predictors (none expected, but check)57b = len(df); df = df.dropna(subset=PREDICTORS)58step("4_drop_missing_predictors", "remove rows with any missing predictor", b, len(df), "complete-case on predictors")5960df["default_next"] = df["default_next"].astype(int)61df["year"] = df["year"].astype(int)62df = df.sort_values(["firm_id", "year"]).reset_index(drop=True)6364# Range sanity65lev_floor = int((df["leverage"] == df["leverage"].min()).sum())66if lev_floor > 1:67 issues.append({"issue": f"leverage has a mass point of {lev_floor} analysed rows at its minimum {df['leverage'].min():.2f} "68 "(apparent floor/censoring in the simulated data)",69 "handling": "kept as recorded; noted as limitation (values are within the valid [0,1] range)"})7071X = sm.add_constant(df[PREDICTORS])72y = df["default_next"]73fit = sm.OLS(y, X).fit(cov_type="cluster", cov_kwds={"groups": pd.factorize(df["firm_id"])[0]})7475analytic = df[["firm_id", "year"] + PREDICTORS + ["default_next"]]76analytic.to_csv(OUT / "analytic.csv", index=False)77pd.DataFrame(ledger).to_csv(OUT / "exclusions.csv", index=False)7879res = {80 "task_id": "F1-conf-03",81 "n_analysed": int(len(df)),82 "n_firms": int(df["firm_id"].nunique()),83 "predictors_used": PREDICTORS,84 "variables_excluded": excluded,85 "beta_leverage": float(fit.params["leverage"]),86 "se_leverage_cluster": float(fit.bse["leverage"]),87 "r2": float(fit.rsquared),88 "default_rate": float(y.mean()),89 "feasibility": "complete",90 "feasibility_reason": None,91 "data_issues": issues,92 "n_input_rows": {"firm_panel.csv": int(n_raw)},93 "random_seed": None,94 # supplementary (not required) keys95 "ci95_leverage_cluster": [float(v) for v in fit.conf_int().loc["leverage"]],96 "coef_all": {k: float(v) for k, v in fit.params.items()},97 "se_cluster_all": {k: float(v) for k, v in fit.bse.items()},98 "baseline_beta_leverage_bivariate": float(sm.OLS(y, sm.add_constant(df[["leverage"]])).fit().params["leverage"]),99 "se_leverage_nonrobust": float(sm.OLS(y, X).fit().bse["leverage"]),100 "input_sha256": hashlib.sha256(DATA.read_bytes()).hexdigest(),101}102(OUT / "results.json").write_text(json.dumps(res, indent=2))103print(json.dumps({k: res[k] for k in ["n_analysed", "n_firms", "beta_leverage", "se_leverage_cluster", "r2", "default_rate"]}, indent=2))104