(Un)Confirmed Appointments Database:
Actings Tracker

← Back to KAPI Lab

Explore data on presidential use of acting appointees and vacancies across federal departments and agencies

⚠️ UNDER CONSTRUCTION ⚠️

This dashboard is a design preview populated with simulated placeholder data; it does not show real records. The actual dataset will replace it when it is ready for public release. Watch this space.

data = FileAttachment("../data/actings.json").json()

admins = ["All", ...new Set(data.map(d => d.administration))]
depts = ["All", ...new Set(data.map(d => d.department)).values()].sort()
levels = ["All", ...new Set(data.map(d => d.position_level)).values()].sort()

viewof adminFilter = Inputs.select(admins, {label: "Administration", value: "All"})
viewof deptFilter = Inputs.select(depts, {label: "Department", value: "All"})
viewof levelFilter = Inputs.select(levels, {label: "Position Level", value: "All"})

filtered = data.filter(d =>
  (adminFilter === "All" || d.administration === adminFilter) &&
  (deptFilter === "All" || d.department === deptFilter) &&
  (levelFilter === "All" || d.position_level === levelFilter)
)

totalPositions = filtered.length
actingCount = filtered.filter(d => d.status === "Acting").length
vacantCount = filtered.filter(d => d.status === "Vacant").length
avgDays = filtered.length > 0
  ? Math.round(filtered.reduce((sum, d) => sum + d.days_served, 0) / filtered.length)
  : 0

html`<div class="stat-grid">
  <div class="stat-box">
    <div class="stat-number">${totalPositions}</div>
    <div class="stat-label">Total Positions Tracked</div>
  </div>
  <div class="stat-box stat-terracotta">
    <div class="stat-number">${actingCount}</div>
    <div class="stat-label">Filled by Actings</div>
  </div>
  <div class="stat-box stat-rose">
    <div class="stat-number">${vacantCount}</div>
    <div class="stat-label">Left Vacant</div>
  </div>
  <div class="stat-box">
    <div class="stat-number">${avgDays}</div>
    <div class="stat-label">Avg. Days as Acting</div>
  </div>
</div>`

// Aggregate data for stacked bar by department
deptStatusData = {
  const depts = [...new Set(filtered.map(d => d.department))];
  const statuses = ["Acting", "Confirmed", "Vacant"];
  const result = [];
  for (const dept of depts) {
    for (const status of statuses) {
      const count = filtered.filter(d => d.department === dept && d.status === status).length;
      if (count > 0) {
        result.push({ department: dept, status, count });
      }
    }
  }
  return result;
}

// Aggregate data for timeline by quarter
timelineData = {
  const result = [];
  const quarters = {};

  for (const d of filtered) {
    const date = new Date(d.start_date);
    const q = Math.floor(date.getMonth() / 3) + 1;
    const key = `${date.getFullYear()}-Q${q}`;

    if (!quarters[key]) {
      quarters[key] = { quarter: key, date: new Date(date.getFullYear(), (q - 1) * 3, 1), Acting: 0, Vacant: 0 };
    }
    if (d.status === "Acting") quarters[key].Acting++;
    else if (d.status === "Vacant") quarters[key].Vacant++;
  }

  for (const key of Object.keys(quarters).sort()) {
    result.push({ date: quarters[key].date, count: quarters[key].Acting, series: "Acting" });
    result.push({ date: quarters[key].date, count: quarters[key].Vacant, series: "Vacant" });
  }
  return result;
}

Position Status by Department

Plot.plot({
  marginLeft: 160,
  marginRight: 20,
  height: Math.max(300, deptStatusData.length * 8),
  x: { label: "Count" },
  y: { label: null },
  color: {
    domain: ["Acting", "Confirmed", "Vacant"],
    range: ["#C06840", "#6B8F6B", "#B85C5C"],
    legend: true
  },
  marks: [
    Plot.barX(deptStatusData, {
      y: "department",
      x: "count",
      fill: "status",
      sort: { y: "-x" }
    }),
    Plot.ruleX([0])
  ]
})

Acting Appointees Over Time

Plot.plot({
  marginLeft: 50,
  height: 300,
  x: { label: "Quarter", type: "utc" },
  y: { label: "Count" },
  color: {
    domain: ["Acting", "Vacant"],
    range: ["#C06840", "#B85C5C"],
    legend: true
  },
  marks: [
    Plot.line(timelineData, {
      x: "date",
      y: "count",
      stroke: "series",
      strokeWidth: 2
    }),
    Plot.dot(timelineData, {
      x: "date",
      y: "count",
      fill: "series",
      r: 3
    })
  ]
})

Linked Nomination for an Acting Spell

Preview. For an acting spell, this links to the nomination pending during (or around) that service. The acting official is not the nominee; they are matched by office and overlapping dates. Numbers are illustrative until the acting dataset is released. Computed by scripts/06_link_actings.py.

actingLinks = FileAttachment("../data/actings_nomination_links.json").json()
linkedActings = actingLinks.by_acting.filter(a => a.nomination_link)

