extract data from file and manipulate content to write to new file

Question

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

Answer 1

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.

Answer 2

$ 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

Answer 3

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