AnsweredAssumed Answered

Using a cursor to compare two side by side fields

Question asked by jborgion Champion on Sep 27, 2017
Latest reply on Sep 28, 2017 by jborgion

I need a little bit of inspiration.  I have two side by side fields in a table, let's call then Addr1 & Addr2.  They happen to be the input and result of a geocoding process where Addr1 is the matched address and Addr2 is the input address.  This particular table are just matches less than 100%.  Some of the reasons for jump right at you, others not so much.  But, it's a list of 3100 records and I'd rather have a cursor go through it than stare at the records and not see the difference.


Having read Richard Fairhursts Turbo Charging Data Manipulation with Python Cursors and Dictionaries, I'd like to try the dictionary approach, but I'm a little fuzzy as to proper way to construct the dictionary and then cursor through it.  Consider the following record where the prefix direction is mismatched:



I have come up with 4 different MisMatchTypes and would like to update the MisMatch to the appropriate value.  All the records are in this format:  HouseNumber, PreFix, Name, Type/SufDir.  

The Match Values are pretty simple:

1 = HouseNumber Mismatch  (probably won't have any of these...)

2 = PreDir Mismatch               (very common)

3 = Name Mismatch                (misspellings on either the input or the matched data source)

4 = SufDir/Type Mismatch.      (very common)


In the example above, it is a type 2 mismatch


With the two fields having a standard format, they are  easy to parse out (see A better way to parse an address? ) to compare the various elements.  It's the cursor/dictionary piece that I need some help with.





Richard Fairhurst

Dan Patterson

Darren Wiens

Joshua Bixby