How to select only info start with pattern in a column and print in another one

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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 3^rd 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.

Answer 5

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