Somewhere in your city's open data portal is a file with a few million rows in it, and every row is a bicycle ride. Four years of bike-share trips, or a year of counts from a loop detector bolted to the pavement, or the anonymized records a local advocacy group cleaned up and published. It's public, it's free, and it's usually messy enough to be interesting.

The question that makes it worth an evening: where and when do people actually ride? Answer that and you can argue for a painted lane, a repair stand, a signal timing change — or simply satisfy your own curiosity. Here's the path from raw download to a map you'd show a stranger.

Start with the question, not the dataset

Datasets tempt you to download first and wonder later. Go the other way. A count of daily rides along one corridor answers a different question than a table of dock-to-dock trips, and neither tells you whether an intersection is dangerous — that needs crash reports, which live in another file, often with coordinates rounded or redacted.

Three questions are almost always answerable with open data:

• Demand: how many people ride, and at what hours?
• Geography: which streets and neighborhoods generate trips?
• Operations: how long does a bike sit, and where do docks run empty?

Each implies a different dataset, a different cleaning job, and a different chart. Write your question on a sticky note before you open a CSV.

Where to look for bike data

Places worth checking, roughly in order of how much they'll hand you:

• Municipal open data portals — usually Socrata or CKAN, searchable, with both an API and a CSV export. Search the words "bike", "bicycle", "cycling", and "counter".
• The transportation or mobility department, which often publishes count programs and bike-lane inventories that never made it to the portal.
• Bike-share operators, who release system data under a license, sometimes monthly, sometimes in annual batches.
• Regional planning organizations, which aggregate counters across several municipalities.
• Local advocacy groups and university transportation labs, which sometimes publish cleaned versions when the city won't.
• GPS route datasets from fitness apps, which show where people ride rather than only where they park.

Read the metadata before the data. Column names are often less precise than the data dictionary, and the dictionary is where you learn that a duration field means seconds in one year and minutes in the next.

Cleaning trip records without erasing the story

Order matters, and so does keeping count of what you discard. A cleaning script that silently drops twelve percent of rows is a script that quietly changes your conclusion.

1. Parse timestamps with a timezone. Local time without an offset breaks twice a year, and daylight saving transitions manufacture fake negative durations.
2. Inspect duration: rides under a minute are usually dock errors or re-docks, and rides over several hours are usually a bike that was never returned properly.
3. Check duplicates by trip ID, and look for staff rebalancing moves logged as customer trips.
4. Normalize station names and IDs. "Main St & 3rd" and "Main Street / 3rd Ave" are the same corner, and they will plot as two.
5. Derive what you'll actually use: start hour, weekday, month, duration in minutes, and whether start and end stations differ.
6. Write a log file. Rows in, rows out, and the reason for every filter.

Then join context: weather from a public station near downtown, public holidays, transit stops, and a bike network file. Those joins explain more variation than any model you'll build this month.

Turning rows into patterns

Start with counts. A bar chart of trips by hour, split into weekday and weekend, tells you whether your city behaves like a commuter system with two sharp peaks or a leisure system with one broad afternoon hump. Most cities are a blend, and the blend shifts by neighborhood.

Then go spatial. Aggregate to a hex grid or census tract instead of plotting individual points; point maps at city scale are slow to render and hard to read. Compute net flow — arrivals minus departures in the morning — for each station. Downtown stations tend to import bikes before nine and export them after five, which is exactly what someone deciding where to place docks needs to see.

Compare sources when you have more than one. Fixed counters record every cyclist passing a point, including people on their own bikes, but only where a counter was installed. Bike-share data covers an entire system but only people who rent. Where the two agree on a corridor's daily rhythm, you can speak with confidence. Where they disagree, the gap is a finding, not a problem.

Making the map readable

Two habits save most mapping projects. First, pick a projection suited to your city and don't let the default stretch it. Second, keep the visual vocabulary small: one color ramp for volume, one line style for the bike network, gray for everything else. Check the ramp in grayscale — if half the map is one indistinguishable shade, your bins are wrong.

Add context people recognize: a few street names, the river, major transit stops, and the boundary of the area you actually have data for. A map with no boundary implies coverage you don't have. A caption stating the date range and source does more for credibility than a legend with fourteen categories.

Caveats worth stating out loud

Trip data is not a census of cyclists. Dockless rentals, private bikes, and very short trips are underrepresented. Weather, construction, and fare changes move the numbers, so compare like months with like months rather than one rainy Tuesday with a dry one. Privacy rules may round or shift coordinates, which matters at the block level. And counts stop where the counter stops — the emptiest sensor is rarely the emptiest street.

If your work touches traffic safety, liability, or funding decisions, treat it as input to a conversation with planners and engineers rather than a verdict on its own.

A project you can finish in a weekend

Pick one corridor and one year. Download the trips or counts that cover it, write the cleaning script with its log, plot hourly volume by weekday, then map net flow within a couple of blocks of each end. Lay that against the bike network file and mark where high volume meets no infrastructure.

Write three sentences: what you found, how confident you are, and what would change your mind. Publish them alongside the script and the dates of the source files. That's a genuine contribution to a local argument, and next weekend you can add a second corridor.

Photo: OrnaW / Pixabay