Cross Validated is a question and answer site for people interested in statistics, machine learning, data analysis, data mining, and data visualization. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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", ""))

#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",""),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)), ]
share|improve this question

closed as off topic by Peter Flom, gung, Peter Ellis, Glen_b, whuber Feb 21 '13 at 8:22

Questions on Cross Validated are expected to relate to statistics within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

Welcome to the site. This is a question about R, so it belongs on StackOverflow rather than here. I have marked it for migration. – Peter Flom Feb 21 '13 at 0:27
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. – imallhere Feb 21 '13 at 3:46
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. – Glen_b 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.

share|improve this answer
Thanks Glen. I will take a look at HoltWinters and Time Series. I appreciate the input. – imallhere Feb 21 '13 at 3:54

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