Using Data to Evaluate Home Listings

Buying a house is intimidating. Understanding my local housing market proved to be more challenging than expected because online articles and resources were disappointingly generic. Beyond some basic questions like “how much house can I afford?” I wasn’t sure how to evaluate whether the price for a listing was a good deal. So, I did my own analysis of housing data.

Step 1: Google

To start, I found that Trulia and Zillow have a portion of their sites devoted to real estate trends. Trulia had a sexy-yet-mysterious map while Zillow had a teasing assortment of graphs. I don’t really care about anything besides 2–4 bedroom single family homes, so looking at aggregate data for all types of housing is not helpful. I can only assume Trulia included everything in their maps and graphs since they didn’t provide that detail, and Zillow has some options for single family homes that are only available for Zillow-specific metrics. Frustrating. I wanted more information before letting myself be channeled into the sales funnel.

Step 2: Novice to GIS Pro

I was overjoyed to find that the City of Madison has an open data initiative. The Assessor Property Information data set has nearly 80 thousand listings and was updated 8/2016. This is a gold mine of information, yet unfortunately I have to leave out all the surrounding suburbs because they do not have this kind of information available as of this date.

Mapping the Madison data was my first priority. I used the program Tableau since I am familiar with it and I like the functionality. My first challenge was with the addresses—there are no zip codes! Most, if not all mapping programs need additional geographic information in order to plot points. A process called geocoding could work, however the best free, web-based option I could find only processes 1,000 rows per batch. No thank you. Going back to the data, I saw that ward is included for every listing, which seems to be what Trulia was showing in their map. Tableau only knows the shapes for zip codes and area codes, but they provided instructions on how to make custom maps using shapefiles. Again I turned to wonderful folks at the City of Madison to get the shapefiles for the city’s wards and the open source QGIS program to convert that information into something Tableau can use, following their instructions.

Step 3: Profit. Um, I Mean Results.

In order to find the “perfect” home, you first need to know where to look. 

Best Places to Live in Madison

Click to view the interactive map on Tableau Public. Larger image available here.

This map shows the best places to buy a 2–5 bedroom home in Madison based on price, square feet, elementary school rating and 1-year price change. The scoring takes the average for all of Madison (see the Methodology and Discussion section for details) and calculates the number of standard deviations each ward is from the mean, for all four metrics. This calculation takes the stance that a lower price, a larger living area, a better school and a larger 1 year price change are good things and all weighted equally. If you would like to view the details for all districts or to change the number of bedrooms and bathrooms, visit the interactive dashboard on Tableau Public by clicking on the map.

This is just a starting point for your housing search. You very well may find a great home in a ward that did not score highly, but that is a case-by-case decision. This does not directly take into account nearby amenities, crime rates, lot sizes, commuting times, necessary repairs and other important considerations.

The largest average homes (by square foot) have a darker shade. Click for full size.

The largest homes (by square foot) have a darker shade. Click for full size.

The highest rated elementary schools have a darker shade. Click for full size.

The highest rated elementary schools have a darker shade. Click for full size.

The most expensive homes have a darker shade. Click for full size.

The most expensive homes have a darker shade. Click for full size.

The homes with the highest 1 year growth rate in price have a darker shade. Click for full size.

The homes with the highest 1-year growth rate in price have a darker shade. Click for full size.

Now that you know where to look and you know your budget, it’s time to figure out if specific listings are a good deal.

Find out which ward your favorite houses are in. Compare the details to the averages for all houses in that ward. Think of this as your measuring stick: if the price is higher than average, does it seem reasonable based on the quality? I am at this step currently and will see how useful this knowledge proves to be. If you have any thoughts on additional analyses that would be helpful, please let me know in the comments! Also I would love to incorporate Middleton, Verona, Fitchburg, Monona, Sun Prairie and more in my map if anyone happens to have connections to housing data for those cities.

Methodology and Discussion

Some wards got a little mangled by the mapping process. Sorry ward 71! Exceptions to the map:

  • Wards were only included if they had at least 30 home listings. This excluded wards 114, 55, 49, 121, 47, 125, 60 and 20.
  • Results are only for listings with a property class of residential.
  • Listings with an elementary school of null, “assigned” or “to be determined” were excluded.
  • Elementary school boundaries do not follow ward boundaries, so each ward was computed as the average of up to two elementary school ratings. Also, this rating ignores parts of Madison that are also zoned for a surrounding suburb’s school district, as in the case with Middleton zoning around ward 102. Middle school and high school ratings were not used.
  • The maps are showing listings that have between 2 and 5 bedrooms, and between 1.5 and 4 bathrooms. The highest and lowest rated wards will change depending on your selection!

Some of the wards have an incredibly high standard deviation for price as shown in the table. In the case of ward 86, there were 38 houses with a current total value of above $1 million. Other wards with waterfront property are also likely to have high variance in price.

Creative Commons Attribution (BY) license

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: