LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 12-27-2017, 06:28 AM   #1
ssathishkumar
LQ Newbie
 
Registered: Jan 2012
Posts: 9

Rep: Reputation: Disabled
Compare 2 files and merge based on field match using awk


file1:
22 epc.XYZ000.ABC454.3gppnetwork.org
1506 epc.XYZ000.ABC460.3gppnetwork.org
2 epc.XYZ001.ABC202.3gppnetwork.org
2 epc.XYZ001.ABC206.3gppnetwork.org
8 epc.XYZ001.ABC214.3gppnetwork.org
7 epc.XYZ001.ABC222.3gppnetwork.org
1 epc.XYZ001.ABC262.3gppnetwork.org
1 epc.XYZ001.ABC278.3gppnetwork.org
15 epc.XYZ001.ABC427.3gppnetwork.org
6 epc.XYZ001.ABC455.3gppnetwork.org


file 2:
India epc.XYZ000.ABC454.3gppnetwork.org
Germany epc.XYZ001.ABC206.3gppnetwork.org
Russia epc.XYZ001.ABC214.3gppnetwork.org
US epc.XYZ001.ABC222.3gppnetwork.org
China epc.XYZ000.ABC460.3gppnetwork.org
UAE epc.XYZ001.ABC278.3gppnetwork.org
UK epc.XYZ001.ABC262.3gppnetwork.org
Singapore epc.XYZ001.ABC427.3gppnetwork.org
Japan epc.XYZ001.ABC455.3gppnetwork.org
France epc.XYZ001.ABC202.3gppnetwork.org

Desired Output:

22 epc.XYZ000.ABC454.3gppnetwork.org India
1506 epc.XYZ000.ABC460.3gppnetwork.org China
2 epc.XYZ001.ABC202.3gppnetwork.org France
2 epc.XYZ001.ABC206.3gppnetwork.org Germany
8 epc.XYZ001.ABC214.3gppnetwork.org Russia
7 epc.XYZ001.ABC222.3gppnetwork.org US
1 epc.XYZ001.ABC262.3gppnetwork.org UK
1 epc.XYZ001.ABC278.3gppnetwork.org UAE
15 epc.XYZ001.ABC427.3gppnetwork.org Singapore
6 epc.XYZ001.ABC455.3gppnetwork.org Japan

When i tried with below command, its not generating any o/p.

awk 'FNR==NR{a[$1]=$2;next} ($2 in a) {print $1,$2,a[$1]}' file2 file1.

Can any one guide me to fix the error ?
 
Old 12-27-2017, 06:45 AM   #2
syg00
LQ Veteran
 
Registered: Aug 2003
Location: Australia
Distribution: Lots ...
Posts: 17,164

Rep: Reputation: 2628Reputation: 2628Reputation: 2628Reputation: 2628Reputation: 2628Reputation: 2628Reputation: 2628Reputation: 2628Reputation: 2628Reputation: 2628Reputation: 2628
Diagnostics 101 - insert some print statements; everything you use, print. Very illuminating usually.
 
Old 12-27-2017, 06:53 AM   #3
ssathishkumar
LQ Newbie
 
Registered: Jan 2012
Posts: 9

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by syg00 View Post
Diagnostics 101 - insert some print statements; everything you use, print. Very illuminating usually.
Sorry, not clear
 
Old 12-27-2017, 07:34 AM   #4
ssathishkumar
LQ Newbie
 
Registered: Jan 2012
Posts: 9

Original Poster
Rep: Reputation: Disabled
Now i made files as (,) separated and tried this one. But it doesn't merge with 2nd file.

#awk -F"," 'NR==FNR{a[$1]=$2;next}; ($2 in a); {print $0","a[$1]}' file2-new file1-new | tail -3

170,epc.XYZ260.ABC310.3gppnetwork.org,
8,epc.XYZ610.ABC302.3gppnetwork.org,
1,epc.XYZ720.ABC302.3gppnetwork.org,

Any suggestions ?

Note: File1 is bigger than file2
 
Old 12-27-2017, 08:27 AM   #5
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 11,487

Rep: Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453
where is this script from? It looks like you mixed $1 and $2:
Code:
awk 'NR==FNR{a[$2]=$1;next}; ($2 in a) {print $0","a[$2]}' file2-new file1-new
 
1 members found this post helpful.
Old 12-28-2017, 12:07 AM   #6
ssathishkumar
LQ Newbie
 
Registered: Jan 2012
Posts: 9

Original Poster
Rep: Reputation: Disabled
Smile

