JHU COVID-19 Wide → Long (unpivot)¶

What

Reshape the Johns Hopkins COVID-19 confirmed-cases time series from its native wide layout (one column per day) into long/tidy rows (one row per country-date), using a single template.

Why interesting¶

The JHU CSSE time series was the most-analysed dataset of the pandemic, and it ships in the shape analysts least want: each day is its own column (1/22/20, 1/23/20, … out to 3/9/23), so the file is ~1147 columns wide and every plotting/grouping tool first has to melt it to long form. Unpivoting is normally a pandas.melt / tidyr::pivot_longer step — but bxp does it declaratively, in the template, with no code: one input row fans out to many output rows via multi-row row_rules.

flowchart LR
    W["**1 wide row** — Afghanistan<br/><small>1/22/20 = 0 · 12/31/20 = 52330 · 12/31/21 = 158084</small>"]
    W -->|"row_rules fan-out"| A["Afghanistan · 2020-01-22 · 0"]
    W --> B["Afghanistan · 2020-12-31 · 52330"]
    W --> C["Afghanistan · 2021-12-31 · 158084"]

Edge cases sourced from.

pandas melt / wide-to-long and tidyr pivot_longer exist precisely because wide time-series need reshaping before analysis
country names embed commas ("Korea, South"), so the file is double-quoted

Data source. JHU CSSE COVID-19 Data Repository — time_series_covid19_confirmed_global.csv (archived March 2023). CC BY 4.0. (this slice: 23 countries × the first day + two year-end snapshot columns.)

The trick¶

Unpivot via multi-row row_rules. Each entry in rows: [ … ] emits one output row, pulling a different date column ([12/31/20]) and stamping the matching ISO date as a literal. Three entries → three country-date rows per input country.

Field references with slashes

Date columns are named with slashes; [1/22/20] works as a field reference. Inside an override you reference fields with [..], not $variables.

At full scale¶

bash fetch-full.sh          # downloads the full ~1147-column series into ./full/
bxp-cli --config full.json  # unpivots all ~289 country/region rows → ~867 long rows

The unpivot is the point, not raw volume (the file is only ~289 rows). The full file is 1147 columns wide — comfortably inside bxp's 16384-column cap, so every day-column stays reachable and the run is warning-free; the three snapshot columns this template reads sit at cols 5 / 349 / 714.

Final result¶

sample.csvx turns one Afghanistan row holding 0 | 52330 | 158084 across three columns into three tidy rows:

Afghanistan,2020-01-22,0
Afghanistan,2020-12-31,52330
Afghanistan,2021-12-31,158084

That long shape drops straight into a GROUP BY date or a time-series plot — no melt step, no Python. To add more snapshots, add more rows entries; to unpivot every day you would loop the columns, which is the natural feature boundary (and the reason day-per-column files want this reshape in the first place).

Sample data¶

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

sample.json (config)sample.csvsample.csvx (result)

{
  // Johns Hopkins COVID-19 confirmed-cases time series — the canonical "wide"
  // dataset: one column PER DAY (1/22/20, 1/23/20, …). Analysis tools nearly
  // always need it in long/tidy form (one row per country-date). This template
  // unpivots three year-boundary snapshots into long rows using bxp's
  // multi-row row_rules — one input country row fans out to N output rows.
  conversion_templates: {
    covid_wide_to_long: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",
      // TRICK 0 — country names embed commas ("Korea, South"), so the file is
      // double-quoted.
      csv_text_quote_in:  "double",
      csv_text_quote_out: "double",

      input_schema: {
        $country: "[Country/Region]"
      },

      // TRICK 1 — UNPIVOT (wide → long). Each `rows` entry emits one output
      // row, pulling a different date column and stamping the matching ISO
      // date as a literal. (Inside an override you reference *fields* with
      // [..], not other $variables — note the slash-named columns work as
      // field refs.) One country row in → three country-date rows out.
      row_rules: [
        {
          when: "1 = 1",
          rows: [
            { $date: "'2020-01-22'", $cases: "[1/22/20]" },
            { $date: "'2020-12-31'", $cases: "[12/31/20]" },
            { $date: "'2021-12-31'", $cases: "[12/31/21]" }
          ]
        }
      ],

      output_schema: {
        country:          "$country",
        date:             "$date",
        cumulative_cases: "$cases"
      }
    }
  }
}

