How do I use awk to match multiple keys from 1 file in another file and print value from second file-CodePudding

I need to match 2 columns from a source file with two columns from a reference file and print a third column from the reference file with all columns from the source file. Each pair in the source file (about 150,000 lines) only occurs once in the reference file (about 15,000,000 lines) and the files are large so I also need to stop searching the second file after the first instance (like grep -m 1). I have tried several attempts with awk and can get the search to work using only a single search key, but I need two keys as neither key is distinct by itself but the pairs are. The reference file is too large to load into R or python (25G as a gzipped file).

file 1 (source, multiple columns, 150K lines):
CHR SNP BP INFO(multiple other columns)
1 ABS141 132156 Random_stuff
2 GSD1151 132143 Random_stuff
3 KJH173 465879 Random_stuff

file 2 (reference, three columns, 25Gb gzipped):
CHR POS ID
1 132156 rid1
1 654987 rid2
2 132143 rid3
2 787987 rid4
3 465879 rid5

desired output file (all columns from file 1   column 3 from file 2):
CHR SNP BP INFO(columns) ID
1 ABS141 132156 Random_stuff rid1
2 GSD1151 132143 Random_stuff rid3
3 KJH173 465879 Random_stuff rid5

Approaches tried:
awk 'NR==FNR {label[$1,$2]=$3; next} (sst[$1,$3]=label[$1,$2]){print $0, label[$1,$2]}' file2 file1 > out_file
Result = empty file

awk 'NR==FNR {seen[$1,$2]=$3; next} NR{print $0, seen[$1,$3]}' file2 file1 > out_file
Result = empty file

awk 'NR==FNR {label[$1,$2]=$3; next} ($1 SUBSEP $3 in label){print $0, label[$1,$2]}' file2 file1 > out_file
Result: empty file
    
awk 'NR==FNR {label[$1,$2]=$3; next} out[$1,$3] in label {print $0, label[$1,$2]}' file2 file1 > out_file
Result: empty file

awk 'NR==FNR {seen[$2]=$3; next} NF{print $0, seen[$3]}' file2 file1 > out_file
1 ABS141 132156 Random_stuff rid1
2 GSD1151 132143 Random_stuff rid3
3 KJH173 465879 Random_stuff rid5
Result = file with ID placed correctly into file 1 as new column, but only uses 1 key (POS) instead of 2 keys (CHR   POS).

CodePudding user response：

Making a few tweaks to OP's 1st awk attempt:

awk '
NR==FNR          { if (FNR==1) $2="BP"                 # insure we can match on 2nd file header row
                   label[$1,$2]=$3
                   next
                 }
($1,$3) in label { print $0, label[$1,$3] }
' file2 file1

This generates:

CHR SNP BP INFO(multiple other columns) ID
1 ABS141 132156 Random_stuff rid1
2 GSD1151 132143 Random_stuff rid3
3 KJH173 465879 Random_stuff rid5

NOTE: this assumes OP can fit all of file2 in memory; if this is an invalid assumption (eg, OP's code is running out of memory) then maybe ...

Assuming the entire expected result (ie, file1, plus field #3 from file2, plush hash of file1 fields #1 & #3) can fit in memory, and we need to maintain the ordering of rows from file1, ...

One awk idea that requires a single pass through each input file:

awk '
FNR==NR { ndx=$1 FS $3
          if (FNR==1) ndx = "CHR" FS "POS"     # override ndx to match header from 2nd file
          lines[ndx]=$0                        # save current line in memory
          order[FNR]=ndx                       # save order of current line
          maxFNR=FNR                           # keep track of total number of lines from 1st file

          next
        }
        { ndx=$1 FS $2
          if (ndx in lines)                    # if there is a match in the lines[] array then ...
             lines[ndx]=lines[ndx] FS $3       # append current field #3 to lines[] entry
        }

END     { for (i=1;i<=maxFNR;i  )              # loop through lines from 1st file and ...
              print lines[order[i]]            # print to stdout
        }
' file1 file2

This generates:

CHR SNP BP INFO(multiple other columns) ID
1 ABS141 132156 Random_stuff rid1
2 GSD1151 132143 Random_stuff rid3
3 KJH173 465879 Random_stuff rid5