1"""F1-dev-01: build analytic dataset and revenue aggregates from data/firms.csv.2Run from workspace root: ~/wp29/venv/bin/python out/analysis.py"""3import json4import pandas as pd56RAW = "data/firms.csv"7UNIT_TO_MUSD = {"USD_millions": 1.0, "USD_thousands": 1e-3}89raw = pd.read_csv(RAW, dtype=str, keep_default_na=False)10n_rows = len(raw)11raw_rev = pd.to_numeric(raw["revenue"].replace("", None), errors="coerce")12mean_revenue_raw = float(raw_rev.dropna().mean()) # baseline, mixed units1314issues, ledger = [], []15def log(step, rule, before, after, rationale):16 ledger.append(dict(step=step, rule=rule, n_before=before, n_after=after,17 n_removed=before - after, rationale=rationale))1819# sanity checks20for c in raw.columns:21 raw[c] = raw[c].str.strip()22assert set(raw["unit"]) <= set(UNIT_TO_MUSD), set(raw["unit"])23assert raw["employees"].ne("").all()24bad_rev = raw["revenue"].ne("") & raw_rev.isna()25assert not bad_rev.any()2627# Step 1: exact duplicates28n_exact_dup = int(raw.duplicated().sum())29df = raw.drop_duplicates().reset_index(drop=True)30log(1, "drop exact duplicate rows (all columns identical)", n_rows, len(df),31 "file assembled from overlapping extracts; identical rows are the same firm repeated")32if n_exact_dup:33 issues.append(dict(issue=f"{n_exact_dup} exact duplicate rows (same firm_id and identical values in all columns)",34 handling="kept the first occurrence; dropped the rest (ledger step 1)"))35# check no conflicting duplicates remain36n_conflict = int(df["firm_id"].duplicated().sum())37assert n_conflict == 0, "conflicting duplicate firm_ids present"38log(2, "check for remaining duplicate firm_id with conflicting values", len(df), len(df),39 "none found; one row per firm")4041# Step 3: unit harmonisation42n_thous = int((df["unit"] == "USD_thousands").sum())43df["revenue_num"] = pd.to_numeric(df["revenue"].replace("", None), errors="coerce")44df["revenue_musd"] = df["revenue_num"] * df["unit"].map(UNIT_TO_MUSD)45df["employees"] = df["employees"].astype(int)46log(3, "convert revenue to millions USD (USD_thousands / 1000)", len(df), len(df),47 f"{n_thous} unique firms reported revenue in thousands of USD; recoded, no rows removed")48if n_thous:49 issues.append(dict(issue=f"mixed revenue units: {n_thous} unique firms in USD_thousands, rest in USD_millions",50 handling="converted all to millions USD by dividing USD_thousands values by 1000 (ledger step 3); converted distributions are comparable"))5152firms_all = df.copy()53n_unique = int(firms_all["firm_id"].nunique())54mean_emp = float(firms_all["employees"].mean())5556# Step 4: missing revenue57miss = firms_all["revenue_musd"].isna()58n_missing = int(miss.sum())59missing_ids = sorted(firms_all.loc[miss, "firm_id"])60an = firms_all.loc[~miss].copy()61log(4, "exclude firms with unknown (empty) revenue", len(firms_all), len(an),62 "revenue unknown; cannot contribute to revenue aggregates. Firms: " + ";".join(missing_ids))63if n_missing:64 issues.append(dict(issue=f"{n_missing} unique firms with empty revenue ({', '.join(missing_ids)})",65 handling="excluded from revenue aggregates and analytic.csv (ledger step 4); retained in n_unique_firms and mean_employees"))6667an = an.sort_values("firm_id")[["firm_id", "sector", "revenue_musd", "employees"]]68an.to_csv("out/analytic.csv", index=False)69pd.DataFrame(ledger).to_csv("out/exclusions.csv", index=False)7071res = dict(72 task_id="F1-dev-01",73 n_unique_firms=n_unique,74 n_missing_revenue=n_missing,75 n_analysed=int(len(an)),76 mean_revenue_musd=float(an["revenue_musd"].mean()),77 median_revenue_musd=float(an["revenue_musd"].median()),78 mean_employees=mean_emp,79 mean_revenue_raw=mean_revenue_raw,80 feasibility="complete",81 feasibility_reason=None,82 data_issues=issues,83 n_input_rows={"firms.csv": n_rows},84 random_seed=None,85)86with open("out/results.json", "w") as f:87 json.dump(res, f, indent=2)88print(json.dumps(res, indent=2))8990# Supplementary descriptive statistics for the report (not required keys)91r = an["revenue_musd"]92extra = dict(sd_revenue_musd=float(r.std(ddof=1)),93 se_mean_revenue_musd=float(r.std(ddof=1) / len(r) ** 0.5),94 min_revenue_musd=float(r.min()), max_revenue_musd=float(r.max()),95 mean_employees_analysed=float(an["employees"].mean()),96 mean_revenue_if_units_ignored=float(firms_all["revenue_num"].mean()))97with open("out/extra_stats.json", "w") as f:98 json.dump(extra, f, indent=2)99print(json.dumps(extra, indent=2))100