Predicting Home Prices from Realty Descriptions

My wife and I recently bought a lovely house in Sunnyvale, CA. The overall process went very smoothly (thanks to our realtor Patrick Johnson) which gave me some spare time to do a little data mining on the property market in Silicon Valley. There are so many interesting applications for analytics in the real estate domain that I will probably write another couple of articles on the subject, but for now I'll focus on a fun data visualization derived from text-mining home descriptions.

I will also add that I had the pleasure of meeting some of the team at Trulia.com earlier this year and they are the ones to watch when it comes to real-estate analytics. They've got great people, huge amounts of data and a robust business model. It will be exciting to see what they bring to market over the next year.

So, back to the data mining. If you have ever read an MLS listing describing a house for sale you will be familiar with language like this:

Classic streamlined California home w/ abundant built-ins, rich hardwood flooring & tall picture windows to the exterior gardens. Large Living Room w/ built in shelving & fireplace. Updated Kitchen w/ granite & stainless steel. Formal Dining w/ sliding door to dining deck & grounds. Fresh air sunroom w/ doors to pool and gardens. Fourishing grounds w/ level lawns. Close to Stanford, dining and shopping.

That certainly sounds attractive...it almost makes me want to pay over the asking price! In fact, that is exactly what I wanted to explore: does the language used in the home description have any statistical relationship with whether or not the home sells for above or below asking price?

The graphic below lets us explore this question in detail. It was produced by text mining the descriptions of 1300 homes that sold in Silicon Valley in 2011 and then computing the average sale price (as a percentage above or below the initial asking price) of each home containing that word. So for example, homes with the word "midtown" in their description sold for well above their asking price (about 4% on average). Words such as "gorgeous" and "bonus" did not fare so well; you may want to underbid on houses with those terms in their description!

Tip: You can hover over a circle with your mouse to see a pop-up with some sample text from the MLS listings.

Update November 29, 2011: Added a full description of how the graphic was created at the end of the article.

Here's how I created the visualization:

Download a CSV file from Redfin.com containing data on 1300 homes that sold in Menlo Park, Palo Alto or Mountain View in 2011 with a sale price between $600k and $2M.
The CSV file contains a URL for each house; the URL links to a web page with its MLS description along with its initial and final selling price. Unfortunately you can only see this page if you are logged in to your Redfin account. That means you can't just use Python's urrllib module to webscrape the MLS text; you need to use some cookie handling code to let Python access the Redfin website. The script 1_compile_data.py below shows how this works. I exported my cookie file for use in the Python script using the Export Cookies add-on for Firefox.
The output of 1_compile_data.py is a new CSV file containing cleaned data on each house, along with the text of its realty description. This is then parsed with the script 2_create_visualization.py that uses Matplotlib to create a PNG of the visualization. The script also creates the HTML divs that act as triggers for the tooltips over each word.

And here are the two Python scripts discussed above.

1_compile_data.py

# Import modules
# ==============

# Standard library modules (tested on Python 2.6 under Windows 7)
import csv
import cookielib
import urllib2
import re

# Beautifulsoup for webscraping (http://www.crummy.com/software/BeautifulSoup/)
import BeautifulSoup

# Define some constants
# =====================

# Inputs:
# - A CSV file downloaded from Redfin
# - Cookie file exported from Firefox allowing login to Redfin
#
# Outputs:
# - A CSV file containing coded features about a property plus the html
#   description from the Redfin page

REDFIN_CSV_FILE = '../data/redfin_data.csv'
COOKIE_FILE = '../data/cookies.txt'
OUTPUT_CSV_FILE = '../data/features.csv'

# Main script starts here
# =======================

# Set up cookies so that we can access Redfin pages through urrllib2
cj = cookielib.MozillaCookieJar()
cj.load(COOKIE_FILE)
opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
urllib2.install_opener(opener)

