5

I have a large (~900MB) tab-delimited text file that I will be processing in a downstream program. I need to delete any line with a missing value. The correct number of columns are on each line (so a missing value would correspond to 2 tabs).

Note: My actual data has ~2 million lines and 80-300 columns. Possible characters are a-z A-Z 0-9 - (hyphen) _ (underscore) and tab (delimited). No spaces or special characters are in the file.

I am new to this kind of scripting, so an explanation of any code provided would be appreciated. I normally use R, but my filesizes have outgrown the data manipulation functionality of R.

How can I at the terminal (or within a shell script) delete lines with missing values from a file (e.g. using sed)?

Example Input File:

Col1    Col2    Col3
A        B        C
D                 F
G        H        I
J        K        

Example Output File:

Col1    Col2    Col3
A        B        C
G        H        I 
4

If your fields can never contain whitespace, an empty field means either a tab as a first character (^\t), a tab as the last character (\t$) or two consecutive tabs (\t\t). You could therefore filter out lines containing any of those:

grep -Ev $'^\t|\t\t|\t$' file

If you can have whitespace, things get more complex. If your fields can begin with spaces, use this instead (it considers a field with only spaces to be empty):

grep -Pv '\t\s*(\t|$)|\t$|^\t' file

The change filters out lines matching a tab followed by 0 or more spaces and then either another tab or the end of the line.

That will also fail if the last field contains nothing but spaces. To avoid that too, use perl with the -F and -a options to split input into the @F array, telling it to print unless one of the fields is empty (/^$/):

perl -F'\t' -lane 'print unless grep{/^$/} @F' file
  • I like that the perl answer is simple to understand and works no matter how many columns I have. – Gaius Augustus Dec 23 '15 at 3:42
  • @terdon I just tried to run the perl on my data. I happen to know that about 700,000 lines do NOT contain missing data. I used wc -l to compare the number of lines, and this version of the perl did not remove any lines. The answer further down perl -lane 'print if @F==81' file removed the correct number of lines. I'd like to be able to use this if possible though (I need to do this again and would like something that works with any number of columns)...any thoughts? – Gaius Augustus Dec 23 '15 at 3:58
  • 1
    @GaiusAugustus - the first grep in my answer should work regardless of column count - if there are two adjacent tabs anywhere on a line it will not print the line. im curious to know what it would do w/ wc -l. it should also be considerably faster than awk or perl. – mikeserv Dec 23 '15 at 5:08
  • I had to reread the first grep a few times, but I now understand it. I tried it, and it outputs the correct number of rows. Thank you for the clarification. – Gaius Augustus Dec 23 '15 at 5:14
  • 2
    @GaiusAugustus the perl won't change the original file, you need to redirect its output to a new file or use -i. However, now that you've specified that your data can never contain spaces, you can simply use time grep -Ev $'^\t|\t\t|\t$' file1> out4 which is blindingly fast and works on an arbitrary number of fields. – terdon Dec 23 '15 at 10:04
8

With awk:

awk -F"\t" '$1!=""&&$2!=""&&$3!=""' file

Actually it is that simple.

  • awk splits the input at the field separator tab \t specified with the -F flag. This could also be omitted, when your content has no spaces in the fields.
  • $1!=""&&... is a condition. When this condition is true, awk simply prints the line. You could also write '$1!=""&&$2!=""&&$3!=""{print}', but that's not necessary. Awks default behavior is to print the line, when no action is given. Here, that condition is true when the fields $1, $2 and $3 all are not empty, hence when the first 3 fields have a value.

To write to another file use this:

awk -F"\t" '$1!=""&&$2!=""&&$3!=""' input_file >output_file

Edit: With an undefined number of columns you could use this awk, it check every field in the line:

