Data centers vs. all other private construction

// Parse the Census Bureau's construction-spending workbook (privsatime.xlsx:
// private construction put in place, seasonally adjusted annual rate, $ millions)
// with nothing but the platform: an .xlsx is a zip of XML, so we walk the zip's
// central directory, inflate the two members we need with DecompressionStream,
// and read the cells with regular expressions. Same code runs in the browser
// (this page) and under node --test.

function u16(b, o) { return b[o] | (b[o + 1] << 8); }
function u32(b, o) { return (b[o] | (b[o + 1] << 8) | (b[o + 2] << 16) | (b[o + 3] << 24)) >>> 0; }

async function inflateRaw(bytes) {
  const ds = new DecompressionStream("deflate-raw");
  const stream = new Blob([bytes]).stream().pipeThrough(ds);
  return new Uint8Array(await new Response(stream).arrayBuffer());
}

/** Read one member of a zip (by name) as a UTF-8 string. */
export async function zipMember(buf, name) {
  const b = new Uint8Array(buf);
  // End-of-central-directory record: signature 0x06054b50, scan back from the end.
  let eocd = b.length - 22;
  while (eocd >= 0 && u32(b, eocd) !== 0x06054b50) eocd--;
  if (eocd < 0) throw new Error("not a zip");
  const count = u16(b, eocd + 10);
  let p = u32(b, eocd + 16);
  const dec = new TextDecoder();
  for (let i = 0; i < count; i++) {
    if (u32(b, p) !== 0x02014b50) throw new Error("bad central directory");
    const method = u16(b, p + 10);
    const csize = u32(b, p + 20);
    const nlen = u16(b, p + 28), elen = u16(b, p + 30), clen = u16(b, p + 32);
    const local = u32(b, p + 42);
    const fname = dec.decode(b.subarray(p + 46, p + 46 + nlen));
    p += 46 + nlen + elen + clen;
    if (fname !== name) continue;
    const dataStart = local + 30 + u16(b, local + 26) + u16(b, local + 28);
    const data = b.subarray(dataStart, dataStart + csize);
    if (method === 0) return dec.decode(data);
    if (method === 8) return dec.decode(await inflateRaw(data));
    throw new Error("unsupported zip method " + method);
  }
  throw new Error("zip member not found: " + name);
}

function unescapeXml(s) {
  return s.replace(/&amp;/g, "&").replace(/&lt;/g, "<").replace(/&gt;/g, ">").replace(/&quot;/g, '"').replace(/&apos;/g, "'");
}

/**
 * @returns {{title: string, columns: string[], rows: {month: string, flag: string, values: Record<string, number>}[]}}
 *   rows are chronological (the file is newest-first); month is like "Dec-23";
 *   flag is "p" (preliminary), "r" (revised) or ""; values are $ millions SAAR.
 */
export async function parseCensusXlsx(buf) {
  const shared = await zipMember(buf, "xl/sharedStrings.xml");
  const strings = [...shared.matchAll(/<si>(.*?)<\/si>/gs)].map((m) => unescapeXml(m[1].replace(/<[^>]+>/g, "")));
  const sheet = await zipMember(buf, "xl/worksheets/sheet1.xml");
  const rows = [];
  for (const [, row] of sheet.matchAll(/<row[^>]*>(.*?)<\/row>/gs)) {
    const cells = {};
    for (const [, col, attrs, inner] of row.matchAll(/<c r="([A-Z]+)\d+"([^>]*)>(.*?)<\/c>/gs)) {
      const v = /<v>(.*?)<\/v>/.exec(inner);
      if (!v) continue;
      cells[col] = attrs.includes('t="s"') ? strings[Number(v[1])] : v[1];
    }
    rows.push(cells);
  }
  const title = rows[0].A;
  // Header row: column letter -> name. Census footnote digits ("Construction1")
  // and embedded line breaks are stripped.
  const header = rows[3];
  const names = {};
  for (const [col, raw] of Object.entries(header)) {
    if (col === "A") continue;
    names[col] = raw.replace(/\s*_x000D_\s*|\r?\n/g, " ").replace(/\d+$/, "").trim();
  }
  const out = [];
  for (const r of rows.slice(4)) {
    if (!r.A || !r.B || !/^[A-Z][a-z]{2}-\d\d[pr]?$/.test(r.A)) continue;
    const flag = /[pr]$/.test(r.A) ? r.A.slice(-1) : "";
    const values = {};
    for (const [col, name] of Object.entries(names)) values[name] = r[col] === undefined || r[col] === "" ? NaN : Number(r[col]);
    out.push({ month: r.A.replace(/[pr]$/, ""), flag, values });
  }
  out.reverse();
  return { title, columns: Object.values(names), rows: out };
}

/** SAAR in $ millions -> $ billions per month, the unit the WSJ chart uses. */
export function perMonth(saarMillions) { return saarMillions / 12 / 1000; }

/** A derived series as change from a base month. f maps a row's values to a number. */
export function changeSince(table, baseMonth, f) {
  const i0 = table.rows.findIndex((r) => r.month === baseMonth);
  if (i0 < 0) throw new Error("no such month " + baseMonth);
  const base = f(table.rows[i0].values);
  return table.rows.slice(i0).map((r) => ({ month: r.month, value: f(r.values) - base }));
}

/** Level series (no base) for the rows from a start month on. */
export function levelsFrom(table, startMonth, f) {
  const i0 = table.rows.findIndex((r) => r.month === startMonth);
  return table.rows.slice(i0).map((r) => ({ month: r.month, value: f(r.values) }));
}

