I have been trying to find a solution but now I need some help.

First, as input, I have a Big File (5.5G) with this structure:

scaffold4691_size302    2       T       1
scaffold4691_size302    3       A       1
scaffold4691_size302    4       a       1
scaffold4691_size302    5       a       1
scaffold4691_size302    6       g       2
scaffold4691_size302    7       c       2
scaffold4691_size302    8       c       2
scaffold4692_size187    68      g       4
scaffold4692_size187    69      c       4
scaffold4692_size187    70      a       4
scaffold4692_size187    71      a       4

What I want is to count for each ID of the FIRST column ($1), how many times the value in the FOURTH column ($4) is greater than or equal to X (e. g. X=4 in the toy model).

So, as input, I expect:

scaffold4691_size302    0
scaffold4691_size187    4

I could easily do it on python as I am more confortable with it but the size is too large.

So far, I did this:

awk 'NR>1 { scf=$1; { if ($4>=4){count++;}}} {print scf "\t" count}' toyModel

But it returns me all the line and a total count. I want to know how to refresh the count for a new ID.


You need to accumulate the counts per id and print the result after processing the file:

awk '!counts[$1] { counts[$1] = 0 }; $4 >= 4 { counts[$1]++ }; END { for (key in counts) print key, counts[key] }' toyModel

The first statement ensures that we measure ids that never match the criterion (ending up with 0 output). The second increments the count for matching lines. The last statement iterates over the keys (ids) and prints out the key and matching count.

If the size is too large for this approach (the counts array becomes too big), and your ids are contiguous in the file, you can use this approach instead:

awk 'curid != $1 { if (NR > 1) print curid, count; curid = $1; count = 0 }; $4 >= 4 { count++ }; END { print curid, count }' toyModel

This keeps the current id and count, and prints them whenever the id changes (and at the end). Whenever the fourth field is greater than 4, the count is incremented, and whenever the id changes, the count is reset.

perl -lane '$h{$F[0]} += $F[3] >= 4 ? 1 : 0}{print "$_\t$h{$_}" for keys %h' toyModel

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.