10.6. Example: Sale Prices for Houses

In this final section, we carry out an exploratory analysis using the questions in the previous section to direct our investigations. Although EDA typically begins in the data wrangling stage, for demonstration purposes the data we work with here have already been partially cleaned so that we can focus on exploring the features of interest. Note also that we do not discuss creating the visualizations in much detail; that topic is covered in Chapter 11.

First, we consider the scope and granularity of the data.

These data were scraped from the San Francisco Chronicle (SFChron) Website1. They form a complete list of homes sold in the area from Apr 2003 to December 2008. Since we have no plans to generalize our findings beyond the time period and the location, we are working with a census so the population matches the access frame and the sample consists of the entire population.

Each record represents a sale of a home in the SF Bay Area during the specified time period. This means that if a home was sold twice during this time, then there are two records in the table. And, if a home in the Bay Area was not up for sale during this time, then it does not appear in the dataset.

The data are in the data frame sfh_df.

sfh_df
city zip street price br lsqft bsqft timestamp
0 Alameda 94501.0 1001 Post Street 689000.0 4.0 4484.0 1982.0 2004-08-29
1 Alameda 94501.0 1001 Santa Clara Avenue 880000.0 7.0 5914.0 3866.0 2005-11-06
2 Alameda 94501.0 1001 Shoreline Drive \#102 393000.0 2.0 39353.0 1360.0 2003-09-21
... ... ... ... ... ... ... ... ...
521490 Windsor 95492.0 9998 Blasi Drive 392500.0 NaN 3111.0 NaN 2008-02-17
521491 Windsor 95492.0 9999 Blasi Drive 414000.0 NaN 2915.0 NaN 2008-02-17
521492 Windsor 95492.0 999 Gemini Drive 325000.0 3.0 7841.0 1092.0 2003-09-21

521491 rows × 8 columns

The dataset does not have an accompanying codebook, but we can determine the features and their storage types by inspection.

sfh_df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 521491 entries, 0 to 521492
Data columns (total 8 columns):
 #   Column     Non-Null Count   Dtype         
---  ------     --------------   -----         
 0   city       521491 non-null  object        
 1   zip        521462 non-null  float64       
 2   street     521479 non-null  object        
 3   price      521491 non-null  float64       
 4   br         421343 non-null  float64       
 5   lsqft      435207 non-null  float64       
 6   bsqft      444465 non-null  float64       
 7   timestamp  521491 non-null  datetime64[ns]
dtypes: datetime64[ns](1), float64(5), object(2)
memory usage: 35.8+ MB

Based on the names of the fields, we expect the primary key to consist of some combination of city, zip, street address, and date.

Sale price is our focus. So let’s begin by exploring the distribution of sale price. To develop your intuition about distributions, make a guess about the shape of the distribution before you start reading the next section. Don’t worry about the range of prices, just sketch the general shape.

10.6.1. Understanding Price

It seems like a good guess is that the distribution of sale price is a highly skewed to the right with a few very expensive houses. The summary statistics shown below confirm this skewness.

percs = [0, 25, 50, 75, 100]
prices = np.percentile(sfh_df['price'], percs, interpolation='lower')
pd.DataFrame({'price': prices}, index=percs)
price
0 22000.00
25 410000.00
50 555000.00
75 744000.00
100 20000000.00

The median is closer to the lower quartile than the upper quartile. Also the maximum is 40 times the median! We might wonder whether that $20m sale price is simply an anomalous value or whether there are many houses that sold at such a high price. To find out, we can zoom in on the right tail of the distribution and compute a few high percentiles.

percs = [95, 97, 98, 99, 99.5, 99.9]
prices = np.percentile(sfh_df['price'], percs, interpolation='lower')
pd.DataFrame({'price': prices}, index=percs)
price
95.00 1295000.00
97.00 1508000.00
98.00 1707000.00
99.00 2110000.00
99.50 2600000.00
99.90 3950000.00

We see that 99.9% of the houses sold for under $4M so the $20M sale is indeed a rarity. Let’s examine the histogram of sale prices below $4M.

under_4m = sfh_df[sfh_df['price'] < 4_000_000]
px.histogram(under_4m, x='price', nbins=50,
            width=400, height=250)
../../_images/eda_example_20_0.svg

