Splunk Search
Highlighted

Turning a table [Transpose]

Contributor

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
Tags (2)
Highlighted

Re: Turning a table [Transpose]

Splunk Employee
Splunk Employee
Highlighted

Re: Turning a table [Transpose]

Splunk Employee
Splunk Employee

I think the transpose command will work

0 Karma
Highlighted

Re: Turning a table [Transpose]

Champion

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.

0 Karma
Highlighted

Re: Turning a table [Transpose]

Motivator

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

0 Karma
Highlighted

Re: Turning a table [Transpose]

Contributor

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)
Highlighted

Re: Turning a table [Transpose]

Motivator

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

....| transpose | rename column as name | rename "row 1" as foo | rename "row 2" as bar | rename "row 3" as ls

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
0 Karma