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 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 at 5:32

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


| improve this answer | |
  • 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 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 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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