Compare two columns of different files and replace another using AWK

Question

I have these two files:

file1

F729981596975938577090932834 18 A1472861
F446395652970789701163827646 10 B35234576
F446395652970789701163827646 10 C96515097
F02508215481124974649572984 18 D83395117
F02508215481124974649572984 18 E26810286

file2

F02508215481124974649572984 28

What I've been trying to accomplish to no avail is:

Print all lines in file1 and...
If the first column of file2 matches with the first column of file1, then replace the second field of file1 with the second field of file2.

desired outcome

F729981596975938577090932834 18 A1472861
F446395652970789701163827646 10 B35234576
F446395652970789701163827646 10 C96515097
F02508215481124974649572984 28 D83395117   <- Second field successfully replaced.
F02508215481124974649572984 28 E26810286   <- Second field successfully replaced.

The closest I've gotten is this:

awk ' NR == FNR { a[$1] = $2 ; next } { $3 = a[$1] ; print } ' file2 file1

F729981596975938577090932834 18
F446395652970789701163827646 10
F446395652970789701163827646 10
F02508215481124974649572984 18 28
F02508215481124974649572984 18 28

Any help would be greatly appreciated.

The fourth bird · Accepted Answer · 2021-09-24 11:25:56Z

1

Currently you are using the third field, and you are not checking if the array contains the value.

If you set it to the second field, without checking the array, the first 3 lines will not find a value in the array and the current $2 column value will be empty.

You could check if the first field is in the array. If it is, set the second field to that value.

awk ' NR == FNR { a[$1] = $2 ; next } { if ($1 in a) {$2 = a[$1]}; print } ' file2 file1

Output

F729981596975938577090932834 18 A1472861
F446395652970789701163827646 10 B35234576
F446395652970789701163827646 10 C96515097
F02508215481124974649572984 28 D83395117
F02508215481124974649572984 28 E26810286

Or slightly shorter

awk ' NR == FNR { a[$1] = $2 ; next } { if($1 in a) $2 = a[$1]} 1' file2 file1

edited Sep 24, 2021 at 11:25

answered Sep 24, 2021 at 11:15

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare two columns of different files and replace another using AWK

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related