LGA_Reported_Offences_Rates.csv https://www.data.qld.gov.au/dataset/lga_reported_offences_rates/resource/39ca02ad-e4e4-4c4b-af17-d6696895864a?truncate=30&inner_span=True
Spreadsheet of reported offence rates per 100,000 persons (population) by local government area and crime type. Updated Monthly.
Local government area boundaries https://qldspatial.information.qld.gov.au/catalogue/custom/detail.page?fid={3F3DBD69-647B-4833-B0A5-CC43D5E70699}
setwd("C:/Users/Jason/Desktop/Grad Cert Data Science/3. Data Visualisation/Assessment 3/A3app")
library(tidyverse)## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --## v ggplot2 3.3.5 v purrr 0.3.4
## v tibble 3.1.1 v dplyr 1.0.5
## v tidyr 1.1.3 v stringr 1.4.0
## v readr 1.4.0 v forcats 0.5.1## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()library(maptools) #readShapeSpatial()## Loading required package: sp## Checking rgeos availability: TRUElibrary(broom) # tidy()
library(rgeos) # simplify()## rgeos version: 0.5-5, (SVN revision 640)
## GEOS runtime version: 3.8.0-CAPI-1.13.1
## Linking to sp version: 1.4-5
## Polygon checking: TRUElibrary(plotly)##
## Attaching package: 'plotly'## The following object is masked from 'package:ggplot2':
##
## last_plot## The following object is masked from 'package:stats':
##
## filter## The following object is masked from 'package:graphics':
##
## layoutdataset<-read.csv("LGAReportedOffencesRates.csv")#Filter to Greater Brisbane
dataset<-dataset%>%
filter(LGA.Name %in% c("Moreton Bay Regional Council", "Brisbane City Council", "Ipswich City Council", "Gold Coast City Council", "Lockyer Valley Regional Council", "Logan City Council", "Noosa Shire Council", "Redland City Council","Scenic Rim Regional Council", "Somerset Regional Council", "Sunshine Coast Regional Council" ))
dataset<-mutate(dataset,
LGA.Name=ifelse(dataset$LGA.Name=="Moreton Bay Regional Council", "Moreton Bay Regional",
ifelse(dataset$LGA.Name=="Brisbane City Council", "Brisbane City",
ifelse(dataset$LGA.Name=="Ipswich City Council", "Ipswich City",
ifelse(dataset$LGA.Name=="Gold Coast City Council", "Gold Coast City",
ifelse(dataset$LGA.Name=="Lockyer Valley Regional Council", "Lockyer Valley Regional",
ifelse(dataset$LGA.Name=="Logan City Council", "Logan City",
ifelse(dataset$LGA.Name=="Noosa Shire Council", "Noosa Shire",
ifelse(dataset$LGA.Name=="Redland City Council", "Redland City",
ifelse(dataset$LGA.Name=="Scenic Rim Regional Council", "Scenic Rim Regional",
ifelse(dataset$LGA.Name=="Somerset Regional Council", "Somerset Regional",
ifelse(dataset$LGA.Name=="Sunshine Coast Regional Council", "Sunshine Coast Regional",""))))))))))))
#change and filter date
dataset$Date<-dataset$Month.Year
dataset$Date<-as.Date(dataset$Date)
#dataset$Date<-format(dataset$Date, "%b-%Y")
dataset<-dataset%>%separate(Month.Year, sep="-", into = c("Year", "Month", "Day"))
dataset<-dataset%>% filter(Date >= ('2018-01-01') & Date <= ('2019-12-01'))
#convert categorical data
dataset$LGA.Name<-as.factor(dataset$LGA.Name)
# dataset$Month.Year<-as.factor(dataset$Month.Year)
# dataset$Month.Year<-format(as.Date(dataset$Month.Year), "%y-%m-%d")
# dataset$Month.Year2<-dataset$Month.Year
# dataset<-dataset %>% separate (Month.Year2, sep='/', into = c("Year", "MonthName", "Date"))
dataset$MonthName<-dataset$Month
dataset$MonthName<-as.factor(dataset$MonthName)
dataset$Year<-as.numeric(dataset$Year)
dataset$Month<-as.numeric(dataset$Month)
#Rename month
dataset<-mutate(dataset, MonthName=ifelse(dataset$MonthName=="01", "Jan", ifelse(dataset$MonthName=="01", "Jan", ifelse(dataset$MonthName=="02", "Feb", ifelse(dataset$MonthName=="03", "Mar", ifelse(dataset$MonthName=="04", "Apr", ifelse(dataset$MonthName=="05", "May", ifelse(dataset$MonthName=="06", "Jun", ifelse(dataset$MonthName=="07", "Jul", ifelse(dataset$MonthName=="08", "Aug", ifelse(dataset$MonthName=="09", "Sep", ifelse(dataset$MonthName=="10", "Oct", ifelse(dataset$MonthName=="11", "Nov", ifelse(dataset$MonthName=="12", "Dec",""))))))))))))))
#level Month
dataset$MonthName<-factor(dataset$MonthName,
levels=c('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'),
ordered=TRUE)
#dataset$Month.Year3<-dataset$Month.Year
# dataset<-dataset %>% separate (Month.Year3, sep='/', into = c("Month", "Year"))
# dataset$Month<-as.integer(dataset$Month)
# dataset$Year<-as.integer(dataset$Year)
dataset$Rank<-dataset$Rank/10000
dataset<-dataset%>%rename( 'LGA_Name'='LGA.Name',
'Prostitution'='Prostitution.Offences',
'Liquor_excl_Drunkenness'='Liquor..excl..Drunkenness.',
'Gaming_Racing_Betting'='Gaming.Racing...Betting.Offences',
'Breach_DVO'='Breach.Domestic.Violence.Protection.Order',
'Trespassing_and_Vagrancy'='Trespassing.and.Vagrancy',
'Weapons_Act'='Weapons.Act.Offences',
'Stock_Related'='Stock.Related.Offences',
'Homicide_Murder'='Homicide..Murder.',
'Other_Homicide'='Other.Homicide',
'Assault'='Assault',
'Sexual_Offences'='Sexual.Offences',
'Robbery'='Robbery',
'Other_Against_Person'='Other.Offences.Against.the.Person',
'Unlawful_Entry'='Unlawful.Entry',
'Arson'='Arson',
'Unlawful_Use_Of_Motor_Vehicle'='Unlawful.Use.of.Motor.Vehicle',
'Fraud'='Fraud',
'Handling_Stolen_Goods'='Handling.Stolen.Goods',
)
# summary(dataset)
# sapply(dataset, class)
# head(dataset)#offences against the person
person<-dataset%>%select(LGA_Name, Date, Month, MonthName, Year, Homicide_Murder, Other_Homicide, Assault, Sexual_Offences, Robbery, Other_Against_Person, Rank)
person$Type<-"Person"
view(person)
#offences against property
dataset$Other_Theft<-dataset$Stealing.from.Dwellings+dataset$Shop.Stealing+dataset$Vehicles..steal.from.enter.with.intent.
property<-dataset%>%select(LGA_Name, Date, Month, MonthName, Year, Unlawful_Entry, Arson, Unlawful_Use_Of_Motor_Vehicle, Other_Theft, Fraud, Handling_Stolen_Goods, Rank)
property$Type<-"Property"
view(property)
#other offences
dataset$Drug_Offences<-dataset$Trafficking.Drugs+dataset$Possess.Drugs+dataset$Produce.Drugs+dataset$Sell.Supply.Drugs
dataset$Good_Order_Offences<-dataset$Disobey.Move.on.Direction+dataset$Resist.Incite.Hinder.Obstruct.Police+dataset$Fare.Evasion+dataset$Public.Nuisance
dataset$Traffic_Offences<-dataset$Dangerous.Operation.of.a.Vehicle+dataset$Drink.Driving+dataset$Disqualified.Driving+dataset$Interfere.with.Mechanism.of.Motor.Vehicle
other<-dataset%>%select(LGA_Name, Date, Month, MonthName, Year, Drug_Offences, Prostitution, Liquor_excl_Drunkenness, Gaming_Racing_Betting, Breach_DVO, Trespassing_and_Vagrancy, Weapons_Act, Good_Order_Offences, Stock_Related, Traffic_Offences, Rank)
other$Type<-"Other"#create Person file
numdata<-select (person, c(6:12))
numdata$Offence<-colnames(numdata)[apply(numdata,1,which.max)]
person2<-full_join(person, numdata, by='Rank')
#view tally
# totals<-person2%>%group_by(max)%>%summarise(count=n())
# head(totals[order(-totals$count),])
#select relevant columns
person2<-person2%>%select(LGA_Name, Date, Month, MonthName, Year, Homicide_Murder.x , Other_Homicide.x, Assault.x, Sexual_Offences.x, Robbery.x, Other_Against_Person.x, Rank, Type, Offence)
#rename
person2 <- person2 %>% rename( 'Homicide_Murder'='Homicide_Murder.x',
'Other_Homicide'='Other_Homicide.x',
'Assault'='Assault.x',
'Sexual_Offences'='Sexual_Offences.x',
'Robbery'='Robbery.x',
'Other_Against_Person'='Other_Against_Person.x',
)
person2$rateperht<-pmax(person2$Homicide_Murder, person2$Other_Homicide, person2$Assault, person2$Sexual_Offences, person2$Robbery, person2$Other_Against_Person)
person2$rateperht<-round(person2$rateperht, digits = 0)
#reduce to relevant columns
person2<-person2%>%select(LGA_Name, Date, Month, MonthName, Year, Rank, Type, Offence, rateperht)
#write.csv(person2,"highestCrimePerson.csv", row.names=TRUE)
#create Property file
numdata2<-select (property, c(6:12))
numdata2$Offence<-colnames(numdata2)[apply(numdata2,1,which.max)]
property2<-full_join(property, numdata2, by='Rank')
#view tally
# totals<-property2%>%group_by(max)%>%summarise(count=n())
# head(totals[order(-totals$count),])
property2<-property2%>%select(LGA_Name, Date, Month, MonthName, Year, Unlawful_Entry.x, Arson.x, Unlawful_Use_Of_Motor_Vehicle.x, Other_Theft.x, Fraud.x, Handling_Stolen_Goods.x, Rank, Type, Offence)
property2 <- property2 %>% rename( 'Unlawful_Entry'='Unlawful_Entry.x',
'Arson'='Arson.x',
'Unlawful_Use_Of_Motor_Vehicle'='Unlawful_Use_Of_Motor_Vehicle.x',
'Other_Theft'='Other_Theft.x',
'Fraud'='Fraud.x',
'Handling_Stolen_Goods'='Handling_Stolen_Goods.x',
)
property2$rateperht<-pmax(property2$Unlawful_Entry, property2$Arson, property2$Unlawful_Use_Of_Motor_Vehicle, property2$Other_Theft, property2$Fraud, property2$Handling_Stolen_Goods)
property2$rateperht<-round(property2$rateperht, digits = 0)
#reduce to relevant columns
property2<-property2%>%select(LGA_Name, Date, Month, MonthName, Year, Rank, Type, Offence, rateperht)
#write.csv(property2,"highestCrimeProperty.csv", row.names=TRUE)
#create Other file
numdata3<-select (other, c(6:16))
numdata3$Offence<-colnames(numdata3)[apply(numdata3,1,which.max)]
other2<-full_join(other, numdata3, by='Rank')
#select relevant columns
other2<-other2%>%select(LGA_Name, Date, Month, MonthName, Year, Drug_Offences.x, Prostitution.x, Liquor_excl_Drunkenness.x, Gaming_Racing_Betting.x, Breach_DVO.x, Trespassing_and_Vagrancy.x, Weapons_Act.x, Good_Order_Offences.x, Stock_Related.x, Traffic_Offences.x, Rank, Type, Offence)
other2 <- other2 %>% rename( 'Drug_Offences'='Drug_Offences.x',
'Prostitution'='Prostitution.x',
'Liquor_excl_Drunkenness'='Liquor_excl_Drunkenness.x',
'Gaming_Racing_Betting'='Gaming_Racing_Betting.x',
'Breach_DVO'='Breach_DVO.x',
'Trespassing_and_Vagrancy'='Trespassing_and_Vagrancy.x',
'Weapons_Act'='Weapons_Act.x',
'Good_Order_Offences'='Good_Order_Offences.x',
'Stock_Related'='Stock_Related.x',
'Traffic_Offences'='Traffic_Offences.x',
)
other2$rateperht<-pmax(other2$Drug_Offences, other2$Prostitution, other2$Liquor_excl_Drunkenness, other2$Gaming_Racing_Betting, other2$Breach_DVO, other2$Trespassing_and_Vagrancy, other2$Weapons_Act, other2$Good_Order_Offences, other2$Stock_Related, other2$Traffic_Offences)
other2$rateperht<-round(other2$rateperht, digits = 0)
#reduce to relevant columns
other2<-other2%>%select(LGA_Name, Date, Month, MonthName, Year, Rank, Type, Offence, rateperht)
#write.csv(other2,"highestCrimeOther.csv", row.names=TRUE)
#sapply(person2, class)#Append 3 tables
appended<- rbind(property2, person2, other2)
head(appended)## LGA_Name Date Month MonthName Year Rank Type Offence
## 1 Brisbane City 2018-01-01 1 Jan 2018 0.1857 Property Other_Theft
## 2 Brisbane City 2018-02-01 2 Feb 2018 0.1858 Property Other_Theft
## 3 Brisbane City 2018-03-01 3 Mar 2018 0.1859 Property Other_Theft
## 4 Brisbane City 2018-04-01 4 Apr 2018 0.1860 Property Other_Theft
## 5 Brisbane City 2018-05-01 5 May 2018 0.1861 Property Other_Theft
## 6 Brisbane City 2018-06-01 6 Jun 2018 0.1862 Property Other_Theft
## rateperht
## 1 108
## 2 103
## 3 104
## 4 104
## 5 116
## 6 116qld.lga.shp <- readShapeSpatial("Local_Government_Areas.shp")## Warning: readShapeSpatial is deprecated; use rgdal::readOGR or sf::st_read## Warning: readShapePoly is deprecated; use rgdal::readOGR or sf::st_read# class(qld.lga.shp)
# names(qld.lga.shp)
# head(qld.lga.shp$LGA)
# head(dataset$LGA_Name)
#Merge file with profile data
#1. convert shp file to dataframe
lga.shp.f<-tidy(qld.lga.shp, region = "LGA")
head(lga.shp.f)## # A tibble: 6 x 7
## long lat order hole piece group id
## <dbl> <dbl> <int> <lgl> <fct> <fct> <chr>
## 1 142. -14.5 1 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 2 142. -14.5 2 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 3 142. -14.5 3 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 4 142. -14.5 4 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 5 142. -14.5 5 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 6 142. -14.5 6 FALSE 1 Aurukun Shire.1 Aurukun Shire#Ensure both dataframes have an LGA_Name variable
lga.shp.f$LGA_Name<-lga.shp.f$id
lga.shp.f<-lga.shp.f%>%
filter(LGA_Name %in% c("Moreton Bay Regional", "Brisbane City", "Ipswich City", "Gold Coast City", "Lockyer Valley Regional", "Logan City", "Noosa Shire", "Redland City","Scenic Rim Regional", "Somerset Regional", "Sunshine Coast Regional" ))
#2. Merge profiles
# memory.size(max=FALSE)
# memory.limit(size=NA)
merge.lga.profiles<-merge(lga.shp.f, appended, by="LGA_Name", all.x=TRUE)
#3. Order data frame (ensures polygons drawn correctly)
choro.data.frame<-merge.lga.profiles[order(merge.lga.profiles$order), ]qld.lga.shp.simp1<-gSimplify(qld.lga.shp, tol=101, topologyPreserve=TRUE)
qld.lga.shp.simp1<-SpatialPolygonsDataFrame(qld.lga.shp.simp1, data=qld.lga.shp@data)
names(qld.lga.shp.simp1)## [1] "ADMINTYPEN" "ADMINAREAN" "LGA_CODE" "ABBREV_NAM" "LGA"
## [6] "CA_AREA_SQ" "SHAPE_Leng" "SHAPE_Area"lga.shp.f.simp1<-tidy(qld.lga.shp.simp1, region = "LGA")## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 152.85295474 -26.892749469999998## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Nested shells at
## or near point 139.47389057000001 -16.61116423## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Nested shells at
## or near point 142.34912012000001 -10.83733217## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 149.52698674000001 -23.563478889999999## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 145.23872298000001 -15.44339057## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 145.29377321000001 -15.99516446## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 151.96879507 -26.318947219999998## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Nested shells at
## or near point 152.85295474 -26.892749469999998## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 143.54342586000001 -9.5107671499999995## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Nested shells at
## or near point 149.36942791999999 -24.609335049999999## SpP is invalid## Warning in rgeos::gUnaryUnion(spgeom = SpP, id = IDs): Invalid objects found;
## consider using set_RGEOS_CheckValidity(2L)print(object.size(lga.shp.f.simp1), units = "MB")## 0.8 Mblga.shp.f.simp1$LGA_Name<-lga.shp.f.simp1$id
merge.lga.profiles2<-merge(lga.shp.f.simp1, appended, by="LGA_Name", all.x=TRUE)
choro.data.frame<-merge.lga.profiles[order(merge.lga.profiles$order), ]
head(choro.data.frame)## LGA_Name long lat order hole piece group
## 1 Brisbane City 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1
## 2 Brisbane City 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1
## 3 Brisbane City 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1
## 4 Brisbane City 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1
## 5 Brisbane City 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1
## 6 Brisbane City 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1
## id Date Month MonthName Year Rank Type Offence
## 1 Brisbane City 2018-01-01 1 Jan 2018 0.1857 Property Other_Theft
## 2 Brisbane City 2018-02-01 2 Feb 2018 0.1858 Property Other_Theft
## 3 Brisbane City 2018-03-01 3 Mar 2018 0.1859 Property Other_Theft
## 4 Brisbane City 2018-04-01 4 Apr 2018 0.1860 Property Other_Theft
## 5 Brisbane City 2018-05-01 5 May 2018 0.1861 Property Other_Theft
## 6 Brisbane City 2018-06-01 6 Jun 2018 0.1862 Property Other_Theft
## rateperht
## 1 108
## 2 103
## 3 104
## 4 104
## 5 116
## 6 116choro.data.frame<-choro.data.frame%>%select(LGA_Name, group, long, lat, Date, Month, MonthName, Year, Offence, rateperht, Type)
#Shorten LGA Names
choro.data.frame<-mutate(choro.data.frame,
LGA_Name=ifelse(choro.data.frame$LGA_Name=="Moreton Bay Regional", "Moreton Bay",
ifelse(choro.data.frame$LGA_Name=="Brisbane City", "Brisbane",
ifelse(choro.data.frame$LGA_Name=="Ipswich City", "Ipswich",
ifelse(choro.data.frame$LGA_Name=="Gold Coast City", "Gold Coast",
ifelse(choro.data.frame$LGA_Name=="Lockyer Valley Regional", "Lockyer Valley",
ifelse(choro.data.frame$LGA_Name=="Logan City", "Logan",
ifelse(choro.data.frame$LGA_Name=="Noosa Shire", "Noosa",
ifelse(choro.data.frame$LGA_Name=="Redland City", "Redland",
ifelse(choro.data.frame$LGA_Name=="Scenic Rim Regional", "Scenic Rim",
ifelse(choro.data.frame$LGA_Name=="Somerset Regional", "Somerset",
ifelse(choro.data.frame$LGA_Name=="Sunshine Coast Regional", "Sunshine Coast",""))))))))))))
head(choro.data.frame)## LGA_Name group long lat Date Month MonthName Year
## 1 Brisbane Brisbane City.1 153.0372 -27.66022 2018-01-01 1 Jan 2018
## 2 Brisbane Brisbane City.1 153.0372 -27.66022 2018-02-01 2 Feb 2018
## 3 Brisbane Brisbane City.1 153.0372 -27.66022 2018-03-01 3 Mar 2018
## 4 Brisbane Brisbane City.1 153.0372 -27.66022 2018-04-01 4 Apr 2018
## 5 Brisbane Brisbane City.1 153.0372 -27.66022 2018-05-01 5 May 2018
## 6 Brisbane Brisbane City.1 153.0372 -27.66022 2018-06-01 6 Jun 2018
## Offence rateperht Type
## 1 Other_Theft 108 Property
## 2 Other_Theft 103 Property
## 3 Other_Theft 104 Property
## 4 Other_Theft 104 Property
## 5 Other_Theft 116 Property
## 6 Other_Theft 116 Property#saveRDS(choro.data.frame, "data.rds")#make ggplot interactive with plotly
filterdata<-choro.data.frame%>% filter(Date ==('2019-10-01'))
p1 <- ggplot(data = filterdata,
aes(x = long, y = lat, group = group,
text = paste(LGA_Name,
"<br>",
rateperht, " per 100,000"),
fill = Offence
))
p1<- p1 + geom_polygon(color = "black", size = 0.25) +
coord_map() +
labs(
title = "Highest Reported Offences by Greater Brisbane LGA",
) +
scale_fill_manual(
breaks= c("Good_Order_Offences", "Drug_Offences", "Traffic_Offences", "Breach_DVO",
"Assault", "Sexual_Offences", "Other_Against_Person", "Other_Theft", "Fraud",
"Unlawful_Entry"),
values= c("#ffffb3", "#8dd3c7", "#fb8072", "#bebada", "#80b1d3", "#fdb462", "#b3de69",
"#fccde5", "#d9d9d9", "#fed9a6"))
ggplotly(p1)#Second Plot data
data2<- dataset%>%select(LGA_Name, Date, Month, MonthName, Year,Good_Order_Offences, Drug_Offences, Traffic_Offences, Breach_DVO, Assault, Sexual_Offences, Other_Against_Person, Other_Theft, Fraud,
Unlawful_Entry)
data2$Good_Order_Offences<-round(data2$Good_Order_Offences, digits=0)
data2$Drug_Offences<-round(data2$Drug_Offences, digits=0)
data2$Traffic_Offences<-round(data2$Traffic_Offences, digits=0)
data2$Breach_DVO<-round(data2$Breach_DVO, digits=0)
data2$Assault<-round(data2$Assault, digits=0)
data2$Sexual_Offences<-round(data2$Sexual_Offences, digits=0)
data2$Other_Against_Person<-round(data2$Other_Against_Person, digits=0)
data2$Other_Theft<-round(data2$Other_Theft, digits=0)
data2$Fraud<-round(data2$Fraud, digits=0)
data2$Unlawful_Entry<-round(data2$Unlawful_Entry, digits=0)
head(data2)## LGA_Name Date Month MonthName Year Good_Order_Offences
## 1 Brisbane City 2018-01-01 1 Jan 2018 76
## 2 Brisbane City 2018-02-01 2 Feb 2018 67
## 3 Brisbane City 2018-03-01 3 Mar 2018 84
## 4 Brisbane City 2018-04-01 4 Apr 2018 59
## 5 Brisbane City 2018-05-01 5 May 2018 75
## 6 Brisbane City 2018-06-01 6 Jun 2018 75
## Drug_Offences Traffic_Offences Breach_DVO Assault Sexual_Offences
## 1 60 48 23 27 8
## 2 42 36 22 26 7
## 3 55 52 21 30 8
## 4 47 33 21 22 7
## 5 53 47 19 26 6
## 6 60 44 18 28 7
## Other_Against_Person Other_Theft Fraud Unlawful_Entry
## 1 5 108 52 71
## 2 6 103 87 63
## 3 6 104 42 65
## 4 4 104 37 58
## 5 6 116 49 63
## 6 5 116 76 59data2<-mutate(data2,
LGA_Name=ifelse(data2$LGA_Name=="Moreton Bay Regional", "Moreton Bay",
ifelse(data2$LGA_Name=="Brisbane City", "Brisbane",
ifelse(data2$LGA_Name=="Ipswich City", "Ipswich",
ifelse(data2$LGA_Name=="Gold Coast City", "Gold Coast",
ifelse(data2$LGA_Name=="Lockyer Valley Regional", "Lockyer Valley",
ifelse(data2$LGA_Name=="Logan City", "Logan",
ifelse(data2$LGA_Name=="Noosa Shire", "Noosa",
ifelse(data2$LGA_Name=="Redland City", "Redland",
ifelse(data2$LGA_Name=="Scenic Rim Regional", "Scenic Rim",
ifelse(data2$LGA_Name=="Somerset Regional", "Somerset",
ifelse(data2$LGA_Name=="Sunshine Coast Regional", "Sunshine Coast",""))))))))))))
head(data2)## LGA_Name Date Month MonthName Year Good_Order_Offences Drug_Offences
## 1 Brisbane 2018-01-01 1 Jan 2018 76 60
## 2 Brisbane 2018-02-01 2 Feb 2018 67 42
## 3 Brisbane 2018-03-01 3 Mar 2018 84 55
## 4 Brisbane 2018-04-01 4 Apr 2018 59 47
## 5 Brisbane 2018-05-01 5 May 2018 75 53
## 6 Brisbane 2018-06-01 6 Jun 2018 75 60
## Traffic_Offences Breach_DVO Assault Sexual_Offences Other_Against_Person
## 1 48 23 27 8 5
## 2 36 22 26 7 6
## 3 52 21 30 8 6
## 4 33 21 22 7 4
## 5 47 19 26 6 6
## 6 44 18 28 7 5
## Other_Theft Fraud Unlawful_Entry
## 1 108 52 71
## 2 103 87 63
## 3 104 42 65
## 4 104 37 58
## 5 116 49 63
## 6 116 76 59data_long<-gather(data2, Offense, Rate, Good_Order_Offences:Unlawful_Entry, factor_key=TRUE)
head(data_long)## LGA_Name Date Month MonthName Year Offense Rate
## 1 Brisbane 2018-01-01 1 Jan 2018 Good_Order_Offences 76
## 2 Brisbane 2018-02-01 2 Feb 2018 Good_Order_Offences 67
## 3 Brisbane 2018-03-01 3 Mar 2018 Good_Order_Offences 84
## 4 Brisbane 2018-04-01 4 Apr 2018 Good_Order_Offences 59
## 5 Brisbane 2018-05-01 5 May 2018 Good_Order_Offences 75
## 6 Brisbane 2018-06-01 6 Jun 2018 Good_Order_Offences 75#saveRDS(data_long, "data3.rds")hist<-data2%>%filter(Date ==('2019-03-01'))
hist$LGA_Name<-hist$LGA_Name%>% factor(levels=hist$LGA_Name[order(-hist$Traffic_Offences)])
p1 <- ggplot(data = hist, aes(x=LGA_Name, y=Traffic_Offences, fill=LGA_Name))
p1<- p1+ geom_histogram(stat="identity", colour = "black", size=0.2)+
theme(axis.text.x=element_text(angle=45,hjust=1))+
labs(title = "Highest Reported Offences by Greater Brisbane LGA") +
scale_fill_manual(breaks= c("Sunshine Coast", "Moreton Bay", "Brisbane", "Gold Coast",
"Noosa", "Redland", "Ipswich", "Lockyer Valley", "Logan",
"Scenic Rim", "Somerset"),
values= c("dodgerblue", "dodgerblue", "dodgerblue", "dodgerblue", "dodgerblue", "dodgerblue", "#b3de69", "#b3de69", "#b3de69", "#b3de69", "#b3de69"))## Warning: Ignoring unknown parameters: binwidth, bins, padggplotly(p1)#Heat map data
qld.lga.shp <- readShapeSpatial("Local_Government_Areas.shp")## Warning: readShapeSpatial is deprecated; use rgdal::readOGR or sf::st_read## Warning: readShapePoly is deprecated; use rgdal::readOGR or sf::st_read#Merge file with profile data
#1. convert shp file to dataframe
lga.shp.f<-tidy(qld.lga.shp, region = "LGA")
head(lga.shp.f)## # A tibble: 6 x 7
## long lat order hole piece group id
## <dbl> <dbl> <int> <lgl> <fct> <fct> <chr>
## 1 142. -14.5 1 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 2 142. -14.5 2 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 3 142. -14.5 3 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 4 142. -14.5 4 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 5 142. -14.5 5 FALSE 1 Aurukun Shire.1 Aurukun Shire
## 6 142. -14.5 6 FALSE 1 Aurukun Shire.1 Aurukun Shire#Ensure both dataframes have an LGA_Name variable
lga.shp.f$LGA_Name<-lga.shp.f$id
lga.shp.f<-lga.shp.f%>%
filter(LGA_Name %in% c("Moreton Bay Regional", "Brisbane City", "Ipswich City", "Gold Coast City", "Lockyer Valley Regional", "Logan City", "Noosa Shire", "Redland City","Scenic Rim Regional", "Somerset Regional", "Sunshine Coast Regional" ))
lga.shp.f<-mutate(lga.shp.f,
LGA_Name=ifelse(lga.shp.f$LGA_Name=="Moreton Bay Regional", "Moreton Bay",
ifelse(lga.shp.f$LGA_Name=="Brisbane City", "Brisbane",
ifelse(lga.shp.f$LGA_Name=="Ipswich City", "Ipswich",
ifelse(lga.shp.f$LGA_Name=="Gold Coast City", "Gold Coast",
ifelse(lga.shp.f$LGA_Name=="Lockyer Valley Regional", "Lockyer Valley",
ifelse(lga.shp.f$LGA_Name=="Logan City", "Logan",
ifelse(lga.shp.f$LGA_Name=="Noosa Shire", "Noosa",
ifelse(lga.shp.f$LGA_Name=="Redland City", "Redland",
ifelse(lga.shp.f$LGA_Name=="Scenic Rim Regional", "Scenic Rim",
ifelse(lga.shp.f$LGA_Name=="Somerset Regional", "Somerset",
ifelse(lga.shp.f$LGA_Name=="Sunshine Coast Regional", "Sunshine Coast",""))))))))))))
#2. Merge profiles
merge.lga.profiles<-merge(lga.shp.f, data2, by="LGA_Name", all.x=TRUE)
head(merge.lga.profiles)## LGA_Name long lat order hole piece group id
## 1 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 2 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 3 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 4 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 5 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 6 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## Date Month MonthName Year Good_Order_Offences Drug_Offences
## 1 2018-01-01 1 Jan 2018 76 60
## 2 2018-02-01 2 Feb 2018 67 42
## 3 2018-03-01 3 Mar 2018 84 55
## 4 2018-04-01 4 Apr 2018 59 47
## 5 2018-05-01 5 May 2018 75 53
## 6 2018-06-01 6 Jun 2018 75 60
## Traffic_Offences Breach_DVO Assault Sexual_Offences Other_Against_Person
## 1 48 23 27 8 5
## 2 36 22 26 7 6
## 3 52 21 30 8 6
## 4 33 21 22 7 4
## 5 47 19 26 6 6
## 6 44 18 28 7 5
## Other_Theft Fraud Unlawful_Entry
## 1 108 52 71
## 2 103 87 63
## 3 104 42 65
## 4 104 37 58
## 5 116 49 63
## 6 116 76 59#3. Order data frame (ensures polygons drawn correctly)
choro.data.frame<-merge.lga.profiles[order(merge.lga.profiles$order), ]#Simplify Heat Map data
qld.lga.shp.simp1<-gSimplify(qld.lga.shp, tol=101, topologyPreserve=TRUE)
qld.lga.shp.simp1<-SpatialPolygonsDataFrame(qld.lga.shp.simp1, data=qld.lga.shp@data)
names(qld.lga.shp.simp1)## [1] "ADMINTYPEN" "ADMINAREAN" "LGA_CODE" "ABBREV_NAM" "LGA"
## [6] "CA_AREA_SQ" "SHAPE_Leng" "SHAPE_Area"lga.shp.f.simp1<-tidy(qld.lga.shp.simp1, region = "LGA")## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 152.85295474 -26.892749469999998## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Nested shells at
## or near point 139.47389057000001 -16.61116423## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Nested shells at
## or near point 142.34912012000001 -10.83733217## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 149.52698674000001 -23.563478889999999## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 145.23872298000001 -15.44339057## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 145.29377321000001 -15.99516446## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 151.96879507 -26.318947219999998## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Hole lies outside
## shell at or near point 143.54342586000001 -9.5107671499999995## Warning in RGEOSUnaryPredFunc(spgeom, byid, "rgeos_isvalid"): Nested shells at
## or near point 149.36942791999999 -24.609335049999999## SpP is invalid## Warning in rgeos::gUnaryUnion(spgeom = SpP, id = IDs): Invalid objects found;
## consider using set_RGEOS_CheckValidity(2L)print(object.size(lga.shp.f.simp1), units = "MB")## 0.8 Mblga.shp.f.simp1$LGA_Name<-lga.shp.f.simp1$id
merge.lga.profiles2<-merge(lga.shp.f.simp1, data2, by="LGA_Name", all.x=TRUE)
choro.data.frame<-merge.lga.profiles[order(merge.lga.profiles$order), ]
head(choro.data.frame)## LGA_Name long lat order hole piece group id
## 1 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 2 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 3 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 4 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 5 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## 6 Brisbane 153.0372 -27.66022 13586 FALSE 1 Brisbane City.1 Brisbane City
## Date Month MonthName Year Good_Order_Offences Drug_Offences
## 1 2018-01-01 1 Jan 2018 76 60
## 2 2018-02-01 2 Feb 2018 67 42
## 3 2018-03-01 3 Mar 2018 84 55
## 4 2018-04-01 4 Apr 2018 59 47
## 5 2018-05-01 5 May 2018 75 53
## 6 2018-06-01 6 Jun 2018 75 60
## Traffic_Offences Breach_DVO Assault Sexual_Offences Other_Against_Person
## 1 48 23 27 8 5
## 2 36 22 26 7 6
## 3 52 21 30 8 6
## 4 33 21 22 7 4
## 5 47 19 26 6 6
## 6 44 18 28 7 5
## Other_Theft Fraud Unlawful_Entry
## 1 108 52 71
## 2 103 87 63
## 3 104 42 65
## 4 104 37 58
## 5 116 49 63
## 6 116 76 59choro.data.frame<-choro.data.frame%>%select(LGA_Name, group, long, lat, Date, Month, MonthName, Year, Good_Order_Offences, Drug_Offences, Traffic_Offences, Breach_DVO, Assault, Sexual_Offences, Other_Against_Person, Other_Theft, Fraud, Unlawful_Entry)
#Shorten LGA Names
# choro.data.frame<-mutate(choro.data.frame,
# LGA_Name=ifelse(choro.data.frame$LGA_Name=="Moreton Bay Regional", "Moreton Bay",
# ifelse(choro.data.frame$LGA_Name=="Brisbane City", "Brisbane",
# ifelse(choro.data.frame$LGA_Name=="Ipswich City", "Ipswich",
# ifelse(choro.data.frame$LGA_Name=="Gold Coast City", "Gold Coast",
# ifelse(choro.data.frame$LGA_Name=="Lockyer Valley Regional", "Lockyer Valley",
# ifelse(choro.data.frame$LGA_Name=="Logan City", "Logan",
# ifelse(choro.data.frame$LGA_Name=="Noosa Shire", "Noosa",
# ifelse(choro.data.frame$LGA_Name=="Redland City", "Redland",
# ifelse(choro.data.frame$LGA_Name=="Scenic Rim Regional", "Scenic Rim",
# ifelse(choro.data.frame$LGA_Name=="Somerset Regional", "Somerset",
# ifelse(choro.data.frame$LGA_Name=="Sunshine Coast Regional", "Sunshine Coast",""))))))))))))
head(choro.data.frame)## LGA_Name group long lat Date Month MonthName Year
## 1 Brisbane Brisbane City.1 153.0372 -27.66022 2018-01-01 1 Jan 2018
## 2 Brisbane Brisbane City.1 153.0372 -27.66022 2018-02-01 2 Feb 2018
## 3 Brisbane Brisbane City.1 153.0372 -27.66022 2018-03-01 3 Mar 2018
## 4 Brisbane Brisbane City.1 153.0372 -27.66022 2018-04-01 4 Apr 2018
## 5 Brisbane Brisbane City.1 153.0372 -27.66022 2018-05-01 5 May 2018
## 6 Brisbane Brisbane City.1 153.0372 -27.66022 2018-06-01 6 Jun 2018
## Good_Order_Offences Drug_Offences Traffic_Offences Breach_DVO Assault
## 1 76 60 48 23 27
## 2 67 42 36 22 26
## 3 84 55 52 21 30
## 4 59 47 33 21 22
## 5 75 53 47 19 26
## 6 75 60 44 18 28
## Sexual_Offences Other_Against_Person Other_Theft Fraud Unlawful_Entry
## 1 8 5 108 52 71
## 2 7 6 103 87 63
## 3 8 6 104 42 65
## 4 7 4 104 37 58
## 5 6 6 116 49 63
## 6 7 5 116 76 59#make long table
data_long<-gather(choro.data.frame, Offense, Rate, Good_Order_Offences:Unlawful_Entry, factor_key=TRUE)
head(data_long)## LGA_Name group long lat Date Month MonthName Year
## 1 Brisbane Brisbane City.1 153.0372 -27.66022 2018-01-01 1 Jan 2018
## 2 Brisbane Brisbane City.1 153.0372 -27.66022 2018-02-01 2 Feb 2018
## 3 Brisbane Brisbane City.1 153.0372 -27.66022 2018-03-01 3 Mar 2018
## 4 Brisbane Brisbane City.1 153.0372 -27.66022 2018-04-01 4 Apr 2018
## 5 Brisbane Brisbane City.1 153.0372 -27.66022 2018-05-01 5 May 2018
## 6 Brisbane Brisbane City.1 153.0372 -27.66022 2018-06-01 6 Jun 2018
## Offense Rate
## 1 Good_Order_Offences 76
## 2 Good_Order_Offences 67
## 3 Good_Order_Offences 84
## 4 Good_Order_Offences 59
## 5 Good_Order_Offences 75
## 6 Good_Order_Offences 75#write.csv(choro.data.frame,"data.csv", row.names=TRUE)
#saveRDS(choro.data.frame, "data2.rds")
#saveRDS(data_long, "data2.rds")#Create Heat Map
filterdata<-choro.data.frame%>% filter(Date ==('2018-10-01'))
p1 <- ggplot(data = filterdata,
aes(x = long, y = lat, group = group,
text = paste(LGA_Name),
fill=Good_Order_Offences
))
p1<- p1 + geom_polygon(color = "black", size = 0.25) +
coord_map() +
labs(
title = "Highest Reported Offences by Greater Brisbane LGA",
) +
scale_fill_gradient(
low = "white",
high = "red",
space = "Lab",
na.value = "grey50",
guide = "colourbar",
aesthetics = "fill"
)
ggplotly(p1)#Line plot
line<-data_long%>%filter(LGA_Name==c("Moreton Bay", "Brisbane", "Ipswich"), Offense==("Sexual_Offences"))
#hist$LGA_Name<-hist$LGA_Name%>% factor(levels=hist$LGA_Name[order(-hist$Good_Order_Offences)])
p1<-ggplot(line, aes(Date, Rate, group = LGA_Name, colour = LGA_Name))
p1<-p1+geom_line(size=1)+geom_point()+
theme(axis.text.x=element_text(angle=45,hjust=1))
# labs(title = "Highest Reported Offences by Greater Brisbane LGA") +
# scale_fill_manual(breaks= c(" Moreton Bay", "Brisbane", "Ipswich"),
# values= c("#8dd3c7", "#ffffb3", "#bebada"))
ggplotly(p1)