u/awongpublic

Prompted from a comment by u/Nomadic_Houseplant that their 2nd grade was going to have a class ratio of 28:1, far above the WSS ratio, it occurred to me that folks might want to dice their own purplebook data. In the past, I've written custom parsers, but that's not super accessible. So let's see how to do this with Claude really fast!

First, you download the purplebooks you want from SPS's website. I usually seach "SPS Purple book 2026" and the first link has it.

Then I opened up claude and uploaded the file gave it this prompt:

This is the "Purple book" for SPS district.  After the intro text, it is a 
repeated format listing funding and staffing allocations per school.

I want you to go through all Elementary Schools (first section of schools) and 
for each school:
  (1) Projected enrollment for K-3, and 4-5 cohorts
  (2) find #FTE of Teacher K-3 and #FTE of Teacher 4-5
  (3) Produce me a table of # students per FTE for both cohorts for each school.

Sort the table in order of highest # students per FTE.

This produced a table like the following

Initial Table view of Elementary ratios based on Claude data

Next, I downloaded the two prior year purple books and uploaded them with the following prompt

here are the last 2 years data. Please add to table for comparisons with the current
year. Sorting should be done by current year data.

and yes, I don't bother copy editing my prompts. (It's not really understanding the grammar directly anyways :P). That created an artifact the following where you can see ratios over the years. It's informative to look at best and worst average student teacher ratio. Here it is sorted by worst ratio

3 years of purplebook ratio data put side by side sorted by worst student/teacher ratio in K-3

Here it is sorted by best ratio where you can see shuffling around of FTE along with some dramatic improvements in certain schools

3 years of purplebook ratio data put side by side sorted by best student/teacher ratio in K-3

Of course some of this seems nonsensical. The original comment prompting this was citing two 28-children classrooms this year in the allocation model. So, let's ask claude to do more. Next prompt was

For each school in each year, attempt to assign the teacher FTE to the grade-levels
of students.  Create a table of the results and highlight any situation that ends
up causing a grade to get over target ratio listed in their purplebook.

This produced a graph that is starting to show some interesting things but which also has numbers that seem insane (48 students to one teacher in North Beach 4th grade? No way)

AI Attempt to assign teachers to classes to produce class-ratios per grade

So I fed this back into claude and had a dialogue with 4 more prompts (mid-point artifact elided)

some of these ratios seem impossibly high. 48:1 and 45:1 seem impossible. Update your
assignment logic. Also ensure that the total assigned per grade sums to the right FTE
from the purplebook for the school.

does that mean they have to create a split class?

can you update the highlight background to get more intense as the over-targeting gets
worse?  And can you ensure you are using a color-blind friendly colorscheme?

make this into a single standalone html publishable artifact

Add big caveat that is an AI guess and may be entirely wrong.

The final result was a graph that looked this like the following:

Final chart of AI assignments for grade levels

Some of these still seem insanely high (eg 39:1 in Concord for 5th grade), but as an exploration tool, it can guide focus for questions.

Anyways... point is, if you want to crunch data, you can use AI to help produce these things. But, unless you've verified it is doing the right thing, use it as a way to think of new questions instead of as a definitive analysis. Cause I really don't think we have so many 36:1 teaching ratios across the board.

Here is the final artifact with the AI attempt to assign teacher to grades (low confidence in correctness):
https://claude.ai/public/artifacts/44b7b0aa-2c8e-496b-8e34-ed99af0cefb5

Here is the initial Visual Table for just one year (medium confidence in correctness):
https://claude.ai/public/artifacts/a86bbe47-f5ec-4b62-8c92-62a81e06975c

And here's the multi-year variant:
https://claude.ai/public/artifacts/b16138de-a86d-4998-b2ba-ea99254fad13

Somehow the cellphone policy got the airtime out of the April 29th meeting, but for me the far more wide-reaching and rich presentation was the "Top 5 in 5" goal framework and the related North Star of

>One North Star: Make SPS the best urban school system in America — where any family can confidently enroll any student and expect a district that is top five in performance within five years, with performance visible across every student group.

Now clearly we're not there and I'm sure there's a bunch of snark that people can throw at this, but at the same time, this is the first time I've seen such a crisp set of goals presented.

Curious folks think?

Also, just as a person note, this is the first time I've *ever* seen the district actually present the General Fund Balance actuals displaying that the Actuals deficit is "only" about 40M, 20M, 30M each year instead of the inflated 100M budget deficit they keep citing.

Screenshot of last slide in dec showing Fund Balance from FY22 to FY25 and projections to FY31

If you want to compare to the SPS by the numbers charts, look at the Vitals dashboard for Beginning Balance and zoom in.

Screenshot of SPS By The Numbers Vitals dashboard for Beginning Balance showing the same numbers. 2023 here is FY22 in the SPS presentation.

This is heartening for me to see the real numbers show up.

And not to tangent too far away from the 5x5 goals, but one thing that worries me still about the fund balance is that it does indeed swing by ~100M from the high in Sept to the low. in June. So looking at the Feb 2026 report page 4 chart, we are not in great shape.

Feb 2026 Fiscal report chart of General Fund balance by month. June low is the balance low-point. 2 years ago it was ~40M. Last year it was ~20M. This year consistently ~15M lower or more at every data point. What will happen?

