match two key columns in two files and print output (awk)


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting match two key columns in two files and print output (awk)
# 1  
Old 09-25-2012
Data match two key columns in two files and print output (awk)

I have two files... file1 and file2.
Where columns 1 and 2 of file1 match columns 1 and 2 of file2 I want to create a new file that is all file1 + columns 3 and 4 of file2

Smilie Many thanks if you know how to do this.... Smilie

file1
31-101 106 0 92
31-101 106 29 92
31-101 106 85 12
31-101 106 66 11
31-101 106 71 33
31-101 146 0 14
31-101 146 45 14
31-101 146 94 14
31-101 146 86 15
31-101 146 93 15
31-101 146 72 15
31-101 186 10 14
31-101 186 58 14
31-101 186 48 14
31-101 186 39 15
31-101 186 54 15
31-101 186 68 15
31-102 106 0 144
31-102 106 128 194
31-102 106 167 198
31-102 106 244 106
31-102 106 240 132
31-102 106 255 139
31-102 106 294 262

file2
31-101 106 396333 9256246
31-101 146 396764 9256784
31-101 186 396676 9256357
31-102 106 397346 9256457

Desired output
All of file one columns 3 and 4 of file two
31-101 106 0 92 396333 9256246
31-101 106 29 92 396333 9256246
31-101 106 85 12 396333 9256246
31-101 106 66 11 396333 9256246
31-101 106 71 33 396333 9256246
31-101 146 0 14 396764 9256784
31-101 146 45 14 396764 9256784
31-101 146 94 14 396764 9256784
31-101 146 86 15 396764 9256784
31-101 146 93 15 396764 9256784
31-101 146 72 15 396764 9256784
31-101 186 10 14 396676 9256357
31-101 186 58 14 396676 9256357
31-101 186 48 14 396676 9256357
31-101 186 39 15 396676 9256357
31-101 186 54 15 396676 9256357
31-101 186 68 15 396676 9256357
31-102 106 0 144 397346 9256457
31-102 106 128 194 397346 9256457
31-102 106 167 198 397346 9256457
31-102 106 244 106 397346 9256457
31-102 106 240 132 397346 9256457
31-102 106 255 139 397346 9256457
31-102 106 294 262 397346 9256457
# 2  
Old 09-25-2012
Code:
awk 'FNR==NR{a[$1,$2]=$3 FS $4;next}{print $0,a[$1,$2]}' file2 file1

This User Gave Thanks to elixir_sinari For This Post:
# 3  
Old 09-25-2012
I spent all morning on that and you resolved it within 3 minutes... thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Match Fields between two files, print portions of each file together when matched in ([g]awk)'

I've written an awk script to compare two fields in two different files and then print portions of each file on the same line when matched. It works reasonably well, but every now and again, I notice some errors and cannot seem to figure out what the issue may be and am turning to you for help. ... (2 Replies)
Discussion started by: jvoot
2 Replies

2. UNIX for Beginners Questions & Answers

Match Strings between two files, print portions of each file together when matched ([g]awk)

I have two files and desire to use the strings from $1 of file 1 (file1.txt) as search criteria to find matches in $2 of file 2 (file2.txt). If matches are found I want to output the entire line of file 2 (file2.txt) followed by fields $2-$11 of file 1 (file1.txt). I can find the matches, I cannot... (7 Replies)
Discussion started by: jvoot
7 Replies

3. Shell Programming and Scripting

awk to print fields that match using conditions and a default value for non-matching in two files

Trying to use awk to match the contents of each line in file1 with $5 in file2. Both files are tab-delimited and there may be a space or special character in the name being matched in file2, for example in file1 the name is BRCA1 but in file2 the name is BRCA 1 or in file1 name is BCR but in file2... (6 Replies)
Discussion started by: cmccabe
6 Replies

4. UNIX for Dummies Questions & Answers

Match the columns between two files and output

Hi Help, I have two files namely a.txt and b.txt a.txt looks like a.txt 1 2 2 1 3 3 2 4 4 4 5 6 6 7 7 b.txt looks like, b.txt 1 2 1 1 3 2 2 4 3 3 4 4 4 5 5 (2 Replies)
Discussion started by: Indra2011
2 Replies

5. 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

6. Shell Programming and Scripting

Compare columns of multiple files and print those unique string from File1 in an output file.

Hi, I have multiple files that each contain one column of strings: File1: 123abc 456def 789ghi File2: 123abc 456def 891jkl File3: 234mno 123abc 456def In total I have 25 of these type of file. (5 Replies)
Discussion started by: owwow14
5 Replies

7. Shell Programming and Scripting

awk : Want to print columns of different input files in line wise

Hi, I want to print last column of 3 input files with awk. cat file1.txt file2.txt file3.txt file1.txt 1 ad 200 2 ss 300 file2.txt 1 mm 444 2 ee 555 file3.txt 1 kk 999 2 jj 555 My o/p should be :- 1 200 444 999 2 300 555 555 (3 Replies)
Discussion started by: satishmallidi
3 Replies

8. Shell Programming and Scripting

Match two columns from two files and print output

Hello, I have two files which are of the following format File 1 which has two columns Protein_ID Substitution NP_997239 T53R NP_060668 V267M NP_058515 P856A NP_001206 T55M NP_006601 D371Y ... (2 Replies)
Discussion started by: nans
2 Replies

9. 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

10. Shell Programming and Scripting

compare columns from seven files and print the output

Hi guys, I need some help to come out with a solution . I have seven such files but I am showing only three for convenience. filea a5 20 a8 16 fileb a3 42 a7 14 filec a5 23 a3 07 The output file shoud contain the data in table form showing first field of... (7 Replies)
Discussion started by: smriti_shridhar
7 Replies
Login or Register to Ask a Question