1"""F1-dev-01: build the analytic firm dataset and revenue aggregates.2Run from workspace root: ~/wp29/venv/bin/python out/analysis.py3Reads only data/firms.csv; deterministic (no randomness)."""4import json, hashlib5from pathlib import Path6import pandas as pd78ROOT = Path(__file__).resolve().parent.parent9RAW = ROOT / "data" / "firms.csv"10OUT = ROOT / "out"11UNIT_TO_MUSD = {"USD_millions": 1.0, "USD_thousands": 1e-3}1213raw = pd.read_csv(RAW, dtype={"firm_id": str, "sector": str, "unit": str})14n_rows = len(raw)15mean_revenue_raw = float(raw["revenue"].mean()) # baseline: non-empty values as read, no cleaning1617issues, ledger = [], []18def log(step, rule, nb, na, why):19 ledger.append(dict(step=step, rule=rule, n_before=nb, n_after=na, n_removed=nb - na, rationale=why))2021# Step 1: exact duplicate rows22n_dup_id = int(raw["firm_id"].duplicated().sum())23d = raw.drop_duplicates().reset_index(drop=True)24n_exact = n_rows - len(d)25log(1, "drop exact duplicate rows (all columns identical)", n_rows, len(d),26 "file assembled from several extracts; identical repeated rows are assembly errors")27issues.append({"issue": f"{n_exact} exact duplicate rows ({n_dup_id} repeated firm_id rows in total)",28 "handling": "kept first occurrence of each identical row"})29# Any remaining conflicting duplicate firm_ids would make the firm ambiguous30conflict = d["firm_id"].duplicated(keep=False)31if conflict.any():32 raise SystemExit(f"conflicting duplicates for {d.loc[conflict,'firm_id'].nunique()} firms; resolve manually")33n_unique = int(d["firm_id"].nunique())34assert n_unique == raw["firm_id"].nunique() == len(d)3536# Step 2: unit harmonisation (no rows removed)37unknown_units = set(d["unit"].unique()) - set(UNIT_TO_MUSD)38if unknown_units:39 raise SystemExit(f"unknown units {unknown_units}")40n_thousands = int((d["unit"] == "USD_thousands").sum())41d["revenue_musd"] = d["revenue"] * d["unit"].map(UNIT_TO_MUSD)42log(2, "convert revenue to millions USD (USD_thousands /1000; USD_millions unchanged)", len(d), len(d),43 "mixed currency units across extracts; outputs must be in millions USD")44issues.append({"issue": f"mixed revenue units: {n_thousands} distinct firms report revenue in USD_thousands, rest in USD_millions",45 "handling": "converted all revenue to millions USD using the per-row unit column"})4647# Employees over all distinct firms (before dropping missing revenue)48mean_employees = float(d["employees"].mean())4950# Step 3: drop firms with unknown revenue51miss = d["revenue_musd"].isna()52n_missing = int(miss.sum())53missing_ids = sorted(d.loc[miss, "firm_id"])54a = d.loc[~miss].copy()55log(3, "drop firms with empty revenue: " + ";".join(missing_ids), len(d), len(a),56 "revenue unknown; cannot contribute to revenue aggregates (still counted in mean_employees)")57issues.append({"issue": f"{n_missing} distinct firms with empty revenue ({int(raw['revenue'].isna().sum())} raw rows incl. duplicates)",58 "handling": "excluded from revenue aggregates and analytic.csv; retained for n_unique_firms and mean_employees"})5960analytic = a[["firm_id", "sector", "revenue_musd", "employees"]].sort_values("firm_id").reset_index(drop=True)61analytic.to_csv(OUT / "analytic.csv", index=False)62pd.DataFrame(ledger).to_csv(OUT / "exclusions.csv", index=False)6364res = {65 "task_id": "F1-dev-01",66 "n_unique_firms": n_unique,67 "n_missing_revenue": n_missing,68 "n_analysed": int(len(analytic)),69 "mean_revenue_musd": round(float(analytic["revenue_musd"].mean()), 6),70 "median_revenue_musd": round(float(analytic["revenue_musd"].median()), 6),71 "mean_employees": round(mean_employees, 6),72 "mean_revenue_raw": round(mean_revenue_raw, 6),73 "feasibility": "complete",74 "feasibility_reason": None,75 "data_issues": issues,76 "n_input_rows": {"firms.csv": int(n_rows)},77 "random_seed": None,78 "input_sha256": {"firms.csv": hashlib.sha256(RAW.read_bytes()).hexdigest()},79 "sd_revenue_musd": round(float(analytic["revenue_musd"].std(ddof=1)), 6),80 "se_mean_revenue_musd": round(float(analytic["revenue_musd"].std(ddof=1) / len(analytic) ** 0.5), 6),81 "n_thousands_unit_firms": n_thousands,82 "n_exact_duplicate_rows": int(n_exact),83}84(OUT / "results.json").write_text(json.dumps(res, indent=2) + "\n")85print(json.dumps({k: v for k, v in res.items() if k != "data_issues"}, indent=2))86