Merging two files based on two columns to make a third file

Hi there,

I'm trying to merge two files and make a third file.

However, two of the columns need to match exactly in both files AND I want everything from both files in the output if the two columns match in that row.

First file looks like this:

chr1    10001980    T    A

Second file looks like this:

1    chr1    41980    41981    snp    A    G    dbsnp.86:rs806721

I need column 1 and 2 in file 1 to match column 2 and 4 in file 2, respectively.

Any help you can provide is very much appreciated.

I've tried using the join function and awk, but I've failed miserably at both.

Thank you.

Are you matching row1 with row1, row2 with row2, .. so on and so forth.. or can a match exist in any row?

Also are you trying to write this in a specific language or does it matter?

A match can exist in any row... the first file should ALL match in some way to an entry in the second file, but the second file has thousands of rows that will not match to the first file.

I'm working in bash on a unix device (macbook) in terminal.

Thank you !!

This may not be the most efficient way if you're files are really large but it works. Let me know if any of it is unclear.

Code:

#!/bin/bash
while read line
do
    col1=$(echo $line | awk '{print $1}')
    col2=$(echo $line | awk '{print $2}')
    row_from_file2=$(grep " $col1 " file2 | grep " $col2 ")
    file2_col2=$(echo $row_from_file2 | awk '{print $2}')
    file2_col4=$(echo $row_from_file2 | awk '{print $4}')
    if [[ "$col1" = "$file2_col2" && "$col2" = "$file2_col4" ]]
    then
        echo "Found a match"
        echo "$line $row_from_file2" >> file3
    fi
done < file1

Output:

# cat file1
chr1 10001980 T A
# cat file2
1 chr1 41980 41981 snp A G dbsnp.86:rs806721
1 chr1 41980 10001980 snp A G dbsnp.86:rs806721
# ./match_file1_file2.bash
Found a match
# cat file3
chr1 10001980 T A 1 chr1 41980 10001980 snp A G dbsnp.86:rs806721

wow, this is incredibly sophisticated compared to the basic unix that I know.

the only thing I'm confused about is where to put the file "paths"... each time it says file2 do i write the actual file path in? ie. /users/etc/

Yep. "file1", "file2" were all local to my working directory. I'm a big fan of using full paths in my scripts so I would suggest it. I just wrote everything local to be just get the logic working. Happy scripting.

nawk 'FNR==NR{f1[$1,$2]=$0;next}{idx=$2 SUBSEP $4; if(idx in f1) $0=f1[idx] OFS $0}1' file1 file2

Hi vgersh99, I tried yours but it said:

-bash: nawk: command not found

Thanks 2pugs...I keep getting errors though

try either awk or gawk (instead of nawk).

awk works, is there any difference between nawk and awk?

the output is off, it is showing this:

29    chr1    59497    59498    snp    T    C    dbsnp.100:rs2854666;
chr1    61442    A    G 3

for example... the chr 1 looks to be lining up but not the 4th and 2nd columns.

the implementation is based on your original description of the key fields:

The sample files you provided don't have matching records based on your description. So either the description is wrong or the provided sample files are not representative.
Provide either a better description OR a more representative data files (using code tags).

Also provide a desired result based on the sample data files: either WITH the matching records OR withOUT one - we can only guess what the OP's intent might be...

Sorry vgersh99 - it does work - thank you so very much.

The reason I was confused is because all the rows from column two are still in this file, but I can just awk for the rows I need.

Much appreciated.

Had you given a desired output, we could have achieved it in one 'go'.....