Median Home Square Footage by State (FRED API)

28_sqft_by_state.py

"""
Pull median home square footage by state from FRED (Realtor.com data) and plot a bar chart.
Series: MEDSQUFEE + state postal code (e.g., MEDSQUFEEUT)

Get a free API key at: https://fredaccount.stlouisfed.org/login/secure/
"""

import requests
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import os

API_KEY = os.environ.get("FRED_API_KEY", "YOUR_API_KEY_HERE")

BASE_URL = "https://api.stlouisfed.org/fred/series/observations"

STATES = {
    "AL": "Alabama", "AK": "Alaska", "AZ": "Arizona", "AR": "Arkansas",
    "CA": "California", "CO": "Colorado", "CT": "Connecticut", "DE": "Delaware",
    "FL": "Florida", "GA": "Georgia", "HI": "Hawaii", "ID": "Idaho",
    "IL": "Illinois", "IN": "Indiana", "IA": "Iowa", "KS": "Kansas",
    "KY": "Kentucky", "LA": "Louisiana", "ME": "Maine", "MD": "Maryland",
    "MA": "Massachusetts", "MI": "Michigan", "MN": "Minnesota", "MS": "Mississippi",
    "MO": "Missouri", "MT": "Montana", "NE": "Nebraska", "NV": "Nevada",
    "NH": "New Hampshire", "NJ": "New Jersey", "NM": "New Mexico", "NY": "New York",
    "NC": "North Carolina", "ND": "North Dakota", "OH": "Ohio", "OK": "Oklahoma",
    "OR": "Oregon", "PA": "Pennsylvania", "RI": "Rhode Island", "SC": "South Carolina",
    "SD": "South Dakota", "TN": "Tennessee", "TX": "Texas", "UT": "Utah",
    "VT": "Vermont", "VA": "Virginia", "WA": "Washington", "WV": "West Virginia",
    "WI": "Wisconsin", "WY": "Wyoming", "DC": "District of Columbia",
    "US": "United States",
}


def fetch_series(series_id, api_key):
    """Fetch all observations for a FRED series. Returns a DataFrame or None."""
    params = {
        "series_id": series_id,
        "api_key": api_key,
        "file_type": "json",
        "sort_order": "desc",
    }
    resp = requests.get(BASE_URL, params=params, timeout=15)
    if resp.status_code != 200:
        return None
    data = resp.json().get("observations", [])
    if not data:
        return None
    df = pd.DataFrame(data)[["date", "value"]]
    df["value"] = pd.to_numeric(df["value"], errors="coerce")
    df["date"] = pd.to_datetime(df["date"])
    return df


def pull_timeseries(api_key=API_KEY):
    """Pull full time series for all states. Returns a long-format DataFrame."""
    frames = []
    for code, name in STATES.items():
        series_id = f"MEDSQUFEE{code}"
        df = fetch_series(series_id, api_key)
        if df is None:
            print(f"  No data: {series_id}")
            continue
        df["state_code"] = code
        df["state"] = name
        frames.append(df)
        print(f"  {code}: {len(df)} observations")

    if not frames:
        return pd.DataFrame()
    combined = pd.concat(frames, ignore_index=True)
    combined = combined.rename(columns={"value": "median_sqft"})
    return combined[["state_code", "state", "date", "median_sqft"]]


