I'm trying to find a way to convert a GroupBy field in a dataset into a column after aggregation, but I'm clearly failing to this idiomatically, although I found a couple of solutions, see below.

Consider the COVID-19 dataset:

covid = ResourceData["Epidemic Data for Novel Coronavirus COVID-19"]

and let's say I want to look at just the number of confirmed cases and deaths by country. It's easy to do in a very idiomatic way:

covid[GroupBy["Country"], Total, {"ConfirmedCases", "Deaths"}]

enter image description here

But let's say I now want Spain etc. to be a column, not a row name. I can do it with KeyValueMap, but it's a bit convoluted:

covid[GroupBy["Country"], Total, {"ConfirmedCases", "Deaths"}][
 KeyValueMap[Join[<|"Country" -> #1|>, #2] &]]

enter image description here

Another idea I had was to look at

covid[GroupBy["Country"] /* Values, Total, {"Country", "ConfirmedCases", 

which unfortunately doesn't work as Country is also aggregated, enter image description here

and to try to fix it by applying each of the aggregating function in a separate query:

covid[GroupBy["Country"] /* Values, <|
  "Country" -> First /* "Country",
  "ConfirmedCases" -> Total /* "ConfirmedCases", 
  "Deaths" -> Total /* "Deaths"

which works as expected, but is also not very pleasant to the eye.

Am I missing something obvious here?

  • $\begingroup$ Transpose @ res[ GroupBy["Country"], Total, {"ConfirmedCases", "Deaths"} ] ? $\endgroup$
    – LouisB
    Apr 10, 2020 at 5:08
  • $\begingroup$ It doesn't do what I want - it returns a dataset of the form <|"ConfirmedCases" -> <|Entity["Country", "Spain"] -> TimeSeries[...]...|>,...|> $\endgroup$
    – Victor K.
    Apr 10, 2020 at 5:32

1 Answer 1


The most valuable advice is to Normal your dataset and look at the underlying associations instead of working with dataset directly. This instantly tells you what you want to do

normalized dataset

You see you have your countries as key with your aggregated measurements as values. Therefore, a simple KeyValueMap should be all it takes to transform the data and add your country key into the existing measurements

 Function[{country, measures}, <|"Country" -> country, measures|>],
 covid[GroupBy["Country"], Total, {"ConfirmedCases", "Deaths"}]

and you get


  • 1
    $\begingroup$ Thanks @halirutan! This is very similar to my own use of KeyValueMap, but it's cleaner, and I like the use of explicit Function to document what's going on. I also didn't know that you can implicitly join associations like this: a = <|1 -> 2, 3 -> 4|>; <|5 -> 6, a|> produces <|5 -> 6, 1 -> 2, 3 -> 4|>; is it described anywhere formally? (Can't find it on Association help page). $\endgroup$
    – Victor K.
    Apr 21, 2020 at 16:47
  • 2
    $\begingroup$ I asked the devs if there is some documentation about this idiom. I never saw one but I use it for a long time. I guess at some point I just thought that this should work and tried it out.. along the lines "what else should Mathematica do with with <|5 -> 6, a|>?" $\endgroup$
    – halirutan
    Apr 22, 2020 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.