CODEWITHSUNDEEP
awk
mmm
mmm

Reputation: 35

Use awk to limit a flag based on two columns of a file

I have an awk command that compares column 3 based on column 2. If column 2 (ID) is equal, it compares column 3 (coupon) to see if the values are different and flags them as 0 or 1.

awk 'BEGIN{OFS=" ";} NR==1 { print; next } { print $0, ($2 == a) ? (($3 == b) ? "0" : "1") : "0"; a = $2; b = $3 }' testv1.txt

Sample input.

Month    ID Coupon  Reprice
2013-12  2  4.00    
2014-01  2  4.00    
2014-02  2  4.50    
2014-03  2  4.50    
2013-10  3  3.00    
2013-11  3  3.00    
2013-12  3  3.25    
2014-01  3  3.25    
2014-02  3  3.25    
2014-03  3  3.50

This is the current output.

Month    ID Coupon  Reprice
2013-12  2  4.00    0
2014-01  2  4.00    0
2014-02  2  4.50    1
2014-03  2  4.50    0
2013-10  3  3.00    0
2013-11  3  3.00    0
2013-12  3  3.25    1
2014-01  3  3.25    0
2014-02  3  3.25    0
2014-03  3  3.50    1

What I would like is, if the ID is the same, only let the reprice flag value 1 occur one time, like so (the last reprice flag would be 0 instead of 1).

Month    ID Coupon  Reprice
2013-12  2  4.00    0
2014-01  2  4.00    0
2014-02  2  4.50    1
2014-03  2  4.50    0
2013-10  3  3.00    0
2013-11  3  3.00    0
2013-12  3  3.25    1
2014-01  3  3.25    0
2014-02  3  3.25    0
2014-03  3  3.50    0

Sorry about the posting errors.

Upvotes: 1

Views: 1676

Answers (1)

SheetJS
SheetJS

Reputation: 22925

Use an associative array (also, next time provide a sample input): 

awk 'BEGIN{OFS=" ";} NR==1 { print; next } { v = ($2 == a) ? (($3 == b) ? "0" : "1") : "0"; } v == "1" && reprice[$2] { v = "0"; } { print $0, v; a = $2; b = $3 } v == "1" {reprice[$2] = 1}' testv1.txt

Explanation:

{ v = ($2 == a) ? (($3 == b) ? "0" : "1") : "0"; } calculates the value for the Reprice column

v == "1" {reprice[$2] = 1} marks that we have seen a reprice (which only triggers if a reprice happened, namely when v == "1")

v == "1" && reprice[$2] { v = "0"; } will set reprice to "0" when you've already seen a reprice for the given id.

{ print $0, v; a = $2; b = $3 } prints the data

Upvotes: 2

Related Questions