def plot_bar_chart(ts, out_path):
    """Plot 12-month average median sqft by state as a horizontal bar chart."""
    cutoff = ts["date"].max() - pd.DateOffset(months=12)
    df = ts[(ts["date"] > cutoff) & (ts["state_code"] != "US")]

    avg = (
        df.groupby(["state_code", "state"])["median_sqft"]
        .mean()
        .reset_index()
        .sort_values("median_sqft", ascending=True)
    )

    start = (ts["date"].max() - pd.DateOffset(months=12)).strftime("%b %Y")
    end = ts["date"].max().strftime("%b %Y")

    fig, ax = plt.subplots(figsize=(10, 14))
    bars = ax.barh(avg["state_code"], avg["median_sqft"], color="#4472C4", edgecolor="none")

    for bar, val in zip(bars, avg["median_sqft"]):
        ax.text(val + 10, bar.get_y() + bar.get_height() / 2,
                f"{val:,.0f}", va="center", ha="left", fontsize=7.5, color="#333333")

    ax.set_xlabel("Median Square Footage (Active Listings)", fontsize=11)
    ax.set_title(f"Median Home Size by State\n(12-Month Average, {start} – {end})",
                 fontsize=13, fontweight="bold")
    ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{x:,.0f}"))
    ax.set_xlim(0, avg["median_sqft"].max() * 1.12)
    ax.spines[["top", "right"]].set_visible(False)
    ax.tick_params(axis="y", labelsize=8.5)
    ax.tick_params(axis="x", labelsize=9)

    fig.text(0.5, -0.01,
             "Mormon Metrics  |  Source: Realtor.com Housing Inventory Core Metrics via FRED (St. Louis Fed)",
             ha="center", fontsize=8.5, color="#666666")

    plt.tight_layout()
    plt.savefig(out_path, dpi=150, bbox_inches="tight")
    print(f"Chart saved -> {out_path}")


if __name__ == "__main__":
    if API_KEY == "YOUR_API_KEY_HERE":
        print("Set your FRED API key via the FRED_API_KEY env variable or edit this script.")
        print("Get a free key at: https://fredaccount.stlouisfed.org/login/secure/")
        exit(1)

    data_dir = os.path.dirname(os.path.abspath(__file__))
    root_dir = os.path.dirname(data_dir)

    print("\n--- Pulling full time series ---")
    ts = pull_timeseries(API_KEY)
    out_ts = os.path.join(root_dir, "data", "sqft_by_state_timeseries.csv")
    ts.to_csv(out_ts, index=False)
    print(f"Data saved  -> {out_ts}")

    print("\n--- Plotting bar chart ---")
    out_chart = os.path.join(root_dir, "images", "28_sqft_by_state.png")
    plot_bar_chart(ts, out_chart)

sqft_by_state.py

"""
Pull median home square footage by state from FRED (Realtor.com data) and plot a bar chart.
Series: MEDSQUFEE + state postal code (e.g., MEDSQUFEEUT)

Get a free API key at: https://fredaccount.stlouisfed.org/login/secure/
"""

import requests
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import os

API_KEY = os.environ.get("FRED_API_KEY", "YOUR_API_KEY_HERE")

BASE_URL = "https://api.stlouisfed.org/fred/series/observations"

STATES = {
    "AL": "Alabama", "AK": "Alaska", "AZ": "Arizona", "AR": "Arkansas",
    "CA": "California", "CO": "Colorado", "CT": "Connecticut", "DE": "Delaware",
    "FL": "Florida", "GA": "Georgia", "HI": "Hawaii", "ID": "Idaho",
    "IL": "Illinois", "IN": "Indiana", "IA": "Iowa", "KS": "Kansas",
    "KY": "Kentucky", "LA": "Louisiana", "ME": "Maine", "MD": "Maryland",
    "MA": "Massachusetts", "MI": "Michigan", "MN": "Minnesota", "MS": "Mississippi",
    "MO": "Missouri", "MT": "Montana", "NE": "Nebraska", "NV": "Nevada",
    "NH": "New Hampshire", "NJ": "New Jersey", "NM": "New Mexico", "NY": "New York",
    "NC": "North Carolina", "ND": "North Dakota", "OH": "Ohio", "OK": "Oklahoma",
    "OR": "Oregon", "PA": "Pennsylvania", "RI": "Rhode Island", "SC": "South Carolina",
    "SD": "South Dakota", "TN": "Tennessee", "TX": "Texas", "UT": "Utah",
    "VT": "Vermont", "VA": "Virginia", "WA": "Washington", "WV": "West Virginia",
    "WI": "Wisconsin", "WY": "Wyoming", "DC": "District of Columbia",
    "US": "United States",
}


