price imputation

disspriceimpute

#Impute price data
prices <- read.csv("~/Documents/CEX0711/prices.csv")
colnames(prices) <- sub("[.]", " ", colnames(prices))
prices <- melt(prices)
prices$Date <- as.yearmon(prices$Date,format="%b-%Y")
colnames(prices) <- c('time','statename','price')
prices$year <- format(prices$time,format="%Y")

statetax <- read.csv("~/Documents/CEX0711/statetax.csv")
colnames(statetax)[1] <- "statename"
colnames(statetax) <- sub("[X]", "", colnames(statetax))
statetax <- melt(statetax)
colnames(statetax) <- c('statename','year','tax')

pricetax <- merge(statetax,prices)
pricetax$federaltax <- rep(18.4,length(pricetax$tax))
pricetax$totaltax <- (pricetax$tax + pricetax$federaltax)/100
pricetax$nprc <- pricetax$price + pricetax$totaltax

pricemerge <- data.frame("statename"=pricetax$statename,"time"=pricetax$time,"nprc"=pricetax$nprc)
pricemerge$time <- as.yearmon(pricemerge$time,format="%b %Y")
pricemerge1 <- unique(data[,c('statename','time')])
pricemerge2 <- pricemerge
pricemerge <- merge(pricemerge1,pricemerge2,all=T)
pricemerge <- pricemerge[order(pricemerge$statename, pricemerge$time),]

quarterCalc <- function(interviewTime, interviewState, priceData){
  if(!inherits(interviewTime, "yearmon")){
    stop("Wrong date type")
  }
  newData <- subset(priceData, as.character(priceData$statename)==as.character(interviewState))
if(dim(subset(priceData, time==interviewTime & as.character(statename)==as.character(interviewState)))[1] > 1){return(NA)}
index = which(newData$time == interviewTime, arr.ind=TRUE)
if(length(index)>1){ stop(print(index))}
else if(index>=3){
    quarterly <- data.frame("time" = interviewTime, "state" = interviewState, "price" = mean(newData$nprc[seq((index-3), (index-1), 1)], na.rm=TRUE))
  }else{
    warning("Missing data. There are less than three prior months")
	return(NA)
  }
  return(quarterly$price[1])
}

nprc <- vector(mode="numeric",length=length(data$cuid))
for(i in 1:nrow(data)){nprc[i] <- quarterCalc(data$time[i], data$statename[i], pricemerge)}

data$nprc <- nprc
data <- data[is.na(data$nprc)==F,]

rm(prices)
rm(statetax)
rm(pricetax)
rm(pricemerge)
rm(pricemerge1)
rm(pricemerge2)