1"""F1-dev-01: build analytic firm dataset and revenue aggregates. Run from workspace root."""2import json, os3import numpy as np4import pandas as pd56SEED = 2026103078RAW = "data/firms.csv"9OUT = "out"10os.makedirs(OUT, exist_ok=True)1112raw = pd.read_csv(RAW, dtype=str, keep_default_na=False)13n_rows = len(raw)14for c in raw.columns:15 raw[c] = raw[c].str.strip()1617# Baseline: raw mean of revenue as in file (mixed units), non-empty rows, before cleaning18rev_raw = pd.to_numeric(raw["revenue"].replace("", None), errors="coerce")19mean_revenue_raw = float(rev_raw.mean())2021issues, ledger = [], []22def step(rule, before, after, rationale):23 ledger.append(dict(step=len(ledger) + 1, rule=rule, n_before=before, n_after=after,24 n_removed=before - after, rationale=rationale))2526step("read data/firms.csv", n_rows, n_rows, "All data rows read (header excluded).")2728# 1. duplicates29n_full_dup = int(raw.duplicated().sum())30n_id_dup = int(raw["firm_id"].duplicated().sum())31df = raw.drop_duplicates().copy()32if df["firm_id"].duplicated().any():33 raise RuntimeError("Conflicting duplicate firm_id rows found; resolve manually.")34step("drop exact duplicate rows (keep first occurrence)", n_rows, len(df),35 f"{n_full_dup} rows were exact copies of another row (same firm_id and all fields), an assembly error; "36 "one row per firm kept.")37if n_full_dup:38 issues.append({"issue": f"{n_full_dup} exact duplicate rows ({n_id_dup} repeated firm_id values; all duplicates identical in every field)",39 "handling": "Dropped duplicates, keeping one row per firm_id; no conflicting duplicates existed."})4041# 2. unit harmonisation42units = df["unit"].value_counts().to_dict()43factor = {"USD_millions": 1.0, "USD_thousands": 1e-3}44unknown = set(df["unit"]) - set(factor)45if unknown:46 raise RuntimeError(f"Unknown units: {unknown}")47df["revenue_num"] = pd.to_numeric(df["revenue"].replace("", None), errors="coerce")48df["revenue_musd"] = df["revenue_num"] * df["unit"].map(factor)49n_thou = int((df["unit"] == "USD_thousands").sum())50step("convert revenue to millions USD (USD_thousands / 1000)", len(df), len(df),51 f"Recoding only: {n_thou} firms reported revenue in USD_thousands; divided by 1000. No rows removed.")52issues.append({"issue": f"Mixed revenue units across rows (firm-level counts after de-duplication: {units})",53 "handling": "Converted all revenue to millions USD using the per-row unit column (thousands/1000)."})5455# employees over all distinct firms (before dropping missing revenue)56df["employees"] = pd.to_numeric(df["employees"], errors="raise").astype(int)57mean_employees = float(df["employees"].mean())58n_unique = int(df["firm_id"].nunique())5960# 3. missing revenue61miss = df["revenue_musd"].isna()62n_missing = int(df.loc[miss, "firm_id"].nunique())63before = len(df)64ana = df.loc[~miss].copy()65step("exclude firms with unknown (empty) revenue", before, len(ana),66 "Revenue unknown; cannot contribute to revenue aggregates. Firm IDs: " + ";".join(sorted(df.loc[miss, "firm_id"])))67issues.append({"issue": f"{n_missing} distinct firms have empty revenue",68 "handling": "Excluded from revenue aggregates (listed in exclusions.csv); retained in mean_employees, which is over all distinct firms."})6970ana = ana.sort_values("firm_id")[["firm_id", "sector", "revenue_musd", "employees"]]71ana.to_csv(f"{OUT}/analytic.csv", index=False)72pd.DataFrame(ledger).to_csv(f"{OUT}/exclusions.csv", index=False)7374# Uncertainty (descriptive; firms treated as a sample from a superpopulation)75r = ana["revenue_musd"].to_numpy()76se_mean = float(r.std(ddof=1) / np.sqrt(len(r)))77rng = np.random.default_rng(SEED)78bs = rng.choice(r, size=(5000, len(r)), replace=True)79ci_mean = np.percentile(bs.mean(axis=1), [2.5, 97.5])80ci_med = np.percentile(np.median(bs, axis=1), [2.5, 97.5])8182res = {83 "task_id": "F1-dev-01",84 "n_unique_firms": n_unique,85 "n_missing_revenue": n_missing,86 "n_analysed": int(len(ana)),87 "mean_revenue_musd": round(float(ana["revenue_musd"].mean()), 6),88 "median_revenue_musd": round(float(ana["revenue_musd"].median()), 6),89 "mean_employees": round(mean_employees, 6),90 "mean_revenue_raw": round(mean_revenue_raw, 6),91 "feasibility": "complete",92 "feasibility_reason": None,93 "data_issues": issues,94 "n_input_rows": {"firms.csv": n_rows},95 "random_seed": SEED,96 "se_mean_revenue_musd": round(se_mean, 6),97 "bootstrap_ci95_mean_revenue_musd": [round(float(x), 6) for x in ci_mean],98 "bootstrap_ci95_median_revenue_musd": [round(float(x), 6) for x in ci_med],99}100with open(f"{OUT}/results.json", "w") as f:101 json.dump(res, f, indent=2)102print(json.dumps(res, indent=2))103