ubuntu - joining two files based on first column IDs

Question

I have two files, with the pattern

file1 (smaller file)

001 word1
002 word2
... ....
00n wordn

file2 (bigger file)

001 word3
002 word4
... ....
00n wordn

I want to get an output file that keeps only matching lines from both files, based on the first column of file1 and joins the two lines where it finds a common column ID, such as, for example

001 001 word1 word 3

Tried various comobos of join, grep and awk but the task seems to be beyond me.

Many thanks!

question from:https://stackoverflow.com/questions/65829613/joining-two-files-based-on-first-column-ids

Answer 1

Given:

$ cat file1
001 word1
002 word2
00n wordn1

$ cat file2 
001 word3
002 word4
003 word_u1
004 word_u2
00n wordn2

(Note the extra 003 word_u1 and 004 word_u2 in file2...)

You can use join that joins those files (as presented) together:

$ join file1 file2
001 word1 word3
002 word2 word4
00n wordn1 wordn2

If the files are not sorted (as you have presented them) you can sort first:

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

If you want to double up the digits, pipe to sed:

$ join file1 file2 | sed -nE 's/^([^[:space:]]*)/1 1/p'
001 001 word1 word3
002 002 word2 word4
00n 00n wordn1 wordn2

Or specify the join output list:

$ join -o 1.1,2.1,1.2,2.2 file1 file2
001 001 word1 word3
002 002 word2 word4
00n 00n wordn1 wordn2