NYC Citi Bike trips visualization using keplergl

Visualizing Urban Mobility

E-Scooter Trips in Chicago vs. Citi Bike Journeys in NYC Using Python and Google BigQuery

Authored by Bogumiła Papiernik, Jolanta Śliwa and Karolina Źróbek

In the times of increasing population density in cities, high costs of owning and maintaining a car and finally environmental and health concerns related to exhaust emissions, it’s no surprise alternative means of transport are gaining popularity. Electric scooters and bikes are available for rent in many big cities offering citizens faster, more economic, more ecological and healthier ways to get around the city.

In this article we analyze data from Chicago and New York City to learn the habits of users of previously mentioned ways of communication, the most frequently used routes and the hours of peak traffic. Notebooks exploring both datasets along with example charts and maps can be found in our Github repository.

Table of contents

Chicago E-scooter Trips

• Data import
• Filtering Outliers and Data Preparation
• Trip Counts by Start/End Areas
• Trip Counts in Time

NYC Citi Bike Trips

• Setting Up Google BigQuery
• Exploring the Dataset in Python Notebooks
• Filtering Outliers
• Most Popular Routes

4D Visualization Using kepler.gl

Chicago E-scooter Trips

In the first part of this article, we are going to show the data analysis of electric scooter trips in the City of Chicago. We used data collected during a pilot program featuring electric scooter trips taken in the 2019 and 2020. Both 2019 and 2020 datasets are available on Chicago Data Portal. There is a total of 710839 scooter trips in a combined dataset.

Data import

Data was imported using sodapy — python client for the Socrata Open Data API.

Using Socrata we are able to get records from cityofchicago API where E-scooter trips dataset is available. To do so, the dataset_identifier and limit of records have to be passed. Results are returned as a list of python dictionaries that will be passed to pandas DataFrame which will be used to store records to make analysis easier. Receiving data from API might take a while.

import pandas as pd
from sodapy import Socrata

def load_data_by_id(
   client: Socrata, dataset_identifier: str = "2kfw-zvte", limit: int = 2000
) -> pd.DataFrame:
   # First `limit` results, returned as JSON from API / converted to 
   # Python list of dictionaries by sodapy.
   results = client.get(dataset_identifier, limit=limit)
   return pd.DataFrame.from_records(results)


client = Socrata("data.cityofchicago.org", None)
scooters_2019= load_data_by_id(client, "2kfw-zvte", 710839)

scooters_2019.head()

Filtering Outliers and Data Preparation

Before analysis filtering outliers is recommended for this dataset. The main way of outlier detection was an assumption that outliers are values that are more than three standard deviations from the mean value.

The datapoints above or below 3*sigma should be filtered out.

• trip_duration column includes individual trips shorter than a minute or longer than few hours (57h maximum) which are quite unrealistic.
• trip_distance column similarly contains values lower than 10m or higher than 50000 km which seems to be erroneus and might intrerfere in performing reliable analysis.
• trip_speed column most probably includes incorrect measurement of e-scooters driving 60km/h.

