"""Builds the master table: one row per UK SOC 2020 four-digit occupation (412).

Inputs (data/raw, fetched by pipeline/fetch.sh):
  dfe_annex1.xlsx               DfE AI occupational exposure (AIOE), SOC 2010
  soc2010_to_2020_jul2021.xlsx  ONS SOC 2010 -> SOC 2020 unit group shares
  ashe14/ ... Table 14.7a       ONS ASHE 2026 provisional, annual gross pay, SOC 2020
  aps_nm218.csv                 ONS APS (Nomis NM_218_1), employment by SOC 2020 x region
  ldv_sep2026.xlsx              ONS labour demand volumes (Textkernel adverts), SOC 2020 x region, monthly

Outputs:
  data/processed/master.csv           the master table
  data/processed/regional_employment.csv
  data/processed/adverts_monthly.csv  UK monthly adverts by SOC 2020
  data/processed/join_log.md          every join, with counts of matched, missing and suppressed rows

Run: .venv/bin/python pipeline/build.py
"""
import re
from pathlib import Path

import numpy as np
import openpyxl
import pandas as pd

ROOT = Path(__file__).resolve().parent.parent
RAW = ROOT / 'data' / 'raw'
OUT = ROOT / 'data' / 'processed'
OUT.mkdir(parents=True, exist_ok=True)

log = []


def note(line=''):
    print(line)
    log.append(line)


def num(v):
    """ONS cells hold 'x', '..', '*' or blanks for suppressed values."""
    if v is None:
        return np.nan
    if isinstance(v, (int, float)):
        return float(v)
    s = str(v).strip().replace(',', '')
    try:
        return float(s)
    except ValueError:
        return np.nan


# ---------------------------------------------------------------------------
# 1. DfE exposure scores (SOC 2010)
# ---------------------------------------------------------------------------
note('# Join log')
note()
note('## 1. DfE AI occupational exposure (SOC 2010)')
raw = pd.read_excel(RAW / 'dfe_annex1.xlsx', sheet_name='AIOE by education level', header=None)
hdr = raw.index[raw.iloc[:, 1].astype(str).str.strip() == 'SOC'][0]
dfe = raw.iloc[hdr + 1:, :5].copy()
dfe.columns = ['education_level', 'soc2010', 'title2010', 'aioe_all', 'aioe_llm']
dfe = dfe.dropna(subset=['soc2010'])
dfe['soc2010'] = dfe['soc2010'].astype(int).astype(str)
dfe['aioe_all'] = dfe['aioe_all'].astype(float)
dfe['aioe_llm'] = dfe['aioe_llm'].astype(float)
assert dfe['soc2010'].is_unique
note(f'- {len(dfe)} SOC 2010 unit groups with a score (SOC 2010 has 369).')

# ---------------------------------------------------------------------------
# 2. Crosswalk SOC 2010 -> SOC 2020
# ---------------------------------------------------------------------------
note()
note('## 2. ONS SOC 2010 to SOC 2020 relationship table (unit groups)')
cw = pd.read_excel(RAW / 'soc2010_to_2020_jul2021.xlsx', sheet_name='soc2010-soc2020-map_Unit')
cw['soc2010'] = cw['SOC2010'].ffill().astype(str).str.extract(r'^(\d{4})')[0]
bases = cw[cw['SOC2020'].astype(str).str.strip() == 'Total sample'].set_index('soc2010')
links = cw[cw['SOC2020'].astype(str).str.match(r'^\d{4}:')].copy()
links['soc2020'] = links['SOC2020'].str.extract(r'^(\d{4})')[0]


def flow(row, src):
    b = bases.loc[row['soc2010']]
    return (num(b[f'men_{src}_base_2010']) * num(row[f'men_{src}_perc_2020']) +
            num(b[f'women_{src}_base_2010']) * num(row[f'women_{src}_perc_2020']))


