2d:["$","title",null,{"children":"Differences among Chicago wards in how long it takes before city service requests are serviced | Forest Gregg | Observable"}] 32:T7ac,{ const focus_completions = completions.get(form.focus_ward); const comparison_completions = completions.get( form.comparison === "city wide" ? "all" : form.comparison ); const tb = htl.html`

service type	distributions of days to completion (log)	ward ${ form.focus_ward }: average days to completion	${ form.comparison === "city wide" ? form.comparison : `ward ${form.comparison}` }: average days to completion

`; for (const [sr_type, items] of focus_completions.entries()) { const data = items.map((d) => d.time_to_completion); const comparison_data = comparison_completions .get(sr_type) .map((d) => d.time_to_completion); const row = tb.insertRow(); const type_cell = row.insertCell(); type_cell.innerHTML = sr_type; const plot_cell = row.insertCell(); plot_cell.appendChild( Plot.plot({ x: { tickFormat: (d) => 10 ** d, label: null, domain: [0, 2.562], clamp: true }, y: { ticks: null, label: null }, width: 70, height: 70, margin: 0, marks: [ Plot.line(kde.density1d(comparison_data.map((d) => Math.log10(d))), { x: "x", y: "y", fill: "#ccc" }), Plot.line(kde.density1d(data.map((d) => Math.log10(d))), { x: "x", y: "y" }), Plot.ruleY([0]) ] }) ); const mean_cell = row.insertCell(); mean_cell.style = "text-align: right;"; mean_cell.innerHTML = d3 .mean(data) .toLocaleString("en-US", { maximumFractionDigits: 0 }); const comparison_mean_cell = row.insertCell(); comparison_mean_cell.style = "text-align: right"; comparison_mean_cell.innerHTML = d3 .mean(comparison_data) .toLocaleString("en-US", { maximumFractionDigits: 0 }); } return tb; }33:T496,## Data Notes This data originally comes from the [City of Chicago's data portal](https://data.cityofchicago.org/Service-Requests/311-Service-Requests/v6vf-nfxy). I have a script that runs every night and mirrors the data into a [datasette application](https://puddle.bunkum.us/service_requests), where public users can run SQL queries. The data for the ward comparisons tables comes from the datasette instance, and [here is the SQL query](https://observablehq.com/d/4231365da7fff12d#ttc). A few key points about how we are filtering the data: 1. We looking at service requests opened in the last 365 days, 2. that have been completed, 3. that took more than 24 hours to complete, 4. that were opened by a phone call, on a website, through a mobile application, or by an aldermanic office, 5. and which were one of the 13 most common service requests types, i.e. service types which had at least complete request in each of the 50 wards. The parameters for the ward multiplier map come from an estimated model, the [details of which can be found here](https://github.com/fgregg/service_requests/blob/main/estimate_ward_effect.R). It uses the same filters of the data.34:T481,with days as ( select SR_TYPE, WARD, cast( julianday(CLOSED_DATE) - julianday(CREATED_DATE) as integer ) as time_to_completion from service_requests where created_date >= ${form.start_date.toISOString().substring(0, 10)} AND created_date <= ${form.end_date.toISOString().substring(0, 10)} and SR_TYPE IN ( 'Abandoned Vehicle Complaint', 'Alley Pothole Complaint', 'Building Violation', 'Garbage Cart Maintenance', 'Graffiti Removal Request', 'Inspect Public Way Request', 'Pothole in Street Complaint', 'Rodent Baiting/Rat Complaint', 'Sewer Cleaning Inspection Request', 'Street Light Out Complaint', 'Traffic Signal Out Complaint', 'Tree Debris Clean-Up Request', 'Water On Street Complaint' ) AND STATUS = 'Completed' AND time_to_completion > 0 AND WARD IS NOT NULL AND ORIGIN IN ( 'Phone Call', 'Internet', 'Mobile Device', 'Alderman''s Office' ) ) select SR_TYPE, WARD, time_to_completion, count(*) as count from days group by SR_TYPE, WARD, time_to_completion35:[{"id":"f5bea33e3b8de8a4a7522ddba707a8c670d63dfe3d3af96e4dffe51564b203b0f9f9b3778aee4c478fec14b766dcfa554b0b0ff23ef0c3b825cc736a34fa12ef","href":"https://static.observableusercontent.com/files/f5bea33e3b8de8a4a7522ddba707a8c670d63dfe3d3af96e4dffe51564b203b0f9f9b3778aee4c478fec14b766dcfa554b0b0ff23ef0c3b825cc736a34fa12ef","name":"Boundaries - Wards (2015-2023).geojson","type":"application/geo+json","lastModified":1672193784893.86,"size":2524138},{"id":"744366ea7b04f3c1e365c435f0a7c05e3d5f1dd2a83ef55c1d1843a967c903c59cc49fe4eeca914addf016ab33391f595305d5822030c9ecbb6090a01d7f5e3a","href":"https://static.observableusercontent.com/files/744366ea7b04f3c1e365c435f0a7c05e3d5f1dd2a83ef55c1d1843a967c903c59cc49fe4eeca914addf016ab33391f595305d5822030c9ecbb6090a01d7f5e3a","name":"ward_parameters@2.csv","type":"text/csv","lastModified":1672322679491.325,"size":5688}] 30:{"id":"4231365da7fff12d","slug":"differences-among-chicago-wards-in-how-long-it-takes-before","title":"Differences among Chicago wards in how long it takes before city service requests are serviced","thumbnail":"532278d6f0fdfd02c2edd860019c6a158faa639d6beae32b65d6374937d43917","access_level":"public","create_time":"$D2022-12-28T02:13:11.694Z","update_time":"$D2023-01-21T17:08:18.003Z","view_time":null,"first_public_time":"$D2022-12-28T02:13:11.694Z","has_unpublished_edits":null,"fork":false,"stars":1,"starred":false,"owner":{"type":"team","id":"97541738f61a2563","login":"fgregg","name":"Forest Gregg","avatar_url":"https://avatars-next.observableusercontent.com/7d00689d4df1c6365c47d99b27c8a37a6722b76a620b6df0af6b0b6fbd3a114d","tier":"starter_2024"},"version":698,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"e11e15c775d16887","login":"fgregg","name":"Forest Gregg","avatar_url":"https://avatars-next.observableusercontent.com/7d00689d4df1c6365c47d99b27c8a37a6722b76a620b6df0af6b0b6fbd3a114d","tier":null}],"members":[{"type":"individual","id":"e11e15c775d16887","login":"fgregg","name":"Forest Gregg","avatar_url":"https://avatars-next.observableusercontent.com/7d00689d4df1c6365c47d99b27c8a37a6722b76a620b6df0af6b0b6fbd3a114d","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":{"id":"33a9d8b2788bd90f","slug":"blog-transfer","title":"blog transfer","owner":{"id":"97541738f61a2563","login":"fgregg","name":"Forest Gregg"}},"type":"notebook","body":{"title":"Differences among Chicago wards in how long it takes before city service requests are serviced","cells":[{"id":0,"value":"# Differences among Chicago wards in how long it takes before city service requests are serviced\n\n\nThis notebook explores differences among Chicago wards in how long it takes the city to complete requests for service made in the last 365 days (by default).\n\nGenerally, the city responds much more quickly to service requests in ward 42, which covers the central business district. The city responds more slowly, on average, to requests from wards on the far South Side.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":540,"value":"# Ward Differences\nThe following map shows how much slower the city is to respond service requests compared to requests from ward 42. For example, service requests in ward 17 takes ${ward_effect.get('17').toLocaleString()} times longer than requests made in ward 42. You can also see [this data as table](https://observablehq.com/d/4231365da7fff12d#cell-575)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":8,"value":"Plot.plot({\n projection: { type: \"mercator\", domain: wards },\n color: {\n type: \"linear\",\n scheme: \"blues\",\n label: \"Ward multiplier\",\n legend: true\n },\n marks: [\n Plot.geo(wards, {\n fill: (d) => ward_effect.get(d.properties.ward)\n }),\n Plot.text(\n wards.features.map((f) => ({\n centroid: d3.geoCentroid(f),\n name: f.properties.ward\n })),\n {\n x: (d) => d.centroid[0], // longitude\n y: (d) => d.centroid[1], // latitude\n text: \"name\",\n textAnchor: \"middle\",\n stroke: \"white\",\n fill: \"black\"\n }\n )\n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":558,"value":"## Ward to Ward comparisons\n\nThe following table lets you compare days to completion for the thirteen most common request types.\n\nIn the plots, the gray area is the comparison and the black line is the primary ward.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":637,"value":"viewof form = Inputs.form({\n focus_ward: Inputs.select(d3.range(1, 51), {\n label: \"Select a ward\",\n value: 17\n }),\n comparison: Inputs.select([...d3.range(1, 51), \"city wide\"], {\n label: \"Select a ward to compare against\",\n value: 42\n }),\n start_date: Inputs.date({\n label: \"Start date\",\n value: new Date(new Date().setFullYear(new Date().getFullYear() - 1))\n }),\n end_date: Inputs.date({\n label: \"End date\",\n value: new Date()\n })\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":686,"value":"{\n if (\n form.start_date < new Date(\"2015-05-18\") &&\n form.end_date >= new Date(\"2015-05-18\")\n ) {\n return md`The ward boundaries changed on May 18, 2015. You should not compare wards across that time period`;\n } else {\n return md``;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":366,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":594,"value":"$33","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":601,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":575,"value":"ward_effect_array","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":576,"value":"ward_effect_array = Array.from(ward_effect).map(([ward, multiplier]) => ({\n ward,\n multiplier\n}))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":131,"value":"completions = {\n const completions = d3.group(\n ttc,\n (d) => d.WARD,\n (d) => d.SR_TYPE\n );\n completions.set(\n \"all\",\n d3.group(ttc, (d) => d.SR_TYPE)\n );\n return completions;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":35,"value":"ward_effect = {\n const wards = ward_parameters\n .filter(\n (d) => d.term.startsWith(\"WARD\") && !d.term.endsWith(\"aldermanic_request\")\n )\n .map((d) => [d.term.slice(4), Math.exp(d.estimate)]);\n return new Map([...wards, [\"42\", 1]]);\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":6,"value":"wards = FileAttachment(\"Boundaries - Wards (2015-2023).geojson\").json()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":53,"value":"ward_parameters = await FileAttachment(\"ward_parameters@2.csv\").csv({typed: \"auto\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"ward_parameters","database":"$undefined"},{"id":664,"value":"ttc = ttc_summarized\n .map((d) =>\n Array(d.count).fill({\n SR_TYPE: d.SR_TYPE,\n WARD: d.WARD,\n time_to_completion: d.time_to_completion\n })\n )\n .flat()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":86,"value":"$34","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"ttc_summarized","database":"var:memdb"},{"id":83,"value":"memdb = new DatasetteClient(\"https://puddle.bunkum.us/_memory\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":205,"value":"kde = require(\"fast-kde\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":78,"value":"import { DatasetteClient } from \"@fgregg/datasette-client-with-sql-cells-full-results-csv-stream\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W35","stdlib":"1","titling":"auto"}} 31:"$30:body" 2c:E{"digest":"NEXT_REDIRECT;replace;/@fgregg/differences-among-chicago-wards-in-how-long-it-takes-before;307;"}