CODEWITHSUNDEEP
rjoindplyr
Ali
Ali

Reputation: 1080

Joining extra values in a data frame

I have a data frame as such:

df <- tibble(ID = c(1,1,1,2,2,2,3,3,4,5,5,5,5),
             Time = c(5,1,3,2,8,5,1,7,2,1,2,3,4),
             Output = c(1,2,3,4,5,6,7,8,9,10,11,12,13)) %>%
      arrange(ID, Time)

> df
# A tibble: 13 x 3
      ID  Time Output
   <dbl> <dbl>  <dbl>
 1     1     1      2
 2     1     3      3
 3     1     5      1
 4     2     2      4
 5     2     5      6
 6     2     8      5
 7     3     1      7
 8     3     7      8
 9     4     2      9
10     5     1     10
11     5     2     11
12     5     3     12
13     5     4     13

The data I have collected is not complete and any missing values need to be added. In my case I have Time = 1:10. If the Time is already recorded then leave the output as is. Otherwise the output should be 0. This needs to be done for each ID. Here is a sample of the output.

      ID  Time Ouput
   <dbl> <dbl> <dbl>
 1     1     1     2
 2     1     2     0
 3     1     3     3
 4     1     4     0
 5     1     5     1
 6     1     6     0
 7     1     7     0
 8     1     8     0
 9     1     9     0
10     1    10     0

Upvotes: 0

Views: 41

Answers (2)

akrun
akrun

Reputation: 887301

We can use expand with left_join

library(dplyr)
library(tidyr)
df %>%
    expand(ID, Time = 1:10) %>% 
    left_join(df)

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 389055

Use tidyr::complete : 

tidyr::complete(df, ID, Time = 1:10, fill = list(Output = 0))

# A tibble: 50 x 3
#      ID  Time Output
#   <dbl> <dbl>  <dbl>
# 1     1     1      2
# 2     1     2      0
# 3     1     3      3
# 4     1     4      0
# 5     1     5      1
# 6     1     6      0
# 7     1     7      0
# 8     1     8      0
# 9     1     9      0
#10     1    10      0
# … with 40 more rows

Upvotes: 1

Related Questions