This chapter covers the two primary datasets — CERF storm allocations and OCHA cyclone population exposure estimates — along with the storm ID matching process that links them and the merge that produces the analysis dataset.
import sys
sys.path.insert(0, "..")
import ocha_stratus as stratus
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
from great_tables import GT, loc, style
from plotnine import *
import warnings
from dotenv import load_dotenv
warnings.filterwarnings("ignore", module="plotnine")
load_dotenv()
df_ocha = stratus.load_parquet_from_blob(
"ds-storm-impact-harmonisation/processed/adm0_ibtracs_exp_all.parquet"
)The ADAM and GDACS exposure datasets proved unusable for this analysis due to temporal gaps (see Section 11.3). However, the OCHA exposure dataset (adm0_ibtracs_exp_all.parquet) covers 2001–2026 with 1,271 storms and uses IBTrACS sid directly — giving us near-complete overlap with CERF.
print(f"Shape: {df_ocha.shape}")
print(f"Columns: {list(df_ocha.columns)}")
print(f"Unique storms (sid): {df_ocha['sid'].nunique()}")
print(f"Unique countries (ADM0_A3): {df_ocha['ADM0_A3'].nunique()}")
print(f"Wind speed thresholds: {sorted(df_ocha['speed'].unique())} kt")
df_ocha["year"] = df_ocha["sid"].str[:4].astype(int)
print(f"Year range: {df_ocha['year'].min()}–{df_ocha['year'].max()}")Shape: (4467, 4)
Columns: ['speed', 'sid', 'pop_exposed', 'ADM0_A3']
Unique storms (sid): 1271
Unique countries (ADM0_A3): 114
Wind speed thresholds: [np.int64(34), np.int64(50), np.int64(64)] kt
Year range: 2001–2026Three cumulative wind speed thresholds (34, 50, 64 kt) with population exposure per country. The data is confirmed cumulative: pop at 34 kt >= 50 kt >= 64 kt.
from src.datasets.cerf import pivot_ocha_wide
wide = pivot_ocha_wide(df_ocha)
both_34_50 = wide.dropna(subset=["pop_exp_34kt", "pop_exp_50kt"])
both_50_64 = wide.dropna(subset=["pop_exp_50kt", "pop_exp_64kt"])
print(f"34kt >= 50kt: {(both_34_50['pop_exp_34kt'] >= both_34_50['pop_exp_50kt']).all()}")
print(f"50kt >= 64kt: {(both_50_64['pop_exp_50kt'] >= both_50_64['pop_exp_64kt']).sum()}/{len(both_50_64)} rows")34kt >= 50kt: True
50kt >= 64kt: 620/621 rowsstorms_per_year = (
df_ocha.drop_duplicates("sid")
.groupby("year")
.size()
.reset_index(name="n_storms")
)
(
ggplot(storms_per_year, aes("year", "n_storms"))
+ geom_col(fill="#4292c6", color="#666666")
+ scale_x_continuous(breaks=range(storms_per_year["year"].min(), storms_per_year["year"].max() + 1))
+ labs(x="Year", y="Number of storms", title="OCHA Exposure — Storms per Year")
+ theme_minimal()
+ theme(
figure_size=(12, 4),
axis_text_x=element_text(rotation=45, ha="right"),
)
)
CERF storm allocation data is sourced from the CERF GMS API, filtered to Storm emergency type and Rapid Response window. Each allocation is linked to an IBTrACS storm via a curated ApplicationCode -> sid dictionary (CERFCODE_TO_SID in src/datasets/cerf.py).
from src.datasets.cerf import (
load_cerf_api_data,
apply_cerfcode_sids,
build_analysis_dataset_api,
CERFCODE_TO_SID,
)
df_cerf_api = load_cerf_api_data()
print(f"CERF API storm allocations: {len(df_cerf_api)}")
print(f"Year range: {int(df_cerf_api['Year'].min())}–{int(df_cerf_api['Year'].max())}")
print(f"Countries: {df_cerf_api['CountryCode'].nunique()}")
n_matched = sum(1 for v in CERFCODE_TO_SID.values() if v is not None)
n_non_tc = sum(1 for v in CERFCODE_TO_SID.values() if v is None)
print(f"\nCERFCODE_TO_SID: {len(CERFCODE_TO_SID)} entries")
print(f" Matched to TC: {n_matched}")
print(f" Non-TC (excluded): {n_non_tc}")CERF API storm allocations: 95
Year range: 2007–2026
Countries: 34
CERFCODE_TO_SID: 96 entries
Matched to TC: 85
Non-TC (excluded): 11# Build a global storm name lookup from IBTrACS
engine = stratus.get_engine("prod")
all_sids = sorted({s for s in CERFCODE_TO_SID.values() if s is not None})
with engine.connect() as conn:
sid_names = pd.read_sql(
f"SELECT sid, name FROM storms.ibtracs_storms "
f"WHERE sid IN ({','.join(repr(s) for s in all_sids)})",
conn,
)
sid_name_map = dict(zip(sid_names["sid"], sid_names["name"]))These allocations are classified as “Storm” in the CERF system but are not tropical cyclones (inland flooding, tornadoes, etc.). They are excluded from the analysis.
df_matched = apply_cerfcode_sids(df_cerf_api)
non_tc_codes = {k for k, v in CERFCODE_TO_SID.items() if v is None}
non_tc = df_cerf_api[df_cerf_api["ApplicationCode"].isin(non_tc_codes)]
display(
non_tc[["ApplicationCode", "CountryName", "Year", "TotalAmountApproved"]]
.sort_values("Year")
.reset_index(drop=True)
.rename(columns={
"CountryName": "Country",
"TotalAmountApproved": "Amount (USD)",
})
)| ApplicationCode | Country | Year | Amount (USD) | |
|---|---|---|---|---|
| 0 | 07-RR-SDN-13738 | Republic of the Sudan | 2007 | 1159499.0 |
| 1 | 07-RR-UGA-11920 | Uganda | 2007 | 6001015.0 |
| 2 | 07-RR-RWA-10462 | Rwanda | 2007 | 416325.0 |
| 3 | 07-RR-SDN-10078 | Republic of the Sudan | 2007 | 2663285.0 |
| 4 | 07-RR-MLI-7117 | Mali | 2007 | 1017103.0 |
| 5 | 07-RR-GHA-5535 | Ghana | 2007 | 2496956.0 |
| 6 | 10-RR-BOL-453 | Bolivia | 2010 | 2486524.0 |
| 7 | 10-RR-COL-2904 | Colombia | 2010 | 3640647.0 |
| 8 | 11-RR-ZWE-12668 | Zimbabwe | 2011 | 977054.0 |
| 9 | 19-RR-CUB-34583 | Cuba | 2019 | 1995221.0 |
| 10 | 20-RR-PAK-41273 | Pakistan | 2020 | 3000016.0 |
merged_agg = build_analysis_dataset_api(df_ocha)
merged_agg["storm_name"] = merged_agg["sid"].map(sid_name_map)print(f"Storm-country pairs: {len(merged_agg)}")
print(f"Unique storms: {merged_agg['sid'].nunique()}")
print(f"Countries: {merged_agg['iso3'].nunique()}")
print(f"Year range: {int(merged_agg['allocation_year'].min())}–{int(merged_agg['allocation_year'].max())}")
print(f"Total CERF: ${merged_agg['total_usd'].sum():,.0f}")Storm-country pairs: 68
Unique storms: 57
Countries: 25
Year range: 2007–2026
Total CERF: $365,264,993merged_agg.sort_values("total_usd", ascending=False)[
["Country", "iso3", "sid", "storm_name", "allocation_year", "total_usd",
"pop_exp_34kt", "pop_exp_50kt", "pop_exp_64kt"]
]| Country | iso3 | sid | storm_name | allocation_year | total_usd | pop_exp_34kt | pop_exp_50kt | pop_exp_64kt | |
|---|---|---|---|---|---|---|---|---|---|
| 10 | Myanmar | MMR | 2008117N11090 | NARGIS | 2008 | 26417370.0 | 19333692.0 | 4048393.0 | 2208832.0 |
| 18 | Philippines | PHL | 2013306N07162 | HAIYAN | 2013 | 25284204.0 | 28890700.0 | 16565265.0 | 10168586.0 |
| 7 | Bangladesh | BGD | 2007314N10093 | SIDR | 2007 | 19692303.0 | 138170560.0 | 89294760.0 | 27572576.0 |
| 33 | Mozambique | MOZ | 2019063S18038 | IDAI | 2019 | 14018121.0 | 4900148.0 | 3142120.0 | 2315791.0 |
| 47 | Philippines | PHL | 2021346N05145 | RAI | 2021 | 11974601.0 | 30291796.0 | 19172290.0 | 12772280.0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 37 | Bahamas | BHS | 2019236N10314 | DORIAN | 2019 | 1002151.0 | 129230.0 | 52998.0 | 33809.0 |
| 0 | Philippines | PHL | 2006329N06150 | DURIAN | 2007 | 938214.0 | 50348248.0 | 22613136.0 | 11771776.0 |
| 8 | Dominican Republic | DOM | 2007345N18298 | OLGA | 2007 | 774539.0 | 10489032.0 | 1622045.0 | NaN |
| 9 | Mozambique | MOZ | 2008062S10064 | JOKWE | 2008 | 548913.0 | 5627767.0 | 2382697.0 | 990164.0 |
| 46 | Fiji | FJI | 2020346S13168 | YASA | 2021 | 500000.0 | 862865.0 | 116364.0 | 111359.0 |
68 rows × 9 columns
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 4))
yearly = merged_agg.groupby("allocation_year").agg(
n_allocations=("total_usd", "size"),
total_usd=("total_usd", "sum"),
)
ax1.bar(yearly.index, yearly["n_allocations"], color="#4292c6", edgecolor="#666")
ax1.set_xlabel("Year")
ax1.set_ylabel("Number of storm-country pairs")
ax1.set_title("Allocation count")
ax2.bar(yearly.index, yearly["total_usd"] / 1e6, color="#ef6548", edgecolor="#666")
ax2.set_xlabel("Year")
ax2.set_ylabel("Amount (millions USD)")
ax2.set_title("Total amount allocated")
plt.tight_layout()
plt.show()
Not all storms with CERF allocations have corresponding entries in the OCHA exposure dataset. This section documents the coverage gap.
cerf_valid_api = df_matched.dropna(subset=["sid"])
ocha_wide_api = pivot_ocha_wide(df_ocha)
merged_api = cerf_valid_api.merge(ocha_wide_api, on=["sid", "iso3"], how="left")
exp_cols = ["pop_exp_34kt", "pop_exp_50kt", "pop_exp_64kt"]
merged_api["has_exposure"] = ~merged_api[exp_cols].isna().all(axis=1)n_tc = len(cerf_valid_api)
n_with_exp = merged_api["has_exposure"].sum()
n_without_exp = (~merged_api["has_exposure"]).sum()
n_sids_missing = merged_api.loc[~merged_api["has_exposure"], "sid"].nunique()
print(f"CERF allocations matched to TC sids: {n_tc}")
print(f" → with exposure data: {n_with_exp}")
print(f" → without exposure data: {n_without_exp} ({n_sids_missing} unique storms)")CERF allocations matched to TC sids: 84
→ with exposure data: 77
→ without exposure data: 7 (6 unique storms)The table below lists CERF allocations that have a valid IBTrACS sid but no matching record in the OCHA exposure data. There are two causes:
gap = merged_api[~merged_api["has_exposure"]].copy()
# Check which sids have *any* record in OCHA exposure
all_ocha_sids = set(df_ocha["sid"].unique())
gap["sid_in_ocha"] = gap["sid"].isin(all_ocha_sids)
gap["gap_reason"] = gap["sid_in_ocha"].map(
{False: "Storm absent from exposure data", True: "Country not in exposure for this storm"}
)
gap["storm_name"] = gap["sid"].map(sid_name_map)
display(
gap[["Country", "iso3", "sid", "storm_name", "allocation_year", "Amount in US$", "gap_reason"]]
.sort_values(["gap_reason", "allocation_year"])
.reset_index(drop=True)
)| Country | iso3 | sid | storm_name | allocation_year | Amount in US$ | gap_reason | |
|---|---|---|---|---|---|---|---|
| 0 | El Salvador | SLV | 2009308N11279 | IDA | 2009 | 2485827.0 | Country not in exposure for this storm |
| 1 | Malawi | MWI | 2019063S18038 | IDAI | 2019 | 3352045.0 | Country not in exposure for this storm |
| 2 | Guatemala | GTM | 2020306N15288 | ETA | 2020 | 2522190.0 | Country not in exposure for this storm |
| 3 | Guatemala | GTM | 2011280N10268 | NaN | 2011 | 2201628.0 | Storm absent from exposure data |
| 4 | Malawi | MWI | 2023036S12117 | FREDDY | 2023 | 5500000.0 | Storm absent from exposure data |
| 5 | Mozambique | MOZ | 2023036S12117 | FREDDY | 2023 | 9995213.0 | Storm absent from exposure data |
| 6 | Madagascar | MDG | 2024084S12054 | GAMANE | 2024 | 3000000.0 | Storm absent from exposure data |
gap_summary = (
gap.groupby("gap_reason")
.agg(
n_rows=("sid", "size"),
n_storms=("sid", "nunique"),
total_usd=("Amount in US$", "sum"),
)
.reset_index()
)
(
GT(gap_summary)
.tab_header(
title="CERF Allocations Without Exposure Data",
)
.cols_label(
gap_reason="Reason",
n_rows="Rows",
n_storms="Unique storms",
total_usd="Amount (USD)",
)
.fmt_currency("total_usd", currency="USD", decimals=0)
)| CERF Allocations Without Exposure Data | |||
| Reason | Rows | Unique storms | Amount (USD) |
|---|---|---|---|
| Country not in exposure for this storm | 3 | 3 | $8,360,062 |
| Storm absent from exposure data | 4 | 3 | $20,696,841 |
These gaps represent a data limitation — the OCHA exposure pipeline has not processed every storm. Notably absent are storms like FREDDY (2023), one of the longest-lived tropical cyclones on record, and GAMANE (2024). These allocations are excluded from the regression analysis but remain in the full CERF inventory for reference.
The original analysis used a CSV export with manually assigned storm IDs and algorithmic auto-matching. The API pipeline replaces this with a curated dictionary. This section compares the resulting analysis datasets.
from src.datasets.cerf import build_analysis_dataset, DEFAULT_MANUAL_OVERRIDES
df_cerf_old = stratus.load_csv_from_blob(
"ds-storm-impact-harmonisation/processed/cerf-storms-with-sids-2024-02-27.csv"
)
merged_old = build_analysis_dataset(
df_cerf_old, df_ocha, manual_overrides=DEFAULT_MANUAL_OVERRIDES
)
merged_old["storm_name"] = merged_old["sid"].map(sid_name_map)summary_df = pd.DataFrame([
{
"Pipeline": "Old CSV",
"Storm-country pairs": len(merged_old),
"Unique storms": merged_old["sid"].nunique(),
"Countries": merged_old["iso3"].nunique(),
"Year range": f"{int(merged_old['allocation_year'].min())}–{int(merged_old['allocation_year'].max())}",
"Total CERF (USD)": merged_old["total_usd"].sum(),
},
{
"Pipeline": "CERF API",
"Storm-country pairs": len(merged_agg),
"Unique storms": merged_agg["sid"].nunique(),
"Countries": merged_agg["iso3"].nunique(),
"Year range": f"{int(merged_agg['allocation_year'].min())}–{int(merged_agg['allocation_year'].max())}",
"Total CERF (USD)": merged_agg["total_usd"].sum(),
},
])
(
GT(summary_df)
.tab_header(title="Pipeline Comparison Summary")
.fmt_currency("Total CERF (USD)", currency="USD", decimals=0)
.fmt_number(["Storm-country pairs", "Unique storms", "Countries"], decimals=0)
)| Pipeline Comparison Summary | |||||
| Pipeline | Storm-country pairs | Unique storms | Countries | Year range | Total CERF (USD) |
|---|---|---|---|---|---|
| Old CSV | 59 | 51 | 23 | 2006–2023 | $298,267,410 |
| CERF API | 68 | 57 | 25 | 2007–2026 | $365,264,993 |
old_keys = set(zip(merged_old["sid"], merged_old["iso3"]))
new_pairs = merged_agg[
~merged_agg.apply(lambda r: (r["sid"], r["iso3"]) in old_keys, axis=1)
].copy()
(
GT(
new_pairs[["Country", "iso3", "sid", "storm_name", "allocation_year", "total_usd"]]
.sort_values("allocation_year")
.reset_index(drop=True)
.rename(columns={"total_usd": "Amount (USD)", "allocation_year": "Year", "storm_name": "Storm"})
)
.tab_header(
title=f"{len(new_pairs)} New Storm-Country Pairs",
subtitle="In API pipeline but not in old CSV pipeline",
)
.fmt_currency("Amount (USD)", currency="USD", decimals=0)
)| 13 New Storm-Country Pairs | |||||
| In API pipeline but not in old CSV pipeline | |||||
| Country | iso3 | sid | Storm | Year | Amount (USD) |
|---|---|---|---|---|---|
| Bangladesh | BGD | 2024145N14087 | REMAL | 2024 | $7,415,323 |
| Grenada | GRD | 2024181N09320 | BERYL | 2024 | $1,500,000 |
| Jamaica | JAM | 2024181N09320 | BERYL | 2024 | $2,498,400 |
| Philippines | PHL | 2024293N13141 | TRAMI | 2024 | $10,581,075 |
| Cuba | CUB | 2024293N21294 | OSCAR | 2024 | $3,499,569 |
| Cuba | CUB | 2024309N13283 | RAFAEL | 2024 | $5,999,888 |
| Mozambique | MOZ | 2024345S11062 | CHIDO | 2024 | $10,053,709 |
| Cuba | CUB | 2025291N11319 | MELISSA | 2025 | $7,500,016 |
| Haiti | HTI | 2025291N11319 | MELISSA | 2025 | $4,000,001 |
| Jamaica | JAM | 2025291N11319 | MELISSA | 2025 | $3,999,999 |
| Philippines | PHL | 2025308N10143 | FUNG-WONG | 2025 | $5,948,201 |
| Madagascar | MDG | 2026030S16043 | FYTIA | 2025 | $7,499,998 |
| Mozambique | MOZ | 2025068S15046 | JUDE | 2026 | $4,500,001 |
api_keys = set(zip(merged_agg["sid"], merged_agg["iso3"]))
dropped = merged_old[
~merged_old.apply(lambda r: (r["sid"], r["iso3"]) in api_keys, axis=1)
].copy()
if len(dropped) > 0:
(
GT(
dropped[["Country", "iso3", "sid", "storm_name", "allocation_year", "total_usd"]]
.sort_values("allocation_year")
.reset_index(drop=True)
.rename(columns={"total_usd": "Amount (USD)", "allocation_year": "Year", "storm_name": "Storm"})
)
.tab_header(
title=f"{len(dropped)} Dropped Storm-Country Pairs",
subtitle="In old CSV pipeline but not in API pipeline",
)
.fmt_currency("Amount (USD)", currency="USD", decimals=0)
)
else:
print("No pairs were dropped — the API pipeline is a strict superset.")