4

I have 2 text files. file1 contains a list of IDs:

11002
10995
48981
79600

file2:

10993   item    0
11002   item    6
10995   item    7
79600   item    7
439481  item    5
272557  item    7
224325  item    7
84156   item    6
572546  item    7
693661  item    7
.....

I am trying to select all lines from file2 where the ID (first column) is in file1. Currently, what I am doing is to loop through the first file to create a regex like:

^\b11002\b\|^\b10995\b\|^\b48981\b|^\b79600\b

Then run:

grep '^11002\|^10995\|^48981|^79600' file2.txt

But when the number of IDs in file1 is too large (~2000), the regular expression becomes quite long and grep becomes slow. Is there another way? I am using Perl + Awk + Unix.

Improve this question
1
  • I see plenty of answers already, but you might find the perl code in here adaptable, if you want: stackoverflow.com/questions/13713032/… You need to add some stuff to filter out the first col in file2, tho. Commented Dec 5, 2012 at 21:06

7 Answers 7

Reset to default
6

Use a hash table. It can be memory-intensive but lookups are in constant time. This is an efficient and correct procedure — not the only one, but efficient and correct — for creating a hash table, using file1 as keys and file2 for looking up keys in the hash table. If a key is in the hash table, the line is printed to standard output:

#!/usr/bin/env perl

use strict;
use warnings;

open FILE1, "< file1" or die "could not open file1\n";
my $keyRef;
while (<FILE1>) {
   chomp;
   $keyRef->{$_} = 1;
}
close FILE1;

open FILE2, "< file2" or die "could not open file2\n";
while (<FILE2>) {
    chomp;
    my ($testKey, $label, $count) = split("\t", $_);
    if (defined $keyRef->{$testKey}) {
        print STDOUT "$_\n";
    }
}
close FILE2;

There are lots of ways to do the same thing in Perl. That said, I value clarity and explicitness over fancy obscurity, because you never know when you have to come back to a Perl script and make changes, and they are hard enough to manage, as it is. One person's opinion.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

TMTOWTDI: my %id; my @keys = do { open my $fh, '<', 'file1'; <$fh> }; @id{@keys} = (); … if(exists $id{$testKey}) { … }
4 
awk 'NR==FNR{tgts[$1]; next} $1 in tgts' file1 file2

Look:

$ cat file1
11002
10995
48981
79600
$ cat file2
10993   item    0
11002   item    6
10995   item    7
79600   item    7
439481  item    5
272557  item    7
224325  item    7
84156   item    6
572546  item    7
693661  item    7
$ awk 'NR==FNR{tgts[$1]; next} $1 in tgts' file1 file2
11002   item    6
10995   item    7
79600   item    7
Improve this answer

5 Comments

it only prints the last line. which is 79600 item 7
Then your file1 only contains 79600 as a key or your files are corrupt or you made a mistake copy/pasting my script or you're using old,m broken awk. What does awk --version tell you? I updated my answer to show the script working.
There is no problem with this.
I tested most of the answers here on Cygwin (Windows) and on Linux and found the results varied (only displaying 79600 item 7 on one platform) so be wary. I think grep -f f1 f2 is the most elegant solution here @EdMorton @jjennifer
@sudo_O but won't the grep find false matches if the numbers from f1 appear in undesirable locations in f2, e.g. if f1 contains 10 and f2 contains 100 in field 1, or it contains 10 in some other field.
3

I would suggest using a tool designed to do just that. Use the join command. Do 'man join' for more info.

linux_prompt> join file1 file2
11002 item 6
10995 item 7
79600 item 7
Improve this answer

2 Comments

However requires files to be sorted.
If it's OK for the OP that the results are in sorted order: join <(sort file1) <(sort file2)
2

Using grep:

$ grep -f f1 f2
11002   item    6
10995   item    7
79600   item    7

Note: I tested a lot of the suggested answer on multiple system and some only display the last match 79600 item 7!?

Improve this answer

2 Comments

This will get a false positive if the ID appears in the wrong column.
@JoshY. on cygwin grep -f f1 f2 only displays the last match, test on Linux and its fine..
1

Load all the elements of your first file into a hash. For each line of the second file, extract the number using the regex ^(\d*) if the hash contains the extracted number, print it

Improve this answer

Comments

0

Use a process substitution to transform the ID's in file1 into regular expressions:

grep -f <(sed 's/.*/^&\\b/' file1) file2

I'm assuming you're using bash or a similarly capable shell

Improve this answer

Comments

0

Simple perl solution is to use a hash and count the number of occurrences of the sought after numbers.

perl -lanwe 'print if $a{$F[0]}++ == 1;' file1.txt file2.txt

I get the following output from your sample data:

11002   item    6
10995   item    7
79600   item    7

Note that you need to use the files in the correct order on the command line.

This will open and read the input file names (-n), autosplit the lines (-a) into @F, and then print each line, if the value in the hash for that number is equal to 1. If you want to print multiple values from file2, simply change == 1 to >= 1.

Note that the ++ operator is applied after the equality comparison is done.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.