# Census-coded samples are larger, so they weight the flows; LFS fills any gap.
links['w_census'] = links.apply(lambda r: flow(r, 'census'), axis=1)
links['w_lfs'] = links.apply(lambda r: flow(r, 'lfs'), axis=1)
links['weight'] = links['w_census'].where(links['w_census'].fillna(0) > 0, links['w_lfs']).fillna(0)
links = links[links['weight'] > 0][['soc2010', 'soc2020', 'weight']]
note(f'- {links.soc2010.nunique()} SOC 2010 groups flow into {links.soc2020.nunique()} SOC 2020 groups '
     f'through {len(links)} weighted links (weight = people in the dual-coded Census sample, men plus women; LFS where Census is empty).')

missing_dfe = sorted(set(links.soc2010) - set(dfe.soc2010))
note(f'- SOC 2010 groups in the crosswalk with no DfE score: {len(missing_dfe)} ({", ".join(missing_dfe) or "none"}).')

m = links.merge(dfe[['soc2010', 'aioe_all', 'aioe_llm']], on='soc2010', how='left')
m['scored_w'] = m['weight'].where(m['aioe_all'].notna(), 0)
agg = m.groupby('soc2020').apply(
    lambda g: pd.Series({
        'exposure': np.average(g['aioe_all'].fillna(0), weights=g['scored_w']) if g['scored_w'].sum() > 0 else np.nan,
        'exposure_llm': np.average(g['aioe_llm'].fillna(0), weights=g['scored_w']) if g['scored_w'].sum() > 0 else np.nan,
        'crosswalk_coverage': g['scored_w'].sum() / g['weight'].sum(),
        'crosswalk_sources': int((g['scored_w'] > 0).sum()),
        'crosswalk_main_soc2010': g.sort_values('weight', ascending=False)['soc2010'].iloc[0],
        'crosswalk_main_share': g['weight'].max() / g['weight'].sum(),
    }), include_groups=False).reset_index()
note(f'- {agg.exposure.notna().sum()} SOC 2020 groups get a score; '
     f'{(agg.crosswalk_coverage < 1).sum()} of them have part of their inflow unscored (coverage below 100%).')
note(f'- {(agg.crosswalk_main_share >= 0.9).sum()} SOC 2020 groups take 90% or more of their workers from a single SOC 2010 group; '
     f'{(agg.crosswalk_main_share < 0.5).sum()} take less than half from any one group (blended scores).')

# ---------------------------------------------------------------------------
# 3. ASHE Table 14.7a (annual gross pay, SOC 2020)
# ---------------------------------------------------------------------------
note()
note('## 3. ONS ASHE Table 14.7a, 2026 provisional')
ashe_file = next((RAW / 'ashe14').glob('*Table 14.7a*'))


def ashe_sheet(sheet):
    d = pd.read_excel(ashe_file, sheet_name=sheet, header=None)
    d = d[d.iloc[:, 1].astype(str).str.match(r"^'?\d{4}$")].iloc[:, [0, 1, 2, 3, 5]]
    d.columns = ['title', 'soc2020', 'jobs_k', 'median', 'mean']
    d['soc2020'] = d['soc2020'].astype(str).str.lstrip("'")
    for c in ['jobs_k', 'median', 'mean']:
        d[c] = d[c].map(num)
    return d.set_index('soc2020')


ft = ashe_sheet('Full-Time')
allj = ashe_sheet('All')

# Mean pay for every employee job, filling suppressed four-digit cells from the
# three-digit (minor group) row, then the two-digit (sub-major group) row.
_all = pd.read_excel(ashe_file, sheet_name='All', header=None)
_all = _all[_all.iloc[:, 1].astype(str).str.match(r"^'?\d{2,3}$")].iloc[:, [1, 5]]
parent_mean = dict(zip(_all.iloc[:, 0].astype(str).str.lstrip("'"), _all.iloc[:, 1].map(num)))
note(f'- Full-time sheet: {len(ft)} four-digit codes; median annual pay published for {ft["median"].notna().sum()}, suppressed for {ft["median"].isna().sum()}.')
note(f'- All-employees sheet: {len(allj)} codes; jobs and mean pay both published for {(allj.jobs_k.notna() & allj["mean"].notna()).sum()}.')

