0

I have a file that looks like this,

 REF     QUERY   COVR    COVQ
sca_10_chr8_2_0 scaffold_2     0.08    2.48
sca_10_chr8_2_0 scaffold_1     0.02    0.02
sca_10_chr8_2_0 scaffold_2      0.22    0.06
sca_10_chr8_2_0 scaffold_2      0.21    0.05
sca_10_chr8_2_0 scaffold_1     0.21    0.27
sca_10_chr8_2_0 scaffold_2     0.21    0.64
sca_10_chr8_2_0 scaffold_4      0.20    0.06
sca_10_chr8_2_0 scaffold_8      0.20    0.07
sca_10_chr8_2_0 scaffold_10     0.21    0.08

For each different combinations of column 1 and 2, or duplicates based on column 1 and 2, I want to sum column 4 (total coverage) and have the output file like this:

REF QUERY COVQ
sca_10_chr8_2_0  scaffold_2  1.52
sca_10_chr8_2_0  scaffold_1  0.29
sca_10_chr8_2_0  scaffold_4  0.06
sca_10_chr8_2_0  scaffold_8  0.07
sca_10_chr8_2_0  scaffold_10  0.08
0
3
$ cat tst.awk
NR==1 { print $1, $2, $4; next }
{ sum[$1 OFS $2] += $4 }
END {
    for (key in sum) {
        print key, sum[key]
    }
}

$ awk -f tst.awk file
REF QUERY COVQ
sca_10_chr8_2_0 scaffold_8 0.07
sca_10_chr8_2_0 scaffold_10 0.08
sca_10_chr8_2_0 scaffold_1 0.29
sca_10_chr8_2_0 scaffold_2 3.23
sca_10_chr8_2_0 scaffold_4 0.06

I assume the expected output in your question is wrong since the above IS the sum of the 4th column values for every $1 and $2 pair.

1
  • Thank you so much for the code. It worked and yes the expected output file values might be wrong in my question but what I wanted was sum of 4th column for every $1 and $2 pair.
    – ambika
    May 21 at 14:05
2

Using Miller:

$ mlr --pprint --ofmt '%.2f' stats1 -a sum -g REF,QUERY -f COVQ yourfile
REF             QUERY       COVQ_sum
sca_10_chr8_2_0 scaffold_2  3.23
sca_10_chr8_2_0 scaffold_1  0.29
sca_10_chr8_2_0 scaffold_4  0.06
sca_10_chr8_2_0 scaffold_8  0.07
sca_10_chr8_2_0 scaffold_10 0.08
2
  • I tried this but unfortunately this program is not available in the supercomputer I am using. Do you have any idea how to do this using awk, sed, grep
    – ambika
    May 21 at 3:02
  • @ambika it's impossible to do math with grep or sed so you can rule those out. grep is for doing g/re/p while sed is for doing s/old/new/.
    – Ed Morton
    May 21 at 3:27

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.