How to merge two files on the first 3 columns?

Question

I have two files that look like this:

$ head ret
chr   pos     gene_id      pval_ret
chr1 10177 ENSG00000227232 0.381708
chr1 10352 ENSG00000227232 0.959523
chr1 11008 ENSG00000227232 0.218132
chr1 11012 ENSG00000227232 0.218132
chr1 13110 ENSG00000227232 0.998262
chr1 13116 ENSG00000227232 0.438572
chr1 13118 ENSG00000227232 0.438572
chr1 13273 ENSG00000227232 0.694523
chr1 14464 ENSG00000227232 0.825113

$ head lcls
chr  pos     gene_id       pval_nominal
chr1 13550 ENSG00000227232 0.375614
chr1 14671 ENSG00000227232 0.474708
chr1 14677 ENSG00000227232 0.699887
chr1 16841 ENSG00000227232 0.127895
chr1 16856 ENSG00000227232 0.627822
chr1 17005 ENSG00000227232 0.802803
chr1 17147 ENSG00000227232 0.443987
chr1 17407 ENSG00000227232 0.397521
chr1 17408 ENSG00000227232 0.615126

How can I merge these two files, ret and lcls, on the first 3 columns: chr, pos, gene_id?

I know how to merge them on the first two columns:

awk 'NR==FNR{a[$1,$2]=$3;next} ($1,$2) in a{print $0, a[$1,$2]}' ret lcls > overlap

But how can I do it on the first 3 columns? In the output I want these 4 columns: chr, pos,gene_id, pval_ret, pval_nominal.

Answer 1

You can use the same approach as for two fields, just add the third:

awk 'NR==FNR{a[$1,$2,$3]=$4;next} ($1,$2,$3) in a{print $0, a[$1,$2,$3]}' ret lcls

Answer 2

With the 'paste' command you can merge two files. Example :

paste ret lcls | awk '{print $1" "$2" "$3" "$6" "$4" "$8}'

Do you want 4 or 5 columns ? A sample of the result you want could help understand your needs.