Monday, May 11, 2015

GF-S Expenditure/Revenue Code



Political piece here. Click on the massive graphic above to enlarge.

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)
   }
plot.new()

library(dplyr)
library(lattice)

ofmPOP_14 <- read.csv("ofm_april1_population_final.csv", ,stringsAsFactors = TRUE)
cjdb90_13 <- read.csv("cjdb90_13.csv", ,stringsAsFactors = FALSE)
ER <- read.csv("ExpenditureRevenue2013.csv")
par(mfrow=c(1,1))


attach(ofmPOP_14)
JS <- Jurisdiction %in% grep(" County",Jurisdiction,value=TRUE);c1 <- subset(ofmPOP_14, JS)
JS <- c1$Jurisdiction %in% grep("Incorporated *",c1$Jurisdiction,invert=TRUE,value=TRUE,perl=TRUE);c1 <- subset(c1, JS)
JS <- c1$Jurisdiction %in% grep("Unincorporated *",c1$Jurisdiction,invert=TRUE,value=TRUE,,perl=TRUE);c1 <- subset(c1, JS)
detach(ofmPOP_14)

r1 <- merge(c1[,c(1,6)],ER[,c(1,3,5)], by="County")
r2 <- cbind(r1,Eratio = ((r1$E2013 * 1000)/r1$X2013),Rratio = ((r1$R2013 * 1000)/r1$X2013))
r2 <- with(r2,cbind(r2,"EsubtR"=(Eratio - Rratio), "EoverR"=Eratio/Rratio))
r3 <- arrange(r2,Eratio)
r4 <- arrange(r2,Rratio)
r5 <- arrange(r2,EsubtR)
r6 <- arrange(r2,EoverR)

with(r6,barplot(EoverR,names.arg=County,cex.names=.75,las=2,col="grey"))
abline(h=1,col="red")
mtext("Expenditure/Revenue (grey); Only counties where E/R =< '1.0' return surplus revenue to WA." ,line=1)

with(r2,barplot(Eratio,names.arg=County,cex.names=.75,las=2,col=rgb(0,0,1,.65),ylim=c(0,3.75)))
par(new=T)
with(r2,barplot(Rratio,las=2,col=rgb(1,0,0,.65),ylim=c(0,3.75)))
par(new=T)
mtext("Expenditure/Person (blue); Revenue/Person (red); Orange is Revenue/Person that exceeds Expense/Person.",line=1)
par(new=F)

par(mfrow=c(1,2))
with(r3,barplot(Eratio,names.arg=County,cex.names=.75,las=2,col="blue",ylim=c(0,3.0)))
with(r4,barplot(Rratio,names.arg=County,cex.names=.75,las=2,col="red",ylim=c(0,3.0)))
par(mfrow=c(1,1))
mtext("Expenditure/Person (blue); Revenue/Person (red)",line=1)


