0
$\begingroup$

I have this dataset of associations:

data = {<|"A" -> "2017011_230833", 
"X" -> {7.96439, 7.85037, 18.128, 6.57162, 14.596, 8.12374, 18.6779, 19.67001, 21.6372, 22.0001, 1.9807,5.8007},
"Y" -> {20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}, "A"->.....|> }

I would like to select a range of X based on a range in Y and then sum it up. I tried the following from answer and answer but I think I'm stuck with syntax format errors as it doesn't seem to work:

test = <|"A" -> #["A"], 
"X" -> 
 Total@Select[#["X"], 
   FirstPosition[#["Y"], x_ /; 20 <= x <= 23] =!= {} &]|> & /@ data


test = <|"A" -> #["A"], 
    "X" -> 
     Total@Pick[#["X"], 
       20 <= #["Y"] <= 23 & /@ #["Y"]]|> & /@ 
  data

I tried this as well, I sort of knew it would not work but I was hoping this answer could have been helpful:

    test = <|"A" -> #["A"], 
     "X" -> 
      Total[Clip[#["X"], {10, 12}, {0, 0}] & /@ 
        Select[data, 
         20 <= #["Y"] <= 23 &]]|> & /@ data;

Finally, I tried Transpose to select based on values, but I would still want to keep A association as I will need to append them later to a list with matching associations

  test2 = <|Transpose@{#X, #Y}|> & /@ 
  data
test3 = Total@Select[#[[2]] & /@ #, 20 <= #[[1]] <= 23 &] & /@ test2

Ideal outcome would be:

result = {<|"A" -> "2017011_230833", X->"40.51438"|>,....}
$\endgroup$
11
  • $\begingroup$ Maybe <|data, "X" -> Select[Flatten@data[[All, "X"]], 10 <= # <= 12 &]|> $\endgroup$
    – eldo
    Sep 10, 2023 at 22:21
  • $\begingroup$ @eldo it does select the specified range from X, how do I link it to the corresponding range of Y? $\endgroup$ Sep 10, 2023 at 22:28
  • $\begingroup$ <|data, "X" -> Select[Flatten@data[[All, "Y"]], 10 <= # <= 12 &]|> $\endgroup$
    – eldo
    Sep 10, 2023 at 22:31
  • $\begingroup$ @eldo Y has varying range I don't know specific values for. I just know that it corresponds to X positions. $\endgroup$ Sep 10, 2023 at 22:32
  • $\begingroup$ @eldo, <|data, "X" -> Select[Flatten@data[[All, "Y"]], 10 <= # <= 12 &]|> just replaces values for X with Y. $\endgroup$ Sep 10, 2023 at 22:47

2 Answers 2

1
$\begingroup$

You should eliminate the curly brackets:

dt = <|data|>

<|"A" -> "2017011_230833", "X" -> {7.96439, 7.85037, 18.128, 6.57162, 14.596, 8.12374, 18.6779}, "Y" -> {20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}|>

<|dt, "X" -> Total @ Extract[dt["X"], Position[dt["Y"], x_ /; 20 <= x <= 23]]|>

<|"A" -> "2017011_230833", "X" -> 40.5144, "Y" -> {20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}|>

Revision 1 (based upon comment)

data = {<|"A" -> "2017011_230833", "X" -> {7.96439, 7.85037, 18.128, 6.57162, 14.596, 8.12374,  18.6779}, "Y" -> {20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}|>, <|"A" -> "2018011_xxxxxx", "X" -> {17.9, 17.8, 28.128, 16.5, 24.6, 18.1, 18.6}, "Y" -> {20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}|>};

Query[All, <|#, "X" -> Total @ Extract[#["X"], Position[#["Y"], x_ /; 20 <= x <= 23]]|> &] @ data

{<|"A" -> "2017011_230833", "X" -> 40.5144, "Y" -> {20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}|>, <|"A" -> "2018011_xxxxxx", "X" -> 80.328, "Y" -> {20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}|>}

Or, if you want to omit the indices:

res = 
   Query[All /* KeyTake[{"A", "X"}], <|#, "X" -> Total @
       Extract[#["X"], Position[#["Y"], x_ /; 20 <= x <= 23]]|> &] @ data

{<|"A" -> "2017011_230833", "X" -> 40.5144|>, <|"A" ->"2018011_xxxxxx", "X" -> 80.328|>}

res // Dataset

enter image description here

The correct syntax for disjoint ranges would be:

Query[All, <|#, "X" -> Total @ Extract[#["X"], 
   Position[#["Y"], x_ /; (20 <= x <= 23 || 25 <= x <= 26)]]|> &] @ data

{<|"A" -> "2017011_230833", "X" -> 67.316, "Y" -> {20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}|>, <|"A" -> "2018011_xxxxxx", "X" -> 117.028, "Y" -> {20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31}|>}

$\endgroup$
4
  • $\begingroup$ Thanks, seems to work. Having a bit of trouble mapping on all associations - it just duplicates one value. <| "A" -> #"A", "X" -> Total@Extract[data["X"],Position[data["Y"], x_ /; 20 <= x <= 23]]|> & /@ data $\endgroup$ Sep 11, 2023 at 7:57
  • 1
    $\begingroup$ See last revision $\endgroup$
    – eldo
    Sep 11, 2023 at 8:34
  • $\begingroup$ thank you for your help. I tried this as well test = Map[<|"A" -> #["A"], "X" -> Total@Extract[#"X", Position[#"Y", x_ /; 20 <= x <= 23]]|> &, data]. If I wanted to perform multiple summation (X = Y2-Y1), is this correct syntax? test = Map[<|"A" -> #["A"], "X" -> Total @ Extract[#["X"],[Position[#["Y"], x_ /; 20 <= x <= 23], Position[#["Y"], x_ /; 25 <= x <= 29]]]|> &, data] $\endgroup$ Sep 11, 2023 at 8:47
  • 1
    $\begingroup$ See revision - And yes, your Map and my Query do the same thing in this case. $\endgroup$
    – eldo
    Sep 11, 2023 at 9:07
1
$\begingroup$

This is pretty jenky, but I think it gives you what you're asking for.

Yselector = 20 <= # <= 23 &;
<|"A" -> #["A"], "X" -> Total[Pick[#["X"], Yselector /@ #["Y"]]]|> & /@ data

I assume that you intended data to have multiple associations instead of just one association that repeats keys.

$\endgroup$
3
  • $\begingroup$ Yes, it works perfectly. What if I want total to be the sum of one range minus the sum of another range? Will this work? Y1 = 20 <= # <= 23 &; Y2 = 25 <= # <= 30 &; Giving <|"A" -> #["A"], "X" -> Total[Pick[#["X"], Y2]] - Total[Pick[#["X"], Y1]]|> & /@ data $\endgroup$ Sep 12, 2023 at 10:10
  • $\begingroup$ Did you try it? $\endgroup$
    – lericr
    Sep 12, 2023 at 14:27
  • $\begingroup$ Yeah but I forgot to map over Y. So this work, <|"A" -> #["A"], "X" -> Total[Pick[#["X"], Y2 /@ #["Y"]]] - Total[Pick[#["X"], Y1 /@ #["Y"]]]|> & /@ data $\endgroup$ Sep 12, 2023 at 14:41

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.