Table Un Pivot

Check this out first

Script created by Nikhil to unpivot tables:

• https://answerquest.github.io/table-unpivot/ Live verson
• https://github.com/answerquest/table-unpivot Code on github

Problem statement

While the tool is almost accomplishing the task of unpivoting, there's a bug. It's sorting the entries in some way, not preserving the original sequence. Example:

Sample column structure:

xx	01	02	..	09	10	11	..	28	29	30	31
1999.01	x	x	..	x	x	x	..	x	x	x	x
1999.02	y	y	..	y	y	y	..	y

This is converted into a "JSON Dictionary" format using a fork of a tool found on the web called "Mr.Data Converter".

Side note: Nikhil had to make a fork of "Mr.Data Converter" and alter so that it doesn't set blank cells (ie, data unavailable, like rainfall for 29th to 31st of Feb) as 0 value which was distorting the data where there are genuine zero's in other cells and they should not be mixed up with null entries.

{
  "1999.01": {
    "10": 0, >> starting from 10th instead of 1st
    "11": 0,
    "12": 0,
...
    "30": 0,
    "31": 0,
    "01": 0, >> 1st, 2nd etc start after last date of month
    "02": 0,
    "03": 0,
...

There is a bug here. It interprets 10, 11, etc as numbers so puts them first, and 01, 02 etc as strings so puts them later. And then the output becomes like:

1999.01.10,0 >> starting from 10th instead of 1st
1999.01.11,0
1999.01.12,0
...
1999.01.30,0
1999.01.31,0
1999.01.01,0 >> 1st, 2nd etc start after last date of month
1999.01.02,0
1999.01.03,0
...

This is owing to the converted json being ordered in such a way.

But we want it to NOT do ANY ordering at all. The initial tabular data we started with.. the order must be maintained precisely in that way. Columns must not be re-arranged in the conversion process.

So, your mission, should you choose to accept it:

Aim

Alter the Mr.Data Converter script used here so it doesn't sort the columns' entries in its output json

OR

Re-do the whole thing to take a 2D table and produce unpivoted output.

Links

• https://blogs.worldbank.org/opendata/unpivoting-data-excel-open-refine-and-python - Unpivoting Data with Excel, Open Refine and Python