topic Re: Transpose CSV column headers to row in Getting Data In

Transpose CSV column headers to row

test365498 — Mon, 25 Jul 2016 16:04:13 GMT

Hello,

Is Splunk able to, before or after indexing, transpose column and rows in this way:
original file: has column headers
need format: each column header to be appended into a column such that each header is repeated as a row corresponding to a value.

Please see the initial and final screen shot attached. Thank you.

Re: Transpose CSV column headers to row

JDukeSplunk — Mon, 25 Jul 2016 16:42:07 GMT

I know splunk can transpose, but from my experience you have to mess with the column/row names a bit to get it pretty.

base search | transpose 5

There are some examples here it the documentation.
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose

Re: Transpose CSV column headers to row

somesoni2 — Mon, 25 Jul 2016 16:58:48 GMT

This should do it

your base search | untable percentile type value | table type percentile value

See more on untable command here. http://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Untable

Re: Transpose CSV column headers to row

test365498 — Mon, 25 Jul 2016 17:35:12 GMT

I need to untable multiple columns, and have the "type" duplicated and corresponding to each value. Leave the percentile however, but in this case the value would need to be duplicated to correspond to each set of "type." Does that make sense?

Re: Transpose CSV column headers to row

test365498 — Mon, 25 Jul 2016 17:43:35 GMT

This does not look correct on my end. I need to append all of the headers are rows with corresponding values (of which there are multiple values, for the "type" would be duplicated).

Re: Transpose CSV column headers to row

sundareshr — Mon, 25 Jul 2016 17:54:45 GMT

How about this

.... | stats first(*) as * by type percentile

Re: Transpose CSV column headers to row

test365498 — Mon, 25 Jul 2016 18:19:49 GMT

Hm, did not seem to produce any results.

Re: Transpose CSV column headers to row

somesoni2 — Mon, 25 Jul 2016 18:25:32 GMT

Not sure I get it completely. If you're desired out is what you need, then give this a try

your base search | untable percentile type value | sort 0 type percentile value | table type percentile value

Re: Transpose CSV column headers to row

test365498 — Tue, 29 Sep 2020 10:18:22 GMT

This is getting closer.
Let me try one more time.

Sample original file:
percentile login logout search_result
1 686 13332 13128
2 687 13333 13129
4 686 13340 13127
8 688 13330 13128
9 690 13331 13126

Desired result after transpose:
type percentile value
login 1 686

login 2 687

login 4 686

login 8 688

login 9 690

logout 1 13332

logout 2 13333

logout 4 13340

logout 8 13330

logout 9 13331

search_result 1 13128
search_result 2 13129
search_result 4 13127
search_result 8 13128
search_result 9 13126

Re: Transpose CSV column headers to row

somesoni2 — Mon, 25 Jul 2016 19:58:31 GMT

I tested with your new sample data and the query returns the desired result.

Re: Transpose CSV column headers to row

woodcock — Wed, 27 Jul 2016 23:39:52 GMT

Just add this:

... | table percentiles login logout search_result | untable percentiles TYPE duration