Dataset collapsing/reducing

Question

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?

Answer 1

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"]
   |>
]

Answer 2

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"]]|> &]

Answer 3

You may use GroupBy and Merge.

ds[GroupBy[{#"A", #"B"} &] /* Values, 
 Merge[Identity] /* Query[{"A" -> First, "B" -> First}]]

Hope this helps