1
$\begingroup$

I have a large Dataset filled with data from a SQL DB. I sort the set via name and date with the SelectBy function and get something like this:enter image description here

In the end, I want a reduced Dataset with only one entry (with the youngest SQLDateTime) per "ID" (first column). My question is now, how can I select the last occurrence of each "ID" listed in the first row?

Sample Data

$dataset = Dataset @* Map[AssociationThread[{"ID", "Date", "Value1", "Value2"} -> #]&] @
  { {"CAV00009", SQLDateTime[{2012, 12, 11, 0, 0, 0.}], 1., 31.}
  , {"CAV00009", SQLDateTime[{2013, 1, 22, 0, 0, 0.}], 1., 139.}
  , {"CAV00009", SQLDateTime[{2013, 2, 19, 19, 30, 0.}], Null, 720.}
  , {"CAV00009", SQLDateTime[{2013, 3, 8, 19, 40, 0.}], Null, 720.}
  , {"CAV00009", SQLDateTime[{2013, 4, 2, 21, 50, 0.}], Null, 720.}
  , {"CAV00010", SQLDateTime[{2012, 12, 12, 0, 2, 0.}], 1., 30.}
  , {"CAV00010", SQLDateTime[{2013, 1, 28, 9, 25, 0.}], 1., 123.9}
  , {"CAV00010", SQLDateTime[{2013, 3, 21, 20, 0, 0.}], Null, 720.}
  , {"CAV00010", SQLDateTime[{2013, 5, 24, 19, 45, 0.}], Null, 786.}
  , {"CAV00010", SQLDateTime[{2013, 6, 1, 17, 0, 0.}], Null, 786.2}
  , {"CAV00011", SQLDateTime[{2012, 12, 10, 10, 0, 0.}], 1., 30.}
  , {"CAV00011", SQLDateTime[{2013, 1, 23, 20, 50, 0.}], 1., 139.}
  , {"CAV00011", SQLDateTime[{2013, 1, 25, 21, 0, 0.}], 2, 720.}
  , {"CAV00011", SQLDateTime[{2013, 4, 24, 20, 35, 0.}], Null, 732.}
  , {"CAV00011", SQLDateTime[{2013, 5, 4, 22, 0, 0.}], Null, 786.}
  , {"CAV00011", SQLDateTime[{2013, 8, 27, 6, 40, 0.}], Null, 720.}
}
$\endgroup$
  • $\begingroup$ You need something like ds[GroupBy[#ID &]][All, Last], where ds is your data set. Generally, it is better to provide explicit small sample in your question, to make it self-contained. $\endgroup$ – Leonid Shifrin Nov 10 '14 at 13:02
3
$\begingroup$

This will do the trick:

$dataset[GroupBy[#ID &] /* Values, Last]

To see why, let's build it up step-by-step. First, we will use GroupBy to gather together all of the rows for each ID:

$dataset[GroupBy[#ID &]]

dataset screenshot

Next, we will extract the last element from each group:

$dataset[GroupBy[#ID &], Last]

dataset screenshot

This is almost our desired result, except that we still have the lingering grouping keys from the first step. We remove them using Values:

$dataset[GroupBy[#ID &] /* Values, Last]

dataset screenshot

This is our final result.

What if the original data were unsorted?

If we had no guarantee that the original data was presented in sorted order, we can still find the latest row in each group using MaximalBy:

$dataset[GroupBy[#ID &] /* Values, MaximalBy[#Date &] /* First]

dataset screenshot

Note the use of First in the subexpression MaximalBy[...] /* First. This is necessary because MaximalBy always returns a list of all elements that are maximal. The use of First is arbitrary, and could just as easily be Last. It only makes a difference if there a multiple maximal entries. If multiple maxima are a possibility, and we care about which one we select, then we should consider expanding the list of inspected components to reflect our preference (e.g. MaximalBy[{#Date, #Value1, #Value2}&]).

$\endgroup$
  • $\begingroup$ I'm really impressed by this forum! You even made my question better by adding a usable sample. The answers works of course. And the last idea with the MaximalBy is very useful. $\endgroup$ – Lea Nov 11 '14 at 8:46
  • $\begingroup$ There is just one question left, why do you use $dataset instead of dataset? $\endgroup$ – Lea Nov 11 '14 at 8:48
  • $\begingroup$ @Lea As to the dollar sign in $dataset, see the second FAQ in my profile. $\endgroup$ – WReach Nov 11 '14 at 14:32
  • $\begingroup$ @WReach, what if you're using 3rd party APIs that either also use $prefix or start with Uppercase? $\endgroup$ – alancalvitti Dec 4 '14 at 17:32
  • $\begingroup$ @alancalvitti To resolve name collisions, I usually choose one of two strategies. If the number of uses of a colliding name is small, then I just use the fully qualified name. If there are a large number of references, then I define a short alias instead. While I can think a few instances where I have had to deal with a function name collision, I can't think of a case where I have had to deal with colliding global variables. Global variable names tend to be very specialized. $\endgroup$ – WReach Dec 4 '14 at 18:14

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.