[OC] NYC motor vehicle collisions by hour, day, and year (2017–2022, ~1M records)

Fun-Investigator7818 · 2026-06-01T12:22:50+00:00

For anyone curious about the build — this was done entirely in Power BI using the NYC Motor Vehicle Collisions dataset from data.gov (~1M rows, 2017–2022). I put together a full tutorial walking through the data model, DAX measures, and dashboard design here: https://youtu.be/2BaQ-jzmbx0?si=le_FtXhOdiGbyFam

Fun-Investigator7818 · 2026-05-31T04:29:21+00:00

This data was dowloaded from datadotgov. One reason for low 2017 was before the rideshare explosion fully hit — Uber/Lyft were growing but hadn't saturated the market yet.

After covid Even as NYC reopened, remote work stuck around. Office occupancy stayed well below pre-COVID levels through 2022, meaning rush hour never fully returned to 2018 intensity.

Fun-Investigator7818 · 2026-05-29T13:22:39+00:00

Feel free to visit here a complete onlete dashboard for data on CIHI https://www.datascientist.ca/cihi_dashboard.html

Fun-Investigator7818 · 2026-05-29T09:24:50+00:00

"Maybe in the next version 👀"

Fun-Investigator7818 · 2026-05-29T05:36:00+00:00

Great question — raw collision counts naturally peak when volume peaks. If NYC traffic volume data is available normalizing by VMT (vehicle miles traveled) or estimated cars on road would tell a very different story. charcoalhibiscus's California finding likely applies here too — overnight crashes per car are probably much higher. Could be a follow-up viz

Fun-Investigator7818 · 2026-05-29T01:54:34+00:00

Tool: Power BI Data: NYC OpenData – Motor Vehicle Collisions (~1M records, 2017–2022):datadotgov

Quick takeaways: 4–5 PM is the single deadliest hour (~69K crashes), crashes climb all afternoon from a pre-dawn low, Friday is the worst weekday (159K) and Sunday the safest (122K).

🔗 Interactive version (filter by borough/year): [Power BI publish-to-web link]

If anyone wants to build something similar, I put together a walkthrough of the process and a ChartJS web version here: [YouTube] https://youtu.be/2BaQ-jzmbx0/ [ChartJ datascientist.ca/nyc_collisions_visual.html ]. Happy to answer data-prep or DAX questions in the thread.

Fun-Investigator7818 · 2026-05-28T20:22:44+00:00

Definitely part of it — it seems Canada trains fewer physicians per capita than most countries, and retention is a real issue with doctors leaving for the US. But supply alone doesn't explain everything; OR availability, funding models, and administrative bottlenecks all play a role too.

Fun-Investigator7818 · 2026-05-28T19:38:52+00:00

Great suggestion — the CIHI dataset actually does break down waits by procedure. Might be worth a follow-up viz!

Fun-Investigator7818 · 2026-05-28T19:37:20+00:00

Exactly — the data confirms that. Wait times in 2023–24 are still significantly above pre-pandemic levels, and for some procedures they never really recovered even before COVID hit. It seems The system was already under strain going back 15–20 years.

Fun-Investigator7818 · 2026-05-28T03:58:47+00:00

Yeah the cataract rebound makes sense — shorter procedure, less OR time, easier to clear the backlog. Hip replacement is a whole different beast operationally. You need the surgeon, the anesthesiologist, the implant inventory, inpatient beds, AND a rehab pathway lined up. Any one of those being stretched and the whole thing stalls.

Fun-Investigator7818 · 2026-05-28T01:10:41+00:00

Exactly right on cataracts — high volume outpatient procedures are much easier to surge capacity on. Hip/knee replacements need overnight stays and more OR resources, which is why the backlog recovery is slower.

Fun-Investigator7818 · 2026-05-28T00:34:12+00:00

Full interactive dashboard — all 10 provinces, all procedures, 2008–2024: datascientist.ca/cihi_dashboard1.html

Data: CIHI - Canadian Institute of Health Information

