Compare columns in two different files using awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare columns in two different files using awk
# 1  
Old 09-15-2011
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.

Code:
 awk -F":" 'NR==FNR{++a[$1,$3,$4];next} !(a[$1,$3,$4])' file1.txt file2.txt

[Apart from column2, if the any of the columns did not match, then that whole row of file2.txt is printed; but I have compared considering that there are only 4 columns on the whole].

Example: File1.txt
Code:
123:09-15-2011:abc:123456
123:09-15-2011:abc:234567
123:09-15-2011:abc:345678

File2.txt
Code:
123:09152011:abc:123456
123:09152011:abc:234567
123:09152011:abc:124567

In actual case, I am not sure how many columns each file may contain, so this awk command I constructed is limitted.
How can I compare columnwise, excluding column 2, without knowing the total number of columns in each file?

Last edited by zaxxon; 09-15-2011 at 10:20 AM.. Reason: added some more code tags for the examples
# 2  
Old 09-15-2011
Hi,

One way:
Code:
$ awk 'BEGIN { FS = OFS = ":" } NR==FNR{ $2 = ""; ++a[$0];next} { second_field = $2; $2 = ""; if ( !(a[$0]) ) { $2 = second_field ; print } }' file1 file2
123:09152011:abc:124567

Regards,
Birei
# 3  
Old 09-15-2011
Thanks Birei,

Can you explain the code? I am still a rookie with awk Smilie
# 4  
Old 09-15-2011
Here you have:
Code:
$ cat script.awk
## Execute this part once. Set input and output field separators to ':'.
BEGIN {
        FS = OFS = ":"
}

## FNR counts lines of each file and NR counts lines of all input files so
## they only will be equal processing first input file.
NR == FNR {
        ## Remove second field and save all the line in an array, so lines will be
        ## saved like:
        ## 123::abc:123456
        ## 123::abc:234567
        ## 123::abc:345678
        ##
        ## We have rid of second field!!
        $2 = ""
        ++a[$0]

        ## Process next line from the beginning.
        next
}

## Run this part of code in every line (of second file).
{ 
        ## Save second field and reset it, like in first file.
        second_field = $2
        $2 = ""

        ## Search for the line in the array, if not exists it means that any field 
        ## (except second one, because was empty) is different, so recover second field and
        ## print the line.
        if ( !a[$0] ) {
                $2 = second_field
                print
        }
}
$ awk -f script.awk file1 file2
123:09152011:abc:124567

Regards,
Birei
This User Gave Thanks to birei For This Post:
# 5  
Old 09-15-2011
MySQL

Thanks a lot for such a detailed explanation Birei!
# 6  
Old 02-15-2012
Error How to compare two columns in two separate fiiles..

I want to know all the possible solutions to this like using cut, diff, awk, sed, script etc.
problem-
compare two columns in 2 diff files...
output-both columns are exactly matching
or both columns are not matching and show the difference.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compare 2 columns of files awk

hello everybody I have 2 files the file1 has 10 columns and the form: ... 110103 0802 1.16 38 20.16 22 1.21 8.77 0.00 20 120103 0832 23.40 38 22.10 21 46.35 10.17 0.00 28 120103 1413 45.00 38 24.50 21 48.85 7.89 0.00 38 130103 1112 23.40 38 22.10 21 48.85 ... (5 Replies)
Discussion started by: phaethon
5 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

[Solved] awk compare two different columns of two files and print all from both file

Hi, I want to compare two columns from file1 with another two column of file2 and print matched and unmatched column like this File1 1 rs1 abc 3 rs4 xyz 1 rs3 stu File2 1 kkk rs1 AA 10 1 aaa rs2 DD 20 1 ccc ... (2 Replies)
Discussion started by: justinjj
2 Replies

5. 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

6. Shell Programming and Scripting

Compare intervals (columns) from two files (awk, grep, Perl?)

Hi dear users, I need to compare numeric columns in two files. These files have the following structure. K.txt (4 columns) A001 chr21 9805831 9846011 A002 chr21 9806202 9846263 A003 chr21 9887188 9988593 A003 chr21 9887188 ... (2 Replies)
Discussion started by: jcvivar
2 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. Shell Programming and Scripting

awk compare specific columns from 2 files, print new file

Hello. I have two files. FILE1 was extracted from FILE2 and modified thanks to help from this post. Now I need to replace the extracted, modified lines into the original file (FILE2) to produce the FILE3. FILE1 1466 55.27433 14.72050 -2.52E+03 3.00E-01 1.05E+04 2.57E+04 1467 55.27433... (1 Reply)
Discussion started by: jm4smtddd
1 Replies

9. Shell Programming and Scripting

awk compare 2 columns, 2 files, output whole line

Hello, I have not been able to find what I'm looking for via searching the forum. I could use some help with an awk script or one-liner to solve this simple problem. I have two files. If $1 and $2 from file1 match $1 and $2 from file2, print the whole line from file2. Example file1 ... (2 Replies)
Discussion started by: jm4smtddd
2 Replies

10. 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
Login or Register to Ask a Question