Province/State,Country/Region,1/22/20,12/31/20,12/31/21
,Afghanistan,0,52330,158084
,Austria,0,356063,1270811
,Belgium,0,646496,2105343
,Brazil,0,7681032,22291839
,Chile,0,608973,1806494
,Egypt,0,138062,385575
,France,0,2616902,9761814
,Germany,0,1719737,7109182
,Greece,0,138850,1210853
,India,0,10286709,34861579
,Italy,0,2107166,6125683
,Japan,2,235749,1733307
,Kenya,0,96458,295028
,"Korea, South",1,61769,635253
,Mexico,0,1426094,3979723
,Nigeria,0,87607,241513
,Norway,0,49567,394259
,Peru,0,1015137,2296831
,Poland,0,1294878,4108215
,Portugal,0,413678,1389646
,Spain,0,1928265,6294745
,Sweden,0,437379,1314784
,Vietnam,0,1465,1731257

country,date,cumulative_cases
Afghanistan,2020-01-22,0
Afghanistan,2020-12-31,52330
Afghanistan,2021-12-31,158084
Austria,2020-01-22,0
Austria,2020-12-31,356063
Austria,2021-12-31,1270811
Belgium,2020-01-22,0
Belgium,2020-12-31,646496
Belgium,2021-12-31,2105343
Brazil,2020-01-22,0
Brazil,2020-12-31,7681032
Brazil,2021-12-31,22291839
Chile,2020-01-22,0
Chile,2020-12-31,608973
Chile,2021-12-31,1806494
Egypt,2020-01-22,0
Egypt,2020-12-31,138062
Egypt,2021-12-31,385575
France,2020-01-22,0
France,2020-12-31,2616902
France,2021-12-31,9761814
Germany,2020-01-22,0
Germany,2020-12-31,1719737
Germany,2021-12-31,7109182
Greece,2020-01-22,0
Greece,2020-12-31,138850
Greece,2021-12-31,1210853
India,2020-01-22,0
India,2020-12-31,10286709
India,2021-12-31,34861579
Italy,2020-01-22,0
Italy,2020-12-31,2107166
Italy,2021-12-31,6125683
Japan,2020-01-22,2
Japan,2020-12-31,235749
Japan,2021-12-31,1733307
Kenya,2020-01-22,0
Kenya,2020-12-31,96458
Kenya,2021-12-31,295028
"Korea, South",2020-01-22,1
"Korea, South",2020-12-31,61769
"Korea, South",2021-12-31,635253
Mexico,2020-01-22,0
Mexico,2020-12-31,1426094
Mexico,2021-12-31,3979723
Nigeria,2020-01-22,0
Nigeria,2020-12-31,87607
Nigeria,2021-12-31,241513
Norway,2020-01-22,0
Norway,2020-12-31,49567
Norway,2021-12-31,394259
Peru,2020-01-22,0
Peru,2020-12-31,1015137
Peru,2021-12-31,2296831
Poland,2020-01-22,0
Poland,2020-12-31,1294878
Poland,2021-12-31,4108215
Portugal,2020-01-22,0
Portugal,2020-12-31,413678
Portugal,2021-12-31,1389646
Spain,2020-01-22,0
Spain,2020-12-31,1928265
Spain,2021-12-31,6294745
Sweden,2020-01-22,0
Sweden,2020-12-31,437379
Sweden,2021-12-31,1314784
Vietnam,2020-01-22,0
Vietnam,2020-12-31,1465
Vietnam,2021-12-31,1731257

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