Ask the database a question

Trips between boroughs (the 2021 'route' feature), with median minutes.

• pickup_borough TEXT · Bronx, Brooklyn, EWR, Manhattan, Queens, Staten Island
• dropoff_borough TEXT · Bronx, Brooklyn, EWR, Manhattan, Queens, Staten Island
• trips INTEGER
• median_min REAL

Rows left after every cleaning rule, revived pipeline next to the counts printed by the 2021 notebook.

• step INTEGER
• stage TEXT · Merge, Model, Raw, Round 1, Round 2, Round 3
• label TEXT
• rule TEXT
• revived_rows INTEGER
• notebook_rows INTEGER
• difference_pct REAL

NYPD motor-vehicle collisions per borough, day and hour (2021 BigQuery export).

• borough TEXT · BRONX, BROOKLYN, MANHATTAN, QUEENS, STATEN ISLAND
• date TEXT
• hour INTEGER
• collisions INTEGER

Split-conformal offsets per calibration bin: global symmetric, Mondrian by predicted decile, and Mondrian by pickup borough x predicted bin, at 80%, 90% and 95%.

• scheme TEXT · random, temporal
• method TEXT · global, mondrian, mondrian_borough
• level REAL
• borough TEXT · null, Bronx, Brooklyn, EWR, Manhattan, Queens, Staten Island
• bin INTEGER
• pred_lo REAL
• pred_hi REAL
• n_cal INTEGER
• q_lo REAL
• q_hi REAL

Empirical coverage of each conformal method on held-out trips, overall and by bin, borough and hour, with mean interval width (lower end clipped at 0) and a 95% interval from a bootstrap that resamples whole test days (B = 2,000, seed 20190101).

• scheme TEXT · random, temporal
• method TEXT · global, mondrian, mondrian_borough
• level REAL
• group_type TEXT · all, bin, borough, borough_bin, hour
• group_value TEXT
• trips INTEGER
• covered INTEGER
• mean_width REAL
• days INTEGER
• ci_low REAL
• ci_high REAL

Trips and covered trips per test day for each scheme, method and level: the units the coverage bootstrap resamples.

• scheme TEXT · random, temporal
• method TEXT · global, mondrian, mondrian_borough
• level REAL
• date TEXT
• trips INTEGER
• covered INTEGER

One row per day of 2019: trips, duration statistics, NOAA Central Park weather, permitted events and collisions (summed over boroughs).

• date TEXT
• isodow INTEGER
• trips INTEGER
• median_min REAL
• mean_min REAL
• mean_miles REAL
• mean_fare REAL
• precipitation REAL
• snow REAL
• snow_depth REAL
• tavg REAL
• tmax INTEGER
• tmin INTEGER
• wt01 REAL
• wt02 REAL
• wt03 REAL
• wt06 REAL
• wt08 REAL
• events INTEGER
• collisions INTEGER

Pickups, median minutes, permitted events and collisions per day and borough.

• date TEXT
• borough TEXT · Bronx, Brooklyn, Manhattan, Queens, Staten Island
• pickups INTEGER
• median_min REAL
• events INTEGER
• collisions INTEGER

Rows and missing congestion surcharges per pickup day in the January 2019 file.

• date TEXT
• rows INTEGER
• missing_congestion INTEGER

Missing values per column in each monthly TLC file of 2019.

• source_file TEXT
• column_name TEXT · RatecodeID, any column, congestion_surcharge, passenger_count, store_and_fwd_flag
• missing INTEGER
• rows INTEGER

Implausible records the 2021 rules let through, counted on the final analysis dataset by vendor (reported, not removed).

