Visit Our UNIX and Linux User Community


Comparing multiple columns using awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Comparing multiple columns using awk
# 1  
Old 05-12-2016
Comparing multiple columns using awk

Hello All;

I have two files with below conditions:

1. Entries in file A is missing in file B (primary is field 1)
2. Entries in file B is missing in file A (primary is field 1)
3. Field 1 is present in both files but Field 2 is different.

Example Content:
File A
Code:
531520011,8501,20160831000000,20160931000000
531520012,8502,20160831000001,20160931000001
531520013,8503,20160831000002,20160931000002
531520014,8504,20160831000003,20160931000003
531520017,8505,20160831000006,20160931000006
531520018,8015,20160831000007,20160931000007
531520019,8015,20160831000008,20160931000008
531520020,8015,20160831000009,20160931000009
531521020,8010,20160831000009,20160931000009
531520021,8015,20160831000010,20160931000010

File B
Code:
531520011,8511,20160831000000,20160931000000
531520012,8512,20160831000001,20160931000001
531520013,8513,20160831000002,20160931000002
531520018,8015,20160831000007,20160931000007
531522019,8015,20160831000008,20160931000008
531522020,8015,20160831000009,20160931000009
531521020,8010,20160831000009,20160931000009
531522021,8015,20160831000010,20160931000010

For Condition 1, I used the below code:
1. Entries in file A is missing in file B (primary is field 1)
Code:
awk -F"," 'FNR==NR{A[$1]=$1;next} !($1 in A){print $0}' FileA File B

Similarly, For Condition 2, I used the below code:
2. Entries in file B is missing in file A (primary is field 1)
Code:
awk -F"," 'FNR==NR{A[$1]=$1;next} !($1 in A){print $0}' FileB File A

But for the third condition, I am facing issue.
3. Field 1 is present in both files but Field 2 is different.
I am using below code which is not working:
Code:
 awk -F, 'NR==FNR{a[$2]=$1$2;next } !(a[$2]==$1$2){print $0}' FileA File B

I know that am missing a part of the logic to handle the missing entries. Please assist on the Condition 3.Smilie

## Just to add, I need to print the changed entry of file 1 and file2 (condition 3) both - in two different output files. I can do it using awk and comm - but I am looking for a single command to do this.

Last edited by mystition; 05-12-2016 at 05:42 AM..
# 2  
Old 05-12-2016
How about
Code:
awk -F"," '
FNR==NR         {A[$1] = $0
                 B[$1] = $2
                 next
                }
!($1 in A)      {print $0 > "COND1"
                 next
                }
$2 != B[$1]     {print $0 > "COND3" 
                }
                {delete A[$1]
                }
END             {for (a in A) print A[a] > "COND2"
                }
' file[12]
cf COND*
COND1:
531522019,8015,20160831000008,20160931000008
531522020,8015,20160831000009,20160931000009
531522021,8015,20160831000010,20160931000010
COND2:
531520020,8015,20160831000009,20160931000009
531520021,8015,20160831000010,20160931000010
531520014,8504,20160831000003,20160931000003
531520017,8505,20160831000006,20160931000006
531520019,8015,20160831000008,20160931000008
COND3:
531520011,8511,20160831000000,20160931000000
531520012,8512,20160831000001,20160931000001
531520013,8513,20160831000002,20160931000002

This User Gave Thanks to RudiC For This Post:
# 3  
Old 05-12-2016
Perfect! Thanks Rudy!

Can you please explain the second part of the code:
Code:
{delete A[$1]} END {for (a in A) print A[a] > "COND2"}'

Actually for Condition 3 - I also need to print one more output of the Entries that differ in File2.
# 4  
Old 05-12-2016
Quote:
Originally Posted by mystition
Perfect! Thanks Rudy!
Can you please explain the second part of the code:
Code:
{delete A[$1]} END {for (a in A) print A[a] > "COND2"}'

Actually for Condition 3 - I also need to print one more output of the Entries that differ in File2.
Hello mystition,