# Create input and output CSV files
input_csv = csv.reader(open(REDFIN_CSV_FILE, 'rb'))
output_csv = csv.writer(open(OUTPUT_CSV_FILE, 'wb'))
header = ['sq_ft',
          'lot_size',
          'beds',
          'baths',
          'yr_built',
          'original_listprice',
          'parking_spots',
          'url',
          'sale_price',
          'description']
output_csv.writerow(header)

# Loop over rows in input CSV (downloaded from Redfin) that contain
# data on each home.
input_csv.next()
for row in input_csv:

    # Select some potentially useful variables from the CSV
    # that we will want to include in our output file
    sq_ft = row[10]
    lot_size = row[11]
    beds = row[7]
    baths = row[8]
    yr_built = row[12]
    original_listprice = row[20]
    parking_spots = row[13]
    url = row[23]

    # The url field contains a link to the home's MLS page with
    # realtor description and sale history
    req = urllib2.Request(url)
    handle = urllib2.urlopen(req)

    # Use Beautiful soup to scrape the text of the property
    # description and also the final sale price
    html = handle.read()
    soup = BeautifulSoup.BeautifulSoup(html)
    try:
        description = soup.find('div', id='property_description').text
    except AttributeError:
        description = ''
    try:
        sale_price = soup.find('div', attrs={'class': 'price'}).text
        sale_price = re.sub('[^0-9]', '', sale_price)
    except AttributeError:
        sale_price = ''

    # Create row of final output data
    data = [sq_ft,
            lot_size,
            beds,
            baths,
            yr_built,
            original_listprice,
            parking_spots,
            url,
            sale_price,
            description]
    print url, description
    output_csv.writerow(data)

2_create_visualization.py

# Import modules
# ==============

# Standard library modules (tested on Python 2.6 under Windows 7)
import random
import csv
import re
import math
import sys

# textmining package from http://christianpeccei.com/projects/textmining/
import textmining

# matplotlib from http://matplotlib.sourceforge.net/
import matplotlib.pyplot as plt
import matplotlib.patches

# Define some constants
# =====================

FEATURES_CSV_FILE = '../data/features.csv'
CONCORDANCE_WIDTH = 8
OUTPUT_PNG_FILE = '../output/visualization.png'
OUTPUT_HTML_FILE = '../output/visualization.html'

# Function definitions
# ====================

def circle_intersect(c1, c2):
    # Tests if two circles intersect. Used to Avoid overlapping
    # word bubbles in the visualization. A circle is defined by a
    # tuple of the form (x_coordinate, y_coordinate, radius)
    d = ((c1[0] - c2[0])**2 + (c1[1] - c2[1])**2)**0.5
    r = c1[2] + c2[2]
    return d < r

# Main script starts here
# =======================

