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

# Question

Turn 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.

# Data and exclusions

• 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.
• Problems found (data_issues):
  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.
  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.
  3. Unknown revenue: 6 distinct firms (n_missing_revenue; 7 raw rows, because F0399 is also duplicated): F0048, F0121, F0129, F0269, F0348 and F0399.
• 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.

# Method

• 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.
• Revenue is converted to millions of USD: USD_thousands values are divided by 1000 and USD_millions values are left as they are.
• 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.
• 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.
• No randomness is used (random_seed = null). out/analysis.py regenerates every output deterministically.

# Results

The 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".

# Uncertainty

These 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.

# Limitations and open issues

• 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.
• 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.
• 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.
• 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).