Data Processing on a New York City Taxi Dataset with Databricks

PS – These projects are from year 2022 – year 2023. Restored these post from old hosting service.

Databricks Notebook Link

1. Introduction

The aim of this project is to analyse the New York Taxicab dataset from January 2019 to April 2022 to answer critical business problem statements — and to create a machine learning model predicting taxi ride fares.

2. Problem Statement

• Store data in Azure Blob Storage; perform data cleaning.
• Combine yellow and green taxi datasets; save the parquet file in DBFS.
• Answer business questions with Spark SQL: trips per year/month; busiest weekday and hour; average passengers; average amount per trip and per passenger; trip duration/distance/speed stats per taxi colour; tip percentages; duration-bin analysis and which bin a driver should target.
• Create an ML model predicting total fare, applied to April 2022 data.

3. Dataset

Since 1971, the NYC Taxi and Limousine Commission (TLC) has published millions of yellow and green taxi trip records: pick-up/drop-off times and locations, distances, rates, payment types, and passenger counts. Yellow cabs hail street passengers citywide (~13,600 medallions); green cabs (since August 2013) improve borough availability.

Key fields (yellow: tpep_* datetime; green: lpep_* datetime; otherwise shared): VendorID, pickup/dropoff datetime, passenger_count, trip_distance, PULocationID/DOLocationID, RateCodeID, store_and_fwd_flag, payment_type, fare_amount, extra, mta_tax, improvement_surcharge, tip_amount, tolls_amount, total_amount, congestion_surcharge, airport_fee (yellow), trip_type (green).

4. Architecture

Three core platforms: Azure Blob Storage (data lake; account rajatsinghuts, container bde-at-2), Apache Spark (distributed processing), and Databricks (managed Spark for the full ML lifecycle).

5. Project Structure and its Processes

• Uploaded green and yellow taxi files to Blob storage; mounted the storage in a Databricks cluster.
• Data Cleaning: removed trips ending before starting, negative or extreme speeds, implausible distances/durations, >5 passengers, <30 seconds, and dates outside 2019-01-01–2022-05-01.
• Transformation: added time_duration (hours) and speed (mph).
• Merging: renamed datetime columns to pickup_datetime/dropoff_datetime, added taxi_type, merged with missing-column support; saved dbfs/taxis_combined_dataset.parquet.
• Analytics: Spark SQL for business questions. ML: linear regression and decision tree on trip_distance, time_duration, taxi_type → total_amount.

7. Business Problems and Solutions

Trips per year/month, busiest weekday/hour, averages per trip and per passenger:

Duration, distance and speed stats per taxi colour:

Percentage of trips with tips:

Of tipped trips, percentage with tips ≥ $10:

Duration bins (under 5 min … 60+ min): average speed and distance per dollar:

Which bin should a driver target? The 5–10 minute bin — very low average distance per dollar (lower fuel cost) with a substantially high trip count.

8. Machine Learning

• Train: dates before April 2022; test: April 2022 onward.
• Features: trip_distance, time_duration, taxi_type → total_amount (StringIndexer + OneHot for categoricals, assembled into a vector).
• Models: Linear Regression (train RMSE 154.46) vs Decision Tree (154.48) — linear regression chosen; test RMSE 7.08.

9. Conclusion

A linear regression model with test RMSE ≈ 7.08 predicts fares, and all business questions were answered with Spark SQL.