// Draws every chart on the page as inline SVG from the parsed Census table.
// Runs in the browser; loaded by the page as an ES module. The page also shows
// this file's source verbatim, so keep it plain.
import { parseCensusXlsx, perMonth, changeSince, levelsFrom } from "./census.client.js";

const T = "Total Private Construction", RES = "Residential (inc. Improvements)", SF = "New single family",
  MF = "New multifamily", NR = "Nonresidential", DC = "Data center", MFG = "Manufacturing",
  OFFGEN = "General", COM = "Commercial (inc. Farm)", PWR = "Power (inc. Gas and Oil)";
const BLUE = "#2a78d6", ORANGE = "#eb6834", AQUA = "#1baf7a", YELLOW = "#eda100";
const INK = "#0b0b0b", INK2 = "#52514e", MUTED = "#8a8985", GRID = "#e6e5e1", SURF = "#fcfcfb";
const GREY = "#6b6a66", HOUSE = "#c9c8c2", COMCOL = "#9a99a8", OTHERNR = "#7c7b86";

const pm = (v, k) => perMonth(v[k]);
const other = (v) => perMonth(v[T] - v[DC]);
const improv = (v) => perMonth(v[RES] - v[SF] - v[MF]);
const nonresRest = (v) => perMonth(v[NR] - v[DC] - v[MFG] - v[OFFGEN] - v[COM] - v[PWR]);
const pct = (v, k, base) => 100 * (v[k] / base[k] - 1);

function esc(s) { return String(s).replace(/&/g, "&amp;").replace(/</g, "&lt;"); }
function text(x, y, s, size = 13, color = INK, anchor = "start", weight = "normal") {
  return "<text x='" + x.toFixed(1) + "' y='" + y.toFixed(1) + "' font-size='" + size + "' fill='" + color +
    "' text-anchor='" + anchor + "' font-weight='" + weight + "'>" + esc(s) + "</text>";
}
function line(x1, y1, x2, y2, color, width = 1) {
  return "<line x1='" + x1.toFixed(1) + "' y1='" + y1.toFixed(1) + "' x2='" + x2.toFixed(1) + "' y2='" + y2.toFixed(1) + "' stroke='" + color + "' stroke-width='" + width + "'/>";
}
function path(pts, color, width = 2, dash = "") {
  const d = "M" + pts.map(([x, y]) => x.toFixed(1) + "," + y.toFixed(1)).join(" L");
  return "<path d='" + d + "' fill='none' stroke='" + color + "' stroke-width='" + width + "' stroke-linejoin='round' " + dash + "/>";
}
function frame(w, h, title, subtitle, body, note) {
  return "<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 " + w + " " + h + "' font-family='Inter,Helvetica,Arial,sans-serif'>" +
    "<rect width='" + w + "' height='" + h + "' fill='" + SURF + "'/>" +
    text(28, 36, title, 20, INK, "start", "600") + text(28, 58, subtitle, 13, INK2) + body +
    text(28, h - 16, note, 11, MUTED) + "</svg>";
}
function yearTicks(series, xof, y) {
  return series.map((p, i) => p.month.startsWith("Jan-") ? text(xof(i), y, "20" + p.month.slice(-2), 12, INK2, "middle") : "").join("");
}
const NOTE = "Source: Census Bureau, Value of Construction Put in Place, private, seasonally adjusted annual rate / 12. Nominal dollars.";

// 0. The chart as the WSJ drew it: dollar differences from Dec 2023, one axis.
export function chartOriginal(t) {
  const W = 900, H = 520, L = 70, R = 210, TOP = 90, B = 70;
  const dc = changeSince(t, "Dec-23", (v) => pm(v, DC));
  const ot = changeSince(t, "Dec-23", other);
  const ymin = -11, ymax = 5.5;
  const xof = (i) => L + i / (dc.length - 1) * (W - L - R);
  const yof = (y) => TOP + (ymax - y) / (ymax - ymin) * (H - TOP - B);
  let body = "";
  for (const g of [-10, -5, 0, 5]) {
    body += line(L, yof(g), W - R, yof(g), g === 0 ? INK : GRID);
    body += text(L - 10, yof(g) + 4, g === 5 ? "$5 billion" : String(g), 12, INK2, "end");
  }
  body += path(dc.map((p, i) => [xof(i), yof(p.value)]), "#1a9bb0", 2.5);
  body += path(ot.map((p, i) => [xof(i), yof(p.value)]), "#6f6f6f", 2.5);
  body += yearTicks(dc, xof, H - B + 22);
  const xe = W - R + 10;
  body += text(xe, yof(dc.at(-1).value) + 4, "Data centers " + dc.at(-1).value.toFixed(1), 13, INK, "start", "600");
  body += text(xe, yof(ot.at(-1).value) + 4, "All other private " + ot.at(-1).value.toFixed(1), 13, INK, "start", "600");
  return frame(W, H, "The chart as published: $ change from December 2023",
    "Reconstructed from the Census file. Same units, same base month, same two lines.", body, NOTE);
}