Following may help you in same.
Code:
awk -F"," '                                          ###### Setting field seprator as a comma(,)
FNR==NR         {                                    ###### FNR==NR, condition will be only TRUE when 1st file is being read because FNR and NR are the awk's built-in variables and FNR will be RESET it's value when a new file is being read and NR's value will increse till then last file is being read and completed. So only when first file is being read till end this condition will be TRUE.
                 A[$1] = $0                          ###### Creating an array named A whose index is $1 and value is complete line($0).
                 B[$1] = $2                          ###### Creating an array named B whose index is $1 and value is $2(second field).
                 next                                ###### Skipping all upcoming statements which we DO NOT want them to execute while first file is being read, so next is a awk's built in utility which will skip all further statements now.
                }
!($1 in A)      {                                    ###### Now second file is being read here and we are chekcing here if $1 is NOT present in array A which we created while 1st file was read and whose index is $1 too.
                 print $0 > "COND1"                  ###### if above is TRUE then print the complete line into file named COND1.
                 next                                ###### skipping further next statements further.
                }
$2 != B[$1]     {                                    ###### Now checking condition when $2's value while second file is being read is NOT equal to array B's value whose index is $1 and was set to value of $2 when first file was read.
                 print $0 > "COND3"                  ###### If previous condition is TURE then print the complete line to file named "COND3".
                }
                {delete A[$1]                        ###### Deleting the array A's value whose index is $1(current line's first field).
                }
END             {for (a in A) print A[a] > "COND2"   ###### In END section, we are traversing through the array A's elements and printing them to file named "COND2".
                }
' file[12]                                           ###### mentioning Input_files named file1 and file2 here.

Thanks,
R. Singh
# 5  
Old 05-12-2016
Quote:
Originally Posted by mystition
.
.
.
Can you please explain the second part of the code:
Code:
{delete A[$1]}                          # if it gets here it means both files' lines are identical in $1, a differing $2 has been  
                                        # dealt with, and so the entry can be deleted so it won't be printed in the END section  

END {for (a in A) print A[a] > "COND2"} # The array A now holds only those lines from file1 whose $1 were not found in file2 and
                                        # thus fulfill condition 2.

Quote:
Actually for Condition 3 - I also need to print one more output of the Entries that differ in File2.
Would this come close?

Code:
awk -F"," '
FNR==NR         {A[$1] = $0
                 B[$1] = $2
                 next
                }
!($1 in A)      {print $0 > "COND1"
                 next
                }
$2 != B[$1]     {print $0 > "COND3"
                 print A[$1] > "COND4"
                }
                {delete A[$1]
                }
END             {for (a in A) print A[a] > "COND2"
                }
' file[12]
COND1:
531522019,8015,20160831000008,20160931000008
531522020,8015,20160831000009,20160931000009
531522021,8015,20160831000010,20160931000010
COND2:
531520020,8015,20160831000009,20160931000009
531520021,8015,20160831000010,20160931000010
531520014,8504,20160831000003,20160931000003
531520017,8505,20160831000006,20160931000006
531520019,8015,20160831000008,20160931000008
COND3:
531520011,8511,20160831000000,20160931000000
531520012,8512,20160831000001,20160931000001
531520013,8513,20160831000002,20160931000002
COND4:
531520011,8501,20160831000000,20160931000000
531520012,8502,20160831000001,20160931000001
531520013,8503,20160831000002,20160931000002


Previous Thread | Next Thread
Test Your Knowledge in Computers #319
Difficulty: Medium
The open source program Redis is a relational database server.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Average across multiple columns - awk

Hi forum members, I'm trying to get an average of multiple columns in a csv file using awk. A small example of my input data is as follows: cu,u3o8,au,ag -9,20,-9,3.6 0.005,30,-9,-9 0.005,50,10,3.44 0.021,-9,8,3.35 The following code seems to do most of what I want gawk -F","... (6 Replies)
Discussion started by: theflamingmoe
6 Replies

