Housing affordability

We want to explore which local authorities are the most/least expensive to buy homes in, relative to wages. For this, we wll use the median house price to residence based earning ratio.

from pathlib import Path
import pandas as pd
ROOT = Path("../").resolve()

# Load the house price to wage ratio data
d = pd.read_parquet(ROOT/"data/house-prices/site/hp_to_wage_ratio.parquet")

# Load the geocodes
codes = pd.read_csv(ROOT / "metadata/lookups/Local_Authority_Districts_(April_2023)_Names_and_Codes_in_the_United_Kingdom.csv", usecols=['LAD23NM', 'LAD23CD'])
codes.rename(columns={'LAD23CD': 'geography_code'}, inplace=True)

# Load the house prices data
hp = pd.read_parquet(ROOT / "data/house-prices/site/median_house_prices.parquet")
hp.reset_index(inplace=True)
# Filter so we only have one date per year. We'll choose the latest which is march.
hp = hp[hp['date'].str.contains('Mar')]
# Get tht year only
hp['date'] = hp['date'].apply(lambda s: s[-4::])

# Merge the dataframes
d = d.merge(codes, on='geography_code').merge(hp, on=['geography_code', 'date']).set_index('geography_code')
d

Let's look at the most recent year's data, which is for 2023. We'll also drop any places that don't have data for 2023.

d = d[d.date == '2023']
d = d.dropna()
least_affordable = d.sort_values(by='value', ascending=False).head(5)
least_affordable

Unsurprisingly, they are all Boroughs of London. We'll do the same thing but ignore London Boroughs (E09 codes).

d = d[~d.index.str.startswith('E09')]
least_affordable_not_london = d.sort_values(by='value', ascending=False).head(5)
least_affordable_not_london

Finally, where are the 5 most affordable (lowest house price to earnings ratio) places?

most_affordable = d.sort_values(by='value', ascending=False).tail(5)
most_affordable