# Awk - Compare Value from a column with a variable, count how many times it passes for each ID

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.

## 2 Answers

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
``````