8

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 
3
  • 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. Sep 17, 2019 at 12:14
  • if you're doing bioinformatics work why not just use R
    – qwr
    Sep 17, 2019 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 Sep 18, 2019 at 19:35

5 Answers 5

16

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.

10

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
1
  • 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, 2019 at 9:52
9

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
9

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
2
  • 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, 2019 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, 2019 at 18:08
2

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 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .