1"""F1-dev-01: build analytic firm dataset and revenue aggregates.2Run from workspace root: ~/wp29/venv/bin/python out/analysis.py3Reads only data/firms.csv; writes out/results.json, out/analytic.csv, out/exclusions.csv.4No randomness is used (random_seed = null)."""5import json, os6import numpy as np7import pandas as pd89RAW = "data/firms.csv"10OUT = "out"11UNIT_TO_MUSD = {"USD_millions": 1.0, "USD_thousands": 1e-3}1213raw = pd.read_csv(RAW, dtype=str, keep_default_na=False)14n_rows = len(raw)1516# Baseline: raw revenue column mean over non-empty cells, no cleaning, units as in file17raw_rev = pd.to_numeric(raw["revenue"].replace("", np.nan), errors="raise")18mean_revenue_raw = float(raw_rev.mean())1920issues, ledger = [], []2122def log_step(step, rule, before, after, rationale):23 ledger.append(dict(step=step, rule=rule, n_before=before, n_after=after,24 n_removed=before - after, rationale=rationale))2526# Sanity checks on the raw file27for c in raw.columns:28 assert (raw[c].str.strip() == raw[c]).all(), c29unknown_units = set(raw["unit"]) - set(UNIT_TO_MUSD)30assert not unknown_units, unknown_units31assert (raw["employees"] != "").all()3233# Step 1: exact duplicate rows34df = raw.copy()35n_exact_dup = int(df.duplicated().sum())36df = df.drop_duplicates(keep="first").reset_index(drop=True)37log_step(1, "drop exact duplicate rows (all 5 columns identical); keep first occurrence",38 n_rows, len(df), "file assembled from several extracts; repeated identical records of the same firm")39if n_exact_dup:40 issues.append({"issue": f"{n_exact_dup} exact duplicate rows ({n_exact_dup} firms appear twice with identical values in all columns)",41 "handling": "dropped the repeated rows, kept one row per firm (exclusion step 1)"})4243# No conflicting duplicates may remain44assert int(df["firm_id"].duplicated().sum()) == 0, "conflicting duplicate firm_id records present"45n_unique_firms = int(raw["firm_id"].nunique())46assert len(df) == n_unique_firms4748# Step 2: unit harmonisation to millions USD (no rows removed)49df["revenue_num"] = pd.to_numeric(df["revenue"].replace("", np.nan), errors="raise")50n_thousands = int((df["unit"] == "USD_thousands").sum())51df["revenue_musd"] = df["revenue_num"] * df["unit"].map(UNIT_TO_MUSD)52log_step(2, "recode revenue to millions USD: USD_thousands values divided by 1000; USD_millions unchanged",53 len(df), len(df), "revenue reported in mixed units; all outputs must be in millions USD (recode only)")54issues.append({"issue": f"mixed revenue units: {n_thousands} distinct firms report revenue in USD_thousands, the others in USD_millions",55 "handling": "converted to millions USD by dividing USD_thousands values by 1000 (exclusion step 2, recode, no rows removed)"})5657# Employees as integer, all firms58df["employees"] = pd.to_numeric(df["employees"], errors="raise").astype(int)59assert (df["employees"] > 0).all()60mean_employees = float(df["employees"].mean())6162# Step 3: missing revenue63miss = df["revenue_musd"].isna()64n_missing_revenue = int(miss.sum())65missing_ids = sorted(df.loc[miss, "firm_id"])66before = len(df)67ana = df.loc[~miss].copy()68log_step(3, "drop firms with empty (unknown) revenue: " + ";".join(missing_ids),69 before, len(ana), "revenue unknown; cannot contribute to revenue aggregates (task rule)")70if n_missing_revenue:71 issues.append({"issue": f"{n_missing_revenue} distinct firms with unknown (empty) revenue ({int(raw['revenue'].eq('').sum())} raw rows including duplicates): " + ", ".join(missing_ids),72 "handling": "excluded from revenue aggregates and analytic.csv (exclusion step 3); kept for n_unique_firms and mean_employees"})7374ana = ana.sort_values("firm_id")[["firm_id", "sector", "revenue_musd", "employees"]].reset_index(drop=True)75n_analysed = len(ana)7677os.makedirs(OUT, exist_ok=True)78ana.to_csv(os.path.join(OUT, "analytic.csv"), index=False, float_format="%.6f")79pd.DataFrame(ledger).to_csv(os.path.join(OUT, "exclusions.csv"), index=False)8081sd = float(ana["revenue_musd"].std(ddof=1))82results = {83 "task_id": "F1-dev-01",84 "n_unique_firms": n_unique_firms,85 "n_missing_revenue": n_missing_revenue,86 "n_analysed": int(n_analysed),87 "mean_revenue_musd": float(ana["revenue_musd"].mean()),88 "median_revenue_musd": float(ana["revenue_musd"].median()),89 "mean_employees": mean_employees,90 "mean_revenue_raw": mean_revenue_raw,91 "feasibility": "complete",92 "feasibility_reason": None,93 "data_issues": issues,94 "n_input_rows": {"firms.csv": int(n_rows)},95 "random_seed": None,96 # supplementary descriptive keys97 "sd_revenue_musd": sd,98 "se_mean_revenue_musd": sd / float(np.sqrt(n_analysed)),99 "n_exact_duplicate_rows": n_exact_dup,100 "n_firms_usd_thousands": n_thousands,101 "mean_employees_analysed": float(ana["employees"].mean()),102}103with open(os.path.join(OUT, "results.json"), "w") as f:104 json.dump(results, f, indent=2)105print(json.dumps({k: v for k, v in results.items() if k != "data_issues"}, indent=2))106