4
$\begingroup$

I have a dataset comparable to the following (you may wrap it in Dataset if you like). I am looking for a relatively simple way to query it and extract information.

Sample dataset

ranStr[] := StringJoin@RandomChoice[CharacterRange["a", "z"], 5]

SeedRandom[321]
asc = 
  AssociationThread[
   CharacterRange["a", "e"],
   Table[
    Association@Table[
     With[{n = RandomInteger[{10, 20}]},
       ranStr[] -> <|
         "x" -> RandomInteger[10, n], 
         "y" -> RandomInteger[10, n], 
         "c" -> RandomInteger[7], 
         "d" -> RandomWord[]
       |>
      ],
      {RandomInteger[3, 6]}
     ],
     {5}
   ]
  ];

Description of the dataset structure

Let me describe it. First, we have two nested levels of associations, but this is included just to mimic an obstacle in the real data I have. What is important is the 3rd, innermost level, which contains the actual data in the keys "x", "y", "c", "d".

At the innermost level, "x" and "y" are a pair of vectors of exactly the same length. The $i$th element of "x" goes with the $i$th element of "y", so it might have been better to store them like this:

asc // Query[1, 1, {"x", "y"} /* Transpose]

(* {<|"x" -> 10, "y" -> 10|>, <|"x" -> 5, "y" -> 9|>, <|"x" -> 6, "y" -> 7|>, ... *)

But that is not how they are stored in this dataset.

"c" is a number which represents a threshold value.

"d" is just some arbitrary additional data to mimic the actual dataset I have better. In there real data, there are several such keys, so repeating their names in the Query just to retain them is very inconvenient.

The task

  • Select those values of "x" where the corresponding value of "y" is greater than "c".
  • Ideally, retain "c" and "d" in the data.
  • Ideally, use Query or Dataset.

Example output

Element 1,1 of the resulting dataset should look like the output of this:

xy = asc // Query[1, 1, {"x", "y"} /* Transpose];
c = asc // Query[1, 1, "c"];
d = asc // Query[1, 1, "d"];

<|"x" -> (xy // Query[Select[#y > c &], "x"]), "c" -> c, "d" -> d|>
(* <|"x" -> {10, 5, 6, 2, 7, 3, 2, 7, 10, 7}, "c" -> 5, "d" -> "bide"|> *)

Or like the output of this:

<|"xy" -> (xy // Query[Select[#y > c &]]), "c" -> c, "d" -> d|>

Or like the output of this:

<|"x" -> (xy // Query[Select[#y > c &], "x"]), 
  "y" -> (xy // Query[Select[#y > c &], "y"]), 
  "c" -> c, "d" -> d|>

What I am looking for is a relatively simple and intuitive Query or combination of Querys that achieves this.

The problems with doing something similar to how the example output is constructed above is:

  • It involves splitting the dataset into parts, which would need to be Merged back together. The two outer nested levels of associations make that inconvenient.
  • Repeating the names of keys like "d" to retain them is very inconvenient.

The following would be a working but rather ugly solution that I am not happy with:

asc // 
 Query[All, All, <|#, "xy" -> KeyTake[{"x", "y"}][#]|> & /* KeyDrop[{"x", "y"}]] (* bundle x and y together *) //
 Query[All, All, {"xy" -> Transpose}] (* rearrange xy *) //
 Query[All, All, <|#, With[{c = #c}, "xy" -> Select[#xy, #x > c &]]|> &] (* filter with a threshold of x *) //
 Query[1, 1] (* remove this to see the whole output *)
$\endgroup$
6
$\begingroup$

Here is a way that produces output that conforms to the first option:

val = asc //
  Query[
    All
  , All
  , <| "x" -> Pick[#x, Positive[#y-#c]], KeyDrop[#, {"x", "y"}] |> &
  ];

val[[1, 1]]

(* <|"x" -> {10, 5, 6, 2, 7, 3, 2, 7, 10, 7}, "c" -> 5, "d" -> "bide"|> *)
| improve this answer | |
$\endgroup$

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.