// 1. Same two series in percent.
export function chartPercent(t) {
  const W = 1000, H = 560, L = 80, R = 200, TOP = 90, B = 70;
  const base = t.rows.find((r) => r.month === "Dec-23").values;
  const dc = changeSince(t, "Dec-23", (v) => pct(v, DC, base));
  const ot = changeSince(t, "Dec-23", (v) => 100 * (other(v) / other(base) - 1));
  const tot = changeSince(t, "Dec-23", (v) => pct(v, T, base));
  const ymin = -25, ymax = 225;
  const xof = (i) => L + i / (dc.length - 1) * (W - L - R);
  const yof = (y) => TOP + (ymax - y) / (ymax - ymin) * (H - TOP - B);
  let body = "";
  for (let g = 0; g <= 225; g += 50) {
    body += line(L, yof(g), W - R, yof(g), g ? GRID : INK);
    body += text(L - 10, yof(g) + 4, g ? "+" + g + "%" : "0", 12, INK2, "end");
  }
  body += path(dc.map((p, i) => [xof(i), yof(p.value)]), AQUA, 2.5);
  body += path(ot.map((p, i) => [xof(i), yof(p.value)]), GREY, 2.5);
  body += path(tot.map((p, i) => [xof(i), yof(p.value)]), BLUE, 2, "stroke-dasharray='5 4'");
  body += yearTicks(dc, xof, H - B + 22);
  const xe = xof(dc.length - 1) + 8, last = t.rows.at(-1).values;
  body += "<circle cx='" + (xe - 8).toFixed(1) + "' cy='" + yof(dc.at(-1).value).toFixed(1) + "' r='4' fill='" + AQUA + "'/>";
  body += text(xe, yof(dc.at(-1).value) + 4, "Data centers " + dc.at(-1).value.toFixed(0) + "%", 13, INK, "start", "600");
  body += text(xe, yof(dc.at(-1).value) + 20, "$" + pm(base, DC).toFixed(1) + "B → $" + pm(last, DC).toFixed(1) + "B a month", 12, INK2);
  body += text(xe, yof(ot.at(-1).value) - 12, "All other private " + ot.at(-1).value.toFixed(0) + "%", 13, INK, "start", "600");
  body += text(xe, yof(ot.at(-1).value) + 4, "$" + other(base).toFixed(0) + "B → $" + other(last).toFixed(0) + "B a month", 12, INK2);
  body += text(xe, yof(ot.at(-1).value) + 20, "Total private " + tot.at(-1).value.toFixed(0) + "% (dashed)", 12, BLUE);
  return frame(W, H, "Same data, as percent change from December 2023",
    "The original plots dollar differences, so a +" + dc.at(-1).value.toFixed(0) + "% line and a " + ot.at(-1).value.toFixed(0) + "% line looked like mirror images.", body, NOTE);
}

// 2. Stacked levels since 2022.
export function chartStack(t) {
  const W = 1000, H = 600, L = 80, R = 230, TOP = 90, B = 70;
  const layers = [
    ["Housing (new + improvements)", (v) => pm(v, RES), HOUSE],
    ["Manufacturing plants", (v) => pm(v, MFG), ORANGE],
    ["Power, gas & oil", (v) => pm(v, PWR), YELLOW],
    ["Commercial & general office", (v) => pm(v, COM) + pm(v, OFFGEN), COMCOL],
    ["Other nonresidential", nonresRest, OTHERNR],
    ["Data centers", (v) => pm(v, DC), AQUA],
  ];
  const rows = levelsFrom(t, "Jan-22", (v) => v);
  const ymax = 160;
  const xof = (i) => L + i / (rows.length - 1) * (W - L - R);
  const yof = (y) => TOP + (ymax - y) / ymax * (H - TOP - B);
  let body = "";
  for (let g = 0; g <= ymax; g += 40) {
    body += line(L, yof(g), W - R, yof(g), GRID);
    body += text(L - 10, yof(g) + 4, g ? "$" + g + "B" : "0", 12, INK2, "end");
  }
  let cum = rows.map(() => 0);
  const legend = [];
  for (const [name, f, col] of layers) {
    const lo = cum;
    cum = cum.map((c, i) => c + f(rows[i].value));
    const up = rows.map((_, i) => xof(i).toFixed(1) + "," + yof(cum[i]).toFixed(1));
    const dn = rows.map((_, i) => xof(i).toFixed(1) + "," + yof(lo[i]).toFixed(1)).reverse();
    body += "<path d='M" + up.concat(dn).join(" L") + "Z' fill='" + col + "' stroke='" + SURF + "' stroke-width='1.5'/>";
    legend.push([name, col, (lo.at(-1) + cum.at(-1)) / 2, cum.at(-1) - lo.at(-1)]);
  }
  body += yearTicks(rows, xof, H - B + 22);
  for (const [name, col, mid, size] of legend) {
    body += "<rect x='" + (W - R + 10) + "' y='" + (yof(mid) - 6).toFixed(1) + "' width='12' height='12' fill='" + col + "'/>";
    body += text(W - R + 28, yof(mid) + 4, name + "  $" + size.toFixed(0) + "B", 12, INK);
  }
  body += text(L, TOP - 8, "$ billions of construction put in place per month", 12, INK2);
  const last = t.rows.at(-1);
  return frame(W, H, "Where data centers sit inside private construction",
    "Data centers are the thin top layer: $" + pm(last.values, DC).toFixed(1) + "B of $" + pm(last.values, T).toFixed(0) + "B a month in " + monthName(last.month) + ". Housing is more than half.", body, NOTE);
}

