Linux - Transpose rows into column Post: 303001051

Sponsored Content

Top Forums Shell Programming and Scripting Linux - Transpose rows into column Post 303001051 by durden_tyler on Wednesday 26th of July 2017 02:00:01 AM

07-26-2017

Registered User

Here's some Perl code that appears to accomplish what you want.
It is based, however, on assumptions that part of your output is hard-coded, your input file is sorted by server name etc.

Code:

$ 
$ cat inputfile
Time,A,Server1,KPI1,data1
Time,A,Server1,KPI2,data2
Time,A,Server1,KPI3,data3
Time,A,Server1,KPI4,data4
Time,A,Server1,KPI5,data5
Time,A,Server2,KPI1,data6
Time,A,Server2,KPI2,data7
Time,A,Server2,KPI3,data8
Time,A,Server2,KPI4,data9
Time,A,Server2,KPI5,data10
Time,A,Server3,KPI1,data11
Time,A,Server3,KPI2,data12
Time,A,Server3,KPI3,data13
Time,A,Server3,KPI4,data14
Time,A,Server3,KPI5,data15
Time,A,Server4,KPI1,data16
Time,A,Server4,KPI2,data17
Time,A,Server4,KPI3,data18
Time,A,Server4,KPI4,data19
Time,A,Server4,KPI5,data20
$ 
$ perl -F, -lane 'BEGIN { @occurrences = (2, 4, 5);
                      $prefix = "Time";
                      %active = map {$_ => 1} @occurrences;
                      $header = join ",", ($prefix, map {"KPI".$_} @occurrences);
                  }
                  if ($F[2] ne $prev) {
                      $counter = 1;
                      if ($output) {print FH $output}
                      open(FH, ">", $F[2].".csv");
                      print FH $header;
                      $output = $prefix;
                  } elsif ($active{$counter}) {
                      $output .= ",".$F[4];
                  }
                  $prev = $F[2];
                  $counter++;
                  END {print FH $output}
                 ' inputfile
$ 
$ find . -name "*.csv"
./Server2.csv
./Server3.csv
./Server4.csv
./Server1.csv
$ 
$ cat Server1.csv
Time,KPI2,KPI4,KPI5
Time,data2,data4,data5
$ 
$ cat Server2.csv
Time,KPI2,KPI4,KPI5
Time,data7,data9,data10
$ 
$ cat Server3.csv
Time,KPI2,KPI4,KPI5
Time,data12,data14,data15
$ 
$ cat Server4.csv
Time,KPI2,KPI4,KPI5
Time,data17,data19,data20
$ 
$

If it doesn't work for your input file then post a sample of your real-world input data and the output desired from that real-world data.

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Transpose Rows

Hi, Am trying to transpose a set of rows into a set of comma separated values. For eg. if the output of ps -ef | tail +2 | awk 'BEGIN{ FS=" " } { print $2 }' is 0 1 3 4 I need to transpose it to - '0','1','3','4' Am currently trying -

2. Shell Programming and Scripting

transpose rows to columns

Any tips on how I can awk the input data to display the desired output per below? Thanking you in advance. input test data: 2 2010-02-16 10:00:00 111111111111 bytes 99999999999 bytes 90% 4 2010-02-16 12:00:00 333333333333 bytes 77777777777 bytes 88% 5 2010-02-16 11:00:00...

3. Shell Programming and Scripting

awk transpose rows to column

Need to transpose in awk rows to column like this: input: A1,6,5,4 3,2,1, A2,8,7,9,10,11,12,13,14 A3,1,2,3,5,7,8,9 A4,9,4,8,1,5,3, output: A1,1 A1,2 A1,4 ... A2,7 A2,8 ... A3,1 A3,2 ... A4,1 A4,3

4. Shell Programming and Scripting

Transpose Datefield from rows to column + Print time diff

Hi Experts, Can you please help me in transposing Datefield from rows to column and calculate the time difference for each of the Jobids: Input File: 08/23/2012 12:36:09,JOB_5340 08/23/2012 12:36:14,JOB_5340 08/23/2012 12:36:22,JOB_5350 08/23/2012 12:36:26,JOB_5350 Required Output:...

5. Shell Programming and Scripting

