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)), ]
  • $\begingroup$ Welcome to the site. This is a question about R, so it belongs on StackOverflow rather than here. I have marked it for migration. $\endgroup$ – Peter Flom - Reinstate Monica Feb 21 '13 at 0:27
  • $\begingroup$ Thanks Peter. It crossed my mind not long after I posted that perhaps Cross Validated was specifically for stats questions and not stats software questions. I have re-posted the question over on Stack Overflow. $\endgroup$ – imallhere Feb 21 '13 at 3:46
  • 1
    $\begingroup$ You should not cross-post. Reading the FAQ would have let you know what's on topic here and not to cross-post. If you wanted this moved there, you would flag it and a moderator would shift it. Likely one of the two will be deleted now ... probably this one. If I am lucky, it will get moved and then combined. $\endgroup$ – Glen_b -Reinstate Monica Feb 21 '13 at 3:56

There are some pre-existing functions that will do exponential smoothing (exponentially weighted moving averages), such as the HoltWinters function in the default distribution of R - see the last example there. Several other packages are available that do EWMA calculations. As a first step check the Time Series task view.

Most are likely to be reasonably efficiently implemented.

  • $\begingroup$ Thanks Glen. I will take a look at HoltWinters and Time Series. I appreciate the input. $\endgroup$ – imallhere Feb 21 '13 at 3:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.