# ---------------------------------------------------------------------------
# 4. APS employment via Nomis (SOC 2020, UK and regions)
# ---------------------------------------------------------------------------
note()
note('## 4. ONS Annual Population Survey, Nomis NM_218_1')
aps = pd.read_csv(RAW / 'aps_nm218.csv', dtype={'SOC2020_FULL_CODE': str})
aps = aps[aps.SOC2020_FULL_NAME.str.match(r'^\d{4} ')].copy()
aps['soc2020'] = aps.SOC2020_FULL_NAME.str[:4]
period = aps.DATE_NAME.iloc[0]
val = aps[aps.MEASURES_NAME == 'Value'].copy()
conf = aps[aps.MEASURES_NAME == 'Confidence'][['GEOGRAPHY_NAME', 'soc2020', 'OBS_VALUE']].rename(columns={'OBS_VALUE': 'ci95'})
val = val.merge(conf, on=['GEOGRAPHY_NAME', 'soc2020'], how='left')
val['employment'] = val['OBS_VALUE'].where(val['OBS_STATUS'] == 'A')
uk = val[val.GEOGRAPHY_NAME == 'United Kingdom'].set_index('soc2020')
emp = pd.read_csv(RAW / 'aps_nm218_employees.csv')
emp = emp[emp.SOC2020_FULL_NAME.str.match(r'^\d{4} ') & (emp.MEASURES_NAME == 'Value')].copy()
emp['soc2020'] = emp.SOC2020_FULL_NAME.str[:4]
emp['employees'] = emp['OBS_VALUE'].where(emp['OBS_STATUS'] == 'A')
val = val.merge(emp[['GEOGRAPHY_NAME', 'soc2020', 'employees']], on=['GEOGRAPHY_NAME', 'soc2020'], how='left')
uk = val[val.GEOGRAPHY_NAME == 'United Kingdom'].set_index('soc2020')
note(f'- Employees only (etype=1): UK {uk.employees.notna().sum()} of {len(uk)} codes published.')
reg = val[val.GEOGRAPHY_NAME != 'United Kingdom'][['GEOGRAPHY_NAME', 'GEOGRAPHY_CODE', 'soc2020', 'employment', 'employees', 'ci95', 'OBS_STATUS']]
note(f'- Period {period}. UK: {uk.employment.notna().sum()} of {len(uk)} codes published.')
for g, s in reg.groupby('GEOGRAPHY_NAME'):
    note(f'  - {g}: {s.employment.notna().sum()} published, {s.employment.isna().sum()} suppressed as unreliable or disclosive')
reg.rename(columns={'GEOGRAPHY_NAME': 'region', 'GEOGRAPHY_CODE': 'region_code', 'OBS_STATUS': 'nomis_status'}).to_csv(OUT / 'regional_employment.csv', index=False)

# ---------------------------------------------------------------------------
# 5. Online job adverts (Textkernel), Table 3: monthly by region x SOC 2020
# ---------------------------------------------------------------------------
note()
note('## 5. ONS labour demand volumes (Textkernel online job adverts), Table 3')
wb = openpyxl.load_workbook(RAW / 'ldv_sep2026.xlsx', read_only=True)
rows = wb['Table 3'].iter_rows(values_only=True)
for r in rows:
    if r and r[0] == 'Region':
        head = list(r)
        break
recs = [r for r in rows if r and r[0]]
ldv = pd.DataFrame(recs, columns=head)
months = [c for c in head if isinstance(c, str) and re.match(r'^[A-Z][a-z]{2}-\d{2}$', c)]
ldv = ldv.rename(columns={'SOC 4 digit code': 'soc2020', 'SOC 4 digit label': 'title'})
ldv['soc2020'] = ldv['soc2020'].astype(str).str.zfill(4)
for c in months:
    ldv[c] = ldv[c].map(num)
note(f'- {len(months)} months ({months[0]} to {months[-1]}), regions: {ldv.Region.nunique()}, '
     f'UK codes: {ldv[ldv.Region == "Total UK"].soc2020.nunique()}.')
