OK so I have a working code to do what I want but I am new to R and feel like my solution is very clunky and there is likely a more efficient way to get the same result.
I have a set of data that contain records with a date, city, and quality score. For each city and each date, I want to create an exponentially weighted-average of that city's prior quality scores. (The first date for each city has no previous data, so it will always be NA.)
My solution expands the original dataframe and calculates the means in a single pass. It does work but I view it as a bit inefficient and expanding the dataframe could be problematic with large data.
As I am new to R, I would love to hear other approaches that get the same end result.
Here is my code:
#load in the data
mydata <- structure(list(date = structure(c(15513, 15507, 15476, 15439, 15442, 15435, 15419, 15410, 15508, 15464, 15461), class = "Date"), city = structure(c(2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 1L, 1L, 1L), .Label = c("MIA", "PHX", "POR"), class = "factor"), quality = c(3.5, 6.7, 5.2, 3.9, 2.6, 7.7, 2.4, 4.7, 3.5, 2.6, 1.8)), .Names = c("date", "city", "quality"), row.names = c(NA, -11L), class = "data.frame")
#fix the date data
mydata$date <- as.Date(mydata$date, "%d%b%Y")
#create a unique id for each record
mydata$id <- paste(mydata$city, as.numeric(mydata$date), sep='_')
#expand the data
mydata_expanded <- expand.grid(x=mydata$id, y=mydata$id)
mydata_expanded$cityX <- substr(mydata_expanded$x,start=1,stop=3)
mydata_expanded$cityY <- substr(mydata_expanded$y,start=1,stop=3)
mydata_expanded <- mydata_expanded[mydata_expanded$cityX==mydata_expanded$cityY,]
mydata_expanded$dateX <- substr(mydata_expanded$x,start=5,stop=10)
mydata_expanded$dateY <- substr(mydata_expanded$y,start=5,stop=10)
mydata_expanded <- merge(mydata_expanded, mydata, by.x=c("y"), by.y=c("id"))
mydata_expanded$dateX <- as.Date(as.numeric(mydata_expanded$dateX), origin="1970-01-01")
mydata_expanded$dateY <- as.Date(as.numeric(mydata_expanded$dateY), origin="1970-01-01")
mydata_expanded <- mydata_expanded[mydata_expanded[,5]>mydata_expanded[,6],]
mydata_expanded <- mydata_expanded[order(-as.numeric(mydata_expanded$dateX)),]
#create the variables needed to calculate the exponentially weighted-means
mydata_expanded$dayssince <- mydata_expanded$dateX - mydata_expanded$date
mydata_expanded$w <- exp(-as.numeric(mydata_expanded$dayssince)/60)
mydata_expanded$wQuality <- mydata_expanded$quality * mydata_expanded$w
sum_w <- aggregate(mydata_expanded$w,by=data.frame(mydata_expanded$dateX, mydata_expanded$city),sum,na.rm=T);
sum_wQuality <- aggregate(mydata_expanded$wQuality,by=data.frame(mydata_expanded$dateX, mydata_expanded$city),sum,na.rm=T)
mydata_quality <- merge(sum_w,sum_wQuality,by=c("mydata_expanded.dateX", "mydata_expanded.city"))
#calculate the actual exponentially weighted-mean
mydata_quality$ewQuality <- mydata_quality$x.y/mydata_quality$x.x
#merge the data back to the original dataframe and cleanup
mydata <- merge(mydata,mydata_quality, by.x=c("date","city"), by.y=c("mydata_expanded.dateX","mydata_expanded.city"),all.x=TRUE)
mydata$id <- NULL
mydata$x.x <- NULL
mydata$x.y <- NULL
mydata$ewQuality <- signif(mydata$ewQuality,digits=4)
mydata <- mydata[with(mydata, order(city)), ]
