1"""F1-dev-01: analytic firm dataset and revenue aggregates. Reads only data/firms.csv."""2import json, hashlib, os3import pandas as pd45ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))6DATA = os.path.join(ROOT, "data", "firms.csv")7OUT = os.path.join(ROOT, "out")8UNIT_FACTOR = {"USD_millions": 1.0, "USD_thousands": 1e-3}910raw = pd.read_csv(DATA, dtype=str, keep_default_na=False)11n_rows = len(raw)12for c in raw.columns:13 raw[c] = raw[c].str.strip()14raw["revenue_num"] = pd.to_numeric(raw["revenue"].replace("", None), errors="coerce")15raw["employees"] = pd.to_numeric(raw["employees"], errors="raise").astype(int)1617# Baseline: raw mean of revenue column as read (mixed units, duplicates included)18mean_revenue_raw = float(raw["revenue_num"].dropna().mean())1920ledger, issues = [], []21def step(name, rule, before, after, why):22 ledger.append(dict(step=name, rule=rule, n_before=before, n_after=after,23 n_removed=before - after, rationale=why))2425step("0_read", "read all data rows of data/firms.csv", n_rows, n_rows, "starting row count")2627# 1. exact duplicate rows28cols = ["firm_id", "sector", "revenue", "unit", "employees"]29n_exact_dup = int(raw.duplicated(subset=cols).sum())30d = raw.drop_duplicates(subset=cols, keep="first").copy()31step("1_dedup_exact", "drop rows identical on all columns to an earlier row (keep first)",32 n_rows, len(d), "assembly error: same firm appended more than once from overlapping extracts")33# any remaining conflicting duplicate firm_ids?34n_conflict = int(d["firm_id"].duplicated().sum())35if n_conflict:36 raise RuntimeError(f"{n_conflict} conflicting duplicate firm_id rows; handling not implemented")37issues.append({"issue": f"{n_exact_dup} exact duplicate rows (same firm_id and identical values); 0 conflicting duplicates",38 "handling": "kept first occurrence; one row per firm_id"})39firms = d # one row per distinct firm40n_unique = int(firms["firm_id"].nunique())41mean_employees = float(firms["employees"].mean())4243# 2. unit harmonisation44unknown_units = set(firms["unit"]) - set(UNIT_FACTOR)45if unknown_units:46 raise RuntimeError(f"unknown units {unknown_units}")47n_thou = int((firms["unit"] == "USD_thousands").sum())48firms["revenue_musd"] = firms["revenue_num"] * firms["unit"].map(UNIT_FACTOR)49step("2_unit_convert", "convert revenue to millions USD (USD_thousands / 1000; USD_millions unchanged)",50 len(firms), len(firms), f"mixed units across extracts; {n_thou} distinct firms recorded in USD_thousands (transformation, no rows removed)")51issues.append({"issue": f"mixed revenue units: {n_thou} distinct firms in USD_thousands, {len(firms)-n_thou} in USD_millions",52 "handling": "converted all revenue to millions USD via the row-level unit column"})5354# 3. missing revenue55miss = firms["revenue_musd"].isna()56n_missing = int(miss.sum())57missing_ids = sorted(firms.loc[miss, "firm_id"].tolist())58analytic = firms.loc[~miss, ["firm_id", "sector", "revenue_musd", "employees"]].copy()59step("3_drop_missing_revenue", "drop firms with empty revenue: " + ";".join(missing_ids),60 len(firms), len(analytic), "revenue unknown; cannot contribute to revenue aggregates (employees still used for mean_employees)")61n_missing_rows_raw = int(raw["revenue_num"].isna().sum())62issues.append({"issue": f"revenue empty on {n_missing_rows_raw} raw rows = {n_missing} distinct firms ({', '.join(missing_ids)})",63 "handling": "excluded from revenue aggregates and analytic.csv; retained for n_unique_firms and mean_employees"})6465analytic = analytic.sort_values("firm_id").reset_index(drop=True)66n_analysed = len(analytic)67assert n_rows - sum(r["n_removed"] for r in ledger) == n_analysed6869res = {70 "task_id": "F1-dev-01",71 "n_unique_firms": n_unique,72 "n_missing_revenue": n_missing,73 "n_analysed": n_analysed,74 "mean_revenue_musd": round(float(analytic["revenue_musd"].mean()), 10),75 "median_revenue_musd": round(float(analytic["revenue_musd"].median()), 10),76 "mean_employees": round(mean_employees, 10),77 "mean_revenue_raw": round(mean_revenue_raw, 10),78 "feasibility": "complete",79 "feasibility_reason": None,80 "data_issues": issues,81 "n_input_rows": {"firms.csv": n_rows},82 "random_seed": None,83 "input_sha256": {"firms.csv": hashlib.sha256(open(DATA, "rb").read()).hexdigest()},84 "baseline_note": "mean_revenue_raw mixes USD_thousands and USD_millions and double-counts duplicates; not interpretable",85}86os.makedirs(OUT, exist_ok=True)87analytic.to_csv(os.path.join(OUT, "analytic.csv"), index=False, float_format="%.6f")88pd.DataFrame(ledger).to_csv(os.path.join(OUT, "exclusions.csv"), index=False)89with open(os.path.join(OUT, "results.json"), "w") as f:90 json.dump(res, f, indent=2)91print(json.dumps({k: res[k] for k in ["n_unique_firms","n_missing_revenue","n_analysed",92 "mean_revenue_musd","median_revenue_musd","mean_employees","mean_revenue_raw"]}, indent=2))93