Skip to content
Bamboo Weekly
13 min read multiple-files multi-index grouping

Bamboo Weekly #9: US house prices (solutions)

Get better at: multiple files, multi-indexes, and grouping

Share
Share Share Share Share Share Email

This week’s topic: US house prices

This week’s data comes from FRED, the amazing financial portal from the St. Louis Federal Reserve. I actually asked you to download six separate CSV files:

Our questions for this week are:

  1. Create a data frame with information from all six locations. The index should contain both the date of the price (as a date), and the two-letter location code.
  2. On which date, and in which region, were housing prices historically the highest?
  3. How long ago was that?
  4. In which region are the housing prices currently the highest?
  5. What are the mean and median prices for each location in our data set?
  6. What are the mean prices in New York, across the entire data set?
  7. Between 2000 and 2020, what were the average prices in New York?
  8. Between 2000 and 2020, what were the average prices in each of New York and California?
  9. Since January 2020, how much have home prices changed in each of the six regions?

The learning goals for this week include combining CSV files into a single data frame, working with time series, and extracting from multi-indexes.

Let’s dive in!

Create a data frame with information from all six locations. The index should contain both the date of the price (as a date), and the two-letter location code.

Before doing anything else, I’ll start with my standard three-line intro for anything Pandas-related:

import numpy as np
import pandas as pd
from pandas import Series, DataFrame

Most of the time, I create a data frame from a CSV or Excel file. Sometimes, however, I have to create my data frame by stitching together several CSV files. That’s exactly what I asked you to do this week, and in some ways, that was one of the harder tasks I asked you to accomplish.

(Actually, I think that a number of the things I asked for this week were a bit hard. Sorry about that!)

We’ll start from the end, namely that Pandas provides a “pd.concat” function. It takes a list of data frames, and returns a single data frame based on them. When I use this, I typically create a list called “all_dfs”, append the data frames to it, and then pass all_dfs to pd.concat:

df = pd.concat(all_dfs)

The data frame that is returned, which I call “df”, then contains all of the data from the input data frames.

How, though, will I create data frames from all six of the downloaded CSV files? I decided to use one of my favorite modules in Python, “glob”, whose “glob” function (yes, “glob.glob”) takes a string describing a pattern of filenames. It returns a list of filenames matching that pattern.

In this case, I put all of the files in my “Downloads” directory, which means that I was able to get all of the filenames with:

glob.glob('/Users/reuven/Downloads/??STHPI.csv')

That’s great… but what do I do now?

In theory, I could just concatenate all of the files together. They all look the same, with the same two columns, “date” and “prices.” However, I asked that you add a third column to each file, namely the two-letter abbreviation for the state whose data we’re reading. In other words: While we’re reading in six two-column CSV files, I would like to build a single, three-column data frame from them.

The best way that I can think of to do this is with a regular ol’ “for” loop. And yes, I often say that you shouldn’t use for loops with Pandas — but here, I’m using the loop to create a data frame, not to iterate over one.

For each filename we get from glob.glob, we’ll create a data frame, and then add a new column whose value is the two-letter code for the current state. Where will I get the letters from? From the filename!

Since all of the filenames have the same structure, I can grab them using a slice. I decided to use negative indexes (i.e., counting from the right) for my slice, ending up with one_filename[-11:-9]. In other words, I want to get two characters, starting at index -11 (i.e., 11 from the right) up to and not including index -9 (i.e., 9 from the right).

Here’s the code:

import glob

all_dfs = []

for one_filename in glob.glob('/Users/reuven/Downloads/??STHPI.csv'):
    one_df = pd.read_csv(one_filename, names=['date', 'price'], 
                         header=0,
                        parse_dates=['date'])
    one_df['location'] = one_filename[-11:-9]
    all_dfs.append(one_df)

While reading each CSV file, I also leaned on several additional options in read_csv:

After creating the data frame, I create a new column, “location”, whose value comes from the filename. Assuming that all filenames really stick to this format, we should be fine; this ensures that I’ll know from which data frame (and city) each of the rows came from.

After going through all six files, all_dfs contains a list of six data frames. We can then stitch them together:

df = pd.concat(all_dfs).set_index(['date', 'location']).sort_index()

I start off by invoking pd.concat, as described above. That returns a data frame. But before I return the data frame to the caller, I invoke “set_index” on it. By passing a list of strings, I indicate that our data frame should have a multi-index, with more than one component. The primary index component will be the “date” column, and the secondary will be the “location” column.

While I could have returned the result of “set_index” to the caller, I decided to do one more thing, namely sort the data frame by its index. There are a number of operations, including slices, that work best if the data frame is sorted by the index, so I decided to get it done right away.

We’ve read our data into a number of data frames, set the correct types, set the correct index. I think we’re ready to answer this week’s questions!

On which date, and in which region, were housing prices historically the highest?

If I want to find the highest price in our “prices” column, I can simply run the “max” method on df['prices']. But I didn’t ask for the highest price; I asked for the date and region of the highest price, which means the index associated with that price.

