Analyzing 33 million bike trips with BigQuery
summary
Analyzes 33 million trips from NYC bike share by importing monthly CSVs into Google Cloud Storage with a Node.js script and loading them into BigQuery + Data Studio + Carto for fast SQL queries and maps. Finds fleet grows from ~6,500 to >10,000 bikes by 2016, subscribers make 88% of 2016 trips with shorter durations, age 31 is the peak rider, one bike logs ~5,500 trips, and a 2.1 TB GitHub query returns ~18.8M C# files.
AI-written summary · about
chapters (45)
- 0:00Introduction
- 0:43Story
- 1:44Questions
- 2:45City Bike
- 3:01CSV Data
- 3:40Subscriber Data
- 5:29Visualization
- 5:41Importing data
- 6:10Using the client library
- 6:34Importing data via web UI
- 7:35Linking your app to BigQuery
- 7:47City bike trips table
- 8:35Count rows
- 8:52City bike system
- 9:02Running the query
- 9:27Data Studio
- 9:40Create a custom query
- 9:57Connect to Data Studio
- 10:10Bar Chart
- 10:34Most Popular Stations
- 10:50Map Data
- 11:24Interactive Map
- 12:03Trips by Member Type
- 13:05Create a pie chart
- 13:17Create new data source
- 13:29Create custom query
- 15:37Gender and age
- 16:10Public graphs
- 16:44Routes by gender
- 16:58Routes by men
- 17:31Routes by women
- 18:25Find the bike
- 18:37Run the query
- 19:01Animation
- 19:56Data Visualization
- 20:12Public Data Sets
- 22:01File Table
- 24:08Other public datasets
- 24:52Oslo Bicycle
- 25:08Public Data
- 25:28Personal Projects
- 28:04Map
- 28:34CSharp
- 29:05NYC Bike Data
- 29:34Google Cloud Blog
related talks
33mAnalyzing 33 million bike trips with BigQuery
42m2017 DevFestMN Sara Robinson
34mCityBikes: bike sharing networks around the world
35mPowering Interactive Data Analysis with Google BigQuery
45mMining dockless bikeshare and dockless scootershare trip data
27mNotorious B.I.G. Data: Wins and fails when building a big data platform in the cloud
31mExploring open data with BigQuery
22mUsing BigQuery as a data warehouse in B2B startup