Match data based on two fields, and append to a line


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Match data based on two fields, and append to a line
# 1  
Old 09-03-2010
Match data based on two fields, and append to a line

I need to write a program to do something like a 'vlookup' in excel. I want to match data from file2 based on two fields (where both match) in file1, and for matching lines, add the data from two of the fields from file2 to file1.

If anyone knows something in perl or awk that can do this, I'd be very happy Smilie

e.g.:
(match on field 1 and 2, and append column 4 and 5 to column 5 and 6)

file1
Code:
1 A X a
2 B Y b
3 C Z c
4 D X d
5 E Y e

file2
Code:
1 A Y b
2 C Z c
3 D X d
4 E Y e
5 E Z c

output
Code:
1 A X a Y b
2 B Y b
3 C Z c
4 D X d
5 E Y e Z c


Last edited by radoulov; 09-03-2010 at 04:16 PM.. Reason: Code tags, please!
# 2  
Old 09-03-2010
This should work

Code:
#!/bin/sh

IRS=" "
num=1
while read var1 var2 var3 var4; do
 awk -v var1=${var1} -v var2=${var2} -v var3=${var3} -v var4=${var4} -v num=${num} 'NR>=num{if($1==var1 && $2==var2){ print $0" "var3" "var4; exit} else { print $0; exit}}' file1
 num=`expr $num + 1`
done < file2

This will only look for the entry in the same position, if entry exists but at a different position, this would fail.

Last edited by vish_indian; 09-03-2010 at 02:47 PM..
This User Gave Thanks to vish_indian For This Post:
# 3  
Old 09-03-2010
Use gawk, nawk or /usr/xpg4/bin/awk on Solaris.

Code:
awk 'NR == FNR {
  _[$1, $2] = FS $3 FS $4
  next
  }
$NF = $NF _[$1, $2]
  ' file2 file1


Last edited by radoulov; 09-03-2010 at 04:33 PM..
This User Gave Thanks to radoulov For This Post:
# 4  
Old 09-06-2010
Thanks very much for your replies.

I'm using Fedora, will this run the Gawk program okay?

Also what would I need to add to the code to say, "if a line from file2 doesn't match a line from file1 on those two columns, add the whole line to the bottom of file1". How could I do that, please?

Thanks again!
# 5  
Old 09-06-2010
Hi, Try this,

Code:
awk 'NR==FNR{a[$1$2]=$3FS$4;next} { if ($1$2 in a) { print $0,a[$1$2]} else {print $0}}' file2 file1

This User Gave Thanks to pravin27 For This Post:
# 6  
Old 09-06-2010
Hi pravin27,

I tried your code with the following inputs:

file1
1 A X a
2 B Y b
3 C Z c
4 D X d
5 E Y e
1 A Y b
2 C Z c
3 D X d

file2
1 A Y b
2 C Z c
3 D X d
4 E Y e
5 E Z c
4 D X d
5 E Y e
6 A X b

Using "./test1.sh > file3"
where test1.sh =

Code:
!/bin/sh

awk 'NR==FNR{a[$1$2]=$3FS$4;next} { if ($1$2 in a) { print $0,a[$1$2]} else {print $0}}' file2 file1

However the output was:

file3
1 A X a Y b
2 B Y b
3 C Z c
4 D X d X d
5 E Y e Y e
1 A Y b Y b
2 C Z c Z c
3 D X d X d

The last line of file2 (6 A X b) was not added to the output file.

Any ideas?
Thanks
# 7  
Old 09-06-2010
You want add two columns from file 1 to file 2 OR from file 2 to file1 ?

My code code will check columns from file2 with file 1 if match then print current record of file1 and add two columns from file2 else print the current line of file1
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

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... (4 Replies)
Discussion started by: axis88
4 Replies

2. Shell Programming and Scripting

Matching two fields in two csv files, create new file and append match

I am trying to parse two csv files and make a match in one column then print the entire file to a new file and append an additional column that gives description from the match to the new file. If a match is not made, I would like to add "NA" to the end of the file Command that Ive been using... (6 Replies)
Discussion started by: dis0wned
6 Replies

3. Shell Programming and Scripting

awk to update file based on match in 3 fields

Trying to use awk to store the value of $5 in file1 in array x. That array x is then used to search $4 of file1 to find aa match (I use x to skip the header in file1). Since $4 can have multiple strings in it seperated by a , (comma), I split them and iterate througn each split looking for a match.... (2 Replies)
Discussion started by: cmccabe
2 Replies

4. Shell Programming and Scripting

Append data with substring of nth column fields using awk

Hi guys, I have problem to append new data at the end of each line of the files where it takes whole value of the nth column. My expected result i just want to take a specific value only. This new data is based on substring of 11th, 12th 13th column that has comma seperated value. My code: awk... (4 Replies)
Discussion started by: null7
4 Replies

5. Shell Programming and Scripting

To append new data at the end of each line based on substring of last column

Hi guys, I need to append new data at the end of each line of the files. This new data is based on substring (3rd fields) of last column. Input file xxx.csv: U1234|1-5X|orange|1-5X|Act|1-5X|0.1 /sac/orange 12345 0 U5678|1-7X|grape|1-7X|Act|1-7X|0.1 /sac/grape 5678 0... (5 Replies)
Discussion started by: null7
5 Replies

6. Shell Programming and Scripting

Match and Append Based on file contexts

Not Sure how to do this. Some combo of awk and sed perhaps. If String in File1 match String in file2 then append file2 File1.txt BullTerrier Boxer Bulldog File2.txt <Defined info="AllAnimals" group="Adoptions" setting="animals"> <SomeID ="NumbersRepresentingDogName"> <for> <add... (2 Replies)
Discussion started by: TY718
2 Replies

7. UNIX for Dummies Questions & Answers

How to remove fields space and append next line to previous line.?

awk 'BEGIN{FS = "Ç"} NR == 1 {p = $0; next} NF > 1 {print p; p = $0} NF <= 1 {p = (p " " $0)} END {print p}' input.txt > output.txt This is what the input data file looks like with broken lines Code: 29863 Ç890000000 Ç543209911 ÇCHNGOHG Ç000000001 Ç055 ... (4 Replies)
Discussion started by: cumeh1624
4 Replies

8. UNIX for Dummies Questions & Answers

using sed delete a line from csv file based on specific data in two separate fields

Hello, :wall: I have a 12 column csv file. I wish to delete the entire line if column 7 = hello and column 12 = goodbye. I have tried everything that I can find in all of my ref books. I know this does not work /^*,*,*,*,*,*,"hello",*,*,*,*,"goodbye"/d Any ideas? Thanks Please... (2 Replies)
Discussion started by: Chris Eagleson
2 Replies

9. UNIX for Dummies Questions & Answers

Match values from 2 files and append certain fields

Hi, I need help on appending certain field in my file1.txt based on matched patterns in file2.txt using awk or sed. The blue color need to match with one of the data in field $2 in file2.txt. If match, BEGIN and FINISHED value in red will have a new value from field $3 and $4 accordingly. ... (1 Reply)
Discussion started by: redse171
1 Replies

10. 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
Login or Register to Ask a Question