A Government Dataset With Footnotes in the Data Cells

Official statistics are published with care and with symbols. A population figure is 1,204,300 (p) because it is provisional. An unemployment rate is 5.3 e because it is an estimate. A cell holds .. or x because the value is unavailable or was withheld to protect someone's privacy. Each of those marks is information that a statistician wants beside the number, and a CSV has one place to put it, the cell itself. So the file is exactly what a person needs and almost useless to a program: the column that should be numeric holds text, with flags in various positions and several tokens for missing. This article shows how to split the number from its flag, keep both, check the result against a stated total and leave the suppressed cell alone. It is one of the problems in worked CSV problems and the patterns behind them, and it shares a technique with an analytics export with percent signs and dashes.

The file

region,year,population,unemployment_rate,note
North,2024,"1,204,300 (p)",4.1,
South,2024,"980,550",5.3 e,
East,2024,..,x,suppressed
West,2024,"1,150,200 r",4.8,revised
Total,2024,"4,100,000 (p)",4.7 e,

Four things are going on. The population has thousands separators and, in three rows, a suffix flag: (p), r. The unemployment rate has a flag too, a bare e after a space. East has .. for population and x for the rate, tokens that mean "no value" and that a statistical agency would explain in a legend. And there is a Total row mixed with the regions, which is both a trap (summing the column double counts) and an opportunity (it is a check).

The legend matters. The meaning of each symbol belongs to the publisher, and the file does not carry it. Before you write a rule, find the key in the documentation of the dataset and write it into your code: (p) provisional, e estimated, r revised, .. not available, x suppressed. Do not guess.

Split the number from the flag

The value is a number at the start of the cell and a flag after it, and a regular expression separates them. Digits, commas and a decimal point at the start are the number, and whatever remains after trimming is the flag:

CREATE TABLE tidy AS
SELECT region, year::INT AS year,
  TRY_CAST(replace(regexp_extract(population, '^[0-9,]+', 0), ',', '') AS BIGINT) AS population,
  nullif(trim(regexp_replace(population, '^[0-9,]+', '')), '') AS population_flag,
  TRY_CAST(regexp_extract(unemployment_rate, '^[0-9.]+', 0) AS DECIMAL(5,2)) AS unemployment_rate,
  nullif(trim(regexp_replace(unemployment_rate, '^[0-9.]+', '')), '') AS unemployment_flag
FROM read_csv('gov.csv', all_varchar=true);

region  year  population  population_flag  unemployment_rate  unemployment_flag
East    2024  NULL        ..               NULL               x
North   2024  1204300     (p)              4.10               NULL
South   2024  980550      NULL             5.30               e
Total   2024  4100000     (p)              4.70               e
West    2024  1150200     r                4.80               NULL

Read it cell by cell. 1,204,300 (p) became 1204300 and the flag (p). The plain 980,550 has a number and no flag. 5.3 e became 5.30 with flag e. East's .. has no leading digits, so the number is NULL, and the flag column holds the token itself, .., which keeps the reason the value is missing: .. and x stay visible instead of vanishing into a NULL. That is the point of keeping a flag column. A NULL says nothing, and .. says "not available" per the legend.

The regex has to be written with care. '^[0-9,]+' takes a run of digits and commas, and it would also take a lone comma; for a field that might start with a comma, tighten it to '^[0-9][0-9,]*'. The decimal rates use '^[0-9.]+'. Negative values, a leading minus or parentheses, and a currency symbol are not in this file and would each need their own clause, as in an analytics export with percent signs and dashes. And since a missing flag is NULL, a statement like population_flag = '(p)' is false for a NULL, which is the behavior you want and a trap for a NOT IN filter.

Check what the flags are

A table of the distinct flags and how many rows carry each is the proof that you know all the symbols in the file:

SELECT population_flag, count(*) FROM tidy GROUP BY 1 ORDER BY 1;

population_flag  count
(p)              2
..               1
r                1
NULL             1

Compare the list with the legend. A flag that is not in the legend, a k or a *, means either the legend is incomplete or the cleaning has a bug, and you want to stop and find out. Put the allowed flags in a table and fail if a row carries something else. For files that arrive regularly, a new flag is an event: the publisher added a category, and the right response is to read what it means.

The total as a check

The Total row is a gift. A published total lets you test the parts. Sum the regions that have values and compare:

SELECT sum(population) FILTER (WHERE region <> 'Total') AS sum_regions,
       max(population) FILTER (WHERE region = 'Total') AS stated_total FROM tidy;

sum_regions  stated_total
3335050      4100000

The three regions with values add to 3,335,050, and the stated total is 4,100,000. They do not match, and the difference, 764,950, is a number worth understanding. In this file the explanation is the suppressed East row: the total includes a value that the table does not show. If exactly one cell is missing, the gap is its value. If two are missing, the gap is their sum, and you cannot separate them. If the numbers had matched, the check would have confirmed that the cleaning kept every value. If they differ by something that is not a suppressed cell, a row was dropped, or a flag swallowed a digit, or the total is computed on a different basis (rounded components, a different date). Always exclude the total row from sums, and write the filter, since a column sum that includes it is exactly double.

There is a caution about what you do with the gap. Statistical agencies suppress cells because publishing them could identify a person or a firm, and a table that gives the total and all but one cell publishes the last one by subtraction. If you derive 764,950 for East and publish it, you may undo the protection that the source applied on purpose. Use the arithmetic as a check, and do not republish a derived value for a cell the source chose to hide. For small counts, this is a matter of privacy and sometimes law (stripping personal data from a CSV before sharing it covers the neighboring ideas).

Keep the flags in the analysis

It is tempting to drop the flag columns once the numbers are clean. Resist. A provisional figure may be revised, an estimate has a margin the final figure does not, and a chart that treats them alike overstates how much is known. Keep the flags, show them where they matter (a footnote mark, a lighter bar) and filter on them when you need final data only: WHERE population_flag IS NULL selects the values with no qualifier. When a later release replaces (p) with the final number, compare: a provisional value that moves by more than a tiny amount is itself a finding.

When a symbol means something you must not drop

Some marks change the meaning of the number. A superscript or letter that says the figure refers to a different population or a different definition, a break in series note, a unit change from thousands to millions halfway down the table: those are not decoration. If the legend says a mark changes the basis, carry it as a column and do not combine rows with different marks in one aggregate. And when the file carries footnotes as separate lines at the bottom (1. Excludes...), read them before you trust the table, because they often hold the definitions. The format gives you none of this, and RFC 4180 is silent on it. For the DuckDB functions used, see its regular expression functions and CSV overview.