Splunk Search

Turning a table [Transpose]

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

HattrickNZ
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

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

linu1988
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

HattrickNZ
Motivator

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

0 Karma

sdaniels
Splunk Employee
Splunk Employee

I think the transpose command will work

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...