"""
Job openings-to-employment ratio (JOE), United States, monthly, 1951 to date.

    JOE = job openings / employment, in percent

Construction (follows Kothari and O'Doherty, 2023):
  1951-01 to 2000-12  Barnichon (2010) composite Help-Wanted Index, which is in
                      job-openings-rate units (openings / labour force). It is
                      rescaled to openings / employment with the CPS labour
                      force and employment levels:
                          JOE = (V/LF) x (CLF16OV / CE16OV)
  2001-01 onwards     JOLTS job openings over CPS employment:
                          JOE = 100 x JTSJOL / CE16OV

Sources (all downloaded from FRED, no API key needed):
  JTSJOL   Job openings, total nonfarm, thousands, SA (BLS JOLTS)
  CE16OV   Employment level, thousands, SA (BLS Current Population Survey)
  CLF16OV  Civilian labour force level, thousands, SA (BLS CPS)
  USREC    NBER recession indicator (1 = recession month)
The 1951-2000 help-wanted series is fixed history and is included at the
bottom of this file, so the script needs nothing except the internet.

Output: joe_monthly.csv next to this script.

Columns
  month                 YYYY-MM
  joe_pct               JOE for that month, percent
  joe_lagged_pct        the latest JOE reading that was public by the end of
                        that month: a 1-month lag to 2000, 2 months from 2001
                        (JOLTS is released about five weeks after the month)
  source                barnichon_hwi or jolts
  v_over_lf_pct         Barnichon openings / labour force, percent (to 2000)
  jolts_openings_thous  JTSJOL (from 2001)
  employment_thous      CE16OV
  labour_force_thous    CLF16OV
  nber_recession        USREC

Notes
  * Values are the latest published vintage, not real-time vintages. The
    lagged column copies publication timing only.
  * October 2025 has no CPS employment figure (the household survey was not
    run during the US federal government shutdown), so JOE is blank for that
    month. It is left blank rather than filled in.
  * From 2001 this matches the series plotted by Kothari and O'Doherty. Before
    2001 it runs about 7% above their plotted levels, a constant ratio, so the
    help-wanted index there was scaled differently. The ups and downs are the
    same; this version is continuous at the 2001 join.

Cite: Kothari, P., and O'Doherty, M. S. (2023). Job postings and aggregate
stock returns. Journal of Financial Markets 64, 100804.
Barnichon, R. (2010). Building a composite Help-Wanted Index. Economics
Letters 109(3), 175-178.

Requires: Python 3.9+, pandas.   Run: python update_joe.py
"""

from io import StringIO
from pathlib import Path
from urllib.request import Request, urlopen

import pandas as pd

FRED_CSV = "https://fred.stlouisfed.org/graph/fredgraph.csv?id={}&cosd=1948-01-01"
SPLICE = pd.Period("2001-01", freq="M")  # first month that uses JOLTS


def fred(series_id: str) -> pd.Series:
    """Download one monthly FRED series as a Series indexed by month."""
    req = Request(FRED_CSV.format(series_id), headers={"User-Agent": "joe-update/1.0"})
    with urlopen(req, timeout=60) as resp:
        text = resp.read().decode("utf-8")
    df = pd.read_csv(StringIO(text), na_values=["."])
    df.columns = ["date", series_id]
    df["month"] = pd.to_datetime(df["date"]).dt.to_period("M")
    return df.set_index("month")[series_id].astype(float)


def barnichon() -> pd.Series:
    """Barnichon (2010) openings / labour force, percent, 1951-01 to 2000-12."""
    values = {}
    for line in BARNICHON_V_OVER_LF.strip().splitlines():
        year, *months = line.split()
        for m, v in enumerate(months, start=1):
            values[pd.Period(f"{year}-{m:02d}", freq="M")] = float(v)
    return pd.Series(values, name="v_over_lf_pct")


