topic Re: How to transpose some rows to columns? in Splunk Search

How to transpose some rows to columns?

jenniferhao — Mon, 23 Sep 2019 16:05:17 GMT

The original data :

_time reg exp raw

2019-09-20 A 1 100
2019-09-20 B 2 200
2019-09-20 C 3 300
2019-09-20 D 1 100
2019-09-20 E 2 200
2019-09-20 F 3 300
2019-09-19 A 1 100
2019-09-19 B 2 200
2019-09-19 C 3 300
2019-09-19 D 1 100
2019-09-19 E 2 200
2019-09-19 F 3 300

How to make a table as following: only show exp values with fields name as reg and have percentages:

_time per A B C D E F
2019-09-20 0.01 1 2 3 1 2 3 //exp values
2019-09-19 0.01 1 2 3 1 2 3 //exp values

per = sum(exp values)/sum(raw values) example: (1+2+3+1+2+3)/(100+200+300+100+200+300)

Re: How to transpose some rows to columns?

adonio — Mon, 23 Sep 2019 16:42:51 GMT

hope i understand your requirement,

try this anywhere:

| makeresults count=1
| eval data = "2019-09-20 A 1 100;;;2019-09-20 B 2 200;;;2019-09-20 C 3 300;;;2019-09-20 D 1 100;;;2019-09-20 E 2 200;;;2019-09-20 F 3 300;;;2019-09-19 A 1 100;;;2019-09-19 B 2 200;;;2019-09-19 C 3 300;;;2019-09-19 D 1 100;;;2019-09-19 E 2 200;;;2019-09-19 F 3 300"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<time>[^\s]+)\s+(?<reg>[^\s]+)\s+(?<exp>[^\s]+)\s+(?<raw>[^\s]+)"
| table time reg exp raw
| rename COMMENT as "the above generates data below is the solution" 
| eventstats sum(raw) as total_raw sum(exp) as total_exp by time
| eval percent = round(total_exp / total_raw, 2)
| eval {reg} = exp
| stats values(*) as * by time

hope it helps

Re: How to transpose some rows to columns?

jenniferhao — Mon, 23 Sep 2019 18:43:43 GMT

Thanks, Adonio. It works. If I only want to show _time A B C D E F percent. How Can I do this?

Re: How to transpose some rows to columns?

adonio — Mon, 23 Sep 2019 18:48:35 GMT

@jenniferhao try ... | table _time A B C D E F ...

Re: How to transpose some rows to columns?

jenniferhao — Mon, 23 Sep 2019 19:10:01 GMT

Thanks, Adonio. It works well now.

Re: How to transpose some rows to columns?

jenniferhao — Thu, 11 Mar 2021 16:37:55 GMT

Thanks.

Re: How to transpose some rows to columns?

fabiofox — Thu, 09 Mar 2023 09:56:14 GMT

what a trick! this is insane. Thank you so much!