CODEWITHSUNDEEP
awk
user3009183
user3009183

Reputation: 3

AWK - lookup and replace multiple fields using multiple files

Having trouble working out what I need to achieve in AWK. I have 2 files:

File1

1|2|3|4|5|6|7|8|9|AAA|BBB|12|13|
1|2|3|4|5|6|7|8|9|CCC|DDD|12|13|
1|2|3|4|5|6|7|8|9|EEE|FFF|12|13|
1|2|3|4|5|6|7|8|9|GGG|HHH|12|13|
1|2|3|4|5|6|7|8|9|III|JJJ|12|13|
1|2|3|4|5|6|7|8|9|KKK|LLL|12|13|
1|2|3|4|5|6|7|8|9|MMM|NNN|12|13|
1|2|3|4|5|6|7|8|9|OOO|PPP|12|13|
1|2|3|4|5|6|7|8|9|QQQ|RRR|12|13|
1|2|3|4|5|6|7|8|9|SSS|TTT|12|13|
1|2|3|4|5|6|7|8|9|UUU|VVV|12|13|
1|2|3|4|5|6|7|8|9|WWW|XXX|12|13|
1|2|3|4|5|6|7|8|9|YYY|ZZZ|12|13|
1|2|3|4|5|6|7|8|9|QWE|RTY|12|13|
1|2|3|4|5|6|7|8|9|ASD|FGH|12|13|
1|2|3|4|5|6|7|8|9|ZXC|VBN|12|13|
1|2|3|4|5|6|7|8|9|ASS|BOB|12|13|
1|2|3|4|5|6|7|8|9|FFR|ERD|12|13|

File2

AAA|BBB|AA1|BB1|
CCC|DDD|CC1|DD1|
EEE|FFF|EE1|FF1|
GGG|HHH|GG1|HH1|
III|JJJ|II1|JJ1|
KKK|LLL|KK1|LL1|
MMM|NNN|MM1|NN1|
OOO|PPP|OO1|PP1|
QQQ|RRR|QQ1|RR1|
SSS|TTT|SS1|TT1|

I need to replace fields 10 and 11 in file 1 with fields 3 and 4 from file 2 where fields 10 and 11 in file 1 equal fields 1 and 2 in file 2.

So my desired output fot this example would be:

1|2|3|4|5|6|7|8|9|AA1|BB1|12|13|
1|2|3|4|5|6|7|8|9|CC1|DD1|12|13|
1|2|3|4|5|6|7|8|9|EE1|FF1|12|13|
1|2|3|4|5|6|7|8|9|GG1|HH1|12|13|
1|2|3|4|5|6|7|8|9|II1|JJ1|12|13|
1|2|3|4|5|6|7|8|9|KK1|LL1|12|13|
1|2|3|4|5|6|7|8|9|MM1|NN1|12|13|
1|2|3|4|5|6|7|8|9|OO1|PP1|12|13|
1|2|3|4|5|6|7|8|9|QQ1|RR1|12|13|
1|2|3|4|5|6|7|8|9|SS1|TT1|12|13|
1|2|3|4|5|6|7|8|9|UUU|VVV|12|13|
1|2|3|4|5|6|7|8|9|WWW|XXX|12|13|
1|2|3|4|5|6|7|8|9|YYY|ZZZ|12|13|
1|2|3|4|5|6|7|8|9|QWE|RTY|12|13|
1|2|3|4|5|6|7|8|9|ASD|FGH|12|13|
1|2|3|4|5|6|7|8|9|ZXC|VBN|12|13|
1|2|3|4|5|6|7|8|9|ASS|BOB|12|13|
1|2|3|4|5|6|7|8|9|FFR|ERD|12|13|

Many thanks for your help.

Upvotes: 0

Views: 324

Answers (1)

fedorqui
fedorqui

Reputation: 289725

Does this make it?

awk 'BEGIN{FS=OFS="|"}
     FNR==NR {a[$1,$2]=$3; b[$1,$2]=$4; next}
    ($10,$11) in a {f10=a[$10,$11];$11=b[$10,$11];$10=f10}
    1' f2 f1

Test

$ awk 'BEGIN{FS=OFS="|"} FNR==NR {a[$1,$2]=$3; b[$1,$2]=$4; next} ($10,$11) in a {f10=a[$10,$11];$11=b[$10,$11];$10=f10}1' f2 f1
1|2|3|4|5|6|7|8|9|AA1|BB1|12|13|
1|2|3|4|5|6|7|8|9|CC1|DD1|12|13|
1|2|3|4|5|6|7|8|9|EE1|FF1|12|13|
1|2|3|4|5|6|7|8|9|GG1|HH1|12|13|
1|2|3|4|5|6|7|8|9|II1|JJ1|12|13|
1|2|3|4|5|6|7|8|9|KK1|LL1|12|13|
1|2|3|4|5|6|7|8|9|MM1|NN1|12|13|
1|2|3|4|5|6|7|8|9|OO1|PP1|12|13|
1|2|3|4|5|6|7|8|9|QQ1|RR1|12|13|
1|2|3|4|5|6|7|8|9|SS1|TT1|12|13|
1|2|3|4|5|6|7|8|9|UUU|VVV|12|13|
1|2|3|4|5|6|7|8|9|WWW|XXX|12|13|
1|2|3|4|5|6|7|8|9|YYY|ZZZ|12|13|
1|2|3|4|5|6|7|8|9|QWE|RTY|12|13|
1|2|3|4|5|6|7|8|9|ASD|FGH|12|13|
1|2|3|4|5|6|7|8|9|ZXC|VBN|12|13|
1|2|3|4|5|6|7|8|9|ASS|BOB|12|13|
1|2|3|4|5|6|7|8|9|FFR|ERD|12|13|

Explanation

  • BEGIN{FS=OFS="|"} set input and output field separators to |.
  • FNR==NR {a[$1,$2]=$3; b[$1,$2]=$4; next} while reading the first file given (f2), store 3rd field in the array a[] and 4th field in array b[] with index given by the pair ($1,$2).
  • ($10,$11) in a {f10=a[$10,$11];$11=b[$10,$11];$10=f10} while looping through the second file given (f1), check if the pair ($10,$11) was stored in the array a[]. If so, do the replacement. Otherwise, lines will keep as they were.
  • 1 as per True condition, print the current line.

Upvotes: 1

Related Questions