I could thus compare df[‘prices’].max() with every element in df[‘prices’], and then use the resulting boolean series to get the matching row — including its index — using df.loc:

df.loc[df['price'] == df['price'].max()]

But Pandas has a method that does this for us, namely df.idxmax, which returns the index associated with the highest value:

df['price'].idxmax()

This not only cuts down on the code I have to write, but also dramatically speeds up the execution. (I checked both methods with %timeit in Jupyter, and it was 4x faster to use idxmax.)

In any event, I see that the highest price was in New York, in the quarter starting July 1st, 2022. We’ve had two quarters since then, which means that prices have gone down in the last nine months.

How long ago was that?

In the last question, we learned that the highest recorded average housing prices were in New York, in the third quarter of 2022. How long ago was that?

When we think of time, we usually think of a particular point in time, with a specific year, month, day, along with an hour, minutes, and seconds. And that’s exactly what a “datetime” object measures. But there’s another time-related type, namely the “timedelta”, which measures the distance between any two times. A timedelta doesn’t have a specific start or end, but it does have a length, measured in days and seconds.

To answer this question, we’ll thus need to measure the time between now (whenever you run this query) and the datetime that we found in the index of the previous solution.

The previous solution gave me a tuple:

(Timestamp('2022-07-01 00:00:00'), 'NY')

We can always retrieve elements from a tuple with []. Which means that we can get that timestamp object back with a simple:

df['price'].idxmax()[0]

But how can I compare that with the current time? Pandas offers the pd.Timestamp class, letting us create a timestamp object based on a string. Normally, w can pass it a string of the form “2023-03-30 10:12:55” and it’ll know what to do. But we can also pass it the special string “now”, and it’ll give us a timestamp object reflecting the current time and date.

Which means that if I want to know how long it has been since prices were that high in New York, I can perform the following calculation:

pd.Timestamp('now') - df['price'].idxmax()[0]

The result we get back is a timedelta object. In my case, it says:

Timedelta('271 days 14:12:09.618781')

In which region are the housing prices currently the highest?

In a multi-index, we can retrieve rows by specifying all parts of the multi-index. In our case, that means passing a 2-element tuple, containing (a) a timestamp object reflecting the quarter at which the report was done and (b) a two-letter state abbreviation.

But what if I only provide a timestamp, matching the outer index? I then get a smaller data frame, one whose index consists of state abbreviations.

In other words, I can use the outer index to retrieve one group of rows from the data frame.

In this question, I asked you to find the location in which housing prices are currently the highest. This meant retrieving all of the rows associated with the latest (i.e., most recent) data. I know that the data is already sorted by index, which means that the most recent data also has the last index.

Fortunately, I can get the index for df by retrieving the “index” attribute:

df.index

This returns an object that’s very similar to a list of two-element tuples, each with a timestamp and a two-letter state code. I can get the final one of these with:

df.index[-1]

That gives me a two-element tuple. I want the timestamp, the first element, which I can get by retrieving index 0:

df.index[-1][0]

I can now retrieve that value from df:

df.loc[df.index[-1][0]]

This returns a small data frame whose index are the state codes, and whose values are the prices. I’m interested in the “price” column for this timestamp, which I can retrieve as:

df.loc[df.index[-1][0], 'price']

Since I want to get the state with the highest value, i.e., the index for the highest value. I can do that by running “idxmax”:

df.loc[df.index[-1][0], 'price'].idxmax()

From our data, we can see that real estate in New York is currently the most expensive.

What are the mean and median prices for each location in our data set?

If our data set had a simple index, one containing just location names, then it wouldn’t be hard for us to calculate the mean and median prices for each of them. We would run a “groupby” operation on the data frame, asking for the mean and median of the “price” column.

But wait a second — don’t we need to specify a column on which we want to group? Yes, unless we want to group by one level of a multi-indexed data frame. If we pass the keyword argument “level”, and specify the name of the multi-index column we want to use, then we’ll group by that.

In other words, we can say

df.groupby(level='location')['price'].agg(['mean', 'median'])

This code:

The result of this is a new data frame, one with six rows, one for each of the locations we’re looking at. The data frame has two columns, one marked “mean” and one marked “median”, allowing us to compare the two values for each location.

What are the mean prices in New York, across the entire data set?

Now I’m asking a slightly different question, namely for the mean prices in New York — and not other regions. In theory, I could just grab the intersection of “NY” and “mean” from the above question, and be done with it.

But if I don’t want to calculate for all regions, and just get those for New York, what can and should I do?

Here, I’m going to take advantage of the amazing “xs” method, which lets me select elements from a multi-indexed data frame based on values at any level. This is particularly useful when I want to use an inner part of a multi-index to retrieve values.

For example, let’s say that I want to get all of the rows with “NY” as the value at the “location” level. I can say:

df.xs('NY', level='location')

This returns a new data frame whose index represents the outer value from our data frame’s multi-index, and whose values correspond to those rows with “NY”. In some ways, we’ve turned our multi-index inside out, choosing from the inner index column rather than the outer one.

Since I wanted to calculate the mean prices from New York, I got a final answer with:

df.xs('NY', level='location')['price'].mean()

Between 2000 and 2020, what were the average prices in New York?

This question is similar to the previous one, except that we’re looking for values where the year was between 2000 and 2020. We’ll thus need to specify both the outer values in the multi-index (being in a range) and also the inner ones (matching “NY”).

Here, I again used “xs”, although I’ll admit that I felt like I was pushing its limits a bit. Before, I passed “location” as the argument to the “level” keyword, telling “xs” that I was only interested in that one part. Now I’m interested in both; how can I specify that?

A general rule of thumb in Pandas is that if wherever you can pass one column name as a string, you can pass multiple column names in a list of strings. So I can pass more than one value for “level” in a list of strings, naming both “date” and “location”, the two parts of my multi-index.

But how can I specify a number of years, as well as a location? I would normally want to use Python’s slice syntax, but that didn’t seem to work for me.

Instead, I called the “slice” builtin, which returns a slice object. That was the first element of a 2-element tuple, indicating that I wanted a bunch of years (from 2000 to 2020) and elements from New York:

df.xs((slice('2000', '2020'), 'NY'),
      level=['date', 'location'])['price'].mean()

Notice that my slice isn’t between the integers 2000 and 2020, but rather between the strings 2000 and 2020. That’s because I have a datetime (outer) index, and those comparisons are done as strings, not as integers.

The above query asks for all rows where the date is between 2000 and 2020, and where the location is NY. We get those rows, retrieve the “price” column, and then calculate the mean, about 570.

Between 2000 and 2020, what were the average prices in each of New York and California?

This question builds on the previous one, and I decided to answer it in a different way. Rather than use “xs”, I use an “IndexSlice” object, which lets me specify all sorts of rules for my index. I first have to import it:

from pandas import IndexSlice as idx

with that in hand, I can now create the IndexSlice. I basically specify what value or values I want for each level in the index:

dx['2000':'2020', ['CA','NY']]

Then I put that in a call to “.loc”, retrieving all columns:

df.loc[idx['2000':'2020', ['CA','NY']], :]

Finally, I can run a “groupby” specifying “location”, and calculate the mean:

df.loc[idx['2000':'2020', ['CA','NY']], :].groupby('location').mean()

Since January 2020, how much have home prices changed in each of the six regions?

My interest in the subject of housing prices for this week stemmed from reports that they have gone up a great deal since the covid-19 pandemic started, about three years ago. Have they gone up? And if so, by how much? And have the increases been the same in every region?

In order to answer this, I first need to grab the rows starting in 2000. The fact that our index is based on sorted datetime values makes this easier, because we can just ask for a slice starting with the string “2000”. I’m only interested in the “price” column, so we’ll add that, too:

df.loc['2020':, 'price']

If I were to run “pct_change” on our data frame, then it would give us the wrong answer, because it would calculate the change from one row the next. We don’t want that; we want from one CO row to the next CO row, and one HI row to the next HI row.

We’ll thus use a special grouping version of “pct_change”:

df.loc['2020':, 'price'].groupby(level='location').pct_change()

This returns the percentage change for each of the individual levels. Unlike most aggregation functions, this gives us back a lot of values, not just one per “location” value. But of course, that’s because “pct_change” is all about calculating the difference between rows, not giving a single answer.

Ah, but wait — by default, “pct_change” uses a period of 1 row for its comparisons. So the change we see is compared with the previous row. We can ask it to look 2 rows back by saying “period=2”, and 3 rows back with “period=3”. What if we ask it to go all the way back?

We have 12 quarters in our data set, so by setting “period=11”, we should be able to see the difference in mean prices between the latest values and the current ones:

df.loc['2020':, 'price'].groupby(level='location').pct_change(periods=11)

This gives us a lot of NaN values, but the final six rows show us how much prices have increased, in total, from the start of 2020. I grabbed those final six rows, and sorted their values:

df.loc['2020':, 'price'].groupby(level='location').pct_change(periods=11).tail(6).sort_values()

I find that prices in Michigan have risen by about 28 percent (the lowest), but prices in Florida have risen by a whopping 58 percent. Wow! You could definitely argue that some of these differences are due to inflation, but that’s still a big rise in housing prices.

And there we go! I hope that this exploration of housing prices, along with multi-indexes in Pandas and some of the ways we can navigate them, was interesting.

Did I make any mistakes? (I’m sure I did!) Any topics you want me to cover or discuss? Data sets that I should take a look at? Just reply to this message. Or if you’re a paid subscriber (thank you!), then let’s talk about it in the message area.

Meanwhile, here’s my Jupyter notebook: https://drive.google.com/file/d/1h6FWphejDZxyyRrVkicFe5dspjeTt7Av/view?usp=drive_link

I’ll be back on Wednesday with a new Pandas puzzle related to the news.

Until then,

Reuven

Share Share Share Share Share Email

Read next

Bamboo Weekly #9: US house prices

Bamboo Weekly #10: Oil prices