# First step is to read in the CSV file produced by the script
# 1_compile_data.py. We will build up a dictionary called word_data
# that contains all the  information for each word needed to create
# the final visualization.
word_data = {}
input_csv = csv.reader(open(FEATURES_CSV_FILE, 'rb'))
# Skip header line
input_csv.next()
for row in input_csv:
    (sq_ft,
     lot_size,
     beds,
     baths,
     yr_built,
     original_listprice,
     parking_spots,
     url,
     sale_price,
     description) = row
    # Compute the variable 'pct', the percentage over the original list
    # price at which a house eventually sold. This will be the y-axis
    # of the final plot.
    # Sometimes the sale price or original list price will be missing
    # in the data so we use a try statement to skip over these records.
    try:
        pct = (float(sale_price) / float(original_listprice) - 1) * 100
    except ValueError:
        continue
    # A data cleaning step: if the percentage increase/decrease is very
    # large or small there is probably something strange going on. For
    # example, the home sale might not have been an "at arm's length"
    # transaction (e.g. a parent sold to their child for a big discount).
    # We'll skip these records.
    if abs(pct) > 20:
        continue
    # Use the text from the home description to create concordances
    # for each word. A concordance is a snippet of text surrrounding
    # a given word that provides the user with some context. It will
    # appear in the tooltips in the final visualization.
    #
    # Strip any HTML tags from the home description
    description = ' ' + re.sub('<.*?>', ' ', description) + ' '
    # Split the text into alternating tokens of non-word, word, non-word, etc
    tokens = re.split('([^A-Za-z]+)', description)
    # Build the concordances for each word. Note that we are goign to add
    # some HTML tags to the concordances so they will display nicely
    # in the tooltips.
    words = set()
    for i in range(2, len(tokens) - 2, 2):
        word = tokens[i].lower()
        words.add(word)
        i_start = max(i - CONCORDANCE_WIDTH, 0)
        i_end = i + CONCORDANCE_WIDTH
        # We will wrap the focus word of this concordance in a highlight
        # tag so we can make it stand out in the tooltip.
        concordance = '<p>... ' + ''.join(tokens[i_start:i] +
                              ["<span class='hilite'>" + tokens[i] +
                               '</span>'] + tokens[i + 1:i_end]) + ' ...</p>'
        if word not in word_data:
            word_data[word] = {'concordances': [], 'pct': []}
        word_data[word]['concordances'].append(concordance)
    # Now that we know all the words that appeard in this description,
    # we store the pct value in a list for each of the words. In the next
    # step we will compute the mean pct for each word.
    for word in words:
        word_data[word]['pct'].append(pct)

# Compute the mean pct increase/decrease for all homes with
# a given word in their description. Also compute number of home
# descriptions containing that word.
for word in word_data:
    pct = word_data[word]['pct']
    word_data[word]['n'] = len(pct)
    word_data[word]['mean_pct'] = sum(pct) / len(pct)

# To eliminate "noise" words we will filter the list of words in three
# ways.
# 1) Remove any words that occur in less than 50 home descriptions
# 2) Remove any common stopwords such as "and", "the", etc
# 3) Remove words that have a very high or low average pct. This
#    ensures all the word bubbles will fit nicely on the y-axis of the plot.
for word in word_data.keys():
    if word_data[word]['n'] < 50 or word in textmining.stopwords \
            or abs(word_data[word]['mean_pct']) > 4:
        del word_data[word]

# We will also scale the pct for each word to lie in the range 0 to 1.
# This will be used for indexing into a colormap to color the word
# bubbles.
min_mean_pct = min(word_data[word]['mean_pct'] for word in word_data)
max_mean_pct = max(word_data[word]['mean_pct'] for word in word_data)
for word in word_data:
    word_data[word]['mean_pct_01'] = (word_data[word]['mean_pct'] -
    min_mean_pct) / (max_mean_pct - min_mean_pct)

# Run layout algorithm to arrange circles
# =======================================

# Assign an initial x and y coordinate for each word bubble.
for word in word_data:
    x = 0
    y = word_data[word]['mean_pct']
    # Set radius of the circle
    r = 0.3
    word_data[word]['circle'] = [x, y, r]

# The first step in the layout algorithm is to create a list
# of words sorted in descending order by their y-coordinate
words = sorted(word_data, key = lambda x: word_data[x]['circle'][1],
               reverse=True)

# Loop over the words and adjust their x-coordinates so there are
# no overlaps. We will skip the first word (hence i starts at 1, not 0).
for i in range(1, len(words)):
    x = 0
    valid_circles = []
    while 1:
        # Find first available circle position to left
        test_circle = word_data[words[i]]['circle'][:]
        test_circle[0] = -x
        intersect_left_any_above = False
        for j in range(i):
            if circle_intersect(test_circle, word_data[words[j]]['circle']):
                intersect_left_any_above = True
        if not intersect_left_any_above:
            valid_circles.append(test_circle)
        # Find first available circle position to right
        test_circle = word_data[words[i]]['circle'][:]
        test_circle[0] = x
        intersect_right_any_above = False
        for j in range(i):
            if circle_intersect(test_circle, word_data[words[j]]['circle']):
                intersect_right_any_above = True
        if not intersect_right_any_above:
            valid_circles.append(test_circle)
        # Exit the loop if we have found a valid circle position
        # on either the right or left
        if valid_circles:
            break
        # If no valid circle, increase the x offset and try again
        x += .1
    # Pick randomly between the right or left circle position and use
    # this as the placement for the word bubble
    random.shuffle(valid_circles)
    word_data[words[i]]['circle'] = valid_circles[0]

