paste two files according to concordance between c

Question

I would need some help about this:

I have file1:

ID
100
102
103
104
108
109
112
.
.
.

And file2:

ID    []    p1    p2
100    2.5    3.0    2.0
101    2.0    4.0    3.0
102    2.6    4.0    2.5
103    2.3    2.0    NA
104    2.3    2.0    2.0
105    3.5    2.8    2.0
106    1.7    NA    3.2
107    5.0    4.0    4.0
108    3.2    2.0    4.0
109    2.9    1.0    1.5
110    5.0    NA    NA
111    2.9    4.0    4.0
112    3.1    2.5    2.0
.
.
.

I would like to paste both files in file3 looking like:

ID    []    p1    p2
100    2.5    3.0    2.0
102    2.6    4.0    2.5
103    2.3    2.0    NA
104    2.3    2.0    2.0
108    3.2    2.0    4.0
109    2.9    1.0    1.5
112    3.1    2.5    2.0
.
.
.

Basically, pasting data in fields 2,3,4 from file2 to file1, considering concordancies in field1 in both file1 and file2.

I've tried some awk commands with NR == NFR but I just get the output with the content of file1 followed by content of file2...

Any help? Unix commands with cut and paste are also welcome

Answer 1

You can indeed use awk for that:

awk 'NR==FNR{a[$1]=1} NR>FNR && a[$1]' file1 file2

NR==FNR{a[$1]=1} The a array filled with the content of file1.

NR>FNR && a[$1] is printing the line of file2 if the array contains the ID (aka $1).

Answer 2

If you like to use join, you can simply do:

join file file2

or , if input is tab separated and you use bash:

join -t $'\t' file1 file2

Answer 3

A more idiomatic awk solution would be :

awk 'NR==FNR{id[$1];next}$1 in id' file1 file2 >file3

Output

ID    []    p1    p2
100    2.5    3.0    2.0
102    2.6    4.0    2.5
103    2.3    2.0    NA
104    2.3    2.0    2.0
108    3.2    2.0    4.0
109    2.9    1.0    1.5
112    3.1    2.5    2.0

---

References:

1. awk [ referring to an array element ].
2. awk [ next statement ].