Merging content of two files with comparing content of column

Question

I have a problem trying to merge content of two files :

file 1 :

873 1.1.1.1 
1033 99.99.99.10
1108 78.2.204.174
1257 78.2.8.181

file 2:

test1 78.2.8.181 p6
test2 99.99.99.10 p9
test7 1.1.1.1 p10
test8 78.2.204.174 p4

I want to merge the two file into one file like this, taking in consideration that the shared columns is the second one, & it must be compared to fit the line in question:

873  1.1.1.1      test7 p10
1033 99.99.99.10  test2 p9
1108 78.2.204.174 test8 p4
1257 78.2.8.181   test1 p6

I've tried several things using awk but without success.

Answer 1

You can use join for this.

First we have to sort both files using the 2nd column as key:

$ sort -t' ' -k 2,2 a > as
$ sort -t' ' -k 2,2 b > bs

Resulting in:

$ cat as
873 1.1.1.1
1108 78.2.204.174
1257 78.2.8.181
1033 99.99.99.10
$ cat bs
test7 1.1.1.1 p10
test8 78.2.204.174 p4
test1 78.2.8.181 p6
test2 99.99.99.10 p9

Then the actual join:

$ join -t' ' -j2 as bs
1.1.1.1 873 test7 p10
78.2.204.174 1108 test8 p4
78.2.8.181 1257 test1 p6
99.99.99.10 1033 test2 p9

Answer 2

join -j2 <(sort -k2 file1) <(sort -k2 file2) -o 1.1,1.2,2.1,2.3 | column -t

outputs

873   1.1.1.1       test7  p10
1108  78.2.204.174  test8  p4
1257  78.2.8.181    test1  p6
1033  99.99.99.10   test2  p9

Answer 3

One way using awk:

$ awk 'NR==FNR{a[$2]=$1 FS $3;next}{print $0 FS a[$2] }' file2 file1
873 1.1.1.1  test7 p10
1033 99.99.99.10 test2 p9
1108 78.2.204.174 test8 p4
1257 78.2.8.181 test1 p6

The file f2 is loaded into an array where the 2nd field is the index and the value of the array is combination of 3rd and 4th fields of file2. When file1 is processed, the entire line along with the stored value in the array is printed.