A Week of Data Entry in Under Ten Minutes

In my senior year of high school I landed an internship with a small city’s planning effort. They’d put me on a commercial real estate project that was updating and combining data to make better decisions about redesigning the downtown area. It came through a school program, but I’d already done a lot of work on business analytics and project management.

Where the project stood

Reliable, current data on these buildings didn’t exist. The assessor’s database was the main source, but finding the important metrics in it was slow. A previous project member had started a spreadsheet to consolidate things: property ID, location, previous or current tenant, type of business, owner. All of it to be filled in by hand. I was also given a list of metrics to track for each property.

What I was responsible for

First, a project outline proposal: where the project stood, the goal, and the steps to get there. I interviewed government employees, commercial real estate agents and members of the community to work out which metrics mattered most. Then I took the proposal and timeline directly to the city’s leadership and asked for approval. When another intern joined, I brought them up to speed and helped split up the work.

The plan was to locate, clean and upload the data for each property by hand from the assessor’s PDF documents.

The scheme

We spent a week doing exactly that. The repetition got to me, and so did how inefficient it was.

Then I noticed all the documents sat in an S3 bucket with a convenient URL structure, so I could find each one by parcel ID (we’d been given the IDs of the properties we cared about). I more or less ignored the manual entry and wrote a program to pull each PDF onto the machine doing the analysis.

The harder part was getting data out of PDFs that weren’t machine-readable. Data points sat in different places with slightly different labels. It was a mess. I spent the next few days on trial and error, hunting for the rough pixel coordinates of each metric. I widened the tolerance until every metric came back with the data and its label (plus a lot of fluff), then cleaned it up. Since I was writing everything in Python, regular expressions were an easy choice for keeping only the values and labels I needed. Multi-line and more complex metrics, anything that needed summing, got algorithms that looped through the data points and calculated a result before returning anything.

The raw data got appended to a CSV file automatically, with the parcel ID as the primary key. SQL would have been too technical for the people overseeing the project.

The result

Building it took about five days, which slowed everyone down. Once finished, it did a full week of manual entry in under ten minutes. (Downloading hundreds of PDFs to a dusty office PC is not blisteringly fast.)

My coworker no longer had a job to do, so we moved on to taking clear, up-to-date photos of every roadside property on the list.

A couple of years later, the results are still being used in the downtown redesign, to make sure each property isn’t hurt by reduced frontage, lot size, zoning or visibility. With that much data, decisions get made faster.

Nobody overseeing the project expected automation or internal tooling. For a short while we sat there scratching our heads because there was no more work to do. I’m proud of it.