2
$\begingroup$

I am quite new to associations, and I have been stumbling multiple times over the same issue: I want to make calculations on a GroupedBy list of associations and merge the result with the original list of associations/dataset (I'd prefer not to use Dataset since it seems to be quite slow). However, I feel like through the grouping I lose information I need to combine with the original list.

To have a practical example, I have these two association lists, where ds1 represents the start times for each j, and ds2 represents the stop times for each j:

ds1={
 <|"j" -> 2, "label" -> "start", "t" -> 27.3, "ts" -> 27.3|>,
 <|"j" -> 1, "label" -> "start", "t" -> 35.7, "ts" -> 35.7|>,
 <|"j" -> 2, "label" -> "start", "t" -> 71.6, "ts" -> 71.6|>,
 <|"j" -> 1, "label" -> "start", "t" -> 80.7, "ts" -> 80.7|>,
 <|"j" -> 2, "label" -> "start", "t" -> 117.7, "ts" -> 117.7|>,
 <|"j" -> 1, "label" -> "start", "t" -> 127., "ts" -> 127.|>
}
ds2={
 <|"j" -> 2, "label" -> "stop", "t" -> 0.1, "te" -> 0.1|>,
 <|"j" -> 1, "label" -> "stop", "t" -> 27.6, "te" -> 27.6|>,
 <|"j" -> 2, "label" -> "stop", "t" -> 35.9, "te" -> 35.9|>,
 <|"j" -> 1, "label" -> "stop", "t" -> 71.9, "te" -> 71.9|>,
 <|"j" -> 2, "label" -> "stop", "t" -> 80.9, "te" -> 80.9|>,
 <|"j" -> 1, "label" -> "stop", "t" -> 118., "te" -> 118.|>,
 <|"j" -> 2, "label" -> "stop", "t" -> 127.2, "te" -> 127.2|>
}

The task is to obtain new columns in ds1 which gives the duration of each j; i.e. the difference between the stop time and start time for each entry, as well as the opposite difference between start and stop times. The catch in this example is, that it is neither guaranteed that the first j is always starting first (e.g. both j are stoping first); nor is it guaranteed that the last j is always stoping (e.g. the last j=1 has only started), so some elements might not end up with a valid difference, which is fine.

My current approach at a solution is first joining and sorting the lists by time t. Afterwards I can group it by j and do the subtractions:

dsjoin = SortBy[Join[ds1, ds2], #["t"] &]
Query[GroupBy["j"], Transpose@{#label & /@ #[[;; -2]], Differences[#t & /@ #]} &]@dsjoin

From this I obtain:

<|
 2 -> {{"stop", 27.2}, {"start", 8.6}, {"stop", 35.7}, {"start", 9.3}, {"stop", 36.8}, {"start", 9.5}},
 1 -> {{"stop", 8.1}, {"start", 36.2}, {"stop", 8.8}, {"start", 37.3}, {"stop", 9.}}
|>

Here, the durations are the start elements. Now I got stuck, I don't see a way to join this association with the original ds1. Is there a way to "unravel" the result within the Query again and match them with the corresponding elements in ds1?

Edit:

Here is the expected output:

ds1={
 <|"j" -> 2, "label" -> "start", "t" -> 27.3, "ts" -> 27.3, "dt" -> 8.6, "dtinactive" -> 27.2|>,
 <|"j" -> 1, "label" -> "start", "t" -> 35.7, "ts" -> 35.7, "dt" -> 36.2, "dtinactive" -> 8.1|>,
 <|"j" -> 2, "label" -> "start", "t" -> 71.6, "ts" -> 71.6, "dt" -> 9.3, "dtinactive" -> 35.7|>,
 <|"j" -> 1, "label" -> "start", "t" -> 80.7, "ts" -> 80.7, "dt" -> 37.3, "dtinactive" -> 8.8|>,
 <|"j" -> 2, "label" -> "start", "t" -> 117.7, "ts" -> 117.7, "dt" -> 9.5, "dtinactive" -> 36.8|>,
 <|"j" -> 1, "label" -> "start", "t" -> 127., "ts" -> 127., "dt" -> Null, "dtinactive" -> 9.|>
 }
$\endgroup$
2
  • 1
    $\begingroup$ What do you want your final result to look like. Put together the expecteed result manually and show us that. $\endgroup$
    – lericr
    Commented Aug 8, 2023 at 17:43
  • $\begingroup$ Good point! I added the expected output. $\endgroup$ Commented Aug 9, 2023 at 13:31

2 Answers 2

1
$\begingroup$

Here is an approach. I've done some normalization/simplification of your data along the way, but hopefully you can trace through what's going on. The general strategy goes like this:

  • split apart your j->1 and j->2 records
  • process the timestamp data for each j group (described further below)
  • destructure the groups back into your original "flat" structure

To process the time data, we inspect start times and stop times in triples to compute inactive durations and active durations that pivot around a start time (this is just how I'm interpreting your expected output). So, given a list of start times and a list of stop times, we inspect the initial values in each list and determine how to compute a pivot record (a name I just invented). It will simplify matters to define helper functions rather than try to inline everything.

PivotRecord[stop1_, start_, stop2_] := 
  <|"ts" -> start, "dtinactive" -> start - stop1, "dtactive" -> stop2 - start|>

I've changed the labels a bit just to keep things clear in my head. This is just a dumb helper--it doesn't do any validity checks.

Now we'll define a function that works on a start-time list and a stop-time list. As we work through these lists, we'll accumulate a result list of pivot records.

Here is the case where the earliest timestamp is a stop time:

PivotRecords[accum_List, starts : {a_, ___}, stops : {b1_, b2_, ___}] := 
  PivotRecords[Append[accum, PivotRecord[b1, a, b2]], Rest@starts, Rest@stops] /; b1 <= a < b2

I've made assumptions about which durations are allowed to be zero, but you may need to reconsider this for your own data. I'm also not handling certain "bad data" cases, so again you may need to reconsider this based on whatever error cases you may have in your data. We accumulate a record and discard the first start time and first stop time as they won't be needed any more.

Now the case where the earliest time is a start time.

PivotRecords[accum_List, starts : {a_, ___}, stops : {b1_, __}] := 
  PivotRecords[Append[accum, PivotRecord[-Infinity, a, b1]], Rest@starts, stops] /; a < b1

Pretty similar. We can discard the start time, but we need to keep that first stop time (because presumably it will be needed to determine the inactive duration for a later start time).

Now let's handle the terminal cases.

PivotRecords[accum_List, starts : {}, stops : {b___}] := accum;
PivotRecords[accum_List, starts : {a_}, stops : {}] := 
  Append[accum, PivotRecord[-Infinity, a, Infinity]];
PivotRecords[accum_List, starts : {a_}, stops : {b_}] := 
  Append[accum, PivotRecord[-Infinity, a, b]] /; a < b;
PivotRecords[accum_List, starts : {a_}, stops : {b_}] := 
  Append[accum, PivotRecord[b, a, Infinity]] /; b <= a;

Now we need a way to get the processing started. Since we start with an association, we'll just create an override for that.

PivotRecords[assoc_Association] := PivotRecords[{}, assoc["ts"], assoc["te"]]

Okay, now we need to prep our data for processing.

data = Merge[Sort]@*KeyDrop[{"j", "label", "t"}] /@ GroupBy[Join[ds1, ds2], #["j"] &]

(* <|2-><|ts->{27.3,71.6,117.7},te->{0.1,35.9,80.9,127.2}|>,1-><|ts->{35.7,80.7,127.},te->{27.6,71.9,118.}|>|> *)

For clarity I got rid of redundant or unnecessary data--you'll have to decide on the actual format you want.

PivotRecords /@ data

(* <|2->{<|ts->27.3,dtinactive->27.2,dtactive->8.6|>,<|ts->71.6,dtinactive->35.7,dtactive->9.3|>,<|ts->117.7,dtinactive->36.8,dtactive->9.5|>},
     1->{<|ts->35.7,dtinactive->8.1,dtactive->36.2|>,<|ts->80.7,dtinactive->8.8,dtactive->37.3|>,<|ts->127.,dtinactive->9.,dtactive->\[Infinity]|>}|> *)

This is job done as far as I'm concerned, but to get closer to your expected output, we can do this:

Catenate[KeyValueMap[Map[Prepend["j" -> #1], #2] &, PivotRecords /@ data]]
$\endgroup$
1
  • $\begingroup$ Awesome, thank you! That indeed does the job. It is more involved than I initially imagined, it took me some time to understand the solution. And I learned some new bits about Mathematica along the way. $\endgroup$ Commented Aug 11, 2023 at 12:37
0
$\begingroup$
SortBy[Join @@ 
  Values[Query[GroupBy["j"], 
     Append[#2, {"dtinactive" -> #2["t"] - #1["t"], 
          "dt" -> (If[# == 0, Null, #] &@(#3["t"] - #2["t"]))}] & @@@ 
       Partition[
        If[Last[#]["label"] === "start", Append[#, Last[#]], #], 3, 
        2] &]@dsjoin], #["t"] &]

{<|"j" -> 2, "label" -> "start", "t" -> 27.3, "ts" -> 27.3,
"dtinactive" -> 27.2, "dt" -> 8.6|>, <|"j" -> 1, "label" -> "start",
"t" -> 35.7, "ts" -> 35.7, "dtinactive" -> 8.1, "dt" -> 36.2|>, <|"j" -> 2, "label" -> "start", "t" -> 71.6, "ts" -> 71.6, "dtinactive" -> 35.7, "dt" -> 9.3|>, <|"j" -> 1, "label" -> "start", "t" -> 80.7, "ts" -> 80.7, "dtinactive" -> 8.8, "dt" -> 37.3|>, <|"j" -> 2, "label" -> "start", "t" -> 117.7, "ts" -> 117.7, "dtinactive" -> 36.8, "dt" -> 9.5|>, <|"j" -> 1, "label" -> "start", "t" -> 127., "ts" -> 127., "dtinactive" -> 9., "dt" -> Null|>}

$\endgroup$

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.