how to do a transpose in bash/awk/other

Question

How do I transpose the following data

colA    colB    colC    colD
val1    val2    val3    val4
val5    val6    val7    val8

So that it looks like this:

colA    val1    val5
colB    val2    val6
colC    val3    val7
colD    val4    val8

This data is tab delimited but It could also be comma delimited.

I can do this very easily in excel but I am wondering how to do it in bash using awk or something like that?

EDIT1

I can get it to work with spaces

$ cat testdata2_withspace.txt
colA colB colC colD
val1 val2 val3 val4
val5 val6 val7 val8

$ ./transpose3.sh testdata2_withspace.txt
colA val1 val5
colB val2 val6
colC val3 val7
colD val4 val8

but it is not doing the same with comma delimited files

$ cat testdata2.txt
colA,colB,colC,colD
val1,val2,val3,val4
val5,val6,val7,val8

$ ./transpose3.sh testdata2.txt
colA,colB,colC,colD val1,val2,val3,val4 val5,val6,val7,val8

check it here http://stackoverflow.com/questions/9534744/how-to-transfer-the-data-of-columns-to-rows-with-awk — Haifeng Zhang, May 25 '15 at 21:11

Ed Morton · Answer 1 · 2015-05-26T03:42:57.190

2

$ cat tst.awk
{ for (i=1; i<=NF; i++) cell[NR,i]=$i }
END {
    for (row=1; row<=NF; row++) {
        for (col=1; col<=NR; col++) {
            printf "%s%s", cell[col,row], (col<NR?OFS:ORS)
        }
    }
}

$ awk -f tst.awk file
colA val1 val5
colB val2 val6
colC val3 val7
colD val4 val8

To use commas instead of spaces as the separator add BEGIN{ FS=OFS="," } at the start.

edited May 26 '15 at 03:42

answered May 25 '15 at 21:33

Ed Morton

188,023
17
78
185

score 0 · Answer 2 · 2015-05-25T22:00:00.050

0

There's an awk script from here that can work:

#!/bin/bash 

transpose() 
{ 
  awk ' 
      BEGIN {
          FS = ",";
          OFS = ",";
      }

      { 
              if (max_nf<NF) 
                    max_nf=NF 
              max_nr=NR 
              for (x=1; x<=NF; ++x) 
                     vector[x, NR]=$x 
      } 

      END { 
              for (x=1; x<=max_nf; ++x) { 
                   for (y=1; y<=max_nr; ++y) 
                        printf("%s ", vector[x, y]) 
                   printf("\n") 
               } 
          }'  ${1} 
} 

transpose ${1}

edited May 25 '15 at 22:00

answered May 25 '15 at 21:11

yes that seems to work on space delimited files. how do I change it so it works on comma delimited files? I can't see the `-F` flag, for field seperater used in there. – HattrickNZ May 25 '15 at 21:35
I just edited the example to set both the input and output field separators to `,`. – May 25 '15 at 22:00

gboffi · Answer 3 · 2018-11-04T15:33:37.630

As the question title comprises "/other" I'm feeling like to post a Python+Bash answer.

The following works for a tab/space delimited file, minor adjustements required for a CSV

$ trans () { python2 -c 'print "\n".join("\t".join(t)for t in zip(*[l.split()for l in open("'"$1"'")]))' ; }
$ cat test2.csv
Roll_num Marks Grade
1        75    A
2        60    C
27       68    B
61       45    E
$ trans test2.csv 
Roll_num        1       2       27      61
Marks   75      60      68      45
Grade   A       C       B       E

The shell function, as defined, should be robust with respect to "fun ny" filenames

$ cp test2.csv fun\ ny
$ trans fun\ ny
Roll_num        1       2       27      61
Marks   75      60      68      45
Grade   A       C       B       E
$

Addendum

Here are the minor adjustements required for a CSV file

$ trans, () { python2 -c'print"\n".join(",".join(r)for r in zip(*[[i.strip()for i in l.strip().split(",")]for l in open("'"$1"'")]))'; }
$ cat test111.csv
Sales #, Date, Tel Number, Comment
393ED3, 5/12/2010, 5555551212, left message
585E54, 6/15/2014, 5555551213, voice mail
585868, 8/16/2010, , number is 5555551214
$ trans, test111.csv
Sales #,393ED3,585E54,585868
Date,5/12/2010,6/15/2014,8/16/2010
Tel Number,5555551212,5555551213,
Comment,left message,voice mail,number is 5555551214

how to do a transpose in bash/awk/other

3 Answers3

Addendum