2.P2 Practice: NYC taxi ride analysis

2.P2.Q1 NYC taxi ride analysis

Taxi trip data helps analysts understand travel patterns, passenger behavior, trip distances, and tipping behavior. In this exercise, you will clean, enrich, format, query, summarize, and visualize records of New York City taxi rides.

Download the NYC taxi starter workbook. The workbook contains 2 worksheets:

  • Raw: individual taxi trip records.
  • Taxi zones: lookup data that maps taxi location IDs to boroughs and zones.

The Raw worksheet contains pickup and drop-off times and locations, passenger counts, trip distances in miles, fares, and credit-card tips. Save your work regularly while completing the exercise.

The fields used in this exercise are:

  • pickup_datetime: date and time when the taxi meter was engaged.
  • dropoff_datetime: date and time when the taxi meter was disengaged.
  • pickup_location_id: taxi zone where the passenger was picked up.
  • dropoff_location_id: taxi zone where the passenger was dropped off.
  • passenger_count: number of passengers in the vehicle.
  • trip_distance: trip distance in miles.
  • fare_amount: time-and-distance fare calculated by the meter.
  • tip_amount: credit-card tips, excluding cash tips.

Figure 1 shows the boroughs and taxi zones used by the lookup worksheet.

Figure 1: New York City boroughs and taxi zones.

Table 1 shows the first 10 records and every field in Raw.

Table 1: First 10 records in Raw.
vendor_id pickup_datetime dropoff_datetime passenger_count trip_distance RatecodeID store_and_fwd_flag pickup_location_id dropoff_location_id payment_type fare_amount extra mta_tax tip_amount tolls_amount improvement_surcharge total_amount
2 2018-06-21 05:52 2018-06-21 06:00 1 1.58 1 N 148 231 1 7.50 0.50 0.50 1.76 0.00 0.30 10.56
2 2018-06-21 06:17 2018-06-21 06:21 1 0.53 1 N 140 262 1 4.50 1.00 0.50 1.00 0.00 0.30 7.30
2 2018-06-21 06:26 2018-06-21 06:53 1 1.69 1 N 230 141 1 16.00 0.00 0.50 3.36 0.00 0.30 20.16
2 2018-06-21 06:28 2018-06-22 05:57 1 1.64 1 N 68 230 1 17.50 0.00 0.50 3.66 0.00 0.30 21.96
2 2018-06-21 06:48 2018-06-21 07:03 1 1.66 1 N 236 161 1 11.00 0.00 0.50 2.36 0.00 0.30 14.16
2 2018-06-21 07:38 2018-06-21 07:52 2 2.30 1 N 237 234 1 11.50 0.00 0.50 2.46 0.00 0.30 14.76
2 2018-06-21 08:05 2018-06-21 08:11 6 1.02 1 N 79 137 2 6.50 1.00 0.50 0.00 0.00 0.30 8.30
2 2018-06-21 08:26 2018-06-22 07:51 5 1.72 1 N 230 90 1 16.00 0.50 0.50 3.46 0.00 0.30 20.76
2 2018-06-21 09:04 2018-06-21 09:07 3 11.27 1 N 138 163 1 45.50 0.00 0.50 9.00 5.76 0.30 61.06
2 2018-06-21 11:41 2018-06-21 11:48 1 1.46 1 N 43 43 1 7.50 0.50 0.50 1.76 0.00 0.30 10.56

Table 2 shows the first 10 lookup records in Taxi zones.

Table 2: First 10 records in Taxi zones.
location_id borough zone
1 EWR Newark Airport
2 Queens Jamaica Bay
3 Bronx Allerton/Pelham Gardens
4 Manhattan Alphabet City
5 Staten Island Arden Heights
6 Staten Island Arrochar/Fort Wadsworth
7 Queens Astoria
8 Queens Astoria Park
9 Queens Auburndale
10 Queens Baisley Park

Cleaning the data

Complete these tasks in a worksheet named Processed.

  1. Copy the data and headers from these Raw columns: pickup_datetime, dropoff_datetime, pickup_location_id, dropoff_location_id, passenger_count, trip_distance, fare_amount, and tip_amount.
  2. Convert the copied data into an Excel Table.
  3. Delete every row where trip_distance, fare_amount, or passenger_count is less than or equal to 0.

Augmenting the data

Continue in Processed.

  1. Convert trip_distance from miles to kilometers using \(1\text{ km}=0.621371\text{ mile}\).
  2. Rename trip_distance to trip_distance_km.
  3. Add ride_length_class and classify each ride as:
    • Short when trip_distance_km < 1;
    • Medium when 1 <= trip_distance_km < 5;
    • Long when trip_distance_km >= 5.
  4. Use XLOOKUP or VLOOKUP with Taxi zones to find the borough for each pickup_location_id. Store the result in a new pickup_borough column.

Formatting the data

Continue in Processed.

  1. Order the columns from left to right as follows: pickup_borough, pickup_location_id, pickup_datetime, dropoff_location_id, dropoff_datetime, passenger_count, trip_distance_km, ride_length_class, fare_amount, and tip_amount.
  2. Adjust the widths so every heading is visible.
  3. Freeze the first 2 columns and the top row.
  4. Sort in ascending order by pickup_borough, then pickup_location_id, then pickup_datetime.
  5. Apply conditional formatting to highlight tip_amount values greater than 10 dollars.

Querying the data

Create a worksheet named Queries and use formulas for all 3 answers.

  1. In B2, calculate the average fare_amount.
  2. In B3, calculate the number of rides where passenger_count is greater than 3 and tip_amount is greater than 10 dollars.
  3. In B4, find the most frequent dropoff_location_id among rides departing from Manhattan.

Analyzing the data

  1. Create a worksheet named Histogram. Use the Analysis ToolPak to produce exactly 10 numeric bin rows for trip_distance_km in columns A:B, with headings in row 1 and the 10 bin limits and frequencies in rows 2–11. Set the final bin limit equal to the maximum valid trip_distance_km, so all valid rides are counted and no additional More row is needed.
  2. Create a PivotTable from all data in Processed and place it in a new worksheet named Pivot.
  3. For each pickup_borough, show the total number of rides, the average fare_amount, and the average tip_amount.
  4. Sort the boroughs in descending order by average tip_amount.

Visualizing the data

  1. Create a worksheet named Charts.
  2. Using the PivotTable results, add a vertical column chart showing average tip_amount for each pickup_borough.
  3. Use Departure borough as the x-axis title, Average tip amount (dollars) as the y-axis title, and Tipping behavior of NYC taxi users as the chart title.

Submission

Save the completed workbook as an .xlsx file. Submit exactly 1 workbook in the Canvas assignment. CodeGrade checks the required worksheets, the formulas in Queries!B2:B4, stored results, formatting, PivotTable-style summary, histogram output, and embedded chart.