• key TEXT · high_fare_per_mile, jfk_flat_fare_not_52, over_50_miles, speed_over_60mph, tiny_distance_long_time, total_excludes_congestion, total_other_mismatch, under_one_minute
• label TEXT · Average speed above 60 mph, Longer than 50 miles, Rate code 2 (JFK) fare not $52, Shorter than one minute, Standard rate above $25 per mile (1 mile or more), Total differs from the sum of its parts (other), Total leaves out the congestion surcharge, Under 0.1 mile but over 30 minutes
• condition TEXT · RatecodeID = 1 AND trip_distance >= 1 AND fare_amount / trip_distance > 25, RatecodeID = 2 AND fare_amount <> 52, abs(total_amount - (fare_amount + extra + mta_tax + tip_amount + tolls_amount + improvement_surcharge + congestion_surcharge)) > 0.01 AND NOT (congestion_surcharge > 0 AND abs(total_amount - (fare_amount + extra + mta_tax + tip_amount + tolls_amount + improvement_surcharge)) <= 0.01), congestion_surcharge > 0 AND abs(total_amount - (fare_amount + extra + mta_tax + tip_amount + tolls_amount + improvement_surcharge)) <= 0.01, travel_time < 1, trip_distance / (travel_time / 60.0) > 60, trip_distance < 0.1 AND travel_time > 30, trip_distance > 50
• reason TEXT · A meter that recorded time but almost no distance: a stuck odometer or a waiting fare., At $2.50 a mile plus waiting time, a standard-rate fare far above $25 a mile is unlikely., New York City's top speed limit is 50 mph; an average above 60 over a whole trip points to a distance or clock error., Possible for out-of-town fares, but rare enough to check., Rides under a minute are usually cancelled at the kerb or a meter started by mistake., The Manhattan-JFK flat fare was $52 throughout 2019., The total equals every other charge but not the $2.50 congestion surcharge recorded on the same row. A vendor reporting convention rather than a pricing error, but any analysis of total fares by vendor would be off by $2.50., The total should equal fare + extra + MTA tax + tip + tolls + improvement + congestion surcharges.
• rows INTEGER
• share REAL
• vendor1_rows INTEGER
• vendor2_rows INTEGER
• vendor1_trips INTEGER
• vendor2_trips INTEGER

The most common values each cleaning rule removes.

• key TEXT
• rank INTEGER
• value TEXT
• rows INTEGER

Every 2021 cleaning rule with its reason, rows removed in sequence, rows failing it on its own and rows failing only it.

• step INTEGER
• key TEXT
• stage TEXT · Round 1, Round 2, Round 3
• label TEXT
• rule TEXT
• reason TEXT
• applied_to TEXT · dropna, raw, round1, zscore
• applied_rows INTEGER
• removed_in_sequence INTEGER
• fails_alone INTEGER
• fails_only_this INTEGER

Per month and vendor: trips whose total leaves out the recorded congestion surcharge.

• month INTEGER
• vendor INTEGER
• rows INTEGER
• trips INTEGER

Per pickup borough and day: trips, the duration index, permitted events and collisions in that borough.

• date TEXT
• borough TEXT · Bronx, Brooklyn, EWR, Manhattan, Queens, Staten Island
• trips INTEGER
• indexed_trips INTEGER
• mean_min REAL
• median_min REAL
• mean_log_ratio REAL
• sd_log_ratio REAL
• precipitation REAL
• tavg REAL
• events INTEGER
• collisions INTEGER

Per day: trips, mean minutes and the composition-adjusted duration index (mean log of minutes / route-hour median), with weather, events and collisions.

• date TEXT
• trips INTEGER
• indexed_trips INTEGER
• mean_min REAL
• median_min REAL
• mean_log_ratio REAL
• sd_log_ratio REAL
• precipitation REAL
• snow REAL
• snow_depth REAL
• tavg REAL
• events INTEGER
• collisions INTEGER

Permitted events starting each day per borough (NYC Open Data bkfu-528j, current version).

• borough TEXT · Bronx, Brooklyn, Manhattan, Queens, Staten Island
• date TEXT
• number_of_event INTEGER

Per test day and model: trips and sums of errors, squared errors, absolute errors, y and y squared. The website bootstraps whole days from these.

• split TEXT · random, temporal
• model TEXT · coef_2021, en_2021_spec, en_light, mean, route_hour_median
• date TEXT
• trips INTEGER
• sum_err REAL
• sum_sq_err REAL
• sum_abs_err REAL
• sum_y REAL
• sum_y2 REAL

The models compared on the temporal (Nov-Dec) and in-period (Jan-Oct fold 0) hold-outs.

• model TEXT · coef_2021, en_2021_spec, en_light, mean, route_hour_median
• label TEXT · 2021 coefficients as published (trained on a random 90% of all 2019), 2021 specification refit on Jan-Oct (regParam 0.3), Jan-Oct mean (no features), Route x hour median (Jan-Oct lookup), Same features, lighter penalty (regParam 0.01), Jan-Oct

All 579 regression coefficients in VectorAssembler order: 2021 fold 1 next to the 2026 refit.

• feature_index INTEGER
• block TEXT
• level INTEGER
• label TEXT
• original_2021 REAL
• refit_2026 REAL

R^2 and RMSE per fold: the 2021 notebook, the 2021 coefficients scored on the revived folds, and the 2026 refit.

