> ## Content Index
> Fetch the complete content index at: https://www.bambooweekly.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Bamboo Weekly #9: US house prices
- URL: https://www.bambooweekly.com/bw-9-us-house-prices/
- Published: 2023-03-29T15:00:53.000Z
- Updated: 2026-08-06T10:46:07.000Z
- Description: Get better at: multiple files, multi-indexes, and grouping
- Author: Reuven M. Lerner
- Tags: multiple-files, multi-index, grouping

When I was in graduate school, I would walk my eldest to school. For the better part of a year, part of our morning ritual involved passing a house with a “for sale” sign on it. We were both puzzled by the fact that a decent-looking house in the Chicago suburbs would remain unsold for so long. But soon enough, it wasn’t alone: Other houses also sported “for sale” signs, and remained on the market for some time.

By the time we returned to Israel in July 2008, we were counting “for sale” signs wherever we went. And there were a *lot* of them. It was clear that something weird was happening with US real estate. Before too long, we started what is now called the “Great Recession,” causing untold economic damage around the world.

We didn’t know it then, but we were keeping tabs on the US housing market in our own little way. Houses remained unsold because the asking prices were too high. Prices had to go down in order for people to agree to buy — and they definitely went down, with lots of people finding themselves “underwater,” owing more than the house was worth.

Now again, during the covid-19 pandemic, we saw house prices skyrocket, for a variety of reasons, including the number of people needing home offices. The Fed, and many other central banks, has been raising interest rates for some time, in the hopes of cooling down the economy — and with it, inflation. They’re looking at housing prices as one indicator of where prices are going, and how much they need to adjust interest rates.

The economy is huge, and no one indicator captures it entirely, or even largely. But housing prices are definitely one metric that is regularly mentioned, and which resonates with many people.

## Data and questions

This week, we’re going to look at US housing prices. Actually, we’re going to look at prices in a number of different regions. Along the way, we’ll exercise our use of multi-indexes in Pandas, a critical tool if you’re going to retrieve information from time-based data sets.

Our data will come from [FRED](https://fred.stlouisfed.org/?ref=bambooweekly.com), the amazingly useful online tool from the St. Louis Federal Reserve. Fred provides access to a large number of data sets, most of which are freely available from the US government.

We’re going to look at housing data from six different cities in the US. The most recent housing data, from the fourth quarter of 2022, was just posted in the last few days, and we’ll examine it to better understand housing prices.

We’ll look at state housing price indexes for six different states in the US:

- California (CA), from [https://fred.stlouisfed.org/series/CASTHPI](https://fred.stlouisfed.org/series/CASTHPI?ref=bambooweekly.com)
- Colorado (CO), from [https://fred.stlouisfed.org/series/COSTHPI](https://fred.stlouisfed.org/series/COSTHPI?ref=bambooweekly.com)
- Florida (FL), from [https://fred.stlouisfed.org/series/FLSTHPI](https://fred.stlouisfed.org/series/FLSTHPI?ref=bambooweekly.com)
- Hawaii (HI), from [https://fred.stlouisfed.org/series/HISTHPI](https://fred.stlouisfed.org/series/HISTHPI?ref=bambooweekly.com)
- Michigan (MI), from [https://fred.stlouisfed.org/series/MISTHPI](https://fred.stlouisfed.org/series/MISTHPI?ref=bambooweekly.com)
- New York (NY), from [https://fred.stlouisfed.org/series/NYSTHPI](https://fred.stlouisfed.org/series/NYSTHPI?ref=bambooweekly.com)

The links that I’ve gives you here lead to each of these data pages; you’ll need to download the CSV files from each of them onto your computer. Each of them will have a name XXSTHPI.csv, where XX will be the two-letter abbreviation for the state (as listed above).

Note that these six locations are meant to be somewhat diverse in geography. But I’m sure that someone who actually knows about demographics and statistical sampling of the US housing market would tell me that these are a terribly skewed sample. So please don’t make any investment decisions based on what we find here!

Here are the questions I’d like you to answer:

- 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.
- On which date, and in which region, were housing prices historically the highest?
- How long ago was that?
- In which region are the housing prices currently the highest?
- What are the mean and median prices for each location in our data set?
- 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?
- Between 2000 and 2020, what were the average prices in New York?
- Between 2000 and 2020, what were the average prices in each of New York and California?
- Since January 2020, how much have home prices changed in each of the six regions?

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

I’ll be back tomorrow with my solutions.

Reuven