Even without the top 0.1%, the distribution remains highly skewed to the right, with a single mode around $500k. Let’s plot the histogram of the log-transformed sale price. The logarithm transformation often does a good job at converting a right-skewed distribution into one that is more symmetric.

log_prices = under_4m.assign(log_price=np.log10(under_4m['price']))
px.histogram(log_prices, x='log_price', nbins=50,
             width=400, height=250)
../../_images/eda_example_22_0.svg

We see that the distribution of log-transformed sale price is roughly symmetric. Now that we have an understanding of the distribution of sale price, let’s consider the so-what questions posed in the previous section on EDA guidelines.

10.6.2. What Next?

We have a description of the shape of sale price, but we need to consider why the shape matters and look for comparison groups where distributions might differ.

Shape matters because models and statistics based on symmetric distributions tend to have more robust and stable properties than highly skewed distributions. (We address this issue more when we cover linear models in Chapter 15). For this reason, we primarily work with the log-transformed sale price. And, we might also choose to limit our analysis to sale prices under $4m since the super-expensive houses may behave quite differently.

As for possible comparisons to make, we look to the context. The housing market rose rapidly during this time and then the bottom fell out of the market. So the distribution of sale price in, say, 2004, might be quite different than in 2008, right before the crash. To explore this notion further, we can examine the behavior of prices over time. Alternatively, we can fix time, and examine the relationships between price and the other features of interest. Both approaches are potentially worthwhile.

We narrow our focus to one year (in Chapter 11 we look at the time dimension). We reduce the data to sales made in 2004, so rising prices should have a limited impact on the distributions and relationships that we examine. To limit the influence of the very expensive and large houses, we also restrict the dataset to sales below $4m and houses smaller than 12,000 ft^2. This subset still contains large and expensive houses, but not outrageously so. Later, we further narrow our exploration to a few cities of interest.

def subset(df):
    return df.loc[(df['price'] < 4_000_000) &
                  (df['bsqft'] < 12_000) & 
                  (df['timestamp'].dt.year == 2004)]


sfh = sfh_df.pipe(subset)
sfh
city zip street price br lsqft bsqft timestamp
0 Alameda 94501.00 1001 Post Street 689000.00 4.00 4484.00 1982.00 2004-08-29
3 Alameda 94501.00 1001 Shoreline Drive \#108 485000.00 2.00 39353.00 1360.00 2004-09-05
10 Alameda 94501.00 1001 Shoreline Drive \#306 390000.00 2.00 39353.00 1360.00 2004-01-25
... ... ... ... ... ... ... ... ...
521469 Windsor 95492.00 9960 Herb Road 439000.00 3.00 9583.00 1626.00 2004-04-04
521473 Windsor 95492.00 9964 Troon Court 1200000.00 3.00 20038.00 4281.00 2004-10-31
521480 Windsor 95492.00 9980 Brooks Road 650000.00 3.00 45738.00 1200.00 2004-10-24

105996 rows × 8 columns

For these data, the shape of the distribution of sale price remains the same—price is still highly skewed to the right. We continue to work with this subset to address the question of whether there are any potentially important features to study along with price.

10.6.3. Examining other features

In addition to the sale price, which is our main focus, a few other features that might be important to our investigation are the size of the house, lot (or property) size, and number of bedrooms. We explore the distributions of these features and their relationship to sale price and to each other.

Since the size of the property is likely related to its price, it seems reasonable to guess that these features are also skewed to the right. The figure below shows the distribution of building size on the left and the log-transformed distribution on the right.

sfh = sfh.assign(log_bsqft=np.log10(sfh['bsqft']))

left = px.histogram(sfh, x='bsqft', histnorm='percent', nbins=60)
right = px.histogram(sfh, x='log_bsqft', histnorm='percent', nbins=60)
fig = left_right(left, right)
../../_images/eda_example_33_0.svg

The distribution is unimodal with a peak at about 1500 ft^2, and many houses are over 2,500 ft^2 in size. We have confirmed our intuition: the log-transformed building size is nearly symmetric, although it maintains a slight skew. The same is the case for the distribution of lot size.

Given both house and lot size have skewed distributions, a scatter plot of the two should most likely be on log scale too. We compare the scatter plot with and without the log-transformation below.

sfh = sfh.assign(log_lsqft=np.log10(sfh['lsqft']))

../../_images/scatterPlot_price_bsqft.jpg