I wonder what Jun will look like. We're skirting $0.

[ARGH Title should have said SBAC 5th Math... and now I can't change it.]

I made some clean-ups to the rushed-out Assessment dashboard (thanks u/DistanceRude9275 for the feedback! super helpful) and figured I'd show how to use the thing a bit.

Overall, there is just way too much data to scan it easily. You have to group by schools to make any sense, and then likely use "Add comparison" to look at things side-by-side. So as a working example, and because it directly relates to the goals presented tomorrow + the community discussion of the past long while, here's how I would take a first cut look at the score performance of students identifying as Black/AA versus All students at a district wide level.

First, Toggle the "Render Charts" switch that I just added to Off. There's too much data so if you don't do this, every click costs 30s.

Second, change the graph facet from "School" to the newly added "Middle School Area" which groups schools by which Middle School Attendance Area they are geographically located in (screenshot of map at end).

Like breaking down by Closure Region, it doesn't always match what I think of as seattle subdivisions, but it is much closer to representing the geographic divides than just "NW/NE/Central/SW/SE."

Third, reduce the Test + Grades to just show 1 thing for one demographic. I'll use SBAC Math for 5th for Black/AA students. This will make the sort order more reflective of the data you want

Fourth, and here's the key, click "Add Comparison" on the lower eft side to create a second column of charts to look at. Then in the left, change the demographic from "Black/AA" to All Students.

Fifth, go to Disclosure Avoidance and select "Drop". We will revisit in a sec.

Now toggle "Render charts" back on and you'll get a graph like this:

Screenshot of tool showing comparison 5th SBAC Math % Met Standard by school, faceted by \"Middle School Attendance Area.\" It highlights the divergent performance of the demographic group when comparing left and right columns. Missing data on the left also how many schools do not even have enough of the Black/AA demographic to produce a result. This highlights the city's segregation sharply.

with a complex URL that you can pass around encoding your settings:

https://www.sps-by-the-numbers.com/finance/assessments?d=c.17001~g.spsbtn~cn.amount~sn.fte~s.QA~gl.IABA~ta.YAP4~sg.IACA~ts.YAXw&d=c.17001~g.spsbtn~cn.amount~sn.fte~s.QA~gl.IABA~ta.YAP4~sg.IAQA~ts.YAXw&c=f.5~l.0~so.d~st.1~so.d~st.1~ys.0~csg.0~cy.1~da.1

Key things to notice. First, the left side has a bunch of missing data. This is because, when the number of students within a category (grade, subject, year, school, demographic) is too small. OSPI suppresses the data. Since we selected Black/AA at 5th grade, barring pass opt-out of testing, this visually shows when a school just has very very few Black/AA kids in 5th for a year.

Now if this seems improbably sparse for Aki Kurose, you're partially right! The "Disclosure Avoidance" setting to Drop is aggressive. When the # of students is small, there are multiple levels of suppression. The first is to change the % to a fudged bound. So it might say ">95.3%" of all Black/AA students met standard. That could be 99.999% or it could be 95.31%. We don't know. If you select "Drop" all these data points are removed. Switching it to "Best Guess" we see a plot like this

Same graph as above but now with \"Best Guess\" Disclosure Avoidance. It shows more data being possibly inaccurate

The graph looks fuller on the left now but some of those data points are bounds, not actual data which makes it harder to interpret. Also, arguably, if there is a low enough population of a demographic to require Disclosure Avoidance Techniques, the impression of the first graph with "Drop" of that population being very isolated in that school is probably correct.

Anyways, moving on... the next thing to notice of course is how the scores on left column visually band at a lower point than the right column even across the COVID disruption. This is achievement gap that we have been able to solve as a community forever.

Lastly, scroll down the charts and you will see VERY quickly that the left column starts getting blanker and blanker. By rows 3 and 4, you start to see that we mostly cannot get enough data, even with Best Guess, to chart on the left.

Rows 3 and 4 of Middle School Area facet start having very few datapoints on the Black/AA column.

In fact, 6-7 of the 12 middle school areas have effectively blank graphs on the left. I grab a subsample here cause it's too hard to screenshot it all

Subsample of areas where you can see many attendance areas just don't have sufficient Black/AA students for test scores

This isn't to reopen some huge soul-searching debate about redlining etc (really had enough of that right now) but to remind folks that policy which isn't aware of this divide is gonna not do the right thing. Specifically, if you do any "all schools will XXXX to help YYY" kind of thing, well, double check that YYY actually attend most schools. Cause if I had to make a major criticism at a lot of advocacy and policy badness for the past decade, it's been folks trying to make district wide changes for the benefit of Black/AA (really African American Males per strategic plan) without recognizing and adjusting for that fact that over 50% those efforts would be touching places that rather literally cannot reach the intended students.

Anyways, have fun with the data dashboard. And post links + screenshots to anything interesting. You should be able compare across grades within a school. Or maybe do things like facet by grade and compare vs other districts, etc.

And continue to give me bugs. I will fix as I get time.

Here's the middle school attendance area chart for reference:

SPS 2025-26 Middle School Attendance Area Map