I have a tab delimited file which looks like this:

gene    v1  v2  v3  v4
g1  NA  NA  NA  NA
g2  NA  NA  2   3
g3  NA  NA  NA  NA
g4  1   2   3   2

The number of fields in every line is fixed and same. I want to remove those rows from the above file where all the fields for every row from column 2 through last is NA. Then the output should look like:

gene    v1  v2  v3  v4
g2  NA  NA  2   3
g4  1   2   3   2 
  • If the non-NA fields are always nonnegative integers, a regular expression as simple as \s\d differentiates between the “good” and the “bad” lines. – Roman Odaisky Sep 17 at 12:14
  • if you're doing bioinformatics work why not just use R – qwr Sep 17 at 21:14
  • Because I am using command line tools upstream to generate this file and I will prefer awk or perl solution if I don't have to save the file to open in R. Of course in R you can remove this with is.na check if I think – user3138373 Sep 18 at 19:35

With awk:

awk '{ for (i=2;i<=NF;i++) if ($i!="NA"){ print; break } }' file

Loop through the fields starting at the second field and print the line if a field not containing NA is found. Then break the loop.


Using GNU sed

sed -e '/g[0-9]\+\(\s*NA\s*\)\+$/d' filename

Short explanation:

g[0-9]\+\(\s*NA\s*\)\+$ is a regex matching g followed by at least one digit, then any number of NAs with optional spaces between until the end of the line.

sed -e '/<regex>/d' deletes all lines that match <regex>

A more standard regexp with the same meaning would be:

sed -Ee '/g[0-9]+([[:space:]]*NA[[:space:]]*)+$/d' filename
  • 4
    Please note that \+ and \s are non-standard regular expressions and will match a simple + or s in most sed versions. Use \{1,\} instead of \+ and [[:space:]] instead of \s to have portable code. – Philippos Sep 17 at 9:52

With all from the Perl List::Util module:

$ perl -MList::Util=all -alne 'shift @F; print unless all { $_ eq "NA" } @F' file
gene  v1  v2  v3  v4
g2    NA  NA  2   3
g4    1   2   3   2

With grep:

egrep -v -x 'g[0-9]+([[:blank:]]+NA)*[[:blank:]]*' filename

This causes grep to not display (-v) lines where the entire line (-x) matches:

  • lower case g in first column, followed by one or more digits
  • any number of instances of whitespace followed by NA
  • optional trailing whitespace
  • 1
    +1, but note also that the number of fields is fixed, so you could use {4} instead of * after the NA group, and you may want to change the first [[:blank:]]* to [[:blank:]]+ to make the whitespace separators mandatory. Regardless, I have never understood why everyone insists on pulling out the awk bazooka to solve these simple filtering problems that grep handles easily. – Kevin Sep 18 at 17:20
  • Thank you for the feedback, @Kevin. I incorporated your first suggestion, but I am retaining the other * so that this solution works equally well for any arbitrary number of NA columns, so long as they are all NA. – Jim L. Sep 18 at 18:08

You could try:

$ grep -P '\t(?!NA(\t|$))' file

$ sed -e 'h;s/\tNA//g;/\t/!d;g' file

$ perl -MList::MoreUtils=any -F'\t' -lane 'print if any { ! /^NA$/ } @F[1..$#F]' file 

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.