Public
Edited
Paused
Importers
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
vl.markBoxplot()
.encode(
vl.x().mean("median_PUNT_GLOBAL"),
vl.y().fieldN("COLE_DEPTO_UBICACION").sort(vl.mean("median_PUNT_GLOBAL")),
)
.data(cached_medians)
.render()
Insert cell
import {beeswarmY} from "@fil/experimental-plot-beeswarm"
Insert cell
colegiosWithEnoughStudents = colegios
.filter((c) => c.count >= minStudents)
.map((c) => c.colegio)
Insert cell
// `${JSON.stringify(selCol).replaceAll('"', "'")}.indexof(datum.label)!=-1`
axisTestExpr = selCol.length < 100
? `indexof(${JSON.stringify(selCol)}, datum.label)!=-1`
: "true"
Insert cell
Insert cell
function simplifyName(d) {
return {
...d,
colegio: d["COLE_NOMBRE_ESTABLECIMIENTO"]
.replace("COLEGIO", "COL")
.replace("INSTITUCION EDUCATIVA", "IE")
};
}
Insert cell

import {vl} from "@vega/vega-lite-api-v5"
Insert cell
// codDane = {
// const fmt = d3.dsvFormat(";");
// return FileAttachment("COD_DANE_DIVIPOLA_CentrosPoblados.csv").text().then(txt => fmt.parse(txt))
// }
Insert cell
puntajesCols = [
"PUNT_GLOBAL",
"PUNT_LECTURA_CRITICA",
"PUNT_MATEMATICAS",
"PUNT_C_NATURALES",
"PUNT_SOCIALES_CIUDADANAS",
"PUNT_INGLES",
"PERCENTIL_LECTURA_CRITICA",
"PERCENTIL_MATEMATICAS",
"PERCENTIL_C_NATURALES",
"PERCENTIL_SOCIALES_CIUDADANAS",
"PERCENTIL_INGLES",
"PERCENTIL_ESPECIAL_GLOBAL",
"PERCENTIL_GLOBAL",
"DESEMP_LECTURA_CRITICA",
"DESEMP_MATEMATICAS",
"DESEMP_C_NATURALES",
"DESEMP_SOCIALES_CIUDADANAS",
"DESEMP_INGLES"
]

//Object.keys(selected[0]).filter(c => c.startsWith("PUNT"))
Insert cell
Insert cell
puntajesCols
.filter((d) => d.startsWith("PUNT"))
.map(
(d) => `median(${d}) OVER withGender as median_genero_${d},
median(${d}) OVER noGender as median_${d}`
).join("\n")
Insert cell
puntajesCols
.filter((d) => d.startsWith("PUNT"))
.map(
(d) => `first(median_genero_${d}) as median_genero_${d},
first(median_${d}) as median_${d},`
).join("\n")
Insert cell
navio([...selected.map(d => ({...d}))])
Insert cell
import {navio} from "@john-guerra/navio"
Insert cell
url = "https://www.datos.gov.co/resource/kgxf-xxbe.json"
Insert cell
import {loadSocrata} from "@john-guerra/socrata-load-multiples-pages"
Insert cell
// We need something to boot the SQL cell (a data array or a databaseClient)
db = DuckDBClient.of({
saber: FileAttachment("resultados_2021_2023.parquet")

// dane: FileAttachment("COD_DANE_DIVIPOLA_CentrosPoblados.csv")
})
Insert cell
db
SELECT row_number() OVER (ORDER BY PUNT_GLOBAL) as "Mejor puntaje estos estudiantes ", * FROM saber
WHERE
COLE_DEPTO_UBICACION = ${selDept} AND
COLE_MCPIO_UBICACION = ${selMun} AND
PERIODO >= ${selPeriodo}*10 AND
PERIODO <= (${selPeriodo}+1)*10
Insert cell
selected
SELECT *
FROM selected
USING SAMPLE 20000 ROWS;
Insert cell
db
SELECT row_number() OVER (ORDER BY PUNT_GLOBAL) as "Mejor puntaje estos estudiantes ", * FROM saber
WHERE
COLE_DEPTO_UBICACION = ${selDept} AND
PERIODO >= ${selPeriodo}*10 AND
PERIODO <= (${selPeriodo}+1)*10
ORDER BY PUNT_GLOBAL
Insert cell
db
SELECT * FROM saber
WHERE COLE_NOMBRE_ESTABLECIMIENTO = ${featuredCol}
Insert cell
cached_medians
SELECT COLE_DEPTO_UBICACION, sum(count) FROM cached_medians
GROUP BY 1
ORDER BY 2 DESC;
Insert cell
cached_medians
SELECT COLE_DEPTO_UBICACION, COLE_MCPIO_UBICACION, sum(count) FROM cached_medians
WHERE COLE_DEPTO_UBICACION = ${selDept}
GROUP BY 1,2
ORDER BY 3 DESC;
Insert cell
cached_medians
SELECT floor(PERIODO/10) as anno, sum(count) as count FROM cached_medians
WHERE
COLE_DEPTO_UBICACION = ${selDept} AND
COLE_MCPIO_UBICACION = ${selMun}
GROUP BY 1
ORDER BY 1 DESC;
Insert cell
cached_medians
SELECT COLE_NOMBRE_ESTABLECIMIENTO AS colegio, avg(count) as count FROM cached_medians
WHERE
COLE_DEPTO_UBICACION = ${selDept} AND
COLE_MCPIO_UBICACION = ${selMun}
GROUP BY 1
ORDER BY 2 DESC;

Insert cell
Insert cell
cached_medians = FileAttachment("median_by_school_dept_mun_2023@1").csv({typed: true})
Insert cell
cached_medians
SELECT * FROM cached_medians
WHERE
COLE_DEPTO_UBICACION = ${selDept} AND
COLE_MCPIO_UBICACION = ${selMun} AND
count >= ${minStudents}
Insert cell
// {
// const brush = vl
// .selectMulti("brush")
// .fields("COLE_NOMBRE_ESTABLECIMIENTO")
// .bind(viewof featuredCol)
// .nearest(true)
// .on("mousedown")
// .value([{COLE_NOMBRE_ESTABLECIMIENTO: colegios[0].colegio}]);
// // const ySort = vl.median(varPuntaje).order("descending");
// const median = vl.markTick({ size: 5, tooltip: { data: true } }).encode(
// vl
// .y()
// .median(vl.repeat("column"))
// .scale({ zero: false })
// // .title("Puntaje Medio")
// ,
// vl.x().fieldO("PERIODO"),
// vl.detail().fieldN("COLE_NOMBRE_ESTABLECIMIENTO"),
// // vl.row().datum(vl.repeat("row"))
// );

// const lines = median.markLine().transform(vl.filter(brush));

// return vl
// .layer([median.select(brush), lines])
// .data(
// selectedAllYears
// // .map(simplifyName)
// // .filter((d) =>
// // colegiosWithEnoughStudents.includes(d.COLE_NOMBRE_ESTABLECIMIENTO)
// // )
// )

// // .width(400)
// // .title(
// // `Ranking Colegios Pruebas Saber11 ${varPuntaje} para ${selMun}, ${selDept} año ${selPeriodo}`
// // )
// .repeat({column: puntajesCols.filter(d => d.startsWith("PUNT"))})
// .render({ renderer: "canvas" });
// }
Insert cell

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.
Learn more