ukads = ldv[ldv.Region == 'Total UK'].set_index('soc2020')[months]
long = ukads.reset_index().melt(id_vars='soc2020', var_name='month', value_name='adverts')
long['month'] = pd.to_datetime(long['month'], format='%b-%y').dt.strftime('%Y-%m')
long.to_csv(OUT / 'adverts_monthly.csv', index=False)
ldv[ldv.Region != 'Total UK'].melt(id_vars=['Region', 'soc2020', 'title'], value_vars=months, var_name='month', value_name='adverts') \
    .assign(month=lambda d: pd.to_datetime(d['month'], format='%b-%y').dt.strftime('%Y-%m')) \
    .to_csv(OUT / 'adverts_monthly_regional.csv', index=False)


# ONS withholds some monthly cells ([x]) when a source failed; March 2026 is withheld for every code.
# Withheld cells stay missing (NaN), never zero. Each window is the average month over the months ONS
# published for that code, times 12, so a missing month does not shrink the total.
supp = ukads.isna()
note(f'- Cells withheld by ONS (kept as missing, not zero): ' +
     ', '.join(f'{m} {int(n)}' for m, n in supp.sum().items() if n) + '.')


def window(start, end):
    cols = [c for c in months if pd.Timestamp(start) <= pd.to_datetime(c, format='%b-%y') <= pd.Timestamp(end)]
    assert len(cols) == 12, (start, end, len(cols))
    return ukads[cols].mean(axis=1, skipna=True) * 12


# ONS: October 2025 to September 2026 is partly imputed after a major source failed, so the headline
# window is the latest 12 months before that: October 2024 to September 2025.
LATEST = ('2024-10-01', '2025-09-01')
PREV = ('2023-10-01', '2024-09-01')
latest_12 = window(*LATEST)
prev_12 = window(*PREV)
pre_12 = window('2021-11-01', '2022-10-01')     # the 12 months before ChatGPT's launch (30 Nov 2022)
y2019 = window('2019-01-01', '2019-12-01')       # pre-pandemic reference year
note('- Headline advert window: October 2024 to September 2025, the latest 12 months before ONS\'s partly imputed '
     'period (October 2025 to September 2026). Compared with calendar 2019 (pre-pandemic), with November 2021 to '
     'October 2022 (the year before ChatGPT launched) and with October 2023 to September 2024 (the year before).')

# ---------------------------------------------------------------------------
# 6. Master table
# ---------------------------------------------------------------------------
note()
note('## 6. Master table')
codes = sorted(c for c in set(ft.index) | set(uk.index) | set(ukads.index) if re.fullmatch(r'\d{4}', c))
mt = pd.DataFrame({'soc2020': codes})
titles = pd.concat([ldv[['soc2020', 'title']].drop_duplicates('soc2020').set_index('soc2020')['title'],
                    ft['title']]).groupby(level=0).first()
mt['title'] = mt.soc2020.map(titles).str.strip()
mt['major_group'] = mt.soc2020.str[0]
mt['sub_major_group'] = mt.soc2020.str[:2]
mt = mt.merge(agg, on='soc2020', how='left')
mt['employment'] = mt.soc2020.map(uk.employment)
mt['employment_ci95'] = mt.soc2020.map(uk.ci95)
mt['median_pay_ft'] = mt.soc2020.map(ft['median'])
mt['employees'] = mt.soc2020.map(uk.employees)
mt['mean_pay_all'] = mt.soc2020.map(allj['mean'])
mt['mean_pay_source'] = np.where(mt.mean_pay_all.notna(), 'ASHE 4-digit', '')
for digits, label in [(3, 'ASHE 3-digit (filled)'), (2, 'ASHE 2-digit (filled)')]:
    gap = mt.mean_pay_all.isna()
    mt.loc[gap, 'mean_pay_all'] = mt.loc[gap, 'soc2020'].str[:digits].map(parent_mean)
    mt.loc[gap & mt.mean_pay_all.notna(), 'mean_pay_source'] = label