The scatter plot on the left is in the original units, which makes it difficult to discern the relationship because most of the points are crowded into the bottom of the plotting region. On the other hand, the scatter plot on the right reveals a few interesting features: there is a horizontal line along the bottom of the scatter plot where it appears that many houses have the same lot size no matter the building size; and there appears to be a slight positive log-log linear association between lot and building size.

Let’s look at some lower quantiles of lot size to try and figure out this unusual value:

percs = [0.5, 1, 1.5, 2, 2.5, 3]
lots = np.percentile(sfh['lsqft'].dropna(), percs, interpolation='lower')
pd.DataFrame({'lot_size': lots}, index=percs)
lot_size
0.50 436.00
1.00 436.00
1.50 436.00
2.00 436.00
2.50 436.00
3.00 782.00

We found something interesting! About 2.5% of the houses have a lot size of 436 ft^2. This is tiny and makes little sense so we make a note of the anomaly for further investigation.

Another measure of house size is the number of bedrooms. Since this is a discrete quantitative variable, we can treat it as a qualitative feature and make a bar plot.

Houses in the Bay Area tend to be on the smaller side so we venture to guess that the distribution will have a peak at three and skewed to the right with a few houses having 5 or 6 bedrooms.

br_cat = sfh.groupby(by=["br"]).size().reset_index(name="counts")
px.bar(br_cat, x="br", y="counts", width=350, height=250)
../../_images/eda_example_41_0.svg

The bar plot confirms that we generally had the right idea. However, we find that there are some houses with over 30 bedrooms! That’s a bit hard to believe and points to another possible data quality problem. Since the records include the addresses of the houses, we can double check theses values on a real estate app.

In the meantime, let’s just transform the number of bedrooms into an ordinal feature by reassigning all values larger than 8 to 8+. Then we recreate the bar plot with the transformed data.

eight_up = sfh.loc[sfh['br'] >= 8, 'br'].unique()
sfh['new_br'] = sfh['br'].replace(eight_up, 8)

br_cat = sfh.groupby(by='new_br').size().reset_index(name="counts")
px.bar(br_cat, x="new_br", y="counts", width=350, height=250)
../../_images/eda_example_43_0.svg

We can see that even lumping all of the houses with 8+ bedrooms together, they do not amount to many. The distribution is nearly symmetric with a peak at 3, nearly the same proportion of houses have 2 or 4 bedrooms, and nearly the same have 1 or 5. There is asymmetry present with a few houses having 6 or more bedrooms.

Now, we examine the relationship between the number of bedrooms and sale price. Before we proceed, we save the transformations done thus far.

def log_vals(df):
    return df.assign(log_price=np.log10(df['price']),
                     log_bsqft=np.log10(df['bsqft']),
                     log_lsqft=np.log10(df['lsqft']))

def clip_br(df):
    eight_up = df.loc[df['br'] >= 8, 'br'].unique()
    new_br = df['br'].replace(eight_up, 8)
    return df.assign(new_br=new_br)

sfh = (sfh_df
 .pipe(subset)
 .pipe(log_vals)
 .pipe(clip_br)
)

Now we’re ready to consider relationships between the number of bedrooms and other variables.

10.6.4. Delving Deeper into Relationships

Let’s begin by examining how the distribution of price changes for houses with different numbers of bedrooms. We can do this with box plots.

px.box(sfh, x='new_br', y='price', log_y=True,
      width=450, height=250)
../../_images/eda_example_49_0.svg

The median sale price increases with the number of bedrooms from 1 to 5 bedrooms, but for the largest houses (those with 6, 7, and 8+ bedrooms), the distribution of log-transformed sale price appears nearly the same.

We would expect that houses with one bedroom are smaller than houses with, say, 4 bedrooms. We might also guess that houses with 6 or more bedrooms are similar in size. To dive deeper, we consider a kind transformation that divides price by building size to give us the price per square foot. We want to check if this feature is constant for all houses; in other words, price is primarily determined by size. To do this we look at the relationship between size and price and price per square foot and size.

We create two scatter plots. The one on the left shows price against the building size (both log-transformed), and the plot on the right shows price per square foot (log-transformed) against building size. In addition, each plot has an added smooth curve that reflects the local average price or price per square foot) for buildings of roughly the same size.

sfh = sfh.assign(
    ppsf=sfh['price'] / sfh['bsqft'], 
    log_ppsf=lambda df: np.log10(df['ppsf']))

