Adding rows to the data frame in R-CodePudding

I have a dataframe like below.I want add rows for each name with year and missing week of the year with a total value of 0.

Name Year Week Total

    John 2021 1 3
    John 2021 2 2
    John 2021 5 1
    John 2021 10 2
    Mary 2020 3 1
    Mary 2021 5 2

Expected result

John 2021 1 3
John 2021 2 2
John 2021 3 0
John 2021 4 0
John 2021 5 1
John 2021 6 0
.
.
.
John 2021 53 0

This is what I am trying to do

data1<-data %>% 
  complete(Week = seq(min(Week), max(Week), by = 'week')) %>%
  mutate_each(funs(ifelse(is.na(.),0,.)))

CodePudding user response：

You were on the right track:

data %>%
  complete(Week = 1:53, Name, fill=list(Total=0))

Gives this:

   Week Name   Year Total
   <int> <chr> <int> <dbl>
 1     1 John   2021     3
 2     2 John   2021     2
 3     3 John     NA     0
 4     4 John     NA     0
 5     5 John   2021     1
 6     6 John     NA     0
 7     7 John     NA     0
 8     8 John     NA     0
 9     9 John     NA     0
10    10 John   2021     2
# ... with 96 more rows

You can use fill(Year) to replace NAs of the column Year by the previous non-NA value. If you also want to do it for multiple years, then you can group_by Year, and it will automatically fill the Year value.

CodePudding user response：

I think this is what you're asking for:

x1  %>% 
  group_by(Name) %>%
  complete(Week = seq(min(Week), max(Week), by = 1), fill = list(Total = 0))