awk -F"\t" '{for(i=1;i<=NF;i++){if($i==""){next}}}1' file
  • If there are more than 3 columns, it is necessary to list all columns, correct? My data may have anywhere from 75-500 columns, so while a great answer for my toy data, may not work as is for my actual data. Any thoughts? – Gaius Augustus Dec 23 '15 at 3:13
  • 1
    @GaiusAugustus See my edit. – chaos Dec 23 '15 at 8:32
5

...for any of the below to work you must first do...

t=$(printf \\t)          ### because it's hard to demo CTRL+V TAB 

...now, with a POSIX grep...

grep -Ev "^$t+|$t($t|$)"     <in >out

grep will select lines which do not match the pattern - which uses the | or metacharacter to denote a ^head-of-line tab, or two consecutive tabs, or a $tail-of-line tab - which are the only possible fail cases as near as I can tell.

without the -vnegation switch it might be:

grep -E "([^$t]+$t){2}[^$t]" <in >out

...which specifies an {occurrence count} for the (pattern group) of + one or more chars in the [class] of characters which are ^ not tabs followed by a tab.


...or with a POSIX sed...

sed -ne"s/[^$t][^$t]*/&/3p"  <in >out

...or...

sed -ne"s/[^$t]\{1,\}/&/3p"  <in >out

...or w/ GNU or BSD seds...

sed -Ene"s/[^$t]+/&/3p"      <in >out

...where sed does -not print by default any line unless it can s///ubstitute for &itself the third occurrence on a line of the longest possible sequence of at least one [^not tab] character.


(using literal tabs should be preferred for portability. the original version of this answer used \backslash escapes and it was not helpful. definitely using \backslash escapes in a [character class] will limit the applicability of your code.)

  • @terdon - it works... (you might try using literal tabs though, as recommended. i have an idea). – mikeserv Dec 22 '15 at 10:59
  • Nope. Still fails if the 1st field is empty. Actually, it looks like grep -E can't understand \t. Try printf 'foo\tbar\n' | grep -E '\t'. You probably need to use $'\t' instead. – terdon Dec 22 '15 at 11:01
  • @terdon - it works. even if the first field is empty. you should try using an actual tab. – mikeserv Dec 22 '15 at 11:05
  • I was using actual tabs, I don't know what you were using. It does work now that you've used a variable for the tab. – terdon Dec 22 '15 at 11:08
  • @terdon... that's really weird... it doesn't help that the data comes in spaces at an off-8 setting. – mikeserv Dec 22 '15 at 11:14
1
awk 'NF==3' file

Prints a line if number of fields equals 3. Pretty straightforward to change number of columns according to your data.

However as pointed out, this doesn't work with OP's requirement of variable number of fields.

  • This doesn't work. If a field is empty, NF will still be 3. It looks like it works because you're not using the correct delimiter. The OP has tab-separated data so there could be spaces within the fields. Your approach will skip such lines. Try, for example: ` printf 'a b\tc\td\n' | awk 'NF==3'. That prints a line with 3 fields but your awk` will think there are 4. – terdon Dec 23 '15 at 9:36
  • Quote: "Possible characters are a-z A-Z 0-9 - (hyphen) _ (underscore) and tab (delimited). No spaces or special characters are in the file." – Joe Dec 23 '15 at 9:38
  • Ah, damn, good point. That wasn't in the original version of the question which is why I hadn't posted precisely this answer :). – terdon Dec 23 '15 at 9:40
  • Oh right, that's probably why every other answer has such a complex approach. :) – Joe Dec 23 '15 at 9:42
  • 1
    There's also the point the OP made in this comment. His lines have a variable number of fields, so this won't work on his actual data. – terdon Dec 23 '15 at 9:43
0

You can try something like this:

grep "^[a-zA-Z0-9]\+[[:space:]][a-zA-Z0-9]\+[[:space:]][a-zA-Z0-9]\+$" input_file > output_file

The purpose of grep is to (or not to) find strings in one or more files that match a given pattern. Here, the pattern [a-zA-Z0-9]\+ matches one or more alphanumeric characters, which is followed by a whitespace or tab. The beginning of a line is matched by ^, whereas $ indicates the end of line. If other characters are used in the columns, they should be added to the character class above. Finally, > redirects the matched output to the output file.

