Linux - Transpose rows into column

Shell Programming and Scripting
1

hello,
I have a server that collect some performance statistics of 4 servers in the following input file :


$ 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

I'ld like to convert this input file (using bash or perl script) to 4 output files (for each server) and the output will be as following


$ cat Server1.csv
Time,KPI2,KPI4,KPI5
Time,data2,data4,data5

$ cat Server2.csv
Time,KPI2,KPI4,KPI5
Time,data6,data9,data10

$ cat Server3.csv
Time,KPI2,KPI4,KPI5
Time,data12,data14,data15

$ cat Server4.csv
Time,KPI2,KPI4,KPI5
Time,data17,data19,data20

thanks in advance

2

Welcome Captain25,

I have a few to questions pose in response first:-

  • What have you tried so far?
  • What output/errors do you get?
  • Have you searched the board yet? There are several threads asking the same thing.
  • What OS and version are you using?
  • What are your preferred tools? (C, shell, perl, awk, etc.)
  • What logical process have you considered? (to help steer us to follow what you are trying to achieve)

Most importantly, What have you tried so far?

There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.

We're all here to learn and getting the relevant information will help us all.

Kind regards,
Robin

3

Hello
� What have you tried so far? ��I tried to create a scripts basing on some scripts that transpose rows to columns posted on this forum.
� What output/errors do you get? ��No error, but the output was not conform as what I need
� Have you searched the board yet? There are several threads asking the same thing ��� even on other forums
� What OS and version are you using? Redhat GNU/Linux
� What are your preferred tools? (C, shell, perl, awk, etc.) ��� shell or perl
� What logical process have you considered? (to help steer us to follow what you are trying to achieve) to be more clear :

I have an input file that contain the following data:

$ 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

I�ld like to convert this input file (using bash or perl script) to 4 output files (for each server) and the output will be as following

$ cat Server1.csv
Time,KPI2,KPI4,KPI5
Time,data2,data4,data5

$ cat Server2.csv
Time,KPI2,KPI4,KPI5
Time,data6,data9,data10

$ cat Server3.csv
Time,KPI2,KPI4,KPI5
Time,data12,data14,data15

$ cat Server4.csv
Time,KPI2,KPI4,KPI5
Time,data17,data19,data20

thanks in advance

4

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.

$ 
$ 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.

5

Hello,

thank you durden_tyler for your answer, your perl script work perfectly but it�s static, and I�ld like to have a dynamic script file to use it on a cronjob (every 5min) to collect the following KPIs:
dynamic script mean that the KPI and server name and time need to be collected from the input file.

Inputfile= counter_2017072516

KPI2= Used Physical Mem MB %, (4th position data on the line)
KPI4= Total CPU Usage of tcs SERVER Component %,
KPI5= Total CPU Usage of tcs Node %,

Server1= m07tcs1
Server2= m05tcs2
Server3= m04tcs3
Server4= m07tcs4

Time=2017-07-25 16:46:04 (dynamic)

$ cat counter_2017072516
2017-07-25 16:46:04,m07tcs1,SERVER@m07tcs1,Used Physical Mem MB,5
2017-07-25 16:46:04,m07tcs1,SERVER@m07tcs1,Used Physical Mem %,29
2017-07-25 16:46:04,m07tcs1,SERVER@m07tcs1,Used Heap Mem MB,9662
2017-07-25 16:46:04,m07tcs1,SERVER@m07tcs1,Total CPU Usage of tcs SERVER Component %,0.0
2017-07-25 16:46:04,m07tcs1,SERVER@m07tcs1,Total CPU Usage of tcs Node %,0.67

2017-07-25 16:46:04,m05tcs2,SERVER@m05tcs2,Used Physical Mem MB,6.4
2017-07-25 16:46:04,m05tcs2,SERVER@m05tcs2,Used Physical Mem %,28
2017-07-25 16:46:04,m05tcs2,SERVER@m05tcs2,Used Heap Mem MB,666
2017-07-25 16:46:04,m05tcs2,SERVER@m05tcs2,Total CPU Usage of tcs SERVER Component %,0.0
2017-07-25 16:46:04,m05tcs2,SERVER@m05tcs2,Total CPU Usage of tcs Node %,1.65

