Data Analytics and Business Intelligence

Posted on by

Data Analytics and Business Intelligence
Group10 Assignment:
Public Transport Passenger Journeys by Service Type

Student Name Student ID
Unisha Dhonju u3231083
Gonglue Gao u3235059
StevenTaseski u3158814
Justin Colman u3160400
Bryant Zhang u3082001

Data Analytics and Business Intelligence 1
Group10 Assignment: 1
Public Transport Passenger Journeys by Service Type 1
1. Executive Summary (1 page/5 marks) 3
2. Introduction (1 page/5 marks) 4
3. Data (2 pages/5 marks) 5
4. Analyses (2 pages/5 marks) 6
5. Results (2 pages/5 marks) 7
6. Discussion (2 pages/5 marks) 8
7. Conclusion (1 page/5 marks) 9
8. Citation and references 10
DABI Assignment Notes 11
DABI Assignment Notes 13
Data Description 15
Weather Set 15
Route Type Set 15
Full Route Set 15

1. Executive Summary (1 page/5 marks)
● Summarize the data, methods and results of your report.

2. Introduction (1 page/5 marks)

In the report, we will be conducting research on the relationship between the Canberra public transport Route type and weather effect. Canberra has an area of 814 square kilometres and a population of more than 360,000 people, and not only everyone has private transportation. In order to solve people’s daily commuting, the Australian government provides a variety of public transportation, the most important of which are Light rail, Rapid Routes and School routes. However, people using these public transport always change their minds due to a number of factors, the most important factors are the weather and Government transport planning. It is obvious that the rainfall in Canberra will seriously affect the number of each kind of public transport journey on that day. Also, the government’s transport planning policy affects people’s daily traffic. For example, the Government changed Canberra’s bus network on April 2019, as the 2019 timetable overhaul attracted strong criticism from Canberra residents after many services were cut, but still has been a 7.1 per cent increase in journeys on public transport after the new network was changed in 2019. (Brown, 2020) Therefore, the case study found that the government’s changes to the Route can change the number of journeys. On the other hand, the research found that rainfall had affected people’s choice of public transportation type based on the analysis of the Public transport journeys data set and the weather data set. The impact of weather on the number of passengers on the line will provide information to the relevant departments, and changing the line frequency will obtain the most efficient occupancy rate and alleviate the serious problem of a lack of driver resources.

3. Data (2 pages/5 marks)
● Describe your data visually and numerically.
● Provide details of the pre-processing undertaken in order to meet the conditions of your chosen modelling techniques.

4. Analyses (2 pages/5 marks)
● Describe the modelling procedure with enough detail to allow someone else to repeat the modelling. At least two (for UG level students) and three (for PG level students) different modelling techniques (e.g., decision trees, ensemble decision trees) should be applied to the problem.

5. Results (2 pages/5 marks)
● Describe the performance of your models. Use appropriate charts and visualization to convey the results to your audience.
As we are using two sets of data to illustrate the results,

6. Discussion (2 pages/5 marks)
● Interpret your results.
● Which model performed the best? Why?
● What do the results mean in light of your aims?
● Which were the most important variables in your model?

7. Conclusion (1 page/5 marks)
(Gary)

8. Citation and references

 
DABI Assignment Notes
https://drive.google.com/drive/folders/1t5cHnWU8a28dS6yuM9OlM1PbOpF9XEHw?usp=sharing

