0

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.

2 Answers 2

0

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
2
  • unfortunately, this did not work. It listed all the values for chr from file2 in terminal. I maybe have to use something else for this merge. Thank you for your help
    – Cprzc
    Commented Feb 2, 2016 at 19:02
  • not sure if it has something to do with the awk version, because it worked for me.
    – jai_s
    Commented Feb 3, 2016 at 3:15
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .