6
$\begingroup$

Given this Dataset fragment (copy-paste friendly version at bottom)

enter image description here

Can this query be simplified by referencing named slots directly in the Select logic?

data[All, Values /* Thread][All, All, Apply@Join][All, Select[#VisitStartTime <= #"WEBCAM_TIME" <= #VisitEndTime &]]

enter image description here

Although functional, limitations include: (1) Creates intermediate Datasets; (2) threads VisitStartTime and VisitEndTime, which must then be dropped; (3) Values removes the Key "Morae/..." which must then be reinstered in each row; (4) overall complexity makes it difficult to modify to similar datasets.

An approach based on named slot syntax shown here by WReach looks applicable, but the naive version fails:

data[All, {"Morae/Manual/Emr/Coder1Data" -> 
   Select[#["Master", "VisitStartTime"] <=  #"WEBCAM_TIME" <= #[
       "Master", "VisitEndTime"] &]}]

enter image description here

Even though it is possible to reference the slots in the other branch like this:

data[All, {"Morae/Manual/Emr/Coder1Data" -> {#["Master", 
      "VisitStartTime"], #["Master", "VisitEndTime"]} &}]

enter image description here

Is it possible to combine these path-lookup methods?

INPUT DATA

data = <|"Q001" -> <|"Morae/Manual/Emr/Coder1Data" -> {<|"WEBCAM_TIME" -> 
       TimeObject[{0, 12, 9.4}], 
      "TASK" -> "F1"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 12, 10.1}], 
      "TASK" -> "F1"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 12, 13.1}], 
      "TASK" -> "F1"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 12, 27.9}], 
      "TASK" -> "F1"|>}, 
   "Master" -> <|"VisitStartTime" -> TimeObject[{0, 12, 15.}], 
     "VisitEndTime" -> TimeObject[{0, 38, 49.}]|>|>, 
 "Q002" -> <|"Morae/Manual/Emr/Coder1Data" -> {<|"WEBCAM_TIME" -> 
       TimeObject[{0, 0, 1.9}], 
      "TASK" -> "F25"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 0, 3.5}], 
      "TASK" -> "F25"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 0, 9.7}], 
      "TASK" -> "F25"|>, <|"WEBCAM_TIME" -> TimeObject[{0, 0, 10.8}], 
      "TASK" -> "F25"|>}, 
   "Master" -> <|"VisitStartTime" -> TimeObject[{0, 13, 0.}], 
     "VisitEndTime" -> TimeObject[{0, 22, 27.}]|>|>|> //Dataset
$\endgroup$

1 Answer 1

4
$\begingroup$

There is a complication that prevents us from using a single Select and named slots: we need to form a cross-product of the data and master elements. Even so, the query can be simplified somewhat:

data[All
, Join @@@ Tuples[{#"Morae/Manual/Emr/Coder1Data", {#Master}}] & /*
    Select[#VisitStartTime <= #"WEBCAM_TIME" <= #VisitEndTime &]
]

dataset screenshot

The Join@@@Tuples construction forms the cross-product and collapses each pair of associations into a single association. That leaves us in a position to apply the Select.


Side note: A limitation in JoinAcross

One might think to use JoinAcross to form the cross-product. Unfortunately, it does not handle a vacuous join condition properly:

JoinAcross[{<|a->1|>, <|a->2|>}, {<|b->10|>}, {}]
(* {} *)

Since JoinAcross is advertised as the Mathematica equivalent of a relational join, I would have expected the result to be the same as this:

Join @@@ Tuples[{{<|a -> 1|>, <|a -> 2|>}, {<|b -> 10|>}}]
(* {<|a -> 1, b -> 10|>, <|a -> 2, b -> 10|>} *)

But it is not. I am sorely tempted to call this a bug, but let's go with "limitation" for now :)


Update

If the master data is not required in the output (as mentioned in a comment), things can made simpler because we can eliminate the explicit cross-product. We start by defining a helper function that encapsulates the filtering condition:

filter[data_, master_] :=
  Select[data, master["VisitStartTime"] <= #"WEBCAM_TIME" <= master["VisitEndTime"]&]

This enables us to use a much simpler query:

data[All
, <| "Morae/Manual/Emr/Coder1Data" -> (filter[#"Morae/Manual/Emr/Coder1Data", #Master]&) |>
]

dataset screenshot

If desired, the master data can also be retained as a separate key like this:

data[All
, <| "Morae/Manual/Emr/Coder1Data" -> (filter[#"Morae/Manual/Emr/Coder1Data", #Master]&)
   , "Master" -> "Master"
  |>
]

dataset screenshot

$\endgroup$
5
  • $\begingroup$ Thanks again for the analysis. However, doesn't address limitations (2) and (3), namely VisitStartTime and VisitEndTime are only for use in the Select logic and should not appear in the output, and desired format is <|"Q001" -> {"Morae/Manual/Emr/Coder1Data" -> ...}, ...|>, ie, with Morae... key included. Ideally (2) and (3) should not be post-processing steps. $\endgroup$ Jan 6, 2015 at 17:20
  • $\begingroup$ @alancalvitti Ah yes, my misunderstanding. Does my update help? $\endgroup$
    – WReach
    Jan 6, 2015 at 18:01
  • $\begingroup$ Why does it only return a single row in Q001 (compare to the output in my Q) $\endgroup$ Jan 6, 2015 at 23:12
  • $\begingroup$ @alancalvitti If I run your query against the exhibited input data, I get the same result as my query: the single entry for 00:12:27.9. That is correct since it is the only one between 00:12:15 and 00:38:49. Perhaps your screenshot was taken after using different data? $\endgroup$
    – WReach
    Jan 7, 2015 at 0:17
  • $\begingroup$ You're right, I was A/B'ing to my original input data. Nice solution here, thanks. $\endgroup$ Jan 7, 2015 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.