Quote:
Originally Posted by pan64 View Post
where is this script from? It looks like you mixed $1 and $2:
Code:
awk 'NR==FNR{a[$2]=$1;next}; ($2 in a) {print $0","a[$2]}' file2-new file1-new
Hi,

Its working perfectly. Can you please explain my mistake, also clarify your script too please ?

And one more thing.. I have many lines in file1, this script compares & merge only for the entry found in file2. It skips other lines in file1, which is not in the file2. Please help me to correct that as well.

eg: I want like below one.

22 epc.XYZ000.ABC454.3gppnetwork.org India
1506 epc.XYZ000.ABC460.3gppnetwork.org China
2 epc.XYZ001.ABC202.3gppnetwork.org France
2 epc.XYZ001.ABC206.3gppnetwork.org Germany
8 epc.XYZ001.ABC214.3gppnetwork.org Russia
7 epc.XYZ001.ABC222.3gppnetwork.org US
1 epc.XYZ001.ABC262.3gppnetwork.org UK
1 epc.XYZ001.ABC278.3gppnetwork.org UAE
15 epc.XYZ001.ABC427.3gppnetwork.org Singapore
6 epc.XYZ001.ABC455.3gppnetwork.org Japan
273 epc.XYZ005.ABC525.3gppnetwork.org N/A
24 epc.XYZ006.ABC454.3gppnetwork.org N/A
5 epc.XYZ006.ABC456.3gppnetwork.org N/A
4 epc.XYZ008.ABC204.3gppnetwork.org N/A
203 epc.XYZ008.ABC450.3gppnetwork.org N/A
3 epc.XYZ008.ABC456.3gppnetwork.org N/A
14 epc.XYZ010.ABC222.3gppnetwork.org N/A


thanks for your support.

Last edited by ssathishkumar; 12-28-2017 at 12:29 AM.
 
Old 12-28-2017, 12:42 AM   #7
ssathishkumar
LQ Newbie
 
Registered: Jan 2012
Posts: 9

Original Poster
Rep: Reputation: Disabled
I made it to work like this....

awk 'NR==FNR{a[$2]=$1;next}; {if($2 in a) print $0,a[$2]; else print $0,"N/A"}' file2 file1

But Please explain me, my earlier mistake
 
Old 12-28-2017, 01:48 AM   #8
MadeInGermany
Member
 
Registered: Dec 2011
Location: Simplicity
Posts: 934

Rep: Reputation: 403Reputation: 403Reputation: 403Reputation: 403Reputation: 403
Or, using a ternary operator ("value if"):
Code:
awk 'NR==FNR { a[$2]=$1; next } { print $0, ($2 in a) ? a[$2] : "N/A" }' file2 file1
The common key is $2 in both files.
So in file2 you index the array by $2 and store the $1 as a value.
 
1 members found this post helpful.
Old 12-28-2017, 05:29 AM   #9
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 11,487

Rep: Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453
Quote:
Originally Posted by ssathishkumar View Post
I made it to work like this....

awk 'NR==FNR{a[$2]=$1;next}; {if($2 in a) print $0,a[$2]; else print $0,"N/A"}' file2 file1

But Please explain me, my earlier mistake
You need to understand how awk works, what do $0, $1 and $2 mean. Next, you will need to understand arrays, what will a[$2]=$1 do.
That's all (almost).
 
Old 12-28-2017, 07:27 AM   #10
ssathishkumar
LQ Newbie
 
Registered: Jan 2012
Posts: 9

Original Poster
Rep: Reputation: Disabled
Question

Quote:
Originally Posted by pan64 View Post
You need to understand how awk works, what do $0, $1 and $2 mean. Next, you will need to understand arrays, what will a[$2]=$1 do.
That's all (almost).
Yep, I'm confusing with that only. Can you please elaborate how it works & differs ?
 
Old 12-28-2017, 07:40 AM   #11
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 11,487

Rep: Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453Reputation: 3453
you can find a huge amount of tutorials/examples/videos explaining awk.
https://www.tutorialspoint.com/awk/a...c_examples.htm
https://www.tutorialspoint.com/awk/awk_arrays.htm
http://spoken-tutorial.org/watch/Lin...f+awk/English/
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] How to match different records based on Same Field values using awk Abdelrahman.fathy Programming 22 11-11-2015 11:32 PM
[SOLVED] How to match different records based on multiple Field values using awk Abdelrahman.fathy Programming 4 11-04-2015 11:49 PM
[SOLVED] Comparing two files and looking for the same line based on field - awk sopier Programming 8 12-26-2011 03:53 PM
[SOLVED] merge 2 files with AWK by the field value dayamoon Linux - Newbie 8 06-03-2010 03:06 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 12:36 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration