//Test score aggregate data from Ohio Dept. of Ed.
perform = FileAttachment("21-22_Achievement_Building.xlsx").xlsx()
achievement = aq.from(perform.sheet("Performance_Index", { headers: true, range: "A1:T" }))
Cuyahogaschoolbuildings_geocodio_28dcf9ec4dd1972c5d302583407ee8890e5b4448@1.csv
Type Table, then Shift-Enter. Ctrl-space for more options.
/* Geocoded building data for all schools in CMSD, downloaded from Ohio Dept. of Ed., filtered and geocoded. Building names edited slightly to match attendance data from CMSD. */
buildings = aq.from(cuyahogaschoolbuildings_geocodio_28dcf9ec4dd1972c5)
// This is the attendance data for a single day in November 2022, obtained by Paul.
sy2023StudentZipCodeCountsBySchool20221101 = FileAttachment("SY 2023 student zip code counts by school 2022-11-01.xlsx").xlsx()
attendance = aq.from(sy2023StudentZipCodeCountsBySchool20221101.sheet(0, { headers: true, range: "A1:C" }))
Type JavaScript, then Shift-Enter. Ctrl-space for more options. Arrow ↑/↓ to switch modes.
// Joining building location data with performance measures from the Ohio Department of Education.
schools = achievement.join_left(buildings, ["Building IRN", "IRN"])
// Joining CMSD's zip code data with performance and building location data.
mash = attendance.join_left(schools, ["SCHOOL", "ORGANIZATION NAME"])
/* Generating a column that says "same" if the child lives in the same zip code as their school, and "different" if they don't. */
mash2 = mash
.derive({
samezip: aq.escape((d) => (d.ZIP == d.Zip ? "Same" : "Different"))
})
// Calculating the total number of kids in and outside the school's zip code for each school.
localstudents = mash2
// .filter((d) => d.SCHOOL != "Cleveland Metro Remote School")
.select("SCHOOL", "Building IRN", "SCHOOL TYPE", "Achievement Component Star Rating", "STUDENTS", "samezip")
.groupby("SCHOOL", "samezip")
.derive({
total: (row) => aq.op.sum(row.STUDENTS)
})
.groupby("SCHOOL", "Building IRN", "SCHOOL TYPE", "Achievement Component Star Rating")
.pivot("samezip", "total")
.derive({
local: (row) => row.Same * 100 /(row.Different + row.Same)
})
localstudents
Type Table, then Shift-Enter. Ctrl-space for more options.
localstudents
Type Table, then Shift-Enter. Ctrl-space for more options.
// Calculating the total number of kids attending schools in their own zip code.
localstudents2 = mash2
// .filter((d) => d.SCHOOL == "Cleveland Metro Remote School HS")
.groupby("samezip")
.derive({
total: (row) => aq.op.sum(row.STUDENTS)
})
.select("samezip", "total")
.derive({
pct: (row) => row.total * 100/(22001+11969)
})
.dedupe().view()
// This calculates the number and percentage of elementary school students
elemstudents = mash2
.filter((d) => d["SCHOOL TYPE"] == "Elementary School")
.groupby("samezip")
.derive({
total: (row) => aq.op.sum(row.STUDENTS)
})
.select("samezip", "total").dedupe()
.derive({
pct: (row) => row.total * 100/(13514+9601)
}).view()
// This calculates the percentage inside and outside the zip code for high schools.
highstudents = mash2
.filter((d) => d["SCHOOL TYPE"] == "High School")
.groupby("samezip")
.derive({
total: (row) => aq.op.sum(row.STUDENTS)
})
.select("samezip", "total").dedupe()
.derive({
pct: (row) => row.total * 100/(8019+2300)
}).view()
/* This breaks out calculates by both school type/level and achievement star rating assigned by the Ohio Dept of Ed. */
achievestudents = mash2
// Taking out the virtual high school.
.filter((d) => d.SCHOOL != "Cleveland Metro Remote School HS")
// .filter((d) => d.SCHOOL == "Cleveland Early College High School")
.select("SCHOOL", "Building IRN", "SCHOOL TYPE", "Achievement Component Star Rating", "STUDENTS", "samezip")
.groupby("SCHOOL TYPE", "Achievement Component Star Rating", "samezip")
.derive({
total: (row) => aq.op.sum(row.STUDENTS)
})
.groupby("SCHOOL TYPE", "Achievement Component Star Rating")
.pivot("samezip", "total")
.derive({
local: (row) => row.Same * 100 /(row.Different + row.Same)
})
Inputs.table(achievestudents)
ziptotals = attendance
.select("ZIP","STUDENTS")
.groupby("ZIP")
.derive({
total: (row) => aq.op.sum(row.STUDENTS)
})
.select("ZIP","total").dedupe()
Select a data source…
Type Table, then Shift-Enter. Ctrl-space for more options.
attendance
Type Table, then Shift-Enter. Ctrl-space for more options.
import { aq, op } from "@uwdata/arquero"
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.