Political piece is here. Web query code is unpublished. Project and Data Analysis are ongoing and subject to revision. -RMF
12:23 PM Tuesday, June 23, 2015 -RMF
# Analysis for use after the POSH, grep, gawk, and sed routines in get-JailList.ps1 retrieve data.
# from "http://apps2.whatcomcounty.us/onlineapps/jailrosters/press/press.jsp"
# Use the fill=FALSE method in read.csv() to flush out and fill in manually all the incomplete fields in a
# text editor with line numbering and regex like Notepad++
# Booked and Crimes are separate CSVs and databases here from the same Jail Roster press releases.
library(dplyr)
# Booked
Booked <- read.csv("Booked.csv", fill=FALSE, header = FALSE, strip.white = TRUE, sep = ",", quote = "", stringsAsFactors = FALSE)
colnames(Booked) <- c("ID","LName","FMName")
Booked <- arrange(Booked,ID)
nrow(Booked)
# Multiple Bookings
MBooked <- subset(arrange(count(Booked,ID),desc(n)),n > 1)
nrow(MBooked)
nrow(subset(Booked, ID %in% MBooked$ID))
# Crimes
Crimes <- read.csv("CrimeASCII.csv", fill=FALSE, header = FALSE, strip.white = TRUE, sep = ",", quote = "", stringsAsFactors = FALSE)
colnames(Crimes) <- c("Code","Crime")
l1 <- arrange(data.frame(count(Crimes,Crime)),desc(n))
l2 <- arrange(data.frame(count(Crimes,Code)),desc(n))
# arrange(as.data.frame(xtabs(~Crime,data=Crimes)),desc(Freq))
# arrange(as.data.frame(xtabs(~Code,data=Crimes)),desc(Freq))
t1 <- arrange(as.data.frame(xtabs(~Crime,data=Crimes)),desc(Freq))
t2 <- arrange(as.data.frame(xtabs(~Code,data=Crimes)),desc(Freq))
CT <- read.delim("Search.Terms.txt")
CT <- arrange(CT,Terms)
rm(CN)
CN <- data.frame()
for(i in CT$Terms) {CN <- append(CN,(rbind((sum(subset(t1,grepl(i,Crime,ignore.case=TRUE),select=c("Freq")))))))}
data.frame(stack(CN))
CTN <- arrange(data.frame(cbind(CT,(data.frame(stack(CN))))),desc(values))
colnames(CTN) <- c("Term","Count","")
# arrange(aggregate(Crime ~ Code, subset(Crimes,Crime == "DUI"), FUN=length),desc(Crime))
# arrange(aggregate(Code ~ Crime, subset(Crimes,Code == "LUM"), FUN=length),desc(Code))
p1 <- arrange(aggregate(Crime ~ Code, subset(Crimes,grepl("ASSAULT",Crime)), FUN=length),desc(Crime))
p2 <- arrange(aggregate(Crime ~ Code, subset(Crimes,grepl("DUI",Crime)), FUN=length),desc(Crime))
p3 <- arrange(aggregate(Crime ~ Code, subset(Crimes,grepl("DWLS",Crime)), FUN=length),desc(Crime))
p4 <- arrange(aggregate(Crime ~ Code, subset(Crimes,grepl("VIOL",Crime)), FUN=length),desc(Crime))
# searchs for CT Terms
#for(i in CT$Terms) {print(as.matrix(grep(i,t1$Crime,ignore.case=TRUE,value=TRUE)))}
for(i in CT$Terms) {print("Grep for:");print(i);print(subset(t1,grepl(i,Crime,ignore.case=TRUE),select=c("Crime","Freq")))}
# Charts
par(mfrow=c(1,1))
# Top Ten for Code and Crime
with(t1,barplot(as.vector(t1[1:10,2]),names.arg=substr(t1[1:10,1],1,9),las=2,cex.names=.75,))
with(t2,barplot(as.vector(t2[1:10,2]),names.arg=substr(t2[1:10,1],1,9),las=2,cex.names=.75,))
with(CTN,barplot(Count,names.arg=Term,las=2,cex.names=.65))
title(main="Arbitrary Categories created by Search Terms")
pie(t1[1:10,2], labels=substr(t1[1:10,1],1,9),radius=1,col=rainbow(9))
title(main="Top 10 Crime Categories by Volume")
pie(t2[1:10,2], labels=substr(t2[1:10,1],1,19),radius=1,col=rainbow(9))
title(main="Top 10 Code Categories by Volume")
pie(CTN$Count, labels=CTN$Term,radius=1.1,col=rainbow(9))
title(main="Arbitrary Categories created by Search Terms")
par(mfrow=c(1,4))
pie(p1$Crime, labels=p1$Code,radius=1,col=rainbow(9))
title(main="ASSAULT by CODE")
pie(p2$Crime, labels=p2$Code,radius=1,col=rainbow(9))
title(main="DUI by CODE")
pie(p3$Crime, labels=p3$Code,radius=1,col=rainbow(9))
title(main="DWLS by CODE")
pie(p3$Crime, labels=p3$Code,radius=1,col=rainbow(9))
title(main="VIOL by CODE")
par(mfrow=c(1,1))
par(mfrow=c(1,4))
pie(p1$Crime, labels=p1$Code,radius=1,col=rainbow(9))
title(main="ASSAULT by CODE")
pie(p2$Crime, labels=p2$Code,radius=1,col=rainbow(9))
title(main="DUI by CODE")
pie(p3$Crime, labels=p3$Code,radius=1,col=rainbow(9))
title(main="DWLS by CODE")
pie(p3$Crime, labels=p3$Code,radius=1,col=rainbow(9))
title(main="VIOL by CODE")
par(mfrow=c(1,1))
# Charts with jpeg_create Macro
# Macro
jpeg_create <- function() {
systime <- as.numeric(Sys.time())
# dev.new()
jpeg(filename = systime,
width = 1024, height = 768, units = "px", pointsize = 12,
quality = 100, bg = "white", res = NA, family = "", restoreConsole = TRUE,
type = c("windows"))
Sys.sleep(2)
}
par(mfrow=c(1,1))
# Bar Charts: Top Ten for Code and Crime
jpeg_create()
with(t1,barplot(as.vector(t1[1:10,2]),names.arg=substr(t1[1:10,1],1,9),las=2,cex.names=.75,))
title(main="Top Ten for Crime")
jpeg_create()
with(t2,barplot(as.vector(t2[1:10,2]),names.arg=substr(t2[1:10,1],1,9),las=2,cex.names=.75,))
title(main="Top Ten for Code ('Court')")
jpeg_create()
with(CTN,barplot(Count,names.arg=Term,las=2,cex.names=.65))
title(main="Arbitrary Categories created by Search Terms")
# Pie Charts
jpeg_create()
pie(t1[1:10,2], labels=substr(t1[1:10,1],1,9),radius=1,col=rainbow(9))
title(main="Top 10 Crime Categories by Volume")
jpeg_create()
pie(t2[1:10,2], labels=substr(t2[1:10,1],1,19),radius=1,col=rainbow(9))
title(main="Top 10 Code ('Court') Categories by Volume")
jpeg_create()
pie(CTN$Count, labels=CTN$Term,radius=.9,col=rainbow(9))
title(main="Arbitrary Categories created by Search Terms")
# Custom jpeg_create
jpeg_create_very_wide <- function() {
systime <- as.numeric(Sys.time())
# dev.new()
jpeg(filename = systime,
width = 1536, height = 512, units = "px", pointsize = 14,
quality = 100, bg = "white", res = NA, family = "", restoreConsole = TRUE,
type = c("windows"))
Sys.sleep(2)
}
jpeg_create_very_wide()
par(mfrow=c(1,4))
pie(p1$Crime, labels=p1$Code,radius=1,col=rainbow(9))
title(main="ASSAULT by CODE")
pie(p2$Crime, labels=p2$Code,radius=1,col=rainbow(9))
title(main="DUI by CODE")
pie(p3$Crime, labels=p3$Code,radius=1,col=rainbow(9))
title(main="DWLS by CODE")
pie(p3$Crime, labels=p3$Code,radius=1,col=rainbow(9))
title(main="VIOL by CODE")
par(mfrow=c(1,1))
# some sample output
cat('
> head(t1,10)
Crime Freq
1 DUI 676
2 DWLS 3RD DEGREE 506
3 ASSAULT 4TH 362
4 DOC DETAINER 255
5 THEFT 3RD 158
6 MAL MISCH 3RD 152
7 PROB/PAROLE VIOL 118
8 ASSAULT 4TH/FTA 106
9 CONT SUB-POSS NO PRESCRIPTION 106
10 ASSAULT 2ND 99
')
cat('CT
Terms
1 ARREST
2 ASSAULT
3 BURGLARY
4 CONT SUB
5 CONTROLLED SUBSTANCE
6 DISORDERLY
7 DOC
8 DRUG
9 DUI
10 DV
11 DWLS
12 HARASSMENT
13 MAL MISCH
14 ORDER
15 PAROLE
16 POLICE
17 ROBBERY
18 SHOPLIFTING
19 THEFT
20 TRESPASS
21 VEHICLE
22 VIOL
23 VIOLATION
24 WARRANTS
')
cat('
Term Count
1 ASSAULT 706
2 DUI 677
3 DWLS 673
4 VIOL 601
5 THEFT 482
6 ORDER 394
7 DOC 325
8 CONT SUB 219
9 BURGLARY 191
10 MAL MISCH 189
11 TRESPASS 184
12 CONTROLLED SUBSTANCE 170
13 DV 162
14 VIOLATION 157
15 DRUG 143
16 SHOPLIFTING 142
17 PAROLE 120
18 HARASSMENT 109
19 VEHICLE 103
20 DISORDERLY 98
21 POLICE 84
22 ARREST 73
23 WARRANTS 59
24 ROBBERY 43
')
cat('[1] "Grep for:"
[1] "ARREST"
Crime Freq
22 RESISTING ARREST 68
141 WARRANT ARREST 5
[1] "Grep for:"
[1] "ASSAULT"
Crime Freq
3 ASSAULT 4TH 362
8 ASSAULT 4TH/FTA 106
10 ASSAULT 2ND 99
36 ASSAULT 3RD 44
53 ASSAULT 1ST 23
67 ASSAULT AND BATTERY 3RD DV 19
94 ASSAULT BATTERY 3RD 10
101 VEHICULAR ASSAULT 10
105 ASSAULT 3RD/FTA 8
142 ASSAULT 2ND/FTA 4
143 ASSAULT 3RD/HEALTH CAREGIVER 4
163 ASSAULT 3
164 ASSAULT 3RD/LE OFFICER 3
186 ASSAULT 1ST/FIREARM 2
187 ASSAULT 2ND SEXUAL MOTIVATION 2
188 ASSAULT 2ND/WITH KNIFE 2
251 ASSAULT 1ST/NO WEAPON/INJURY 1
252 ASSAULT 2ND/OTHER WEAPON/FTA 1
253 ASSAULT 4TH/FTC 1
254 ASSAULT OF A CHILD 2ND 1
255 ASSAULT OF A CHILD 3RD 1
[1] "Grep for:"
[1] "BURGLARY"
Crime Freq
20 BURGLARY 2ND 74
29 BURGLARY RESIDENTIAL 56
51 BURGLARY 1ST 24
64 BURGLARY 2ND/FTA 20
95 BURGLARY RESIDENTIAL/FTA 10
165 BURGLARY TOOLS - MAKE/POSS 3
189 BURGLARY 1ST/FTA 2
258 BURGLARY 2ND/DAY/ATT FORCE 1
259 BURGLARY 2ND/UNK/NO FORCE 1
[1] "Grep for:"
[1] "CONT SUB"
Crime Freq
9 CONT SUB-POSS NO PRESCRIPTION 106
11 CONT SUB-MFG/DEL/POSS W/INT 99
120 CONT SUB-DIST NAR/METH/FLN MIN 6
169 DEL IN LIEU OF CONT SUB/FTA 3
192 CONT SUB-METH-INT TO MFG 2
193 CONT SUB-NOT IN ORIG CONTAINER 2
267 CONT SUB TRANS/INVOLVE MINOR 1
[1] "Grep for:"
[1] "CONTROLLED SUBSTANCE"
Crime Freq
12 CONTROLLED SUBSTANCE VIOLATION 95
37 DELIVER CONTROLLED SUBSTANCE 44
43 CONTROLLED SUBSTANCE VIOL/FTA 31
[1] "Grep for:"
[1] "DISORDERLY"
Crime Freq
27 DISORDERLY CONDUCT 59
73 DISORDERLY CONDUCT/FTA 17
121 DISORDERLY CONDUCT/ABUSIVE LANGUAGE 6
130 DISORDERLY CONDUCT/DISTURBS THE PEACE 5
146 DISORDERLY COND/ANNOY/BOTHER/INSULT/OFFE 4
200 DISORDERLY COND/FIGHTING 2
270 DISORDERLY COND/CONSUME LIQUOR IN PUBLIC 1
271 DISORDERLY COND/NOISE DISTURBS PEACE 1
272 DISORDERLY COND/OBSTRUCT PEDEST/TRAFFIC 1
273 DISORDERLY COND/REFUSING TO STOP ACTIVIT 1
274 DISORDERLY COND/VULG/PROF/OBSC LANG 1
[1] "Grep for:"
[1] "DOC"
Crime Freq
4 DOC DETAINER 255
23 DOC Swift & Certain 65
147 DOC-NEW CRIMINAL ACTIVITY HOLD 4
285 ID DOCUMENTS VIOL 1
[1] "Grep for:"
[1] "DRUG"
Crime Freq
17 POSS DRUG PARAPHERNALIA 81
48 DRUG COURT VIOLATION 26
123 MAINTAINING A HOUSE FOR DRUGS 6
149 MAN/DEL/POSS NARCOTIC DRUG 4
153 POSS DRUG PARAPHERNALIA/FTA 4
154 POSS LEGEND DRUG W/O RX 4
175 MAINTAINING VEH/PREMISE DRUG TRAFFICKING 3
176 POSESSION OF DRUGS BY PRISONER 3
182 UNLAW POSS LEG DRUG 3
217 MAN/DEL/POSS CNTRFT DRUG 2
223 OBTAIN LEGEND DRUG BY FRAUD 2
229 POSS LEG DRUG NO SCRIPT 2
291 LEGEND DRUG VIOLATION 1
305 POSS LEGEND DRUG W/O RX/PBV 1
314 SELL/DEL/POSS LEG DRUG 1
[1] "Grep for:"
[1] "DUI"
Crime Freq
1 DUI 676
276 DUI - FELONY 1
[1] "Grep for:"
[1] "DV"
Crime Freq
26 VIOL NO CONTACT ORDER/DV 62
45 INTERFERE W/REPORT OF DV 30
46 VIOL DV PROT ORDER 28
67 ASSAULT AND BATTERY 3RD DV 19
79 VIOL PROTECTION ORDER/DV 16
201 DISPLAY WEAPON DV 2
243 VIOL ANTIHARASS ORD/DV 2
244 VIOL DV PROT ORDER/FTA 2
289 INTERFERE W/REPORT OF DV/FTA 1
[1] "Grep for:"
[1] "DWLS"
Crime Freq
2 DWLS 3RD DEGREE 506
14 DWLS 1ST DEGREE 87
18 DWLS 2ND DEGREE 80
[1] "Grep for:"
[1] "HARASSMENT"
Crime Freq
24 HARASSMENT/FEL 65
40 HARASSMENT 36
205 HARASSMENT/FEL/FTA 2
234 TELEPHONE HARASSMENT 2
295 MALICIOUS HARASSMENT/FTA 1
317 TELEPHONE HARASSMENT/FELONY 1
330 VIOL ANTI HARASSMENT ORDER 1
331 VIOL ANTIHARASSMENT ORDR/FTA 1
[1] "Grep for:"
[1] "MAL MISCH"
Crime Freq
6 MAL MISCH 3RD 152
82 MAL MISCH 2ND 15
84 MAL MISCH 1ST 14
214 MAL MISCH 1ST/INTERUPT SVC 2
215 MAL MISCH 1ST/INTERUPT SVC/FTA 2
216 MAL MISCH 2ND/INTERUPT SVC 2
293 MAL MISCH 3RD/GRAFFITI 1
294 MAL MISCH 3RD/GRAFFITI/FTA 1
[1] "Grep for:"
[1] "ORDER"
Crime Freq
19 VIOL PROTECTION ORDER 76
26 VIOL NO CONTACT ORDER/DV 62
27 DISORDERLY CONDUCT 59
33 VIOL NO CONTACT ORDER 50
46 VIOL DV PROT ORDER 28
50 DISOBEDIENCE OF LAWFUL ORDER OF COURT 25
71 VIOL PROTECTION ORDER/FTA 18
73 DISORDERLY CONDUCT/FTA 17
78 VIOL NO CONTACT ORDER/FTA 16
79 VIOL PROTECTION ORDER/DV 16
121 DISORDERLY CONDUCT/ABUSIVE LANGUAGE 6
130 DISORDERLY CONDUCT/DISTURBS THE PEACE 5
146 DISORDERLY COND/ANNOY/BOTHER/INSULT/OFFE 4
200 DISORDERLY COND/FIGHTING 2
244 VIOL DV PROT ORDER/FTA 2
270 DISORDERLY COND/CONSUME LIQUOR IN PUBLIC 1
271 DISORDERLY COND/NOISE DISTURBS PEACE 1
272 DISORDERLY COND/OBSTRUCT PEDEST/TRAFFIC 1
273 DISORDERLY COND/REFUSING TO STOP ACTIVIT 1
274 DISORDERLY COND/VULG/PROF/OBSC LANG 1
329 VIOL ANTI-HARASS ORDER 1
330 VIOL ANTI HARASSMENT ORDER 1
333 VIOL PROTECTIONS ORDER/FTC 1
[1] "Grep for:"
[1] "PAROLE"
Crime Freq
7 PROB/PAROLE VIOL 118
299 O/FPROB/PAROLE VIOL 1
303 PAROLE VIOL 1
[1] "Grep for:"
[1] "POLICE"
Crime Freq
25 OBSTRUCT POLICE OFFICER 62
72 ATTEMPTING ELUDE POLICE VEHICL 17
203 FAIL TO OBEY POLICE OFFICER 2
277 FAIL COMPLY POLICE/FLG/FRFGHT 1
281 GIVE FALSE INFO TO POLICE 1
328 VEH OPR-REFUSE COMPLY POLICE 1
[1] "Grep for:"
[1] "ROBBERY"
Crime Freq
49 ROBBERY 1ST 26
92 ROBBERY 2ND 12
157 ROBBERY 1ST/FTA 4
313 ROBBERY 2ND STRONGARM/BANK 1
[1] "Grep for:"
[1] "SHOPLIFTING"
Crime Freq
16 SHOPLIFTING GOODS LESS THAN 83
31 SHOPLIFTING 54
139 THEFT 3RD/SHOPLIFTING 5
[1] "Grep for:"
[1] "THEFT"
Crime Freq
5 THEFT 3RD 158
13 THEFT 3RD/FTA 92
21 THEFT 2ND 69
56 THEFT OF MOTOR VEHICLE 23
59 THEFT 1ST 22
62 Retail Theft w/Extenuating Circum 3rd 21
63 THEFT 2ND/FTA 21
70 RETAIL THEFT 3RD 18
88 IDENTITY THEFT 2ND 13
110 IDENTITY THEFT 8
126 THEFT OF FIREARM 6
132 IDENTITY THEFT 1ST 5
139 THEFT 3RD/SHOPLIFTING 5
148 IDENTITY THEFT 2ND/FTA 4
159 THEFT 2ND ACCESS DEVICE 4
180 THEFT RENTAL PROP 1ST 3
231 RETAIL THEFT 2ND 2
286 IDENTITY THEFT 1ST/FTA 1
312 RETAIL THEFT 1ST 1
318 THEFT 1ST/AUTOMOBILE 1
319 THEFT 3RD/FTC 1
320 THEFT 3RD/OTHER LOCATIONS 1
321 THEFT 3RD/SERVICES 1
322 THEFT OF PROPERTY LOST/MISLAID/DELIVERED 1
323 THEFT RENTAL PROP 1ST/FTA 1
[1] "Grep for:"
[1] "TRESPASS"
Crime Freq
32 CRIMINAL TRESPASS 2ND 53
34 CRIMINAL TRESPASS 1ST 47
35 CRIMINAL TRESPASS 2ND/FTA 46
44 CRIMINAL TRESPASS 1ST/FTA 30
197 CRIMINAL TRESPASS 1ST/FTC 2
198 CRIMINAL TRESPASS 2ND/FTC 2
236 TRESPASS TO REAL PROPERTY 2
265 COMPUTER TRESPASS 2ND 1
266 COMPUTER TRESPASS 2ND/FTA 1
[1] "Grep for:"
[1] "VEHICLE"
Crime Freq
55 POSSESSION OF STOLEN VEHICLE 23
56 THEFT OF MOTOR VEHICLE 23
57 HIT AND RUN ATTENDED VEHICLE 22
81 HIT/RUN UNATTENDED VEHICLE 15
93 VEHICLE PROWL 2ND 12
160 VEHICLE PROWL 2ND/FTA 4
238 UNATTENDED VEHICLE-HIT VEH/PRO 2
242 VEHICLE PROWL 1ST/FTA 2
[1] "Grep for:"
[1] "VIOL"
Crime Freq
7 PROB/PAROLE VIOL 118
12 CONTROLLED SUBSTANCE VIOLATION 95
19 VIOL PROTECTION ORDER 76
26 VIOL NO CONTACT ORDER/DV 62
33 VIOL NO CONTACT ORDER 50
43 CONTROLLED SUBSTANCE VIOL/FTA 31
46 VIOL DV PROT ORDER 28
47 COMMUNITY CUSTODY VIOLATION 27
48 DRUG COURT VIOLATION 26
71 VIOL PROTECTION ORDER/FTA 18
78 VIOL NO CONTACT ORDER/FTA 16
79 VIOL PROTECTION ORDER/DV 16
99 IGNITION INTERLOCK DR LIC VIOL 10
115 DOM VIOL CRT ORD VIOL-VUL ADUL 7
138 ROLLED SUBSTANCE VIOLATION 5
237 TRIP PERMIT VIOLATION 2
243 VIOL ANTIHARASS ORD/DV 2
244 VIOL DV PROT ORDER/FTA 2
285 ID DOCUMENTS VIOL 1
291 LEGEND DRUG VIOLATION 1
299 O/FPROB/PAROLE VIOL 1
303 PAROLE VIOL 1
325 TRIP PERMIT VIOLATION-USAGE 1
329 VIOL ANTI-HARASS ORDER 1
330 VIOL ANTI HARASSMENT ORDER 1
331 VIOL ANTIHARASSMENT ORDR/FTA 1
332 VIOL CIVIL ANTIHARASS ORD/FTA 1
333 VIOL PROTECTIONS ORDER/FTC 1
[1] "Grep for:"
[1] "VIOLATION"
Crime Freq
12 CONTROLLED SUBSTANCE VIOLATION 95
47 COMMUNITY CUSTODY VIOLATION 27
48 DRUG COURT VIOLATION 26
138 ROLLED SUBSTANCE VIOLATION 5
237 TRIP PERMIT VIOLATION 2
291 LEGEND DRUG VIOLATION 1
325 TRIP PERMIT VIOLATION-USAGE 1
[1] "Grep for:"
[1] "WARRANTS"
Crime Freq
28 FUGITIVE FROM JUSTICE WARRANTS 59
')
No comments:
Post a Comment