I know I’m probably not supposed to pick favorites among Denverite readers, but the reader who asked this question is definitely my No. 1.
“Why does the city export permit records in an unusable format?”
She’s talking about file that Denver publishes every week that includes permits issued for new construction, roofing, demolitions and more. I have spent at least a couple hours trying to programmatically fix this file. I had not asked the city why the file is formatted in a way that’s not easy for me to use.
The problem with the file is that there are hidden rows and spaces between each entry that prevent sorting or other aggregating functions that one can use for further analysis.
Here’s why that is, according to Denver Community Planning and Development spokesperson Laura Swartz:
“The data is originally generated via Crystal Reports, which is formatted for PDF. As a result, when we export them into Excel, the formatting is adjusted to keep the visual spacing found on the Crystal Report — hence adding rows, etc. Unfortunately, I’m not sure there is a way to correct it since the data is not originally formatted for Excel.”
So there you have it. Another case of a PDF spoiling the fun of journalists and Denverite readers. (I’d still love to use Python or some sort of script to fix the Excel file. If you can do that, feel free to email me.)
And if you’re just looking to read the file for yourself, here’s a bit more on how it’s organized. The stat codes track what type of work is being done, and the legend is here. Different types of permits are possible within stat codes, such as a demolition permit or building permit.