# Annual wage bill of employees: APS employees x ASHE mean annual gross pay (all employee jobs).
mt['wage_bill_gbp'] = mt.employees * mt.mean_pay_all
note(f'- Mean pay: {(mt.mean_pay_source == "ASHE 4-digit").sum()} from the four-digit row, '
     f'{(mt.mean_pay_source == "ASHE 3-digit (filled)").sum()} filled from three-digit, '
     f'{(mt.mean_pay_source == "ASHE 2-digit (filled)").sum()} from two-digit.')
note(f'- Wage bill total £{mt.wage_bill_gbp.sum() / 1e9:,.0f}bn across {mt.wage_bill_gbp.notna().sum()} jobs.')
mt['adverts_oct24_sep25'] = mt.soc2020.map(latest_12)
mt['adverts_oct23_sep24'] = mt.soc2020.map(prev_12)
mt['adverts_nov21_oct22'] = mt.soc2020.map(pre_12)
mt['adverts_2019'] = mt.soc2020.map(y2019)
mt['adverts_change_vs_nov21_oct22'] = mt.adverts_oct24_sep25 / mt.adverts_nov21_oct22 - 1
mt['adverts_change_vs_2019'] = mt.adverts_oct24_sep25 / mt.adverts_2019 - 1
# Fewer than 100 adverts in the base year is too few to compare; left blank.
mt.loc[mt.adverts_nov21_oct22 < 100, ['adverts_change_vs_nov21_oct22']] = np.nan
mt.loc[mt.adverts_2019 < 100, ['adverts_change_vs_2019']] = np.nan

# ---------------------------------------------------------------------------
# 7. Depth: entry rung, degree share, second exposure measure
# ---------------------------------------------------------------------------
note()
note('## 7. Additional measures')
cen = pd.read_excel(RAW / 'census_occ_age_sex_4digit_EW.xlsx', sheet_name='1', header=7)
cen.columns = ['soc2020', 'title', 'age', 'sex', 'people', 'pct']
cen['soc2020'] = cen['soc2020'].astype(str).str.extract(r'(\d{4})')[0]
cen['people'] = cen['people'].map(num)
suppressed = cen['people'].isna().sum()
cen['people'] = cen['people'].fillna(0)        # [c] cells are a handful of people each
young = cen[cen.age.isin(['Aged 16 to 19 years', 'Aged 20 to 24 years'])].groupby('soc2020').people.sum()
alln = cen.groupby('soc2020').people.sum()
mt['young_share_2021'] = mt.soc2020.map(young / alln)
note(f'- Census 2021 (England and Wales), people aged 16 to 24 as a share of each job: {mt.young_share_2021.notna().sum()} jobs; '
     f'{suppressed} small cells suppressed by ONS counted as zero.')

def aps_adhoc(sheet):
    d = pd.read_excel(RAW / 'aps_soc4_age_qual_sex_sic_itl3_apr25mar26.xlsx', sheet_name=sheet, header=None)
    hdr = d.iloc[8, 2:].tolist()
    body = d.iloc[9:, 1:].copy()
    body.columns = ['label'] + hdr
    body = body[body.label.astype(str).str.match(r'^\d{4} ')]
    body['soc2020'] = body.label.str[:4]
    return body.set_index('soc2020').drop(columns='label').map(num).fillna(0)   # '*' small, '-' none

qual = aps_adhoc('4-digit occ. by highest qual.')
known = qual.drop(columns=[c for c in qual.columns if "Don't know" in str(c)]).sum(axis=1)
deg = qual[[c for c in qual.columns if str(c).startswith('1 Degree')][0]]
mt['degree_share'] = mt.soc2020.map((deg / known).where(known >= 5000))
note(f'- APS Apr 2025 to Mar 2026 (ONS ad hoc 3501), share with a degree or equivalent: {mt.degree_share.notna().sum()} jobs '
     '(jobs with fewer than 5,000 people with a known qualification left blank).')

ci = pd.read_excel(RAW / 'soc2020_coding_index.xlsx', sheet_name='SOC2020 coding index',
                   usecols=['SOC_2020', 'ISCO-08 code based on SOC2020'], dtype=str)
