I have a dataset ds with 3 columns named A, B, C. Columns A and B have repeated values. How can I obtain another dataset that contains a list of the values in C for each of the unique combinations of A and B?
For example,
ds = Dataset[ {<| "A" -> 2, "B" -> 3, "C" -> 100 |>, <| "A" -> 2, "B" -> 4, "C" -> 200 |>, <| "A" -> 2, "B" -> 3, "C" -> 300 |>}] I want to get
Dataset[ {<| "A" -> 2, "B" -> 3, "Clist" -> {100, 300} |>, <| "A" -> 2, "B" -> 4, "Clist" -> {200} |>}] How can I do that?
To get a list of C values for each A/B combination:
ds[GroupBy[{#A, #B}&] /* Values , <| "A" -> Query[First, "A"] , "B" -> Query[First, "B"] , "CList" -> Query[All, "C"] |> ] 
This is not as succinct as SQL's GROUP BY operator, but it does allow us to easily perform multiple aggregations if desired:
ds[GroupBy[{#A, #B}&] /* Values , <| "A" -> Query[First, "A"] , "B" -> Query[First, "B"] , "CList" -> Query[All, "C"] , "CMean" -> Query[Mean, "C"] , "CMin" -> Query[Min, "C"] , "CMax" -> Query[Max, "C"] |> ] 
Dataset[] at some point... I keep picking up new stuff from your answers on this topic. :) $\endgroup$ Failed to find a duplicate:
by = {"A", "B"}; Values @ GroupBy[ds, Query[by], MapAt[First, List /@ by] @* Merge[Identity]] 
or:
ds // GroupBy[Query[{"A", "B"}] -> (#C &)] // KeyValueMap[<|#, "Clist" -> #2|> &] ds // GroupBy[Query[{"A", "B"}]] // KeyValueMap[<|#, "Clist" -> #2[[;; , "C"]]|> &] Merge[Mean] is nice in a way that it will automatically reduce A and B lists to the repeating element. Here we have a more general case where I have to use First manually. $\endgroup$ You may use GroupBy and Merge.
ds[GroupBy[{#"A", #"B"} &] /* Values, Merge[Identity] /* Query[{"A" -> First, "B" -> First}]] 
Hope this helps
