How to paste over column by separate matching column

Question

I have these two files:

    file1.txt  
    0 rs3094315 0 0 C T  
    0 rs12562034 0 0 A G  
    0 rs3934834 0 0 C T  

    file2.txt  
    Chr,Pos,snp_name  
    1, 742429,rs3094315   

Where the first column of file1 is also a Chr column, and has to be replaced by file2 chr column. However, it has to match snp to that it looks like this:

    file3  
    1 rs3094315 0 0 C T  

File2 has different row and column numbers.

Answer 1

already answered partially here -- https://stackoverflow.com/questions/24516141/awk-processing-2-files-with-different-field-separators

awk 'NR==FNR {arr[$2]=$2FS$3FS$4FS$5FS$6; next} {print $1,arr[$3]}' file1 FS="," file2 
1 rs3094315 0 0 C T

Answer 2

Using join and a shell that understands process substitutions:

$ join -t ' ' -1 2 -2 3 -o2.1,0,1.3,1.4,1.5,1.6 <( sort -k2 file1 ) <( tr ',' ' ' <file2 | sort -k3 )
1 rs3094315 0 0 C T

The second file, file2, has a problem in that it uses a different field delimiter from the first file, file1. This is remedied by running its content through tr to replace all commas with spaces.

The two files are then sorted on the fields that we're going to join the data on; field 2 in the first file, and field 3 in the second file.

The join will then read these two datasets and join them on the specified spaces-separated fields.

The output fields are determined by the -o flag, and we pick the first field of the second file, the join field, followed by fields 3 through to 6 from the first file.