Public
Edited
Nov 30, 2022
1 fork
BRATENAHL
Questions we want to ask:
Are some officers being more racist than others?
Missing data: Out of ${totalStops} stops, ${blankRace} had missing value for race, and ${unknownRace} had race unknown. That's ${parseFloat((blankRace + unknownRace)/totalStops *100).toFixed(2)}% of total cases.
Inputs.table(dt)
totalStops = dt.count().objects()[0]["count"]
blankRace = dt
.filter((d) => d["race"] == "")
.count()
.objects()[0]["count"]
unknownRace = dt
.filter((d) => d["race"] == "U")
.count()
.objects()[0]["count"]
stopsbyrace = dt
.groupby("race")
.count()
.derive({
pct: (row) => (row.count / aq.op.sum(row.count)) * 100
})
Inputs.table(stopsbyrace)
racenotlisted = dt
.filter((d) => d["race"] === "")
.groupby("dispo")
.count()
.derive({
pct: (row) => (row.count / aq.op.sum(row.count)) * 100
})
.view()
raceGrouped = dt
.groupby("race", "dispo")
.count()
.groupby("race")
.derive({
pct: (row) => row.count / aq.op.sum(row.count)
})
.orderby("race", aq.desc("pct"))
.derive({
count: aq.escape((d) => (typeof d.count == "undefined" ? 0 : d.count))
})
.pivot("dispo", "count")
Inputs.table(raceGrouped)
racearrests = dt
.filter((d) => d["dispo"] === "ARREST")
.groupby("race")
.count()
.derive({
pct: (row) => (row.count / aq.op.sum(row.count)) * 100
})
.orderby("race", aq.desc("pct"))
Inputs.table(racearrests)
racearrestspct = dt
.filter((d) => d["dispo"] === "ARREST")
.filter((d) => d["race"] !== "")
.groupby("race")
.count()
.derive({
pct: (row) => (row.count / aq.op.sum(row.count)) * 100
})
.orderby("race", aq.desc("pct"))
racecitation = dt
.filter((d) => d["dispo"] === "CITE ISSUED")
.groupby("race")
.count()
.derive({
pct: (row) => (row.count / aq.op.sum(row.count)) * 100
})
.orderby("race", aq.desc("pct"))
Inputs.table(racecitation)
racecitationpct = dt
.filter((d) => d["dispo"] === "CITE ISSUED")
.filter((d) => d["race"] !== "")
.filter((d) => d["race"] !== "U")
.groupby("race")
.count()
.derive({
pct: (row) => (row.count / aq.op.sum(row.count)) * 100
})
.orderby("race", aq.desc("pct"))
dt
.filter((d) => d.race == "A")
.derive({
consequence: (d) =>
op.includes(["ARREST", "CITE ISSUED", "CITE SRAD"], d["dispo"])
? "consequence"
: "no_consequence"
})
.orderby((d) => d.consequence)
.view()
dt
.derive({
consequence: (d) =>
op.includes(["ARREST", "CITE ISSUED", "CITE SRAD"], d["dispo"])
? "consequence"
: "no_consequence"
})
.groupby("race", "consequence")
.count()
.impute({ count: () => 0 })
.groupby("race")
.derive({
pct: (row) => row.count / aq.op.sum(row.count)
})
.orderby("race", aq.desc("pct"))
.derive({
count: aq.escape((d) => (typeof d.count == "undefined" ? 0 : d.count))
})
.pivot("consequence", "count")
.derive({
pct: (row) =>
Math.round(
(row.consequence / (row.consequence + row.no_consequence)) * 10000
) / 100
})
.view()
// Parse datetime objects and extract day of week
dtDayOfWeek = dt
.derive({ dateWithoutTime: (d) => op.replace(d["date"], " 0:00", "") })
.derive({ dateSplit: (d) => op.split(d["dateWithoutTime"], "/") })
.spread("dateSplit", { as: ["monthClean", "dateClean", "yearClean"] })
.derive({
dateTimeObject: (d) =>
// Really weird how op.datetime counts month from 0. Just, why?
op.datetime(d["yearClean"], d["monthClean"] - 1, d["dateClean"])
})
.derive({
dayOfWeekClean: aq.escape((d) =>
d["dateTimeObject"].toLocaleString("en-us", { weekday: "long" })
)
})
stops2020 = dtDayOfWeek.filter((d) => d.yearClean === "2020")
totalStops2020 = stops2020.count().objects()[0]["count"]
stopsbyrace20 = stops2020
.groupby("race")
.count()
// .groupby("race")
.derive({
pct: (row) => row.count / aq.op.sum(row.count)
})
.orderby("race", aq.desc("pct"))
Inputs.table(stopsbyrace20)
stops2021 = dtDayOfWeek.filter((d) => d.yearClean === "2021")
totalStops2021 = stops2021.count().objects()[0]["count"]
stopsbyrace21 = stops2021
.groupby("race")
.count()
// .groupby("race")
.derive({
pct: (row) => row.count / aq.op.sum(row.count)
})
.orderby("race", aq.desc("pct"))
Inputs.table(stopsbyrace21)
racedispoGrouped21 = stops2021
.groupby("race", "dispo")
.count()
.groupby("race")
.derive({
pct: (row) => row.count / aq.op.sum(row.count)
})
.orderby("race", aq.desc("pct"))
.derive({
count: aq.escape((d) => (typeof d.count == "undefined" ? 0 : d.count))
})
.pivot("dispo", "count")
bybadge = dt.groupby("badge1", "race").count()
Inputs.table(bybadge)
racebybadge = bybadge
.groupby("badge1")
.pivot("race", "count")
.rename(aq.names("badge1", "unlisted"))
.derive({
// You'd need to repeat this for all the columns you want to convert
// For a reason I'm not sure of yet, aq.escape() is needed, otherwise the `typeof` causes problems.
B: aq.escape((d) => (typeof d.B == "undefined" ? null : d.B)),
F: aq.escape((d) => (typeof d.F == "undefined" ? null : d.F)),
A: aq.escape((d) => (typeof d.A == "undefined" ? null : d.A)),
U: aq.escape((d) => (typeof d.U == "undefined" ? null : d.U)),
I: aq.escape((d) => (typeof d.I == "undefined" ? null : d.I)),
W: aq.escape((d) => (typeof d.W == "undefined" ? null : d.W)),
U: aq.escape((d) => (typeof d.U == "undefined" ? null : d.U)),
unlisted: aq.escape((d) =>
typeof d.unlisted == "undefined" ? null : d.unlisted
)
})
.derive({
total: (row) => row.unlisted + row.A + row.W + row.B + row.F + row.I + row.U
})
.derive({
pctblack: (row) =>
(100 * row.B) / (row.A + row.W + row.B + row.F + row.I + row.U)
})
.derive({
pctblank: (row) =>
(100 * row.unlisted) /
(row.A + row.W + row.B + row.F + row.I + row.U + row.unlisted)
})
.filter((d) => d["total"] > 10)
Inputs.table(racebybadge)
Inputs.table(dtDayOfWeek)
stops
racebybadge.view()
Plot.plot({
inset: 8,
grid: true,
color: {
legend: true
},
marks: [
Plot.dot(racebybadge, { x: "pctblack", y: "pctblank", r: "total" }),
Plot.linearRegressionY(racebybadge, {
x: "pctblack",
y: "pctblank",
stroke: "steelblue",
ci: 0.95
})
]
})
locationsbyrace = toplocations
.groupby("loc1", "loc2")
.derive({
race: aq.escape((d) => (typeof d.race == "undefined" ? null : d.race))
})
.pivot("race", "count")
.rename(aq.names("loc1", "loc2", "unlisted"))
.derive({
// You'd need to repeat this for all the columns you want to convert
// For a reason I'm not sure of yet, aq.escape() is needed, otherwise the `typeof` causes problems.
B: aq.escape((d) => (typeof d.B == "undefined" ? null : d.B)),
F: aq.escape((d) => (typeof d.F == "undefined" ? null : d.F)),
A: aq.escape((d) => (typeof d.A == "undefined" ? null : d.A)),
U: aq.escape((d) => (typeof d.U == "undefined" ? null : d.U)),
I: aq.escape((d) => (typeof d.I == "undefined" ? null : d.I)),
W: aq.escape((d) => (typeof d.W == "undefined" ? null : d.W)),
U: aq.escape((d) => (typeof d.U == "undefined" ? null : d.U)),
unlisted: aq.escape((d) =>
typeof d.unlisted == "undefined" ? null : d.unlisted
)
})
.derive({
total: (row) => row.unlisted + row.A + row.W + row.B + row.F + row.I + row.U
})
.filter((d) => d["total"] > 10)
.derive({
pctblack: (row) =>
(100 * row.B) / (row.A + row.W + row.B + row.F + row.I + row.U)
})
Inputs.table(toplocations)
Inputs.table(locationsbyrace)
addTooltips(
Plot.plot({
width: 1000,
overflow: "scroll",
color: {
type: "linear",
range: ["steelblue", "orange"] // uses d3.interpolateRgb
},
x: {
domain: d3.sort(locationsbyrace, (d) => -d.total).map((d) => d.loc1)
},
y: {
grid: true
},
marks: [
Plot.barY(locationsbyrace, {
x: "loc1",
y: "total",
fill: (d) => -d.pctblack,
title: (d) => d.loc1 + " & " + d.loc2 + "\n" + d.pctblack + "% Black"
}),
Plot.ruleY([0])
]
})
)
Inputs.table(racedispoGrouped21)
raceGrouped
.derive({
consequences: (d) => d.ARREST + d["CITE ISSUED"] + d["CITE SRAD"]
})
.view()
raceGrouped.view()
Inputs.table(racearrestspct)
Inputs.table(racecitationpct)
Inputs.table(racenotlisted)
stops = aq.from(
await FileAttachment("Jan 1 2020- Sept 15 2022 .xls - callracesex.csv").csv()
)
jan12020Sept152022XlsCallracesex = FileAttachment("Jan 1 2020- Sept 15 2022 .xls - callracesex.csv").csv()
import { addTooltips } from "@mkfreeman/plot-tooltip"
import { Plot } from "@mkfreeman/plot-tooltip"
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