// 3. Change by component, Dec 2023 to the latest month.
export function chartDecomposition(t) {
  const W = 1000, H = 520, L = 300, TOP = 90;
  const base = t.rows.find((r) => r.month === "Dec-23").values, last = t.rows.at(-1);
  const d = (f) => f(last.values) - f(base);
  const comps = [
    ["Data centers", d((v) => pm(v, DC)), AQUA],
    ["Owner home improvements", d(improv), HOUSE],
    ["Power, gas & oil", d((v) => pm(v, PWR)), YELLOW],
    ["Other nonresidential", d(nonresRest), OTHERNR],
    ["General office", d((v) => pm(v, OFFGEN)), COMCOL],
    ["New multifamily housing", d((v) => pm(v, MF)), HOUSE],
    ["Commercial (retail, warehouse)", d((v) => pm(v, COM)), COMCOL],
    ["New single-family housing", d((v) => pm(v, SF)), HOUSE],
    ["Manufacturing plants", d((v) => pm(v, MFG)), ORANGE],
  ].sort((a, b) => b[1] - a[1]);
  comps.push(["Total private construction", d((v) => pm(v, T)), BLUE]);
  const xmin = -6, xmax = 5, xw = W - L - 60, rowh = 34;
  const xof = (x) => L + (x - xmin) / (xmax - xmin) * xw;
  let body = "";
  for (let g = xmin; g <= xmax; g++) {
    body += line(xof(g), TOP, xof(g), TOP + rowh * comps.length, g ? GRID : INK);
    body += text(xof(g), TOP + rowh * comps.length + 18, g ? (g > 0 ? "+" : "") + g : "0", 12, INK2, "middle");
  }
  comps.forEach(([name, val, col], r) => {
    const y = TOP + r * rowh + 6, total = r === comps.length - 1;
    if (total) body += line(L, y - 4, L + xw, y - 4, GRID);
    const [x1, x2] = [xof(0), xof(val)].sort((a, b) => a - b);
    body += "<rect x='" + x1.toFixed(1) + "' y='" + y + "' width='" + (x2 - x1).toFixed(1) + "' height='" + (rowh - 12) + "' fill='" + col + "' rx='3'/>";
    body += text(L - 12, y + 16, name, 13, INK, "end", total ? "600" : "normal");
    body += text(xof(Math.max(val, 0)) + 8, y + 16, (val >= 0 ? "+" : "") + val.toFixed(1), 12, INK2);
  });
  body += text(L + xw / 2, TOP + rowh * comps.length + 38, "change in $ billions per month, Dec 2023 → " + monthName(last.month), 12, INK2, "middle");
  return frame(W, H, "What actually changed between December 2023 and " + monthName(last.month),
    "Half of the decline in “everything else” is factory construction rolling off its CHIPS/IRA peak; most of the rest is housing.", body, NOTE);
}

// 4. Small multiples in the original's units.
export function chartSmallMultiples(t) {
  const W = 1000, H = 640;
  const base = t.rows.find((r) => r.month === "Dec-23").values;
  const panels = [
    ["Data centers", (v) => pm(v, DC), AQUA],
    ["Manufacturing plants", (v) => pm(v, MFG), ORANGE],
    ["New single-family housing", (v) => pm(v, SF), MUTED],
    ["Commercial (retail, warehouse)", (v) => pm(v, COM), MUTED],
    ["New multifamily housing", (v) => pm(v, MF), MUTED],
    ["General office", (v) => pm(v, OFFGEN), MUTED],
  ];
  const cols = 3, pw = 300, ph = 230, ox = 60, oy = 90, gapx = 20, gapy = 40, ymin = -6, ymax = 5;
  let body = "";
  panels.forEach(([name, f, col], n) => {
    const px = ox + (n % cols) * (pw + gapx), py = oy + Math.floor(n / cols) * (ph + gapy);
    const L = px + 30, R = px + pw - 10, TT = py + 28, BB = py + ph - 24;
    const s = changeSince(t, "Dec-23", f);
    const xof = (i) => L + i / (s.length - 1) * (R - L);
    const yof = (y) => TT + (ymax - y) / (ymax - ymin) * (BB - TT);
    for (let g = ymin; g <= ymax; g += 2) {
      body += line(L, yof(g), R, yof(g), GRID);
      if (n % cols === 0) body += text(L - 8, yof(g) + 4, g ? (g > 0 ? "+" : "") + g : "0", 11, INK2, "end");
    }
    body += line(L, yof(0), R, yof(0), INK);
    body += path(s.map((p, i) => [xof(i), yof(p.value)]), col, 2.5);
    body += "<circle cx='" + xof(s.length - 1).toFixed(1) + "' cy='" + yof(s.at(-1).value).toFixed(1) + "' r='4' fill='" + col + "'/>";
    body += text(px + 30, py + 14, name, 13, INK, "start", "600");
    body += text(px + pw - 10, py + 14, (s.at(-1).value >= 0 ? "+" : "") + s.at(-1).value.toFixed(1) + "  (" + (100 * s.at(-1).value / f(base)).toFixed(0) + "%)", 12, INK2, "end");
    body += yearTicks(s, xof, BB + 16);
  });
  body += text(ox, oy - 10, "change from Dec 2023, $ billions per month, same scale in every panel", 12, INK2);
  return frame(W, H, "The original chart’s units, but with “all other” taken apart",
    "Each panel is one component of private construction. Data centers’ rise sits next to the pieces that fell.", body, NOTE);
}

function monthName(m) {
  const names = { Jan: "January", Feb: "February", Mar: "March", Apr: "April", May: "May", Jun: "June", Jul: "July", Aug: "August", Sep: "September", Oct: "October", Nov: "November", Dec: "December" };
  return names[m.slice(0, 3)] + " 20" + m.slice(-2);
}

