More Code for FBI UCR, WASPC, OFM WA SAC data

Political piece is here. Code below:



# jpg function for charts. Finish with 'graphics.off()'. Charts have time-date stamped number.
# Require renaming with jpg extension
jpeg_create <- function() {
 systime <- as.numeric(Sys.time())
 # dev.new()
 jpeg(filename = systime,
          width = 1220, height = 824, units = "px", pointsize = 18,
          quality = 500, bg = "white", res = NA, family = "", restoreConsole = TRUE,
          type = c("windows"))
 Sys.sleep(2)
   }
setwd("C:/Crime")

library(dplyr)
## data from http://www.ofm.wa.gov/sac/dnld/ucr_94_13.xlsx
## data is 18 x 5184 consists of all Counties plus all sherrif/police/tribal LEO stats for Years 1994 - 2013.
## However most LEO jurisdictions stopped UCR either completely or partially after 2011
## some of them splitting record keeping between LEOs that covered UCR/NIBRS or mainly just NIBRS for 2012,2013 (and beyond).
## So:
cat('
arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ COUNTY,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR == 2012),sum)),desc(POPULATION))

     COUNTY POPULATION VTOTAL PTOTAL
1      KING     841815   2022  27241
2 SNOHOMISH     633588   1016  15622
3   SPOKANE     449100   1708  27407
4  THURSTON     116102    274   4403
5   WHATCOM      87921    328   1137
6    PIERCE      16398     65    894

arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ COUNTY,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR == 2013),sum)),desc(POPULATION))

     COUNTY POPULATION VTOTAL PTOTAL
1      KING     653585   1048  17703
2 SNOHOMISH     611421   1198  20305
3   SPOKANE     452805   1763  28712
4   WHATCOM      88276    157   1340
')

## xlsx is R ready. Create a csv =  ucr_94_13.csv
ucr94_13 <- read.csv("ucr_94_13.csv",stringsAsFactors = FALSE)
as.matrix(sapply(ucr94_13,class))
cat('
           [,1]       
COUNTY     "character"
INDEXYEAR  "integer"  
LOCATION   "character"
POPULATION "integer"  
TOTAL      "integer"  
RATE       "numeric"  
VTOTAL     "integer"  
VRATE      "numeric"  
MURDER     "integer"  
RAPE       "integer"  
ROBBERY    "integer"  
ASSAULT    "integer"  
PTOTAL     "integer"  
PRATE      "numeric"  
ARSON      "integer"  
BURGLARY   "integer"  
THEFT      "integer"  
MVTHEFT    "integer"
')  

# Some xtabs examples for Specific Counties and Locations.
arrange(as.data.frame(xtabs(VTOTAL ~ INDEXYEAR,data=subset(ucr94_13,COUNTY == "WHATCOM" & LOCATION !="COUNTY TOTAL"))),desc(INDEXYEAR))[c(-1,-2),]
arrange(as.data.frame(xtabs(VTOTAL ~ INDEXYEAR,data=subset(ucr94_13,COUNTY == "WHATCOM" & LOCATION !="COUNTY TOTAL"))),desc(INDEXYEAR))[c(-1,-2),]
plot(arrange(as.data.frame(xtabs(VTOTAL ~ INDEXYEAR,data=subset(ucr94_13,COUNTY == "WHATCOM" & LOCATION == "Bellingham Police Department"))),desc(INDEXYEAR)))
arrange(as.data.frame(xtabs(VTOTAL ~ LOCATION,data=subset(ucr94_13,COUNTY == "WHATCOM" & LOCATION !="COUNTY TOTAL"))),desc(Freq))


# UCR history of crime for Whatcom County
t1 <- arrange(aggregate(cbind(POPULATION,VTOTAL,PTOTAL,TOTAL) ~ LOCATION,
data=subset(ucr94_13,COUNTY == "WHATCOM" & LOCATION !="COUNTY TOTAL" & INDEXYEAR == 2011),sum),desc(TOTAL))
with(t1,barplot(TOTAL,names.arg=substr(LOCATION,1,10),cex.names=.75,las=2,main="UCR Crime Totals in Whatcom County By Location 2011"))
t2 <- arrange(aggregate(cbind(VTOTAL,PTOTAL,TOTAL) ~ LOCATION,
data=subset(ucr94_13,COUNTY == "WHATCOM" & LOCATION !="COUNTY TOTAL"),sum),desc(TOTAL))
with(t2,barplot(TOTAL,names.arg=substr(LOCATION,1,10),cex.names=.75,las=2,main="UCR Crime Totals in Whatcom County By Location 1994 - 2011",))
t3 <- arrange(aggregate(cbind(VTOTAL,PTOTAL,TOTAL) ~ INDEXYEAR,
data=subset(ucr94_13,COUNTY == "WHATCOM" & LOCATION !="COUNTY TOTAL"),sum),INDEXYEAR)
with(t3,barplot(TOTAL,names.arg=INDEXYEAR,cex.names=.75,las=2,main="UCR Crime Totals in Whatcom County By Year 1994 - 2011"))
mtext("UCR Crime for Years 1994 - 2013. Note: 2012 and 2013 may reflect UCR/NIBRS split reporting.",side=3,line=-2)
#END


# Arguments that aggregate crime as whole in WA.
arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ INDEXYEAR,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR < 2012),sum)),desc(POPULATION))
arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ COUNTY,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR < 2012),sum)),desc(POPULATION))
arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ COUNTY,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR == 2012),sum)),desc(POPULATION))
arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ COUNTY,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR == 2011),sum)),desc(POPULATION))
cat('
arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ COUNTY,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR == 2012),sum)),desc(POPULATION))
     COUNTY POPULATION VTOTAL PTOTAL
