Converting rows to columns in csv file

Shell Programming and Scripting
1

Hi,

I have a requirement to convert rows into columns. data looks like:

c1,c2,c3,..
r1,r2,r3,..
p1,p2,p3,..
and so on..

output shud be like this:

c1,r1,p1,..
c2,r2,p2,..
c3,r3,p3,..

Thanks in advance,

2

With some assumptions..

Try sth like this....

$ cat file
c1,c2,c3
r1,r2,r3
p1,p2,p3

$ awk -F, '{for(i=1;i<=NF;i++){A[NR,i]=$i}}
END{for(i=1;i<=NR;i++){
for(j=1;j<=NF;j++){
s=s?s","A[j,i]:A[j,i]}
print s;s=""}}' file

c1,r1,p1
c2,r2,p2
c3,r3,p3
3

Try like...

awk -F "," '{
for (f = 1; f <= NF; f++)
a[NR, f] = $f
}
NF > nf { nf = NF }
END {
for (f = 1; f <= nf; f++)
for (r = 1; r <= NR; r++)
printf a[r, f] (r==NR ? RS : FS)
}' test.txt
1 Like
4

Hey m newbee in unix..file with input is file_in.csv and i want output in File_out.csv... can please tell me where i need to place these file names?

5 
awk -F, '{for(i=1;i<=NF;i++){A[NR,i]=$i}}
END{for(i=1;i<=NR;i++){
for(j=1;j<=NF;j++){
s=s?s","A[j,i]:A[j,i]}
print s;s=""}}'  file_in.csv  >  File_out.csv

or in short..

awk {} input_file  > output_file
1 Like
6

thanks..it worked..:)..however its converting only 9 columns to rows..but i have 26 columns..please help

---------- Post updated at 05:25 AM ---------- Previous update was at 04:55 AM ----------

its working but its converting only 9 columns into rows..however i need to convert 26 columns..please help...:frowning:

7

Sorry I've done one mistake there.

now check

awk -F, '{for(i=1;i<=NF;i++){A[NR,i]=$i};if(NF>n){n=NF}}
END{for(i=1;i<=n;i++){
for(j=1;j<=NR;j++){
s=s?s","A[j,i]:A[j,i]}
print s;s=""}}' file
8

My solution (I need to practice awk :D)

file

c1,c2,C3,c4,c5,c6
r1,r2,r3,r4,r5,r6
p1,p2,p3,p4,p5,p6
w1,w2,w3,w4,w5,w6
y1,y2,y3,y4,y5,y6
z1,z2,z3,z4,z5,z6

script.awk

BEGIN {
  FS=","
}

{
  for (i = 1; i <= NF; i++)
      arr[i,NR]=$i
}

END {
  for (x = 1; x <= NR; x++)
  {
    for (y = 1; y <= NF; y++)
      {
        printf("%s", arr[x,y])
        if(y < NF)
          printf("%s", ",")
      }
    print ""
  }
} # END

Execution :

#awk -f script.awk < file

result

c1,r1,p1,w1,y1,z1
c2,r2,p2,w2,y2,z2
C3,r3,p3,w3,y3,z3
c4,r4,p4,w4,y4,z4
c5,r5,p5,w5,y5,z5
c6,r6,p6,w6,y6,z6

It should work for a more than 6x6 matrix

---------- Post updated 28th Dec 2012 at 10:38 AM ---------- Previous update was 27th Dec 2012 at 03:35 PM ----------

A great majority of the scripts at my new office are written in KSh, but some Perl scripts remain in some servers.
I need to practice Perl too, so hereafter the Perl version of the previous awk solution :

#!/usr/bin/perl -w
use strict;
 
my $cur_dir = $ENV{PWD};
my $filename = $cur_dir."/file";
 
open(FILE,"<$filename") or die"open: $!";
my ($record,@fields,%col);
while( defined( $record = <FILE> ) ) {
  my $col_counter=1;
  chomp $record;
  @fields=split(/,/,$record);
  foreach (@fields) {
    $col{$col_counter} .= "$_,";
    $col_counter++;
  }
}
 
close(FILE);
 
for ( my $i=1; $i<=keys(%col); $i++ ) {
  my $line=$col{$i};
  chop $line;
  print "$line\n";
}

If Awk of Perl gurus have comments about my code, they are welcome :).

I'm also wondering what is the best choice for my future new scripts : KSh+awk or Perl ?

Thank You

9

Need some corrections to your script..

Please check..

Your script may work on 6*6 or 9*9 matrix. But try on 6*9 or sth like this. :slight_smile:

BEGIN {
  FS=","
}

{
  for (i = 1; i <= NF; i++)
      arr[i,NR]=$i
}

