awk column matching AND mismatching between two files.

grail · 05-27-2016, 01:16 PM

Well I am not sure why the '!' was giving you weird information but the single quotes would not have made it done anything good, I would suggest the results were just lucky.

Using your new input data and changing field 2 for 3 I get the correct results for both files irrelevant of order:

Code:

$ awk 'FNR==NR{a[$3$4] = $1;next}a[$3$4] && a[$3$4] != $1' f1 f2
Dc id 168 20
P d 76 0
P f 76 19
V d 75 0
V f 75 19
G f 9 19
G t 9 18
$ awk 'FNR==NR{a[$3$4] = $1;next}a[$3$4] && a[$3$4] != $1' f2 f1
Di i 168 20
Mm d 75 0
Mm f 75 19
Mv d 76 0
Mv f 76 19
n f 9 19
n t 9 18

I hand checked all results and they are correct.

Good luck with the python solution.

danielbmartin · 05-29-2016, 02:14 PM

Quote:

Originally Posted by grail

[code]
$ awk 'FNR==NR{a[$3$4] = $1;next}a[$3$4] && a[$3$4] != $1' f1 f2

A wonderful concise solution. Testing reveals a minor oversight.

With InFile1 ...

Code:

Di i 168 20
aa bb 5 66

... and InFile2 ...

Code:

Dc id 168 20
cc dd 56 6

... the code given in post #16 gives this result ...

Code:

Dc id 168 20
cc dd 56 6

... but the correct results are ...

Code:

Dc id 168 20

This "band aid" fix works ...

Code:

awk 'FNR==NR{a[$3"~"$4] = $1;next}a[$3"~"$4] && a[$3"~"$4] != $1' f1 f2

... but perhaps you have a nicer one.

Daniel B. Martin

grail · 05-29-2016, 02:18 PM

Good pick up Daniel. I would just use a comma which will place SUBSEP between each, but any character like your suggestion is fine too