2
$\begingroup$

Good Morning

I have the following dataset:

Input

And I try to reach this output:

Output

  • key: only one row per key
  • text: join all texts of the rows with the same key (but no duplicates)
  • min: take the lowest value (but not empty) of the rows with the same key
  • max: take the highest value (but not empty) of the rows with the same key

I used dataInput[GroupBy[{#key} &]] but then I got stuck. I tried to apply Union,Max and Min for individual rows but somehow I don't manage to generalize it.

Other attempts:

  • dataInput[GroupBy[#key &], MaximalBy[#max &]&&MinimalBy[#min&] // Values // Flatten
  • Query[GroupBy["key"], Catenate, "text"] @ dataInput
  • JoinAcross[dataInput, Key[key], "Inner", KeyCollisionFunction -> Function[x, Max[x]]]
$\endgroup$
  • $\begingroup$ Would help for you to post your dataset code as well. $\endgroup$ – Edmund Apr 16 '16 at 10:32
5
$\begingroup$
dataset = Dataset[{
   <|"key" -> "abc", "text" -> "h", "min" -> 1, "max" -> 5|>,
   <|"key" -> "abc", "text" -> "a", "min" -> 1, "max" -> 5|>,
   <|"key" -> "aaa", "text" -> "h", "min" -> 2, "max" -> 3|>,
   <|"key" -> "aaa", "text" -> "e", "min" -> 2, "max" -> 4|>,
   <|"key" -> "aaa", "text" -> "a", "min" -> 1|>,
   <|"key" -> "aaa", "text" -> "a", "max" -> 4|>}]

enter image description here

dataset2 = Merge[#, DeleteDuplicates] & /@ Values[GroupBy[dataset, Key["key"]]]

enter image description here

dataset2[All, {"key" -> First, "min" -> Min, "max" -> Max}]

enter image description here

$\endgroup$
  • $\begingroup$ It should really be as simple as dataset[GroupBy["key"],Merge[{"text"->DeleteDuplicates,"min" -> Min, "max" -> Max}]]. Hope Merge will be improved in the future. $\endgroup$ – swish Apr 16 '16 at 12:15
1
$\begingroup$

Given:

dataset = Dataset[{
   <|"key" -> "abc", "text" -> "h", "min" -> 1, "max" -> 5|>,
   <|"key" -> "abc", "text" -> "a", "min" -> 1, "max" -> 5|>,
   <|"key" -> "aaa", "text" -> "h", "min" -> 2, "max" -> 3|>,
   <|"key" -> "aaa", "text" -> "e", "min" -> 2, "max" -> 4|>,
   <|"key" -> "aaa", "text" -> "a", "min" -> 1|>,
   <|"key" -> "aaa", "text" -> "a", "max" -> 4|>}];

Then the following examples will work in Mathematica 10.1 and later:

dataset[
  GroupBy["key"] /* Values
, Transpose /* Query[{"key" -> First, "min" -> Min, "max" -> Max}]
]

dataset screenshot

dataset[
  GroupBy["key"] /* Values
, <| "key" -> First /* "key"
   , "text" -> Query[All, "text"]
   , "min" -> Query[Min, "min"]
   , "max" -> Query[Max, "max"]
   |>
]

dataset screenshot

This second form is less concise, but allows for more general transformations such as renaming columns, introducing new columns that were not present in the source data, or computing values that are dependent upon multiple source columns.

$\endgroup$

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.