Public
Edited
May 4, 2023
Insert cell
Insert cell
Insert cell
Insert cell
Plot.plot({
width: 1200,
height: 1000,
insetLeft: 10,
insetRight: 60,
r: {range: [0, 80]},
marks: [
Plot.frame({anchor: "bottom"}),
Plot.dot(
fails,
Plot.dodgeY({
sort,
anchor: "bottom",
padding: 2,
x: "Date",
r: Assets, // N.B. sqrt scale
title: (d) => `${d["Bank Name"]}\n${(d[Assets] / 1000).toFixed(1)}B`,
fill: "#ddd",
stroke: "#000",
strokeWidth: 1
})
),
Plot.text(
fails,
Plot.filter((d) => d.Assets >= 2000, Plot.dodgeY({
sort,
anchor: "bottom",
padding: 2,
x: "Date",
lineWidth: 5,
r: Assets,
text: (d) => d.Assets > 12900
? `${d["Bank Name"]}\n${(d[Assets] / 1000).toFixed(0)}B`
: `${(d[Assets] / 1000).toFixed(1)}`,
pointerEvents: "none",
fill: "#000",
stroke: "#ddd"
}))
)
]
})
Insert cell
Insert cell
fails = (await FileAttachment("bfb-data@1.csv").csv({array: true}))
.slice(1, -2)
.map((d) => {
const date = parseDate(d[2]);
const assets = parseAssets(d[3]);
return {
"Bank Name": d[0].split(", ")[0],
"City, State": d[0].split(", ").slice(1).join(", "),
"Date": date,
"Assets": assets,
"Assets (adj.)": adjustForInflation(date, assets),
"Acquirer": d[5]
};
}).concat(pre2000)
Insert cell
pre2000 = (function () {
// Data from https://www.fdic.gov/bank/historical/managing/chronological/appendices/index.html --> "FDIC at a Glance"
var raw = `Total Failed and Assisted Banks 11 10 42 48 80 120 145 203 279 207 169 127 122 41 13 6 6 1 3 8 7 4 11 3
Total Assets of Failed and Assisted Banks $8,192.4 $4,947.4 $11,722.6 $7,191.7 $43,432.5 $8,977.3 $8,069.1 $9,407.0 $53,899.4 $28,935.0 $16,937.7 $64,635.0 $45,391.1 $3,828.9 $1,463.9 802.1 232.6 27.9 290.2 1592.2 414.5 1821.8 2914.5 1138`;

var table = raw.split("\n").map((s) =>
s
.split("\t")
.slice(1)
.map((s) => Number(s.replace(/[^0-9.]/g, "")))
);

table = table[0].map((elt, i) => [1980 + i, elt, table[1][i]]);

function randomDateInYear(year) {
const date = new Date(`${year}-01-01`);
const millis = date.valueOf();
const randomDate = new Date(
millis + Math.random() * (365 * 24 * 3600 * 1e3)
);
return new Date(randomDate).toISOString().slice(0, 10);
}

var table2 = table.flatMap(([year, numFail, totalAssets]) =>
Array.from(Array(numFail), (_, i) => [
`${year}-${i + 1}`,
"(None)",
randomDateInYear(year),
totalAssets / numFail,
0,
"(None)",
])
).filter(row => row[0] < '2001'); // Nice individualized FRED data starts in 2001

return table2.map((row) => {
const date = pre2000ParseDate(row[2]);
const assets = row[3];
return {
"Bank Name": row[0],
"City, State": "",
Date: date,
Assets: row[3],
"Assets (adj.)": adjustForInflation(date, assets),
Acquirer: "",
};
});
})();

Insert cell
pre2000ParseDate = d3.utcParse("%Y-%m-%d");
Insert cell
parseDate = d3.utcParse("%d-%b-%y")
Insert cell
parseAssets = (x) => parseFloat(x.replace(/[^\d.]/g, ""))
Insert cell
Insert cell
findCpi = {
const bisector = d3.bisector((d) => d.DATE);
return (date) => cpiaucsl[bisector.center(cpiaucsl, date)].CPIAUCSL;
}
Insert cell
adjustForInflation = {
const currentCpi = findCpi(new Date("2023-05-01"));
return (date, value) => currentCpi / findCpi(date) * value;
}
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more