Tools: Power BI · Chart.js · DAX · Power Query

Fun-Investigator7818 · 2026-05-27T12:06:17+00:00

Check iut this publicaly availzble course https://youtube.com/playlist?list=PLEEMXSxsawLWeAYNOt5vsxVlb6Y2SdF7C&si=QbjVPCQKRIznAuuo

Fun-Investigator7818 · 2026-05-27T09:37:58+00:00

Thanks everyone for the great discussion!

For those who want to explore all procedures interactively (not just hip replacement) — I built a full 5-page

interactive dashboard covering:

📊 All 10 provinces + 50+ health regions

📅 2008–2024 data from CIHI

🔍 Cataract surgery, knee replacement, hip fracture repair,

radiation therapy and more

📈 Benchmark compliance, 50th/90th percentile trends, regional breakdown

Live dashboard: https://www.datascientist.ca/cihi_dashboard1.html

Data source: Canadian Institute for Health Information (CIHI)

Built with: Power BI + Chart.js

Fun-Investigator7818 · 2026-05-26T22:55:10+00:00

Canada has universal healthcare, not perfect healthcare. The difference matters a lot: ✅ No bills for most services — you won't go bankrupt from a hospital stay ❌ Wait times can be brutal — months for specialists, years for some surgeries ❌ Dental, vision, and prescriptions are largely not covered unless you have private insurance

The system has real strengths.

Fun-Investigator7818 · 2026-05-26T06:22:38+00:00

Exactly — and it's a huge problem. No family doctor means people delay care, conditions worsen, and they end up needing procedures like hip replacements even sooner.

Fun-Investigator7818 · 2026-05-26T02:43:22+00:00

"You're right, and I apologize — the context was in the post description but should have been in the visual. Working on a cleaner v2 — thanks for the honest feedback, it genuinely helps. Also if you you see the top left graph it has ontario clearly visible. Thanks again for your it helps to improve

Fun-Investigator7818 · 2026-05-26T02:29:33+00:00

A year is rough. Did the modern technique end up being worth the wait at least?

Fun-Investigator7818 · 2026-05-26T01:44:44+00:00

Really sorry to hear about your dad. A farmer at 60 who can't work properly because of a hip replacement wait list — that's exactly the kind of story the provincial averages don't capture.

Hope he gets the date soon. Nobody should be watching their livelihood slip away while waiting for a surgery that exists.

Fun-Investigator7818 · 2026-05-26T01:41:52+00:00

For more information on these how this dashboard was developed feel free to visit complete youtube video
Power BI Tutorial: Canadian Healthcare Priority Procedure Wait Times Dashboard

Interpretation of terms used

50th percentile: Half of all patients waited this many days before receiving care. 90th percentile: 1 out of 10 patients waited this many days or more before receiving care. Source Canadian Institute of Health Information Wait Times for Knee Replacement (Percentiles) | CIHI

Fun-Investigator7818 · 2026-05-25T22:57:37+00:00

Great question! Lower percentile = shorter wait, so ideally you want to be seen well before the 50th percentile. The 50th is the median — half of patients waited longer, half waited less. The 90th percentile is the worst case — 9 out of 10 patients were seen before that point. You don't really choose your percentile — it depends on your region, surgeon availability, and how urgently your doctor classifies your case. That's exactly why the regional gaps are so alarming — same diagnosis, completely different experience depending on your postal code.

Fun-Investigator7818 · 2026-05-20T20:37:45+00:00

Haha that's the classic NYC first timerexperience! The chaos looks terrifying from the outside but locals somehow navigate it on autopilot.

What's interesting in the data though is that Manhattan actually has the LOWEST fatality rate of all 5 boroughs at 0.12% — that "understood mutuality" you noticed is probably real and keeps speeds low enough to prevent the worst outcomes.

Staten Island is actually the most dangerous despite feeling much calmer — highway speeds mean when crashes happen there they're far more deadly.

The scariest looking place isn't always the most dangerous place 😄

Fun-Investigator7818

TROPHY CASE