Append missing dates for each combination excluding result column

Question

How to append dates with respect to few columns grouped. Please find expected output and current 1 below

Although these are similar questions, grouping considering these columns is different in this case:

R / tidyr::complete - filling missing values dynamically

Library (tidyverse)
sample_data <- tribble (~A, ~B, ~C, ~ Date, ~ Result,
     "AL",123,"12", as.Date ("2014-02-01"), 12345,
     "AL",123,"12", as.Date ("2014-04-01"), 12349,
     "AL",123,"12", as.Date ("2014-06-01"), 12977,
     "AZ",123,"12", as.Date ("2014-01-01"),23435,
     "AZ",123,"12", as.Date ("2014-04-01"),453454,
     "AZ",123,"12", as.Date ("2014-07-01"),123976)

sample_data %<>% complete (Date = seq.Date (min (Date), max (Date), by="month")
# Output
> sample_data
# A tibble: 8 x 5
  Date       A         B C     Result
  <date>     <chr> <dbl> <chr>  <dbl>
1 2014-01-01 AZ      123 12     23435
2 2014-02-01 AL      123 12     12345
3 2014-03-01 NA       NA NA        NA
4 2014-04-01 AL      123 12     12349
5 2014-04-01 AZ      123 12    453454
6 2014-05-01 NA       NA NA        NA
7 2014-06-01 AL      123 12     12977
8 2014-07-01 AZ      123 12    123976

# Tried but
sample_data %>%
  group_by (A, B, C) %>%
  mutate (tidyr:: complete (Date = seq.Date (min (Date), max (Date), by="month")

# Expected output
expected_output <-tribble (~A, ~B, ~C, ~ Date, ~ Result,
     "AL",123,"12", as.Date ("2014-01-01"), NA,
     "AL",123,"12", as.Date ("2014-02-01"), 12345,
     "AL",123,"12", as.Date ("2014-03-01"), NA,
     "AL",123,"12", as.Date ("2014-04-01"), 12349,
     "AL",123,"12", as.Date ("2014-05-01"), NA,
     "AL",123,"12", as.Date ("2014-06-01"), 12977,
     "AL",123,"12", as.Date ("2014-07-01"), NA,
     "AZ",123,"12", as.Date ("2014-01-01"),23435,
     "AZ",123,"12", as.Date ("2014-02-01"),NA,
     "AZ",123,"12", as.Date ("2014-03-01"),NA,
     "AZ",123,"12", as.Date ("2014-04-01"),453454,
     "AZ",123,"12", as.Date ("2014-05-01"),NA,
     "AZ",123,"12", as.Date ("2014-06-01"),NA,
     "AZ",123,"12", as.Date ("2014-07-01"),123976)

Duplicate of [R - Fill missing dates by group](https://stackoverflow.com/questions/48633460/r-fill-missing-dates-by-group) and [Filling missing dates in a grouped time series - a tidyverse-way?](https://stackoverflow.com/questions/46130246/filling-missing-dates-in-a-grouped-time-series-a-tidyverse-way) — M--, Jul 10 '19 at 14:49

score 1 · Accepted Answer · answered Jul 10 '19 at 14:28

An option would be to use group_by and use the min and max from the whole 'Date' column instead of the min and max of each group

library(dplyr)
library(tidyr)
sample_data %>% 
   group_by(A, B, C) %>% 
   complete(Date = seq.Date(min(.$Date), max(.$Date), by="month"))
# A tibble: 14 x 5
# Groups:   A, B, C [2]
#   A         B C     Date       Result
#   <chr> <dbl> <chr> <date>      <dbl>
# 1 AL      123 12    2014-01-01     NA
# 2 AL      123 12    2014-02-01  12345
# 3 AL      123 12    2014-03-01     NA
# 4 AL      123 12    2014-04-01  12349
# 5 AL      123 12    2014-05-01     NA
# 6 AL      123 12    2014-06-01  12977
# 7 AL      123 12    2014-07-01     NA
# 8 AZ      123 12    2014-01-01  23435
# 9 AZ      123 12    2014-02-01     NA
#10 AZ      123 12    2014-03-01     NA
#11 AZ      123 12    2014-04-01 453454
#12 AZ      123 12    2014-05-01     NA
#13 AZ      123 12    2014-06-01     NA
#14 AZ      123 12    2014-07-01 123976

Append missing dates for each combination excluding result column

1 Answers1