# F1-dev-01 — Analytic firm dataset and revenue aggregates

# Question

How many distinct firms are in data/firms.csv (simulated data), and what are the mean and median annual revenue (millions USD) over firms with known revenue, and the mean head count over all distinct firms? Every removal/transformation is documented.

# Data and exclusions

• 445 data rows read (header excluded); 400 distinct firm_id values.
• Exact duplicates: 45 rows are byte-identical copies of another row (assembly duplicates). Dropped, keeping one per firm → 400 rows. After this, no firm_id appears with conflicting values.
• Mixed units: 60 of the 400 firms report revenue in USD_thousands, 340 in USD_millions. Thousands were divided by 1000 (no rows removed).
• Missing revenue: 6 distinct firms (F0048, F0121, F0129, F0269, F0348, F0399) have an empty revenue cell (7 raw rows, as F0399 is duplicated). Excluded from revenue aggregates → 394 analysed firms. They are kept for the employee mean (employees is complete for all firms).
• Other checks: firm IDs all match F####; sectors take exactly five values; only the two units above occur; all revenues are positive; no non-numeric values.
• Ledger: out/exclusions.csv (445 → 445 → 400 → 400 → 394). Analytic data: out/analytic.csv (394 rows).

# Method

Deterministic cleaning in out/analysis.py (no randomness, random_seed = null): strip whitespace, drop exact duplicate rows, convert revenue to millions USD by unit, exclude empty revenue, compute mean/median revenue over the 394 firms and mean employees over the 400 deduplicated firms. Baseline mean_revenue_raw is the mean of the raw revenue column over all 438 non-empty rows, with mixed units and duplicates included.

# Results

The raw baseline (≈30,696) is roughly 120 times the cleaned mean. This comes almost entirely from the USD_thousands rows, which shows that unit harmonisation is needed. Revenue is right-skewed (mean > median; range 5.742–2057.575 MUSD).

# Uncertainty

These are descriptive statistics of the full file, not estimates from a sample. If the firms are viewed as a sample from a larger population, the standard error of mean revenue is 13.819 MUSD (normal 95% CI 222.876–277.048), and the SE of mean employees is 63.155. The analysis script prints these values. The cleaning itself is deterministic.

# Limitations and open issues

• The data are synthetic; the results describe the simulated file only.
• I assumed exact duplicates are assembly errors and not distinct firms. The fact that firm_id is identical in each pair supports this.
• Missing revenue is treated as missing completely at random. The 6 excluded firms include some with large head counts (e.g. 3246 and 3547 employees), so revenue aggregates could be biased downward if missingness is related to size. No imputation was done.
• Unit labels are taken as correct. No row-level magnitude check showed a mislabelled unit (converted values fall within the range of the USD_millions rows), but mislabelling cannot be ruled out.