tempbystate.r

# ftp://ftp.ncdc.noaa.gov/pub/data/cirs/climdiv/climdiv-tmpcst-v1.0.0-20160605
# ftp://ftp.ncdc.noaa.gov/pub/data/cirs/climdiv/state-readme.txt

dat = read.table("climdiv-tmpcst-v1.0.0-20160605.csv", colClasses = "character")
splitel <- function(x) {
  statecode = substr(x,1,3)
  divnum = substr(x,4,4)
  elcode = substr(x,5,6)
  year = substr(x,7,10)
  return(c(statecode, divnum, elcode, year))
}

c1sp = lapply(dat[,1], splitel)
c1sp = data.frame(matrix(unlist(c1sp), nrow=length(c1sp), byrow=T),stringsAsFactors=FALSE)
dat = cbind(dat, c1sp)
colnames(dat) <- gsub("V","", names(dat))
library(reshape2)
dat_long = melt(dat, id.vars=c(names(dat)[1], names(dat)[14:17]), direction="long")
dat_long$variable = as.numeric(as.character(dat_long$variable)) - 1
colnames(dat_long) = c("col1", "statecode", "divnum", "elcode", "year", "month", "temp")

statecodelegend = c(
"001"="Alabama",
"030"="New York",
"002"="Arizona",
"031"="North Carolina",
"003"="Arkansas",
"032"="North Dakota",
"004"="California",
"033"="Ohio",
"005"="Colorado",
"034"="Oklahoma",
"006"="Connecticut",
"035"="Oregon",
"007"="Delaware",
"036"="Pennsylvania",
"008"="Florida",
"037"="Rhode Island",
"009"="Georgia",
"038"="South Carolina",
"010"="Idaho",
"039"="South Dakota",
"011"="Illinois",
"040"="Tennessee",
"012"="Indiana",
"041"="Texas",
"013"="Iowa",
"042"="Utah",
"014"="Kansas",
"043"="Vermont",
"015"="Kentucky",
"044"="Virginia",
"016"="Louisiana",
"045"="Washington",
"017"="Maine",
"046"="West Virginia",
"018"="Maryland",
"047"="Wisconsin",
"019"="Massachusetts",
"048"="Wyoming",
"020"="Michigan",
"050"="Alaska",
"021"="Minnesota",
"022"="Mississippi",
"023"="Missouri",
"024"="Montana",
"025"="Nebraska",
"026"="Nevada",
"027"="New Hampshire",
"028"="New Jersey",
"029"="New Mexico",
"049"="Hawaii"
)

stfind <- function(x) {
  r = "Other"
  if(x %in% names(statecodelegend)) {
    r = statecodelegend[[x]]
  }
  return(r)
}
statenames = sapply(dat$X1,function(x) stfind(x))

dat_long$statename = statenames