// The Census rows themselves, Dec 2023 on, for the columns the charts use.
export function dataTable(t) {
  const cols = [T, RES, SF, MF, NR, "Office", OFFGEN, DC, COM, MFG, PWR];
  const i0 = t.rows.findIndex((r) => r.month === "Dec-23");
  let h = "<table><thead><tr><th>Month</th>" + cols.map((c) => "<th>" + esc(c) + "</th>").join("") + "</tr></thead><tbody>";
  for (const r of t.rows.slice(i0)) {
    h += "<tr><td>" + esc(r.month + r.flag) + "</td>" + cols.map((c) => "<td>" + r.values[c].toLocaleString("en-US") + "</td>").join("") + "</tr>";
  }
  return h + "</tbody></table>";
}

export async function main() {
  const res = await fetch("/privsatime.xlsx");
  if (!res.ok) throw new Error("census file: " + res.status);
  const t = await parseCensusXlsx(await res.arrayBuffer());
  const last = t.rows.at(-1);
  const asof = document.getElementById("asof");
  asof.className = "";
  asof.textContent = "Latest month in the file: " + monthName(last.month) + (last.flag === "p" ? " (preliminary)" : "") + ". " + t.rows.length + " monthly rows, " + t.columns.length + " columns.";
  const put = (id, svg) => { document.getElementById(id).innerHTML = svg; };
  put("chart-original", chartOriginal(t));
  put("chart-percent", chartPercent(t));
  put("chart-stack", chartStack(t));
  put("chart-decomposition", chartDecomposition(t));
  put("chart-small-multiples", chartSmallMultiples(t));
  document.getElementById("data-table").innerHTML = dataTable(t);
}

#!/usr/bin/env python3
"""Reconstruct the WSJ-style chart "Private construction spending relative to
December 2023" (data centers vs. all other private construction) from the
Census Bureau's Value of Construction Put in Place series, and decompose the
"all other" line into its parts.

Source file: https://www.census.gov/construction/c30/xlsx/privsatime.xlsx
  (private construction, seasonally adjusted annual rate, millions of dollars)

The chart's units turn out to be SAAR / 12, i.e. nominal $ billions per month,
as a level difference from Dec 2023. No inflation adjustment, no percentages.

Usage:  python3 reconstruct.py [path/to/privsatime.xlsx]
        (downloads the file to ./privsatime.xlsx if no path is given)
Writes: reconstruction.csv
No third-party dependencies: the xlsx is parsed with zipfile + regex.
"""
import csv, os, re, sys, urllib.request, zipfile

URL = "https://www.census.gov/construction/c30/xlsx/privsatime.xlsx"
BASE = "Dec-23"


def load_sheet(path):
    z = zipfile.ZipFile(path)
    ss = z.read("xl/sharedStrings.xml").decode()
    strs = [re.sub(r"<[^>]+>", "", m) for m in re.findall(r"<si>(.*?)</si>", ss, re.S)]
    sh = z.read("xl/worksheets/sheet1.xml").decode()
    rows = []
    for r in re.findall(r"<row[^>]*>(.*?)</row>", sh, re.S):
        cells = {}
        for m in re.finditer(r'<c r="([A-Z]+)\d+"([^>]*)>(.*?)</c>', r, re.S):
            col, attrs, inner = m.groups()
            v = re.search(r"<v>(.*?)</v>", inner)
            if not v:
                continue
            v = v.group(1)
            cells[col] = strs[int(v)] if 't="s"' in attrs else v
        rows.append(cells)
    return rows


def load_series(path):
    rows = load_sheet(path)
    hdr = rows[3]
    cols = {re.sub(r"\s*_x000D_\s*|\r\n", " ", v).strip(): k for k, v in hdr.items()}
    out = []
    for r in rows[4:]:
        if not r.get("A") or not r.get("B"):
            continue
        month = r["A"].rstrip("pr")  # strip preliminary/revised flags
        vals = {}
        for name, c in cols.items():
            if c == "A":
                continue
            raw = r.get(c)
            vals[name] = float(raw) if raw not in (None, "") else float("nan")
        out.append((month, vals))
    return out[::-1]  # file is newest-first; return chronological


T = "Total Private Construction1"
RES = "Residential (inc. Improvements)2"
SF, MF = "New single family", "New multifamily"
NR, DC, MFG = "Nonresidential", "Data center", "Manufacturing"
OFFGEN, COM, PWR = "General", "Commercial (inc. Farm)", "Power (inc. Gas and Oil)"


