How do I transpose a row to a column in a tab-delimited file?

Question

I have a tab-delimited file with a number and the names belonging to the same number on the same row. The number and names are separated by a tab. The names are linked with each other by 2 underscores (__). It looks like this:

33  Hhe.1__Hhe.2__Hhe.3__Hhe.4

I would like to convert it (by using the command line) to this output:

33  Hhe.1
33  Hhe.2
33  Hhe.3
33  Hhe.4

Answer 1

With awk:

$ awk -F '\t|__' '{for (i=2;i<=NF;i++) {printf "%s\t%s\n", $1, $i}}' foo.txt 
33  Hhe.1
33  Hhe.2
33  Hhe.3
33  Hhe.4

• We split the lines into fields based on tab (\t) or two underscores (__).
• Then we loop over fields from the second till the last, and print each prefixed with the first field and a tab.

Answer 2

You can use a perl one-liner for that:

perl -ane '@l=split(/__/,$F[1]); foreach $val (@l){print $F[0],"\t",$val,"\n"}'

Example:

$ echo "33  Hhe.1__Hhe.2__Hhe.3__Hhe.4" | perl -ane '@l=split(/__/,$F[1]); foreach $val (@l){print $F[0],"\t",$val,"\n"}'
33  Hhe.1
33  Hhe.2
33  Hhe.3
33  Hhe.4

Explanation of the commands used:

perl -ane                    #read input line-wise and split line on tab
'@l=split(/__/,$F[1]);       #split the second element ($F[1]) on a double _
foreach $val (@l){           #for each value, print the first element and the value.
  print $F[0],"\t",$val,"\n"
 }'

Answer 3

Another way using Perl:

perl -lane '$,="\n"; print(map($F[0] . "\t" . $_, split("__", $F[1])))' file

perl -lane '
    $,="\n";
    print(map($F[0] . "\t" . $_, split("__", $F[1])))
' file

• -l[octnum]: enables automatic line-ending processing. It has two separate effects. First, it automatically chomps $/ (the input record separator) when used with -n or -p. Second, it assigns $\ (the output record separator) to have the value of octnum so that any print statements will have that separator added back on. If octnum is omitted, sets $\ to the current value of $/.
• -a: turns on autosplit mode when used with a -n or -p. An implicit split command to the @F array is done as the first thing inside the implicit while loop produced by the -n or -p.

• -n: causes Perl to assume the following loop around your program, which makes it iterate over filename arguments somewhat like sed -n or awk:

  LINE:
    while (<>) {
        ...             # your program goes here
    }
  

• -e: may be used to enter one line of program;

• $,="\n"; print(map($F[0] . "\t" . $_, split("__", $F[1]))); sets the output field separator to a newline character, splits the second field on __ and prepends the first field followed by a tabulation to each subfield, finally printing the record.

% cat file
33  Hhe.1__Hhe.2__Hhe.3__Hhe.4
% perl -lane '$,="\n"; print(map($F[0] . "\t" . $_, split("__", $F[1])))' file
33  Hhe.1
33  Hhe.2
33  Hhe.3
33  Hhe.4

Answer 4

Yet another perl one-liner:

$ perl -lane 'print "$F[0]\t$_" for split(/__/,$F[1])' file 
33  Hhe.1
33  Hhe.2
33  Hhe.3
33  Hhe.4

This is the same basic idea as the existing Perl answers, only shorter. The -a automatically splits each line of the input file on whitespaces and makes the resulting fields available as the array @F. So, $F[0] is the 1st field, $F[1] the second and so on. The -n means "read each input file line by line and apply the script given by -e. The -l removes trailing \n characters from each input line and adds a \n to each print call.

split(/__/,$F[1]) creates an array by splitting the 1st field of the file on the characters __. So, print "$F[0]\t$_" for split... will iterate over the array returned by the split call and print the first field of the file ($F[0]), a tab character \t and the current field of the split array.

Answer 5

Similar to what muru suggested: what about splitting the second field based on __ and then looping through the slices?

awk 'BEGIN{FS=OFS="\t"}
     {n=split($2,a,"__"); for (i=1;i<=n;i++) print $1, a[i]}' file

This uses the fact that split() returns the number of elements created.

Also, it sets the Input and Output Field Separators to the tab, so that you then don't have to mention it when you print. In fact, FS doesn't need to be set here, because FS defaults to the space and tab is included.

It returns:

33  Hhe.1
33  Hhe.2
33  Hhe.3
33  Hhe.4