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
orDataset
.
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 Query
s 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
Merge
d 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 *)