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 CENTOS

time::seconds

Time::Seconds(3pm)					 Perl Programmers Reference Guide					Time::Seconds(3pm)

NAME

       Time::Seconds - a simple API to convert seconds to other date values

SYNOPSIS

	   use Time::Piece;
	   use Time::Seconds;

	   my $t = localtime;
	   $t += ONE_DAY;

	   my $t2 = localtime;
	   my $s = $t - $t2;

	   print "Difference is: ", $s->days, "
";

DESCRIPTION

       This module is part of the Time::Piece distribution. It allows the user to find out the number of minutes, hours, days, weeks or years in a
       given number of seconds. It is returned by Time::Piece when you delta two Time::Piece objects.

       Time::Seconds also exports the following constants:

	   ONE_DAY
	   ONE_WEEK
	   ONE_HOUR
	   ONE_MINUTE
	   ONE_MONTH
	   ONE_YEAR
	   ONE_FINANCIAL_MONTH
	   LEAP_YEAR
	   NON_LEAP_YEAR

       Since perl does not (yet?) support constant objects, these constants are in seconds only, so you cannot, for example, do this: "print
       ONE_WEEK->minutes;"

METHODS

       The following methods are available:

	   my $val = Time::Seconds->new(SECONDS)
	   $val->seconds;
	   $val->minutes;
	   $val->hours;
	   $val->days;
	   $val->weeks;
	       $val->months;
	       $val->financial_months; # 30 days
	   $val->years;
	   $val->pretty; # gives English representation of the delta

       The usual arithmetic (+,-,+=,-=) is also available on the objects.

       The methods make the assumption that there are 24 hours in a day, 7 days in a week, 365.24225 days in a year and 12 months in a year.
       (from The Calendar FAQ at http://www.tondering.dk/claus/calendar.html)

AUTHOR

       Matt Sergeant, matt@sergeant.org

       Tobias Brox, tobiasb@tobiasb.funcom.com

       BalieXXzs SzabieXX (dLux), dlux@kapu.hu

LICENSE

       Please see Time::Piece for the license.

Bugs
       Currently the methods aren't as efficient as they could be, for reasons of clarity. This is probably a bad idea.

perl v5.16.3							    2013-03-04							Time::Seconds(3pm)