NYC Citi Bike Trips

Citi Bike is the nation's largest bike share program, with 10,000 bikes and 600 stations across Manhattan, Brooklyn, Queens, and Jersey City. This dataset includes Citi Bike trips since Citi Bike launched in September 2013 and is updated daily. The data has been processed by Citi Bike to remove trips that are taken by staff to service and inspect the system, as well as any trips below 60 seconds in length, which are considered false starts.

You can start exploring this data in the BigQuery console:

Go to NYC Citi Bike Dataset

Sample queries

Here are some examples of SQL queries you can run on this data in BigQuery.

These samples use BigQuery’s support for standard SQL. Use the #standardSQL tag to let BigQuery know you want to use standard SQL. For more information about the #standardSQL prefix, see Setting a query prefix.

First, let's look at the most popular Citi Bike stations, including their name, location, and number of trips.

Web UI

Open the following query in the Web UI

#standardSQL
SELECT
  start_station_name,
  start_station_latitude,
  start_station_longitude,
  COUNT(*) AS num_trips
FROM
  bigquery-public-data.new_york.citibike_trips
GROUP BY
  1,
  2,
  3
ORDER BY
  num_trips DESC
LIMIT 10

Command-line 

bq query --use_legacy_sql=false '
SELECT
  start_station_name,
  start_station_latitude,
  start_station_longitude,
  COUNT(*) AS num_trips
FROM
  `bigquery-public-data.new_york.citibike_trips`
GROUP BY
  1,
  2,
  3
ORDER BY
  num_trips DESC
LIMIT 10'

The results are shown here:

+-----------------------+------------------------+-------------------------+-----------+
|  start_station_name   | start_station_latitude | start_station_longitude | num_trips |
+-----------------------+------------------------+-------------------------+-----------+
| E 17 St & Broadway    |            40.73704984 |            -73.99009296 |    291615 |
| Lafayette St & E 8 St |            40.73028666 |             -73.9907647 |    277060 |
| W 21 St & 6 Ave       |            40.74173969 |            -73.99415556 |    275348 |
| West St & Chambers St |            40.71754834 |            -74.01322069 |    260911 |
| Pershing Square North |              40.751873 |              -73.977706 |    246181 |
| Broadway & E 14 St    |            40.73454567 |            -73.99074142 |    244420 |
| Broadway & E 22 St    |             40.7403432 |            -73.98955109 |    237394 |
| 8 Ave & W 33 St       |              40.751551 |              -73.993934 |    223970 |
| W 20 St & 11 Ave      |              40.746745 |              -74.007756 |    211840 |
| Broadway & W 24 St    |             40.7423543 |            -73.98915076 |    211713 |
+-----------------------+------------------------+-------------------------+-----------+

Next, let's look at the most popular routes by subscriber type, where “Subscribers” are Citibike members and “Customers” are one-off users. This query uses CONCAT to get the route.

Web UI

Open the following query in the Web UI

#standardSQL
SELECT
  usertype,
  CONCAT(start_station_name, " to ", end_station_name) as route,
  COUNT(*) as num_trips,
  ROUND(AVG(cast(tripduration as int64) / 60),2) as duration
FROM
  bigquery-public-data.new_york.citibike_trips
GROUP BY
  start_station_name, end_station_name, usertype
ORDER BY
  num_trips DESC
LIMIT 10

Command-line 

bq query --use_legacy_sql=false '
SELECT
  usertype,
  CONCAT(start_station_name, " to ", end_station_name) as route,
  COUNT(*) as num_trips,
  ROUND(AVG(cast(tripduration as int64) / 60),2) as duration
FROM
  `bigquery-public-data.new_york.citibike_trips`
GROUP BY
  start_station_name, end_station_name, usertype
ORDER BY
  num_trips DESC
LIMIT 10'

The results are shown here:

+------------+------------------------------------------------------------------------+-----------+----------+
|  usertype  |                                 route                                  | num_trips | duration |
+------------+------------------------------------------------------------------------+-----------+----------+
| Customer   | Central Park S & 6 Ave to Central Park S & 6 Ave                       |     40009 |    50.89 |
| Customer   | Grand Army Plaza & Central Park S to Grand Army Plaza & Central Park S |     15234 |    52.99 |
| Customer   | Centre St & Chambers St to Centre St & Chambers St                     |     12466 |    35.65 |
| Subscriber | W 21 St & 6 Ave to 9 Ave & W 22 St                                     |     11594 |     5.35 |
| Customer   | Broadway & W 60 St to Broadway & W 60 St                               |     10816 |    52.36 |
| Subscriber | W 21 St & 6 Ave to W 22 St & 10 Ave                                    |     10451 |     6.95 |
| Subscriber | E 7 St & Avenue A to Lafayette St & E 8 St                             |      9639 |     5.59 |
| Subscriber | West Thames St to Vesey Pl & River Terrace                             |      9218 |     6.67 |
| Subscriber | W 17 St & 8 Ave to 8 Ave & W 31 St                                     |      8760 |     6.43 |
| Subscriber | E 43 St & Vanderbilt Ave to W 41 St & 8 Ave                            |      8743 |     7.04 |
+------------+------------------------------------------------------------------------+-----------+----------+

What are the top routes by gender?

This query looks at top routes by gender. Here we get the top female routes in 2016. This query can easily be edited for different years and genders.

Web UI

Open the following query in the Web UI

#standardSQL
SELECT
  CONCAT(start_station_name, " to ", end_station_name) AS route,
  COUNT(*) AS num_trips
FROM
  bigquery-public-data.new_york.citibike_trips
WHERE
  gender = "female"
  AND CAST(starttime AS string) LIKE '2016%'
GROUP BY
  start_station_name,
  end_station_name
ORDER BY
  num_trips DESC
LIMIT
  5

Command-line 

bq query --use_legacy_sql=false '
SELECT
  CONCAT(start_station_name, " to ", end_station_name) AS route,
  COUNT(*) AS num_trips
FROM
  `bigquery-public-data.new_york.citibike_trips`
WHERE
  gender = "female"
  AND CAST(starttime AS string) LIKE "2016%"
GROUP BY
  start_station_name,
  end_station_name
ORDER BY
  num_trips DESC
LIMIT
  5'

The results are shown here:

+------------------------------------------------------+-----------+
|                        route                         | num_trips |
+------------------------------------------------------+-----------+
| W 21 St & 6 Ave to W 22 St & 10 Ave                  |       942 |
| N 6 St & Bedford Ave to Wythe Ave & Metropolitan Ave |       905 |
| W 21 St & 6 Ave to 9 Ave & W 22 St                   |       809 |
| Wythe Ave & Metropolitan Ave to N 6 St & Bedford Ave |       769 |
| W 22 St & 10 Ave to W 21 St & 6 Ave                  |       765 |
+------------------------------------------------------+-----------+

About the data

Dataset Source: Citi Bike

Category: New York City, Transportation

Use: For public use, subject to Citi Bike’s Data License Agreement.

Update Frequency: Daily

View in BigQuery: Go to NYC Citi Bike dataset

Send feedback about...

This page
Documentation feedback
BigQuery Documentation
Product feedback