2. Shell Programming and Scripting

Awk: is it possible to print into multiple columns?

Hi guys, I have hundreds file like this, here I only show two of them: file 1 feco4_s_BB95.log ZE_1=-1717.5206260 feco4_t_BB95.log ZE_1=-1717.5169250 feco5_s_BB95.log ZE_1=-1830.9322060... (11 Replies)
Discussion started by: liuzhencc
11 Replies

3. UNIX for Advanced & Expert Users

Need help in comparing multiple columns from two files.

Hi all, I have two files as below. I need to compare field 2 of file 1 against field 1 of file 2 and field 5 of file 1 against filed 2 of file 2. If both matches , then create a result file 1 with first file data and if not matches , then create file with first fie data. Please help me in... (12 Replies)
Discussion started by: sivarajb
12 Replies

4. Shell Programming and Scripting

awk arrays comparing multiple columns across two files.

Hi, I'm trying to use awk arrays to compare values across two files based on multiple columns. I've attempted to load file 2 into an array and compare with values in file 1, but success has been absent. If anyone has any suggestions (and I'm not even sure if my script so far is on the right lines)... (4 Replies)
Discussion started by: hubleo
4 Replies

5. UNIX for Dummies Questions & Answers

Comparing multiple fields from 2 files uing awk

Hi I have 2 files as below File 1 Chr Start End chr1 120 130 chr1 140 150 chr2 130 140 File2 Chr Start End Value chr1 121 128 ABC chr1 144 149 XYZ chr2 120 129 PQR I would like to compare these files using awk; specifically if column 1 of file1 is equal to column 1 of file2... (7 Replies)
Discussion started by: sshetty
7 Replies

6. Shell Programming and Scripting

Awk match multiple columns in multiple lines in single file

Hi, Input 7488 7389 chr1.fa chr1.fa 3546 9887 chr5.fa chr9.fa 7387 7898 chrX.fa chr3.fa 7488 7389 chr21.fa chr3.fa 7488 7389 chr1.fa chr1.fa 3546 9887 chr9.fa chr5.fa 7898 7387 chrX.fa chr3.fa Desired Output 7488 7389 chr1.fa chr1.fa 2 3546 9887 chr5.fa chr9.fa 2... (2 Replies)
Discussion started by: jacobs.smith
2 Replies

7. Shell Programming and Scripting

Awk if-else syntax with multiple columns

I can't seem to get this to work. I can reformat the date field if it's the first field (and only field) in the file: However, I get a syntax error when the date field is the second field (or has any other columns following): I can use a ";" but then it puts each column on separate... (8 Replies)
Discussion started by: giannicello
8 Replies

8. Shell Programming and Scripting

AWK: Comparing two columns from two different files

Hi - I have two files as follows: File 1: chr5 118464905 118465027 ENST00000514151 utr5 0 + chr5 118464903 118465118 ENST00000504031 utr5 0 + chr5 118468826 118469180 ENST00000504031 utr5 0 + chr5 118469920 118470084 ... (14 Replies)
Discussion started by: polsum
14 Replies

9. Shell Programming and Scripting

comparing the values of repeated keys in multiple columns

Hi Guyz The 1st column of the input file has repeated keys like x,y and z. The ist task is if the 1st column has unique key (say x) and then need to consider 4th column, if it is + symbol then subtract 2nd column value with 3rd column value (we will get 2(10-8)) or if it is - symbol subtract 3rd... (3 Replies)
Discussion started by: repinementer
3 Replies

10. Shell Programming and Scripting

AWK subtraction in multiple columns

AWK subtraction in multiple columns Hi there, Can not get the following: input: 34523 934 9485 3847 394 3847 3456 9384 awk 'NR==1 {for (i = 1; i <= NF; i++) {n=$i; next}; {n-=$i} END {print n}' input output: 21188 first column only,... (2 Replies)
Discussion started by: awkward
2 Replies

Featured Tech Videos