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

# Question

Build a documented, analysis-ready version of the synthetic firm cross-section data/firms.csv and report: the number of distinct firms, mean and median revenue in millions of USD over firms with known revenue, and mean head count over all distinct firms.

# Data and exclusions

• Input: data/firms.csv (SHA-256 78f119a9…625a), 445 data rows (n_input_rows). Full audit in out/state/data_audit.md.
• Issues detected (data_issues):
  1. 45 rows repeat an earlier firm_id; every one is an exact full-row duplicate (no conflicting values). Handling: keep one row per firm. Result: 400 distinct firms (n_unique_firms).
  2. Mixed revenue units: some rows are in USD_thousands, the rest in USD_millions. Handling: divide USD_thousands values by 1000 (60 distinct firms). After conversion, revenue per employee for the two groups falls in the same range, so the unit labels look correct.
  3. 6 distinct firms have an empty revenue cell (n_missing_revenue): F0048, F0121, F0129, F0269, F0348, F0399. Handling: they are left out of the revenue aggregates but kept in n_unique_firms and mean_employees.
• Exclusion ledger (out/exclusions.csv): 445 → (drop exact duplicates, −45) 400 → (unit recoding, −0) 400 → (drop missing revenue, −6) 394 = n_analysed. The analytic dataset out/analytic.csv has 394 rows, one per firm.

# Method

This is deterministic data cleaning plus descriptive statistics (pandas). It uses no randomness (random_seed = null). Revenue aggregates are the arithmetic mean and median of revenue_musd over the 394 analysed firms. mean_employees is the mean head count over all 400 deduplicated firms. The baseline mean_revenue_raw is the mean of the raw revenue column over all 438 non-empty rows, taken as read, with no deduplication or unit conversion. out/analysis.py regenerates every output.

# Results

• Distinct firms: 400 (n_unique_firms). Firms with unknown revenue: 6 (n_missing_revenue). Firms analysed: 394 (n_analysed).
• Mean revenue: 249.96 million USD (mean_revenue_musd). Median revenue: 162.31 million USD (median_revenue_musd). The mean is above the median, so the distribution is right-skewed.
• Mean employees: 1101.4 (mean_employees).
• Raw baseline: 30,695.9 in the file's mixed units (mean_revenue_raw). This number has no meaningful unit. It is about 123 times the cleaned mean because the USD_thousands rows are mixed in without conversion, which shows why the unit recoding is necessary.
• Feasibility: complete (feasibility).

# Uncertainty

The estimands describe this file exactly; there is no sampling uncertainty about them. For reference only, if the 394 firms were treated as a random sample, the standard deviation of revenue would be 274.3 million USD (sd_revenue_musd) and the standard error of the mean 13.82 million USD (se_mean_revenue_musd). The results depend on the cleaning choices: whether to deduplicate, how to convert units, and that missing revenue is treated as missing completely at random (unknown values are not imputed).

# Limitations and open issues

• The data are simulated. Duplicates were identified by exact match on all columns. Near-duplicates with different firm_id values cannot be detected from this file.
• I assumed the unit column is correct on every row. A plausibility check of revenue per employee (85.9k–826.9k USD) found no rows that look mislabelled, but this cannot be fully verified.
• Excluding the 6 firms with missing revenue may bias the revenue aggregates if the missingness is related to firm size. Their head counts (326 to 3547) span the observed range.