Visit Our UNIX and Linux User Community


How to compare two columns in two files?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to compare two columns in two files?
# 8  
Old 09-28-2009
Base on your original data sample
Code:
awk -F, 'NR==FNR{a[$0]=$0;next}a[$2FS$7]{print a[$2FS$7],"->",$0}' file1 file2

# 9  
Old 09-29-2009
Thanks danmero, but my request is slightly different.

I want to compare file1 and file2 based on their first 2 columns (case insensitive). If the 2 columns match then print their lines to file3 as shown below.

(input) 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

input (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
# 10  
Old 09-29-2009
Quote:
Originally Posted by Ghetz
Thanks danmero, but my request is slightly different.
Code:
# awk 'NR==FNR{a[$1FS$2]=$0;next}a[tolower($1FS$2)]{print a[tolower($1FS$2)],"->",$0}' file1 file2

# 11  
Old 09-29-2009
Many thanks danmero.

It works perfectly.

---------- Post updated 09-30-09 at 02:27 AM ---------- Previous update was 09-29-09 at 01:33 PM ----------

My final request danmero:

I tried to send the output to a file (say file3) by amending your code as follows:

Code:
awk 'NR==FNR{a[$1FS$2]=$0;next}a[tolower($1FS$2)]{print a[tolower($1FS$2)],"->",$0}' file1 file2 >> file3

but it ends up joining the lines. Don't know how to include the newline character.

Thanks
# 12  
Old 09-29-2009
Quote:
Originally Posted by Ghetz
but it ends up joining the lines. Don't know how to include the newline character.
Works for me Smilie

Code:
# cat file1
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
# cat file2
LISA SIMPSON new_address occupation
maggie simpson new_address occupation
GRANPA simpson age new_address occupation
# awk 'NR==FNR{a[$1FS$2]=$0;next}a[tolower($1FS$2)]{print a[tolower($1FS$2)],"->",$0}' file1 file2 > file3
# cat file3
lisa simpson old_address occupation -> LISA SIMPSON new_address occupation
maggie simpson old_address occupation -> maggie simpson new_address occupation

# 13  
Old 09-30-2009
This is my code:

Code:
#/!bin/bash

NR==FNR{a[$1FS$2]=$0;next}a[tolower($1FS$2)]{print a[tolower($1FS$2)],"->",$0}' file1 file2 >file3

I previously tried it in Cygwin, which gave an output on a single line.

But on a Mac I get the following error:

Code:
line 3: syntax error near unexpected token `$1FS$2'
./match.sh: line 3: `NR==FNR{a[$1FS$2]=$0;next}a[tolower($1FS$2)]{print a[tolower($1FS$2)],"->",$0}' file1 file2 >file3'

Any ideas?

Thanks

---------- Post updated at 10:29 AM ---------- Previous update was at 10:24 AM ----------

danmero

Please ignore my previous post. Your code works fine.

I typed the code incorrectly. My bad.

Thanks
# 14  
Old 10-02-2009
Hi danmero

I applied your code :
Code:
awk 'NR==FNR{a[$1FS$2]=$0;next}a[tolower($1FS$2)]{print a[tolower($1FS$2)],"->",$0}' file1 file2

to the following input files and there's no output even though there are matching items:

file1:
Code:
Cohen	Philip 	28 Johnson Street
Cohen	Hanna	28 Johnson Street
Cohen	Sarah	28 Johnson Street
Cohen	Solomon	28 Johnson Street
Cohen	Jacob	72 Stock Street
Cohen	Flora	72 Stock Street
Cohen	Charlotte	4 Back Stock Street
Cohen	Jane	4 Back Stock Street
Cohen	Joel	4 Back Stock Street
Cohen	Mark	4 Back Stock Street
Cohen	Caroline	6 Clarrissa Place
Cohen	Henry	6 Clarrissa Place
Cohen	Joseph	6 Clarrissa Place
Cohen	Hyman	12 Winter Street
Cohen	Zipora	12 Winter Street
Cohen	David	12 Winter Street
Cohen	Moses	12 Winter Street
Cohen	Dora	12 Winter Street
Cohen	Emily	12 Winter Street
Cohen	Mark	 Winter Street
Cohen	Emma	 Winter Street
Cohen	Solly	 Winter Street
Cohen	Joel	 Winter Street
Cohen	Esther	 Winter Street
Cohen	Mary Ann	 Winter Street
Cohen	Jacob	 Winter Street
Cohen	Fushel 	14 Fernie Street
Cohen	Maria	14 Fernie Street
Cohen	Annie	14 Fernie Street
Cohen	Betsy	14 Fernie Street
Cohen	Sarah	14 Fernie Street
Cohen	Samuel	16 Fernie Street
Cohen	Maria	16 Fernie Street
Cohen	Annie	16 Fernie Street
Cohen	Fanny	16 Fernie Street
Cohen	Harris	28 Fernie Street
Cohen	Jane	28 Fernie Street
Cohen	Henry	28 Fernie Street
Cohen	Yetta	28 Fernie Street
Cohen	Levi	28 Fernie Street
Cohen	Gotlip 	25 Fernie Street
Cohen	Gotleib	25 Fernie Street
Cohen	Dorah	25 Fernie Street
Cohen	Yetty	25 Fernie Street
Cohen	Harris	10 Verdon Street
Cohen	Mary	10 Verdon Street
Cohen	Eliza	10 Verdon Street
Cohen	Samuel	22 Verdon Street
Cohen	Hyman	27 Verdon Street
Cohen	Ann 	27 Verdon Street
Cohen	Ann	27 Verdon Street
Cohen	Billy	27 Verdon Street
Cohen	Ernest	27 Verdon Street
Cohen	Louis	27 Verdon Street
Cohen	Mark	27 Verdon Street
Cohen	Esther	27 Verdon Street
Cohen	Wolf	27 Verdon Street
Cohen	Hannah	27 Verdon Street
Cohen	Louis	27 Verdon Street
Cohen	David 	7 Back Verdon Street
Cohen	Siggly	7 Back Verdon Street
Cohen	Abraham	7 Back Verdon Street
Cohen	Lewis	9 Back Verdon Street
Cohen	Hetty	9 Back Verdon Street
Cohen	Rachel	9 Back Verdon Street
Cohen	Ernest	9 Back Verdon Street
Cohen	George	10 Back Scotland
Cohen	Levi	22 Scotland Cellar
Cohen	Sarah	22 Scotland Cellar
Cohen	David	22 Scotland Cellar
Cohen	Rachel	22 Scotland Cellar
Cohen	Ellen	22 Scotland Cellar
Cohen	Rosa	22 Scotland Cellar
Cohen	Sarah	22 Scotland Cellar
Cohen	Esther	22 Scotland Cellar
Cohen	Louis	40 Fernie Street
Cohen	Sarah	40 Fernie Street
Cohen	Isaac	40 Fernie Street
Cohen	Esther	40 Fernie Street
Cohen	Isaac	42 Fernie Street
Cohen	Sarah	42 Fernie Street
Cohen	Hanna	42 Fernie Street
Cohen	Jacob	42 Fernie Street
Cohen	Rachel	42 Fernie Street
Cohen	Rebecca	42 Fernie Street
Cohen	Barnett	42 Fernie Street
Cohen	Leana	42 Fernie Street
Cohen	Janett	42 Fernie Street
Cohen	Solomon	44 Fernie Street
Cohen	Sarah	44 Fernie Street
Cohen	Barnet	51 Fernie Street
Cohen	Rebecca	51 Fernie Street
Cohen	Lewis	55 Fernie Street
Cohen	Simon	40 Moreton Street
Cohen	Meyer	40 Moreton Street
Cohen	Michael	16 Julia Street
Cohen	Aaron	3 George Street
Cohen	Hannah	3 George Street
Cohen	Woolf	3 George Street
Cohen	Morris	3 George Street
Cohen	Raphael	3 George Street
Cohen	Emila	3 George Street
Cohen	Rachel	3 George Street
Cohen	Philip	83 Berkley Street
Cohen	Flora	83 Berkley Street
Cohen	George	83 Berkley Street
Cohen	Rosa	83 Berkley Street
Cohen	Alfred	83 Berkley Street
Cohen	Sarah	83 Berkley Street
Cohen	Sarah	83 Berkley Street
Cohen	Abraham	87 Berkley Street
Cohen	Augusta	87 Berkley Street
Cohen	Esther	87 Berkley Street
Cohen	Charles	87 Berkley Street
Cohen	Joshua	11 Moreton Street
Cohen	Joshua	13 Moreton Street
Cohen	Ezra	 Boarding house
Cohen	Michael	23 Irwell Street
Cohen	Minnie	23 Irwell Street
Cohen	Sarah	23 Irwell Street
Cohen	Rebecca	23 Irwell Street
Cohen	Eugen	27 Whitfield Street
Cohen	Elija J	36 Clarance Street
Cohen	Rebecca	36 Clarance Street
Cohen	Simon J	27 Broughton Street
Cohen	Ina	27 Broughton Street
Cohen	Fanny	27 Broughton Street
Cohen	Polly	27 Broughton Street
Cohen	Isaac	27 Broughton Street
Cohen	Levy A	44 Elizabeth Street
Cohen	Ledreia	44 Elizabeth Street
Cohen	Moses	44 Elizabeth Street
Cohen	Amelia	44 Elizabeth Street
Cohen	Mindlla	6 Gt. Cheetham St.
Cohen	Jacob	55 Dantzig Street
Cohen	Betsy	55 Dantzig Street
Cohen	Hyman	66 Portland Street
Cohen	Fany	66 Portland Street
Cohen	Betsy	66 Portland Street
Cohen	Joseph	66 Portland Street
Cohen	David	66 Portland Street
Cohen	John	2 Hulmes Court
Cohen	Mary	2 Hulmes Court
Cohen	Eliza	2 Hulmes Court
Cohen	Mary A	2 Hulmes Court
Cohen	Margret	2 Hulmes Court
Cohen	Sarah J	2 Hulmes Court
Cohen	Catherine	2 Hulmes Court
Cohen	Marks	8 Oswald Street
Cohen	Mary	8 Oswald Street
Cohen	Lewis	231 Deansgate
Cohen	Sarah	231 Deansgate
Cohen	Marks	231 Deansgate
Cohen	Fanny	231 Deansgate
Cohen	Jacob	231 Deansgate
Cohen 	Alex	22 York Street
Cohen 	Myer	22 York Street
Cohen 	Esther	22 York Street
Cohen 	Sarah	22 York Street
Cohen 	Purcilla	150 York Street
Cohen 	Esther	150 York Street
Cohen 	Eliezer	150 York Street
Cohen 	Maria	150 York Street
Cohen 	Edith	150 York Street
Cohen 	Marcella	150 York Street
Cohen 	Harry 	67 North Street
Cohen 	Hannah	67 North Street
Cohen 	Bertha	67 North Street
Cohen 	Jacob	67 North Street
Cohen 	Morris	67 North Street

file2
Code:
COHEN	Miriam	6 Johnson St
COHEN	Rachel	6 Johnson St
COHEN	Hyman	25 Clarence St
COHEN	Benjamin	6 Johnson St
COHEN	Solomon	6 Johnson St
COHEN	Hannah	6 Johnson St
COHEN	Moses	28 Lord St
COHEN	Solomon	30 Julia St
COHEN	Nathan	10 Foulkes St
COHEN	Jane	82 Moreton St
COHEN	Hannah	99 Moreton St
COHEN	Hannah	30 Julia St
COHEN	Rachel	10 Foulkes St
COHEN	Isaac	25 Johnson St
COHEN	Jacob	251 York
COHEN	Jacob	82 Moreton St
COHEN	Arron	99 Moreton St
COHEN	Mondel	11 Cheetwood St
COHEN	Israel	62 Moreton St
COHEN	Mary	62 Moreton St
COHEN	Abraham	62 Moreton St
COHEN	Hana	42 Fernie St
COHEN	Rebecca	29 Mary St
COHEN	Fanny	29 Mary St
COHEN	Abraham	29 Mary St
COHEN	Annie	27 Berkeley St
COHEN	Rebecca	12 Foulkes St
COHEN	Ester	11 Cheetwood St
COHEN	Rachel	42 Fernie St
COHEN	Bertha	29 Mary St
COHEN	Sarah	11 Cheetwood St
COHEN	Leah	11 Cheetwood St
COHEN	Morris	42 Fernie St
COHEN	Heron	29 Mary St
COHEN	Lewis	29 Mary St
COHEN	Micheal	27 Berkeley St
COHEN	Simon	11 Cheetwood St
COHEN	Rachael	11 Cheetwood St
COHEN	David	12 Foulkes St
COHEN	Soloman	23 Berkeley St
COHEN	Harris	23 Berkeley St
COHEN	Yetta	23 Berkeley St
COHEN	Jacob	54 Fernie St
COHEN	Arron	136 Moreton St
COHEN	Rachael	128 Moreton St
COHEN	Mary	76 Moreton St
COHEN	Sarah	80 Moreton St
COHEN	Elliss	128 Moreton St
COHEN	Kalman	107 Moreton St
COHEN	Sarah	107 Moreton St
COHEN	Mark	76 Moreton St
COHEN	Morris	9 Nightingale St
COHEN	Abraham	107 Moreton St
COHEN	Solomon	40 Carter St
COHEN	Maria	15 Francis Street
COHEN	Samuel	26 Pimblett St
COHEN	David	16 Pimblett Street
COHEN	Fagy	16 Pimblett Street
COHEN	Esther	26 Pimblett St
COHEN	Glaty	16 Pimblett Street
COHEN	Morris	15 Drake St
COHEN	Wolf	15 Dewhurst St
COHEN	Goodman	15 Dewhurst St
COHEN	Lear	15 Dewhurst St
COHEN	David	40 Lord St
COHEN	Freedman	40 Lord St
COHEN	Hannah	40 Lord St
COHEN	Sarah J.	40 Lord St
COHEN	Betsy	40 Lord St
COHEN	Joseph	40 Lord St
COHEN	Flora	15 Broughton St
COHEN	Joseph	53 Fernie St
COHEN	Rachael	7 Augustus St
COHEN	Betsy	2 Foulkes St
COHEN	Lewis L.	38 (& 40) Fernie St
COHEN	Wolf	34 Fernie St
COHEN	Aaron	86 Pimblett St
COHEN	Annie	34 Fernie St
COHEN	Isaac	38 (& 40) Fernie St
COHEN	Esther	54 Fernie St
COHEN	Abraham	39 Fernie St
COHEN	Morris T.	34 Fernie St
COHEN	Rachel	52 Fernie St
COHEN	Rose	36 Fernie St
COHEN	Sarah	38 (& 40) Fernie St
COHEN	Jacob	15 Broughton St
COHEN	Harris	7 Augustus St
COHEN	Zetta	53 Fernie St
COHEN	Dorah	53 Fernie St
COHEN	Esther	39 Fernie St
COHEN	Susman	40 Lord St

Just can't figure out what the problem is.

Please help.

Thanks

Previous Thread | Next Thread
Test Your Knowledge in Computers #345
Difficulty: Medium
Scott McNealy received $238 million USD when the Oracle acquisition closed.
True or False?

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 ... (13 Replies)
Discussion started by: PRAMOD 96
13 Replies

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

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'... (5 Replies)
Discussion started by: bkane3
5 Replies

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 (3 Replies)
Discussion started by: jamie_123
3 Replies

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,... (3 Replies)
Discussion started by: Subbeh
3 Replies

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

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... (1 Reply)
Discussion started by: polsum
1 Replies

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

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

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

Featured Tech Videos