rm(list = ls())
# Use dplyr for renaming and dropping columns (rename, select)
library('dplyr')
# Use reshape2 for converting to long (melt) and wide (dcast) data
library('reshape2')
setwd('C:/Users/......../RCodes')
crimesDF <- read.csv('Crime.csv', stringsAsFactors = FALSE)
head(crimesDF)
Offence State X2000 X2001 X2002 X2003 X2004 X2005 X2006
1 Homicide NSW 262 313 256 233 149 153 176
2 Assault NSW 68,714 75,461 80,028 79,890 78,305 81,165 82,773
3 Sexual assault NSW 5,975 6,268 6,477 6,799 7,072 6,826 6,669
4 Kidnapping NSW 385 470 435 421 467 462 450
5 Robbery NSW 13,328 15,234 11,707 10,849 8,885 9,565 9,240
6 Blackmail NSW 62 66 79 78 94 85 81
... ... ...
... ... ...
tail(crimesDF)
str(crimesDF)
summary(crimesDF)
colnames(crimesDF)
"Offence" "State" "X2000" "X2001" "X2002" "X2003" "X2004"
"X2005" "X2006" "X2007" "X2008" "X2009" "X2010" "X2011"
"X2012" "X2013" "X2014" "X2015" "X2016" "X2017" "X2018"
"X2019" "X2020" "X2021" "X2022"
longCrimesDF <- melt(data = crimesDF,
id.vars = c('Offence', 'State'),
variable.name = 'Year',
value.name = 'CountOfOffences')
head(longCrimesDF)
Offence State Year CountOfOffences
1 Homicide NSW X2000 262
2 Assault NSW X2000 68,714
3 Sexual assault NSW X2000 5,975
4 Kidnapping NSW X2000 385
5 Robbery NSW X2000 13,328
6 Blackmail NSW X2000 62
tail(longCrimesDF)
Offence State Year CountOfOffences
1651 Kidnapping ACT X2022 3
1652 Robbery ACT X2022 187
1653 Blackmail ACT X2022 13
1654 Unlawful entry into property ACT X2022 1,872
1655 Motor vehicle theft ACT X2022 1,181
1656 Other theft ACT X2022 7,140
# Rename columns
longCrimesDF <- longCrimesDF %>% dplyr::rename("Count" = "CountOfOffences")
colnames(longCrimesDF)
"Offence" "State" "Year" "Count"
# Remove 'X' from years
longCrimesDF$Year <- sub('.', '', longCrimesDF$Year)
head(longCrimesDF)
Offence State Year Count
1 Homicide NSW 2000 262
2 Assault NSW 2000 68,714
3 Sexual assault NSW 2000 5,975
4 Kidnapping NSW 2000 385
5 Robbery NSW 2000 13,328
6 Blackmail NSW 2000 62
# Covert data to wide format (dcast)
wideCrimesDF <- dcast(data = longCrimesDF,
formula = Offence + State ~Year,
value.var = 'Count')
head(wideCrimesDF)
Offence State 2000 2001 2002 2003 2004 2005 2006
1 Assault ACT 1,744 1,955 1,989 2,001 1,768 1,771 1,968
2 Assault NSW 68,714 75,461 80,028 79,890 78,305 81,165 82,773
3 Assault NT 2,938 2,950 3,428 3,708 3,384 3,979 4,348
4 Assault QLD
5 Assault SA 15,424 16,291 16,542 16,003 14,881 15,407 16,176
6 Assault TAS 2,720 3,485 3,647 3,767 3,974 4,550 4,120
... ... ...
... ... ...
colnames(wideCrimesDF)
"Offence" "State" "2000" "2001" "2002" "2003" "2004"
"2005" "2006" "2007" "2008" "2009" "2010" "2011"
"2012" "2013" "2014" "2015" "2016" "2017" "2018"
"2019" "2020" "2021" "2022"