END {
  for (x = 1; x <= NF; x++)
  {
    for (y = 1; y <= NR; y++)
      {
        printf("%s", arr[x,y])
        if(y < NR)
          printf("%s", ",")
      }
    print ""
  }
}
10

Hi Pamu,

my awk script is working well.
Can you explain your modifications ?

Thank You

11

Please check..

$ cat file
c1,c2,C3,c4,c5,c6
r1,r2,r3,r4,r5,r6
p1,p2,p3,p4,p5,p6
w1,w2,w3,w4,w5,w6
y1,y2,y3,y4,y5,y6
z1,z2,z3,z4,z5,z6
c1,c2,C3,c4,c5,c6
r1,r2,r3,r4,r5,r6
p1,p2,p3,p4,p5,p6
w1,w2,w3,w4,w5,w6
y1,y2,y3,y4,y5,y6
z1,z2,z3,z4,z5,z6

$ awk 'BEGIN {
  FS=","
}

{
  for (i = 1; i <= NF; i++)
      arr[i,NR]=$i
}

END {
  for (x = 1; x <= NR; x++)
  {
    for (y = 1; y <= NF; y++)
      {
        printf("%s", arr[x,y])
        if(y < NF)
          printf("%s", ",")
      }
    print ""
  }
}' file
c1,r1,p1,w1,y1,z1
c2,r2,p2,w2,y2,z2
C3,r3,p3,w3,y3,z3
c4,r4,p4,w4,y4,z4
c5,r5,p5,w5,y5,z5
c6,r6,p6,w6,y6,z6
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,


$ cat file
c1,c2,C3,c4,c5,c6
r1,r2,r3,r4,r5,r6
p1,p2,p3,p4,p5,p6
w1,w2,w3,w4,w5,w6
y1,y2,y3,y4,y5,y6
z1,z2,z3,z4,z5,z6
c1,c2,C3,c4,c5,c6
r1,r2,r3,r4,r5,r6
p1,p2,p3,p4,p5,p6
w1,w2,w3,w4,w5,w6
y1,y2,y3,y4,y5,y6
z1,z2,z3,z4,z5,z6

$ awk 'BEGIN {
  FS=","
}

{
  for (i = 1; i <= NF; i++)
      arr[i,NR]=$i
}

END {
  for (x = 1; x <= NF; x++)
  {
    for (y = 1; y <= NR; y++)
      {
        printf("%s", arr[x,y])
        if(y < NR)
          printf("%s", ",")
      }
    print ""
  }
}' file
c1,r1,p1,w1,y1,z1,c1,r1,p1,w1,y1,z1
c2,r2,p2,w2,y2,z2,c2,r2,p2,w2,y2,z2
C3,r3,p3,w3,y3,z3,C3,r3,p3,w3,y3,z3
c4,r4,p4,w4,y4,z4,c4,r4,p4,w4,y4,z4
c5,r5,p5,w5,y5,z5,c5,r5,p5,w5,y5,z5
c6,r6,p6,w6,y6,z6,c6,r6,p6,w6,y6,z6
1 Like
12

Merci Pamu :slight_smile:

13

considering number of columns are not the same.

With some assumptions and assuming required output is as below(with comma separated for null values):smiley:

$ cat file
c1,c2,C3,c4,c5,c6
r1,r2,r3,r4,r5,r6
p1,p2,p3,p4,p5,p6,p7
w1,w2,w3,w4,w5,w6
y1,y2,y3,y4,y5,y6
z1,z2,z3,z4,z5,z6
c1,c2,C3,c4,c5,c6
r1,r2,r3,r4,r5,r6,r7,r8
p1,p2,p3,p4,p5,p6
w1,w2,w3,w4,w5,w6
y1,y2,y3,y4,y5,y6
z1,z2,z3,z4,z5,z6

$ awk -F, '{for(i=1;i<=NF;i++){A[NR,i]=$i};if(NF>n){n=NF}}
END{for(i=1;i<=n;i++){
s=A[1,i]?A[1,i]:"  ";
for(j=2;j<=NR;j++){
p=A[j,i]?A[j,i]:"  "
s=s","p}
print s}}' file

c1,r1,p1,w1,y1,z1,c1,r1,p1,w1,y1,z1
c2,r2,p2,w2,y2,z2,c2,r2,p2,w2,y2,z2
C3,r3,p3,w3,y3,z3,C3,r3,p3,w3,y3,z3
c4,r4,p4,w4,y4,z4,c4,r4,p4,w4,y4,z4
c5,r5,p5,w5,y5,z5,c5,r5,p5,w5,y5,z5
c6,r6,p6,w6,y6,z6,c6,r6,p6,w6,y6,z6
  ,  ,p7,  ,  ,  ,  ,r7,  ,  ,  ,
  ,  ,  ,  ,  ,  ,  ,r8,  ,  ,  ,