def main():
    path = sys.argv[1] if len(sys.argv) > 1 else "privsatime.xlsx"
    if not os.path.exists(path):
        print("downloading", URL, file=sys.stderr)
        urllib.request.urlretrieve(URL, path)
    series = load_series(path)
    base = dict(series)[BASE]
    start = [i for i, (m, _) in enumerate(series) if m == BASE][0]

    def per_month(v, k):
        return v[k] / 12 / 1000  # SAAR $M -> $B per month

    def other(v):  # everything except data centers
        return (v[T] - v[DC]) / 12 / 1000

    def improvements(v):
        return (v[RES] - v[SF] - v[MF]) / 12 / 1000

    def nonres_rest(v):  # nonres excluding data centers and manufacturing
        return (v[NR] - v[DC] - v[MFG]) / 12 / 1000

    fields = ["month", "data_centers", "all_other", "residential", "single_family",
              "multifamily", "improvements", "manufacturing", "office_general",
              "commercial", "power", "nonres_ex_dc_mfg"]
    rows = []
    for m, v in series[start:]:
        rows.append({
            "month": m,
            "data_centers": per_month(v, DC) - per_month(base, DC),
            "all_other": other(v) - other(base),
            "residential": per_month(v, RES) - per_month(base, RES),
            "single_family": per_month(v, SF) - per_month(base, SF),
            "multifamily": per_month(v, MF) - per_month(base, MF),
            "improvements": improvements(v) - improvements(base),
            "manufacturing": per_month(v, MFG) - per_month(base, MFG),
            "office_general": per_month(v, OFFGEN) - per_month(base, OFFGEN),
            "commercial": per_month(v, COM) - per_month(base, COM),
            "power": per_month(v, PWR) - per_month(base, PWR),
            "nonres_ex_dc_mfg": nonres_rest(v) - nonres_rest(base),
        })
    with open("reconstruction.csv", "w", newline="") as f:
        w = csv.DictWriter(f, fieldnames=fields)
        w.writeheader()
        for r in rows:
            w.writerow({k: (f"{x:.3f}" if isinstance(x, float) else x) for k, x in r.items()})

    last_m, last = series[-1]
    print(f"Chart units: $B per month (SAAR/12), nominal, change since {BASE}\n")
    print(f"{'month':8}{'data ctr':>10}{'all other':>11}")
    for r in rows:
        print(f"{r['month']:8}{r['data_centers']:10.2f}{r['all_other']:11.2f}")
    print(f"\nLevels ($B/month) {BASE} -> {last_m}:")
    for k in [T, RES, SF, MF, NR, DC, OFFGEN, COM, MFG, PWR]:
        b, l = per_month(base, k), per_month(last, k)
        print(f"  {k:34} {b:7.1f} -> {l:7.1f}   {l-b:+6.1f}  ({100*(l/b-1):+.0f}%)")
    print(f"\nData centers as share of total private: {100*base[DC]/base[T]:.1f}% -> {100*last[DC]/last[T]:.1f}%")
    print(f"Data centers as share of private nonres: {100*base[DC]/base[NR]:.1f}% -> {100*last[DC]/last[NR]:.1f}%")

    # Self-check against the chart as published (endpoints read off the image):
    # data centers ~ +4.2, all other ~ -10 in the last month.
    if last_m == "Jul-26":
        assert abs(rows[-1]["data_centers"] - 4.24) < 0.05, rows[-1]
        assert abs(rows[-1]["all_other"] + 10.03) < 0.05, rows[-1]
        print("\nself-check vs. published chart endpoints: OK")


if __name__ == "__main__":
    main()

#!/usr/bin/env python3
"""Draw clearer versions of the 'data centers vs. all other private construction'
chart as standalone SVGs (no dependencies). Reads privsatime.xlsx via reconstruct.py.

Outputs charts/*.svg. Render to PNG with render.mjs (playwright).
"""
import os
from reconstruct import load_series, T, RES, SF, MF, NR, DC, MFG, OFFGEN, COM, PWR

series = load_series("privsatime.xlsx")
months = [m for m, _ in series]
i0 = months.index("Dec-23")
start_ctx = months.index("Jan-22")

# palette (dataviz reference instance, light mode)
BLUE, ORANGE, AQUA, YELLOW, MAGENTA, GREEN, VIOLET, RED = (
    "#2a78d6", "#eb6834", "#1baf7a", "#eda100", "#e87ba4", "#008300", "#4a3aa7", "#e34948")
INK, INK2, MUTED, GRID, SURF = "#0b0b0b", "#52514e", "#8a8985", "#e6e5e1", "#fcfcfb"
FONT = "font-family='Inter,Helvetica,Arial,sans-serif'"

def pm(v, k): return v[k] / 12 / 1000                      # $B per month
def other(v): return (v[T] - v[DC]) / 12 / 1000
def improv(v): return (v[RES] - v[SF] - v[MF]) / 12 / 1000
def nonres_rest(v): return (v[NR] - v[DC] - v[MFG] - v[OFFGEN] - v[COM] - v[PWR]) / 12 / 1000

def text(x, y, s, size=13, color=INK, anchor="start", weight="normal", extra=""):
    return f"<text x='{x:.1f}' y='{y:.1f}' font-size='{size}' fill='{color}' text-anchor='{anchor}' font-weight='{weight}' {FONT} {extra}>{s}</text>"

def frame(w, h, title, subtitle, body, note="Source: Census Bureau, Value of Construction Put in Place, private, seasonally adjusted annual rate / 12. Nominal dollars. Jul 2026 preliminary."):
    return (f"<svg xmlns='http://www.w3.org/2000/svg' width='{w}' height='{h}' viewBox='0 0 {w} {h}'>"
            f"<rect width='{w}' height='{h}' fill='{SURF}'/>"
            + text(28, 36, title, 20, INK, weight="600") + text(28, 58, subtitle, 13, INK2)
            + body + text(28, h - 16, note, 11, MUTED) + "</svg>")

def year_ticks(idx_from, idx_to, xof):
    out = ""
    for i in range(idx_from, idx_to + 1):
        if months[i].startswith("Jan-"):
            out += text(xof(i), 0, "20" + months[i][-2:], 12, INK2, "middle")
    return out

def path(pts, color, width=2, dash=""):
    d = "M" + " L".join(f"{x:.1f},{y:.1f}" for x, y in pts)
    return f"<path d='{d}' fill='none' stroke='{color}' stroke-width='{width}' stroke-linejoin='round' {dash}/>"

