Comparing multiple columns using awk

Shell Programming and Scripting
1

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

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

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

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:

 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.:wall:

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

2

How about

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
1 Like
3

Perfect! Thanks Rudy!

Can you please explain the second part of the 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

Hello mystition,

Following may help you in same.

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

Would this come close?

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