One of the analyses I’d like to run on the NSW property sales data is a repeat-sales price index. To compare a property against itself I need to know which sales are of the same property. Happily every property has a stable ID for exactly this. Except in the 90s, where about 725,000 sales (a third of everything from that decade) have no property_id. They carry an older valuation number instead, but the index needs a stable property key, and I’d rather not throw those away, so I’ve been trying to recover the missing IDs by matching on address. I want to measure whether that matching works.
Why not just use the valuation number?
Every one of those ID-less sales does carry an identifier: an older valuation number from the Register of Land Values (a code like 147010000000), the key the system used before property_id. All 724,628 of them have one. So the obvious move is to use that, and for part of the job I do.
It works within the 90s. Two sales of the same property share a valuation number, so it pairs them straight away. The catch is the era boundary: the current-era files, from 2001 on, dropped the valuation number and record only property_id. A property sold in 1996 and again in 2018 has a valuation number on the old sale and a property_id on the new one, with nothing in common to join on. To use cross-era resales I need a modern property_id on the old rows.
Approach
The plan is to give each ID-less sale the best key it can, most trustworthy first. Keep the real ID where it exists. Failing that, borrow one from another sale that carries the same valuation number. Otherwise match on address to a sale that does have one. Failing that, fall back to a synthetic within-90s key from the old valuation number. And if nothing works, flag it unrecoverable rather than invent something. Address matching is a bit fiddly. The two eras abbreviate street types differently (“AV” vs “AVE”, “CR” vs “CRES”), so everything has to be normalised to a common form before anything lines up.
Checking it works
An address match is really a prediction (this ID-less sale is the same property as that one), so I can check it the way I’d check any classifier. I took 100,000 sales that already have an ID, hid it, ran the matcher, and asked how often it recovered the ID I’d hidden. Repeating that across a range of thresholds, how much evidence I require before accepting a match, traces out the trade-off between precision (getting the hidden ID right) and recovery (how many sales get matched at all):
Precision stays high throughout; recovery is what falls away as I demand more evidence. I’ve settled on four supporting records for now (the ringed point), which gives 99.0% precision and recovers about a fifth of the held-out sales. Dropping to one would recover three times as many, but at 96.5%, which feels like too many wrong matches for the repeat-sales index.
Validation also killed one other idea: bridging the old valuation number to a property_id (find the same number on a row that does have an ID, then borrow it). It validated at ~100% precision but recovered only ~3,700 rows: only about 24,000 of the ~380,000 distinct valuation numbers ever land on an ID-bearing row, so there is usually nothing to bridge to.
Where it landed
About 105,000 sales recover a real ID, another ~257,000 get a synthetic key good for pairing within the 90s, and ~362,000 stay flagged as unrecoverable. The unrecoverable ones split about evenly: ~180,000 have an incomplete address (no house number, street or suburb to match on), and ~175,000 carry a valuation number that points at more than one address, so it can’t be trusted as a key. “Unrecoverable” means flagged, not dropped: the rows stay in the table, set aside from pairing but there for any analysis that doesn’t need a stable key.