How to compare two columns in two files?

Login or Register to Ask a Question and Join Our Community

How to compare two columns in two files?

Tags

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to compare two columns in two files?

07-29-2009

Registered User

12, 0

Join Date: Jul 2009

Last Activity: 4 November 2009, 12:05 PM EST

Posts: 12

Thanks Given: 0

Thanked 0 Times in 0 Posts

How to compare two columns in two files?

Hello all,

Could someone please let me know shell script or awk solution to compare two columns in two files? Here is the sample -

file1.txt

Code:

abc/xyz,M1234
ddd/lyg,M2345
cnn/tnt,G0123

file2.txt

Code:

A,abc/xyz,kk,dd,zz,DCT,G0123,1
A,ddd/lyg,kk,dd,zz,DCT,M1234,1
A,abc/xyz,kk,dd,zz,DCT,M1234,1
A,cnn/tnt,kk,dd,zz,DCT,G0123,1
A,ddd/lyg,kk,dd,zz,DCT,G0123,1

In above files, I would like to compare files 1 and 2 in file1.txt with fields 2 and 7 in file2.txt. Only if both fields matches, I would like to print/save file2.txt line into new file. So, in above comparison I would like to print out following -

file3.txt

Code:

A,abc/xyz,kk,dd,zz,DCT,M1234,1
A,cnn/tnt,kk,dd,zz,DCT,G0123,1

Could someone please help me with script for this?

Thank you for your help! :-)

sncoupons

View Public Profile for sncoupons

Find all posts by sncoupons

07-29-2009

Registered User

2,050, 105

Join Date: Jun 2008

Last Activity: 9 June 2020, 12:25 AM EDT

Location: INDIA, Bangalore

Posts: 2,050

Thanks Given: 16

Thanked 105 Times in 102 Posts

awk will do...

Code:

awk -F"," 'FILENAME=="file1"{A[$1$2]=$1$2}
FILENAME=="file2"{if(A[$1$2]==$2$7){print}}' file1 file2

vidyadhar85

View Public Profile for vidyadhar85

Find all posts by vidyadhar85

07-29-2009

Registered User

12, 0

Join Date: Jul 2009

Last Activity: 4 November 2009, 12:05 PM EST

Posts: 12

Thanks Given: 0

Thanked 0 Times in 0 Posts

this doesn't generate any output

---------- Post updated at 06:17 PM ---------- Previous update was at 06:10 PM ----------

Please help!!

sncoupons

View Public Profile for sncoupons

Find all posts by sncoupons

07-29-2009

Registered User

2,050, 105

Join Date: Jun 2008

Last Activity: 9 June 2020, 12:25 AM EDT

Location: INDIA, Bangalore

Posts: 2,050

Thanks Given: 16

Thanked 105 Times in 102 Posts

sorry I made a small typo error

this will work...

Code:

awk -F"\," 'FILENAME=="file1"{A[$1$2]=$1$2}
FILENAME=="file2"{if(A[$2$7]){print}}' file1 file2

vidyadhar85

View Public Profile for vidyadhar85

Find all posts by vidyadhar85

07-30-2009

Registered User

1,305, 26

Join Date: Jun 2007

Last Activity: 11 November 2016, 3:44 AM EST

Location: Beijing China

Posts: 1,305

Thanks Given: 0

Thanked 26 Times in 26 Posts

Code:

nawk -F"," 'NR==FNR{_[$1]=$2} NR!=FNR{if($7==_[$2]) print;}' file1 file2

summer_cherry

View Public Profile for summer_cherry

Find all posts by summer_cherry

07-30-2009

Registered User

12, 0

Join Date: Jul 2009

Last Activity: 4 November 2009, 12:05 PM EST

Posts: 12

Thanks Given: 0

Thanked 0 Times in 0 Posts

Thank you folks for your help.

vidyadhar85 - Your solution worked perfectly. I went ahead with your solution.

summer_cherry - Thank you for your help too. Somehow the file would not print the first match in test I ran (it would print other matches).

Thanks

sncoupons

View Public Profile for sncoupons

Find all posts by sncoupons

