I have a search which produces a stats with 2 columns, and n = 3k , where k is an integer, rows. The second column is a simple integer, and the rows form logical triplets, like so:
foo_x 2
foo_y 2
foo_z 5
bar_x 12
bar_y 5
bar_z 9
...
How can I transform my result into a form with k rows and 3+1 columns, like so?:
x y z
foo 2 2 5
bar 12 5 9
Normally I would achieve this by changing the form of the query that produces the n rows into something more sensible. In this case, I'm not sure if that's possible - read on for why -
Extra information regarding source of these 3k rows
I have a list of users, and k properties of those users, like so:
fishs_age dogs_age cats_age babys_age
user_a
user_b
user_c
...
For each property, I classify 3 risk labels (they need to be separate so I can later sum them):
| foreach fishs_age,
dogs_age,
cats_age [eval <<FIELD>>_hi = if(<<FIELD>> >= 9, 1, 0) ,
<<FIELD>>_md = if(<<FIELD>> >= 4 AND <<FIELD>> < 9, 1, 0) ,
<<FIELD>>_lo = if(<<FIELD>> >= 2 AND <<FIELD>> < 4, 1, 0)]
I then calculate sum s and flip the axes in hopes to make a chart:
| stats sum(*_lo), sum(*_md), sum(*_hi)
| rename sum(*) as *
| transpose
And this is the source of the original dataset
foo_x 2
foo_y 2
foo_z 5
bar_x 12
bar_y 5
bar_z 9
...
i.e.
fishs_age_hi 2
fishs_age_md 5
fishs_age_lo 6
dogs_age_hi 3
dogs_age_md 4
dogs_age_lo 12
...
Again: what I want is to transform this into:
x y z
foo 2 2 5
bar 12 5 9
i.e.
hi md lo
fishs_age 2 2 5
dogs_age 12 5 9
... View more