How to reformat a Dataset[]

Question

I have a dataset jdataSDG1 and like to reformat it as jdataSDG2.

rawdataSDG1 ={
   {"country", "indicator", 2001., 2002.},
   {"Bhutan","consumption",76.9, 78.7},
   {"Bhutan", "GDP",36.9,"Missing[]"}, 
   {"Lao PDR","consumption",79.9, 85.7},
   {"Lao PDR", "GDP", "Missing[]",55.},
   {"Malaysia", "consumption", 174.6, 101.8},
   {"Malaysia", "GDP",72.6, 74.6}
            };

jdataSDG1 = 
 Join @@ Map[
  a \[Function] 
  Dataset[AssociationThread[First@a, #] & /@ 
  Rest[a]]][{rawdataSDG1}];

jdataSDG2 = {
        {"time", "country ", "indicator", "data"},
        {2001., "Bhutan", "consumption", 76.9},
        {2001., "Bhutan", "GDP", 36.9}, 
        {2001., "Lao PDR", "consumption", 79.9},
        {2001., "Lao PDR", "GDP", "Missing[]"}, 
        {2001., "Malaysia", "consumption", 174.6},
        {2001., "Malaysia", "GDP", 72.6},
        {2002., "Bhutan", "consumption", 78.7},
        {2002., "Bhutan", "GDP", "Missing[]"},
        {2002., "Lao PDR", "consumption", 85.7},
        {2002., "Lao PDR", "GDP", 55.}, 
        {2002., "Malaysia", "consumption", 101.8},
        {2002., "Malaysia", "GDP", 74.6}
       };

Note that I manually created jdataSDG2.

Answer 1

Update: It turns out it is easier to process the original input rawdataSDG1 into the desired form:

keys = First[rawdataSDG1] /. x_?NumericQ :> ToString[Round@x];

f = Function[a, {#, #2, "time" -> a[[1]], "data" -> a[[2]]}] /@ {##3} &;

Dataset[Join @@ Values @ GroupBy[Rest @ rawdataSDG1, #[[{1, 2}]] &, 
    Map[Association] @* Apply[f] @* (Thread[keys -> #] &) @* First]]

Original answer:

There must be a shorter/cleaner way ... but the following gives the desired result:

jdataSDG1[GroupBy[{#country, #indicator} &] /* Transpose /* 
   Map[KeyValueMap[<|"country" -> #[[1]], "indicator" -> #[[2]], #2|> &]],
  First, 
  KeySelect[StringMatchQ[NumberString]] /* 
    KeyValueMap[Association[{"time" -> #, "data" -> #2}] &]]

Answer 2

To get predictable formatting results use String-keys:

header = ToString /@ ReplaceAll[x_Real :> Round @ x] @ rawdataSDG1[[1]] 

Map header and use the inbuilt Missing[_]

odat = Map[AssociationThread[header, #] &, rawdataSDG1[[2 ;;]]] /. 
   "Missing[]" -> Missing[_];

odat // Dataset[#, Alignment -> {"2001" -> Right, "2002" -> Right}] &

The transformation to the wanted result is quite simple:

d1 = Query[All, {<|#, "time" -> "2001", "data" -> #"2001"|>} &] @ odat;
d2 = Query[All, {<|#, "time" -> "2002", "data" -> #"2002"|>} &] @ odat;

ndat = Join[d1, d2] // Map[KeyDrop[{"2001", "2002"}]];

ndat // Dataset[#, Alignment -> {"data" -> Right}] &