1"""F1-dev-01 analysis. Run from the workspace root: ~/wp29/venv/bin/python out/analysis.py23Reads only data/firms.csv. Writes out/results.json, out/analytic.csv and out/exclusions.csv.4If data/firms.csv is absent (as it was when this script was written), every output is written in5'infeasible' mode: estimates are null, analytic.csv has only a header, and the ledger records 0 rows read.6No random numbers are used (random_seed = null).7"""8import hashlib, json, os9import pandas as pd1011TASK_ID = "F1-dev-01"12SRC = os.path.join("data", "firms.csv")13EXPECTED_SHA = "78f119a92d7c11aebb8c238c69d62687a80d91031f1e8a30fb412b9e77e1625a" # from bundle.json14OUT = "out"15# Multipliers to millions of USD. Only units listed here are converted; any other unit is excluded and reported.16UNIT_TO_MUSD = {"usd": 1e-6, "$": 1e-6, "kusd": 1e-3, "usd_thousands": 1e-3, "thousand usd": 1e-3,17 "thousands usd": 1e-3, "musd": 1.0, "usd_millions": 1.0, "million usd": 1.0, "millions usd": 1.0,18 "usd millions": 1.0, "usd thousands": 1e-3, "busd": 1e3, "usd_billions": 1e3}19COLS = ["firm_id", "sector", "revenue_musd", "employees"]20LEDGER_COLS = ["step", "rule", "n_before", "n_after", "n_removed", "rationale"]212223def write(results, analytic, ledger):24 os.makedirs(OUT, exist_ok=True)25 analytic.to_csv(os.path.join(OUT, "analytic.csv"), index=False)26 pd.DataFrame(ledger, columns=LEDGER_COLS).to_csv(os.path.join(OUT, "exclusions.csv"), index=False)27 with open(os.path.join(OUT, "results.json"), "w") as fh:28 json.dump(results, fh, indent=2, sort_keys=False)29 print(json.dumps({k: results[k] for k in ("feasibility", "n_analysed", "mean_revenue_musd")}))303132def infeasible(reason, issues):33 results = {"task_id": TASK_ID, "n_unique_firms": None, "n_missing_revenue": None, "n_analysed": None,34 "mean_revenue_musd": None, "median_revenue_musd": None, "mean_employees": None,35 "mean_revenue_raw": None, "feasibility": "infeasible", "feasibility_reason": reason,36 "data_issues": issues, "n_input_rows": {"firms.csv": None}, "random_seed": None}37 ledger = [[0, "read data/firms.csv", 0, 0, 0,38 "input file absent from workspace; no rows could be read; nothing analysed"]]39 write(results, pd.DataFrame(columns=COLS), ledger)404142def main():43 if not os.path.isfile(SRC):44 infeasible("Required input data/firms.csv is absent from the workspace (data/ is empty), although "45 "bundle.json lists it (sha256 78f119a9...). TASK.md forbids any other input, so no estimand "46 "can be computed; all estimates are null rather than guessed.",47 [{"issue": "data/firms.csv missing from workspace",48 "handling": "task declared infeasible; all estimates set to null; no substitute data used"}])49 return50 raw_bytes = open(SRC, "rb").read()51 sha = hashlib.sha256(raw_bytes).hexdigest()52 issues = []53 if sha != EXPECTED_SHA:54 issues.append({"issue": f"sha256 {sha} differs from bundle manifest", "handling": "analysed as found; flagged"})55 df = pd.read_csv(SRC, dtype=str, keep_default_na=False)56 n_raw = len(df)57 for c in df.columns:58 df[c] = df[c].str.strip()59 rev_num = pd.to_numeric(df["revenue"].replace("", None), errors="coerce")60 mean_raw = float(rev_num.mean()) if rev_num.notna().any() else None61 ledger, n = [], n_raw62 ledger.append([0, "read data/firms.csv", n_raw, n_raw, 0, "rows read (excluding header)"])63 # step 1: exact duplicate rows64 d = df.drop_duplicates()65 if len(d) < n:66 issues.append({"issue": f"{n - len(d)} exact duplicate rows", "handling": "dropped, first kept"})67 ledger.append([1, "drop exact duplicate rows", n, len(d), n - len(d), "assembly duplicates"]); n = len(d); df = d68 # step 2: conflicting duplicates of firm_id -> keep first occurrence (documented)69 dup = df["firm_id"].duplicated(keep="first")70 if dup.any():71 issues.append({"issue": f"{int(dup.sum())} rows repeat a firm_id with differing values",72 "handling": "first occurrence kept; later rows dropped"})73 df = df[~dup]74 ledger.append([2, "one row per firm_id (keep first)", n, len(df), n - len(df), "one intended row per firm"]); n = len(df)75 n_unique = int(df["firm_id"].nunique())76 emp = pd.to_numeric(df["employees"].replace("", None), errors="coerce")77 if emp.isna().any():78 issues.append({"issue": f"{int(emp.isna().sum())} firms with unknown employees", "handling": "excluded from mean_employees"})79 mean_emp = float(emp.mean()) if emp.notna().any() else None80 # step 3: missing revenue81 rev = pd.to_numeric(df["revenue"].replace("", None), errors="coerce")82 miss = rev.isna()83 n_miss = int(miss.sum())84 if n_miss:85 issues.append({"issue": f"{n_miss} firms with unknown or non-numeric revenue", "handling": "excluded from revenue aggregates"})86 df = df[~miss].copy(); rev = rev[~miss]87 ledger.append([3, "drop firms with unknown revenue", n, len(df), n - len(df), "revenue unknown"]); n = len(df)88 # step 4: unit conversion89 mult = df["unit"].str.lower().map(UNIT_TO_MUSD)90 bad = mult.isna()91 if bad.any():92 issues.append({"issue": f"{int(bad.sum())} rows with unrecognised unit {sorted(df.loc[bad, 'unit'].unique())}",93 "handling": "excluded (cannot convert to USD millions)"})94 ledger.append([4, "convert revenue to USD millions (recode)", n, n, 0, "unit-specific multipliers"])95 df = df[~bad].copy(); df["revenue_musd"] = rev[~bad] * mult[~bad]96 ledger.append([5, "drop rows with unrecognised unit", n, len(df), n - len(df), "no valid conversion"]); n = len(df)97 df["employees"] = pd.to_numeric(df["employees"].replace("", None), errors="coerce")98 analytic = df[COLS].reset_index(drop=True)99 feas = "complete" if not bad.any() else "partial"100 results = {"task_id": TASK_ID, "n_unique_firms": n_unique, "n_missing_revenue": n_miss, "n_analysed": int(n),101 "mean_revenue_musd": float(analytic["revenue_musd"].mean()) if n else None,102 "median_revenue_musd": float(analytic["revenue_musd"].median()) if n else None,103 "mean_employees": mean_emp, "mean_revenue_raw": mean_raw, "feasibility": feas,104 "feasibility_reason": None if feas == "complete" else "some units could not be converted",105 "data_issues": issues, "n_input_rows": {"firms.csv": int(n_raw)}, "random_seed": None}106 write(results, analytic, ledger)107108109if __name__ == "__main__":110 main()111