1# F1-dev-01 — Analytic firm dataset and revenue aggregates23## Question4How 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.56## Data and exclusions7- 445 data rows read (header excluded); 400 distinct `firm_id` values.8- **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.9- **Mixed units:** 60 of the 400 firms report revenue in `USD_thousands`, 340 in `USD_millions`. Thousands were divided by 1000 (no rows removed).10- **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).11- 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.12- Ledger: `out/exclusions.csv` (445 → 445 → 400 → 400 → 394). Analytic data: `out/analytic.csv` (394 rows).1314## Method15Deterministic 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.1617## Results18| quantity | value |19|---|---|20| distinct firms | 400 |21| firms with missing revenue | 6 |22| firms analysed | 394 |23| mean revenue | 249.962 MUSD |24| median revenue | 162.306 MUSD |25| mean employees (400 firms) | 1101.435 |26| raw-column revenue mean (baseline, mixed units) | 30695.882 |2728The 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).2930## Uncertainty31These 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.3233## Limitations and open issues34- The data are synthetic; the results describe the simulated file only.35- I assumed exact duplicates are assembly errors and not distinct firms. The fact that `firm_id` is identical in each pair supports this.36- 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.37- 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.38