Eurostat Population (bulk TSV) → Clean Per-Country Rows¶

What

Turn one row of Eurostat's bulk demo_pjan download (population on 1 January) into a clean per-country row: dimensions unpacked into their own columns, year values stripped of their quality-flag suffixes, and the : missing-data marker turned into a real empty cell — all in one template.

Why interesting¶

Eurostat is the official statistical office of the EU and its bulk TSV is the format behind thousands of research pipelines, yet every file ships with four quirks that quietly break naive parsers: (1) it is tab-separated, not comma; (2) the first column packs five dimensions comma-joined into a single header cell — freq,unit,age,sex,geo\TIME_PERIOD, with the row value like A,NR,TOTAL,T,DE — so you must split it before you can group by country; (3) a missing observation is the literal : (often ": " with a trailing space), which a naive numeric cast reads as 0 or NaN; (4) present values carry a space-separated quality-flag suffix — 83118501 b (break in series), 68277210 p (provisional), 445891011 bep (several flags at once) — so value * 1 throws and cut-based pipelines keep the b glued to the number.

Edge cases sourced from.

Eurostat bulk download / data format docs — the : not-available marker and the single-letter observation flags (b break, e estimated, p provisional, d definition differs, …) are the documented Eurostat conventions.
The packed first-column header dim1,dim2,…\TIME_PERIOD is the standard shape of every Eurostat bulk TSV.