• fold INTEGER
• test_rows INTEGER
• notebook_r2 REAL
• notebook_rmse REAL
• original_on_revived_r2 REAL
• original_on_revived_rmse REAL
• refit_r2 REAL
• refit_rmse REAL
• refit_nonzero INTEGER

Mean 10-fold R^2, RMSE and number of non-zero coefficients for several regParam values at the notebook's elasticNetParam = 0.8.

• reg_param REAL
• elastic_net_param REAL
• cv_r2 REAL
• cv_rmse REAL
• nonzero REAL

Spark StringIndexer order of pickup and drop-off zone names (most trips first), rebuilt from the revived data.

• side TEXT · dropoff, pickup
• idx INTEGER
• zone TEXT
• trips INTEGER

Unpenalised OLS counterpart of the 2021 model on all 2019 model rows: estimates with classical, HC3 and day-clustered standard errors and 95% intervals. One reference level per block is omitted.

• feature_index INTEGER
• block TEXT
• level INTEGER
• label TEXT
• trips INTEGER
• estimate REAL
• se_classical REAL
• se_hc3 REAL
• se_cluster_day REAL
• ci_low_hc3 REAL
• ci_high_hc3 REAL
• ci_low_cluster REAL
• ci_high_cluster REAL

Mean and 10th/50th/90th percentile residual per 1-minute fitted bin (bins with at least 200 trips).

• model TEXT · coef_2021, ols
• fitted_lo REAL
• fitted_hi REAL
• trips INTEGER
• mean_resid REAL
• p10 REAL
• p50 REAL
• p90 REAL

Trips, mean residual, residual SD and MAE per pickup borough, pickup hour and borough x hour.

• model TEXT · coef_2021, ols
• group_type TEXT · borough, borough_hour, hour
• group_value TEXT
• trips INTEGER
• mean_resid REAL
• sd_resid REAL
• mae REAL

Trips per 1-minute fitted bin and 2-minute residual bin, for the 2021 coefficients and the OLS fit.

• model TEXT · coef_2021, ols
• fitted_lo REAL
• resid_lo REAL
• trips INTEGER

Residual quantiles against the normal quantiles with the same mean and standard deviation.

• model TEXT · coef_2021, ols
• p REAL
• sample_q REAL
• normal_q REAL

Hourly trips and median minutes for zone pairs with at least 1,000 trips in 2019.

• pu_id INTEGER
• do_id INTEGER
• hour INTEGER
• trips INTEGER
• median_min REAL

Every pickup zone to drop-off zone pair seen in 2019 with trips, median and mean minutes, distance, fare and speed.

• pu_id INTEGER
• do_id INTEGER
• trips INTEGER
• median_min REAL
• mean_min REAL
• mean_miles REAL
• mean_fare REAL
• mean_mph REAL

Daily Central Park weather after the notebook's cleaning: TAVG = (TMAX + TMIN) / 2, gaps filled with 0, WT04 dropped.

• date TEXT
• precipitation REAL
• snow REAL
• snow_depth REAL
• tavg REAL
• tmax INTEGER
• tmin INTEGER
• wt01 REAL
• wt02 REAL
• wt03 REAL
• wt06 REAL
• wt08 REAL

Mean and median trip minutes per ISO weekday, pickup hour and vendor (0 = both), as in the 2021 vendor line charts.

• vendor INTEGER
• isodow INTEGER
• hour INTEGER
• trips INTEGER
• mean_min REAL
• median_min REAL

Trips and trip-duration statistics per zone, ISO weekday (1 = Monday, 0 = all days) and hour (24 = all day), for pickups (pickup time) and drop-offs (drop-off time).

• side TEXT · dropoff, pickup
• location_id INTEGER
• isodow INTEGER
• hour INTEGER
• trips INTEGER
• median_min REAL
• mean_min REAL

Yearly trips per zone and vendor, the data behind the 2021 vendor choropleths.

• side TEXT · dropoff, pickup
• location_id INTEGER
• vendor INTEGER
• trips INTEGER

One row per TLC taxi zone: borough, service zone, the zone name the 2021 model used, map centroid and yearly pickups/drop-offs.

• location_id INTEGER
• borough TEXT · Bronx, Brooklyn, EWR, Manhattan, Queens, Staten Island, Unknown
• zone TEXT
• service_zone TEXT · Airports, Boro Zone, EWR, N/A, Yellow Zone
• model_zone_name TEXT
• has_polygon INTEGER
• centroid_lon REAL
• centroid_lat REAL
• pickups INTEGER
• dropoffs INTEGER