· Public Transport Journeys Data Set
o https://www.data.act.gov.au/Transport/Daily-Public-Transport-Passenger-Journeys-by-Servi/nkxy-abdj
· Weather Data Set
o http://www.bom.gov.au/climate/data/index.shtml?bookmark=200
· Rain Intensity:
o None = 0
o Light = >0 <2.5 o Moderate >2.5 <7.6 o Heaver > 7.6
· Added usage column to see how many days each service was used
· Removed Peak Services and Other columns
· Added new date columns (Year, Day, and Month (Day/Month both in word format, not number)) to make it easier for sorting
· Weather data reduced to only date and rainfall amount, with intensity added
· Both sets combined
· Extra sets made for weekdays only and weekends only, and for each separate service (With weekdays/weekends sets as well)
· In each individual service type’s separate data set, the not-used days and non-representative days were removed. This was only in the School Route and Light Rail sets. The Light Rail didn’t run on Christmas 2019 and the School Route doesn’t run during school holidays and weekends (~6 weekend days had values <10, so were cancelled out as they were not representative of the rest of the data set) · Monthly Average data sets were created for the full set · Data Preprocessing o Aggregation § Combined Day/Month/Year in weather data set to make it one attribute § Full data set combined from individual days into months o Feature Subset Selection § Weather set had many attributes that were not needed for the data mining (including weather station, period of which rainfall was measured and the data quality) § After using the “Usage” attributes for initial stats, it was removed for the final data set, since the days in which the route was not used, were removed from the set o Feature Creation § A Rain Intensity attribute was created using the formula: if(value == 0, "None", if((value > 0).and(value < 2.5), "Light", if((value >= 2.5).and(value < 7.6), "Moderate", "Heavy"))) § Created “Usage” attribute for each route to help get an understanding of how many days each route was used during the time period of the data § Created average monthly data for a brand new data set o Data/Attribute Transformation § Rain Intensity § Created Usage attribute then removed when finished using it before the data mining § Monthly Average Set § Remove un-needed attributes from original sets before combining weather and public transport data sets DABI Assignment Notes · Public Transport Journeys Data Set o https://www.data.act.gov.au/Transport/Daily-Public-Transport-Passenger-Journeys-by-Servi/nkxy-abdj o Removed Peak Services and Other columns, as they aren’t very useful o Added usage column to see how many days each service was used – Found both used every day of 2020/2021 o Added new date columns (Year, Month, Date(Number), DayOfWeek(Mon, Tue, etc), DayType(Weekday/Weekend)) to make it easier for sorting o · Weather Data Set o http://www.bom.gov.au/climate/data/index.shtml?bookmark=200 o Rain Today: § Days under 5 don’t count as that amount of rain probably would change people mind about taking public transport (reasonable Assumption) o Rain Tomorrow § cross(row.index + 1, "", "")[0].cells['Rain Today'].value o Average Over Under § if(value > cells[‘Local Route Avg’].value, “Over”, “Under”)
o Weather data reduced to only date and rainfall amount, with intensity added
· Both sets combined
· Monthly Average data set were created for the Weekday/Weekend sets. These sets have the average for each month (Jan, Feb, Mar, etc)
· The monthly averages were added into the full set and 2 new attributes were added (Local Route O/U, Light Rail O/U), which outputs either under or over, depending if that day is under or over that months average
· Everyday set split into separate sets, weekdays only and weekends only
· Data Preprocessing
o Aggregation
§ Combined Day/Month/Year in weather data set to make it one attribute for the identifier variable
o Feature Subset Selection
§ Weather set had many attributes that were not needed for the data mining (including weather station, period of which rainfall was measured and the data quality)
§ After using the “Usage” attributes for initial stats, it was removed for the data set, since both services were used everyday
o Feature Creation
§ Rain Today:
· If(value > 5, “Yes”, “No”)
· Days under 5 don’t count as that amount of rain probably would change people mind about taking public transport (reasonable Assumption)
§ Rain Tomorrow:
· cross(row.index + 1, “”, “”)[0].cells[‘Rain Today’].value
· Used for future prediction, to predict if there would is rain tomorrow would affect the passenger numbers.
§ Rain Risk:
· Same as Rain Tomorrow, but for the Rainfall attribute
§ Average Over Under
· if(value > cells[‘Local Route Avg’].value, “Over”, “Under”)
· Will be used to possible find a correlation/prediction between rainfall and if the passenger numbers will be under or over the monthly average
§ Created “Usage” attribute for each route to help get an understanding of how many days each route was used during the time period of the data
§ Created average monthly data variables
§ Created DayOfWeek (Mon, Tue,etc) and DayType (Weekend/Weekday), for sorting and visuals in the data descriptions
o Data/Attribute Transformation
§ Rain Intensity
§ Created Usage attribute then removed when finished using it before the data mining
§ Monthly Average Set
§ Remove un-needed attributes from original sets before combining weather and public transport data sets
Data Description
Weather Set
· Observations/Days of Data = 731 (Weekdays = 523) (Weekends = 208)
· Min = 0 (Same for Weekday/Weekend)
· Max = 60.4 (Weekdays = 60.4) (Weekends = 39.6)
· Average (Full Set) = 1.99 (Weekdays = 1.86) (Weekends = 2.32)
· Rain Today:
o Yes = 94
o No = 637
· Full Set
o https://i.imgur.com/w2wxNFL.png – Rain Today/Tomorrow + Local/Rail O/U
Route Type Set
Full Route Set
· Observations/Days of Data = 731 (Weekdays = 523) (Weekends = 208)
· https://i.imgur.com/4LqSUTR.png – Both Routes By day
· https://i.imgur.com/4lo6jFw.png – Both Routes By Month
· Local Route Set
o Min = 79 (Weekdays = ) (Weekends = )
o Max = 21070 (Weekdays = ) (Weekends = )
o Average = 8214 (Weekdays = ) (Weekends = )
o Over = 433, Under = 298
· Light Rail Set
o Min = 375 (Weekdays = 451) (Weekends = 375)
o Max = 15154 (Weekdays = 14789) (Weekends = 15154)
o Average = 5449 (Weekdays = 7325) (Weekends = 3627)
o Over = 397, Under = 334
Imgur image: