CODEWITHSUNDEEP
rtime-series
caas
caas

Reputation: 455

Data frame "expand" procedure in R?

This is not a real statistical question, but rather a data preparation question before performing the actual statistical analysis. I have a data frame which consists of sparse data. I would like to "expand" this data to include zeroes for missing values, group by group.

Here is an example of the data (a and b are two factors defining the group, t is the sparse timestamp and xis the value):

test <- data.frame(
    a=c(1,1,1,1,1,1,1,1,1,1,1),
    b=c(1,1,1,1,1,2,2,2,2,2,2),
    t=c(0,2,3,4,7,3,4,6,7,8,9),
    x=c(1,2,1,2,2,1,1,2,1,1,3))

Assuming I would like to expand the values between t=0 and t=9, this is the result I'm hoping for:

test.expanded <- data.frame(
    a=c(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1),
    b=c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2),
    t=c(0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9),
    x=c(1,0,2,1,2,0,0,2,0,0,0,0,0,1,1,0,2,1,1,3))

Zeroes have been inserted for all missing values of t. This makes it easier to use.

I have a quick and dirty implementation which sorts the dataframe and loops through each of its lines, adding missing lines one at a time. But I'm not entirely satisfied by the solution. Is there a better way to do it?

For those who are familiar with SAS, it is similar to the proc expand.

Thanks!

Upvotes: 2

Views: 967

Answers (2)

Ari B. Friedman
Ari B. Friedman

Reputation: 72769

This is convoluted but works fine:

test <- data.frame(
   a=c(1,1,1,1,1,1,1,1,1,1,1),
   b=c(1,1,1,1,1,2,2,2,2,2,2),
   t=c(0,2,3,4,7,3,4,6,7,8,9),
  x=c(1,2,1,2,2,1,1,2,1,1,3))
 
my.seq <- seq(0,9)
not.t <- !(my.seq %in% test$t)
test[nrow(test)+seq(length(my.seq[not.t])),"t"] <- my.seq[not.t]
test
#------------
    a  b t  x
1   1  1 0  1
2   1  1 2  2
3   1  1 3  1
4   1  1 4  2
5   1  1 7  2
6   1  2 3  1
7   1  2 4  1
8   1  2 6  2
9   1  2 7  1
10  1  2 8  1
11  1  2 9  3
12 NA NA 1 NA
13 NA NA 5 NA

Not sure if you want it sorted by t afterwards or not. If so, easy enough to do:

https://stackoverflow.com/a/6871968/636656

Upvotes: 1

Brian Diggs
Brian Diggs

Reputation: 58845

As you noted in a comment to the other answer, doing it by group is easy with plyr which just leaves how to "fill in" the data sets. My approach is to use merge.

library("plyr")

test.expanded <- ddply(test, c("a","b"), function(DF) {
  DF <- merge(data.frame(t=0:9), DF[,c("t","x")], all.x=TRUE)
  DF[is.na(DF$x),"x"] <- 0
  DF
})

merge with all.x=TRUE will make the missing values NA, so the second line of the function is needed to replace those NAs with 0's.

Upvotes: 4

Related Questions