def fetch_series(series_id, api_key):
    """Fetch all observations for a FRED series. Returns a DataFrame or None."""
    params = {
        "series_id": series_id,
        "api_key": api_key,
        "file_type": "json",
        "sort_order": "desc",
    }
    resp = requests.get(BASE_URL, params=params, timeout=15)
    if resp.status_code != 200:
        return None
    data = resp.json().get("observations", [])
    if not data:
        return None
    df = pd.DataFrame(data)[["date", "value"]]
    df["value"] = pd.to_numeric(df["value"], errors="coerce")
    df["date"] = pd.to_datetime(df["date"])
    return df


def pull_timeseries(api_key=API_KEY):
    """Pull full time series for all states. Returns a long-format DataFrame."""
    frames = []
    for code, name in STATES.items():
        series_id = f"MEDSQUFEE{code}"
        df = fetch_series(series_id, api_key)
        if df is None:
            print(f"  No data: {series_id}")
            continue
        df["state_code"] = code
        df["state"] = name
        frames.append(df)
        print(f"  {code}: {len(df)} observations")

    if not frames:
        return pd.DataFrame()
    combined = pd.concat(frames, ignore_index=True)
    combined = combined.rename(columns={"value": "median_sqft"})
    return combined[["state_code", "state", "date", "median_sqft"]]


def plot_bar_chart(ts, out_path):
    """Plot 12-month average median sqft by state as a horizontal bar chart."""
    cutoff = ts["date"].max() - pd.DateOffset(months=12)
    df = ts[(ts["date"] > cutoff) & (ts["state_code"] != "US")]

    avg = (
        df.groupby(["state_code", "state"])["median_sqft"]
        .mean()
        .reset_index()
        .sort_values("median_sqft", ascending=True)
    )

    start = (ts["date"].max() - pd.DateOffset(months=12)).strftime("%b %Y")
    end = ts["date"].max().strftime("%b %Y")

    fig, ax = plt.subplots(figsize=(10, 14))
    bars = ax.barh(avg["state_code"], avg["median_sqft"], color="#4472C4", edgecolor="none")

    for bar, val in zip(bars, avg["median_sqft"]):
        ax.text(val + 10, bar.get_y() + bar.get_height() / 2,
                f"{val:,.0f}", va="center", ha="left", fontsize=7.5, color="#333333")

    ax.set_xlabel("Median Square Footage (Active Listings)", fontsize=11)
    ax.set_title(f"Median Home Size by State\n(12-Month Average, {start} – {end})",
                 fontsize=13, fontweight="bold")
    ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{x:,.0f}"))
    ax.set_xlim(0, avg["median_sqft"].max() * 1.12)
    ax.spines[["top", "right"]].set_visible(False)
    ax.tick_params(axis="y", labelsize=8.5)
    ax.tick_params(axis="x", labelsize=9)

    fig.text(0.5, -0.01,
             "Mormon Metrics  |  Source: Realtor.com Housing Inventory Core Metrics via FRED (St. Louis Fed)",
             ha="center", fontsize=8.5, color="#666666")

    plt.tight_layout()
    plt.savefig(out_path, dpi=150, bbox_inches="tight")
    print(f"Chart saved -> {out_path}")


if __name__ == "__main__":
    if API_KEY == "YOUR_API_KEY_HERE":
        print("Set your FRED API key via the FRED_API_KEY env variable or edit this script.")
        print("Get a free key at: https://fredaccount.stlouisfed.org/login/secure/")
        exit(1)

    data_dir = os.path.dirname(os.path.abspath(__file__))
    root_dir = os.path.dirname(data_dir)

    print("\n--- Pulling full time series ---")
    ts = pull_timeseries(API_KEY)
    out_ts = os.path.join(data_dir, "sqft_by_state_timeseries.csv")
    ts.to_csv(out_ts, index=False)
    print(f"Data saved  -> {out_ts}")

    print("\n--- Plotting bar chart ---")
    out_chart = os.path.join(root_dir, "images", "sqft_by_state.png")
    plot_bar_chart(ts, out_chart)