##Data PreProcessing
First all required file needs to be imported using their file structure. The datasets are inspected after importing to confirm that they have been loaded correctly and that their row and column counts can be compared with the provided metadata.
library(tidyverse)## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.2.1 ✔ readr 2.2.0
## ✔ forcats 1.0.1 ✔ stringr 1.6.0
## ✔ ggplot2 4.0.3 ✔ tibble 3.3.1
## ✔ lubridate 1.9.5 ✔ tidyr 1.3.2
## ✔ purrr 1.2.2
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errorslibrary(jsonlite)##
## Attaching package: 'jsonlite'
##
## The following object is masked from 'package:purrr':
##
## flatten#csv import
lga_stop_routes <- read.csv("lga_stops_routes.csv", check.names = FALSE)
#for population dataet, after it was downloaded from the ABS website, the table 3 was edited such the above unrequired rows were deleted and the header were edited. then it was exported as csv.
population <- read.csv("population.csv", check.names = FALSE)
routes_city_freq <- read.csv("routes_city_freq.csv", check.names = FALSE)
lga_area<- read.csv("lga_area.csv", check.names = FALSE)
lga_2021_2024 <- read.csv("lga_2021_lga_2024.csv",check.names = FALSE)
#read json file
lga_area_json <- fromJSON("lga_area.geojson")##Function Creation for Inspection A function is created to inspect the number of rows and columns, missing values, blank values, and duplicate rows so we dont have to repeat the code again and again.
check_data <- function(data, name) {
cat("\n---Table name:", name, "---\n")
#row n column count so we cross check the count with the metadata file manually
cat("Rows:", nrow(data), "\n")
cat("Columns:", ncol(data), "\n")
# missing values
cat("\nMissing values:\n")
print(colSums(is.na(data)))
#blank/empty string values
cat("\nBlank/empty string values:\n")
blank_count <- sapply(data, function(x) sum(x == "", na.rm = TRUE))
print(blank_count)
# Duplicate rows
cat("\nDuplicate rows:", sum(duplicated(data)), "\n")
}Imported datasets are checked before any filtering or cleaning is done. Then, the row and column counts are compared with metadata. Also, missing values, blank values and duplicate records are identified. The structure of the datasets is also inspected where data types are important.
check_data(lga_stop_routes, "LGA Stop Routes")##
## ---Table name: LGA Stop Routes ---
## Rows: 41188
## Columns: 7
##
## Missing values:
## lga_code21 lga_name21 stop_id stop_name vehicle route_number
## 0 0 0 0 0 0
## route_name
## 0
##
## Blank/empty string values:
## lga_code21 lga_name21 stop_id stop_name vehicle route_number
## 0 0 0 0 0 2953
## route_name
## 6
##
## Duplicate rows: 4print("---------------")## [1] "---------------"check_data(population, "Population")##
## ---Table name: Population ---
## Rows: 552
## Columns: 29
##
## Missing values:
## S/T code S/T name LGA code LGA name 0–4
## 0 0 4 0 3
## 5–9 10–14 15–19 20–24 25–29
## 3 3 3 3 3
## 30–34 35–39 40–44 45–49 50–54
## 3 3 3 3 3
## 55–59 60–64 65–69 70–74 75–79
## 3 3 3 3 3
## 80–84 85 and over Total persons
## 3 3 3 552 552
##
## 552 552 552 552
##
## Blank/empty string values:
## S/T code S/T name LGA code LGA name 0–4
## 1 4 0 3 0
## 5–9 10–14 15–19 20–24 25–29
## 0 0 0 0 0
## 30–34 35–39 40–44 45–49 50–54
## 0 0 0 0 0
## 55–59 60–64 65–69 70–74 75–79
## 0 0 0 0 0
## 80–84 85 and over Total persons
## 0 0 0 0 0
##
## 0 0 0 0
##
## Duplicate rows: 0print("---------------")## [1] "---------------"check_data(routes_city_freq, "Routes City Frequency")##
## ---Table name: Routes City Frequency ---
## Rows: 453
## Columns: 6
##
## Missing values:
## vehicle route_number route_name daily_freq weekday_freq weekend_freq
## 0 0 0 0 0 0
##
## Blank/empty string values:
## vehicle route_number route_name daily_freq weekday_freq weekend_freq
## 0 39 0 0 0 0
##
## Duplicate rows: 0print("---------------")## [1] "---------------"check_data(lga_area, "LGA Area")##
## ---Table name: LGA Area ---
## Rows: 37
## Columns: 4
##
## Missing values:
## lga_code21 lga_name21 sqkm cbd_euclidean_km
## 0 0 1 1
##
## Blank/empty string values:
## lga_code21 lga_name21 sqkm cbd_euclidean_km
## 0 1 0 0
##
## Duplicate rows: 0print("---------------")## [1] "---------------"check_data(lga_2021_2024, "LGA 2021-2024 Mapping")##
## ---Table name: LGA 2021-2024 Mapping ---
## Rows: 565
## Columns: 4
##
## Missing values:
## LGA_CODE_2021 LGA_NAME_2021 LGA_CODE_2024 LGA_NAME_2024
## 0 0 0 0
##
## Blank/empty string values:
## LGA_CODE_2021 LGA_NAME_2021 LGA_CODE_2024 LGA_NAME_2024
## 0 0 0 0
##
## Duplicate rows: 0##Checking Structure of the Datasets imported Now structure of the dataset are checked.
str(lga_stop_routes)## 'data.frame': 41188 obs. of 7 variables:
## $ lga_code21 : int 20110 20110 20110 20110 20110 20110 20110 20110 20110 20110 ...
## $ lga_name21 : chr "Alpine" "Alpine" "Alpine" "Alpine" ...
## $ stop_id : int 47869 18689 48807 45128 41496 22886 18687 18687 18686 45125 ...
## $ stop_name : chr "Freeburgh Comunity Hall/Great Alpine Rd (Freeburgh)" "Alpine Hotel/Anderson St (Bright)" "Mudgegonga Public Hall/Myrtleford-Yackandandah Rd (Mudgegonga)" "Boyds Lane/Kiewa Valley Hwy (Kergunyah South)" ...
## $ vehicle : chr "Bus" "Bus" "Bus" "Bus" ...
## $ route_number: chr "" "Wangaratta-Bright" "" "" ...
## $ route_name : chr "Bright - Omeo Via Hotham Heights" "Bright - Wangaratta Via Bright" "Albury - Myrtleford Via Wodonga" "Mt Beauty - Albury Via Baranduda, Kergunya, Tawonga South" ...str(population)## 'data.frame': 552 obs. of 29 variables:
## $ S/T code : chr "1" "1" "1" "1" ...
## $ S/T name : chr "New South Wales" "New South Wales" "New South Wales" "New South Wales" ...
## $ LGA code : int 10050 10180 10250 10300 10470 10500 10550 10600 10650 10750 ...
## $ LGA name : chr "Albury" "Armidale" "Ballina" "Balranald" ...
## $ 0–4 : int 3701 1631 2353 141 2637 9898 1629 546 403 30803 ...
## $ 5–9 : int 3921 1583 2497 120 2726 8529 1842 627 407 33794 ...
## $ 10–14 : int 3745 1810 2775 166 2953 8013 2036 871 507 33438 ...
## $ 15–19 : int 3666 2437 2874 124 3232 8607 2110 885 507 30086 ...
## $ 20–24 : int 3420 2592 2122 107 3201 15211 1333 377 281 29473 ...
## $ 25–29 : int 3931 1968 2089 131 2747 21026 1267 331 340 32006 ...
## $ 30–34 : int 4136 1955 2330 110 2893 20394 1659 503 405 33661 ...
## $ 35–39 : int 3791 1809 2744 144 2959 17013 1819 657 369 39464 ...
## $ 40–44 : int 3689 1691 3073 94 2682 13304 1847 767 348 39913 ...
## $ 45–49 : int 3330 1474 2941 122 2421 10559 1776 765 444 30660 ...
## $ 50–54 : int 3420 1601 3130 121 2845 10453 2221 939 554 25296 ...
## $ 55–59 : int 3272 1577 2971 165 2603 8981 2441 938 521 21005 ...
## $ 60–64 : int 3441 1743 3458 160 2692 8843 3279 1148 675 19370 ...
## $ 65–69 : int 3447 1620 3440 164 2559 7255 3484 1197 800 16248 ...
## $ 70–74 : int 2959 1346 3300 159 2137 6338 3020 1090 747 13087 ...
## $ 75–79 : int 2540 1210 2778 94 1793 5476 2495 793 715 10383 ...
## $ 80–84 : int 1662 860 1887 56 1182 3881 1439 448 393 5744 ...
## $ 85 and over : int 1467 745 1841 66 1097 3989 1224 466 357 4954 ...
## $ Total persons: int 59538 29652 48603 2244 45359 187770 36921 13348 8773 449385 ...
## $ : logi NA NA NA NA NA NA ...
## $ : logi NA NA NA NA NA NA ...
## $ : logi NA NA NA NA NA NA ...
## $ : logi NA NA NA NA NA NA ...
## $ : logi NA NA NA NA NA NA ...
## $ : logi NA NA NA NA NA NA ...str(routes_city_freq)## 'data.frame': 453 obs. of 6 variables:
## $ vehicle : chr "Bus" "Bus" "Bus" "Bus" ...
## $ route_number: chr "" "" "" "" ...
## $ route_name : chr "Adelaide - Melbourne Via Bendigo & Nhill" "Ballarat - Melbourne Via Melton" "City - Melbourne Airport" "Cowes or Inverloch - Melbourne Via Dandenong & Koo Wee Rup" ...
## $ daily_freq : int 1 1 2 3 13 2 3 1 9 6 ...
## $ weekday_freq: int 0 1 2 3 13 2 3 1 9 5 ...
## $ weekend_freq: int 1 0 2 3 0 2 0 1 9 6 ...str(lga_area)## 'data.frame': 37 obs. of 4 variables:
## $ lga_code21 : int 20660 20910 21110 21180 21450 21610 21890 22170 22310 22670 ...
## $ lga_name21 : chr "Banyule" "Bayside (Vic.)" "Boroondara" "Brimbank" ...
## $ sqkm : num 62.5 37.2 60.2 123.4 1282.6 ...
## $ cbd_euclidean_km: num 14 15 9 16 61 45 10 40 12 29 ...str(lga_2021_2024)## 'data.frame': 565 obs. of 4 variables:
## $ LGA_CODE_2021: chr "10050" "10180" "10250" "10300" ...
## $ LGA_NAME_2021: chr "Albury" "Armidale Regional" "Ballina" "Balranald" ...
## $ LGA_CODE_2024: chr "10050" "10180" "10250" "10300" ...
## $ LGA_NAME_2024: chr "Albury" "Armidale" "Ballina" "Balranald" ...Convert LGA code to character for matching for everydataset, as it need to be joined/mapped.
population$`LGA code` <- as.character(population$`LGA code`)
lga_stop_routes$lga_code21 <-as.character(lga_stop_routes$lga_code21)
lga_area$lga_code21<- as.character(lga_area$lga_code21)
#stop_id as character as it wont be used for other mathemathical purpose
lga_stop_routes$stop_id <-
as.character(lga_stop_routes$stop_id)##Cleaning LGA Area Data: The LGA area dataset contains data for LGAs outside Greater Melbourne as well. Records with missing LGA codes or names are removed.The five excluded LGAs are Macedon Ranges, Mitchell, Moorabool, Murrindindi and Unincorporated Vic.The dataset is then restricted to the Greater Melbourne LGAs required for the analysis.
check_data(lga_area, "LGA Area")##
## ---Table name: LGA Area ---
## Rows: 37
## Columns: 4
##
## Missing values:
## lga_code21 lga_name21 sqkm cbd_euclidean_km
## 0 0 1 1
##
## Blank/empty string values:
## lga_code21 lga_name21 sqkm cbd_euclidean_km
## 0 1 0 0
##
## Duplicate rows: 0#remove records with missing LGA code or LGA name
lga_area <- lga_area %>%
filter(
!is.na(lga_code21) & lga_code21 != "",
!is.na(lga_name21) & lga_name21 != ""
)
#keep only the 31 Greater Melbourne LGAs
greater_melbourne_area <- lga_area %>%
filter(
!lga_name21 %in% c(
"Macedon Ranges",
"Mitchell",
"Moorabool",
"Murrindindi",
"Unincorporated Vic"
)
)
#check the number of Greater Melbourne LGAs
nrow(greater_melbourne_area)## [1] 31# Check that LGA codes are unique
greater_melbourne_area %>%
count(lga_code21) %>%
filter(n > 1)## [1] lga_code21 n
## <0 rows> (or 0-length row.names)##Geojson dataset: First inspection is done for the dataset, then extract LGA attributes separately and filter the rows so only Greater Melbourne data remains. Next we retain geom values in another variable, filter it then join the geom with its lga_code and lga_name.
str(lga_area_json)## List of 4
## $ type : chr "FeatureCollection"
## $ name : chr "lga_area"
## $ crs :List of 2
## ..$ type : chr "name"
## ..$ properties:List of 1
## .. ..$ name: chr "urn:ogc:def:crs:OGC:1.3:CRS84"
## $ features:'data.frame': 36 obs. of 3 variables:
## ..$ type : chr [1:36] "Feature" "Feature" "Feature" "Feature" ...
## ..$ properties:'data.frame': 36 obs. of 4 variables:
## .. ..$ lga_code21 : chr [1:36] "20660" "20910" "21110" "21180" ...
## .. ..$ lga_name21 : chr [1:36] "Banyule" "Bayside (Vic.)" "Boroondara" "Brimbank" ...
## .. ..$ sqkm : num [1:36] 62.5 37.2 60.2 123.4 1282.6 ...
## .. ..$ cbd_euclidean_km: num [1:36] 14 15 9 16 61 45 10 40 12 29 ...
## ..$ geometry :'data.frame': 36 obs. of 2 variables:
## .. ..$ type : chr [1:36] "Polygon" "Polygon" "Polygon" "Polygon" ...
## .. ..$ coordinates:List of 36
## .. .. ..$ : num [1, 1:1863, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:1019, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:2177, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:1416, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:9402, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:7437, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:1857, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:659, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:475, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:2003, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:1569, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:4222, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:1175, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:8100, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:2112, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:4302, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:696, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:1828, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:826, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:3793, 1:2] 145 144 144 144 144 ...
## .. .. ..$ : num [1, 1:2424, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:5709, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:1266, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:4273, 1:2] 144 144 144 144 144 ...
## .. .. ..$ : num [1, 1:1607, 1:2] 145 145 145 145 145 ...
## .. .. ..$ :List of 23
## .. .. .. ..$ : num [1, 1:10098, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:7, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:10, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:12, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:12, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:16, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:10, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:12, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:7, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:18, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:16, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:14, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:23, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:24, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:7, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:9, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:10, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:10, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:10, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:25, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:10, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:25, 1:2] 145 145 145 145 145 ...
## .. .. .. ..$ : num [1, 1:16, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:1072, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:3504, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:767, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:905, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:3223, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:5147, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:4504, 1:2] 144 144 144 144 144 ...
## .. .. ..$ : num [1, 1:1655, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:7228, 1:2] 145 145 145 145 145 ...
## .. .. ..$ : num [1, 1:76, 1:2] 145 145 145 145 145 ...names(lga_area_json)## [1] "type" "name" "crs" "features"#extract LGA attributes from the GeoJSON
lga_properties <- lga_area_json$features$properties
head(lga_properties)## lga_code21 lga_name21 sqkm cbd_euclidean_km
## 1 20660 Banyule 62.54018 14
## 2 20910 Bayside (Vic.) 37.21109 15
## 3 21110 Boroondara 60.17757 9
## 4 21180 Brimbank 123.39906 16
## 5 21450 Cardinia 1282.56760 61
## 6 21610 Casey 409.42896 45#check the number of GeoJSON LGA records
nrow(lga_properties)## [1] 36#filter the rows
lga_properties_melbourne <- lga_properties %>%
filter(
lga_code21 %in% greater_melbourne_area$lga_code21
)
#check the number of Greater Melbourne rows
nrow(lga_properties_melbourne)## [1] 31# verify if any Greater Melbourne LGA is missing
greater_melbourne_area %>%
anti_join(
lga_properties_melbourne,
by = "lga_code21"
)## [1] lga_code21 lga_name21 sqkm cbd_euclidean_km
## <0 rows> (or 0-length row.names)#Now for geometry from geojson file
#extract the geom data
lga_geometry <- lga_area_json$features$geometry
#combine LGA code, name and geometry
lga_geometry_table <- data.frame(
lga_code21 = lga_properties$lga_code21,
lga_name21 = lga_properties$lga_name21,
geometry = lga_geometry
)
#keep only Greater Melbourne geometries
lga_geometry_melbourne <- lga_geometry_table %>%
filter(
lga_code21 %in% greater_melbourne_area$lga_code21
)
# Check the number of geometries
nrow(lga_geometry_melbourne)## [1] 31##Population + LGA mapping: Metadata specifically warned about the 2024/2021 LGA difference as the population dataset uses LGA codes from the 2024 version, while the other datasets uses 2021 LGA codes. First we clean the population table by removing unwanted columns and then map it with lga_2021_2024.We also check if mapping passed to see whether any records are lost or unmatched after joining tables.and finally filter the dataframe and verify the row count.
#remove unwanted empty/unnamed columns from the population table
head(population)## S/T code S/T name LGA code LGA name 0–4 5–9 10–14 15–19 20–24
## 1 1 New South Wales 10050 Albury 3701 3921 3745 3666 3420
## 2 1 New South Wales 10180 Armidale 1631 1583 1810 2437 2592
## 3 1 New South Wales 10250 Ballina 2353 2497 2775 2874 2122
## 4 1 New South Wales 10300 Balranald 141 120 166 124 107
## 5 1 New South Wales 10470 Bathurst 2637 2726 2953 3232 3201
## 6 1 New South Wales 10500 Bayside (NSW) 9898 8529 8013 8607 15211
## 25–29 30–34 35–39 40–44 45–49 50–54 55–59 60–64 65–69 70–74 75–79 80–84
## 1 3931 4136 3791 3689 3330 3420 3272 3441 3447 2959 2540 1662
## 2 1968 1955 1809 1691 1474 1601 1577 1743 1620 1346 1210 860
## 3 2089 2330 2744 3073 2941 3130 2971 3458 3440 3300 2778 1887
## 4 131 110 144 94 122 121 165 160 164 159 94 56
## 5 2747 2893 2959 2682 2421 2845 2603 2692 2559 2137 1793 1182
## 6 21026 20394 17013 13304 10559 10453 8981 8843 7255 6338 5476 3881
## 85 and over Total persons
## 1 1467 59538 NA NA NA NA NA NA
## 2 745 29652 NA NA NA NA NA NA
## 3 1841 48603 NA NA NA NA NA NA
## 4 66 2244 NA NA NA NA NA NA
## 5 1097 45359 NA NA NA NA NA NA
## 6 3989 187770 NA NA NA NA NA NApopulation <- population[
!is.na(names(population)) &
names(population) != "" &
!startsWith(names(population), ".")
]
head(population) #29 columns to 23 columns## S/T code S/T name LGA code LGA name 0–4 5–9 10–14 15–19 20–24
## 1 1 New South Wales 10050 Albury 3701 3921 3745 3666 3420
## 2 1 New South Wales 10180 Armidale 1631 1583 1810 2437 2592
## 3 1 New South Wales 10250 Ballina 2353 2497 2775 2874 2122
## 4 1 New South Wales 10300 Balranald 141 120 166 124 107
## 5 1 New South Wales 10470 Bathurst 2637 2726 2953 3232 3201
## 6 1 New South Wales 10500 Bayside (NSW) 9898 8529 8013 8607 15211
## 25–29 30–34 35–39 40–44 45–49 50–54 55–59 60–64 65–69 70–74 75–79 80–84
## 1 3931 4136 3791 3689 3330 3420 3272 3441 3447 2959 2540 1662
## 2 1968 1955 1809 1691 1474 1601 1577 1743 1620 1346 1210 860
## 3 2089 2330 2744 3073 2941 3130 2971 3458 3440 3300 2778 1887
## 4 131 110 144 94 122 121 165 160 164 159 94 56
## 5 2747 2893 2959 2682 2421 2845 2603 2692 2559 2137 1793 1182
## 6 21026 20394 17013 13304 10559 10453 8981 8843 7255 6338 5476 3881
## 85 and over Total persons
## 1 1467 59538
## 2 745 29652
## 3 1841 48603
## 4 66 2244
## 5 1097 45359
## 6 3989 187770#map the population 2024 LGA code to 2021 LGA code
population_mapped <- population %>%
left_join(
lga_2021_2024,
by = c("LGA code" = "LGA_CODE_2024")
)
#check population records that could not be mapped to a 2021 LGA code
population_mapped %>%
filter(is.na(LGA_CODE_2021)) %>%
nrow()## [1] 7#filter to keep only Greater Mel data
population_melbourne <- population_mapped %>%
filter(
LGA_CODE_2021 %in% greater_melbourne_area$lga_code21
)
#check the no of records
nrow(population_melbourne)## [1] 31Verify if all lga data are there in melbourne mapped population table.
#check for LGAs missing from the population table
greater_melbourne_area %>%
anti_join(
population_melbourne,
by = c("lga_code21" = "LGA_CODE_2021")
)## [1] lga_code21 lga_name21 sqkm cbd_euclidean_km
## <0 rows> (or 0-length row.names)#check for population LGAs that are not present in the Greater Melbourne LGA tbl
population_melbourne %>%
anti_join(
greater_melbourne_area,
by = c("LGA_CODE_2021" = "lga_code21")
)## [1] S/T code S/T name LGA code LGA name 0–4
## [6] 5–9 10–14 15–19 20–24 25–29
## [11] 30–34 35–39 40–44 45–49 50–54
## [16] 55–59 60–64 65–69 70–74 75–79
## [21] 80–84 85 and over Total persons LGA_CODE_2021 LGA_NAME_2021
## [26] LGA_NAME_2024
## <0 rows> (or 0-length row.names)#both returns 0 meaning it was success##LGA_stop_route data: This dataset contains repeated stop records as a single stop can be used by multiple routes and vehicle types. Hence, duplicate records are investigated rather than directly deleting. Blank route numbers are not deleted as these records still contain valid stop information. also empty route name rows are also not deleted for the same reason and also it has route number. For the later route-frequency,assumption is made that only records with a route number can be matched to the frequency table.
#keep only Greater Melbourne data
lga_stop_routes_melbourne <- lga_stop_routes %>%
filter(
lga_code21 %in% greater_melbourne_area$lga_code21
)
#check the filtered dataset
check_data(lga_stop_routes_melbourne,"LGA Stop Routes Melbourne")##
## ---Table name: LGA Stop Routes Melbourne ---
## Rows: 30993
## Columns: 7
##
## Missing values:
## lga_code21 lga_name21 stop_id stop_name vehicle route_number
## 0 0 0 0 0 0
## route_name
## 0
##
## Blank/empty string values:
## lga_code21 lga_name21 stop_id stop_name vehicle route_number
## 0 0 0 0 0 295
## route_name
## 6
##
## Duplicate rows: 4#check the structure and data types
str(lga_stop_routes_melbourne)## 'data.frame': 30993 obs. of 7 variables:
## $ lga_code21 : chr "20660" "20660" "20660" "20660" ...
## $ lga_name21 : chr "Banyule" "Banyule" "Banyule" "Banyule" ...
## $ stop_id : chr "10937" "35" "142" "74" ...
## $ stop_name : chr "Adam Cres/Sherbourne Rd (Montmorency)" "Invermay Gr/Waiora Rd (Rosanna)" "Pacific Dr/Ramu Pde (Heidelberg West)" "Porter Rd/Southern Rd (Heidelberg Heights)" ...
## $ vehicle : chr "Bus" "Bus" "Bus" "Bus" ...
## $ route_number: chr "902" "517" "550" "517" ...
## $ route_name : chr "Chelsea - Airport West" "St Helena - Northland SC" "Northland SC - La Trobe University" "St Helena - Northland SC" ...#check the no of Greater Melbourne LGAs
n_distinct(lga_stop_routes_melbourne$lga_code21)## [1] 31#displaying duplicate rows
lga_stop_routes_melbourne %>%
filter(duplicated(.))## lga_code21 lga_name21 stop_id
## 1 25060 Moonee Valley 19270
## 2 25060 Moonee Valley 19270
## 3 25060 Moonee Valley 19276
## 4 25060 Moonee Valley 19276
## stop_name vehicle
## 1 34-Moonee Valley Civic Centre/Pascoe Vale Rd (Moonee Ponds) Tram
## 2 34-Moonee Valley Civic Centre/Pascoe Vale Rd (Moonee Ponds) Bus
## 3 40-Nicholson St/Fletcher St (Essendon) Tram
## 4 40-Nicholson St/Fletcher St (Essendon) Bus
## route_number route_name
## 1 59 Flinders Street Station, City - Airport West
## 2 959 Broadmeadows - City
## 3 59 Flinders Street Station, City - Airport West
## 4 959 Broadmeadows - City#--The duplicacy identified 4 rows but when the data was checked for the duplicate rows, manual inspection showed that the records had different vehicle type and route no for the same stop. So, they are not considered duplicates and are retained.
#check rows with blank route name
lga_stop_routes_melbourne %>%
filter(route_name == "")## lga_code21 lga_name21 stop_id stop_name vehicle route_number
## 1 24600 Melbourne 10920 Flagstaff Station Train City Circle
## 2 24600 Melbourne 10924 Parliament Station Train City Circle
## 3 24600 Melbourne 10922 Melbourne Central Station Train City Circle
## 4 24600 Melbourne 22188 Southern Cross Station Train City Circle
## 5 24600 Melbourne 11213 Flinders Street Station Train City Circle
## 6 24600 Melbourne 11212 Flinders Street Station Train City Circle
## route_name
## 1
## 2
## 3
## 4
## 5
## 6#--The identified 6 rows with blank route_name had still valid route_numbe which provides usable route information so the rows are not deleted.
#295 rows have blank route numbers but they are not deleted as the records themselves are not invalid as they have data but we exclude them only when constructing the route list for frequency matching because a blank route number cannot be matched to a route-frequency record.##Cleaning and Linking Route Frequency Data: This dataset contains route-level service frequency information. Only routes appearing in the Greater Melbourne dataset are required. Therefore, a list of distinct non-blank route numbers is created from the data and used to filter the frequency table.
#create a list of unique non-blank route numbers found in Greater Melbourne
melbourne_routes <- lga_stop_routes_melbourne %>%
filter(route_number != "") %>%
distinct(route_number)
#check the no of unique Melbourne routes
nrow(melbourne_routes)## [1] 410head(melbourne_routes, 10)## route_number
## 1 902
## 2 517
## 3 550
## 4 381
## 5 566
## 6 343
## 7 514
## 8 250
## 9 350
## 10 549#keep only rows relevant to Melbourne routes
routes_city_freq_melbourne <- routes_city_freq %>%
filter(
route_number %in% melbourne_routes$route_number
)
#check the filtered frequency table
check_data(
routes_city_freq_melbourne,
"Routes City Frequency Melbourne"
)##
## ---Table name: Routes City Frequency Melbourne ---
## Rows: 405
## Columns: 6
##
## Missing values:
## vehicle route_number route_name daily_freq weekday_freq weekend_freq
## 0 0 0 0 0 0
##
## Blank/empty string values:
## vehicle route_number route_name daily_freq weekday_freq weekend_freq
## 0 0 0 0 0 0
##
## Duplicate rows: 0The unmatched route check identifies route no. present in the Greater Melbourne stop-route but absent in the filtered frequency table. These records are not automatically assigned a frequency of 0 as metadata states that missing route frequency values should be checked.
#Melbourne routes without a matching frequency record
unmatched_routes <- melbourne_routes %>%
anti_join(
routes_city_freq_melbourne,
by = "route_number"
)
unmatched_routes## route_number
## 1 941
## 2 959
## 3 982
## 4 981
## 5 979
## 6 978
## 7 953
## 8 967
## 9 City Circle
## 10 951#validate frequency uniqueness
#check if vehicle, route number and route name uniquely identify frequency records
routes_city_freq_melbourne %>%
count(vehicle, route_number, route_name) %>%
filter(n > 1)## [1] vehicle route_number route_name n
## <0 rows> (or 0-length row.names)#--No duplicate rws with vehicle–route-number–route-name combinations were found. So we use these variables to link route-frequency information to the stop-route records.Unique LGA-vehicle-route records is created with distinct lga code, name, vehicle, route no and name. Left join is used to join so that all route records are retained, inluding the rows with unavailable frequency data. This allows unmatched records to be identified.
t3_routes <- lga_stop_routes_melbourne %>%
filter(route_number != "") %>%
distinct(
lga_code21,
lga_name21,
vehicle,
route_number,
route_name
)
nrow(t3_routes) #check row count## [1] 942#join route frequency data to the unique route records
t3_routes_freq <- t3_routes %>%
left_join(
routes_city_freq_melbourne,
by = c(
"vehicle",
"route_number",
"route_name"
)
)
#check the number of records after joining
nrow(t3_routes_freq)## [1] 942# Check unmatched frequency records
sum(is.na(t3_routes_freq$daily_freq))## [1] 25#--There are 25 unmatched frequency records. These values are not replaced with 0 because a missing frequency record does not necessarily mean that the route has zero service.
# Display routes for which no daily frequency was matched
t3_routes_freq %>%
filter(is.na(daily_freq)) %>%
select(
lga_code21,
lga_name21,
vehicle,
route_number,
route_name
)## lga_code21 lga_name21 vehicle route_number
## 1 21180 Brimbank Bus 941
## 2 21180 Brimbank Bus 959
## 3 21610 Casey Bus 982
## 4 21610 Casey Bus 981
## 5 22670 Greater Dandenong Bus 979
## 6 22670 Greater Dandenong Bus 982
## 7 22670 Greater Dandenong Bus 978
## 8 22670 Greater Dandenong Bus 981
## 9 23270 Hume Bus 953
## 10 23270 Hume Bus 959
## 11 23430 Kingston (Vic.) Bus 978
## 12 23430 Kingston (Vic.) Bus 979
## 13 23670 Knox Bus 967
## 14 24410 Maroondah Bus 967
## 15 24600 Melbourne Bus 959
## 16 24600 Melbourne Train City Circle
## 17 24600 Melbourne Train Overland
## 18 24970 Monash Bus 967
## 19 24970 Monash Bus 979
## 20 24970 Monash Bus 978
## 21 25060 Moonee Valley Bus 959
## 22 25250 Moreland Bus 951
## 23 25250 Moreland Bus 959
## 24 26980 Whitehorse Bus 967
## 25 27450 Yarra Ranges Bus 967
## route_name
## 1 Watergardens Station - Sunshine Station
## 2 Broadmeadows - City
## 3 Cranbourne Park - Dandenong Station
## 4 Cranbourne Park - Dandenong Station
## 5 Dandenong Station - Clayton Station
## 6 Cranbourne Park - Dandenong Station
## 7 Dandenong Station - Clayton Station
## 8 Cranbourne Park - Dandenong Station
## 9 Craigieburn - Broadmeadows Station
## 10 Broadmeadows - City
## 11 Dandenong Station - Clayton Station
## 12 Dandenong Station - Clayton Station
## 13 Bayswater - Glen Waverley
## 14 Bayswater - Glen Waverley
## 15 Broadmeadows - City
## 16
## 17 Melbourne
## 18 Bayswater - Glen Waverley
## 19 Dandenong Station - Clayton Station
## 20 Dandenong Station - Clayton Station
## 21 Broadmeadows - City
## 22 Glenroy - City
## 23 Broadmeadows - City
## 24 Bayswater - Glen Waverley
## 25 Bayswater - Glen Waverley# Count unmatched records by vehicle type
t3_routes_freq %>%
filter(is.na(daily_freq)) %>%
count(vehicle)## vehicle n
## 1 Bus 23
## 2 Train 2#-The 25 unmatched rows consists of 23 bus data and 2 train data.Final validation of the tables This check is used to confirm that the cleaned datasets contain the required Greater Melbourne LGAs and that the key relationships between datasets can be established.
#No. of Greater Melbourne LGAs in the cleaned area table
nrow(greater_melbourne_area)## [1] 31#No. of Greater Melbourne LGAs in the population table
nrow(population_melbourne)## [1] 31#No. of Greater Melbourne LGAs in the stop-route table
n_distinct(lga_stop_routes_melbourne$lga_code21)## [1] 31#No. of Greater Melbourne LGAs in the GeoJSON
nrow(lga_properties_melbourne)## [1] 31#check unmatched population LGAs
greater_melbourne_area %>%
anti_join(
population_melbourne,
by = c("lga_code21" = "LGA_CODE_2021")
)## [1] lga_code21 lga_name21 sqkm cbd_euclidean_km
## <0 rows> (or 0-length row.names)#check unmatched GeoJSON LGAs
greater_melbourne_area %>%
anti_join(
lga_properties_melbourne,
by = "lga_code21"
)## [1] lga_code21 lga_name21 sqkm cbd_euclidean_km
## <0 rows> (or 0-length row.names)#check unmatched route-frequency records
melbourne_routes %>%
anti_join(
routes_city_freq_melbourne,
by = "route_number"
)## route_number
## 1 941
## 2 959
## 3 982
## 4 981
## 5 979
## 6 978
## 7 953
## 8 967
## 9 City Circle
## 10 951