ci.columns = ['soc2020', 'isco08']
ci['soc2020'] = ci.soc2020.str.extract(r'(\d{4})')[0]
ci['isco08'] = ci.isco08.str.extract(r'(\d{4})')[0]
ilo = pd.read_csv(RAW / 'ilo_wp140_genai_exposure_isco08_4digit.csv', dtype={'isco08_4digit': str})
ci = ci.dropna().merge(ilo[['isco08_4digit', 'mean_score']], left_on='isco08', right_on='isco08_4digit', how='left')
ilo_soc = ci.dropna(subset=['mean_score']).groupby('soc2020').mean_score.mean()   # averaged over index job titles
mt['ilo_genai_score'] = mt.soc2020.map(ilo_soc)
both = mt[['exposure', 'ilo_genai_score']].dropna()
note(f'- ILO 2025 generative AI exposure (Working Paper 140), mapped from ISCO-08 through the ONS SOC 2020 coding index '
     f'(average over each job\'s index titles): {mt.ilo_genai_score.notna().sum()} jobs. '
     f'Rank correlation with the DfE score: {both.corr("spearman").iloc[0, 1]:.2f}.')

overall = {
    'young_share_all': round(float(young.sum() / alln.sum()), 4),
    'degree_share_all': round(float(deg.sum() / known.sum()), 4),
}
note(f'- All jobs: {overall["young_share_all"]:.1%} aged 16 to 24 (Census 2021); {overall["degree_share_all"]:.1%} with a degree (APS).')

# ---------------------------------------------------------------------------
# 8. Cross-checks on the advert series (one supplier, so test it against two others)
# ---------------------------------------------------------------------------
note()
note('## 8. Cross-checks on the advert series')
x06 = pd.read_excel(RAW / 'x06nov2026.xlsx', sheet_name='Vacancies by industry', header=None)
vac = x06.iloc[8:, [0, 2]].dropna()
vac = vac[pd.to_datetime(vac[0], errors='coerce').notna()]
vac.index = pd.to_datetime(vac[0])
vac = pd.to_numeric(vac[2], errors='coerce')
vac = vac[~vac.index.duplicated()]
tk_total = long.assign(m=pd.to_datetime(long.month)).groupby('m').adverts.sum(min_count=1)
# Months where any code is withheld are left out of the total rather than undercounted.
tk_total[supp.any(axis=0).values] = np.nan
last12 = (pd.Timestamp(LATEST[0]), pd.Timestamp(LATEST[1]))
def rel(s, a, b, c, d): return float(s[a:b].dropna().mean() / s[c:d].dropna().mean() - 1)
xc = {
    'window': f'{last12[0]:%b %Y} to {last12[1]:%b %Y}',
    # Same method as every other advert figure: per-job averages over published months, summed.
    'textkernel_vs_2019': round(float(latest_12.sum() / y2019[latest_12.notna()].sum() - 1), 3),
    'x06_vs_2019': round(rel(vac, *last12, '2019-01', '2019-12'), 3),
}
oja = pd.read_excel(RAW / 'oja2024.xlsx', sheet_name='Adverts by category Feb 2020', header=None)
h = oja.index[oja.iloc[:, 0].astype(str).str.contains('Date', na=False)][0]
oja.columns = oja.iloc[h]
oja = oja.iloc[h + 1:].rename(columns=lambda c: 'date' if str(c).startswith('Date') else c)
oja['date'] = pd.to_datetime(oja['date'], errors='coerce')
oja = oja.dropna(subset=['date']).set_index('date').apply(pd.to_numeric, errors='coerce')
W24 = ('2024-01-01', '2024-09-30')
tk_codes = lambda pred: long[long.soc2020.map(pred)].assign(m=lambda d: pd.to_datetime(d.month)).groupby('m').adverts.sum()
pairs = [  # Adzuna category, matching SOC 2020 groups (approximate: Adzuna uses job-board categories)
    ('Legal', lambda c: c.startswith('241')), ('Accounting / Finance', lambda c: c.startswith('242')),
    ('IT / Computing / Software', lambda c: c.startswith('213')), ('Admin / clerical / secretarial', lambda c: c.startswith('41')),
    ('Construction / trades', lambda c: c.startswith('5')), ('Healthcare and Social care', lambda c: c.startswith('61')),
    ('Catering and hospitality', lambda c: c[:2] in ('54', '92')), ('Transport / logistics / warehouse', lambda c: c[:2] in ('82', '91')),
    ('Marketing / advertising / PR', lambda c: c in ('3554', '3555', '2434', '1132')), ('Graduate', lambda c: c.startswith('2')),
]
cmp_rows = [(cat, rel(oja[cat], *W24, '2019-01', '2019-12'), rel(tk_codes(pred), *W24, '2019-01', '2019-12')) for cat, pred in pairs]
cmp_df = pd.DataFrame(cmp_rows, columns=['adzuna_category', 'adzuna_change', 'textkernel_change'])
xc.update({
    'common_window': 'Jan to Sep 2024',
    'adzuna_all_vs_2019_2024': round(rel(oja['All industries'], *W24, '2019-01', '2019-12'), 3),
    'textkernel_all_vs_2019_2024': round(rel(tk_total, *W24, '2019-01', '2019-12'), 3),
    'x06_vs_2019_2024': round(rel(vac, *W24, '2019-01', '2019-12'), 3),
    'category_rank_correlation': round(float(cmp_df[['adzuna_change', 'textkernel_change']].corr('spearman').iloc[0, 1]), 2),
    'categories': [{'category': r.adzuna_category, 'adzuna': round(r.adzuna_change, 3), 'textkernel': round(r.textkernel_change, 3)} for r in cmp_df.itertuples()],
})
note(f'- Latest 12 months against 2019: Textkernel adverts {xc["textkernel_vs_2019"]:+.0%}, ONS X06 vacancies {xc["x06_vs_2019"]:+.0%}.')
note(f'- Jan to Sep 2024 against 2019: Textkernel {xc["textkernel_all_vs_2019_2024"]:+.0%}, Adzuna {xc["adzuna_all_vs_2019_2024"]:+.0%}, X06 {xc["x06_vs_2019_2024"]:+.0%}; '
     f'rank correlation of the two advert sources across {len(cmp_df)} matched categories {xc["category_rank_correlation"]}.')