# ---------- Chart 1: same two series, in percent -------------------------------
def chart_percent():
    W, H = 1000, 560; L, R, TOP, B = 80, 200, 90, 70
    idx = range(i0, len(months))
    base = series[i0][1]
    dc = [100 * (pm(v, DC) / pm(base, DC) - 1) for _, v in series[i0:]]
    ot = [100 * (other(v) / other(base) - 1) for _, v in series[i0:]]
    tot = [100 * (pm(v, T) / pm(base, T) - 1) for _, v in series[i0:]]
    ymin, ymax = -25, 225
    xof = lambda i: L + (i - i0) / (len(months) - 1 - i0) * (W - L - R)
    yof = lambda y: TOP + (ymax - y) / (ymax - ymin) * (H - TOP - B)
    body = ""
    for g in range(0, 226, 50):
        body += f"<line x1='{L}' x2='{W-R}' y1='{yof(g):.1f}' y2='{yof(g):.1f}' stroke='{GRID if g else INK}' stroke-width='1'/>"
        body += text(L - 10, yof(g) + 4, f"{g:+d}%" if g else "0", 12, INK2, "end")
    body += path([(xof(i), yof(dc[j])) for j, i in enumerate(idx)], AQUA, 2.5)
    body += path([(xof(i), yof(ot[j])) for j, i in enumerate(idx)], "#6b6a66", 2.5)
    body += path([(xof(i), yof(tot[j])) for j, i in enumerate(idx)], BLUE, 2, "stroke-dasharray='5 4'")
    body += year_ticks(i0, len(months) - 1, xof).replace("y='0.0'", f"y='{H-B+22}'")
    xe = xof(len(months) - 1) + 8
    body += f"<circle cx='{xe-8:.1f}' cy='{yof(dc[-1]):.1f}' r='4' fill='{AQUA}'/>"
    body += text(xe, yof(dc[-1]) + 4, f"Data centers  {dc[-1]:+.0f}%", 13, INK, weight="600")
    body += text(xe, yof(dc[-1]) + 20, "$2.0B → $6.3B a month", 12, INK2)
    body += text(xe, yof(ot[-1]) - 12, f"All other private  {ot[-1]:+.0f}%", 13, INK, weight="600")
    body += text(xe, yof(ot[-1]) + 4, "$138B → $128B a month", 12, INK2)
    body += text(xe, yof(ot[-1]) + 20, f"Total private  {tot[-1]:+.0f}%  (dashed)", 12, BLUE)
    return frame(W, H, "Same data, as percent change from December 2023",
                 "The original chart plots dollar differences, so a +210% line and a −7% line looked like mirror images.", body)

# ---------- Chart 2: stacked levels, data centers as a slice of the whole -----
def chart_stack():
    W, H = 1000, 600; L, R, TOP, B = 80, 230, 90, 70
    idx = list(range(start_ctx, len(months)))
    layers = [  # bottom to top
        ("Housing (new + improvements)", lambda v: pm(v, RES), "#c9c8c2"),
        ("Manufacturing plants", lambda v: pm(v, MFG), ORANGE),
        ("Power, gas & oil", lambda v: pm(v, PWR), YELLOW),
        ("Commercial & general office", lambda v: pm(v, COM) + pm(v, OFFGEN), "#9a99a8"),
        ("Other nonresidential", nonres_rest, "#7c7b86"),
        ("Data centers", lambda v: pm(v, DC), AQUA),
    ]
    ymax = 160
    xof = lambda i: L + (i - idx[0]) / (idx[-1] - idx[0]) * (W - L - R)
    yof = lambda y: TOP + (ymax - y) / ymax * (H - TOP - B)
    body = ""
    for g in range(0, 161, 40):
        body += f"<line x1='{L}' x2='{W-R}' y1='{yof(g):.1f}' y2='{yof(g):.1f}' stroke='{GRID}'/>"
        body += text(L - 10, yof(g) + 4, f"${g}B" if g else "0", 12, INK2, "end")
    cum = [0.0] * len(idx)
    tops = []
    for name, f, col in layers:
        lo = cum[:]
        cum = [c + f(series[i][1]) for c, i in zip(cum, idx)]
        up = [(xof(i), yof(cum[j])) for j, i in enumerate(idx)]
        dn = [(xof(i), yof(lo[j])) for j, i in enumerate(idx)][::-1]
        d = "M" + " L".join(f"{x:.1f},{y:.1f}" for x, y in up + dn) + "Z"
        body += f"<path d='{d}' fill='{col}' stroke='{SURF}' stroke-width='1.5'/>"
        tops.append((name, col, (lo[-1] + cum[-1]) / 2, cum[-1] - lo[-1]))
    body += year_ticks(idx[0], idx[-1], xof).replace("y='0.0'", f"y='{H-B+22}'")
    xe = W - R + 10
    for name, col, mid, size in tops:
        body += f"<rect x='{xe}' y='{yof(mid)-6:.1f}' width='12' height='12' fill='{col}'/>"
        body += text(xe + 18, yof(mid) + 4, f"{name}  ${size:.0f}B", 12, INK)
    body += text(L, TOP - 8, "$ billions of construction put in place per month", 12, INK2)
    return frame(W, H, "Where data centers sit inside private construction",
                 "Data centers are the thin top layer: $6.3B of $134B a month in July 2026. Housing is more than half.", body)