2017-07-25 16:46:04,m04tcs3,SERVER@m04tcs3,Used Physical Mem MB,8
2017-07-25 16:46:04,m04tcs3,SERVER@m04tcs3,Used Physical Mem %,15
2017-07-25 16:46:04,m04tcs3,SERVER@m04tcs3,Used Heap Mem MB,366
2017-07-25 16:46:04,m04tcs3,SERVER@m04tcs3,Total CPU Usage of tcs SERVER Component %,0.0
2017-07-25 16:46:04,m04tcs3,SERVER@m04tcs3,Total CPU Usage of tcs Node %,04

2017-07-25 16:46:04,m07tcs4,SERVER@m07tcs4,Used Physical Mem MB,13
2017-07-25 16:46:04,m07tcs4,SERVER@m07tcs4,Used Physical Mem %,45.5
2017-07-25 16:46:04,m07tcs4,SERVER@m07tcs4,Used Heap Mem MB,778
2017-07-25 16:46:04,m07tcs4,SERVER@m07tcs4,Total CPU Usage of tcs SERVER Component %,1.0
2017-07-25 16:46:04,m07tcs4,SERVER@m07tcs4,Total CPU Usage of tcs Node %,0.8

The output files need to be like

$ cat m07tcs1_2017072516.csv
Time, Used Physical Mem %, Total CPU Usage of tcs SERVER Component %, Total CPU Usage of tcs Node %
2017-07-25 16:46:04,29,0.0,0.67

$ cat m05tcs2_2017072516.csv
Time, Used Physical Mem %, Total CPU Usage of tcs SERVER Component %, Total CPU Usage of tcs Node %
2017-07-25 16:46:04,28,0.0,0.4

$ cat m04tcs3_2017072516.csv
Time, Used Physical Mem %, Total CPU Usage of tcs SERVER Component %, Total CPU Usage of tcs Node %
2017-07-25 16:46:04,15,1.0,04

$ cat m07tcs4_2017072516.csv
Time, Used Physical Mem %, Total CPU Usage of tcs SERVER Component %, Total CPU Usage of tcs Node %
2017-07-25 16:46:04,45.5,1.0,0.8

Thanks again for your replies

6

So, what have you tried to achieve this? If you show us your code so far then we can suggest changes to help. That way, we're not just writing code that you don't understand and can't support and we can see what other things your description might be missing.

Giving us something that doesn't work along with the output/errors and the desired output from real input would be very useful. We're all here to learn, after all.

Thanks again,
Robin

7

hi rbatte1

I'm a noob in scripting, and I posted this thread, looking for someone that can help me to develop the requested script.

Best regards

---------- Post updated at 02:48 PM ---------- Previous update was at 07:36 AM ----------

Hello,
Any idea ?
Br

8

No problem with that, we all started as beginners.

But what rbatte1 means is this: if you post your code, however bad it is, we can tell you how and why it failed and you would have the opportunity to learn from your own errors. Instead you posted nothing but a request, got a solution, then told us that the request is not the real request and the solution should be modified (naturally, by one of us).

Sorry to be so blunt but you come across not like someone seeking an opportunity to learn but someone who needs to get a job done. We help others to help themselves, but we are NOT a free scripting service for people not willing to script themselves.

bakunin

9

As i said i tried to create a script basing on some simillar posted threads but i failed, and i have to learn more to be able to create a such script.
Thanks guys and special thank to durden_tyler for his solution,
I guess only expert are welcome here .

10

No, that isn't the case and you know it damn well. I repeat what i have written in another thread today already:

you will write your script! And while we will assist you, explain things to you, correct your code if necessary and help you in any possible way - we will not write it for you.

But to help you we would have to be able to see at least one line of code you wrote, working or not. Instead, you fail to show that and repeat telling us your tries "don't work". To analyse why they don't work we would have to see them, don't you think? And that is not a case of "experts" or "beginners". You don't call the doctor saying "it hurts" but you tell him where it hurts and how. Without seeing what you have done we cannot tell you what was wrong.

bakunin

2 Likes