HTTPS SSH

Bikeshare Trip History

Utilities for working with Capital Bikeshare trip history data.

The latest merged data is available in a zipped CSV (259 MB) if you don't need to customize the output.

Quickstart

To download and merge all history data into a single CSV (1.7 GB for 2010Q4-2016Q4):

git clone https://bitbucket.org/ArlingtonCounty/bikeshare-trip-history.git
cd bikeshare-trip-history
wget --input-file=history_urls.txt # Or download file(s) manually
unzip '*.zip'
python merge_csv.py history_merged.csv

history_merged.csv will contain the following columns:

  • Duration (ms)
  • Start time: ISO 8601 format (YYYY-MM-DDTHH:MM:SS)
  • Start station
  • End time: ISO 8601 format (YYYY-MM-DDTHH:MM:SS)
  • End station
  • Bike number: may contain odd data (e.g. ? (0x74BEBCE4)). These are errors in the original files.
  • Type: Terminology has changed over time, original values are preserved.

Station IDs are not included as they are not present in some files and may not have changed if a station moved.

Header and first line:

Duration (ms),Start time,Start station,End time,End station,Bike number,Type
51962000,2010-12-31T23:49:00,10th & U St NW,2011-01-01T14:15:00,10th & U St NW,W00771,Casual

Databases

If you intend to import the merged CSV into a database, the --header option may be useful:

python merge_csv.py --header=sql history_merged.csv # duration_ms,start_time,start_station,end_time,end_station,bike_number,type
sqlite3 cabi.sqlite3
sqlite> .mode csv
sqlite> .import history_merged.csv trips
sqlite> SELECT * FROM trips ORDER BY duration_ms DESC LIMIT 10;
python merge_csv.py --header=none history_merged.csv
sqlite3 cabi.sqlite3
sqlite> CREATE TABLE trips(
   ...>   duration_ms INTEGER,
   ...>   start_time TEXT,
   ...>   start_station TEXT,
   ...>   end_time TEXT,
   ...>   end_station TEXT,
   ...>   bike_number TEXT,
   ...>   type TEXT
   ...> );
sqlite> .mode csv
sqlite> .import history_merged.csv trips
sqlite> SELECT * FROM trips ORDER BY duration_ms DESC LIMIT 10;

License

GNU General Public License, Version 3 (see LICENSE).

2017 Arlington County Government