2

I have a data file A.tsv (field separator = \t) :

id  clade   mutation
243 40A S:ojo,L:juju,S:lili
254     
267 40B J:jijy,S:asel,M:ase

And I want to print in another column (in a new file B.tsv) only mutation that start with S:, like this :

id  clade   mutation    S_mutation
243 40A S:ojo,L:juju,S:lili S:ojo,S:lili
254     
267 40B J:jijy,S:asel,M:ase S:asel

I tried some command with awk with no result :

awk -F '\t' 'BEGIN { OFS = FS } NR==1 {$(NF+1)="S_Mutation"} ; NR != 1 { $4 = ($3==^[Ss] ? $4 ) }; 1' A.tsv > B.tsv

Do you have an idea how to do that ? Thanks

2
  • Is it correct that you don't want a comma separating the added columns from the originals?
    – mattb
    Jun 23, 2021 at 8:46
  • Yes the Field separator of my file is a tab (\t).All the column including the new one needs do be tab-separated
    – nstatam
    Jun 23, 2021 at 8:53

5 Answers 5

4

With POSIX awk, we can do as shown. Split the third fied on comma for rows >1 and select those elements that begin with S: and then join them with comma, and make it the last+1 field.

awk -F '\t' '
  BEGIN {
    OFS = FS
    _SEP_ = ","
  }

  NR==1{$(NF+1) = "S_mutation"}

  NR>1&&length($3)>0{
    nf = split($3, a, _SEP_)
    t = ""
    for (i=1; i<=nf; i++) {
      if (a[i] ~ /^S:/) {
        t = t (t=="" ? t : _SEP_) a[i]
      }
    }
    $(NF+1) = t
  }1
' file

Same thing in Perl but with the use of regexes

perl -lnse '$,="\t";
  print $_,($.==1?q(S_mutation):
  "@{[/(?:\t|,)\KS:[^,]*/g]}"||());
' -- -\"=, ./file

Output:

id   clade  mutation             S-mutation
243  40A    S:ojo,L:juju,S:lili  S:ojo,S:lili
254                              
267  40B    J:jijy,S:asel,M:ase  S:asel
2
  • 1
    instead of setting $(NF+1) that it's squeezing consecutive Tabs into one (for example in line 4th of the OP's sample data), you can do ` print $0, (NR==1?"S_mutation":t)` and so you need to remove the idiom 1 plus NR==1{$(NF+1) = "S_mutation"} and $(NF+1) = t parts then Jun 23, 2021 at 15:45
  • Thanks for the tip! will try to incorporate it in the solution.
    – guest_7
    Jun 24, 2021 at 8:04
3

Using awk (POSIX).

awk -F "\t" '
BEGIN   { OFS = FS }
NR == 1 { $4 = "S_Mutation" }
NR  > 1 && NF == 3 {
    printf "%s", $0 "\t"
    $0 = $3
    gsub(/[^S]:[^,]*,?/, "")
    sub(/,$/, "")
    print $1
    next
}
1' data.txt
  • If line is > 1 and number of fields = 3
    • Print line, without trailing newline
    • Set line = field 3
    • Remove strings not starting with S:
    • Remove any trailing comma
    • Print field with newline
    • Process next line
  • Else
    • Print line

You might want to not print trailing tab, test if $1 is anything after substitution and then print TAB + field. (In case you do not want trailing tabs on lines where there are no S:xxx fields. Something like:

    # Print line
    printf "%s", $0
    # Set line = field 3
    $0 = $3
    # Replace all N:xxx where N is not S
    gsub(/[^S]:[^,]*,?/, "")
    # Remove trailing comma
    sub(/,$/, "")
    # Print the field
    if ($1 != "")
        print "\t"$1
    else
        print ""
    # Continue with next line
    next
2

With Perl

perl -F'\t' -lpe '
    push @F, $. == 1 ? "S_mutation" : join ",", grep { /^S:/ } split /,/, $F[2];
    $_ = join "\t", @F
' A.tsv > B.tsv

Ex.

$ perl -F'\t' -lpe '
    push @F, $. == 1 ? "S_mutation" : join ",", grep { /^S:/ } split /,/, $F[2];
    $_ = join "\t", @F
' A.tsv | column -t
id   clade  mutation             S_mutation
243  40A    S:ojo,L:juju,S:lili  S:ojo,S:lili
254
267  40B    J:jijy,S:asel,M:ase  S:asel
2
awk 'BEGIN{ FS=OFS="\t" }
{ c3=$3; gsub(/(^|,)[^S]*(,|$)/, ",", c3); gsub(/^,+|,+$/, "", c3)
  print $0, (NR==1?"S_Mutation":c3)
}' infile

FS is the input field separator and OFS is the output field separator; both we set to a Tab \t character; then we took a backup from the 3rd field by c3=$3 for doing substitution on that and keep original intact.

then the first gsub() function replaces those sub-fields (which they are delimited with a comma character) with a comma that if that is not started with S char; second gsub() removes the leading(^,+)/trailing(,+$) comma(s) that they may appear after some removal.

then we print the entire line followed by the final value of the variable c3 or S_Mutation string if it was the first line that ternary operator (condition?if-true:if-false) controls that which to print when condition met.

1

If you don't mind using sed:

sed 'h;s/.*[[:space:]]/,/;s/,[^S]:[^,]*//g;x;G;s/\n,/\t/'

Please note that \t will only work with GNU sed. For other flavours, insert a literal tab, usually by entering ctrlv and then the tab key.

The concept is to save the line in the hold space, then to isolate the last column and remove all mutations except for the S: ones. Then append this as the last column to the saved line:

  • h copies the line to the hold space
  • s/.*[[:space:]]/,/ replaces everything upto the last tab or whitespace by a comma, so the last column remains, prepended by a comma. This comma will help in the next step
  • s/,[^S]:[^,]*//g removes all elements except the S: ones
  • x exchanges hold space and pattern space, so G will append the new column to the original one kept in hold space
  • s/\n,/\t/ finally replaces the newline from appending and the comma we introduced by a tab

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .