Add missing rows per group in R

Question

Dataset:

original <- data.frame(
            type = c(1,1,1,1,2,2,2,2),
            day = as.POSIXct(c("01-01-2000 00:00:00",
                               "01-01-2000 00:01:00",
                               "01-01-2000 00:02:00",
                               "01-01-2000 00:04:00",
                               "01-01-2000 12:00:00",
                               "01-01-2000 12:01:00",
                               "01-01-2000 12:02:00",
                               "01-01-2000 12:04:00"), format="%m-%d-%Y %H:%M:%S"),
            value = c(4, 3, 1, 1, 3, 5, 6, 3))

I have a data frame like this

  type                 day value
1    1 2000-01-01 00:00:00     4
2    1 2000-01-01 00:01:00     3
3    1 2000-01-01 00:02:00     1
4    1 2000-01-01 00:04:00     1
5    2 2000-01-01 12:00:00     3
6    2 2000-01-01 12:01:00     5
7    2 2000-01-01 12:02:00     6
8    2 2000-01-01 12:04:00     3

I want to fill the missing minute level data with value = 0, within each type

Hence, expected output will be

  type                 day value
1    1 2000-01-01 00:00:00     4
2    1 2000-01-01 00:01:00     3
3    1 2000-01-01 00:02:00     1
4    1 2000-01-01 00:03:00     0
5    1 2000-01-01 00:04:00     1
6    2 2000-01-01 12:00:00     3
7    2 2000-01-01 12:01:00     5
8    2 2000-01-01 12:02:00     6
9    2 2000-01-01 12:03:00     0
10    2 2000-01-01 12:04:00    3

I can solve this using padr, however I am looking for datatable solution. Is it possible for each group that is type?

You may need `complete` from `tidyr` `original %>% group_by(type) %>% complete(day = seq(first(day), last(day), by = "1 min"), fill = list(value = 0))` — akrun, Feb 21 '19 at 12:19

score 2 · Accepted Answer · answered Feb 21 '19 at 12:23

Using data.table, we can do a join after expanding the original dataset

new <- setDT(original)[, .(day = seq(first(day), last(day), by = "1 min"), value = 0),
  by =  type]
new[original, value := i.value, on = .(type, day)][]
#    type                 day value
# 1:    1 2000-01-01 00:00:00     4
# 2:    1 2000-01-01 00:01:00     3
# 3:    1 2000-01-01 00:02:00     1
# 4:    1 2000-01-01 00:03:00     0
# 5:    1 2000-01-01 00:04:00     1
# 6:    2 2000-01-01 12:00:00     3
# 7:    2 2000-01-01 12:01:00     5
# 8:    2 2000-01-01 12:02:00     6
# 9:    2 2000-01-01 12:03:00     0
#10:    2 2000-01-01 12:04:00     3

Or using tidyverse

library(tidyverse)
original %>%
   group_by(type) %>% 
   complete(day = seq(first(day), last(day), by = "1 min"), fill = list(value = 0))

Add missing rows per group in R

1 Answers1