transpose one line/lines from row to column using shell (awk,sed)

When manipulating data, sometime, I want transpose 1 line or lines from row to column. for example, from this

c1,c2,c3
1,2,3
4,5,6
7,8,9

to that
c1,1,4,7
c2,2,5,8
c3,3,6,9

To do the head line only, I use

head -1 file.csv | sed -e"s/,/\n/g" | awk '{print NR, $1}'

here is my tiny script

#!/bin/sh
f=$1
sep=$2
if [ "$sep" == "" ]; then
  if [[ "$1" =~ "csv" ]]; then
    sep=","
  else
    sep=" "
 fi
fi
head -1 $f | sed -e "s/[$sep|\t]/\n/g" | awk '{print NR, $1}'

you can save it as ‘transhd’ in ~/bin/. chmod 700 transhd

to use it
transhd file.csv # assume comma as the seperator

transhd file.txt # assume a spce as the seperator
or one any line , say line 5

awk -F, 'NR==5 {print $0}' file.csv | sed -e"s/,/\n/g" | awk '{print NR, $1}'

here are two scripts from others

###################
num=$(awk -F"," 'NR==1 { print NF }' data)
print $num
i=1
while (( $i <= $num ))
do
newline=''
for val in $(cut -d"," -f$i data)
do
newline=$newline$val","
done
nline=`print ${newline%?}`
print $nline >> tmpdata
(( i = i + 1 ))
done
mv tmpdata data
####################

A awk script as file trans.awk

BEGIN {FS=","}
{
for (i=1;i<=NF;i++)
{
arr[NR,i]=$i;
if(big <= NF)
big=NF;
}
}
END {
for(i=1;i<=big;i++)
{
for(j=1;j<=NR;j++)
{
printf("%s\t",arr[j,i]);
}
printf("\n");
}
}

awk -f trans.awk file.csv

About these ads

12 responses to “transpose one line/lines from row to column using shell (awk,sed)

  1. Hello , I have a row like following
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 – is in a file
    i want that after every fourth column next four should come in new line
    1 2 3 4
    5 6 7 8
    9 10 11 12
    13 14 15 16
    Can you help me with this ?

    • IT may be not easy to do this in awk or sed, but you can try perl
      assume file a.txt is
      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
      2 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
      4 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
      5 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
      you can simply insert a newline code “\n” in to the begining of every 5th, 9th, 13th column of each line

      here is a example commandline code

      perl -e ‘while ( ) {@c=(4,8,12); @a=split; foreach $i (@c) {$a[$i]=”\n$a[$i]“;} printf “%s\n”, join ” “, @a }’ < a.txt

      1 2 3 4
      5 6 7 8
      9 10 11 12
      13 14 15 16
      2 2 3 4
      5 6 7 8
      9 10 11 12
      13 14 15 16
      4 2 3 4
      5 6 7 8
      9 10 11 12
      13 14 15 16
      5 2 3 4
      5 6 7 8
      9 10 11 12
      13 14 15 16

      Gook luck

  2. Thanks a lot , but if i can make it a bit generic, i am completely unknown to perl – like we give input that we want a newline at 4th or 9th or 15th , any advice

    Thanks & Regards

  3. take this example, if you want a newline at the end of 4th, 9th, 11th, 34th columns etc, you can change the command line as

    perl -e ‘while ( ) {@c=(4,9,11,34); @a=split; foreach $i (@c) {$a[$i]=”\n$a[$i]“;} printf “%s\n”, join ” “, @a }’ < a.txt

    the perl array index starts at 0

  4. awk ‘{for(i=10;i<=NF;i+=9)$i=RS $i;}1' file
    or
    xargs -n9 < infile
    solved the purpose.

    Thanks a lot for your valuable feedback.

    Solved my purposed

  5. Hello all,

    I am linux beginner I want to make a script. In which I need input from 2 files and dispay them as one row in third file..
    for example:
    a1.txt-
    1
    2
    3
    a2.txt-
    4
    5
    6
    a3.txt
    1 4
    2 5
    3 6

  6. I’m using the trans.awk above. I’m getting this output
    fieldname1 data1
    fieldname2 data2
    and so on but I would like to have this instead
    fieldnr1 fieldname1 data1
    fieldnr2 fieldname2 data2
    and so on.
    How could I change that script to get this?

    • you need a bit amendment on this script to print out field numbers

      BEGIN {FS=","}
      {
      for (i=1;i<=NF;i++)
      {
      arr[NR,i]=$i;
      if(big <= NF)
      big=NF;
      }
      }
      END {
      for(i=1;i<=big;i++)
      {
      printf "%s\t", i; # print out field number
      for(j=1;j<=NR;j++)
      {
      printf("%s\t",arr[j,i]);
      }
      printf("\n");
      }
      }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s