CODEWITHSUNDEEP
rmissing-data
Ananta
Ananta

Reputation: 3711

filling in data for missing dates with previous week in R

I was working with tennis ranking data and noticed that for some dates there is no data. However, I wanted to fill in with the data from previous week.

consider a data structure 

structure(list(date2 = structure(c(16076, 16076, 16076, 16076, 
16076, 16076, 16076, 16076, 16076, 16076, 16083, 16083, 16083, 
16083, 16083, 16083, 16083, 16083, 16083, 16083, 16090, 16097, 
16097, 16097, 16097, 16097, 16097, 16097, 16097, 16097, 16097
), class = "Date"), rank = c(5L, 6L, 4L, 3L, 7L, 8L, 2L, 9L, 
10L, 1L, 5L, 6L, 7L, 8L, 9L, 10L, 1L, 2L, 3L, 4L, NA, 7L, 8L, 
9L, 10L, 4L, 5L, 6L, 1L, 2L, 3L), Name = c("Del Potro,Juan Martin", 
"Federer,Roger", "Murray,Andy", "Ferrer,David", "Berdych,Tomas", 
"Wawrinka,Stanislas", "Djokovic,Novak", "Gasquet,Richard", "Tsonga,Jo-Wilfried", 
"Nadal,Rafael", "Del Potro,Juan Martin", "Federer,Roger", "Berdych,Tomas", 
"Wawrinka,Stanislas", "Gasquet,Richard", "Tsonga,Jo-Wilfried", 
"Nadal,Rafael", "Djokovic,Novak", "Ferrer,David", "Murray,Andy", 
NA, "Berdych,Tomas", "Federer,Roger", "Gasquet,Richard", "Tsonga,Jo-Wilfried", 
"Del Potro,Juan Martin", "Ferrer,David", "Murray,Andy", "Nadal,Rafael", 
"Djokovic,Novak", "Wawrinka,Stanislas"), points = c(5255L, 4355L, 
5560L, 5800L, 4180L, 3890L, 12260L, 3140L, 3065L, 13130L, 5415L, 
4355L, 4180L, 3890L, 3140L, 3065L, 13130L, 12260L, 5640L, 5560L, 
NA, 4540L, 4355L, 3050L, 2885L, 5370L, 5280L, 4720L, 14330L, 
10620L, 5710L)), .Names = c("date2", "rank", "Name", "points"
), class = "data.frame", row.names = c(722066L, 722067L, 722078L, 
722106L, 722110L, 722111L, 722118L, 722139L, 722140L, 722143L, 
722330L, 722331L, 722332L, 722333L, 722334L, 722335L, 722406L, 
722407L, 722408L, 722409L, 722672L, 722677L, 722683L, 722684L, 
722689L, 722748L, 722749L, 722750L, 723098L, 723099L, 723100L))

I used this approach to fill, 

nadates<-temp85[which(is.na(temp85$rank)),"date2"]
fillindates=as.Date(nadates)-7
nadatesfilled=temp85[temp85$date2 %in% fillindates,]
nadatesfilled$date2<-as.Date(nadatesfilled$date2)+7
temp85filled<-rbind(na.omit(temp85),nadatesfilled)

It worked, however, my question is, is there any better way? Specially, considering in some cases where I have 2,3 weeks missing subsequently (not in this subset of data). I repeated the code to fill in those, but still, any better way?

Upvotes: 0

Views: 74

Answers (1)

Brian Diggs
Brian Diggs

Reputation: 58825

library("zoo")
temp85filled <- do.call(rbind, 
        lapply(na.omit(unique(temp85$rank)), function(rank) {
           na.locf(temp85[(is.na(temp85$rank) | temp85$rank == rank),])
}))

Bit trickier than it looks at first because there is a single row with a date that should be replaced with a whole set of rows corresponding to the previous date. To get around that, I did it for each rank separately, but for each rank, looked at that rank or a missing rank (which is why this is not a simple split-apply-combine problem). Within that, na.locf can be used to fill in the missing values (this does assume that the data is sorted by time first; if it is not, that can be done before passing it to na.locf within the lapply anonymous function). temp85fillled is not sorted by date, but it can be. (temp85filled <- temp85filled[order(temp85filled$date2),]).

Upvotes: 2

Related Questions