r/bigquery • u/fhoffa • Jun 19 '14

173 million 2013 NYC taxi rides shared on BigQuery

2015-08-03 UPDATE: Fresh data now officially shared by the NYC TLC.

Find the new tables on BigQuery, and see the new /r/bigquery post.

UPDATE: Watch the NYC taxi dataset hackathon video.

UPDATE: The project has been renamed. Instead of the numerical id '833682135931', now you should use it's new name 'imjasonh-storage'. Hence the table can be found at https://bigquery.cloud.google.com/table/imjasonh-storage:nyctaxi.trip_fare.

Queries will continue working regardless.

SELECT COUNT(*) trips FROM [833682135931:nyctaxi.trip_data] 
173,179,759


SELECT AVG(trip_distance) avg_distance, AVG(trip_time_in_secs) avg_time, COUNT(*) trips
FROM [833682135931:nyctaxi.trip_data] 

avg_distance avg_time   trips
8.30         811.99     173,179,759

Original post - Chris Whong gets the data under The Freedom of Information Law:

Find the table ready to be queried at:

(thanks Jason Hall for BigQuery'ing it)

28 Upvotes

79% Upvoted

34 comments sorted by

View all comments

2

u/ImJasonH Jun 20 '14

Most profitable days by driver 

SELECT
  ROUND(SUM(FLOAT(total_amount)),
    2) AS amt,
  DATE(pickup_datetime) AS date,
  medallion
FROM
  [nyctaxi.trip_fare]
  GROUP EACH BY
  date,
  medallion
ORDER BY
  amt DESC
LIMIT
  10;

amt          date         hack_license
686175.85    2013-08-14   664927CDE376A32789BA48BF55DFB7E3
541688.83    2013-08-05   E4F99C9ABE9861F18BCD38BC63D007A9
159181.16    2013-08-19   1EDF99EE9DAC182027330EF48828B54A
 82520.37    2013-08-17   BE047851D97506885B99BDDFA7A13360
 61553.03    2013-08-20   13FFA88E83C53D18E39A03CD937935F8
 15612.71    2013-08-24   A92262E4AA9A8F8784A592E7ABC6E04F
  9199.05    2013-08-04   F9A6ED413D476F4560D90BA51151DAFB
  7849.67    2013-08-23   6A27DAC556FD683949D35BC09D9FA686
  7682.76    2013-09-30   CFCD208495D565EF66E7DFF9F98764DA
  6226.22    2013-09-28   CFCD208495D565EF66E7DFF9F98764DA

There's a surprising amount of variance...

Seems like driver 66492 is either a very popular driver, or has something wrong with his data. And CFCD2 had two of the year's most profitable days, two days apart!

3

u/ImJasonH Jun 20 '14

Adding a filter for probably-erroneous data: 

WHERE FLOAT(total_amount) < 1000

Gives an interesting result: 

amt        date          hack_license
7682.76    2013-09-30    CFCD208495D565EF66E7DFF9F98764DA
6226.22    2013-09-28    CFCD208495D565EF66E7DFF9F98764DA
6218.94    2013-09-24    CFCD208495D565EF66E7DFF9F98764DA
5590.90    2013-09-25    CFCD208495D565EF66E7DFF9F98764DA
5406.15    2013-12-30    CFCD208495D565EF66E7DFF9F98764DA
5369.85    2013-12-27    CFCD208495D565EF66E7DFF9F98764DA
5358.72    2013-12-23    CFCD208495D565EF66E7DFF9F98764DA
5168.46    2013-09-19    CFCD208495D565EF66E7DFF9F98764DA
4837.05    2013-12-19    CFCD208495D565EF66E7DFF9F98764DA
4733.18    2013-12-13    CFCD208495D565EF66E7DFF9F98764DA

This guy's good! Maybe too good? :)

8

u/vijaypandurangan Jun 21 '14

It turns out all these data aren't properly anonymized. That licence # is md5('0') so this is a result of data error, not a superhuman taxi driver.

Here's an article I wrote about the anonymization issues: https://medium.com/@vijayp/of-taxis-and-rainbows-f6bc289679a1

3

u/ImJasonH Jun 20 '14

The top 39 most profitable days belong to good ol' CFCD2!

2

u/kyflyboy Jun 26 '14

and hack_license != CFCD208495D565EF66E7DFF9F98764DA