How can I add a column to a file that is matched to another column?

Question

How can I match the first column from file1 according to the numbers in the second column to the file 2?

File file1

k002  25
k004  54
k003  23

File file2

25 h
23 j
54 hg

Desired output

k002 25 h
k003 23 j
k004 54 hg

I have no idea how to do that, and I did not find similar questions.

awk 'matching {print ... $1, $2}' file1 file2 > file_des

Answer 1

You could perhaps do something like this:

awk 'NR == FNR { x[$2]=$1; next} { print x[$1], $0 }' file1 file2

Where:

• FNR: The input record number in the current input file.
• NR : The total number of input records seen so far.

Note that this will read entire file1 into memory.

Answer 2

If you don't care about preserving original file order you can use join:

# sort the first file:
sort -k2 file1 > nfile1

# Sort the second file:
sort file2 > nfile2

join -1 2 -2 1 -o 1.1,1.2,2.2 nfile1 nfile2 > joined_file

Or as suggested by @Ed Morton, if you use bash or any other shell that support process sobstitution, you can avoid creating temporary files in this way:

join -1 2 -2 1 -o 1.1,1.2,2.2 <(sort -k2 file1) <(sort file2)

Answer 3

Using awk:

awk 'NR==FNR{ar[$2]=$0;next}($1 in ar){print ar[$1],$2}' file1 file2

In this command NR==FNR ensures that {ar[$2]=$0;next} will work for first file only because after reading first file FNR would be set to zero. In expression {ar[$2]=$0;next} an array ar indexed on $2 is created and with next no further actions are taken.

The expression ($1 in ar) sees if $1 (first field of second file) is present in indices of array ar. If present, then this prints ar and second field of second file by this statement {print ar[$1],$2}.

($1 in ar) is important because this means whether $1 of second file matches indices of ar. And ar is indexed on $2 of first file. So this checks if $1 of second file matches $2 of first file.