1      KING     841815   2022  27241
2 SNOHOMISH     633588   1016  15622
3   SPOKANE     449100   1708  27407
4  THURSTON     116102    274   4403
5   WHATCOM      87921    328   1137
6    PIERCE      16398     65    894
arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ COUNTY,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR == 2011),sum)),desc(POPULATION))
         COUNTY POPULATION VTOTAL PTOTAL
1          KING    1966075   6776  78116
2        PIERCE     795245   3339  31126
3     SNOHOMISH     700430   1197  19962
4       SPOKANE     472650   1644  24697
5         CLARK     427915    961  11476
6      THURSTON     254100    555   7015
7        KITSAP     253900    782   7352
8        YAKIMA     244700    808  10496
9       WHATCOM     202100    449   6398
10       BENTON     177900    415   5055
11       SKAGIT     117400    240   5210
12      COWLITZ     102785    315   3489
13        GRANT      85075    251   3770
14     FRANKLIN      80500    238   1816
15       ISLAND      78800     96   1440
')

arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ INDEXYEAR,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR < 2012),sum)),desc(POPULATION))
cat('   INDEXYEAR POPULATION VTOTAL PTOTAL
1       2011    6761195  19568 239428
2       2010    6726695  20599 247503
3       2009    6659685  21919 244368
4       2008    6495630  21199 242956
5       2007    6480754  21409 259129
6       2006    6358704  21842 284505
7       2005    6240985  21629 308321
8       2004    6109399  21093 300745
9       2003    6033987  21078 290945
10      2002    5965974  20656 285250
11      2001    5961220  21007 284827
12      2000    5786711  21482 276945
13      1999    5738985  21355 276491
14      1998    5664584  23894 299858
15      1997    5593341  24539 306777
16      1995    5329768  25364 304800
17      1996    5316176  23075 296859
18      1994    5297247  26548 288958
')

# How property and violent Crime in WA have decreased from 1994 - 2011
# Also some coplot examples
coplot_t <-arrange(as.data.frame(aggregate(cbind(POPULATION,VTOTAL,PTOTAL) ~ INDEXYEAR,data=subset(ucr94_13,LOCATION =="COUNTY TOTAL" & INDEXYEAR < 2012),sum)),desc(POPULATION))
jpeg_create()
par(mfrow=c(2,2))
with(coplot_t,plot(PTOTAL ~ INDEXYEAR));with(coplot_t,lines(lowess(PTOTAL ~ INDEXYEAR),col="blue"))
with(coplot_t,plot(VTOTAL ~ INDEXYEAR));with(coplot_t,lines(lowess(VTOTAL ~ INDEXYEAR),col="red"))
with(coplot_t,plot(PTOTAL/POPULATION ~ INDEXYEAR));with(coplot_t,lines(lowess(PTOTAL/POPULATION ~ INDEXYEAR),col="blue"))
with(coplot_t,plot(VTOTAL/POPULATION ~ INDEXYEAR));with(coplot_t,lines(lowess(VTOTAL/POPULATION ~ INDEXYEAR),col="red"))
par(mfrow=c(1,1))
par(cex=3.0)
mtext("Top:Volume, Bottom:Per Capita. Lowess Smoothed Property Crime(blue) and Violent Crime (red) from WA UCR 1994 - 2011",line=1)
jpeg_create()
coplot(PTOTAL ~ INDEXYEAR | POPULATION , data=coplot_t,panel = panel.smooth,col="blue")
jpeg_create()
coplot(VTOTAL ~ INDEXYEAR | POPULATION , data=coplot_t,panel = panel.smooth,col="red")
#End Run

# All Counties
# Loop routines to plot with xtabs
List <- subset(ucr94_13,COUNTY == "WHATCOM")
LocalList <- list(unique(subset(List,LOCATION != "COUNTY TOTAL",select=LOCATION)))
rm(i)
for(i in (1:(nrow(as.data.frame(LocalList))))) 
{
jpeg_create()
plot(
as.matrix(
xtabs(PTOTAL ~ INDEXYEAR,data=
subset(List,LOCATION == as.data.frame(LocalList)[i,]))),
col.axis="red",
xlab="Property Crime (Green) Violent Crime (Black): UCR 1994 - 2011",ylab="",
xlim=c(0,20),ylim=c(0,with(List,(max(List[LOCATION == as.data.frame(LocalList)[i,],"PTOTAL"])))),type="l",las=2,lwd=5,col="green")
mtext(as.data.frame(LocalList)[i,])
lines(
as.matrix(
xtabs(VTOTAL ~ INDEXYEAR,data=
subset(List,LOCATION == as.data.frame(LocalList)[i,]))),
col.axis="black",
xlab="Property Crime (Green) Violent Crime (Black): UCR 1994 - 2011",ylab="",
xlim=c(0,20),ylim=c(0,with(List,(max(List[LOCATION == as.data.frame(LocalList)[i,],"PTOTAL"])))),type="l",las=2,lwd=5,col="black")
}

List <- subset(ucr94_13,LOCATION == "COUNTY TOTAL")
LocalList <- list(unique(subset(List,select=COUNTY)))
rm(i)
for(i in (1:(nrow(as.data.frame(LocalList))))) 
{
jpeg_create()
plot(
as.matrix(
xtabs(PTOTAL ~ INDEXYEAR,data=
subset(List,COUNTY == as.data.frame(LocalList)[i,]))),
col.axis="red",
xlab="Property Crime (Green) Violent Crime (Black): UCR 1994 - 2011",ylab="",
xlim=c(1,20),ylim=c(0,with(List,(max(List[COUNTY == as.data.frame(LocalList)[i,],"PTOTAL"])))),type="l",las=2,lwd=5,col="green")
mtext(as.data.frame(LocalList)[i,])
lines(
as.matrix(
xtabs(VTOTAL ~ INDEXYEAR,data=
subset(List,COUNTY == as.data.frame(LocalList)[i,]))),
col.axis="black",
xlab="Property Crime (Green) Violent Crime (Black): UCR 1994 - 2011",ylab="",
xlim=c(1,20),type="l",las=2,lwd=5,col="black")
mtext(as.data.frame(LocalList)[i,])
}


List <- subset(ucr94_13,LOCATION == "COUNTY TOTAL")
LocalList <- list(unique(subset(List,select=COUNTY)))
rm(i)
for(i in (1:(nrow(as.data.frame(LocalList))))) 
{
#jpeg_create()
# Per Volume
par(mfrow=c(1,2))
plot(
as.matrix(
xtabs(PTOTAL ~ INDEXYEAR,data=
subset(List,COUNTY == as.data.frame(LocalList)[i,]))),
col.axis="black",
xlab="Years",ylab="",
xlim=c(1,20),ylim=c(0,with(List,(max(List[COUNTY == as.data.frame(LocalList)[i,],"PTOTAL"])))),type="l",las=2,lwd=4,col="green")
mtext(as.data.frame(LocalList)[i,])
mtext("Property and Violent Crime By Volume",line=-1)
lines(
as.matrix(
xtabs(VTOTAL ~ INDEXYEAR,data=
subset(List,COUNTY == as.data.frame(LocalList)[i,]))),
col.axis="black",
xlab="Years",ylab="",
xlim=c(1,20),type="l",las=2,lwd=4,col="black")
# Per Capita
plot(
as.matrix(
xtabs(PTOTAL/POPULATION ~ INDEXYEAR,data=
subset(List,COUNTY == as.data.frame(LocalList)[i,]))),
col.axis="red",
xlab="Years",ylab="",
xlim=c(1,20),type="l",las=2,lwd=4,col="light blue")
mtext(as.data.frame(LocalList)[i,])
mtext("Property and Violent Crime(10x) / Population",line=-1)
lines(
as.matrix(
xtabs(((VTOTAL/POPULATION) * 10) ~ INDEXYEAR,data=
subset(List,COUNTY == as.data.frame(LocalList)[i,]))),
col.axis="red",
xlab="Years",ylab="",
xlim=c(1,20),type="l",las=2,lwd=4,col="gray")

par(mfrow=c(1,1))
mtext("UCR Crime for Years 1994 - 2013. Note: 2012 and 2013 may reflect UCR/NIBRS split reporting.",side=3,line=2)
mtext("Property Crime Volume (green), Violent Crime Volume (black)",side=1,line=3) 
mtext("Property/POP (blue), Violent(10x)/POP (gray)",side=1,line=4)
}

# All Counties
# Loop routines to plot with aggregate
# show population, vtotal, ptotal on one graph
List <- subset(ucr94_13,LOCATION == "COUNTY TOTAL")
LocalList <- list(unique(subset(List,select=COUNTY)))
rm(i)
for(i in (1:(nrow(as.data.frame(LocalList))))) 
{
#jpeg_create()
t1 <- subset(List,COUNTY == as.data.frame(LocalList)[i,] & INDEXYEAR < 2012)
t2 <- subset(List,COUNTY == as.data.frame(LocalList)[i,] & INDEXYEAR < 2012,select=POPULATION)
plot(as.matrix(aggregate(POPULATION/10 ~ INDEXYEAR ,data=t1,sum)),ylim=c(0,max(t2/10)),type="l",las=2,lwd=4,col="red")
lines(as.matrix(aggregate(PTOTAL ~ INDEXYEAR ,data=t1,sum)),col="blue",lwd=4)
lines(as.matrix(aggregate(VTOTAL ~ INDEXYEAR ,data=t1,sum)),col="black",lwd=4)
mtext(as.data.frame(LocalList)[i,])
mtext("By Volume: Population/10 (red), Property Crime (blue), Violent Crime (black) for WA UCR 1994 - 2011",line=-1)
}

#coplot in loop
List <- subset(ucr94_13,LOCATION == "COUNTY TOTAL")
LocalList <- list(unique(subset(List,select=COUNTY)))
rm(i)
for(i in (1:(nrow(as.data.frame(LocalList))))) 
{
#jpeg_create()
t1 <- subset(List,COUNTY == as.data.frame(LocalList)[i,] & INDEXYEAR < 2012)
t2 <- subset(List,COUNTY == as.data.frame(LocalList)[i,] & INDEXYEAR < 2012,select=POPULATION)
coplot(PTOTAL ~ INDEXYEAR | POPULATION,data=t1, panel = panel.smooth,xlab=as.data.frame(LocalList)[i,])
coplot(VTOTAL ~ INDEXYEAR | POPULATION,data=t1, panel = panel.smooth,xlab=as.data.frame(LocalList)[i,])
}

# using specific LocalList[]
t1 <- subset(List,COUNTY == as.data.frame(LocalList)[17,] & INDEXYEAR < 2012)
coplot(PTOTAL ~ INDEXYEAR | POPULATION,data=t1, panel = panel.smooth,xlab=as.data.frame(LocalList)[17,])
coplot(VTOTAL ~ INDEXYEAR | POPULATION,data=t1, panel = panel.smooth,xlab=as.data.frame(LocalList)[17,])

t1 <- subset(List,COUNTY == as.data.frame(LocalList)[37,] & INDEXYEAR < 2012)
coplot(PTOTAL ~ INDEXYEAR | POPULATION,data=t1, panel = panel.smooth,xlab=as.data.frame(LocalList)[37,])
coplot(VTOTAL ~ INDEXYEAR | POPULATION,data=t1, panel = panel.smooth,xlab=as.data.frame(LocalList)[37,])

t1 <- subset(List,COUNTY == as.data.frame(LocalList)[39,] & INDEXYEAR < 2012)
coplot(PTOTAL ~ INDEXYEAR | POPULATION,data=t1, panel = panel.smooth,xlab=as.data.frame(LocalList)[39,])
coplot(VTOTAL ~ INDEXYEAR | POPULATION,data=t1, panel = panel.smooth,xlab=as.data.frame(LocalList)[39,])


# Some Functions need work
LocalList <- list(unique(ucr94_13$LOCATION))
fun_VP <- function(x) {arrange(as.data.frame(aggregate(cbind(VTOTAL,PTOTAL,TOTAL) ~ INDEXYEAR,data=subset(ucr94_13,LOCATION == x),sum)),desc(INDEXYEAR))}
fun_VP("Bellingham Police Department")
cat('
   INDEXYEAR VTOTAL PTOTAL TOTAL
1       2011    200   3745  3945
2       2010    280   3768  4048
3       2009    171   4358  4529
4       2008    181   4381  4562
5       2007    187   5053  5240
6       2006    196   5354  5550
7       2005    174   5597  5771
8       2004    182   5903  6085
9       2003    181   5181  5362
10      2002    164   4973  5137
11      2001    137   4728  4865
12      2000    134   4032  4166
13      1999    192   4246  4438
14      1998    198   4577  4775
15      1997    203   4416  4619
16      1996    192   4565  4757
17      1995    217   4526  4743
18      1994    233   4251  4484
')

lapply(LocalList[[1]][3],fun_VP)

LocalList <- unique(ucr94_13$LOCATION)
fun_VC <- function(x)
 {arrange(
as.data.frame(
xtabs(VTOTAL ~ INDEXYEAR,data=subset(
ucr94_13,COUNTY == "WHATCOM" & LOCATION == x))),
desc(INDEXYEAR))}


LocalList <- unique(ucr94_13$LOCATION)
fun_VC <- function(x)
{plot(
as.matrix(
xtabs(VTOTAL ~ INDEXYEAR,data=subset(
ucr94_13,LOCATION == x))),
xlab="1994 - 2011",ylab="Violent Crime", type="l",las=2)}


List <- subset(ucr94_13,COUNTY == "WHATCOM")
LocalList <- list(unique(subset(List,LOCATION != "COUNTY TOTAL",select=LOCATION)))
fun_VC <- function(x)
{
plot(
as.matrix(
xtabs(VTOTAL ~ INDEXYEAR,data=
subset(List,LOCATION == x))),
xlab="1994 - 2011",ylab="Violent Crime",type="l",las=2)}

LocalList <- unique(ucr94_13$LOCATION)
fun_VC <- function(x)
{plot(
arrange(
as.data.frame(
xtabs(VTOTAL ~ INDEXYEAR,data=subset(
ucr94_13,COUNTY == "WHATCOM" & LOCATION == x))),
desc(INDEXYEAR)))}