Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

Print matching fields (if they exist) from two text files

Shell Programming and Scripting


Tags
awk, solved

Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 12-27-2016
gacanepa gacanepa is offline
Registered User
 
Join Date: Jun 2013
Last Activity: 10 January 2017, 8:33 PM EST
Location: Villa Mercedes, San Luis, Argentina
Posts: 176
Thanks: 24
Thanked 27 Times in 23 Posts
Tools Print matching fields (if they exist) from two text files

Hi everyone,
Given two files (test1 and test2) with the following contents:
test1:

Code:
80263760,I71
80267369,M44
80274628,L77
80276793,I32
80277390,K05
80277391,I06
80279206,I43
80279859,K37
80279866,K35
80279867,J16
80280346,I14

and test2:

Code:
80263760,PT18
80279867,PT01

I need to do some kind of join to obtain this result:

Code:
80263760,PT18
80267369,M44
80274628,L77
80276793,I32
80277390,K05
80277391,I06
80279206,I43
80279859,K37
80279866,K35
80279867,PT01
80280346,I14

In short:
  1. Join on 1st field of both files.
  2. If a value in field #1 exists in test2, replace field #2 in test1 with field #2 in test2.
  3. Else, leave field #2 in test1 intact.
I tried with the following join command:

Code:
join -t, -1 1 -2 1 -o 1.1 2.2 -a 1 -a 2 test1 test2

but it only complies with requirement 2 above and leaves non-matching fields empty:

Code:
80263760,PT18
80267369,
80274628,
80276793,
80277390,
80277391,
80279206,
80279859,
80279866,
80279867,PT01
80280346,

Any ideas will be more than welcome.
Thanks in advance.
The Following User Says Thank You to gacanepa For This Useful Post:
rbatte1 (12-28-2016)
Sponsored Links
    #2  
Old Unix and Linux 12-27-2016
Scrutinizer's Unix or Linux Image
Scrutinizer Scrutinizer is offline Forum Staff  
Moderator
 
Join Date: Nov 2008
Last Activity: 27 July 2017, 5:17 PM EDT
Location: Amsterdam
Posts: 11,495
Thanks: 494
Thanked 3,323 Times in 2,931 Posts
A standard way to solve this (you will find many examples on these forums) is this awk construct, so give that a try:

Code:
awk 'NR==FNR{A[$1]=$2; next} $1 in A{$2=A[$1]}1' FS=, OFS=, test2 test1

The Following User Says Thank You to Scrutinizer For This Useful Post:
gacanepa (01-10-2017)
Sponsored Links
    #3  
Old Unix and Linux 12-27-2016
bakunin bakunin is offline Forum Staff  
Bughunter Extraordinaire
 
Join Date: May 2005
Last Activity: 28 July 2017, 9:13 AM EDT
Location: In the leftmost byte of /dev/kmem
Posts: 5,502
Thanks: 104
Thanked 1,535 Times in 1,139 Posts
Quote:
Originally Posted by gacanepa View Post
I tried with the following join command:

Code:
join -t, -1 1 -2 1 -o 1.1 2.2 -a 1 -a 2 test1 test2

but it only complies with requirement 2 above and leaves non-matching fields empty:
Yes: works as designed. ;-)

The join-command follows your orders to include non-matched lines (the "-a") and therefore you see these lines in the output. Depending on what exactly you want leave out "-a 1" or "-a 2" or both. Add a "1.2" to your output rules as fallback.

I hope this helps.

bakunin
The Following 2 Users Say Thank You to bakunin For This Useful Post:
gacanepa (01-10-2017), rbatte1 (12-28-2016)
    #4  
Old Unix and Linux 01-10-2017
gacanepa gacanepa is offline
Registered User
 
Join Date: Jun 2013
Last Activity: 10 January 2017, 8:33 PM EST
Location: Villa Mercedes, San Luis, Argentina
Posts: 176
Thanks: 24
Thanked 27 Times in 23 Posts
Thank you guys for your answers! I was able to solve my problem using your suggestions.
Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
How to merge two or more fields from two different files where there is non matching column? karthikram Shell Programming and Scripting 3 09-06-2013 11:08 AM
awk print all fields except matching regex glev2005 UNIX for Advanced & Expert Users 11 06-17-2013 03:22 PM
Matching multiple fields from two files and then some? mbp Shell Programming and Scripting 4 06-19-2012 10:27 PM
comparing two files for matching fields gameli Shell Programming and Scripting 3 02-16-2012 09:14 PM
AWK Matching Fields and Combining Files Michelangelo Shell Programming and Scripting 5 03-30-2009 09:28 PM



All times are GMT -4. The time now is 02:45 PM.