awk to transpose preceding row to 1st column of next rows

Gurus: How can I transpose the output below to a format in which I can plot a graph to show VSZ memory usage by PIDs? stdout: Tue Jan 22 07:29:19 CUT 2013 42336296 1841272 java wilyadm 21889232 438616 jlaunch sidadm 42532994 414336 jlaunch sidadm Tue Jan 22 07:49:20 CUT 2013...

6. Shell Programming and Scripting

Transpose Column of Data to Rows

I can no longer find my commands, but I use to be able to transpose data with common fields from a single column to rows using a command line. My data is separated as follows: NAME=BOB ADDRESS=COLORADO PET=CAT NAME=SUSAN ADDRESS=TEXAS PET=BIRD NAME=TOM ADDRESS=UTAH PET=DOG I would...

7. Shell Programming and Scripting

Transpose multiple rows (with a mix of space and enter) to a single column

How to change the uploaded weekly file data to the following format? New Well_Id,Old Well_Id,District,Thana,Date,Data,R.L,WellType,Lati.,Longi. BAG001,PT006,BARGUNA,AMTALI,1/2/1978,1.81,2.29,Piezometer,220825,901430 BAG001,PT006,BARGUNA,AMTALI,1/9/1978,1.87,2.29,Piezometer,220825,901430...

8. Shell Programming and Scripting

Peel syntax for transpose rows into column

Dear all, Plz let me know syntax for transposing rows into column in perl, I am having 30 csv files which are merged into a single xls sheet. but i want to transpose each row into column in excel sheet in each tab (1 CSV = 1tab in xls sheet) example is as below ...

9. Programming

To transpose rows to column in hadoop

Hi, i am having an HDFS file which is comma seperated, i need to transpose from rows to column only the header columns text.csv cnt,name,place 1,hi,nz 2,hello,aus I need cnt, name, place while using below command in hadoop getting the error hadoop fs -fmt -1 text.csv

10. UNIX for Beginners Questions & Answers

How to transpose pieces of data in a column to multiple rows?

Hello Everyone, I am very new to the world of regular expressions. I am trying to use grep/sed for the following: Input file is something like this and there are multiple such files: abc 1 2 3 4 5 ***END*** abc 6 7 8 9 ***END*** abc 10

LEARN ABOUT OPENSOLARIS

iicpbmp

iicpbmp(1M)						  System Administration Commands					       iicpbmp(1M)

NAME

       iicpbmp - copy Availability Suite Point-In-Time bitmap volumes

SYNOPSIS

       iicpbmp [-c] old_bitmap new_bitmap...

DESCRIPTION

       The iicpbmp command copies an Availability Suite Point-in-Time bitmap volume, rewriting the bitmap header so that it is consistent with the
       new bitmap volume name. The configuration entry for the shadow set is rewritten to reflect the location of the new bitmap.

       No checks on the current use of either the old or new bitmap volumes are made. The iicpbmp command should only be run  when  the  Point-In-
       Time Copy shadow set using the old bitmap is suspended.

OPTIONS

       -c	    Do	not  attempt  to  update  the Availability Suite configuration for the Point-in-Time shadow set that uses the bitmap. This
		    option produces a duplicate of the bitmap but does not affect the shadow set using the old bitmap volume.

OPERANDS

       old_bitmap new_bitmap

	   The old and new Point-In-Time bitmap volumes.

WARNINGS

       The iicpbmp should be run only when a system is in single-user mode. iicpbmp makes no attempt to check if an Point-In-Time Copy set  is	in
       use at the time the copy is made. Running iicpbmp without the -c flag while Point-In-Time Copy is using the shadow set results in inconsis-
       tencies in the shadow set the next time Point-In-Time Copy is started.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE	     |	    ATTRIBUTE VALUE	   |
       +-----------------------------+-----------------------------+
       |Availability		     |SUNWiir, SUNWiiu		   |
       +-----------------------------+-----------------------------+
       |Interface Stability	     |Evolving			   |
       +-----------------------------+-----------------------------+

SEE ALSO

       iiadm(1M), iicpshd(1M), attributes(5)

SunOS 5.11							    2 Oct 2007							       iicpbmp(1M)