I’ve been working with the NSW property data, parsing millions of records out of a stack of zip files. It would be nice to be able to check if I’m silently dropping or doubling rows anywhere. While working through the .DAT files I realised they have a useful feature: each archive ends with a trailer record stating how many sale records it should contain. The file ends in a Z record giving the total record count and the number of B (sale) records. So for each year I can compare the rows I pulled out against the rows the file says it has. If they disagree, either my parser is wrong or the source is, and I can figure out which. So I started checking my extraction against it:
Missing rows
This turned out to be a useful check. It caught a bug of mine: ~21,600 sales from early 2001 were falling through the gap between the two archive formats. The 2001 zip contains an old-format file that my historic reader (which stopped at 2000) never opened, and my current-era reader deliberately skipped. Neither branch read it, so those sales just weren’t there, and any 2001 number was quietly wrong. The check against the trailer made the hole obvious.
It also turned up an error in the source. In the plot the bars are what I extracted and the diamonds are each file’s claim. They line up everywhere but 1994 (the red bar). Its trailer says 105,332 sales, but the file actually has 148,516, and I extracted all of them. That 105,332 number is 1990’s sale count, exactly. Maybe someone copy-pasted the wrong year’s total into the trailer? I double-checked and the extraction is right. Handy to know before I spend time fixing code when it’s a data bug.
Note: the plot stops at 2001 because that’s where the old archive format ends, but current-era files (2001 on) carry the same Z trailers. I checked those too. They reconcile except for a few files from 2004 and 2006, which come up 63 sales short of the trailer counts. I dug into those years but the rows aren’t anywhere in the files, so there’s nothing to recover.
Duplicate rows
Trailer counts catch rows going missing. The opposite problem is the source repeating itself. The data is published as weekly extracts, and it turns out to be common for each one to re-list recent sales. So the same transaction shows up in many consecutive weekly files: 149,048 current-era sales appear more than once. The copies are almost always identical. Across those repeated sales the land area differs in 3.1%, zoning in 2.2%, nature in 0.7%, primary purpose in 1.1% and the dealing number in 3.9%; the record counter changes in 96%, but it should: the spec labels that field “unique for file”, so it’s bookkeeping rather than data. I’ve made it so that the extraction keeps the most recent copy of each and drops the rest.
Next up will be patching a hole. A third of the 1990s sales have no property identifier at all.