# Throw out any words which have an x-coordinate greater than +- 2.5.
# This step is to prevent creating a very wide graphic, since there
# are a lot of words and if we plotted them all the final
# visualization would be much wider than it was tall. 
for word in word_data.keys():
    if abs(word_data[word]['circle'][0]) > 2.5:
        del word_data[word]

# Use Matplotlib to draw the visualization
# ========================================

# Set up figure area and axis
plt.figure(figsize=(7.77, 10), dpi=100)
plt.axis([-3, 4, -4.5, 4.5])
plt.subplots_adjust(left=0, right=1, top=1, bottom=0)
plt.gca().set_axis_off()

# Add horizontal lines with labels showing significant price deltas
plt.hold(True)
plt.plot((-3, 4), (-2, -2), 'k', zorder=-1)
plt.plot((-3, 4), (0, 0), 'k', zorder=-1)
plt.plot((-3, 4), (2, 2), 'k', zorder=-1)
plt.text(2.8, -1.9, 'Sold 2% below asking', fontsize=8)
plt.text(2.8, .1, 'Sold at asking', fontsize=8)
plt.text(2.8, 2.1, 'Sold 2% above asking', fontsize=8)

# Choose a colormap for the bubbles.
# You can try changing this to cm.Blues or any other colormap
# to get a different effect
cmap = plt.cm.jet

# Open the html file that will contain the tooltip information.
# Note that the tooltips are generated to be compatible with
# the jQuery Tools library. See http://flowplayer.org/tools/tooltip/index.html
f_html = open(OUTPUT_HTML_FILE, 'w')

# Loop over the words to create the final visualization
for word in word_data:
    # Draw the bubble for the word
    x, y, r = word_data[word]['circle']
    c = word_data[word]['mean_pct_01']
    plt.gca().add_patch(matplotlib.patches.Circle((x, y), r,
        facecolor=cmap((c - .5) * .6 + .5),
        edgecolor='none'))
    # Add the word itself as some text inside the bubble
    plt.text(x, y, word, fontsize=8,
             horizontalalignment='center',
             verticalalignment='center')
    # We want to add a tooltip for each bubble so we will create a div
    # in our HTML that covers the face of the bubble. Divs are rectangular
    # but the bubbles are round, so the div won't cover the bubble completely.
    # The coordinates of the div in the HTML are in pixels but we currently
    # have the coordinates of the bubble in the coordinate system of
    # the Matplotlib axis. We use the transform method to convert figure
    # coordinates into pixels.
    px, py = plt.gca().transData.transform((x, y))
    # The half_width of the div rectangle is hard-coded to correspond
    # to the radius of 0.3 defined earlier. I know, this should be
    # computed dynamically.
    half_width = 25
    top = 1000 - py - half_width
    left = px - half_width
    # We will also include a random selection of 8 concordances
    # for this word in the tooltip for the bubble. We shuffle the
    # concordances so that we get a mix of word usage from across
    # all houses in the dataset.
    random.shuffle(word_data[word]['concordances'])
    concordances = '\n'.join(word_data[word]['concordances'][:8])
    f_html.write("""<div class="tooltip_div"
style="position:absolute; top:%dpx; left:%dpx;
width:%dpx; height:%dpx;"></div>
<div class="tooltip">
%s
</div>

""" % (top, left, half_width * 2, half_width * 2, concordances))

# Close HTML file
f_html.close()

# Save figure to PNG file
plt.savefig(OUTPUT_PNG_FILE, dpi=100)