1"""F1-dev-01: build analytic firm dataset and revenue aggregates. Run from workspace root."""2import json, os3import numpy as np, pandas as pd45DATA = "data/firms.csv"6OUT = "out"7os.makedirs(OUT, exist_ok=True)8UNIT_FACTOR = {"USD_millions": 1.0, "USD_thousands": 1e-3}910raw = pd.read_csv(DATA, dtype={"firm_id": str, "sector": str, "unit": str})11n_rows = len(raw)12issues, ledger = [], []1314def log(step, rule, nb, na, why):15 ledger.append(dict(step=step, rule=rule, n_before=nb, n_after=na, n_removed=nb - na, rationale=why))1617# Baseline: raw revenue mean, no cleaning18mean_revenue_raw = float(raw["revenue"].dropna().mean())1920# Step 1: exact duplicate rows21n_dup_id = int(raw["firm_id"].duplicated().sum())22d = raw.drop_duplicates().reset_index(drop=True)23log(1, "drop exact duplicate rows (all columns identical)", n_rows, len(d),24 "file assembled from several extracts; one intended row per firm")25if n_dup_id:26 issues.append({"issue": f"{n_dup_id} rows repeat an earlier firm_id; all are exact full-row duplicates",27 "handling": "kept first occurrence of each firm (drop_duplicates); no conflicting values"})28if d["firm_id"].duplicated().any():29 raise SystemExit("conflicting duplicate firm records remain; no resolution rule")30n_unique = int(raw["firm_id"].nunique())31assert n_unique == len(d)3233# Step 2: unit recoding34unknown_units = set(d["unit"].unique()) - set(UNIT_FACTOR)35if unknown_units:36 raise SystemExit(f"unknown units {unknown_units}")37d["revenue_musd"] = d["revenue"] * d["unit"].map(UNIT_FACTOR)38n_thou = int((d["unit"] == "USD_thousands").sum())39log(2, "convert revenue to millions USD (USD_thousands / 1000)", len(d), len(d),40 "mixed currency units across rows; recoding only, no rows removed")41issues.append({"issue": f"mixed revenue units: {n_thou} distinct firms in USD_thousands, rest in USD_millions",42 "handling": "revenue_musd = revenue/1000 for USD_thousands rows; unchanged for USD_millions"})4344mean_employees = float(d["employees"].mean())4546# Step 3: missing revenue47miss = d["revenue_musd"].isna()48n_missing = int(miss.sum())49a = d.loc[~miss, ["firm_id", "sector", "revenue_musd", "employees"]].sort_values("firm_id").reset_index(drop=True)50log(3, "exclude firms with empty revenue from revenue aggregates", len(d), len(a),51 "revenue unknown; firms: " + " ".join(sorted(d.loc[miss, "firm_id"])))52if n_missing:53 issues.append({"issue": f"{n_missing} distinct firms with empty (unknown) revenue",54 "handling": "excluded from revenue aggregates (exclusions.csv step 3); kept in n_unique_firms and mean_employees"})5556res = {57 "task_id": "F1-dev-01",58 "n_unique_firms": n_unique,59 "n_missing_revenue": n_missing,60 "n_analysed": int(len(a)),61 "mean_revenue_musd": float(a["revenue_musd"].mean()),62 "median_revenue_musd": float(a["revenue_musd"].median()),63 "mean_employees": mean_employees,64 "sd_revenue_musd": float(a["revenue_musd"].std(ddof=1)),65 "se_mean_revenue_musd": float(a["revenue_musd"].std(ddof=1) / np.sqrt(len(a))),66 "mean_revenue_raw": mean_revenue_raw,67 "feasibility": "complete",68 "feasibility_reason": None,69 "data_issues": issues,70 "n_input_rows": {"firms.csv": int(n_rows)},71 "random_seed": None,72}73a.to_csv(f"{OUT}/analytic.csv", index=False)74pd.DataFrame(ledger).to_csv(f"{OUT}/exclusions.csv", index=False)75with open(f"{OUT}/results.json", "w") as f:76 json.dump(res, f, indent=2)77print(json.dumps({k: v for k, v in res.items() if k != "data_issues"}, indent=2))78