# ---------- Chart 3: what changed, by component ----------------------------------
def chart_waterfall():
    W, H = 1000, 520; L, TOP = 300, 90
    base, last = series[i0][1], series[-1][1]
    comps = [
        ("Data centers", pm(last, DC) - pm(base, DC), AQUA),
        ("Owner home improvements", improv(last) - improv(base), "#c9c8c2"),
        ("Power, gas & oil", pm(last, PWR) - pm(base, PWR), YELLOW),
        ("Other nonresidential", nonres_rest(last) - nonres_rest(base), "#7c7b86"),
        ("General office", pm(last, OFFGEN) - pm(base, OFFGEN), "#9a99a8"),
        ("New multifamily housing", pm(last, MF) - pm(base, MF), "#c9c8c2"),
        ("Commercial (retail, warehouse)", pm(last, COM) - pm(base, COM), "#9a99a8"),
        ("New single-family housing", pm(last, SF) - pm(base, SF), "#c9c8c2"),
        ("Manufacturing plants", pm(last, MFG) - pm(base, MFG), ORANGE),
    ]
    comps.sort(key=lambda c: -c[1])
    total = pm(last, T) - pm(base, T)
    xmin, xmax = -6, 5
    x0 = L; xw = W - L - 60
    xof = lambda x: x0 + (x - xmin) / (xmax - xmin) * xw
    rowh = 34
    body = ""
    for g in range(xmin, xmax + 1):
        body += f"<line y1='{TOP}' y2='{TOP + rowh*(len(comps)+1)}' x1='{xof(g):.1f}' x2='{xof(g):.1f}' stroke='{GRID if g else INK}'/>"
        body += text(xof(g), TOP + rowh * (len(comps) + 1) + 18, f"{g:+d}" if g else "0", 12, INK2, "middle")
    for r, (name, val, col) in enumerate(comps + [("Total private construction", total, BLUE)]):
        y = TOP + r * rowh + 6
        if r == len(comps):
            body += f"<line x1='{x0}' x2='{x0+xw}' y1='{y-4}' y2='{y-4}' stroke='{GRID}'/>"
        x1, x2 = sorted((xof(0), xof(val)))
        body += f"<rect x='{x1:.1f}' y='{y}' width='{x2-x1:.1f}' height='{rowh-12}' fill='{col}' rx='3'/>"
        body += text(x0 - 12, y + 16, name, 13, INK, "end", "600" if r == len(comps) else "normal")
        lx = xof(max(val, 0)) + 8
        body += text(lx, y + 16, f"{val:+.1f}", 12, INK2, "start")
    body += text(x0 + xw / 2, TOP + rowh * (len(comps) + 1) + 38, "change in $ billions per month, Dec 2023 → Jul 2026", 12, INK2, "middle")
    return frame(W, H, "What actually changed between December 2023 and July 2026",
                 "Half of the decline in “everything else” is factory construction rolling off its CHIPS/IRA peak; most of the rest is housing.", body)

# ---------- Chart 4: small multiples, same units as the original -----------------
def chart_small_multiples():
    W, H = 1000, 640
    base = series[i0][1]
    panels = [
        ("Data centers", lambda v: pm(v, DC), AQUA),
        ("Manufacturing plants", lambda v: pm(v, MFG), ORANGE),
        ("New single-family housing", lambda v: pm(v, SF), "#8a8985"),
        ("Commercial (retail, warehouse)", lambda v: pm(v, COM), "#8a8985"),
        ("New multifamily housing", lambda v: pm(v, MF), "#8a8985"),
        ("General office", lambda v: pm(v, OFFGEN), "#8a8985"),
    ]
    cols, rows = 3, 2
    pw, ph = 300, 230; ox, oy = 60, 90; gapx, gapy = 20, 40
    ymin, ymax = -6, 5
    body = ""
    for n, (name, f, col) in enumerate(panels):
        px = ox + (n % cols) * (pw + gapx); py = oy + (n // cols) * (ph + gapy)
        L, R, TT, BB = px + 30, px + pw - 10, py + 28, py + ph - 24
        xof = lambda i: L + (i - i0) / (len(months) - 1 - i0) * (R - L)
        yof = lambda y: TT + (ymax - y) / (ymax - ymin) * (BB - TT)
        for g in range(ymin, ymax + 1, 2):
            body += f"<line x1='{L}' x2='{R}' y1='{yof(g):.1f}' y2='{yof(g):.1f}' stroke='{GRID}'/>"
            if n % cols == 0:
                body += text(L - 8, yof(g) + 4, f"{g:+d}" if g else "0", 11, INK2, "end")
        body += f"<line x1='{L}' x2='{R}' y1='{yof(0):.1f}' y2='{yof(0):.1f}' stroke='{INK}'/>"
        vals = [f(v) - f(base) for _, v in series[i0:]]
        body += path([(xof(i0 + j), yof(y)) for j, y in enumerate(vals)], col, 2.5)
        body += f"<circle cx='{xof(len(months)-1):.1f}' cy='{yof(vals[-1]):.1f}' r='4' fill='{col}'/>"
        body += text(px + 30, py + 14, name, 13, INK, weight="600")
        body += text(px + pw - 10, py + 14, f"{vals[-1]:+.1f}  ({100*vals[-1]/f(base):+.0f}%)", 12, INK2, "end")
        for i in range(i0, len(months)):
            if months[i].startswith("Jan-"):
                body += text(xof(i), BB + 16, "20" + months[i][-2:], 11, INK2, "middle")
    body += text(ox, oy - 10, "change from Dec 2023, $ billions per month, same scale in every panel", 12, INK2)
    return frame(W, H, "The original chart’s units, but with “all other” taken apart",
                 "Each panel is one component of private construction. Data centers’ +4.2 sits next to the pieces that fell.", body)

os.makedirs("charts", exist_ok=True)
for name, fn in [("1-percent", chart_percent), ("2-stacked-levels", chart_stack),
                 ("3-decomposition", chart_waterfall), ("4-small-multiples", chart_small_multiples)]:
    with open(f"charts/{name}.svg", "w") as f:
        f.write(fn())
    print("wrote charts/%s.svg" % name)