def build() -> pd.DataFrame:
    vlf = barnichon()
    jol, emp, clf, rec = (fred(s) for s in ("JTSJOL", "CE16OV", "CLF16OV", "USREC"))

    last = jol.dropna().index.max()  # JOLTS is the later-published input
    months = pd.period_range("1951-01", last, freq="M")
    df = pd.DataFrame(index=months)
    df["v_over_lf_pct"] = vlf.reindex(months)
    df["jolts_openings_thous"] = jol.reindex(months).where(months >= SPLICE)
    df["employment_thous"] = emp.reindex(months)
    df["labour_force_thous"] = clf.reindex(months)
    df["nber_recession"] = rec.reindex(months)

    early = months < SPLICE
    df["source"] = ["barnichon_hwi" if e else "jolts" for e in early]
    df["joe_pct"] = (df["v_over_lf_pct"] * df["labour_force_thous"] / df["employment_thous"]).where(early)
    df.loc[~early, "joe_pct"] = 100 * df["jolts_openings_thous"] / df["employment_thous"]

    # Publication timing: 1-month lag to 2000-12, 2-month lag from 2001-01.
    lag = pd.Series([1 if e else 2 for e in early], index=months)
    pos = pd.Series(range(len(months)), index=months) - lag
    df["joe_lagged_pct"] = [df["joe_pct"].iloc[p] if p >= 0 else float("nan") for p in pos]

    df.index = df.index.strftime("%Y-%m")
    df.index.name = "month"
    cols = ["joe_pct", "joe_lagged_pct", "source", "v_over_lf_pct", "jolts_openings_thous",
            "employment_thous", "labour_force_thous", "nber_recession"]
    return df[cols]


def main() -> None:
    df = build()
    out = Path(__file__).with_name("joe_monthly.csv")
    df.round({"joe_pct": 4, "joe_lagged_pct": 4}).to_csv(out, float_format="%.10g")
    latest = df["joe_pct"].dropna()
    print(f"Wrote {out.name}: {len(df)} months, {df.index[0]} to {df.index[-1]}")
    print(f"Latest JOE: {latest.iloc[-1]:.2f}% ({latest.index[-1]})")
    missing = df.index[df["joe_pct"].isna()].tolist()
    if missing:
        print("No JOE value (missing input) for:", ", ".join(missing))


