How do I get the average of all the individual rows (like the addtotals
but average) and append those values as a column (like appendcols
) dynamically
Some simple data to work with
| makeresults
| eval data = "
1 2017-12 A 155749 131033 84.1;
2 2017-12 B 24869 23627 95;
3 2017-12 C 117618 117185 99.6;
"
| makemv delim=";" data
| mvexpand data
| rex field=data "(?<serial>\d)\s+(?<date>\d+-\d+)\s+(?<type>\w)\s+(?<attempts>\d+)\s+(?<successfullAttempts>\d+)\s+(?<sr>\d+)"
| fields + date serial type attempts successfullAttempts sr
| search serial=*
| eval x=round((attempts+successfullAttempts)/2,0)
Gives the following:
date serial type attempts successfullAttempts sr _time x
1 2017-12 1 A 155749 131033 84 2018-04-24 10:01:20 143391
2 2017-12 2 B 24869 23627 95 2018-04-24 10:01:20 24248
3 2017-12 3 C 117618 117185 99 2018-04-24 10:01:20 117402
X gets the average of 2 columns attempts
and successfullAttempts
but how do I make this dynamic so that I can get the average of all columns except for the 1st column that would have the date?
I was thinking eval x = avg(*)
but that does not work.
What I am looking for is addtotals but instead of adding all the values I want to get average. Maybe I need to use addtotals and then divide by the number of columns I added. I wonder if there is a way to count the number of columns?
Add this:
| eval _col_count="0"
| eval _col_sum="0"
| foreach *
[ eval col_min = min(col_min, '<<FIELD>>')
| eval col_max = max(col_max, '<<FIELD>>')
| eval _col_count = _col_count + if(isnum('<<FIELD>>'), 1, 0)
| eval _col_sum = _col_sum + if(isnum('<<FIELD>>'), '<<FIELD>>', 0)]
| eval col_avg = _col_sum / _col_count
Add this:
| eval _col_count="0"
| eval _col_sum="0"
| foreach *
[ eval col_min = min(col_min, '<<FIELD>>')
| eval col_max = max(col_max, '<<FIELD>>')
| eval _col_count = _col_count + if(isnum('<<FIELD>>'), 1, 0)
| eval _col_sum = _col_sum + if(isnum('<<FIELD>>'), '<<FIELD>>', 0)]
| eval col_avg = _col_sum / _col_count
@woodcock your solutions worked for me like a charm.
tks, I did find another way
...addtotals fieldname=Tot_avg | eval avgT_21=Tot_avg/21 | fields + _time avgT_21
but yours is more elegant!
Can you just clarify this tho please?
Does the underscore not show these as a column?
| eval _col_count="0"
| eval _col_sum="0"
And can it be slightly adjusted to get the max and min?
Yes, leading underscore makes them invisible. Answer adjusted for min/max.
tks - the max and min had to be adjusted slightly to handle the 1st column which is a string in this new dataset here
| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| transpose header_field=Date
| eval _col_count="0"
| eval _col_sum="0"
| foreach *
[ eval col_min = min(col_min, if(isnum('<<FIELD>>'), '<<FIELD>>', "n/a"))
| eval col_max = max(col_max, if(isnum('<<FIELD>>'), '<<FIELD>>', 0))
| eval _col_count = _col_count + if(isnum('<<FIELD>>'), 1, 0)
| eval _col_sum = _col_sum + if(isnum('<<FIELD>>'), '<<FIELD>>', 0)]
| eval col_avg = _col_sum / _col_count
Another way...
| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| untable Date shift count
| stats max(count) min(count) avg(count) sum(count) by shift
I also want to be able to get the min and the max, in similar fashion.