## Trip duration filtering
# low pass filter for trips shorter than 1 minute
reduced_trip_duration = scooters[scooters["trip_duration"] >= 60]
# high pass filter for data > 3*std
mean = reduced_trip_duration["trip_duration"].mean()
std = reduced_trip_duration["trip_duration"].std()
higher_limit = mean + 3 * std
reduced_trip_duration = reduced_trip_duration[reduced_trip_duration["trip_duration"] <= higher_limit

## Trip distance filtering
# low pass filter for trips shorter than 10 meters
reduced_trip_distance = scooters[scooters["trip_distance"] >= 10] 
# high pass filter for data > 3*std
mean = reduced_trip_distance["trip_distance"].mean()
std = reduced_trip_distance["trip_distance"].std()
higher_limit = mean + 3 * std
reduced_trip_duration = reduced_trip_duration[reduced_trip_duration["trip_duration"] <= higher_limit

# Trip speed filtering
# high pass filter for speeds over 60km/h (17m/s) 
reduced_trip_speed = scooters[scooters["trip_distance"] / scooters["trip_duration"] <= 17]

To be able to work on this data numeric columns like trip_duration or trip_distance were transformed to numeric type and all columns with time data like start/end time to datetime type.

numeric_columns = [
  "trip_distance",
  "trip_duration",
  "accuracy",
  "start_community_area_number",
  "end_community_area_number",
  "start_centroid_latitude",
  "start_centroid_longitude",
  "end_centroid_longitude",
  "end_centroid_location",
]
scooters_2019[numeric_columns] = scooters_2019[numeric_columns].apply(
  pd.to_numeric, errors="ignore"
)
scooters_2019[["start_time", "end_time"]] = scooters_2019[["start_time", "end_time"]].apply(pd.to_datetime)

After detecting and removing outliers, datasets were checked for duplicates based on trip_id.

After changes, we are now able to start analysis on this dataset.

Trip Counts by Start/End Areas

First, we are going to look at the most popular areas of the city. Thanks to pandas we can plot this information using value_counts() command will count number of occurrences for chosen column.

Below code visualizes top 45 start area - end area pairs:

scooters_2019.apply(
  lambda x: (
    x.start_community_area_name, 
    x.end_community_area_name
  ), axis="columns"
).value_counts().nlargest(45).plot(kind="bar", title="Start-end area counts")

The plot focuses on the top 45 most-traveled routes to simplify the visualization, highlighting key start-end area pairs.

Taking into account the above value counts for routes between community areas, there is a large dominance of trips inside one area. Near West Side, the most popular, is a hub of activity due to its proximity to institutions like the University of Illinois and major landmarks such as the United Center and Chicago Union Station. This area’s popularity can be attributed to the high concentration of students, professionals, and visitors who use e-scooters for commuting.

West Town, adjacent to Near West Side, features community areas like Wicker Park and Ukrainian Village, offering parks and neighborhoods where e-scooters facilitate local transport. Logan Square, neighboring West Town, also sees substantial e-scooter usage, supported by its cultural centers and historical neighborhoods.

Trip counts in time

Next we looked how numbers of trips changed over time. As previously, we used value_counts() on date part of values in end time column to get the number of trips for each day. We can use sort_index to plots data chronologically and not from the date with the largest number of trips to the one with the smallest.

scooters_2019["end_time"] \
    .dt \
    .date \
    .value_counts() \
    .sort_index() \
    .plot(kind="line", title="Trip counts by date", rot=45)

The plot shows a noticeable change towards the end, with sparse data for October, mainly on October 15, 2019. This month is excluded from the analysis. There’s a decreasing trend over time, likely related to dropping temperatures transitioning from summer to autumn. Conversely, there’s an increasing trend at the plot’s start, indicating rising temperatures during summer. Local minimums, like those on July 29 and 18, 2019, may be due to adverse weather conditions, such as storms in Chicago. High trip counts were observed during the Independence Day holiday weekend, peaking near 7000 trips. However, the count dipped on Friday (the 5th), possibly due to another storm or heightened violence associated with the holiday.

Trips count by month, weekday and hour

Similar to previous plots, we apply value_counts aggregated in month, weekday and hour. Use below code to explore more by creating bar plots:

# Trips by month
scooters_2019["start_time"] \
  .dt \
  .month \
  .value_counts() \
  .sort_index() \
  .plot(
    kind="bar", title="Trips by month", rot=0
  )

# Trips by day of the week

days_of_week = [
  "Monday",
  "Tuesday",
  "Wednesday",
  "Thursday",
  "Friday",
  "Saturday",
  "Sunday"]

pd.Categorical(
  scooters_2019["start_time"] \
  .dt \
  .day_name(), 
  categories=days_of_week, 
  ordered=True
).value_counts().sort_index().plot(
  kind="bar", title="Trips by day of the week", rot=45
)

# Trips by hour
scooters_2019["start_time"] \
  .dt \
  .hour \
  .value_counts() \
  .sort_index() \
  .plot(kind="bar", title="Hours counts")

Monthly aggregation shows a clear link between temperature, weather, and trip numbers. July, the sunniest and hottest month, had the highest trip count. August saw more stormy days, leading to a slight decrease in trips. September, even cooler and less sunny, continued this trend.

Weekends show an increase in trips, though Sundays see a significant drop, possibly due to university areas having fewer social activities. Matches and cultural preferences may also influence Sunday’s low trip count. Weekdays exhibit a less predictable trend, possibly influenced by weather and limited data spanning only about four months.

Explore more time aggregation bar plots by running the code above.

The highest numbers of trips are recorded in early evening hours (6 PM). These hours are the most popular ones for all people to go back home from work, go out to see friends, go shopping, visit theatre or similar. There is not a visible local increase around the hour of the start of the working day. E-scooter might be used mostly by younger generation. With this assumption and months that are included in this dataset, users might use e-scooters mostly for reasons mentioned above but not necessary for transport to/from work since holiday months are a large part of the dataset. As it is expected, there are almost no trips during the night.

NYC Bike Trip analysis using Google BigQuery

In the first part of this article, we analyzed the Chicago e-scooters dataset using Python, uncovering trends and usage patterns. Now, we’ll shift to the NYC Citi Bike program.

In this second part, we’ll use Google BigQuery to analyze the NYC Citi Bike dataset. BigQuery’s powerful infrastructure allows us to efficiently handle large data volumes and perform complex queries.

We’ll introduce the NYC Citi Bike dataset, set up BigQuery, load the data, and connect to it using Python. Finally, we’ll analyze and visualize the data, comparing it with Chicago e-scooter data to draw insights about urban mobility in both cities.

Setting Up Google BigQuery

Usage note

Google BigQuery is a powerful tool within Google Cloud designed for fast and efficient data analysis. However, to use BigQuery, you will need to have Google Cloud credits. Google offers free credits for up to 1TB of queries, but be mindful that the NYC Citi Bike dataset is large, and you might quickly exceed this limit.

Getting Started

To get started with Google BigQuery, follow these steps:

1. Set Up a Google Cloud Project

First, you’ll need to set up a project in Google Cloud Console. If you don’t already have a project, create one by following this tutorial. Ensure that you have billing set up in your Google Cloud account. You may need to use your free credits initially.

2. Access BigQuery

Once your project is set up, go to the BigQuery page in Google Cloud Console. Select your new project from the project selector at the top of the page. In the “Search for resources, documents, services, and more” search bar, type “BigQuery” and select it from the list.

Click on “SQL Query” to start writing and executing SQL queries.

You are now ready to explore and query the NYC Citi Bike dataset. Let’s start by running a simple query that will return first 10 rows from the dataset:

SELECT *
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE starttime IS NOT NULL
ORDER BY starttime
LIMIT 10

You can explore the data right away using the “Chart” tab in the “Query Results” panel. In the next section, we will guide you through analyzing the queried data using Python.

Exploring the Dataset in Python Notebooks

To explore the data acquired by SQL queries in python notebook click “View Data” and choose “Explore using python notebook”. Next you can start using the data as a pandas Dataframe. The query results will be imported automatically using the “jobs.query” method.

# Running this code will display the query used to generate your previous job
job = client.get_job('job_id_will_be_here') # Job ID inserted based on the query results selected to explore
print(job.query)

## Also a code snippet for importing the data into bigquery Dataframe will be generated for you (this is not yet a pandas Dataframe!)
# Running this code will read results from your previous job
job = client.get_job('job_id_will_be_here') # Job ID inserted based on the query results selected to explore
destination = job.destination

# Load data from a BigQuery table using BigFrames DataFrames:
bq_df = bpd.read_gbq(f"{destination.project}.{destination.dataset_id}.{destination.table_id}")
bq_df

Lastly the data can be converted to pandas Dataframe and sorted by year in the following way:

# Convert BigQuery DataFrame to pandas DataFrame.
pandas_df = bq_df.to_pandas()
pandas_df.sort_values(by=['year'], inplace=True)

First Look at the Data Using plotly

To answer basic statistical questions regarding the dataset we will create some beautiful charts in python notebook using plotly. Plotly offers extensive customization options, allowing you to adjust colors and other features to suit your needs. For detailed guidance on customizing your charts, you can refer to the plotly documentation.

Get Karolina M Zrobek’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

First let’s see if the Citibikes’ popularity was rising or dropping over the years.

SELECT EXTRACT(YEAR FROM starttime) AS year, 
 COUNT(DISTINCT(bikeid)) AS num_bikes
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
GROUP BY year
ORDER BY year;

Create a graph:

import plotly.graph_objects as go
# Define custom colors for the bars
colors = ['#00CED1', '#7FFFD4', '#40E0D0', '#800080', '#BA55D3', '#9400D3']

# Create a bar plot
fig = go.Figure(data=[go.Bar(x=pandas_df['year'], y=pandas_df['num_bikes'], marker_color=colors)])
# Update layout for better visualization
fig.update_layout(
 title='Number of Bikes Over the Years',
 xaxis_title='Year',
 yaxis_title='Number of Bikes',
 xaxis={'type': 'category'}
)
# Save the figure as an HTML file
fig.write_html("numbikes_plot.html")
# Render the plot 
fig.show()

We can observe a rising popularity trend over 2013–2017 and then slight drop in numbers.

How about exploring gender-related differences in Citibikes’ usage over the years?

SELECT EXTRACT(YEAR FROM starttime) AS year,
 COUNT(CASE WHEN gender = 'female' THEN 1 END ) AS 
 count_female,
 COUNT(CASE WHEN gender = 'male' THEN 1 END ) AS count_male
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
GROUP BY year
ORDER BY year;

Create a graph:

# Create bar plot
fig = go.Figure()
# Add female bars
fig.add_trace(go.Bar(
 x=pandas_df['year'],
 y=pandas_df['count_female'],
 name='Female',
 marker_color='rgb(255, 127, 14)',
 text=pandas_df['count_female'],
 textposition='auto'
))
# Add male bars
fig.add_trace(go.Bar(
 x=pandas_df['year'],
 y=pandas_df['count_male'],
 name='Male',
 marker_color='rgb(44, 160, 44)',
 text=pandas_df['count_male'],
 textposition='auto'
))
# Update layout
fig.update_layout(
 title='Number of Users by Year and Gender',
 xaxis_title='Year',
 yaxis_title='Number of Users',
 barmode='group', # Group bars
 template='plotly_white' # White background
)
# Show plot
fig.show()

The results show a big gender gap between male and female users every year.

In Google BigQuery you can also combine data from other NYC datasets. Find out more about NYC datasets by watching this youtube video. In the following example, we will compare trip durations in 2014 and 2015 by the month of the year, with meteorological data from this period to examine if there might be a link between them. Specifically we will focus on the temperature. The period 2014–2015 is chosen because the field “trip duration” contains the fewest null values compared to other years

.

There are generally more trips in 2015 than 2014. Let’s look at some example months. August 2014 was much colder than August 2015 and there are indeed fewer trips in August 2014 than August 2015, the same goes for October, however 2015 in total had more trips than 2014 so further analysis would be needed in this manner.

We encourage you to explore SQL queries yourself. For more examples you can look at the NYC Citi Bikes Data Card.

Handy Data Sampling Query

The NYC Citi Bikes dataset is quite large, making it excellent for data mining projects. However, this can be daunting for those wanting to explore it for practice or who have limited resources for analysis. Therefore, it is useful to use the following query to sample a desired number of rows from the entire dataset, covering the years 2013–2018.

SELECT *
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
LIMIT [number_of_rows] OFFSET [skip_rows_interval]
For example to select 10000 rows that will contain data through the whole dataset min and max time you can use:
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
LIMIT 10000 OFFSET 10000

The above query will result in a pandas dataframe that will be especially useful for on-map visualization.

Filtering Outliers

Just as in the Chicago dataset we performed outlier detection using values of mean and standard deviation. We decided to check if there are any trips that have the same start_station_id and end_station_id. Entries like that will not be useful since we don’t know anything about the trips’ routes. There were 228 rows that matched this condition. We removed them from the dataset.

## Trip duration filtering
# Calculated higher limit was 30705 seconds (8.5 hours)
max_tripduration = nyc["tripduration"].max()
mean = nyc["tripduration"].mean()
std = nyc["tripduration"].std()
higher_limit = mean + 3 * std
reduced_tripduration = nyc[nyc["tripduration"] <= higher_limit]

# Filtering by station_id outliers
nyc[nyc["start_station_id"] == nyc["end_station_id"]]
reduced_station_id = nyc[nyc["start_station_id"] != nyc["end_station_id"]]

Most Popular Routes

Similar to Chicago dataset we prepared routes plot. As before we used python and subset of 10000 records from data analysed using BigQuery. This time we have TOP 10 routes from the subset.

Let’s examine the neighborhoods around these stops:

1. Residential & Recreational: Citibikes are popular in areas like West Thames St and South End Ave & Liberty St, surrounded by apartments, parks, and recreational facilities, indicating use for commuting and leisure.
2. Commercial & Business: Stops near Vesey Pl & River Terrace and Liberty St & Broadway are close to business centers and shopping areas, suggesting Citibikes are used for short trips within these commercial hubs.
3. Tourist Attractions: Locations like Broadway & Battery Pl cater to tourists with nearby landmarks, implying Citibikes are used for sightseeing and exploration.
4. Transportation Hubs: Stops near Liberty St & Broadway serve as last-mile connections to transit stations, facilitating commuting.
5. Educational Institutions: Citibikes near West St & Chambers St and Greenwich St & Warren St may serve students and faculty commuting to nearby educational institutions.
6. Tourism & Hospitality: Stops like Pearl St & Hanover Square, near hotels and dining places, indicate Citibikes cater to tourists and hospitality workers.
7. Landmark Accessibility: Locations near W 52 St & 5 Ave and W 33 St & 7 Ave provide convenient access to iconic landmarks, making Citibikes a popular choice for tourists and locals alike.

In summary, Citibikes serve various purposes including commuting, recreation, tourism, and accessing amenities across different neighborhoods of New York City.

Visualization Using kepler.gl

Kepler.gl is an open-source geospatial analysis tool created by Uber’s data visualization team. It is designed to handle large-scale geospatial data and provides an interactive web-based interface for visualizing and analyzing this data. You can explore the possibilities of this tool simply using a web demo.

For trip visualization three main features of kepler maps will be used:

1. Points — create a point on map, in our case points will designate start and and station locations
2. Arc — creates arc between two points on the map, particularly effective for 3D visualizations. The arc will illustrate trips between two given points.
3. Time filters — this feature will allow for 4D visualization, not only taking space into consideration, but also changes in data over time.

The kepler.gl can be used in python notebooks by installing pypi packedge:

pip install keplergl

Let’s visualize Chicago E-Scooters dataset first. Assuming the dataset has been loaded into trips_df, below code creates a map that illustrates e-scooter trips in the form of arcs. Each trip is represented by an arc that connects start and end station points on the map of Chicago:

import keplergl
#Creating base map
map = keplergl.KeplerGl(height=600, widht=800)
#Adding layer containing e-scooter trips
map.add_data(data=trips_df, name="Trip Data")
map

You can customize your map in the map widget itself (colors, 3D / 2D visualization and more). Especially use the ”Layers” tab on the left hand side and change start_station , end_station and Arc layers to visible.

More layers can be added to the map. For example a layer containing bounds of Chicago community areas can be added. Community Areas dataset is publicly available and just as e-scooter trips dataset it can be imported using Socrata.

from sodapy import Socrata
#get community areas data
client = Socrata("data.cityofchicago.org", None)
com_areas_bounds = load_data_by_id(client,"igwz-8jzy", 2000 )
map.add_data(data=com_areas_bounds, name="Community Areas")
map

The map should now look similar to this:

Adding time filter

We can also visualize changes in time to the map. This can be achieved in kepler.gl web demo. Save NYC Citibikes Lite Data (selected 10000 rows sample) into a csv file. See this tutorial on how to save pandas Dataframe into a csv file.

Next upload it in the keplergl demo app. Customize your layers, and make start_station, end_station and Arc visible.

Now use the Filter tab and create a filter based on starttime (or endtime).

And that’s it! Your 4D visualization will now look similar to:

Conclusion

In this article, we presented a step-by-step guide on how to preprocess and analyse data in Python. Data from two different urban mobility systems: Chicago’s e-scooter program and NYC’s Citi Bike program have been explored and visualized. By leveraging Python and Google BigQuery, we were able to gain valuable insights into user behaviour, trip characteristics, and usage patterns.

Chicago E-Scooters:

• E-scooters are primarily used for short trips within the same community area.
• There is a correlation between ridership and weather, with usage dropping as temperatures decrease.
• Weekends see a higher number of trips compared to weekdays, with Friday being the most popular day.
• The most frequent trips are likely related to student commutes and entertainment activities near universities.

NYC Citi Bikes:

• Citi Bike ridership experienced a growth trend from 2013 to 2017, with a slight decline in 2018.
• Gender data reveals a potential increase in female ridership over the years.

Both e-scooters and Citi Bikes cater to short-distance trips within urban areas. E-scooters seem to be more popular for transport purposes, while Citi Bikes might be used for both commuting and recreation. Weather has a significant impact on ridership for both systems.