# Barnichon (2010) composite Help-Wanted Index, 2017 revision, in job-openings-rate
# units: vacancies / labour force, percent. One row per year, January to December.
BARNICHON_V_OVER_LF = """
1951  3.73 3.72 4.09 4.05 4.09 3.88 3.85 3.88 4.01 3.97 3.92 3.85
1952  4.09 4.14 4.01 3.97 3.88 3.64 3.48 3.73 4.16 4.20 4.36 4.05
1953  4.18 4.34 4.44 4.14 4.12 3.83 3.53 3.46 3.38 3.05 2.75 2.33
1954  2.37 2.24 2.16 2.09 2.05 2.05 2.02 1.98 1.92 1.98 2.14 2.11
1955  2.38 2.38 2.64 2.70 2.88 2.85 3.07 3.18 3.14 3.31 3.36 3.16
1956  3.53 3.53 3.27 3.29 3.33 3.29 3.14 3.12 3.12 3.24 3.20 3.09
1957  3.29 3.07 3.05 2.83 2.88 2.83 2.83 2.61 2.51 2.20 2.07 1.98
1958  1.98 1.81 1.72 1.63 1.66 1.77 1.85 1.85 1.77 1.87 2.00 1.96
1959  2.14 2.26 2.40 2.50 2.61 2.70 2.70 2.57 2.51 2.42 2.53 2.40
1960  3.07 3.07 2.96 2.85 2.81 2.74 2.64 2.61 2.50 2.40 2.35 2.24
1961  2.24 2.20 2.24 2.26 2.35 2.37 2.44 2.53 2.59 2.72 2.81 2.79
1962  2.92 2.87 2.87 2.87 2.96 2.87 2.87 2.79 2.74 2.70 2.70 2.70
1963  2.74 2.74 2.77 2.72 2.66 2.68 2.75 2.72 2.70 2.79 2.74 2.88
1964  2.88 2.83 2.87 2.98 2.98 3.09 3.18 3.12 3.16 3.16 3.31 3.29
1965  3.33 3.46 3.49 3.53 3.66 3.70 3.70 3.83 3.97 4.09 4.36 4.44
1966  4.47 4.58 4.84 4.64 4.68 4.71 4.71 4.64 4.55 4.55 4.53 4.47
1967  4.51 4.47 4.36 4.38 4.34 4.31 4.22 4.33 4.27 4.34 4.31 4.33
1968  4.47 4.38 4.47 4.55 4.53 4.51 4.64 4.73 4.83 5.07 5.05 4.95
1969  5.16 5.12 5.16 5.20 5.21 4.99 4.95 4.84 5.12 5.03 4.92 4.73
1970  4.51 4.46 4.20 4.07 3.83 3.75 3.61 3.57 3.53 3.27 3.25 3.25
1971  3.12 3.22 3.22 3.20 3.24 3.38 3.31 3.33 3.29 3.33 3.38 3.42
1972  3.57 3.64 3.70 3.73 3.79 3.83 3.90 4.03 4.09 4.27 4.34 4.70
1973  4.84 4.79 4.81 4.71 4.77 4.77 4.84 4.73 4.68 4.73 4.68 4.47
1974  4.46 4.34 4.36 4.51 4.44 4.31 4.14 4.05 3.86 3.59 3.31 3.03
1975  2.85 2.79 2.70 2.70 2.68 2.88 2.92 2.88 2.96 2.96 3.07 3.07
1976  3.12 3.29 3.31 3.27 3.35 3.36 3.31 3.31 3.29 3.33 3.38 3.53
1977  3.64 3.62 3.72 3.81 3.90 3.92 4.03 4.10 4.09 4.27 4.38 4.51
1978  4.53 4.57 4.70 4.95 4.90 4.88 4.88 4.95 5.01 5.16 5.10 5.12
1979  5.07 4.94 4.97 5.16 5.12 5.03 4.97 5.03 5.14 5.21 4.88 4.83
1980  4.70 4.58 4.53 3.99 3.66 3.72 3.72 3.73 3.90 3.90 4.07 3.92
1981  3.81 3.86 3.85 3.83 3.77 3.83 3.79 3.75 3.57 3.35 3.33 3.18
1982  3.16 3.03 2.94 2.81 2.77 2.63 2.50 2.42 2.29 2.29 2.29 2.38
1983  2.42 2.42 2.50 2.55 2.77 2.75 2.92 2.96 3.01 3.31 3.31 3.44
1984  3.53 3.68 3.68 3.83 3.86 3.94 3.99 3.86 3.96 3.97 3.92 4.07
1985  3.96 3.97 4.07 3.96 4.05 4.05 4.01 4.03 4.09 4.01 4.09 3.97
1986  3.96 3.94 3.90 3.92 3.85 4.07 3.90 3.94 3.97 3.96 4.12 3.90
1987  3.85 4.07 4.23 4.20 4.29 4.29 4.29 4.47 4.46 4.49 4.55 4.34
1988  4.23 4.29 4.40 4.36 4.46 4.33 4.40 4.40 4.22 4.44 4.29 4.38
1989  4.23 4.23 4.12 4.33 4.14 3.96 4.03 3.99 3.97 4.07 3.90 4.03
1990  3.94 3.77 3.75 3.70 3.61 3.57 3.57 3.42 3.29 3.12 2.90 2.90
1991  2.70 2.61 2.55 2.53 2.53 2.57 2.48 2.44 2.46 2.35 2.38 2.40
1992  2.27 2.37 2.48 2.38 2.46 2.42 2.40 2.46 2.38 2.44 2.51 2.51
1993  2.46 2.55 2.53 2.53 2.63 2.55 2.64 2.70 2.64 2.77 2.79 2.87
1994  2.95 3.08 3.12 3.12 3.24 3.16 3.33 3.18 3.22 3.42 3.37 3.50
1995  3.37 3.38 3.30 3.34 3.11 3.16 3.32 3.29 3.29 3.34 3.22 3.53
1996  3.32 3.24 3.33 3.25 3.12 3.43 3.21 3.22 3.39 3.22 3.49 3.45
1997  3.41 3.55 3.51 3.47 3.27 3.58 3.45 3.51 3.69 3.62 3.85 3.59
1998  3.74 3.84 3.81 3.83 3.84 3.77 3.78 3.84 3.66 3.63 3.83 3.75
1999  3.99 4.05 3.90 3.86 3.93 3.75 3.82 3.84 3.71 3.87 3.84 3.91
2000  4.06 4.13 4.06 4.08 3.82 3.74 3.83 3.60 3.72 3.70 3.56 3.55
"""

if __name__ == "__main__":
    main()