6

I have a file and I want to remove all the line if it has a dot in the 5th Column.

Input File:

sc2/80         20      .        A        C        80      PASS    N=2       F=5;U=4
sc2/60         55      .        G        .        72      PASS    N=2       F=5;U=4 
sc2/68         20      .        T        A        71      PASS    N=2       F=5;U=4 
sc2/10         24      .        T        .        31      PASS    N=2       F=5;U=4
sc2/40         59      .        T        .        31      PASS    N=2       F=5;U=4
sc2/24         24      .        A        G        38      PASS    N=2       F=5;U=4

Expected Output:

sc2/80         20      .        A        C        80      PASS    N=2       F=5;U=4
sc2/68         20      .        T        A        71      PASS    N=2       F=5;U=4
sc2/24         24      .        A        G        38      PASS    N=2       F=5;U=4

Note: The file has a size of 64G and it has 690,000,000 lines in it. Is there an efficient solution as it is quite a big dataset. I would prefer Awk and Sed as I am new in the field. Thanks for your help.

9

You can try this

awk '{ if ( $5 != "." ) { print $0; } }' input_file.Txt > output_file.txt

This will test if 5th column is not a dot and display the line.

1
  • 14
    Shorter: awk '$5 != "."' input_file.Txt > output_file.txt – manatwork Oct 11 '13 at 15:20
5

With grep, assuming the columns are tab separated, here using the ksh93/zsh/bash $'...' quotes:

LC_ALL=C grep -v $'^\([^\t]*\t\)\{4\}\.\t'

If the columns are separated by any amount of blanks:

LC_ALL=C grep -vE '^([^[:blank:]]+[[:blank:]]+){4}\.[[:blank:]]'

GNU grep is generally faster than awk (especially GNU awk) or sed. Setting the locale to C generally speeds things up as well.

2
  • Can you define faster? 1%, 10%, 100%, 1000%? For most cases I would prefer awk's readibility. – Bernhard Oct 14 '13 at 11:45
  • @Bernhard, from -100% to +1000% depending on what you're doing and on what data and in what locale. The regex engine in GNU grep is very efficient. In this particular case it may or may not be more efficient, but as the question was about processing huge files where performance is important, it's worse giving it a try. It will most certainly be more efficient than the sed equivalent. I agree that if performance is not a concern, the awk one is a lot better. – Stéphane Chazelas Oct 21 '13 at 9:15
4
awk '$5 != "."' your-big-file     # from manatwork's comment

or

sed -n '/^ *[^ ]\+ \+[^ ]\+ \+[^ ]\+ \+[^ ]\+ \+\. /!p' your-big-file

The portable version from Stephane Chazelas's comment:

sed -n '/^ *[^ ][^ ]*  *[^ ][^ ]*  *[^ ][^ ]*  *[^ ][^ ]*  *\. /!p' your-big-file

The version assumes the separator is a space, but you can adapt it.

Performance:

Namrata said (comment) that the sed script is faster than the awkone. Stephane Chazelas thinks the grep equivalent may be even faster...

3
  • I have also provided a sed version. Please run both using time awk '...' your-file and time sed '...' your-file and let us know the timings... Cheers – oHo Oct 11 '13 at 15:43
  • Currently I ran it with a small dataset. With the small dataset with the sed command it took the least time. Thanks a lot!! – Namrata Oct 11 '13 at 16:01
  • 1
    Note that x\+ is not portable. The portable equivalent is x\{1,\} or xx* – Stéphane Chazelas Oct 11 '13 at 16:27

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.