The color of education: Six years’ worth of school data anchors report

Using six school years’ worth of data from the state education office, Dahlia Bazzaz from The Seattle Times and Katie Gillespie from The Columbian analyzed the widening racial mismatch between Washington’s public-school students and their teachers.

Here’s how we produced the resulting story, map and graphics.

How did you obtain the data you used in your analysis?

Our two main sources of data — a database of student demographics and a database of teacher demographics, each broken down by district — came from the Office of Superintendent of Public Instruction. The agency included information for most districts, which are required to produce an annual report of student and staff data, including their self-identified race and ethnicity.

Student enrollment data going back several years are available on OSPI’s website. But we had to request the teacher information from the agency.

How was the data modified for your reporting?

To create a database that would allow us to look at individual districts and review six school years of demographic data, we had to manually combine more than a dozen spreadsheets using a database manager: First, we merged all the years of student data into one big file using Structured Query Language, or SQL. We did the same with teacher data. Then we combined those two files to create a master list.

In our parity calculations and total tally of teachers and students, we decided only to count districts and educational facilities that reported data for both teachers and students. For the 2017-2018 school year, we did not have corresponding teacher demographic data for seven locations, which together serve roughly 3,200 students. Those were mostly charter schools and state-run facilities, like the Washington Military Department’s Youth Academy. We thought it wouldn’t be fair to add these students into the analysis if we couldn’t count their teachers.

What is missing in this data?

Lots of nuance that the federal government’s race and ethnicity reporting standards cannot capture. We know, for example, that people of Native American and Alaskan Native descent are routinely undercounted in population surveys, so the actual number of Native students and teachers could be much higher. This particular set of data is further complicated by the fact that marking “Hispanic/Latino” or identifying with more than one race excludes an individual from being counted in other categories.

The teacher data also only reflects classroom teachers with state teaching licenses, and excludes others — including instructional assistants, librarians and counselors — who may also spend considerable time with students.