People by country data: sources, fields and accuracy checks

Country columns break in a small number of ways, over and over, in every project that has one. Most of the breakage happens on read, before anybody has thought about the subject at all: a file is opened with default settings, and the column arrives already damaged.

What follows is the set of defects worth designing against, and the checks that catch each one. Which country lists exist and why they differ is on the country list page; this is what happens to the values once they are in a file.

What to take away

• Read country codes as text, always. Default type inference destroys them in two different ways and reports no error.
• A country split turns one code into several, so any long series needs a concordance table with an explicit rule, and the rule is an assumption you must record.
• A blank in a country column has at least four meanings, and only one of them is an absence.

Two silent failures on read

The missing-value collision. One of the two-letter country codes is the same pair of characters that spreadsheet and statistical tools use as their default token for a missing value. Open a file with type inference on and those rows become nulls. Nothing warns you. The count for one country drops to zero and everything else looks fine.

Leading zeros. Numeric country codes are fixed-width strings, not numbers. Read as numbers, the leading zeros vanish and the values no longer match any lookup table. Written back out, the file now contains codes that do not exist.

Both are prevented the same way: declare the column as text on read, never rely on inference, and check the distinct values against the published code list, whose structure and change history are documented with ISO 3166-1, before doing anything else.

The third read-time defect is character encoding. Country names carry diacritics and non-Latin scripts, and a wrong encoding assumption turns them into mangled sequences that no longer match anything. It is one more reason the name is a display field and the code is the key, a point covered under dataset shape.

Splits, mergers and the concordance table

When one entity becomes several, a single historical code maps to multiple modern ones. There is no arithmetic that resolves this on its own.

You need a concordance table holding, for each mapping: the old code, the new code or codes, the effective date, and the treatment. The treatment is where the honesty lives:

Treatment	When it applies	What it costs
One to one	A rename or a code change with no territorial change	Nothing, if the date is recorded
One to many, unsplit	A split where the underlying records cannot be attributed	The earlier years cannot be shown per successor
One to many, attributed	Where each record carries enough detail to place it	Record level work, and a documented rule
Many to one	A merger	The combined series is not comparable to either input

What you must not do is apportion by a share and present the result as data. If an apportionment is unavoidable, store it in its own column, name the assumption, and keep the unapportioned value beside it.

Aggregating without double counting

Two traps sit between country rows and a regional total.

Dependencies. Some lists carry a territory as its own row and also inside its governing state's row. Sum the column and you have counted it twice. Decide which convention the file uses, apply it once, and state it.

Region schemes. Statistical regions, economic groupings and continental groupings are different schemes maintained by different bodies, and the same country sits in different regions in each. Mixing two schemes in one table produces regions that overlap and totals that do not reconcile. Pick a scheme, name it, and use only that one.

Denominators come from somewhere too

Per-capita figures are the usual reason a country dataset acquires a second source, and the join is where they go wrong.

Match the vintage: a numerator drawn from one period divided by a population from another is a ratio of two unrelated things. Match the boundary: the population of the territory as it then was, or of the modern state, and the two can differ enormously. Match the code system, including its version, or the join will silently drop the entries whose codes changed.

And record where the population figure came from, with its own date. A denominator with no source is the most common unattributed number in this kind of work, and it usually carries more uncertainty than the numerator it is dividing. Statistical agencies publish the definitions behind theirs, as with the guidance on migration and asylum statistics.

Four kinds of blank

• Not collected. The source never asked, or the field was added later.
• Not applicable. No country applies, for example a record of a stateless person or a birth outside any state.
• Not established. A value exists and has not been determined.
• Suppressed. The publisher withheld it, usually to protect small counts.

Only the first three are properties of the record; the fourth is a property of the publication. Collapse them and every rate you compute silently changes its denominator, and every map gains an absence that is really a redaction.

Checks to run on every load

• Code validity against the named list and vintage, with unknown codes listed rather than dropped.
• Name and code agreement. Where both columns exist, they should agree; disagreements are usually merge debris.
• Round trip. Write the file and read it back. Anything that changes is a type or encoding defect.
• Sum of parts against published totals, where the publisher gives a total. A mismatch means a dependency, a suppression or a scheme mix.
• Distinct value count over time. A jump in the number of countries is a container event, not a discovery, and the year page explains how to present it.
• Coverage by entity. Fill rate per country. Very high and very low outliers both indicate a source artifact.

What the data does not license

A clean country dataset supports description of its own source. It does not support statements about individuals, and it does not support ordering countries by the people in them, which is a page this site declines to publish for reasons set out on the rankings page.

For living people the field is not a technical question at all. We do not record anyone's nationality, ancestry, religion or birthplace, and no amount of schema care makes an inferred value publishable.

Bottom line

Most damage to a country column happens at read time, from type inference and encoding, and it is silent. Read as text, validate against a named vintage, keep a concordance for every container event, and treat blanks as four different conditions rather than one.

Common questions

Should historical records be stored under modern codes?

Store both: the entity named on the record, and the modern entity covering that ground, in separate columns with the concordance date. Storing only one loses the ability to answer half the questions people will ask.

Is a code list a safe join key across two publishers?

Only if both name the same system and version. Publishers extend standard lists with their own entries for territories they need, and those extensions do not match each other.