# download("http://www.ofm.wa.gov/fiscal/expenditures_and_revenues/county_expenditures_revenues.pdf")
cat('Data from  OFM Forecasting April 2015 : www.ofm.wa.gov/fiscal/expenditures_and_revenues/county_expenditures_revenues.pdf
Table 7 Comparison of FY 2008 and FY 2013 Expenditures and Revenues by County, Method 1 ($ in millions).
Translate this into an R worthy CSV and add some diffs:

head(ER)
   County E2008  E2013 R2008 R2013 R13subtE13 R08subtE08 R13subtR08 E13subtE08
1   Adams  55.7   53.6  23.3  31.7      -21.9      -32.4        8.4       -2.1
2  Asotin  50.4   51.1  22.9  31.0      -20.1      -27.5        8.1        0.7
3  Benton 405.6  454.0 319.6 412.6      -41.4      -86.0       93.0       48.4
4  Chelan 172.7  180.2 168.0 173.8       -6.4       -4.7        5.8        7.5
5 Clallam 153.0  162.7 118.5 116.5      -46.2      -34.5       -2.0        9.7
6   Clark 911.4 1067.3 655.6 697.8     -369.5     -255.8       42.2      155.9
')

library(dplyr)
library(lattice)

setwd("C:/Crime")
ER <- read.csv("ExpenditureRevenue.csv")
plot.new()

attach(ER)
ER <- cbind(ER,"R13subtE13"=(R2013 - E2013))
ER <- cbind(ER,"R08subtE08"=(R2008 - E2008))
ER <- cbind(ER,"R13subtR08"= (R2013 - R2008))
ER <- cbind(ER,"E13subtE08"=(E2013 - E2008))
ER <- cbind(ER,"R08toR13"= ((1 - (R2008/R2013)) * 100 ))
ER <- cbind(ER,"E08toE13"=((1 - (E2008/E2013)) * 100))

detach(ER)

arrange(ER,desc(E2013))

par(mfrow=c(1,2))
with(arrange(ER,desc(R2013)),pie(R2013,labels=County[1:17],radius=1,cex=.85,clockwise=TRUE,col=rainbow(length(County))))
with(arrange(ER,desc(E2013)),pie(E2013,labels=County[1:17],radius=1,cex=.85,clockwise=TRUE,col=rainbow(length(County))))
par(mfrow=c(1,1))
mtext("State Revenues (left) and Expenses (right) in WA State for 2013: All Counties",line=2)

par(mfrow=c(1,2))
with(arrange(ER,desc(R2013))[-1,],pie(R2013,labels=County[1:22],radius=1.0,angle=90,cex=.85,clockwise=TRUE,col=rainbow(length(County))))
with(arrange(ER,desc(E2013))[-1,],pie(E2013,labels=County[1:22],radius=1.0,angle=90,cex=.85,clockwise=TRUE,col=rainbow(length(County))))
par(mfrow=c(1,1))
mtext("State Revenues (left) and Expenses (right) in WA State for 2013: NO KING COUNTY",line=2)

par(mfrow=c(1,2))
with(arrange(ER,desc(R2013))[-10:-1,],pie(R2013,labels=County[1:22],radius=1.0,angle=90,cex=.85,clockwise=TRUE,col=rainbow(length(County))))
with(arrange(ER,desc(E2013))[-10:-1,],pie(E2013,labels=County[1:22],radius=1.0,angle=90,cex=.85,clockwise=TRUE,col=rainbow(length(County))))
par(mfrow=c(1,1))
mtext("State Revenues (left) and Expenses (right) in WA State for 2013: NO TOP TEN COUNTIES",line=2)

arrange(ER,desc(E2013))
arrange(ER,desc(R13subtE13))

with(arrange(ER,desc(R13subtE13)),barchart(County ~ R13subtE13,cex=.85,col=rainbow(length(County))))
mtext("State (Revenues - Expenses) in WA State for 2013: All Counties",line=4)
with(arrange(ER,desc(R08subtE08)),barchart(County ~ R08subtE08,cex=.85,col=rainbow(length(County))))
mtext("State (Revenues - Expenses) in WA State for 2008: All Counties",line=4)
with(arrange(ER,desc(R13subtE13))[-1,],barchart(County ~ R13subtE13,cex=.85,col=rainbow(length(County))))
mtext("State (Revenues - Expenses) in WA State for 2013: NO KING COUNTY",line=4)
with(arrange(ER,desc(R08subtE08))[-1,],barchart(County ~ R08subtE08,cex=.85,col=rainbow(length(County))))
mtext("State (Revenues - Expenses) in WA State for 2008: NO KING COUNTY",line=4)
with(arrange(ER,desc(R13subtE13))[-1,],barchart(County ~ R13subtE13 + R08subtE08,cex=.85,auto.key=TRUE))
mtext("State (Revenues - Expenses) in WA State for 2008 and 2013: NO KING COUNTY",line=4)


jpeg_create <- function() {
 systime <- as.numeric(Sys.time())
 # dev.new()
 jpeg(filename = systime,
          width = 1640, height = 1440, units = "px", pointsize = 24,
          quality = 500, bg = "white", res = NA, family = "", restoreConsole = TRUE,
          type = c("windows"))
 Sys.sleep(2)
   }

library(dplyr)
library(lattice)

ofmPOP_14 <- read.csv("ofm_april1_population_final.csv", ,stringsAsFactors = TRUE)
cjdb90_13 <- read.csv("cjdb90_13.csv", ,stringsAsFactors = FALSE)
ER <- read.csv("ExpenditureRevenue2013.csv")
par(mfrow=c(1,1))

attach(ofmPOP_14)
JS <- Jurisdiction %in% grep(" County",Jurisdiction,value=TRUE);c1 <- subset(ofmPOP_14, JS)
JS <- c1$Jurisdiction %in% grep("Incorporated *",c1$Jurisdiction,invert=TRUE,value=TRUE,perl=TRUE);c1 <- subset(c1, JS)
JS <- c1$Jurisdiction %in% grep("Unincorporated *",c1$Jurisdiction,invert=TRUE,value=TRUE,,perl=TRUE);c1 <- subset(c1, JS)
detach(ofmPOP_14)

r1 <- merge(c1[,c(1,6)],ER[,c(1,3,5)], by="County")
r2 <- cbind(r1,Eratio = ((r1$E2013 * 1000)/r1$X2013),Rratio = ((r1$R2013 * 1000)/r1$X2013))
r2 <- with(r2,cbind(r2,"EsubtR"=(Eratio - Rratio), "EoverR"=Eratio/Rratio))
r3 <- arrange(r2,Eratio)
r4 <- arrange(r2,Rratio)
r5 <- arrange(r2,EsubtR)
r6 <- arrange(r2,EoverR)

jpeg_create()
with(r6,barplot(EoverR,names.arg=County,cex.names=.75,las=2,col="grey"))
abline(h=1,col="red")
mtext("Expenditure/Revenue (grey); Only counties where E/R =< '1.0' return surplus revenue to WA." ,line=1)

jpeg_create()
with(r2,barplot(Eratio,names.arg=County,cex.names=.75,las=2,col=rgb(0,0,1,.65),ylim=c(0,3.75)))
par(new=T)
with(r2,barplot(Rratio,las=2,col=rgb(1,0,0,.65),ylim=c(0,3.75)))
par(new=T)
mtext("Expenditure/Person (blue); Revenue/Person (red); Orange is Revenue/Person that exceeds Expense/Person.",line=1)
par(new=F)

jpeg_create()
par(mfrow=c(1,2))
with(r3,barplot(Eratio,names.arg=County,cex.names=.75,las=2,col="blue",ylim=c(0,3.0)))
with(r4,barplot(Rratio,names.arg=County,cex.names=.75,las=2,col="red",ylim=c(0,3.0)))
par(mfrow=c(1,1))
mtext("Expenditure/Person (blue); Revenue/Person (red)",line=1)


## GF_S Expenditure by County
GFS <- read.csv("GFS-Expenditure2013.csv",stringsAsFactors = FALSE)
GFSCat <- as.data.frame(stack(colSums(GFS[,-1])))
colnames(GFSCat) <- c("Amount","Categories")

t1 <- arrange(GFSCat, desc(Amount))
t1
cat('
   Amount        Categories
1 6893586  School.Districts
2 4199985            AOA_DS
3 2699969              DSHS
4  803005       Corrections
5  525872  Two.Year.College
6  439423 Four.Year.College
7  221160              WSAC
')

jpeg_create()
with(arrange(GFSCat, desc(Amount)),barplot(Amount,names.arg=Categories))

jpeg_create <- function() {
 systime <- as.numeric(Sys.time())
 # dev.new()
 jpeg(filename = systime,
          width = 2440, height = 1640, units = "px", pointsize = 24,
          quality = 500, bg = "white", res = NA, family = "", restoreConsole = TRUE,
          type = c("windows"))
 Sys.sleep(2)
   }

jpeg_create()
par(mfrow=c(2,4))
with(arrange(GFSCat,desc(Amount)),pie(Amount,labels=Categories,radius=1,cex=.85,clockwise=TRUE,col=rainbow(nrow(GFSCat))));mtext("All GF-S Expenditures By Category = $15.8B",side=1,line=2)
with(arrange(GFS,desc(School.Districts)),pie(School.Districts,labels=County[1:15],radius=1,cex=.85,clockwise=TRUE,col=rainbow(length(County))));mtext("School District GF-S Expenditures = $6.9B",side=1,line=2)
with(arrange(GFS,desc(AOA_DS)),pie(AOA_DS,labels=County[1:15],radius=1,cex=.85,clockwise=TRUE,col=rainbow(length(County))));mtext("All Other Agencies/ Debt Service = $4.2B",side=1,line=2)
with(arrange(GFS,desc(DSHS)),pie(DSHS,labels=County[1:15],radius=1,cex=.85,clockwise=TRUE,col=rainbow(length(County))));mtext("DSHS GF-S Expenditures = $2.7B",side=1,line=2)
with(arrange(GFS,desc(Corrections)),pie(Corrections,labels=County[1:15],radius=1,cex=.85,clockwise=TRUE,col=rainbow(length(County))));mtext("Corrections GF-S Expenditures = $800M",side=1,line=2)
with(arrange(GFS,desc(Two.Year.College)),pie(Two.Year.College,labels=County[1:15],radius=1,cex=.85,clockwise=TRUE,col=rainbow(length(County))));mtext("Two Year College GF-S Expenditures = $526M",side=1,line=2)
with(arrange(GFS,desc(Four.Year.College)),pie(Four.Year.College,labels=County[1:15],radius=1,cex=.85,clockwise=TRUE,col=rainbow(length(County))));mtext("Four Year College GF-S Expenditures = $440M",side=1,line=2)
with(arrange(GFS,desc(WSAC)),pie(WSAC,labels=County[1:15],radius=1,cex=.85,clockwise=TRUE,col=rainbow(length(County))));mtext("WSAC GF-S Expenditures = $221M",side=1,line=2)
par(mfrow=c(1,1))
mtext("From Table 4 FY 2013 GF-S Expenditures (Allocation Method 1, by County Where Benefits Were Received)",line=3)
mtext("http://www.ofm.wa.gov/fiscal/expenditures_and_revenues/county_expenditures_revenues.pdf",line=2)
mtext("FY 2013 GF-S Expenditures = $15.8 Billion",line=1)


subset(arrange(ER,desc(R2013)), R2013 > 400)

No comments:

Post a Comment