Please also look at @terdon's comment below for potential pitfalls and an alternative solution. Note that if you are working in Linux/Unix environments, the usefulness of grep goes much beyond this particular solution.

  • 1
    +1 but why not just negate the [[:space:]] class instead of attempting to define what's allowed? What if a field contains non alphanumeric characters? Also, you might want to use an actual tab instead of just whitespace since fields could contains spaces. Something like this would be safer: grep '^[^[:space:]]\+'$'\t''[^[:space:]]\+'$'\t''[^[:space:]]\+$' file. – terdon Dec 22 '15 at 11:10
  • @terdon A valid observation and a nice alternative solution! Personally, I wanted to keep it simple. Moreover, the OP says he is "new to this kind of scripting". Nevertheless, could you please explain what the intermediate $ signs in your grep does? – Barun Dec 22 '15 at 11:44
  • 2
    They let grep understand tabs. Vanilla grep doesn't understand \t as a tab but does understand $'\t' since that's expanded by the shell and not grep itself. I just learned, however, that you don't need to break the quotes each time as I did, you can just add the $ at the beginning: grep $'^[^[:space:]]\+\t[^[:space:]]\+\t[^[:space:]]\+$' file. This will still break if any of the fields consists entirely of spaces though. – terdon Dec 22 '15 at 11:48
  • @terdon - grep does understand tabs. some greps don't understand backslash+t. – mikeserv Dec 22 '15 at 18:57
  • What's all this about [[:alnum:]] and [[:space:]]? The question specifies tab-delimited fields. – Gilles Dec 22 '15 at 23:04
0

I have more generic way to do this task

<$your_file perl -CASD -ne 'print if not grep { /^$/ } split "\t"'

@terdon: You are right, now it works as expected.

  • This fails if the missing field is the last one because your split will also include the trailing \n in the field. – terdon Dec 22 '15 at 11:04
  • I'm afraid this still fails if the last field contains spaces but the OP has now clarified that is not a possibility. – terdon Dec 23 '15 at 9:41
-1

This is one perl can do quite neatly:

perl -lane 'print if @F==3';

autosplits into the array @F and if the number of fields is 3, it prints.

Edit: You can auto-configure the field number:

perl -lane '$cols //= @F; print if @F == $cols'

This will set the $cols variable to the number of columns on the first line (which from your example, is the header row).

Note - it doesn't work with white spaced fields (other examples above are more appropriate for that. Something like using -F"\t" to set the delimiter and grep to filter the empty fields)

Note //= is a conditional assignment - it assigns if no value is currently defined. It's also a newer perl feature, and I'm aware there's some quite old perl versions in circulation. (5.8.8 pops up fairly frequently).

If this doesn't work on your version, ||= should do the trick just fine - that's a conditional assignment too, that tests 'truth'. Which should work fine with your data set. (It handles '0' and '' differently to // but those shouldn't show up).

  • The fields are tab-delimited, not whitespace-delimited. Your approach can fail if a field contains a space. – Gilles Dec 22 '15 at 23:03
  • But none the less, works fine given the example data. – Sobrique Dec 22 '15 at 23:14
  • My data has no spaces in it, so this may work. So, I just need to make @F== set to the number of columns in my file, correct? Can I then output this to a file? perl -lane 'print if @F==n' > output.txt? – Gaius Augustus Dec 23 '15 at 3:21
  • Yes. Correct. You could probably auto set it based on the header row. Something like '$n //= @F; print if @F == $n' – Sobrique Dec 23 '15 at 7:31
  • On older perl you would need ||= which will probably accomplish the same result either your data. (a conditional assignment that sets $n if it doesn't currently have a value - // tests if it is defined, where || tests if it is true, and that would only be a problem if you had zero columns. – Sobrique Dec 23 '15 at 7:41

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.