1# F1-dev-01 — Analytic firm dataset and revenue aggregates23## Question4Turn the simulated firm cross-section `data/firms.csv` into a documented dataset with one row per firm, ready for analysis. Then report: the number of distinct firms; the mean and median revenue in **millions of USD** over the firms whose revenue is known; and the mean number of employees over all distinct firms.56## Data and exclusions7- Input: `data/firms.csv` (SHA-256 `78f119a9…625a`). It has 445 data rows (`n_input_rows`) and 400 distinct `firm_id` values (`n_unique_firms`). The full audit is in `out/state/data_audit.md`.8- Problems found (`data_issues`):9 1. **Exact duplicate rows:** 45 rows (`n_exact_duplicate_rows`). Each one repeats another row in all five columns. No firm has two records that disagree.10 2. **Mixed revenue units:** 60 distinct firms report revenue in `USD_thousands` (`n_firms_usd_thousands`); all the others use `USD_millions`. After converting thousands to millions, revenue per employee is on the same scale in both groups. This supports the conversion and gives no sign of mislabelled units.11 3. **Unknown revenue:** 6 distinct firms (`n_missing_revenue`; 7 raw rows, because F0399 is also duplicated): F0048, F0121, F0129, F0269, F0348 and F0399.12- Exclusion ledger (`out/exclusions.csv`): 445 → step 1, drop exact duplicates (−45) → 400 → step 2, convert units to millions (recode, −0) → 400 → step 3, drop firms with unknown revenue (−6) → 394 (`n_analysed`). The analytic file `out/analytic.csv` has exactly 394 rows.1314## Method15- The file is read as strings so that empty cells are kept as "unknown". Exact duplicate rows are removed, keeping the first copy. After that, every `firm_id` appears exactly once; this is asserted in the code.16- Revenue is converted to millions of USD: `USD_thousands` values are divided by 1000 and `USD_millions` values are left as they are.17- Revenue aggregates use only firms with known revenue (complete cases). Mean employees uses all 400 distinct firms, as the task specifies; employees is never missing.18- Baseline: the plain mean of the raw `revenue` column over the 438 non-empty raw rows. It is computed before any cleaning and keeps each row's original unit, whatever it is.19- No randomness is used (`random_seed` = null). `out/analysis.py` regenerates every output deterministically.2021## Results22| quantity | value | key |23|---|---|---|24| distinct firms | 400 | `n_unique_firms` |25| firms with unknown revenue | 6 | `n_missing_revenue` |26| firms in revenue aggregates | 394 | `n_analysed` |27| mean revenue (M USD) | 249.96 | `mean_revenue_musd` |28| median revenue (M USD) | 162.31 | `median_revenue_musd` |29| mean employees (all 400 firms) | 1101.44 | `mean_employees` |30| baseline: raw mean of revenue column (mixed units, with duplicates) | 30695.88 | `mean_revenue_raw` |3132The raw baseline (`mean_revenue_raw`) is about 120 times the cleaned mean (`mean_revenue_musd`). The cause is that rows recorded in thousands of USD are treated as if they were millions. The baseline is therefore not a meaningful revenue figure; it shows how much the cleaning matters. The median is well below the mean, so the revenue distribution is skewed to the right. Feasibility: `feasibility` = "complete".3334## Uncertainty35These aggregates are descriptive statistics of the file, not estimates for a wider population, so no sampling uncertainty is needed to answer the question. As a supplementary figure, the standard deviation of revenue is 274.30 M USD (`sd_revenue_musd`). The standard error of the mean is 13.82 M USD (`se_mean_revenue_musd`), but it only matters if the firms are treated as an i.i.d. sample from a larger population.3637## Limitations and open issues38- The revenue aggregates assume the 6 firms with unknown revenue are ignorable (complete-case analysis); nothing was imputed. Their mean head count compared with the analysed firms can be seen by comparing `mean_employees` (1101.44, all firms) with `mean_employees_analysed` (1095.65), and the difference is small.39- The conversion relies on the `unit` column being correct for each row. The revenue-per-employee check supports this but cannot prove it row by row.40- Removing duplicates is safe here because every duplicate pair is identical in all columns. If a record had conflicted, the script would stop (assertion) instead of guessing.41- Process note: during the work the shell's working directory briefly drifted into `data/`, which created an empty `data/out/` directory there by mistake. It was removed. `data/firms.csv` was never written to, and its SHA-256 is unchanged (see `out/state/log.md`).42