# F1-dev-01 — Firm cross-section: analytic dataset and revenue aggregates

# Question

How many distinct firms are in data/firms.csv (simulated data)? What are the mean and median revenue in millions of USD over the firms whose revenue is known? What is the mean head count over all distinct firms? Every row removed or changed on the way to the analytic dataset is documented.

# Data and exclusions

• 445 data rows read. Each row has firm_id, sector (5 sectors), revenue, unit, employees.
• Duplicates: 45 rows are exact copies of another row, identical in every column. They were dropped and the first copy kept, leaving 400 rows. No firm_id appears more than once with different values.
• Mixed units: 60 of the 400 firms report revenue in USD_thousands and the rest in USD_millions. All values were converted to millions of USD (thousands ÷ 1000). After conversion the two groups look alike: median 124.8 vs 178.0 M USD and maximum 1005.8 vs 2057.6 M USD. That supports the conversion.
• Missing revenue: 6 firms have an empty revenue cell: F0048, F0121, F0129, F0269, F0348 and F0399. F0399 also appeared twice as an exact duplicate. These firms are left out of the revenue aggregates and analytic.csv. They still count in n_unique_firms and mean_employees.
• Every firm has a head count. No values were non-numeric, and no firm IDs or sector labels were malformed.

Ledger (out/exclusions.csv): 445 → 400 after removing exact duplicates → 400 after checking for conflicting duplicates → 400 after unit conversion → 394 after removing missing revenue.

# Method

Deterministic cleaning in out/analysis.py, with no randomness (random_seed = null). The aggregates are the arithmetic mean and median of revenue_musd over the 394 firms in the analytic dataset. Mean employees is taken over all 400 distinct firms. The baseline mean_revenue_raw is the mean of the raw revenue column over the 438 non-empty rows. That includes duplicates and mixes the two units.

# Results

The raw baseline is about 120 times the cleaned mean. Almost all of that gap comes from the 60 firms reported in thousands of USD.

# Uncertainty

The aggregates describe this file in full; they are not estimated from a sample. If the 394 firms are treated as a sample from a larger population, the revenue SD is 274.3 M USD and the standard error of the mean is 13.8 M USD, giving an approximate 95% CI of ±27 M USD. Revenue ranges from 5.74 to 2057.58 M USD and is right-skewed (mean above median). These extra numbers are in out/extra_stats.json.

# Limitations and open issues

• The data are synthetic.
• Units were converted based on the unit column alone. If a row is labelled with the wrong unit, this cannot be detected beyond the distribution check above.
• Firms with missing revenue were dropped (complete-case analysis) and not imputed. The 6 firms are 1.5% of all firms, so any bias should be small. Their mean head count can be compared with the analysed firms: 1095.6 for the 394 analysed firms vs 1101.4 for all 400.
• Treating exact duplicates as assembly copies is an assumption. Two genuinely different firms would not share an ID and all their values.