09-28-2009

Registered User

22, 0

Join Date: Jul 2009

Last Activity: 3 January 2011, 5:12 PM EST

Posts: 22

Thanks Given: 2

Thanked 0 Times in 0 Posts

vidyadhar85, can you please extend your code to print the entire lines containing the matching columns to a new file (file3) separated by "->"?

for example using above:

file1:

Code:

homer simpson age old_address occupation
marge simpson age old_address occupation
bart simpson old_address occupation
lisa simpson old_address occupation
maggie simpson old_address occupation

file2:

Code:

LISA SIMPSON new_address occupation
maggie simpson new_address occupation
GRANPA simpson age new_address occupation

output (file3):

Code:

lisa simpson old_address occupation -> LISA SIMPSON new_address occupation
maggie simpson old_address occupation -> maggie simpson new_address occupation

Thanks

Ghetz

View Public Profile for Ghetz

Find all posts by Ghetz

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

How to compare two columns in two files?

Hi All, I have a.dat file with content 1,338,30253395122015103,2015103,UB0085000,STMT151117055527002,,, 1,338,30253395122015103,2015103,UB0085000,STMT151117055527001,,, and b.dat having content 1,STMT151117055527001,a1.txt,b1.txt,c1.txt 1,STMT151117055527002,a2.txt,b2.txt,c2.txt ...

2. UNIX for Dummies Questions & Answers

Help need to compare columns in files

Hi, Below is my requirement file1 id|cnt 1|1 2|2 3|3 file2 id_1|cnt_1 1|1 2|1 3|1 I want to compare cnt and cnt_1 columns, if they are differ then give the details Am using below awk command, but the output is not as expected.

3. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'...

4. Shell Programming and Scripting

Compare columns in different files

Hi, I have two files like this: 8 1.3 10 1.3 12 1.3 15 1.3 21 1.3 and 1 2 3 4 10 11 15 16 21 22

5. Shell Programming and Scripting

Compare multiple columns from 2 files

Hi, I need to compare multiple columns from 2 files. I can, for example, have these 2 files: file1: col1, col2, col3,col4 a,1,4,7 b,2,5,8 c,3,6,9file2: col1, col2, col3,col4 a,2,3,2 b,5,7,5 c,1,9,8As a result, I need for example the difference between the columns 2 and 4: col2,...

6. Shell Programming and Scripting

Compare columns in two different files using awk

Hi, I want to compare the columns of two files excluding column 2 from both the files. I tried this awk command. awk -F":" 'NR==FNR{++a;next} !(a)' file1.txt file2.txt . Example: File1.txt 123:09-15-2011:abc:123456 123:09-15-2011:abc:234567 123:09-15-2011:abc:345678 ...

7. Shell Programming and Scripting

Compare Columns of two files

Hi I have file 1 like this and file 2 like this I need to compare column 3 of both files and delete lines in file1 with same column 3 values in two files. So the output is I tried with perl but didnt work. A perl code will be good as I am learning the language, but any other code would...

8. UNIX for Dummies Questions & Answers

Compare Columns in two files

Hi all, I would like to compare a column in one file to a column in another file and when there is a match it prints the first column and the corresponding second column. Example File1 ABA ABC ABE ABF File 2 ABA 123 ABB 124 ABD 125 ABC 126 So what I would like printed to a file...

9. Shell Programming and Scripting

How to compare 2 files & get only few columns based on a condition related to both files?

Hiiiii friends I have 2 files which contains huge data & few lines of it are as shown below File1: b.dat(which has 21 columns) SSR 1976 8 12 13 10 44.00 39.0700 70.7800 7.0 0 0.00 0 2.78 0.00 0.00 0 0.00 2.78 0 NULL ISC 1976 8 12 22 32 37.39 36.2942 70.7338...

10. Shell Programming and Scripting

Compare few columns from two files

My Friends, Need your help to find the difference between few columns from two comma delimited files. For example, File1 and File2 has 22 columns, and I want to find the difference in first 12 columns. I have list of file names in MyListOfFiles2Compare.txt. Data is separated with commas....

Login or Register to Ask a Question