12. July 2011
The original data are on several excel files and contain more than 6000 variables. For directly reading excel files we have to install the package xlsReadWrite.
The first attempt to read the file returned me only the first sheet of table. After reading the xlsReadWrite manual I wrote a relatively elegant first solution
> readVars ← function(xlsFile){
+ xl <- xls.open(xlsFile,readonly=TRUE)
+ ix <- xls.info(xl); sn <- ix$sheet.names
+ df <- as.data.frame(lapply(sn,function(x) read.xls(xl,sheet=x,colNames=TRUE)))
+ xls.cancel(xl)
+ return(df)
+ }
> afn <- readVars("./afn01.xls")
> af <- afn[,c("Areaname","STCOU","AFN220197D","AFN220202D","AFN230197D",
+ "AFN230202D","AFN320197D","AFN320202D","AFN330197D","AFN330202D")]
> age <- data.frame(readVars("./age01.xls"),readVars("./age02.xls"),
+ readVars("./age03.xls"),readVars("./age04.xls"))
> sel <- c("STCOU","AGE010180D","AGE010190D","AGE010200D","AGE050180D","AGE050190D",
+ "AGE050200D","AGE110180D","AGE110190D","AGE110200D","AGE270180D","AGE270190D",
+ "AGE270200D","AGE880190D","AGE880200D")
> ag <- age[,sel]
but it turned out that not all tables have the same set of units. To consider this fact I had to rewrite the solution. There were some trubles because R tends to “factorize” the character data. After some attempts I had this under control. But the unexpected STCOUs were still appearing. Finally I noticed that this happens when I am reading saved accumulated variables file counties?.csv. The stanadardization of STCOUs after reading resolved the problems. The scheme is the following
> setwd("D:/Data/counties/USAC")
> library(xlsReadWritePro)
>
> standard <- function(x) {s <- paste("0000",x,sep=""); substr(s,nchar(s)-4,nchar(s))}
>
> mergeVars <- function(v,t){
+ cat("merge:\n"); flush.console()
+ vid <- v$STCOU; tid <- t$STCOU
+ u <- merge(v,t,by.x="STCOU",by.y="STCOU",all=TRUE)
+ vt <- setdiff(vid,tid); tv <- setdiff(tid,vid)
+ if(length(vt)>0) cat(" missing:",vt,"\n")
+ if(length(tv)>0) cat(" new :",tv,"\n")
+ u$Areaname <- ifelse(is.na(u$Areaname.x),u$Areaname.y,u$Areaname.x)
+ u$Areaname.x <- u$Areaname.y <- NULL
+ return(u)
+ }
>
> readVars <- function(xlsFiles){
+ first <- TRUE
+ for(xlsFile in xlsFiles){
+ cat(xlsFile,":\n",sep=''); flush.console()
+ xl <- xls.open(xlsFile,readonly=TRUE)
+ ix <- xls.info(xl); sn <- ix$sheet.names
+ for(s in sn){
+ cat(" ",s,": ",sep=''); flush.console()
+ t <- read.xls(xl,sheet=s,colNames=TRUE,stringsAsFactors=FALSE)
+ t$STCOU <- standard(t$STCOU)
+ if(first) {v <- t; first <- FALSE; cat("\n")} else v <- mergeVars(v,t)
+ }
+ }
+ return(v)
+ }
> # -------------------------
> afn <- readVars("./afn01.xls")
./afn01.xls:
Sheet1:
Sheet2: merge:
Sheet3: merge:
> age <- readVars(c("./age01.xls","./age02.xls","./age03.xls","./age04.xls"))
./age01.xls:
Sheet1:
Sheet2: merge:
...
Sheet10: merge:
./age04.xls:
Sheet1: merge:
Sheet2: merge:
> safn <- c("Areaname","STCOU","AFN220197D","AFN220202D","AFN230197D","AFN230202D","AFN320197D",
+ "AFN320202D","AFN330197D","AFN330202D")
> afnr <- afn[,safn]
> sage <- c("Areaname","STCOU","AGE010180D","AGE010190D","AGE010200D","AGE050180D",
+ "AGE050190D","AGE050200D","AGE110180D","AGE110190D","AGE110200D",
+ "AGE270180D","AGE270190D","AGE270200D","AGE880190D","AGE880200D")
> ager <- age[,sage]
> cou <- mergeVars(afnr,ager)
merge:
missing: 02105 02195 02198 02230 02275
new : 02201 02232 02280
> write.csv(cou,file="counties1.csv",row.names=FALSE)
and
> cou <- read.csv(file="counties3.csv",stringsAsFactors=FALSE) > cou$STCOU <- standard(cou$STCOU) ...
The final selection I also exported to excel. Since it allows only max 256 variables per file I had to split it to 5 files
> A <- cou[,c(1,251:499)] > write.xls(A,file="countiesB.xls",colNames=TRUE,rowNames=FALSE) > A <- cou[,c(1,500:748)] > write.xls(A,file="countiesC.xls",colNames=TRUE,rowNames=FALSE) > A <- cou[,c(1,749:997)] > write.xls(A,file="countiesD.xls",colNames=TRUE,rowNames=FALSE) > A <- cou[,c(1,998:1124)] > write.xls(A,file="countiesE.xls",colNames=TRUE,rowNames=FALSE)
It turned out that in the first selection three variables (“EMS010190D”, “EMS010200D”,”POP600200D”) were not included. I added them to the counties.CSV file and I also exported them to an additional excel file countiesF.xls. I put at wiki zvonka two zip files (CSV and XLS) with the variables needed to produce the final set of variables.
First we produce a smaller file selection.csv that contains all variables needed for computing the final set of variables.
> setwd("D:/Data/counties/USAC")
> library(xlsReadWritePro)
> standard <- function(x) {s <- paste("0000",x,sep=""); substr(s,nchar(s)-4,nchar(s))}
> cou <- read.csv(file="counties.csv",stringsAsFactors=FALSE)
> cou$STCOU <- standard(cou$STCOU)
> selVars <- c("AGE010200D","AGE050200D","AGE110200D","AGE270200D","AGE880200D","AGN020202D",
+ "AGN030202D","AGN050202D","AGN330202D","AGN420202D","AGN430202D","BNK050200D","BZA115203D",
+ "CLF040200D","CRM110200D","CRM140200D","CRM150200D","EDU010201D","EDU600200D","EDU610200D",
+ "EDU635200D","EDU685200D","EDU910200D","EDU920200D","ELE020200D","ELE030200D","EMN070201D",
+ "EMN100201D","EMS010190D","EMS010200D","FED110200D","GEE020200D","GEE320200D","HIS010200D",
+ "HIS020200D","HIS030200D","HIS040200D","HSG030200D","HSG045200D","HSG190200D","HSG200200D",
+ "HSG215200D","HSG220200D","HSG230200D","HSG295200D","HSG440200D","HSG495200D","HSG680200D",
+ "INC610199D","INC910189D","INC910199D","IPE010200D","IPE110195D","IPE110200D","IPE120200D",
+ "IPE220200D","LFE020200D","LFE023200D","LFE025200D","LFE040190D","LFE040200D","LFE305200D",
+ "LFE330200D","LFE340200D","LFE350200D","LFE360200D","LFE370200D","LFE380200D","LFE390200D",
+ "LFE400200D","LFE410200D","LFE420200D","LFE430200D","LFE450200D","LND010200D","LND110200D",
+ "LND210200D","PIN020200D","POP050200D","POP060200D","POP110190D","POP110200D","POP120190D",
+ "POP120200D","POP165200D","POP225200D","POP255200D","POP285200D","POP325200D","POP405200D",
+ "POP600200D","POP610200D","POP645200D","PST180201D","PVY010199D","PVY020199D","PVY030199D",
+ "PVY615199D","PVY625199D","PVY645199D","PVY655199D","PVY675199D","PVY685199D","PVY740199D",
+ "PVY750199D","PVY810199D","PVY820199D","VST020200D","VST220200D","VST420200D","WAT130200D",
+ "WAT140200D","WAT420200D","WAT440200D")
> selection <- cou[,c("STCOU","Areaname",selVars)]
> write.csv(selection,file="selection.csv",row.names=FALSE)
> write.xls(selection,file="selection.xls",colNames=TRUE,rowNames=FALSE)
From the file selection.csv we determine the final set of variables
> setwd("D:/Data/counties/USAC")
> standard <- function(x) {s <- paste("0000",x,sep=""); substr(s,nchar(s)-4,nchar(s))}
> basic <- read.csv(file="selection.csv",stringsAsFactors=FALSE)
> basic$STCOU <- standard(basic$STCOU)
>
> orig <- c("AGE050200D","BZA115203D","CLF040200D","EDU635200D","EDU685200D","HSG045200D",
+ "HSG495200D","INC610199D","INC910199D","IPE010200D","IPE120200D","IPE220200D",
+ "LFE305200D","PIN020200D","POP050200D","POP060200D","POP165200D","POP225200D",
+ "POP255200D","POP285200D","POP325200D","POP405200D","POP645200D","VST020200D",
+ "VST220200D","VST420200D","WAT130200D")
>
> vars <- basic[,c("STCOU","Areaname",orig)]
>
> N <- data.frame(
+ STCOU=basic$STCOU,
+ P.pop.under5=100*basic$AGE110200D/basic$AGE010200D,
+ P.pop.under18=100*basic$AGE270200D/basic$AGE010200D,
+ P.pop.over85=100*basic$AGE880200D/basic$AGE010200D,
+ P.ind.fam.farms=100*basic$AGN030202D/basic$AGN020202D,
+ P.land.farms=basic$AGN050202D/(basic$LND110200D*6.4),
+ P.irrigatedLand=100*basic$AGN330202D/basic$AGN050202D,
+ P.violent.crime=100*basic$CRM110200D/basic$AGE010200D,
+ P.murders=100*basic$CRM140200D/basic$AGE010200D,
+ P.rapes=100*basic$CRM150200D/basic$AGE010200D,
+ P.16to19.notHighSc=100*basic$EDU920200D/basic$EDU910200D,
+ P.Hisp.Latin=100*basic$HIS020200D/basic$HIS010200D,
+ R.Hisp.Lat.maleFemale=basic$HIS040200D/basic$HIS030200D,
+ P.emply.ind.AGR.FOR.FISH.HUNT.MINING=100*basic$LFE330200D/basic$LFE020200D,
+ P.emply.ind.CONSTRUCTION=100*basic$LFE340200D/basic$LFE020200D,
+ P.emply.ind.MANUFACTORING=100*basic$LFE350200D/basic$LFE020200D,
+ P.emply.ind.WHOLESALEtrade=100*basic$LFE360200D/basic$LFE020200D,
+ P.emply.ind.RETAILtrade=100*basic$LFE370200D/basic$LFE020200D,
+ P.emply.ind.TRANSPORT.WAREHOUSING=100*basic$LFE380200D/basic$LFE020200D,
+ P.emply.ind.INFORMATION=100*basic$LFE390200D/basic$LFE020200D,
+ P.emply.ind.FINANC.INSUR=100*basic$LFE400200D/basic$LFE020200D,
+ P.emply.ind.PROFscientTECH=100*basic$LFE410200D/basic$LFE020200D,
+ P.emply.ind.EDUC.HEALTH=100*basic$LFE420200D/basic$LFE020200D,
+ P.emply.ind.ARTSaccomFOOD=100*basic$LFE430200D/basic$LFE020200D,
+ P.emply.ind.PUBLICaddmin=100*basic$LFE450200D/basic$LFE020200D,
+ P.25overLESS9thGRADE=100*basic$EDU610200D/basic$EDU600200D,
+ P.employ.FARMING=100*basic$EMN070201D/basic$LFE040200D,
+ P.employ.AGRIC.FOREST.FISH.HUNT=100*basic$EMN100201D/basic$LFE040200D,
+ F.GOV.EXP.perCapita=100*basic$FED110200D/basic$AGE010200D,
+ P.employ.GOV=100*basic$GEE020200D/basic$LFE040200D,
+ P.employ.GOV.stateLoc=100*basic$GEE320200D/basic$LFE040200D,
+ P.vacantHousingUnits=100*basic$HSG190200D/basic$HSG030200D,
+ P.occupiedHousingUnits=100*basic$HSG200200D/basic$HSG030200D,
+ P.occupiedHousingUnitsBLACK=100*basic$HSG215200D/basic$HSG030200D,
+ P.occupiedHousingUnitsHisLat=100*basic$HSG220200D/basic$HSG030200D,
+ P.OWNERoccupiedHousingUnits=100*basic$HSG440200D/basic$HSG030200D,
+ P.RENTERoccupiedHousingUnits=100*basic$HSG680200D/basic$HSG030200D,
+ P.occupiedHousingUnitsLackingPlumb=100*basic$HSG295200D/basic$HSG230200D,
+ P.URBANpopul=100*basic$POP110200D/basic$AGE010200D,
+ P.RURALpopul=100*basic$POP120200D/basic$AGE010200D,
+ P.CHANGErural90to00=100*(basic$POP120200D-basic$POP120190D)/basic$LFE040190D,
+ P.CHANGEurban90to00=100*(basic$POP110200D-basic$POP110190D)/basic$LFE040190D,
+ P.LAND=100*basic$LND110200D/basic$LND010200D,
+ P.WATER=100*basic$LND210200D/basic$LND010200D,
+ P.BELOWpovertyLevel=100*basic$PVY020199D/basic$PVY010199D,
+ P.ABOVEpovertyLevel=100*basic$PVY030199D/basic$PVY010199D,
+ P.WHITE.BELOWpovertyLevel=100*basic$PVY625199D/basic$PVY615199D,
+ P.aINDIAN.BELOWpovertyLevel=100*basic$PVY685199D/basic$PVY675199D,
+ P.BLACK.BELOWpovertyLevel=100*basic$PVY655199D/basic$PVY645199D,
+ P.ASIAN.BELOWpovertyLevel=100*basic$PVY750199D/basic$PVY740199D,
+ P.HisLat.BELOWpovertyLevel=100*basic$PVY820199D/basic$PVY810199D,
+ CHANGEperCapitaIncome89to99=basic$INC910199D-basic$INC910189D,
+ P.CHANGEemployIndustry90to00=100*(basic$EMS010200D-basic$EMS010190D)/basic$EMS010190D,
+ P.IrrigationGROUNDwaterUse=100*basic$WAT440200D/basic$WAT420200D,
+ GroundWaterUsePerCapita=basic$WAT140200D/basic$AGE010200D,
+ P.NET.DOMESTIC.MIGRATIONS=100*basic$PST180201D/basic$AGE010200D,
+ P.NativePopulationBornInStateOfRes=100*basic$POP610200D/basic$POP600200D,
+ R.LABOR.FORCEmaleFemale=basic$LFE023200D/basic$LFE025200D,
+ R.VOTING.DEMOCRATESoverREPUBLICANS=100*basic$ELE020200D/basic$ELE030200D,
+ P.PUBLIC.SCHOOL.ENROLNEMT=100*basic$EDU010201D/basic$AGE270200D,
+ TOTAL.DEPOSITSperCapita=basic$BNK050200D/basic$AGE010200D,
+ CROPvaluePerFARM=basic$AGN420202D/basic$AGN020202D,
+ LIFESTOCKvaluePerFARM=basic$AGN430202D/basic$AGN020202D,
+ P.CHANGEpverty95to00=100*(basic$IPE110200D-basic$IPE110195D)/basic$IPE110195D
+ )
> final <- merge(vars,N,by.x="STCOU",by.y="STCOU",all=TRUE)
> names(final)
[1] "STCOU" "Areaname"
[3] "AGE050200D" "BZA115203D"
[5] "CLF040200D" "EDU635200D"
[7] "EDU685200D" "HSG045200D"
.....
[25] "POP645200D" "VST020200D"
[27] "VST220200D" "VST420200D"
[29] "WAT130200D" "P.pop.under5"
[31] "P.pop.under18" "P.pop.over85"
[33] "P.ind.fam.farms" "P.land.farms"
[35] "P.irrigatedLand" "P.violent.crime"
.....
[87] "R.VOTING.DEMOCRATESoverREPUBLICANS" "P.PUBLIC.SCHOOL.ENROLNEMT"
[89] "TOTAL.DEPOSITSperCapita" "CROPvaluePerFARM"
[91] "LIFESTOCKvaluePerFARM" "P.CHANGEpverty95to00"
> write.csv(final,file="vars.csv",row.names=FALSE)
> library(xlsReadWritePro)
> write.xls(final,file="vars.xls",colNames=TRUE,rowNames=FALSE)
Now we have to align the variables data with the relational data - see details.