Matching two files for similar first line

Question

I have 2 set of files.

File one contains ID's ex:

1111
2222
6666
3333
4444

File two contains ID and username:

1873 Neil
1111 Roger
7632 Tim
3333 Oscar
8723 Greg
4444 Roy
6666 Patrick

I want to extract the ID and username, but only the ones that has the same ID as in file 1. I did the normal grep -f file1 file2, on two test files I made, with few Id's such as the ones i just posted. However, when i apply this to the two proper files, where file1 contains 3500 ID's and File2 contains 12000 ID's + Username, instead of extracting the 3500 lines that occur in both files, it extracts 12000 lines. However, with the 2 test files, and a few dummy ID's it will only extract the correct ids and leave the others.

Any tip on what's wrong?

Answer 1

Try doing this using join instead of grep, this will be more suitable :

$ join  <(sort file1) <(sort file2)

1111 Roger
3333 Oscar
4444 Roy
6666 Patrick

If your shell lack process substitutions <( ), you can do :

sort file1 > new_file1
sort file2 > new_file2
join new_file1 new_file2

Doc said :

join writes to standard output a line for each pair of input lines that have identical join fields.

See http://www.gnu.org/software/coreutils/manual/html_node/join-invocation.html

Notes :

The file need to be sorted for join to work properly, that's why we uses some file descriptors in the background using process substitutions See http://mywiki.wooledge.org/ProcessSubstitution , or http://mywiki.wooledge.org/BashFAQ/024 for a common use.

Answer 2

grep matches lines where a username is the same as an id. join correctly limits the match to the first field, but requires sorted input. Depending on the input awk might be a good alternative:

awk 'FNR == NR { ids[$1]++; next } ids[$1]' ids users

Or more readable:

awk 'FNR == NR { ids[$1]; next } $1 in ids' ids users

Output:

1111 Roger
3333 Oscar
4444 Roy
6666 Patrick

Explanation

The awk program is divided into two parts: one that is evaluated for the first file and one that is evaluated for the second file.

The first block is only evaluated for the first file and saves ids in the ids array. When these ids are encountered when reading users the default block is invoked ({print $0}).