I would like to merge two files, which share some common data. File 1 includes more than file 2. I want to merge the files based on their shared column following the order of file 1 and I want to add 0 to column 5 (AN1) when the variable is not present in file 2.
My files look like this: File 1
CHR BP SNP CM base 20 61098 rs6078030 -0.00024510777 1 20 61795 rs4814683 0 1 20 63231 rs6076506 0.0005026053 1 20 63244 rs6139074 0.00050714752 1 File 2
CHR BP SNP CM AN1 20 9836704 rs221007 0 1 20 9817032 rs221011 0 1 20 9764069 rs2206484 0 1 20 9639395 rs4816159 0 1 I want to match them based on column 3 (SNP). I want to keep all the other columns for now.
My desired output would look like this (0 when rsX is not present):
File 3
CHR BP SNP CM base AN1 20 61098 rs6078030 -0.00024510777 1 1 20 61795 rs4814683 0 1 1 20 63231 rs6076506 0.0005026053 1 1 20 63244 rs6139074 0.00050714752 1 1 I figured that I need to do this in more than 1 step. I tried to use awk to to the first step but it only creates empty files.
awk -F' ' 'NR==FNR{e[$1$2]=1;next};e[$1$2]' file1 file 2 > file 3 awk -F' ' 'NR==FNR{e[$1$2]=1;next};e[1$2]' file2 file 1 > file 3 I guess the last step will be join file1 file 2 > file 3.
