1"""F1-dev-01: analytic dataset and revenue aggregates. Run from workspace root."""2import json, os3import numpy as np4import pandas as pd56DATA = "data/firms.csv"7OUT = "out"8os.makedirs(OUT, exist_ok=True)910raw = pd.read_csv(DATA, dtype={"firm_id": str, "sector": str, "unit": str})11n_rows = len(raw)1213# Baseline: raw revenue column mean, no cleaning14mean_revenue_raw = float(raw["revenue"].dropna().mean())1516ledger, issues = [], []17def step(name, rule, before, after, why):18 ledger.append(dict(step=name, rule=rule, n_before=before, n_after=after,19 n_removed=before - after, rationale=why))2021# Step 1: exact duplicate rows22n_dup = int(raw.duplicated().sum())23d = raw.drop_duplicates().reset_index(drop=True)24step("1_drop_exact_duplicates", "drop rows identical to an earlier row on all columns",25 n_rows, len(d), "assembly error: same firm record appended more than once")26if n_dup:27 issues.append({"issue": f"{n_dup} rows are exact duplicates of another row (file assembled from several extracts)",28 "handling": "kept first occurrence; one row per firm_id"})29# Check: no conflicting duplicates remain30n_conflict = int(d["firm_id"].duplicated().sum())31assert n_conflict == 0, "conflicting duplicate firm records"32n_unique_firms = int(raw["firm_id"].nunique())33assert len(d) == n_unique_firms3435# Step 2: unit harmonisation36factor = {"USD_millions": 1.0, "USD_thousands": 1e-3}37unknown_units = set(d["unit"].unique()) - set(factor)38assert not unknown_units, f"unknown units {unknown_units}"39d["revenue_musd"] = d["revenue"] * d["unit"].map(factor)40n_thou = int((d["unit"] == "USD_thousands").sum())41step("2_convert_units", "revenue_musd = revenue/1000 if unit==USD_thousands else revenue",42 len(d), len(d), f"recoding only; {n_thou} firms reported in USD thousands")43issues.append({"issue": f"mixed revenue units: {n_thou} distinct firms in USD_thousands, rest in USD_millions",44 "handling": "converted all revenue to millions USD (thousands / 1000)"})4546# mean employees over all distinct firms (before revenue exclusion)47mean_employees = float(d["employees"].mean())4849# Step 3: missing revenue50miss = d["revenue_musd"].isna()51n_missing = int(miss.sum())52a = d.loc[~miss].copy()53step("3_drop_missing_revenue", "drop firms with empty revenue",54 len(d), len(a), "revenue unknown; cannot enter revenue aggregates (still counted in mean_employees)")55if n_missing:56 issues.append({"issue": f"{n_missing} distinct firms have empty (unknown) revenue: "57 + ", ".join(sorted(d.loc[miss, 'firm_id'])),58 "handling": "excluded from revenue aggregates and analytic.csv; included in n_unique_firms and mean_employees"})5960a = a.sort_values("firm_id")[["firm_id", "sector", "revenue_musd", "employees"]]61a.to_csv(f"{OUT}/analytic.csv", index=False)62pd.DataFrame(ledger).to_csv(f"{OUT}/exclusions.csv", index=False)6364res = {65 "task_id": "F1-dev-01",66 "n_unique_firms": n_unique_firms,67 "n_missing_revenue": n_missing,68 "n_analysed": int(len(a)),69 "mean_revenue_musd": round(float(a["revenue_musd"].mean()), 6),70 "median_revenue_musd": round(float(a["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 # supplementary (not required): descriptive uncertainty for the mean79 "sd_revenue_musd": round(float(a["revenue_musd"].std(ddof=1)), 6),80 "se_mean_revenue_musd": round(float(a["revenue_musd"].std(ddof=1) / np.sqrt(len(a))), 6),81}82with open(f"{OUT}/results.json", "w") as f:83 json.dump(res, f, indent=2)84print(json.dumps({k: res[k] for k in ["n_unique_firms", "n_missing_revenue", "n_analysed",85 "mean_revenue_musd", "median_revenue_musd", "mean_employees", "mean_revenue_raw"]}, indent=1))86