2

I want to combine data frames in long format with different length because of the time variable (imbalanced panel data):

set.seed(63)
#function to create a data frame that includes id, time and x
func1 <- function (size=5) {
             x=sample(c(0,1), 1)
             data.frame(time=1:size, x=x)}
    
#function to row combine data frames
func2 <- do.call("rbind", Map(function(x,y) {
             data.frame(id=x, func1(y))
             }, 1:5, 5))
    
#Sample 10 observations to create imbalanced panel data
dd <- func2[sample(nrow(func2), 10), ]  
fd <- dd[with(dd, order(id, time)),]
> fd
   id time x
1   1    1 0
2   1    2 0
3   1    3 0
4   1    4 0
5   1    5 0
10  2    5 1
13  3    3 0
17  4    2 0
18  4    3 0
21  5    1 0

In the end, I want to convert it to wide format and and fill with NA the skipped cells of x based on the time variable . Something like this:

id    x.time1 x.time2 x.time3 x.time4 x.time5 
1       0       0       0       0       0
2       NA      NA      NA      NA      1
3       NA      NA      0       NA      NA
4       NA      0       0       NA      NA
5       0       NA      NA      NA      NA
cliu
  • 933
  • 6
  • 13

2 Answers2

1

A possible solution:

library(tidyverse)

fd <- data.frame(
          id = c(1L, 1L, 1L, 1L, 1L, 2L, 3L, 4L, 4L, 5L),
        time = c(1L, 2L, 3L, 4L, 5L, 5L, 3L, 2L, 3L, 1L),
           x = c(0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L)
)

fd %>% 
  pivot_wider(id, names_from = time, values_from = x, names_prefix = "x.time")

#> # A tibble: 5 × 6
#>      id x.time1 x.time2 x.time3 x.time4 x.time5
#>   <int>   <int>   <int>   <int>   <int>   <int>
#> 1     1       0       0       0       0       0
#> 2     2      NA      NA      NA      NA       1
#> 3     3      NA      NA       0      NA      NA
#> 4     4      NA       0       0      NA      NA
#> 5     5       0      NA      NA      NA      NA
PaulS
  • 21,159
  • 2
  • 9
  • 26
  • 1
    Thank you @Paul! I upvoted your answer but accepted the `data.table` solution by akrun because supposedly it is faster than `tidyverse` – cliu Nov 26 '21 at 20:21
  • 1
    You are welcome, @cliu! And no problem: I am here to learn and not to get StackOverflow reputation! ;-) – PaulS Nov 26 '21 at 20:23
1

Using data.table

library(data.table)
dcast(setDT(fd), id ~ paste0('x.time', time), value.var = 'x')

-output

   id x.time1 x.time2 x.time3 x.time4 x.time5
1:  1       0       0       0       0       0
2:  2      NA      NA      NA      NA       1
3:  3      NA      NA       0      NA      NA
4:  4      NA       0       0      NA      NA
5:  5       0      NA      NA      NA      NA
Wai Ha Lee
  • 8,598
  • 83
  • 57
  • 92
akrun
  • 874,273
  • 37
  • 540
  • 662