2

I have a csv file from which I extract data relevant to me. These are reference numbers on the one hand and genetic information in the form of the letters G, A, T and C on the other.

Content csv:

1,S188823,188823,,,,ACTCTCGA,,CTGTACCA,ID23,
1,S189843,189843,,,,ACCCTGGA,,CTTGTACA,ID23,
...

The information relevant to me is 188823,,,,ACCCTGGA,,CTTGTACA From each line. Duplicates must be removed in the process. Also the first two lines have to be truncated.

This is what I currently do:

cat File.csv | cut -d "," -f 3,9,7 | uniq | sed -e '1d' -e '2d'

The result looks like this:

188823,ACTCTCGA,CTGTACCA
189843,ACCCTGGA,CTTGTACA
...

But now two things have to be done, which I fail at. Field 3 and field 2 have to be swapped

188823,CTGTACCA,ACTCTCGA
189843,CTTGTACA,ACCCTGGA
...

and now from field2 the bases have to be "reverse complemented" This means that each A becomes a T, each C becomes a G, each G becomes a C and each T becomes an A and the sequence order is reversed. So, CTGTACCA becomes TGGTACAG.

The final result must then look like this:

188823,TGGTACAG,ACTCTCGA
188823,TGTACAAG,ACCCTGGA

I hope it is understandable and you can help me. Some help about reverse complement building can be found here: revcomp builder online

1
  • 1
    How do the first two lines need to be truncated? Do you mean completely removed or truncated?
    – terdon
    Dec 24 '20 at 14:00
5

With GNU awk:

awk -F, '!seen[$3 FS $9 FS $7]++ {
    cmd="echo \047" $9 "\047 | rev | tr ATCG TAGC";
    if ((cmd |getline $9)>0){ print $3, $9, $7; };
    close(cmd);
}' OFS=, infile

Output:

188823,TGGTACAG,ACTCTCGA
189843,TGTACAAG,ACCCTGGA

add NR>2 to the command like awk 'NR>2{ ...; }' to skip first two lines from the input.

Here we used [getline into a Variable from a Pipe] with predefined command in cmd="..." $9 "..." (note that we put only things related to the external command within double-quotes), then call it from the Pipe to getline and save the result into same $9 field; then we printed desired fields in output if a getline result was successful.

finally we should close(cmd) the command we opened.

!seen[$3 FS $9 FS $7]++ is used to ignore processing duplicates lines on fields #3, #9 and #7.

0
3
$ cat tst.awk
BEGIN {
    FS=OFS=","
    map["A"] = "T"
    map["C"] = "G"
    map["G"] = "C"
    map["T"] = "A"
}
{
    str = ""
    for (i=1; i<=length($9); i++) {
        str = map[substr($9,i,1)] str
    }
    print $3, str, $7
}

$ awk -f tst.awk content.csv
188823,TGGTACAG,ACTCTCGA
189843,TGTACAAG,ACCCTGGA

or if you'd like to have tr and rev type functions in awk:

$ cat tst.awk
BEGIN { FS=OFS="," }
{ print $3, rev(tr($9,"ACGT","TGCA")), $7 }

function tr(oldStr,oldChars,newChars,   i,pos,oldChar,newStr) {
    for (i=1; i<=length(oldStr); i++) {
        oldChar = substr(oldStr,i,1)
        pos     = index(oldChars,oldChar)
        newStr  = newStr (pos ? substr(newChars,pos,1) : oldChar)
    }
    return newStr
}

function rev(oldStr,    i,newStr) {
    for (i=1; i<=length(oldStr); i++) {
        newStr = substr(oldStr,i,1) newStr
    }
    return newStr
}

$ awk -f tst.awk content.csv
188823,TGGTACAG,ACTCTCGA
189843,TGTACAAG,ACCCTGGA

or just one function that does both:

$ cat tst.awk
BEGIN { FS=OFS="," }
{ print $3, trev($9,"ACGT","TGCA"), $7 }

function trev(oldStr,oldChars,newChars, i,pos,oldChar,newStr) {
    for (i=1; i<=length(oldStr); i++) {
        oldChar = substr(oldStr,i,1)
        pos     = index(oldChars,oldChar)
        newStr  = (pos ? substr(newChars,pos,1) : oldChar) newStr
    }
    return newStr
}

$ awk -f tst.awk content.csv
188823,TGGTACAG,ACTCTCGA
189843,TGTACAAG,ACCCTGGA
1

We can use perl for this.

  • split lines on comma and store fields in zero indexed array @F:
  • set list separator $, and list joiner $" to comma.
  • reverse the characters of the 9th element $F[8] and then apply the character translation.
$ perl -F, -lane '
    BEGIN { $, = $" = ","; }
    print $F[2], reverse($F[8]) =~ tr/ATGC/TACG/r, $F[6]
      if $. > 2 && !$h{"@F[2,6,8]"}++;
' file
188823,TGGTACAG,ACTCTCGA
189843,TGTACAAG,ACCCTGGA

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.