import json
(OUT / 'overall.json').write_text(json.dumps({**overall, 'advert_crosscheck': xc}, indent=1))

scored = mt.exposure.notna()
mt.loc[scored, 'exposure_rank'] = mt.loc[scored, 'exposure'].rank(ascending=False, method='min').astype(int)
mt.loc[scored, 'exposure_percentile'] = mt.loc[scored, 'exposure'].rank(pct=True)   # 1.0 = most exposed
mt['highly_exposed'] = mt.exposure_percentile > 0.8                                 # most exposed fifth of jobs

note(f'- {len(mt)} SOC 2020 codes. Exposure {mt.exposure.notna().sum()}, employment {mt.employment.notna().sum()}, '
     f'full-time median pay {mt.median_pay_ft.notna().sum()}, wage bill {mt.wage_bill_gbp.notna().sum()}, '
     f'advert change vs 2019 {mt.adverts_change_vs_2019.notna().sum()}.')
complete = mt[['exposure', 'employment', 'median_pay_ft']].notna().all(axis=1)
note(f'- Complete for the hero chart (exposure, employment and pay): {complete.sum()}.')
note(f'- "Highly exposed" = the most exposed fifth of scored jobs: {int(mt.highly_exposed.sum())} jobs.')
no_score = mt[~scored]
if len(no_score):
    note(f'- No exposure score: {", ".join(no_score.soc2020 + " " + no_score.title.fillna(""))}')

mt.to_csv(OUT / 'master.csv', index=False)
(OUT / 'join_log.md').write_text('\n'.join(log) + '\n')
print('\nwrote', OUT / 'master.csv')
