topic Turning a table [Transpose] in Splunk Search

Turning a table [Transpose]

vbumgarner — Fri, 09 Aug 2013 18:51:47 GMT

Is there a generic way to simply flip a table around the top-left to bottom-right diagonal axis?

For instance, if I had this:

name b c d e f
foo  1 2 3 4 5
bar  5 4 3 2 1
la   9 8 7 6 5

And I instead want this:

name foo bar la
b    1   5   9
c    2   4   8
d    3   3   7
e    4   2   6
f    5   1   5

Re: Turning a table [Transpose]

davecroto — Fri, 09 Aug 2013 19:22:39 GMT

http://splunk-base.splunk.com/answers/13009/can-i-format-a-table-in-reverse-field-headings-by-row-not-by-columns

Re: Turning a table [Transpose]

sdaniels — Fri, 09 Aug 2013 19:59:01 GMT

I think the transpose command will work

Re: Turning a table [Transpose]

linu1988 — Fri, 09 Aug 2013 20:26:11 GMT

Transpose will work but you need to rename the new column names and have to filter out the actual column names which will come in the first row.

Re: Turning a table [Transpose]

vbumgarner — Fri, 09 Aug 2013 20:35:22 GMT

Here's python that would do it, but I was hoping there was already a command included that does that natively:

import csv
import sys

output = []

csvreader = csv.reader(sys.stdin)
for rowidx, row in enumerate(csvreader):
    for cellidx, cell in enumerate(row):
        if rowidx is 0:
            output.append([cell])
        else:
            output[cellidx].append( cell )

csvwriter = csv.writer(sys.stdout)
for row in output:
    csvwriter.writerow(row)

Re: Turning a table [Transpose]

HattrickNZ — Thu, 04 Jun 2015 02:16:30 GMT

I have come accross something similar myself

... | stats max(field1) as foo max(field2) as bar max(field3) as la by name | transpose

gives me the below :

 column row 1 row 2 row3
 name foo bar la
 b    1   5   9
 c    2   4   8
 d    3   3   7
 e    4   2   6
 f    5   1   5

but I dont want "column row 1 row 2 row3" as my column headers I want " name foo bar la" as my column headers

But now I have to work out a way to remove row1 " name foo bar la" after I have renamed the column headers

Which will hopefull give me this:

 name foo bar la
  b    1   5   9
  c    2   4   8
  d    3   3   7
  e    4   2   6
  f    5   1   5

Re: Turning a table [Transpose]

HattrickNZ — Thu, 04 Jun 2015 02:17:30 GMT

@linu1988 how do you filter out the "column names whcih will come in the first row"?