Children in Poverty in Whatcom County School Districts. Political piece here.
## Census ReadMe here: http://www.census.gov/did/www/saipe/downloads/sd13/README.txt
## Data from Census SAIPE program:
## http://www.census.gov/did/www/saipe/downloads/sd13/sd13_WA.txt
## Parsing space delimited files with multiple space delimited School District names in a file with multiple space delimiters was a pain!
## Below I collapse all the multiple spaces to one each,remove some useless name verbiage
## and then write out a tabbed delimited file that I hand edit in Notepad++
## setwd("C:/Politics")
library(sqldf)
library(plyr)
header <- c("FIPS","CCDID","DName","TotPop","SAChild","SAChildPovHH")
t1 <- read.delim("sd13_WA.txt",sep=c("\t"),strip.white = TRUE,header=FALSE)
t2 <- gsub(" +", " ", noquote(t1$V1))
t3 <- gsub("School District", "", noquote(t2),fixed=TRUE)
t3 <- gsub("sd13_WA.txt 24NOV2014", "", noquote(t3),fixed=TRUE)
t4 <- gsub(" +", "\t", noquote(t3),fixed=TRUE)
write(t4,file="t4.txt",sep="\t")
# open up "t4.txt" in a editor like Notepad++ and merge all School District descriptions to one word.
# check for spaces in DName and duplicates; use 'sapply(t4,class)' to check all columns; all but the DName should be integer or numeric class.
# then run the next series of commands
t5 <- data.frame(read.delim("t4.txt",sep="",col.names=header,fill=TRUE))
# substitute School Districts of your choice here
t6 <- droplevels(sqldf("Select * from t5 where
DNAME LIKE 'Blaine' OR
DNAME LIKE 'Ferndale' OR
DNAME LIKE 'Bellingham' OR
DNAME LIKE 'Lynden' OR
DNAME LIKE 'NooksackValley' OR
DNAME LIKE 'MountBaker' OR
DNAME LIKE 'Meridian' OR
DNAME LIKE 'Concrete' OR
DNAME LIKE 'Sedro-Woolley' "))
# All WA State data
a.1 <-arrange(data.frame(xtabs((SAChildPovHH)~ DName, data=t5)),desc(Freq));colnames(a.1) <- c("Children In Poverty in District", "Count")
a.2 <- arrange(data.frame(xtabs(as.integer((SAChildPovHH/SAChild) * 100) ~ DName, data=t5)),desc(Freq));colnames(a.2) <- c("Children In Poverty / Children in District", "PCT")
a.3 <- cbind(a.1,a.2)[1:25,]
a.4 <- a.3[,c(1,3)]
a.4[,2] %in% a.4[,1]
# not a pretty chart
a1 <- arrange(data.frame(xtabs((SAChildPovHH)~ DName, data=t5)),desc(Freq))
with(a1,barplot(Freq,names.arg=DName,cex.names= .25,las=2,col="yellow"))
mtext("2013 School Children in Poverty in 294 WA School Districts", line=-9,cex=1.75)
mtext("196,162 out of 1,147,859 WA School Children (17%) are attached to a household living in Poverty", line=-11,cex=1.75)
a2 <- arrange(data.frame(xtabs((SAChildPovHH)~ DName, data=t5)),desc(Freq))
a3 <- arrange(data.frame(xtabs((SAChildPovHH/SAChild) ~ DName, data=t5)),desc(Freq))
a4 <- arrange(data.frame(xtabs((TotPop)~ DName, data=t5)),desc(Freq))
a5 <- arrange(data.frame(xtabs((SAChildPovHH/TotPop) ~ DName, data=t5)),desc(Freq))
# All Whatcom County data
t7 <- arrange(data.frame(xtabs((SAChildPovHH)~ DName, data=t6)),desc(Freq))
t8 <- arrange(data.frame(xtabs((SAChildPovHH/SAChild) ~ DName, data=t6)),desc(Freq))
t9 <- arrange(data.frame(xtabs((TotPop)~ DName, data=t6)),desc(Freq))
t10 <- arrange(data.frame(xtabs((SAChildPovHH/TotPop) ~ DName, data=t6)),desc(Freq))
with(t7,barplot(Freq,names.arg=DName,cex.names= .85,col="yellow"))
mtext("SAIPE 2013 School Children in Poverty in 9 (whole or in part) Whatcom County School Districts:", line=-9,cex=1.75)
mtext("5,618 out of 35,624 School Children (15.8%) are attached to a household living in Poverty.", line=-11,cex=1.75)
mtext("The 2013 Federal Poverty level for a family of four with two children is $23,624.",line=-13,cex=1.75)
with(t8,barplot(Freq,names.arg=DName,cex.names= .85,las=1,col="red"))
mtext("School Children in Poverty / Total Children in District",cex=1.75)
with(t9,barplot(Freq,names.arg=DName,cex.names= .85,cex=.65,las=1,col="light blue"))
mtext("Total Population in District",cex=1.75)
with(t10,barplot(Freq,names.arg=DName,cex.names= .85,las=1,col="blue"))
mtext("School Children in Poverty / Total Population in District",cex=1.75)
# For school districts, check the map here: https://www.k12.wa.us/maps/images/SchoolDistrictsBig.png
# Some output for Whatcom County School Districts
colSums(t6[,4:6]) # All nine school Districts including two (Concrete, Sedro-Woolley) shared with Skagit County
# TotPop SAChild SAChildPovHH
# 237838 35624 5618
colSums(t6[c(c(1,2),c(4:8)),4:6]) # Remaining seven school districts all encapsulated by Whatcom County
# TotPop SAChild SAChildPovHH
# 206271 30416 4705
t6
cat('
FIPS CCDID DName TotPop SAChild SAChildPovHH
1 53 420 Bellingham 104767 12340 1908
2 53 570 Blaine 16388 2380 420
3 53 1660 Concrete 4921 717 168
4 53 2850 Ferndale 31180 5885 912
5 53 4620 Lynden 18863 3612 397
6 53 5010 Meridian 10032 1630 249
7 53 5310 MountBaker 14724 2573 515
8 53 5670 NooksackValley 10317 1996 304
9 53 7740 Sedro-Woolley 26646 4491 745
')
cSt5 <- colSums(t5[,4:6]) # Entire 294 School Districts in WA State
cSt6 <- colSums(t6[,4:6]) # 8 Whatcom County School Districts
cSt5
# TotPop SAChild SAChildPovHH
# 6951521 1147859 196162
cSt6
# TotPop SAChild SAChildPovHH
# 237838 35624 5618
as.matrix(cSt5)
cat(' [,1]
TotPop 6951521
SAChild 1147859
SAChildPovHH 196162
')
as.matrix(cSt6)
cat(' [,1]
TotPop 237838
SAChild 35624
SAChildPovHH 5618
')
# All WA State %
196162/1147859
#[1] 0.1708938
# All Whatcom School Districts
5618/35624
#[1] 0.1577027
No comments:
Post a Comment