2a:null 2b:["$","title",null,{"children":"CompStat + 311 | Zhi Keng He | Observable"}] 31:T4c1,compstat_book = { const res_data = await d3.json( CORS + "https://compstat.nypdonline.org/api/reports/1/datasource/list", { body: JSON.stringify({ filters: [ { key: "PRECINCTKey", label: "Precinct", values: ["Citywide"] }, { key: "BOROKey", label: "Patrol Borough", values: ["Citywide"] }, { key: "RECORDID", label: "SELECTION", values: [] } ] }), headers: { "content-type": "application/json" }, method: "POST", mode: "cors", redirect: "follow" } ); //pull out the lastest week of compstat const dates = [...res_data[0].RowLabel.match(/(\d+[/]\d+[/]\d+)/g)]; //get rows then flip const rows = res_data.map((i) => i.Columns.map((e) => e.Value)); const data = rows.map((row) => ({ _id: row[0], wtd_year1: +row[1], wtd_year0: +row[2], wtd_change: +row[3], day28_year1: +row[4], day28_year0: +row[5], day28_change: +row[6], ytd_year1: +row[7], ytd_year0: +row[8], ytd_change: +row[9] })); return { dates, data }; }32:T9bc,service_requests_book = (async () => { //query soda for data for each of the data ranges then put it in the same format as compstat_book function query311ServiceRequests( startDate, endDate, inCommunityBoards = null ) { //returns rows of complaint_type and their counts for the during the start and end dates const base = "https://data.cityofnewyork.us/resource/erm2-nwe9.json"; let query = `?$$app_token=MOkdR2TuLzWqf8P66Yv8CRxZZ&$select=complaint_type,COUNT(unique_key)&$where=(created_date between '${startDate}' and '${endDate}') and agency='NYPD'`; if (inCommunityBoards) query += `and community_board in ${inCommunityBoards}`; query += "&$group=complaint_type"; return fetch(base + query).then((res) => res.json()); } const values = await Promise.all( Object.values(queryDates).map((ranges) => { return Promise.all( ranges.map((range) => query311ServiceRequests(range[0], range[1])) ); }) ).then((sets) => { //loop through each set and generate 'wtk', '28day', and 'ytd' function addToBook(row, book, key_year = "wtd_year1") { //look for if _id exist in book, add value e.g. wtd_year1 const { complaint_type, COUNT_unique_key } = row; let bookEntry = book.find((i) => i._id === complaint_type); if (!bookEntry) { book.push({ _id: complaint_type, [key_year]: +COUNT_unique_key }); } else { bookEntry[key_year] = +COUNT_unique_key; } } return sets.reduce((book, set, i) => { const key = Object.keys(queryDates)[i]; set[0].map((row) => addToBook(row, book, key + "_year1")); set[1].map((row) => addToBook(row, book, key + "_year0")); return book; }, []); }); //sort keys to be in the same order as compstat book, also fill in blank values const orderedValues = values.map((row) => { const newRow = {}; Object.keys(compstat_book.data[0]).map((key) => { if (!(key in row)) { newRow[key] = NaN; } else { newRow[key] = row[key]; } }); return newRow; }); //calculate change between years, if both values exist orderedValues.forEach((value) => { Object.keys(queryDates).map((key) => { if (value[key + "_year1"] && value[key + "_year0"]) { value[key + "_change"] = ((value[key + "_year1"] - value[key + "_year0"]) / value[key + "_year1"]) * 100; } }); }); return orderedValues; })()33:[] 2e:{"id":"d11fe9e8acd96efc","slug":"compstat-311","title":"CompStat + 311","thumbnail":"fec2eb0c0652035fbe9d33968fa01b7354ec80a4b0a5bd4b16666d16d03c0281","access_level":"public","create_time":"$D2021-12-13T17:12:31.556Z","update_time":"$D2023-04-18T17:39:53.460Z","view_time":null,"first_public_time":"$D2021-12-13T22:55:23.477Z","fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"16506c8d98fe0d4f","login":"zhik","name":"Zhi Keng He","avatar_url":"https://avatars-next.observableusercontent.com/1921c3cb8ffa82cde953aa4417dc8b601dbd05384629c1f8ecf0544be068a204","tier":"starter_2024"},"version":344,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"215e59083f0e53f8","login":"zhik","name":"Zhi Keng He","avatar_url":"https://avatars-next.observableusercontent.com/1921c3cb8ffa82cde953aa4417dc8b601dbd05384629c1f8ecf0544be068a204","tier":null}],"members":[{"type":"individual","id":"215e59083f0e53f8","login":"zhik","name":"Zhi Keng He","avatar_url":"https://avatars-next.observableusercontent.com/1921c3cb8ffa82cde953aa4417dc8b601dbd05384629c1f8ecf0544be068a204","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"CompStat + 311","cells":[{"id":0,"value":"# CompStat + 311 \n\nComparison dashboard for both [NYPD CompStat](https://compstat.nypdonline.org) crime data and [NYPD 311 Service Requests](https://data.cityofnewyork.us/Social-Services/311-Service-Requests-from-2010-to-Present/erm2-nwe9/data) data broken down by Week to Date (wtd), 28 Day, and Year to Date (ytd).","pinned":false,"hidden":false,"mode":"md"},{"id":274,"value":"Inputs.select([\n \"Citywide\",\n // \"Brooklyn North\",\n // \"Brooklyn South\",\n // \"Bronx\",\n // \"Manhattan North\",\n // \"Manhattan South\",\n // \"Queens North\",\n // \"Queens South\",\n // \"Staten Island\"\n])","pinned":false,"hidden":false,"mode":"ojs"},{"id":256,"value":"{\n return md`## CompStat Week of ${compstat_book.dates.join(\" - \")}`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":163,"value":"{\n return Inputs.table(compstat_book.data, {\n format: {\n wtd_change: changeSparkbar(\n d3.extent(compstat_book.data, (d) => d.wtd_change)\n ),\n day28_change: changeSparkbar(\n d3.extent(compstat_book.data, (d) => d.day28_change)\n ),\n ytd_change: changeSparkbar(\n d3.extent(compstat_book.data, (d) => d.ytd_change)\n )\n },\n header: {\n _id: \"Name\",\n wtd_year1: \"this year (wtd)\",\n wtd_year0: \"last year\",\n wtd_change: htl.html`Change`,\n day28_year1: \"this (28 day)\",\n day28_year0: \"last\",\n day28_change: htl.html`Change`,\n ytd_year1: \"this (ytd)\",\n ytd_year0: \"last\",\n ytd_change: htl.html`Change`\n }\n });\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":264,"value":"## 311 Service Requests ","pinned":false,"hidden":false,"mode":"md"},{"id":233,"value":"Inputs.table(service_requests_book, {\n format: {\n wtd_change: changeSparkbar(\n d3.extent(service_requests_book, (d) => d.wtd_change)\n ),\n day28_change: changeSparkbar(\n d3.extent(service_requests_book, (d) => d.day28_change)\n ),\n ytd_change: changeSparkbar(\n d3.extent(service_requests_book, (d) => d.ytd_change)\n )\n },\n\n header: {\n _id: \"Name\",\n wtd_year1: \"this year (wtd)\",\n wtd_year0: \"last year\",\n wtd_change: htl.html`Change`,\n day28_year1: \"this (28 day)\",\n day28_year0: \"last\",\n day28_change: htl.html`Change`,\n ytd_year1: \"this (ytd)\",\n ytd_year0: \"last\",\n ytd_change: htl.html`Change`\n },\n sort: \"wtd_year1\",\n reverse: true\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":279,"value":"### Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":6,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":45,"value":"queryDates = {\n const [start_week, end_date] = compstat_book.dates.map((i) =>\n dateFns.parse(i, \"MM/dd/yy\")\n );\n\n const start_28 = dateFns.subWeeks(end_date, 4);\n const start_year = dateFns.subYears(end_date, 1);\n\n //revisit this to match with compstat ranges - probably use endOfDay\n const betweenDates = {\n wtd: [\n [start_week, end_date],\n [dateFns.subYears(start_week, 1), dateFns.subYears(end_date, 1)]\n ],\n day28: [\n [start_28, end_date],\n [dateFns.subYears(start_28, 1), dateFns.subYears(end_date, 1)]\n ],\n ytd: [\n [start_year, end_date],\n [dateFns.subYears(start_year, 1), dateFns.subYears(end_date, 1)]\n ]\n };\n //convert all dates to \"YYYY-MM-DD[T]HH:mm:ss\" format\n return Object.keys(betweenDates).reduce((obj, k) => {\n obj[k] = [\n betweenDates[k][0].map((i) => dateFns.format(i, \"YYYY-MM-DD[T]HH:mm:ss\")),\n betweenDates[k][1].map((i) => dateFns.format(i, \"YYYY-MM-DD[T]HH:mm:ss\"))\n ];\n\n return obj;\n }, {});\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":94,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":51,"value":"dateFns = await require(\"https://cdnjs.cloudflare.com/ajax/libs/date-fns/1.30.1/date_fns.js\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":166,"value":"function changeSparkbar(extent) {\n //get the largest extent\n let max = d3.max(extent.map((i) => Math.abs(i)));\n\n //limit max numbers to 500\n if (max > 500) max = 500;\n\n return (x) => htl.html`

0 ? \"pink\" : \"lightblue\"};\n width: ${(100 * Math.abs(x)) / max}%;\n float: left;\n text-align: left;\n padding-right: 3px;\n box-sizing: border-box;\n color: black;\">${d3.format(\".0f\")(x)}%

`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":339,"value":"CORS = \"https://4dvj5dcxge.execute-api.us-east-1.amazonaws.com/staging/\"","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W33","stdlib":"1","titling":"auto"}} 2f:"$2e:body"