viewof selectedActing = Inputs.select(linkedActings, {
  label: "Acting spell",
  format: a => `${a.name}, ${a.position_title} (${a.department}) ${a.start_date}–${a.end_date || "ongoing"}`
})

{
  const a = selectedActing;
  if (!a || !a.nomination_link) return html`<em style="color:#7A8290">No linked acting spell selected.</em>`;
  const nl = a.nomination_link;

  const parse = s => s ? new Date(s + "T00:00:00") : null;
  const today = new Date();
  const nomStart = parse(nl.date_submitted);
  const nomEnd = parse(nl.date_resolved) || today;
  const actStart = parse(a.start_date);
  const actEnd = parse(a.end_date) || today;

  let minD = nomStart, maxD = nomEnd;
  for (const d of [actStart, actEnd]) { if (d && d < minD) minD = d; if (d && d > maxD) maxD = d; }
  const pad = Math.max(1, (maxD - minD)) * 0.06;
  const width = 720, topPad = 18, rowH = 30, rows = 2;
  const height = topPad + rows * rowH + 34;
  const margin = { left: 150, right: 20 };
  const x = d3.scaleTime()
    .domain([new Date(+minD - pad), new Date(+maxD + pad)])
    .range([margin.left, width - margin.right]);

  const svg = d3.create("svg")
    .attr("viewBox", [0, 0, width, height])
    .attr("width", width).style("max-width", "100%").style("height", "auto")
    .style("font-family", "Nunito Sans, sans-serif");

  svg.append("g")
    .attr("transform", `translate(0,${topPad + rows * rowH + 6})`)
    .call(d3.axisBottom(x).ticks(6).tickFormat(d3.timeFormat("%b %Y")))
    .call(g => g.selectAll("text").attr("font-size", "10px").attr("fill", "#7A8290"))
    .call(g => g.selectAll("line,path").attr("stroke", "#E6DDD0"));

  // Nomination window (navy)
  svg.append("rect")
    .attr("x", x(nomStart)).attr("y", topPad)
    .attr("width", Math.max(2, x(nomEnd) - x(nomStart))).attr("height", 18)
    .attr("rx", 3).attr("fill", "#1E2D4F").attr("opacity", 0.85)
    .append("title").text(`Nomination pending: ${nl.date_submitted} → ${nl.date_resolved || "ongoing"} (${nl.status})`);
  svg.append("text").attr("x", margin.left - 8).attr("y", topPad + 13).attr("text-anchor", "end")
    .attr("font-size", "11px").attr("font-weight", "700").attr("fill", "#1E2D4F").text("Nomination");

  // Acting spell (terracotta)
  const ay = topPad + rowH;
  svg.append("rect")
    .attr("x", x(actStart)).attr("y", ay)
    .attr("width", Math.max(2, x(actEnd) - x(actStart))).attr("height", 18)
    .attr("rx", 3).attr("fill", "#C06840").attr("opacity", 0.85)
    .append("title").text(`Acting: ${a.name} · ${a.start_date} → ${a.end_date || "ongoing"} (${a.status})`);
  svg.append("text").attr("x", margin.left - 8).attr("y", ay + 13).attr("text-anchor", "end")
    .attr("font-size", "10px").attr("fill", "#C06840")
    .text(a.name.length > 20 ? a.name.slice(0, 19) + "…" : a.name);

  return svg.node();
}

{
  const a = selectedActing;
  if (!a || !a.nomination_link) return html``;
  const nl = a.nomination_link;
  return html`<div>
    <div class="stat-grid" style="grid-template-columns:repeat(4,1fr);margin-top:1rem">
      <div class="stat-box stat-terracotta"><div class="stat-number">${nl.overlap_days}</div><div class="stat-label">Days nominee waited while acting served</div></div>
      <div class="stat-box"><div class="stat-number" style="font-size:1.15rem">${nl.status_during}</div><div class="stat-label">Nomination status during acting</div></div>
      <div class="stat-box"><div class="stat-number">${nl.days_before_submission}</div><div class="stat-label">Acting days before submission</div></div>
      <div class="stat-box stat-rose"><div class="stat-number">${nl.days_after_resolution}</div><div class="stat-label">Acting days after return/withdrawal</div></div>
    </div>
    <p style="margin-top:1rem;color:#4F5B6E">Linked nominee: <strong>${nl.nominee_name}</strong>, ${nl.position} (${nl.administration}, ${nl.citation}, ${nl.status}). <a href="nominations.qmd" style="color:#C06840;font-weight:600">View on the Nominations Tracker →</a></p>
  </div>`;
}

Data Table

viewof searchActings = Inputs.text({ placeholder: "Search by name...", width: 300 })

tableFiltered = filtered.filter(d =>
  d.name.toLowerCase().includes(searchActings.toLowerCase())
)

Inputs.table(tableFiltered, {
  columns: ["name", "position_title", "department", "administration", "status", "days_served"],
  header: {
    name: "Name",
    position_title: "Position",
    department: "Department",
    administration: "Administration",
    status: "Status",
    days_served: "Days Served"
  },
  sort: "days_served",
  reverse: true,
  rows: 20
})

Note: Sample data is shown for demonstration purposes. This data is mock/simulated and does not represent actual appointee records.