Data match 2 files based on first 2 columns matching only and join if match

UNIX for Beginners Q & A
1

Hi,

i have 2 files , the data i need to match is in masterfile and i need to pull out column 3 from master if column 1 and 2 match and output entire row to new file

I have tried with join and awk and i keep getting blank outputs or same file

is there an easier way than what i am trying to do, with grep or seb

here is what i have so far

awk 'FNR == NR { h[$1,$2] = 1; next } h[$1,$2]' file1 file2 > output

awk 'NR==FNR {a[substr($1,1,5)]=$2; next} 
               {print $1,a[$1],$2,$3,a[$3],$4}' file2 file1

awk 'BEGIN{while((getline<"file1")>0)l[$1","$2]=1}!l[$1","$2]' file2

any help would be appreciated, also i will contribute to this forum with anything i can help with

thanks

2

An example input file and desired output would help avoid some misinterpretation.

I believe this fills your requirement:

awk 'FNR == NR { h[$1,$2] = $3; next } $1 SUBSEP $2 in h {print h[$1,$2]}' file1 file2

Or, if you know column 3 isn't blank:

awk 'FNR == NR { h[$1,$2] = $3; next } $0 = h[$1,$2]' file1 file2
3

Hi,

ok thanks for the reply,

here is an example of the match file and the master file

so what needs to happen is if the first 2 columns of the file match then the script would pull out the entire row with the mobile number added to the match file

so basically i need the mobiles added to field 30 on the match file as all the mobiles would be in the master file

hope this makes sense and again thanks for this

match file

surname	dob	f3	f4	f5	f6	f7	f8	f9	f10	f11	f12	f13	f14	f15	f16	f17	f18	f19	f20	f21	f22	f23	f24	f25	f26	f27	f28	f29
jones	        22/03/1988	  xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx
smith	19/03/1977	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx
john	22/07/1955	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx

so first 2 colums of both files are surname and DOB

master file with the mobile in the 3rd

f1	f2	mobile
john	22/02/1988	7777777777
jones	19/05/1975	7888888888
etc	22/07/1990	7878787878
etc	01/01/1959	7878787878

hope this makes more sense

thanks

4

Unfortunately, none of your $1,$2 pairs match between files 1 and 2, not even in the header. So, the result of the proposal applied to your samples is empty.

awk '
FNR == NR       {h[$1,$2] = $3
                 next
                }
                {$(NF+1) = h[$1,$2]
                }
1
' OFS="\t" file2 file1
5

Yes sorry forgot to mention, there will be matches in the master to the match file

so there will be (surname) (dob) in match file that are in the master, then if match would output > filewithallfieldsandmobile

thanks

--- Post updated at 04:30 PM ---

also the Headers of column 1 2 will be same

surname dob