1

I have a very long data frame (~11 million x 130) for which is sparsely filled with percentages (0.00 - 100.00, float).

The entries that are missing are labeled "NA", like so:

cat1    NA    NA    99.90    NA    NA    10.90    NA
cat2    NA    1.00    NA    19.12    NA    NA    NA  
cat3    NA    NA    NA    NA    NA    NA    45.00

I want to go through this data frame row by row and sum up the occurrences of "NA".

I can normally accomplish this very easily in R by doing apply(data.frame, 1, function(x) sum(is.na(x)), but because the file is so large I keep having memory problems. I'd like to do this with AWK if possible so that I can apply this on the command line for future similar problems.

I am not great with awk, but I essentially want to sum the occurrences of "NA" across fields for each record/row and then later I can use this as an index for skipping lines of the file.

Thanks in advance

  • to sum up NA across all rows at once, or get sum for each separate record? – RomanPerekhrest Nov 18 '19 at 7:11
  • @RomanPerekhrest sum for each separate record – Reilstein Nov 18 '19 at 16:27
1

Straightforwardly:

$ awk '{ for (i=2;i<=NF;i++) if ($i=="NA") c++; print c; c=0 }' file
5
5
6
  • Yeah this looks like it works. I'm not super familiar w/ the internal workings of AWK, but is this mechanism efficient for ~10 million records? would a gsub() based method work more efficiently? – Reilstein Nov 18 '19 at 16:47
  • @Reilstein, the better way is to try both approaches on your actual data – RomanPerekhrest Nov 18 '19 at 16:56
  • in the process of doing that now, thanks! – Reilstein Nov 18 '19 at 16:58
  • 1
    both methods worked sufficiently fast for this use case. I will remember this for future large datasets. Thanks! – Reilstein Nov 18 '19 at 19:15
1

You could just use a string replacement function that matches the pattern NA and count the number of occurrences it is doing per line and sum it up for the whole file as below.

awk '{ sum += gsub(/NA/, "", $0) } END { print sum }' file

or make it more robust to avoid false matches to the pattern NA. The below regex is based on your input sample where NA does not appear at the start of the file.

awk '{ sum += gsub(/[[:space:]]*NA[[:space:]]*|[[:space:]]*NA$/, "", $0) } END { print sum }' file
  • Yes i think this could work. I want the sum for each individual record, so would I just change it to awk '{ sum += gsub(/NA/, "", $0)} {print sum}' file ? – Reilstein Nov 18 '19 at 16:32
  • @Reilstein : Yes that could work! – Inian Nov 18 '19 at 16:52
  • actually it would be awk '{ sum += gsub(/NA/, "", $0)} {print sum; sum=0}' file – Reilstein Nov 18 '19 at 16:57

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.