Data source. Eurostat — demo_pjan (Population on 1 January by age and sex) via the dissemination API. Free reuse with attribution (Commission Decision 2011/833/EU). (This slice: 8 real freq=A, unit=NR, age=TOTAL, sex=T rows, column-sliced to 2021–2023 and hand-picked so that every observation shape is present — a clean value, each of the b / p / ep / bep flag combinations, and Andorra's : not-available marker.)

The trick¶

(see sample.json):

Unpack the dimension column with SPLIT_PART(FIELDS(1), ',', N) — geo is part 5, sex part 4, age part 3.
Clean each observation with one composable idiom: NULLIF(SPLIT_PART(TRIM(FIELDS(n)), ' ', 1), ':') — TRIM drops the trailing space, SPLIT_PART(…, ' ', 1) keeps the value and discards the flag, and NULLIF(…, ':') turns the missing-marker into an empty cell.
Keep the flag as data, not noise: SPLIT_PART(TRIM(FIELDS(n)), ' ', 2) lifts the b/p/bep suffix into its own column so the break/provisional status survives the cleanup.

By name or by position? — [Name] vs FIELDS(n)

[Name] in bxp is always a lookup by header name — there is no [N] positional form, and headers are trimmed before matching, so Eurostat's 2023 header (bare integer, trailing space) is in fact reachable as [2023], and even the packed first column answers to [freq,unit,age,sex,geo\TIME_PERIOD].

This template addresses the file positionally instead, with the FIELDS(n) accessor: FIELDS(1) is the packed dimension column, and the year columns are FIELDS(2)/FIELDS(3)/FIELDS(4) in the sliced sample and FIELDS(63)/FIELDS(64)/FIELDS(65) in the full file, where every year from 1960 is present. Positional access keeps sample.json and full.json the same shape — the only thing that changes between them is the index — and new year columns are appended at the end on each release, so the front-counted positions stay stable. Either style works here; pick by-name when the header is stable and descriptive, FIELDS(n) when the file is really a positional record.

At full scale¶

bash fetch-full.sh          # downloads the full demo_pjan bulk TSV into ./full/
bxp-cli --config full.json  # cleans every ~17.7k rows (all age/sex/geo combos)

Final result¶

Germany's raw 2023 cell is 83118501 b and Andorra's is :. The template turns them into:

DE,TOTAL,T,83155031,83237124,83118501,b
AD,TOTAL,T,,,,

— a clean integer plus a preserved b flag for Germany, and three genuinely empty cells for Andorra (not a misleading 0). That drops straight into a GROUP BY geo or a join on country code, with no pre-processing in Python.

Sample data¶

Run it with bxp-cli --config ./sample.json --template eurostat_pop_tsv_clean:

sample.json (config)sample.csvsample.csvx (result)full.json (the same template at full scale)

{
  // Eurostat bulk TSV (`demo_pjan` — population on 1 January). The canonical
  // shape of every Eurostat bulk download, and a minefield of quirks:
  //   * tab-separated, not comma;
  //   * the FIRST column packs all dimensions comma-joined into one header
  //     cell: `freq,unit,age,sex,geo\TIME_PERIOD`;
  //   * the year headers are bare integers (`2021`, `2022`, …);
  //   * a missing observation is the literal `:` (with a trailing space);
  //   * present values carry a space-separated quality FLAG suffix
  //     (`83118501 b` = break in series, `… p` = provisional, `bep` = several).
  // This template turns one raw row into a clean per-country population row.
  conversion_templates: {
    eurostat_pop_tsv_clean: {
      data_dir:          ".",
      file_pattern_in:   ".csv",
      file_pattern_out:  ".csvx",
      // TRICK 0 — Eurostat bulk files are TAB-separated.
      csv_delimiter_in:  "\t",
      csv_delimiter_out: ",",

      input_schema: {
        // TRICK 1 — POSITIONAL field refs via FIELDS(n). The year headers are
        // bare integers; `[2023]` WOULD resolve (brackets always look up by
        // header name, and headers are trimmed), but naming each year couples
        // the template to one vintage of the file. FIELDS(n) addresses by
        // column position instead, so the same template survives a new year
        // being appended: FIELDS(1) is the packed dimension column,
        // FIELDS(2)/FIELDS(3)/FIELDS(4) are years 2021/2022/2023.

        // TRICK 2 — UNPACK the dimension column. Column 1 is
        // `A,NR,TOTAL,T,DE` — five comma-joined dimension codes. SPLIT_PART
        // pulls each one out (freq=1, unit=2, age=3, sex=4, geo=5).
        $geo: "SPLIT_PART(FIELDS(1), ',', 5)",
        $age: "SPLIT_PART(FIELDS(1), ',', 3)",
        $sex: "SPLIT_PART(FIELDS(1), ',', 4)",

        // TRICK 3 — CLEAN each observation. Each cell is e.g. `83118501 b `
        // (value + flag + trailing space) or `: ` (missing). The idiom:
        //   TRIM       → drop the trailing space            "83118501 b"
        //   SPLIT_PART → keep the value, drop the flag       "83118501"
        //   NULLIF     → turn the `:` missing-marker → ""    "" for ": "
        $pop_2021: "NULLIF(SPLIT_PART(TRIM(FIELDS(2)), ' ', 1), ':')",
        $pop_2022: "NULLIF(SPLIT_PART(TRIM(FIELDS(3)), ' ', 1), ':')",
        $pop_2023: "NULLIF(SPLIT_PART(TRIM(FIELDS(4)), ' ', 1), ':')",

        // TRICK 4 — PRESERVE the quality flag instead of discarding it. The
        // flag (`b`/`p`/`bep`) is data, not noise — it tells an analyst the
        // figure is a break/provisional. Empty when the value is clean.
        $flag_2023: "SPLIT_PART(TRIM(FIELDS(4)), ' ', 2)"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        geo:       "$geo",
        age:       "$age",
        sex:       "$sex",
        pop_2021:  "$pop_2021",
        pop_2022:  "$pop_2022",
        pop_2023:  "$pop_2023",
        flag_2023: "$flag_2023"
      }
    }
  }
}

freq,unit,age,sex,geo\TIME_PERIOD   2021    2022    2023 
A,NR,TOTAL,T,DE 83155031    83237124    83118501 b
A,NR,TOTAL,T,FR 67728568    68091703    68277210 p
A,NR,TOTAL,T,CZ 10494836 b  10516707    10827529 
A,NR,TOTAL,T,AD :   :   : 
A,NR,TOTAL,T,AL 2829741     2793592     2761785 
A,NR,TOTAL,T,EU27_2020  445891011 bep   445972024 ep    447695350 bep
A,NR,TOTAL,T,IT 59236213    59030133    58997201 
A,NR,TOTAL,T,PL 37073357 bep    36889761 ep 36753736 ep

geo,age,sex,pop_2021,pop_2022,pop_2023,flag_2023
DE,TOTAL,T,83155031,83237124,83118501,b
FR,TOTAL,T,67728568,68091703,68277210,p
CZ,TOTAL,T,10494836,10516707,10827529,
AD,TOTAL,T,,,,
AL,TOTAL,T,2829741,2793592,2761785,
EU27_2020,TOTAL,T,445891011,445972024,447695350,bep
IT,TOTAL,T,59236213,59030133,58997201,
PL,TOTAL,T,37073357,36889761,36753736,ep

Only the indices move: FIELDS(2) → FIELDS(63), because the full file carries every year from 1960 while the slice carries three. That is the whole point of addressing this file positionally.

{
  // Scale/complete-run config: same cleaning idiom as sample.json, pointed at
  // the full demo_pjan bulk TSV in ./full/ (all age/sex/geo combos, ~17.7k
  // rows). The ONLY difference from sample.json is the positional year indices:
  // the full file carries every year from 1960, so 2021/2022/2023 sit at
  // columns 63/64/65 (in the column-sliced sample they are 2/3/4). Year columns
  // are appended at the end each release, so these front-counted positions stay
  // stable.
  conversion_templates: {
    eurostat_pop_tsv_clean: {
      data_dir:          "full",
      file_pattern_in:   ".tsv",
      file_pattern_out:  ".csvx",
      csv_delimiter_in:  "\t",
      csv_delimiter_out: ",",

      input_schema: {
        $geo: "SPLIT_PART(FIELDS(1), ',', 5)",
        $age: "SPLIT_PART(FIELDS(1), ',', 3)",
        $sex: "SPLIT_PART(FIELDS(1), ',', 4)",
        $pop_2021: "NULLIF(SPLIT_PART(TRIM(FIELDS(63)), ' ', 1), ':')",
        $pop_2022: "NULLIF(SPLIT_PART(TRIM(FIELDS(64)), ' ', 1), ':')",
        $pop_2023: "NULLIF(SPLIT_PART(TRIM(FIELDS(65)), ' ', 1), ':')",
        $flag_2023: "SPLIT_PART(TRIM(FIELDS(65)), ' ', 2)"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        geo:       "$geo",
        age:       "$age",
        sex:       "$sex",
        pop_2021:  "$pop_2021",
        pop_2022:  "$pop_2022",
        pop_2023:  "$pop_2023",
        flag_2023: "$flag_2023"
      }
    }
  }
}

Full-scale & binary files (run it on the complete dataset): fetch-full.sh · full.json.