Matching two fields between two files with awk

bop-a-nator · 04-29-2013, 05:11 PM

Hi,

I have two files I am trying to find the items on File2 based on Field3 and Field4 matching the content of File1 Field1 and Field2. I may have other fields to check, but I figured if I understood how to do two, I could go from there. I’m not really sure how to set this up. I do not have Perl, and yes, I am looking at awk specifically, I realize grep may be an option too, though my question is about awk. Appreciate the help, bop-a-nator

File1:

Fruit,Kiwi
Fruit,Pear
Veggie,carrot
Dairy,Milk

File2:

Item,A,Veggie,tomato,20120903,Bin,42
Item,B,Fruit,Kiwi,20120901,Bin,2
Item,B,Fruit,Grapes,20120903,Bin,12
Item,A,Dairy,Milk,20120921,Fridge,3
Item,B,Fruit,Pear,20120903,Bin,14
Item,C,Veggie,carrot,20120903,Bin,45
Item,B,Veggie,celery,20120903,Bin,32

Output I am trying to get:

Item,B,Fruit,Kiwi,20120901
Item,A,Dairy,Milk,20120921
Item,B,Fruit,Pear,20120903
Item,C,Veggie,carrot,20120903

What I ran:

/bin/gawk 'BEGIN{OFS=FS=","}NR==FNR{file1[$3,$4]=$0;next} $1,$2 in file1 {print $1,$2,$3,$4,$5}' File1 File2 > Fileout

What came out on Fileout:

Item,A,Veggie,tomato,20120903
Item,B,Fruit,Kiwi,20120901
Item,B,Fruit,Grapes,20120903
Item,A,Dairy,Milk,20120921
Item,B,Fruit,Pear,20120903
Item,C,Veggie,carrot,20120903
Item,B,Veggie,celery,20120903

danielbmartin · 04-29-2013, 08:36 PM

Quote:

Originally Posted by bop-a-nator

... I realize grep may be an option ...

grep is a good choice for this matching problem. Try this ...

Code:

grep -f $InFile1 $InFile2 |cut -d, -f1-5 >$OutFile

Daniel B. Martin

shivaa · 04-30-2013, 02:31 AM

Just try this:

Code:

#!/bin/bash
while read -r line; do
grep $line File2 | cut -d"," -f1-5    ## Either use grep plus cut
# awk -v t='$line' 'BEGIN{FS=","}; /t/ {print $1,$2,$3,$4,$5}' File2 # Or usr awk
done < File1

grail · 04-30-2013, 03:26 AM

Well you were close with awk:

Code:

awk 'BEGIN{OFS=FS=","}NR==FNR{a[$1][$2];next}$4 in a[$3]{print $1,$2,$3,$4,$5}' file1 file2

This should work for gawk v4+

bop-a-nator · 04-30-2013, 10:59 AM

Hi grail,

I tried the bit above and I seem to get a syntax error with the "[" I don't see any mis-matches and I think I had the quotes all in the correct locations.

prompt> /bin/gawk 'BEGIN{OFS=FS=","}NR==FNR{a[$1][$2];next}$4 in a[$3]{print $1,$2,$3,$4,$5}' File1 File2
gawk: BEGIN{OFS=FS=","}NR==FNR{a[$1][$2];next}$4 in a[$3]{print $1,$2,$3,$4,$5}
gawk: ^ syntax error
gawk: BEGIN{OFS=FS=","}NR==FNR{a[$1][$2];next}$4 in a[$3]{print $1,$2,$3,$4,$5}
gawk: ^ syntax error

Thanks,
bop-a-nator

grail · 04-30-2013, 11:51 AM

Which version of gawk do you have? Assuming older which does not support multi-dimension arrays, the following change should work:

Code:

awk 'BEGIN{OFS=FS=","}NR==FNR{a[$1$2];next}$3$4 in a{print $1,$2,$3,$4,$5}' file1 file2