../../_images/trendPPSF.jpg

The lefthand plot shows what we expect—larger houses cost more. We also see that there is roughly a log-log association between these features.

The righthand plot in this figure is interestingly nonlinear. We see that smaller houses cost more per square foot than larger ones, and the price per square foot for larger houses (houses with many bedrooms) is relatively flat. This feature appears to be quite interesting so we save the price per square foot transforms into sfh.

def compute_ppsf(df):
    return df.assign(
    ppsf=df['price'] / df['bsqft'], 
    log_ppsf=lambda df: np.log10(df['ppsf']))

sfh = (sfh_df
 .pipe(subset)
 .pipe(log_vals)
 .pipe(clip_br)
 .pipe(compute_ppsf)
)

So far we haven’t considered the relationship between prices and location. There are house sales from over 150 different cities in this dataset. Some cities have a handful of sales and others have thousands. We continue our narrowing down of the data and examine relationships for a few cities next.

10.6.5. Fixing Location

You may have heard the expression: There are three things that matter in real estate: location, location, location. Comparing price across cities might bring additional insights to our investigation.

We examine data for some cities in the East Bay: Richmond, El Cerrito, Albany, Berkeley, Walnut Creek, Lamorinda (which is a combination of Lafayette, Moraga, and Orinda, three neighboring bedroom communities), and Piedmont.

Let’s begin by comparing the distribution of sale price for these cities.

cities = ['Richmond', 'El Cerrito', 'Albany', 'Berkeley',
          'Walnut Creek', 'Lamorinda', 'Piedmont']

px.box(sfh.query('city in @cities'),
        x='city', y='price', log_y=True,
       width=450, height=250)
../../_images/eda_example_61_0.svg

The box plots show that Lamorinda and Piedmont tend to have more expensive homes and Richmond has the least expensive, but there is overlap in sale price for many cities.

Next, we examine the relationship between price per square foot and house size more closely with a scatter plot for four of these cities.

four_cities = ['Berkeley', 'Lamorinda', 'Piedmont', 'Richmond']
fig = px.scatter(sfh.query('city in @four_cities'),
           x='bsqft', y='log_ppsf', facet_col='city', facet_col_wrap=2,
           trendline='ols', trendline_color_override="black"
           )
fig.update_layout(xaxis_range=[0,5500], 
                  yaxis_range=[1.5, 3.5], width=450, height=400)
fig.show()
../../_images/eda_example_64_0.svg

The relationship between price per square foot and building size is roughly log-linear with a negative association for each of the four locations. While, not parallel, it does appear that there is a “location” boost for houses, regardless of size, where, say, a house in Berkeley costs about $250 more per square foot than a house in Richmond. We also see that Piedmont and Lamorinda are more expensive cities, and in both cities, there is not the same reduction in price per square foot for larger houses in comparison to smaller ones. These plots support the location-location-location saying.

In EDA, we often revisit earlier plots to check whether new findings add insights to previous visualization. It is important to continually take stock of our findings and use them to guide us in further explorations. Let’s summarize our findings so far.

10.6.6. EDA discoveries

Our EDA has uncovered several interesting phenomenon. Briefly, some of the most notable are:

  • Sale price and building size are highly skewed to the right with one mode.

  • Price per square foot decreases nonlinearly with building size, with smaller houses costing more per square foot than larger houses, and the price per square foot being roughly constant for houses with three or more bedrooms.

  • More desirable locations add a bump in sale price that is roughly the same size for houses of different sizes.

There are many additional explorations we can (and should) perform, and there are several checks that we should make. These include: investigating the 436 value for lot size and crosschecking unusual houses, like the 30 bedroom house and the $20m house, with online real estate apps.

We narrowed our investigation down to one year and later to a few cities. This narrowing helped us control for features that might interfere with finding simpler relationships. For example, since the data were collected over several years, the date of sale may confound the relationship between sale price and number of bedrooms. At other times, we want to consider the effect of time on prices. To examine price changes over time we often make line plots, and we adjust for inflation. We revisit these data in Chapter 11 when we consider data scope and look more closely at trends in time.

Despite being brief, this section conveys the basic approach of EDA in action. For an extended case study on a different dataset, see Chapter 12.


1

The SFChron published weekly data on the sale of houses in the San Francisco Bay Area. This site is no longer available.