I have a very similar problem to this question, but have no idea how to adapt the answer to my own issue.

I have a tab-sep file with 2nd column containing comma-sep list, such as:

TRINITY_DN1_c0_g1   DN1_c0_g1   GO:0000166,GO:0003674,GO:0005488,GO:0005515,GO:0005524,GO:0005575
TRINITY_DN1_c0_g3   DN1_c0_g3   GO:0005829,GO:0006457,GO:0006458,GO:0006950,GO:0008134
TRINITY_DN10_c0_g1  DN10_c0_g1  GO:0050896,GO:0051082,GO:0051084,GO:0051085

I want to get it to this:

TRINITY_DN1_c0_g1   DN1_c0_g1   GO:0000166
TRINITY_DN1_c0_g1   DN1_c0_g1   GO:0003674
TRINITY_DN1_c0_g1   DN1_c0_g1   GO:0005488
TRINITY_DN1_c0_g1   DN1_c0_g1   GO:0005515
TRINITY_DN1_c0_g1   DN1_c0_g1   GO:0005524
TRINITY_DN1_c0_g1   DN1_c0_g1   GO:0005575
TRINITY_DN1_c0_g3   DN1_c0_g3   GO:0005829
TRINITY_DN1_c0_g3   DN1_c0_g3   GO:0006457
TRINITY_DN1_c0_g3   DN1_c0_g3   GO:0006458
TRINITY_DN1_c0_g3   DN1_c0_g3   GO:0006950
TRINITY_DN1_c0_g3   DN1_c0_g3   GO:0008134
TRINITY_DN10_c0_g1  DN10_c0_g1  GO:0050896
TRINITY_DN10_c0_g1  DN10_c0_g1  GO:0051082
TRINITY_DN10_c0_g1  DN10_c0_g1  GO:0051084
TRINITY_DN10_c0_g1  DN10_c0_g1  GO:0051085

There is a variable number of terms in the 3rd column. I need a separate line for each with it's associated 1st and 2nd column.

If any help, the starting one liner from above questions is:

perl -lne 'if(/^(.*?: )(.*?)(\W*)$/){print"$1$_$3"for split/, /,$2}'

But I have no idea which bits needs to be changed to work for my issue!

Many thanks in advance for help.

  • Are you positive there are tabs in your input file, and not sequences of spaces? – glenn jackman Apr 13 at 16:15
  • 1
    Can you provide us the output of the command: cat -A yourfile ? That will show what kind of line ndings are there. – guest_7 Apr 13 at 16:26

This awk command is quite readable:

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

In perl, this is

perl -F'[,\t]' -lane 'print join "\t", @F[0,1], $F[$_] for 2..$#F' file
# or
perl -F'[,\t]' -slane 'print @F[0,1], $F[$_] for 2..$#F' -- -,=$'\t' file

If you're not sure you have actual tab characters:

  • awk: FS = ",|[[:blank:]]+"
  • perl: -F',|\s+'

And for fun, bash

while IFS= read -r line; do
    IFS=, read -ra gos <<< "${line#$prefix}"
    for go in "${gos[@]}"; do echo "$prefix$go"; done
done < file

This version doesn't care about spaces versus tabs, but it will be much slower than perl or awk.

  • Thanks @glenn-jackman but both is giving 3 GO:xxx columns, the first two are repeats of first 2 terms in comma-sep list and 3rd is working way through rest. I need each line to only have one GO:xxx. Definitely tab-separated columns. – AmyE Apr 13 at 16:17
  • I don't know what to tell you except I don't believe you have tabs in the input file. I can reproduce what you're seeing with expand file | awk ... – glenn jackman Apr 13 at 16:22
  • 1
    Oh damn... you were right. Mi real (very big) file is tab-delim. My mocked up one to test your answers was not. My sincere apologies for wasting your time - it works like a charm! – AmyE Apr 13 at 16:24

Use the -a switch to get each line split into the @F array on whitespace.

perl -lane 'print join "\t", @F[0, 1], $_ for split /,/, $F[2]'
  • Thanks for speedy reply! But this outputting nothing :( – AmyE Apr 13 at 16:18
  • It works for me. Did you provide the input file as the last argument? – choroba Apr 13 at 16:19
  • As above, my mistake in test file I was using was not actually tab-delim. Sorry this works too! – AmyE Apr 13 at 16:27
  • 1
    The -a splits on any whitespace, tabs or spaces. – choroba Apr 13 at 16:30

Another option here is the nest --explode action of Miller

mlr --nidx --fs tab nest --explode --values --across-records --nested-fs ',' -f 3 file

or using the shorthand nest specifier

mlr --nidx --fs tab nest --evar ',' -f 3 file

Using GNU sed which has [\n\t] regex features we can do as shown:

sed -n '
      P; s/\t[^\n\t]*\n/\t/
'  file

You can do it using perl also

perl -F'(\t)' -pale '$"="";
  $_ = pop(@F) =~ tr/,/\n/r =~ s/^/@F/mgr;
' file

One method with Perl is shown here. $a scalar holds the first two fields and then the search begins in the while loop from where it was left off due to the /c modifier.

perl -lne '
  my($a) = /^((?:.*?\t){2})/gc;
  print $a, $1 while /\G([^,]+)(?:,|$)/g;
' file

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.