Citibike Ridership

This projct analyzes the relationship between daily NYC weather conditions
and Citibike ridership trends (2013 - 2018) to build a predictive linear regression
model and an interactive web application.

Using public datasets from Citibike and NOAA hosted on Google BigQuery, this application
provides real-time model predictions and searchable data tables to help forecast bike-share demand based on weather conditions.

**Live app: ** citibike-ridership.streamlit.app

• End-to-End Ownership: Designed, developed and deployed the entire project,
  from data ingestion, modeling, frontend UI and cloud deployment automation.

• Data Pipeline & Analysis: Formulated SQL queries to extract and join public Citibike and NOAA weather datasets from Google BigQuery, cleaned/preprocessed raw data in Pandas,
  and engineered calendar and weather features.

• Model Development: Trained and evaluated a linear regression model in Scikit-learn, handling missing value edge cases (99.99 precipitation markers) and performing residual analysis.

• Application & Infrastrucutre: Built the interactive Streamlit dashboard using DuckDB
  for in-process querying and configured a GitHub Actions CI/CD workflow with headless Selenium to keep the Streamlit app active.

• Predictive Pipeline: Preprocessed multi-year daily ride and weather data,
  engineered key features (temperature, precipitation, windspeed, calendar metrics) and trained a linear
  regression model achieving a test R² score of 0.6686.

• Interactive Web Application: Built and deployed a live Streamlit dashboard allowing users
  to query ridership predictions and explore historical trend visualizations interactively

• Automated Data Pipeline Infrastructure: Integrated DuckDB with BigQuery for efficient in-process
  SQL querying and established a Github Actions workflow using headless Selenium to maintain Streamlit app uptime.

One data issue that I found was a single missing observation value for the precipitation metric as denoted by the value ‘99.99’ which government datasets often encode for missing values. As this was one value out of 1610 data rows, I felt that removing this row was not going to pose a detrimental impact to the quality of the dataset. To handle the missing value, I replaced the ‘99.99’ with ‘NaN’, to denote that this special value was ‘Not a Number.’ This then allowed me to drop the row during the preprocessing phase and prep the data to be analyzed.

Features that were engineered for this project included: Temperature, precipitation, wind speed, days of the week and Year.

For the linear regression model where we’re trying to determine what (if any) relationship there is between the features to be engineered (Temperature, precipitation, wind speed, days of the week and Year) and the number of rides (which is what we’re trying to predict).

These features were taken as they are known in advance and are measurable. We’re trying to predict the number of riders for a given day based on these features as best as we can.

• Predictive Capability: The linear regression model achieved a test R² score of 0.6686 (training R² of 0.7394), establishing that temperature, precipitation, wind speed, day of week and year account for ~ 67% of the variance in daily CitiBike ridership

• Primary Drivers of Demand: Temperature proved to be the strongest positive predictor of daily rides, while precipitation showed a strong inverse relationship with ridership volume.

• Data Quality & Anomaly Finding: Identified a critical 4-month gap in early 2017
  within the primary dataset.

After the testing and training process which takes our dataset to optimize for prediction, the metric that we’re using R² to gauge how much of the ups and downs in daily ridership our model can account for. For testing, the R² score is: 0.6686 which means that the model explains less variance on data that is unseen. For training, the R² score increased to: 0.7394. The training R² of 0.7394 indicates that ~73.9% of the variance in daily ridership is explained by the model's features (temperature, precipitation, wind speed, day of week, and year).

The biggest weakness I see is that in 2017 at the start of the year, there was a period of 4 months where we had no input on ridership which impacted the data testing and training. To address this issue, a good starting point would be to do some investigation to see what led to the gap and see if there are any secondary resources to fill in the data gap if we’re not able to locate the primary data during this period.

This project uses DuckDB with the community BigQuery extension to query Google BigQuery's public datasets directly. It also uses the google-cloud-bigquery Python client for a subset of queries. The front end is built with Streamlit that allows for interactive, searchable data tables.

To prevent the app from spinning down due to inactivity, a GitHub Actions workflow runs every 7 hours via a cron schedule and uses Selenium to ping the app via the GitHub runner, keeping it awake.

This project authenticates to Google BigQuery in two ways:

1. DuckDB connects via the community bigquery extension using a TYPE bigquery secret, authenticated with an ACCESS_TOKEN generated from service account credentials.
2. The google-cloud-bigquery Python client authenticates separately using service_account.Credentials.from_service_account_info().

To run this project locally, create a .streamlit/secrets.toml file in the project root with your own GCP service account credential. Be sure to provision the appropriate IAM roles for service account accordingly:

[gcp_service_account]
type = "service_account"
project_id = "your-project-id"
private_key_id = "your-private-key-id"
private_key = "your-private-key"
client_email = "your-service-account-email"
client_id = "your-client-id"

This file is excluded from version control via .gitignore and should never be committed.

If you're planning on using the Streamlit Community cloud to share your app, you'll need to implement the Streamlit Secrets Manager when deploying the app.

You'll need to navigate to the Advanced Settings when deploying Streamlit Community Cloud app and populate the environment variables that were configured in .streamlit/secrets.toml in your local development environment.

For more info, refer to: Streamlit Secrets Manager