2
$\begingroup$

The following Dataset

ds = Dataset@*Map[AssociationThread[{"ID", "Date", "Type"} -> #] &]@ {{"C09",SQLDateTime[{2013, 4, 2, 21, 50, 0.}], "D"}, {"C11", SQLDateTime[{2013, 4, 24, 20, 35, 0.}], "T"}, {"C10", SQLDateTime[{2013, 3, 21, 20, 0, 0.}], "T"}, {"C09", SQLDateTime[{2012, 12, 11, 0, 0, 0.}], "V"}, {"C09", SQLDateTime[{2013, 2, 19, 19, 30, 0.}], "D"}, {"C09", SQLDateTime[{2013, 3, 8, 19, 40, 0.}], "T"}, {"C10", SQLDateTime[{2012, 12, 12, 0, 2, 0.}], "V"}, {"C10", SQLDateTime[{2013, 1, 28, 9, 25, 0.}], "T"}, {"C10", SQLDateTime[{2013, 5, 24, 19, 45, 0.}], "D"}, {"C10", SQLDateTime[{2013, 6, 1, 17, 0, 0.}], "T"}, {"C11", SQLDateTime[{2012, 12, 10, 10, 0, 0.}], "V"}, {"C09", SQLDateTime[{2013, 1, 22, 0, 0, 0.}], "T"}, {"C11", SQLDateTime[{2013, 1, 23, 20, 50, 0.}], "T"}, {"C11", SQLDateTime[{2013, 1, 25, 21, 0, 0.}], "D"}, {"C11", SQLDateTime[{2013, 5, 4, 22, 0, 0.}], "V"}, {"C11", SQLDateTime[{2013, 8, 27, 6, 40, 0.}], "T"}}

looks after ds[SortBy[{#"ID" &, #"Date" &}]] like this enter image description here

My question is now, how can I extract two subsequent lines with the same ID, both having Type T?

As it only occurs in the 2nd and 3rd row of ID C10. All other cases, with a D or V in between are not interesting for me (e.g. last three lines).

I assume one has to use the Query functionality, but since I'm still at the beginning of my Mathematica studies, I don't know how.

Improve this question
$\endgroup$

1 Answer 1

Reset to default
2
$\begingroup$ 
ds[SortBy[{#"ID" &, #"Date" &}]][SplitBy[#, {#ID, #Type} &] &][Select[Length[#] == 2 &]] 
{{<|"ID" -> "C10", "Date" -> SQLDateTime[{2013, 1, 28, 9, 25, 0.}], "Type" -> "T"|>, <|"ID" -> "C10", "Date" -> SQLDateTime[{2013, 3, 21, 20, 0, 0.}], "Type" -> "T"|>}}

EDIT

Added Select after WReach, though I don't believe it should only return the First instance.

Improve this answer
$\endgroup$
3
  • $\begingroup$ +1 You could also replace 7 with SelectFirst[Length@# >= 2 &] to address it structurally. $\endgroup$ Commented Dec 4, 2014 at 19:19
  • $\begingroup$ @alancalvitti Sorry for the late reply, I was in vacation. Any idea how to re-establish the nice formating of a Dataset for the result? $\endgroup$ Commented Dec 9, 2014 at 9:13
  • $\begingroup$ Flatten[ds[SortBy[{#"ID" &, #"Date" &}]][SplitBy[#, {#ID, #Type} &] &][Select[Length[#] >= 2 &]]] helps to get back a usable Dataset $\endgroup$ Commented Dec 9, 2014 at 11:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.