Great Data Enthusiasts, happy to present my latest project as an internship with Quantum Analytics NG.
Visualization Tool: Power BI
Data Source: Quantum Analytics
Business Objective: Below are the main
purposes of this Analysis:
What’s the average number of trips we can expect this week, What’s the average fare per trip we expect to collect, What’s the average distance traveled per trip, How do we expect trip volume to change, relative to last week, Which days of the week and times of the day will be busiest, and What will likely be the most popular pick-up and drop-off locations?
Step 1: Dataset Understanding
This dataset contains six tables in CSV format, along with a geospatial map in Topojson and Shapefile formats. The four Taxi Trips tables contain a total of 28 million Green Taxi trips in New York City from 2017 to 2020. Each record represents one trip, with fields containing details about the pick-up/drop-off times and locations, distances, fares, passengers, and more.
There is also another table -The 454 Calendar table which contains a fiscal calendar (2017–2020) used by the Taxi & Limousine Commission, with fields containing the date and fiscal year, quarter, month, and week. The Taxi Zones table contains information about 265 zone locations in New York City, including the location id, borough, and service zone.
The Taxi Zones Map files contain a map of New York City with divisions for the 265 locations that can be used to create custom map visuals in Power BI.
Step 2: Data Transformation and Cleaning
Merging and Appending, the first thing was to load the four Taxi Trips tables into the power query editor and append all as they all have an equal number of columns to increase rows. Secondly, I Merged the Taxi Zones Map files with the Appended tables using location as the primary key.
Conditional Columns
Trip Type: I further used the conditional column to transform the trip_type column as 1= Street-hail; 2= Dispatch, and named it Trip Type1.
Payment Type: I also used a conditional column to transform the payment type column as 1= Credit card; 2= Cash; 3= No charge; 4= Dispute; 5= Unknown; 6= Voided trip, as given in the instructions, and renamed it as Payment Type 1.
Clean:
Tolls Amount and improvement_surcharge Column has a series of ‘null’ values which were replaced with ‘0’. I also replaced -0.3 with 0.3 according to the instructions.
Total Amount, Trip Amount, and Fare Amount Column
Expanded these columns to see the number of negative values then transformed them to positive values — according to instruction in the pdf.
Transforming Negative to Positive Values
First, highlight the column you want to transform, go to the ‘Transform’ pane, then click on the drop-down arrow on “Scientific”, then click on “Absolute Value”.
I want to thank Mr. Jonathan and the Quantum Analytics group for the opportunity.
#Data #taxi #trio #analysis #nyc #teamwork #quantum #communication #powerbi #dashboard #visualizasion