Diving into the many underlying trends throughout the entire 1.5 Billion rows of NYC Taxi data with Pivot Billion

The age of data has arrived. With it, more and more datasets are created and they just keep getting bigger. Whether dealing with private or open data, individuals and organizations across the world are realizing that there are enormous amounts of information and insights to be gained from massive data. I wanted to dive into one of these accumulated datasets, the public NYC Taxi and Limousine Commission Trip Record Data, to explore passenger and taxi trends over the years. I decided to use Pivot Billions to analyze the data due to its scalability to handle massive datasets.

Using Pivot Billions, I took the over 208 compressed csv files to load 1.5 Billion rows of data into 170 Amazon c4.large instances in 3 minutes. Now that the data was loaded, I explored the data using Pivot Billions’ reorganization and transformation features. One thing I noticed right away is the data had tip and total taxi cost as separate columns. It’s more useful to compare percentages so I created a new tip percent metric from those columns using Pivot Billions’ f(x) function (took about 4 seconds). Another messy data property I noticed was overlapping payment type codes. As seen in the following column distribution, the codes were modified over the years and came in both cases as well as abbreviated and even numeric.

I quickly applied a lookup table in Pivot Billions to create a new, cleaner transformed column called PayType. Now that my data was clean and enhanced enough to draw some meaningful insights, I simply pivoted my data to get the number of taxi trips and taxi payment statistics by PayType, year, and tip_percent.

By entering Pivot Billions’ PivotView, I visualized the data using a heatmap to compare the distribution of tips for cash and credit card passengers.

It was immediately clear that people paying in cash generally did not pay a tip or, more likely, the driver did not report a tip. However, credit card users typically paid ~16% tip, possibly due to the ease of the taxis’ touch panel payment system for credit card users.

Now that I understood a bit more about passenger and taxi tip behavior I wanted to see if I could find any other trends underlying the data. Quickly pivoting the data again to view the number of trips and trip distance statistics by year and month, I started to draw some new insights. By visualizing the data as a LineChart in PivotView and comparing the total trip distances by year to the average trip distances by year, I noticed an interesting discrepancy.

Though the total trip distances were decreasing year over year, the average distances were rising slightly. The growing popularity of ridesharing in recent years is likely responsible for this trend, a nearly 40% reduction in total trip distances logged from 2009 to 2017.

By incorporating the rideshare data from the NYC Taxi and Limousine Commission Trip Record Data , and doing a quick comparison of total 2015 -2018 trips in the months of January, it shows a pretty remarkable shift in the riding habits in NYC.

This chart seems to validate the underlying trend surmised in the prior analysis, that rideshare growth has eroded yellow taxi ridership by nearly 40%.

© 2019 Data Science Central ® Powered by

Badges | Report an Issue | Privacy Policy | Terms of Service

**Most Popular Content on DSC**

To not miss this type of content in the future, subscribe to our newsletter.

**Technical**

- Free Books and Resources for DSC Members
- Learn Machine Learning Coding Basics in a weekend
- New Machine Learning Cheat Sheet | Old one
- Advanced Machine Learning with Basic Excel
- 12 Algorithms Every Data Scientist Should Know
- Hitchhiker's Guide to Data Science, Machine Learning, R, Python
- Visualizations: Comparing Tableau, SPSS, R, Excel, Matlab, JS, Pyth...
- How to Automatically Determine the Number of Clusters in your Data
- New Perspectives on Statistical Distributions and Deep Learning
- Fascinating New Results in the Theory of Randomness
- Long-range Correlations in Time Series: Modeling, Testing, Case Study
- Fast Combinatorial Feature Selection with New Definition of Predict...
- 10 types of regressions. Which one to use?
- 40 Techniques Used by Data Scientists
- 15 Deep Learning Tutorials
- R: a survival guide to data science with R

**Non Technical**

- Advanced Analytic Platforms - Incumbents Fall - Challengers Rise
- Difference between ML, Data Science, AI, Deep Learning, and Statistics
- How to Become a Data Scientist - On your own
- 16 analytic disciplines compared to data science
- Six categories of Data Scientists
- 21 data science systems used by Amazon to operate its business
- 24 Uses of Statistical Modeling
- 33 unusual problems that can be solved with data science
- 22 Differences Between Junior and Senior Data Scientists
- Why You Should be a Data Science Generalist - and How to Become One
- Becoming a Billionaire Data Scientist vs Struggling to Get a $100k Job
- Why do people with no experience want to become data scientists?

**Articles from top bloggers**

- Kirk Borne | Stephanie Glen | Vincent Granville
- Ajit Jaokar | Ronald van Loon | Bernard Marr
- Steve Miller | Bill Schmarzo | Bill Vorhies

**Other popular resources**

- Comprehensive Repository of Data Science and ML Resources
- Statistical Concepts Explained in Simple English
- Machine Learning Concepts Explained in One Picture
- 100 Data Science Interview Questions and Answers
- Cheat Sheets | Curated Articles | Search | Jobs | Courses
- Post a Blog | Forum Questions | Books | Salaries | News

**Archives**: 2008-2014 | 2015-2016 | 2017-2019 | Book 1 | Book 2 | More

**Most popular articles**

- Free Book and Resources for DSC Members
- New Perspectives on Statistical Distributions and Deep Learning
- Time series, Growth Modeling and Data Science Wizardy
- Statistical Concepts Explained in Simple English
- Machine Learning Concepts Explained in One Picture
- Comprehensive Repository of Data Science and ML Resources
- Advanced Machine Learning with Basic Excel
- Difference between ML, Data Science, AI, Deep Learning, and Statistics
- Selected Business Analytics, Data Science and ML articles
- How to Automatically Determine the Number of Clusters in your Data
- Fascinating New Results in the Theory of Randomness
- Hire a Data Scientist | Search DSC | Find a Job
- Post a Blog | Forum Questions

## You need to be a member of Data Science Central to add comments!

Join Data Science Central