how to join two files with awk.

01-26-2012

ken002

Guest

n/a, 0

Posts: n/a

how to join two files with awk.

Hi, Unix Gurus,
I need to compare two file based on key value and load result to different files.
requirement as following:
file1

Code:

1, abc
2, bcd
4, cde

file2

Code:

1, aaaaa
2, bbbbb
5, ccccc

key value is first column for both file.
I need generate following files;
records_in_1_not_2.txt

Code:

4, cde

records_in_both.txt

Code:

1, abc, aaaaa
2, bcd, bbbbb

records_in_2_not_1.txt

Code:

5, ccc

Anybody can help me this.

another thing. can anybody explain difference of following two code

Code:

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

Code:

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

if i put ";" behind the if statement, it print out all records in file2, without ";", it only print out matched records.

Thanks in advance.

Last edited by ken002; 01-26-2012 at 05:16 PM.. Reason: Add more question

ken002

01-26-2012

Registered User

3,733, 1,154

Join Date: Apr 2009

Last Activity: 3 August 2016, 11:03 AM EDT

Posts: 3,733

Thanks Given: 7

Thanked 1,154 Times in 1,124 Posts

Code:

comm -23 <(cut -d"," -f1 file1 |sort) <(cut -d"," -f1 file2|sort) | while read x; do awk -vx=$x -F, '$1==x' file1; done > records_in_1_not_2.txt

Code:

comm -12 <(cut -d"," -f1 file1 |sort) <(cut -d"," -f1 file2|sort) | while read x; do awk -vx=$x -F, '$1==x' file1; done > records_in_both.txt

Code:

comm -13 <(cut -d"," -f1 file1 |sort) <(cut -d"," -f1 file2|sort) | while read x; do awk -vx=$x -F, '$1==x' file2; done > records_in_2_not_1.txt

This User Gave Thanks to bartus11 For This Post:

bartus11

View Public Profile for bartus11

Find all posts by bartus11

01-26-2012

ken002

Guest

n/a, 0

Posts: n/a

Quote:

Originally Posted by bartus11

Code:

comm -23 <(cut -d"," -f1 file1 |sort) <(cut -d"," -f1 file2|sort) | while read x; do awk -vx=$x -F, '$1==x' file1; done > records_in_1_not_2.txt

Code:

comm -12 <(cut -d"," -f1 file1 |sort) <(cut -d"," -f1 file2|sort) | while read x; do awk -vx=$x -F, '$1==x' file1; done > records_in_both.txt

Code:

comm -13 <(cut -d"," -f1 file1 |sort) <(cut -d"," -f1 file2|sort) | while read x; do awk -vx=$x -F, '$1==x' file2; done > records_in_2_not_1.txt

Thanks for your quick reply, it works perfect.
Would you please take a look my another question? ( I just added it).

ken002

01-26-2012

Registered User

3,733, 1,154

Join Date: Apr 2009

Last Activity: 3 August 2016, 11:03 AM EDT

Posts: 3,733

Thanks Given: 7

Thanked 1,154 Times in 1,124 Posts

This:

Code:

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

is equivalent to this:

Code:

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

I hope you get my point

bartus11

View Public Profile for bartus11

Find all posts by bartus11

01-26-2012

ken002

Guest

n/a, 0

Posts: n/a

Quote:

Originally Posted by bartus11

This:

Code:

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

is equivalent to this:

Code:

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

I hope you get my point Smilie

Maybe I got it, but I still confused something.

Code:

if ($1 in a) {};

means it prints the matched records in file2 then

Code:

print $1, a[$1]}

print out all records in files 1 and file2. if this is true. the matched records in file2 should be duplicated, but the output doesn't contains any duplicates.

Thanks again.

---------- Post updated at 05:03 PM ---------- Previous update was at 04:49 PM ----------

Quote:

Originally Posted by bartus11

This:

Code:

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

is equivalent to this:

Code:

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

I hope you get my point Smilie

I got it

Code:

if ($1 in a) {}

means if match found, do nothing.

---------- Post updated at 05:03 PM ---------- Previous update was at 05:03 PM ----------

Quote:

Originally Posted by bartus11

This:

Code:

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

is equivalent to this:

Code:

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

I hope you get my point Smilie

I got it

Code:

if ($1 in a) {}

means if match found, do nothing.

ken002

01-26-2012

Registered User

87, 16

Join Date: Jul 2011

Last Activity: 29 May 2012, 10:09 PM EDT

Posts: 87

Thanks Given: 0

Thanked 16 Times in 15 Posts

records_in_1_not_2.txt

Code:

join -t"," -1 1 -2 1 -v 1 <(sort -t "," file1) <(sort -t"," file2)

records_in_2_not_1.txt

Code:

join -t"," -1 1 -2 1 -v 2 <(sort -t "," file1) <(sort -t"," file2)

records_in_1_and_2.txt

Code:

join -t"," -1 1 -2 1  <(sort -t "," file1) <(sort -t"," file2)

This User Gave Thanks to tarun_agrawal For This Post:

tarun_agrawal

View Public Profile for tarun_agrawal

Find all posts by tarun_agrawal

01-26-2012

ken002

Guest

n/a, 0

Posts: n/a

Quote:

Originally Posted by tarun_agrawal

records_in_1_not_2.txt

Code:

join -t"," -1 1 -2 1 -v 1 <(sort -t "," file1) <(sort -t"," file2)

records_in_2_not_1.txt

Code:

join -t"," -1 1 -2 1 -v 2 <(sort -t "," file1) <(sort -t"," file2)

records_in_1_and_2.txt

Code:

join -t"," -1 1 -2 1  <(sort -t "," file1) <(sort -t"," file2)

Thanks

ken002

Shell Programming and Scripting

how to join two files with awk.

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Discussion started by: yjacknewton

2. Shell Programming and Scripting

Join two files using awk

Discussion started by: mystition

3. Shell Programming and Scripting

awk join 2 files

Discussion started by: vikus

4. Shell Programming and Scripting

awk program to join 2 fields of different files

Discussion started by: abhisheksunkari

5. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Discussion started by: evelibertine

6. Shell Programming and Scripting

Awk - join multiple files

Discussion started by: quincyjones

7. UNIX for Dummies Questions & Answers

how to join two files using "Join" command with one common field in this problem?

Discussion started by: mindfreak

8. Shell Programming and Scripting

Join multiple files by column with awk

Discussion started by: macsx82

9. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Discussion started by: InfoSeeker

10. Shell Programming and Scripting

Left join on files using awk

Discussion started by: pinnacle