An efficient way to transpose a file in Bash

user_209467 0
Viewed 150265

I have a huge tab-separated file formatted like this

X column1 column2 column3
row1 0 1 2
row2 3 4 5
row3 6 7 8
row4 9 10 11

I would like to transpose it in an efficient way using only bash commands (I could write a ten or so lines Perl script to do that, but it should be slower to execute than the native bash functions). So the output should look like

X row1 row2 row3 row4
column1 0 3 6 9
column2 1 4 7 10
column3 2 5 8 11

I thought of a solution like this

cols=`head -n 1 input | wc -w`
for (( i=1; i <= $cols; i++))
do cut -f $i input | tr $'\n' $'\t' | sed -e "s/\t$/\n/g" >> output
done

But it's slow and doesn't seem the most efficient solution. I've seen a solution for vi in this post, but it's still over-slow. Any thoughts/suggestions/brilliant ideas? :-)

transpose unix parsing bash
32 Answers

I used fgm's solution (thanks fgm!), but needed to eliminate the tab characters at the end of each row, so modified the script thus:

#!/bin/bash 
declare -a array=( )                      # we build a 1-D-array

read -a line < "$1"                       # read the headline

COLS=${#line[@]}                          # save number of columns

index=0
while read -a line; do
    for (( COUNTER=0; COUNTER<${#line[@]}; COUNTER++ )); do
        array[$index]=${line[$COUNTER]}
        ((index++))
    done
done < "$1"

for (( ROW = 0; ROW < COLS; ROW++ )); do
  for (( COUNTER = ROW; COUNTER < ${#array[@]}; COUNTER += COLS )); do
    printf "%s" ${array[$COUNTER]}
    if [ $COUNTER -lt $(( ${#array[@]} - $COLS )) ]
    then
        printf "\t"
    fi
  done
  printf "\n" 
done
user_1377510

Another awk solution and limited input with the size of memory you have.

awk '{ for (i=1; i<=NF; i++) RtoC[i]= (RtoC[i]? RtoC[i] FS $i: $i) }
    END{ for (i in RtoC) print RtoC[i] }' infile

This joins each same filed number positon into together and in END prints the result that would be first row in first column, second row in second column, etc. Will output:

X row1 row2 row3 row4
column1 0 3 6 9
column2 1 4 7 10
column3 2 5 8 11
user_40239500

Another bash variant

$ cat file 
XXXX    col1    col2    col3
row1    0       1       2
row2    3       4       5
row3    6       7       8
row4    9       10      11

Script

#!/bin/bash

I=0
while read line; do
    i=0
    for item in $line; { printf -v A$I[$i] $item; ((i++)); }
    ((I++))
done < file
indexes=$(seq 0 $i)

for i in $indexes; {
    J=0
    while ((J<I)); do
        arr="A$J[$i]"
        printf "${!arr}\t"
        ((J++))
    done
    echo
}

Output

$ ./test 
XXXX    row1    row2    row3    row4    
col1    0       3       6       9   
col2    1       4       7       10  
col3    2       5       8       11
user_126074430

A oneliner using R...

  cat file | Rscript -e "d <- read.table(file('stdin'), sep=' ', row.names=1, header=T); write.table(t(d), file=stdout(), quote=F, col.names=NA) "
user_4514600

I've used below two scripts to do similar operations before. The first is in awk which is a lot faster than the second which is in "pure" bash. You might be able to adapt it to your own application. 

awk '
{
    for (i = 1; i <= NF; i++) {
        s[i] = s[i]?s[i] FS $i:$i
    }
}
END {
    for (i in s) {
        print s[i]
    }
}' file.txt

declare -a arr

while IFS= read -r line
do
    i=0
    for word in $line
    do
        [[ ${arr[$i]} ]] && arr[$i]="${arr[$i]} $word" || arr[$i]=$word
        ((i++))
    done
done < file.txt

for ((i=0; i < ${#arr[@]}; i++))
do
    echo ${arr[i]}
done
user_19446310

Simple 4 line answer, keep it readable.

col="$(head -1 file.txt | wc -w)"
for i in $(seq 1 $col); do
    awk '{ print $'$i' }' file.txt | paste -s -d "\t"
done
user_69048880

I'm a little late to the game but how about this:

cat table.tsv | python -c "import pandas as pd, sys; pd.read_csv(sys.stdin, sep='\t').T.to_csv(sys.stdout, sep='\t')"

or zcat if it's gzipped.

This is assuming you have pandas installed in your version of python

user_6785720 
for i in $(seq $(head -n1 file.txt | tr ' ' '\n' | wc -l))
do
  cut -d' ' -f"$i" file.txt | paste -s -d' ' -
done

or

seq $(head -n1 file.txt | tr " " "\n" | wc -l) | xargs -I{} sh -c 'cut -d" " -f"{}" file.txt | paste -s -d" " -'
user_7184530

If you wanna just directly print the transposed form, and u know for sure the column/field count (i.e. NF) is consistent across the input file, then just do it all in one shot :

 {m,g}awk '
  END {  _____ = ", "
            __ = gsub("\n", "&")
          ____ = NF
      
      for(___ ^= _<_; ___<=__; ___++) {
         for(_ = ___; _<=____; _+=__) {
              
         printf("%s%s",$_, ____<(_+__)\
                       ? "\n" : _____) }  } }' FS='[[:space:]]+' RS=

|

0, 3, 6, 9
1, 4, 7, 10
2, 5, 8, 11

As for concerns regarding overloading system resources with too many fields at once, I've gotten mawk2 to allocate 1.331 billion fields, using a non-default OFS, in 10.5 secs :

( date | mawk2 '$!NF=NF=(191)^+(OFS="4")'; )  

 7.82s user 2.30s system 96% cpu 10.499 total
     1  1,330,863,361

By comparison, mawk 1.3.4 was only slightly behind at 11.03 secs

( date | mawk '$!NF=NF=(191)^+(OFS="4")'; )  

8.37s user 2.28s system 96% cpu 11.031 total
     1  1,330,863,361

… but the amazing gawk 5.1.1 took 6x the time to allocate only 39% of that amount :

( date | LC_ALL=C gawk -be '$-_=NF=(151)^+(OFS="4")'; )  

14.59s user 34.98s system 77% cpu 1:04.38 total

     1  519,885,601
user_146721140
Related