INFS5710 Information Technology Infrastructure for Business Analytics
Assessment 3
Project Statement
(Due by 4 PM on Tuesday, 12 November 2024 via Moodle)
• This project accounts for 25% of the total marks for this course.
• The deliverable is a PowerPoint file with speaker notes.
The New York City taxi system is one of the most iconic and extensive in the world. Yellow taxis are traditional cabs that can pick up passengers anywhere in the five boroughs of New York City. The New York City Taxi and Limousine Commission (TLC) regulates the taxi industry, ensuring safety, reliability, and fair pricing. Yellow taxis operate under a medallion system, where each taxi must have a medallion (aspecial license) to operate. These medallions are limited in number and can be expensive. The modern yellow taxis have GPS, credit card payment systems, and digital meters. Many also have screens that provide information and entertainment to passengers. Efforts have been made to increase the number of wheelchair-accessible taxis, making the system more inclusive.
The TLC provides detailed trip data, which includes information such as pick-up and drop-off locations, trip distances, fares, and payment methods. This data is valuable for analysing trends, optimizing routes, and understanding passenger behaviour.
Ref:Exploratory Data Analysis Of New York City Yellow Taxi Data (nycdatascience.com)
NYC Yellow Taxi Data
The manager of the Yellow Taxi company has hired you to analyse the data. The New York City Taxi and Limousine Commission (TLC Trip Record Data—TLC (nyc.gov)) generates many data. However, for this assignment, we only use a one-month sample dataset from theNYC Taxi Fare Dataset (kaggle.com) website. Note: Do not download the data file directly from the website.
Note: You are not allowed to contact collaborators on the website or anyone with experience with the taxi data for assistance. You and your fellow group members must do all the work. However, you can research how taxi operators, taxi data's background, and soon. The taxi model is different from car sharing, such as Uber.
The websiteNYC Taxi Fare Dataset (kaggle.com)has some basic statistics that you can view. One of the contributors used Python to do the data analysis. It was found that more passengers paid with cards than with cash. All the analyses performed on the cards and cash were done correctly. Nevertheless, the explanation and recommendations are not exciting. It is seen that more passengers use cards than cash to pay. To investigate further, I would have explored from which locations or taxi zones passengers use cash or cards to pay. Alternatively, I would have explored the time of the day or the day of the week. I believe you can do a better job than this contributor.
One recommendation by this contributor is to “encoinkurage customers to pay with credit cards to capitaliseon the potential for generating more revenue for taxi drivers.” If you look carefully at the tip amount for cash, it is zero because it is not recorded. Conversely, every transaction, including the tips using credit cards, is recorded. It is common that, in cash business, not all income, such as tips, is reported to the Internal Revenue Service or IRS (government tax department). Thus, the “dishonest” taxi drivers have no incentive to encourage customers to pay with credit cards.
You can find other analyses done by contributors related to the taxi data, such as Unraveling NYC Yellow Taxi Patterns: An In-depth Exploratory Data Analysis | by Haonan Zhong | Medium. Copying the same analysis will not get you a mark as the results have been shown. However, you can get some ideas from the analysis and then expand from there.
The original dataset downloaded from theNYC Taxi Fare Dataset website (kaggle.com)is reasonably clean but has 65 thousand missing data. After removing the missing data, the dataset now encompasses records of approximately 6.34 million taxi trips. The number of missing record is roughly 1% of the total number of rows, so the impact is minimal.
The metadata can be found on the websiteTLC Trip Record Data—TLC (nyc.gov). There, you can also see the taxi zone maps and lookup tables. Please note that more columns have been added recently, so the last column is not in your downloaded dataset.
Other factors that might impact the data, which you have to do some research and find the data yourself, include:
(a) Weather data: The weather might play an important role in this project. For example, you might want to explore if weather (e.g., temperature) impacts taxi rides. If, after analysing the data, you find weather has no impact on the number of passengers using the taxi when you think it should be based on the literature or previous historical data (say, for example), you need to explain why that is not the case.
(b) Holiday data: Holidays are another factor that could influence taxi rides. You can easily search for the dates of federal holidays in the US.
(c) Location (taxi zones) data: Another factor that could influence taxi rides is location.
Tasks
In this project, you are expected to manage and clean the collected data; some columns may contain missing data, different formatting, and incomplete information. On the surface, the data is relatively clean. 65,000 missing rows have been excluded. You have roughly 6.34 million taxi trips.
These insights will help to understand taxi hire in New York City.
(1) Entity Relationship Model
Before proceeding with data analysis, you must download the data (see next (2) for instructions). Based on the initial downloaded table, you create a normalised entity-relationship model/diagram (ERM/ERD). It is imperative to model all the necessary tables for your analysis, encompassing primary and foreign keys and attributes. If you want to use data from the downloaded table, such as weather, you need to include it in the model. You have to justify the creation of your model.
Due to the dataset size, you do not have to implement individual tables. However, if you feel more comfortable with individual tables, you are most welcome to do so. You will not be penalised for taking this step.
(2) Import Data
A few steps exist to import the data from the downloaded file into the SAS Enterprise Guide. Ensure that the data are created in a SAS dataset file; this is your responsibility and part of the assessment.
(a) Download file
Firstly, you can download the file TaxiFareDatasetMod.csv from this link:Group Assignment Yellow Taxi Data, under the Group Assignment channel in Teams. The file is about 500Mb, and you might want download when you are at the university.
It is highly recommended that you put the downloaded file in the same folder as your Orion/OrionDB datasets.
(b) File Import
The option to import the downloaded file into the SAS Enterprise Guide is File > Import Data on the Menu. You will have to find the folder containing your downloaded file,e.g., in your Orion/OrionDB folder.
Note: the screenshots might look different from yours.
(c) Define your library
The SAS dataset file will be created in the folder defined as your library. You must find the folder where you want to save your dataset. In this case, we want to save it in the Orion/OrionDB folder.
Click the <Next> button to continue.
(d) Select Data in SAS Enterprise Guide - Select Data Source
Select all the default options and click the <Next> button to continue; it will check the rows.
It will import just less than 6 million rows.
(e) Select Data in SAS Enterprise Guide - Define Field Attributes
SAS will define the field attributes based on what it found. Leave everything as default and click the <Next> button to continue.
(f) Select Data in SAS Enterprise Guide - Advanced Options
Leave everything as default and click the <Finish> button to import the data.
Uploading the data as a SAS dataset file will take a while. So, please be patient!
Once the data is uploaded, you can see a screen with the imported data.
You can also find the SAS dataset generated in the Orion/OrionDB folder.
You can make a copy of this SAS dataset. I made a copy of the dataset and named it taxidata.sas7dat, as an example, leaving the original dataset as a backup.
(3) Data Analysis
The manager of the NYC Yellow Taxi asked you to analyse the data and “let the data speak for itself.” You understand that the company wants to grow the market further and attract more customers. Before they do it, they want to have some insights from the data:
“Using Yellow Taxis in New York City offers a reliable and efficient transportation option
backed by comprehensive data analysis. Studies show that Yellow Taxis provides consistent
service across all boroughs, with high availability during peak hours, ensuring you can
always find a ride when you need it most.The data reveals that Yellow Taxis are particularly effective for short to mid-distance trips, with 80% of rides being under 5 miles, making them ideal for quick commutes or errands.”
The following are some suggestions that you may consider:
• Location analysis: What locations are most popular (for start or destination)? At what times?
• Trip analysis: For example, what routes are most popular? What is the average distance of trips? Are most trips within a few streets or across cities?
• Time analysis for demand: What time of day has a higher demand?
• Holiday analysis: How do holidays affect the demand, if any?
• Weather analysis: How does weather influence demand, if any?
• Customer Behaviour analysis: How do the customers like to travel?
SAS Enterprise Guide (EG)
You are required to use only the SAS Enterprise Guide (EG) for this project. To begin with the ETL
(extract, transform. and loading) process, you need to prepare your data in proper tables that will go
into SAS. That is, you need to create tables in the SAS environment. By following the previous step, you have done it.
Whenever you want to conduct an analysis (e.g., trip analysis), you must write a query to select relevant attributes for a specific analysis. Sometimes, you might want to retrieve only the needed data and put it into another SAS dataset before you do your analysis. See the appendix for some common data
analysis functions of SAS EG.
Finally, please note you can assume that the management (or the LIC or the tutors) knows nothing beyond this project statement. Therefore, you need to use your judgment and make necessary and reasonable assumptions when doing this project. Make sure to present all assumptions made in the project. You have to make a judgement on the data and what to do with them.