IMG_0510

Until recently, I had no idea how bicycles worked. I had no idea, for example, that they were the most efficient form of human locomotion, that traveling on level ground on a bicycle uses a whopping one-third the energy of doing so on foot. Being a software developer, not a mechanical engineer, I still don’t fully understand this, so I set out to understand the bicycle through a lens that made sense for me.

Since its launch in 2013, Citi Bike has become one of the largest and most heavily used bike-sharing systems in the world. Thanks to some forward-thinking provisions in the City’s contract with what was originally Alta Bicycle Share (later Motivate and then Lyft), we have access to a comprehensive trip-level historical dataset, allowing us to study how bikeshare has been used in New York.

At first glance you might look at the data and think “oh, great, an S3 bucket of zipped CSVs, this should be easy!”. I thought that, too.

There are two documented schemas, but you’ll actually find three different varieties of column headers. Within the first variety, there are four different timestamp formats present across various vintages of data. And that’s all once you actually get to the CSV files themselves. Some of the Zip archives have two copies of the same data: one chunked into files of a million rows each, and one with an entire month in a single file. These must be handled carefully to avoid ingesting duplicates. Other Zip archives contain Zip archives nested within.

So, while some modern databases and data analysis libraries provide built-in support for consuming files from an S3 bucket or consuming files within compressed archives, the totality of eccentricities in this dataset is just too much for them. Instead, you have to pre-process the data before loading.

I started with a fairly ungainly combination of shell incantations to get all of the CSVs (or at least the non-duplicate ones) into a directory I could point ClickHouse at. This worked, but ClickHouse feels a little heavyweight for the task (despite the existence of clickhouse-local). Then, I tried building a custom tool in Rust which used the rawzip crate and Polars to directly produce Parquet files from the dataset, and while that worked, I wasn’t totally happy with how it behaved, and using Polars directly from Rust (as opposed to via its Python bindings) doesn’t feel particularly ergonomic.

Finally, I decided to reimplement the extraction bit in Python, and leverage DuckDB to do the heavy lifting with the data. The result is citibike-extractor, available now. Download the contents of the S3 bucket (e.g. with rclone copy :s3:tripdata tripdata --filter "+ *-citibike-tripdata*.zip" --filter "- *"), then run the tool with uvx run --from git+https://github.com/kurtraschke/citibike-extractor.git citibike-extractor tripdata.db tripdata/*.zip (changing the paths as needed for your environment).

The result is a DuckDB database containing all 320 million rows (as of June 2026), each representing a single bikeshare trip. We can, of course, analyze the data directly in DuckDB, or, if we want to use another tool or import it into another database, export to a year-month partitioned Parquet dataset with the following incantation:

COPY (FROM tripdata ORDER BY started_at, ended_at, ride_id)
TO 'tripdata' (
    FORMAT PARQUET, COMPRESSION 'zstd',
    PARTITION_BY (started_year, started_month)
);

Now, let’s talk a little bit about what we’ve done. We’ve gone from 28 gigabytes of CSV files in a variety of formats (about 60 gigabytes with everything unzipped) to a DuckDB database which weighs in at about 20 gigabytes (admittedly, not much of an improvement), or a tool-agnostic Parquet dataset which takes just nine gigabytes. In addition, we’ve unified the old and new formats as best we can. Of course, we can’t conjure up data that doesn’t exist. The new format omits bike IDs, for example, and the old format didn’t have a “ride ID” column, so while we’ve synthesized ride IDs for those trips, there’s nothing official about those IDs. And then there are the timestamps. Unfortunately, the start and end times are all local times. “So what, Citi Bike only exists in one time zone!” you might say. Yes, but this means we run into trouble twice a year, every year, on DST transition days. When the clocks go back, we get trips which appear to have ended before they began. We could, in principle, apply some heuristics to fix these timestamps, but strictly speaking, by recording these times in the local timezone rather than UTC, some information is permanently lost. The moral of the story, then, is to always use UTC, even in contexts where everything physically happens in one time zone!

Anyway, enough of that. You might ask “so why don’t you just stick those Parquet files in a bucket somewhere so the rest of us don’t have to fuss with this?”.

Unfortunately, this is where I have to slightly amend my comment about forward-thinking contract provisions. Lyft’s Data Sharing Policy forbids a number of acts, including to “[h]ost, stream, publish, distribute, sublicense, or sell the Data as a stand-alone dataset”. So, while I can tell you how to do this, I am forbidden to provide you with the output of this process.

However, nothing would stop Lyft from going back and re-processing the trip history from 2013 onward and providing it as a series of Parquet files themselves! This dataset is well past the size where anyone is going to be able to work with it in Excel (unless they do some data reduction in Power Query first, but of course Power Query supports Parquet!). So, arguments about “we have to use CSV because it’s universal” don’t really hold water anymore. Parquet is simply a far superior format to CSV in all regards. Parquet files carry a defined schema with them, they support highly efficient compression, and are faster than CSV to produce and consume. Nobody is going to be disadvantaged by Lyft replacing a hodgepodge of CSVs in Zip files with a cleaned-up Parquet dataset.

The point of this, though, is not the data infrastructure (though I’ve had a great deal of fun with that); it’s what the data can tell us about cycling in New York and the behavior of bikeshare users, and now, with this tool, it’s a little easier to go from raw data to a cleaned, analysis-ready dataset. And remember, “slow is forever”.