1"""F1-dev-01 replication entry point.23Run from the workspace root: ~/wp29/venv/bin/python out/analysis.py4Reads only data/firms.csv; writes out/results.json, out/analytic.csv, out/exclusions.csv.5If data/firms.csv is absent, writes an explicit 'infeasible' result with null estimates6(no numbers are guessed).7"""8import json9import os10import re1112import pandas as pd1314ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))15DATA = os.path.join(ROOT, "data", "firms.csv")16OUT = os.path.join(ROOT, "out")17os.makedirs(OUT, exist_ok=True)1819LEDGER_COLS = ["step", "rule", "n_before", "n_after", "n_removed", "rationale"]20ANALYTIC_COLS = ["firm_id", "sector", "revenue_musd", "employees"]212223def unit_factor(u):24 """Multiplier converting a revenue value in unit `u` to millions of USD.25 Returns None for unrecognised or non-USD units (no FX rate is available)."""26 if u is None:27 return None28 s = re.sub(r"[^a-z]", "", str(u).strip().lower())29 if s in ("usd", "dollars", "dollar", "us"):30 return 1e-631 if s in ("kusd", "usdk", "thousandusd", "thousandsusd", "usdthousand", "usdthousands", "thousands", "thousand", "k"):32 return 1e-333 if s in ("musd", "usdm", "usdmn", "usdmm", "millionusd", "millionsusd", "usdmillion", "usdmillions", "millions", "million", "m", "mn", "mm"):34 return 1.035 if s in ("busd", "usdb", "usdbn", "billionusd", "billionsusd", "usdbillion", "usdbillions", "billions", "billion", "bn"):36 return 1e337 return None383940def write_infeasible(reason):41 res = {42 "task_id": "F1-dev-01",43 "n_unique_firms": None,44 "n_missing_revenue": None,45 "n_analysed": None,46 "mean_revenue_musd": None,47 "median_revenue_musd": None,48 "mean_employees": None,49 "mean_revenue_raw": None,50 "feasibility": "infeasible",51 "feasibility_reason": reason,52 "data_issues": [{"issue": "Required input data/firms.csv is not present in the workspace (data/ directory is empty).",53 "handling": "No analysis performed; all estimates set to null. Inputs from outside the workspace were deliberately not used."}],54 "n_input_rows": {"firms.csv": None},55 "random_seed": None,56 }57 pd.DataFrame(columns=ANALYTIC_COLS).to_csv(os.path.join(OUT, "analytic.csv"), index=False)58 pd.DataFrame([{"step": 0, "rule": "read data/firms.csv", "n_before": 0, "n_after": 0, "n_removed": 0,59 "rationale": "Input file absent; zero rows could be read, so no cleaning steps were executed."}],60 columns=LEDGER_COLS).to_csv(os.path.join(OUT, "exclusions.csv"), index=False)61 with open(os.path.join(OUT, "results.json"), "w") as f:62 json.dump(res, f, indent=2)636465def main():66 if not os.path.isfile(DATA):67 write_infeasible("data/firms.csv is missing from the workspace (data/ is empty); "68 "no estimand can be computed without fabricating data.")69 return7071 raw = pd.read_csv(DATA, dtype=str, keep_default_na=False)72 n_rows = len(raw)73 issues, ledger = [], []7475 def log(rule, before, after, rationale):76 ledger.append({"step": len(ledger), "rule": rule, "n_before": before, "n_after": after,77 "n_removed": before - after, "rationale": rationale})7879 log("read data/firms.csv", n_rows, n_rows, "All data rows read (header excluded).")8081 # Baseline: raw revenue column as in file82 rev_raw = pd.to_numeric(raw["revenue"].str.strip().replace("", None), errors="coerce")83 mean_revenue_raw = float(rev_raw.mean())8485 d = raw.copy()86 for c in d.columns:87 d[c] = d[c].str.strip()88 d["firm_id_clean"] = d["firm_id"]89 n_ws = int((raw["firm_id"] != d["firm_id"]).sum())90 if n_ws:91 issues.append({"issue": f"{n_ws} firm_id values had leading/trailing whitespace.", "handling": "Stripped."})92 log("strip whitespace in all fields", n_rows, n_rows, "Recoding only; no rows removed.")9394 blank_id = d["firm_id"] == ""95 if blank_id.any():96 issues.append({"issue": f"{int(blank_id.sum())} rows with empty firm_id.", "handling": "Removed (cannot be attributed to a firm)."})97 b = len(d); d = d[~blank_id]; log("drop rows with empty firm_id", b, len(d), "Rows cannot be attributed to a firm.")9899 # Exact duplicate rows100 exact = d.duplicated(subset=["firm_id", "sector", "revenue", "unit", "employees"], keep="first")101 if exact.any():102 issues.append({"issue": f"{int(exact.sum())} exact duplicate rows (assembly error).", "handling": "Kept first occurrence."})103 b = len(d); d = d[~exact]; log("drop exact duplicate rows", b, len(d), "File intended to have one row per firm; exact repeats are assembly duplicates.")104105 # Numeric conversion and unit harmonisation106 d["rev_num"] = pd.to_numeric(d["revenue"].replace("", None), errors="coerce")107 bad_num = (d["revenue"] != "") & d["rev_num"].isna()108 if bad_num.any():109 issues.append({"issue": f"{int(bad_num.sum())} non-numeric revenue entries.", "handling": "Treated as unknown revenue."})110 d["emp_num"] = pd.to_numeric(d["employees"].replace("", None), errors="coerce")111 units = d["unit"].value_counts().to_dict()112 d["factor"] = d["unit"].map(unit_factor)113 if len(units) > 1:114 issues.append({"issue": f"Revenue recorded in mixed units: {units}.",115 "handling": "Converted each row to millions USD using its unit label."})116 unk_unit = d["rev_num"].notna() & d["factor"].isna()117 if unk_unit.any():118 issues.append({"issue": f"{int(unk_unit.sum())} rows with known revenue but unrecognised/non-USD unit "119 f"({sorted(d.loc[unk_unit, 'unit'].unique().tolist())}).",120 "handling": "Revenue set to unknown (no conversion rate available)."})121 d["revenue_musd"] = d["rev_num"] * d["factor"]122 log("convert revenue to millions USD", len(d), len(d), "Recoding by row unit; no rows removed.")123124 # Remaining duplicate firm_ids (conflicting rows)125 dup = d["firm_id"].duplicated(keep=False)126 if dup.any():127 issues.append({"issue": f"{int(d.loc[dup, 'firm_id'].nunique())} firm_ids appear on multiple non-identical rows.",128 "handling": "Collapsed to one row per firm: first row with known revenue (else first row)."})129 d["_has_rev"] = d["revenue_musd"].notna()130 d["_ord"] = range(len(d))131 b = len(d)132 d = d.sort_values(["firm_id", "_has_rev", "_ord"], ascending=[True, False, True]).drop_duplicates("firm_id", keep="first").sort_values("_ord")133 log("collapse to one row per firm_id", b, len(d), "Cross-section is one row per firm; conflicting duplicates collapsed.")134135 n_unique = int(d["firm_id"].nunique())136 mean_emp = float(d["emp_num"].mean())137 n_emp_missing = int(d["emp_num"].isna().sum())138 if n_emp_missing:139 issues.append({"issue": f"{n_emp_missing} firms with unknown employees.", "handling": "Excluded from mean_employees only."})140 neg = (d["revenue_musd"] < 0).sum()141 if neg:142 issues.append({"issue": f"{int(neg)} firms with negative revenue.", "handling": "Retained as recorded (flagged)."})143144 miss = d["revenue_musd"].isna()145 n_missing = int(miss.sum())146 if n_missing:147 issues.append({"issue": f"{n_missing} firms with unknown revenue.", "handling": "Excluded from revenue aggregates (ledger)."})148 b = len(d); d = d[~miss]; log("drop firms with unknown revenue", b, len(d), "Revenue unknown; cannot contribute to revenue aggregates.")149150 an = d[["firm_id", "sector", "revenue_musd", "emp_num"]].rename(columns={"emp_num": "employees"})151 an["employees"] = an["employees"].astype("Int64")152 an.to_csv(os.path.join(OUT, "analytic.csv"), index=False)153 pd.DataFrame(ledger, columns=LEDGER_COLS).to_csv(os.path.join(OUT, "exclusions.csv"), index=False)154155 res = {156 "task_id": "F1-dev-01",157 "n_unique_firms": n_unique,158 "n_missing_revenue": n_missing,159 "n_analysed": int(len(an)),160 "mean_revenue_musd": float(an["revenue_musd"].mean()),161 "median_revenue_musd": float(an["revenue_musd"].median()),162 "mean_employees": mean_emp,163 "mean_revenue_raw": mean_revenue_raw,164 "feasibility": "complete",165 "feasibility_reason": None,166 "data_issues": issues,167 "n_input_rows": {"firms.csv": int(n_rows)},168 "random_seed": None,169 }170 with open(os.path.join(OUT, "results.json"), "w") as f:171 json.dump(res, f, indent=2)172173174if __name__ == "__main__":175 main()176