Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Data match 2 files based on first 2 columns matching only and join if match
# 1  
Old 11-21-2019
Data match 2 files based on first 2 columns matching only and join if match
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
Code:
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
Last edited by RavinderSingh13; 11-22-2019 at 01:20 AM..
# 2  
Old 11-21-2019
An example input file and desired output would help avoid some misinterpretation.

I believe this fills your requirement:

Code:
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:

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

# 3  
Old 11-22-2019
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

Code:
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

Code:
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  
Old 11-22-2019
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.
Code:
awk '
FNR == NR       {h[$1,$2] = $3
                 next
                }
                {$(NF+1) = h[$1,$2]
                }
1
' OFS="\t" file2 file1

# 5  
Old 11-22-2019
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Comparing two columns in two files and printing a third based on a match

Hello all, First post here. I did not notice a previous post to help me down the right path. I am looking to compare a column in a CSV file against another file (which is not a column match one for one) but more or less when a match is made, I would like to append a third column that contains a... (17 Replies)
Discussion started by: dis0wned
17 Replies

2. Shell Programming and Scripting

awk to update file based on partial match in field1 and exact match in field2

I am trying to create a cronjob that will run on startup that will look at a list.txt file to see if there is a later version of a database using database.txt as the source. The matching lines are written to output. $1 in database.txt will be in list.txt as a partial match. $2 of database.txt... (2 Replies)
Discussion started by: cmccabe
2 Replies

3. Shell Programming and Scripting

New files based off match or no match

Trying to match $2 in original_targets with $2 of new_targets . If the two numbers match exactly then a match.txt file is outputted using the information in the new_targets in the beginning 4 fields $1, $2, $3, $4 and value of $4 in the original_targets . If there is "No Match" then a no... (2 Replies)
Discussion started by: cmccabe
2 Replies

4. Shell Programming and Scripting

Join two files combining multiple columns and produce mix and match output

I would like to join two files when two columns in each file matches with each other and then produce an output when taking multiple columns. Like I have file A 1234,ABCD,23,JOHN,NJ,USA 2345,ABCD,24,SAM,NY,USA 5678,GHIJ,24,TOM,NY,USA 5678,WXYZ,27,MAT,NJ,USA and file B ... (2 Replies)
Discussion started by: mady135
2 Replies

5. UNIX for Advanced & Expert Users

Match and print based on columns

HI, I have 2 different questions in this thread. Consider 2 files as input (input file have different line count ) File 1 1 1 625 56 1 12 657 34 1 9 25 45 1 2 20 54 67 3 25 35 27 4 45 73 36 5 125 56 45 File2 1 1 878 76 1 9 83 67 2 20 73 78 4 47 22 17 3 25 67 99 (4 Replies)
Discussion started by: rossi
4 Replies

6. Shell Programming and Scripting

Join lines from two files based on match

I have two files. File1 >gi|11320906|gb|AF197889.1|_Buchnera_aphidicola ATGAAATTTAAGATAAAAAATAGTATTTT >gi|11320898|gb|AF197885.1|_Buchnera_aphidicola ATGAAATTTAATATAAACAATAAAA >gi|11320894|gb|AF197883.1|_Buchnera_aphidicola ATGAAATTTAATATAAACAATAAAATTTTT File2 AF197885 Uroleucon aeneum... (2 Replies)
Discussion started by: pathunkathunk
2 Replies

7. Shell Programming and Scripting

Match files based on either of the two columns awk

Dear Shell experts, I have 2 files with structure: File 1: ID and count head test_GI_count1.txt 1000094 2 10039307 1 10039641 1 10047177 11 10047359 1 1008555 2 10120302 1 10120672 13 10121776 1 10121865 32 And 2nd file: head Protein_gi_GeneID_symbol.txt protein_gi GeneID... (11 Replies)
Discussion started by: smitra
11 Replies

8. Shell Programming and Scripting

Matching string on two files based on match rules.

Hi, How to check if a string on file2 exactly matches with a part or complete string on file1, and return a match indicator based on some match rules. 1) only records on file1 with category A should be matched. for other category, the output match indicator should default to 'N' 2) on file2... (13 Replies)
Discussion started by: effay
13 Replies

9. Shell Programming and Scripting

Extract data based on match against one column data from a long list data

My input file: data_5 Ali 422 2.00E-45 102/253 140/253 24 data_3 Abu 202 60.00E-45 12/23 140/23 28 data_1 Ahmad 256 7.00E-45 120/235 140/235 22 data_4 Aman 365 8.00E-45 15/65 140/65 20 data_10 Jones 869 9.00E-45 65/253 140/253 18... (12 Replies)
Discussion started by: patrick87
12 Replies

10. Shell Programming and Scripting

Matching same columns and finding the smallest match

Hi all, I am wondering if its possible to solve my problem with a simple code. Basically I have a file that looks like this (tab delimited) bob 8 250 tina 8 225 sam 8 225 ellen 9 315 kyle 9 275 sally 9 135 So what I want to do is match columns 2 and 5. If columns 2 and 5... (2 Replies)
Discussion started by: phil_heath
2 Replies
Login or Register to Ask a Question