How do I fill the NA to next row in R?

Question

I would like to fill NA to next row. Here is the data-set.

structure(list(timestamp = structure(c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L), .Label = c("2019-07-07 00:00:00", "2019-07-07 00:00:01", "2019-07-07 00:00:02", "2019-07-07 00:00:03", "2019-07-07 00:00:04", "2019-07-07 00:00:05", "2019-07-07 00:00:06", "2019-07-07 00:00:07", "2019-07-07 00:00:08", "2019-07-07 00:00:09", "2019-07-07 00:00:10" ), class = "factor"), source = structure(c(NA, NA, NA, 1L, NA, NA, 1L, NA, NA, NA, NA, NA, 2L, NA, 2L, NA, NA, 2L, NA, NA, 2L, NA), .Label = c("USER_A", "USER_B"), class = "factor"), value = c(NA, NA, NA, 1L, NA, NA, 1L, NA, NA, NA, NA, NA, 1L, NA, 1L, NA, NA, 2L, NA, NA, 3L, NA)), class = "data.frame", row.names = c(NA, -22L))

             timestamp source value
1  2019-07-07 00:00:00   <NA>    NA
2  2019-07-07 00:00:01   <NA>    NA
3  2019-07-07 00:00:02   <NA>    NA
4  2019-07-07 00:00:03 USER_A     1
5  2019-07-07 00:00:04   <NA>    NA
6  2019-07-07 00:00:05   <NA>    NA
7  2019-07-07 00:00:06 USER_A     1
8  2019-07-07 00:00:07   <NA>    NA
9  2019-07-07 00:00:08   <NA>    NA
10 2019-07-07 00:00:09   <NA>    NA
11 2019-07-07 00:00:10   <NA>    NA
12 2019-07-07 00:00:00   <NA>    NA
13 2019-07-07 00:00:01 USER_B     1
14 2019-07-07 00:00:02   <NA>    NA
15 2019-07-07 00:00:03 USER_B     1
16 2019-07-07 00:00:04   <NA>    NA
17 2019-07-07 00:00:05   <NA>    NA
18 2019-07-07 00:00:06 USER_B     2
19 2019-07-07 00:00:07   <NA>    NA
20 2019-07-07 00:00:08   <NA>    NA
21 2019-07-07 00:00:09 USER_B     3
22 2019-07-07 00:00:10   <NA>    NA

The table is kinds of loop between time and source. There are fixed rows (00:00:00 to 00:00:10 in this case) for each source (A and B).

Here is the expected result table.

             timestamp source value
1  2019-07-07 00:00:00   <NA>    NA
2  2019-07-07 00:00:01   <NA>    NA
3  2019-07-07 00:00:02   <NA>    NA
4  2019-07-07 00:00:03 USER_A     1
5  2019-07-07 00:00:04 USER_A     1
6  2019-07-07 00:00:05 USER_A     1
7  2019-07-07 00:00:06 USER_A     1
8  2019-07-07 00:00:07   <NA>    NA
9  2019-07-07 00:00:08   <NA>    NA
10 2019-07-07 00:00:09   <NA>    NA
11 2019-07-07 00:00:10   <NA>    NA
12 2019-07-07 00:00:00   <NA>    NA
13 2019-07-07 00:00:01 USER_B     1
14 2019-07-07 00:00:02 USER_B     1
15 2019-07-07 00:00:03 USER_B     1
16 2019-07-07 00:00:04 USER_B     2
17 2019-07-07 00:00:05 USER_B     2
18 2019-07-07 00:00:06 USER_B     2
19 2019-07-07 00:00:07 USER_B     3
20 2019-07-07 00:00:08 USER_B     3
21 2019-07-07 00:00:09 USER_B     3
22 2019-07-07 00:00:10   <NA>    NA

The value and source of 5 and 6 row is replaced by those of 7 row based on USER_A. USER_B rows are also replaced with same way based on next row.

How do I make this processes in R?

Ronak Shah Ronak Shah · Accepted Answer · 2019-07-17T04:47:50

Here is one way to do it using dplyr since there are fixed number of rows for each source. We first create a group for every n rows and add a new column group2 which has 1 only between min and max index of non-NA values in the group. We then group_by group2 as well to fill the missing values by previous non-missing values by group.

n <- 11
library(dplyr)  

df %>%
  group_by(group1 = gl(n()/n, n)) %>%
  mutate(group2 = 0, 
         group2 = replace(group2, min(which(!is.na(source))) : 
                                  max(which(!is.na(source))), 1)) %>%
  group_by(group2) %>%
  tidyr::fill(source, value) %>% 
  ungroup() %>%
  select(-group1, -group2) 

# A tibble: 22 x 3
#   timestamp           source value
#   <fct>               <fct>  <int>
# 1 2019-07-07 00:00:00 NA        NA
# 2 2019-07-07 00:00:01 NA        NA
# 3 2019-07-07 00:00:02 NA        NA
# 4 2019-07-07 00:00:03 USER_A     1
# 5 2019-07-07 00:00:04 USER_A     1
# 6 2019-07-07 00:00:05 USER_A     1
# 7 2019-07-07 00:00:06 USER_A     1
# 8 2019-07-07 00:00:07 NA        NA
# 9 2019-07-07 00:00:08 NA        NA
#10 2019-07-07 00:00:09 NA        NA
# … with 12 more rows

How do I fill the NA to next row in R?

1 Answers