Add rows with specific value and replace missing values with zero in R

Question

This is my data frame:

year  year_month month distance weeksum  
  <chr> <chr>      <chr> <fct>      <dbl> 
1 2017  2017_05    05    15             4 
2 2017  2017_05    05    10             1 
3 2017  2017_05    05    5              5 
4 2017  2017_05    05    0              1  
5 2017  2017_06    06    20             7 
6 2017  2017_06    06    0             17  
7 2017  2017_07    07    10             8  
8 2017  2017_07    07    5              3  
9 2017  2017_07    07    0             20  
10 2017  2017_08    08    20             3  

I want to add rows so that I get the full range of my distances (0,5,10,15,20) for each month in my data frame and a 0 for the weeksum like this:

 year  year_month month distance weeksum  
   <chr> <chr>      <chr> <fct>      <dbl> 
 1 2017  2017_05    05    20             0 
 2 2017  2017_05    05    15             4 
 3 2017  2017_05    05    10             1 
 4 2017  2017_05    05    5              5 
 5 2017  2017_05    05    0              1  
 6 2017  2017_06    06    20             7 
 7 2017  2017_06    06    15             0 
 8 2017  2017_06    06    10             0 
 9 2017  2017_06    06    5              0 
10 2017  2017_06    06    0             17  
11 2017  2017_07    07    20             0 
12 2017  2017_07    07    15             0 
13 2017  2017_07    07    10             8  
14 2017  2017_07    07    5              3  
15 2017  2017_07    07    0             20  

I've been trying to use the padr package but didn't get what I wanted to.

Answer 1

You can use complete from tidyr, i.e.

library(tidyverse)
complete(df, distance, nesting(year, year_month, month), fill = list(weeksum = 0)) %>% 
    arrange(year_month)

which gives,

# A tibble: 20 x 5
   distance  year year_month month weeksum
      <int> <int> <fct>      <int>   <dbl>
 1        0  2017 2017_05        5       1
 2        5  2017 2017_05        5       5
 3       10  2017 2017_05        5       1
 4       15  2017 2017_05        5       4
 5       20  2017 2017_05        5       0
 6        0  2017 2017_06        6      17
 7        5  2017 2017_06        6       0
 8       10  2017 2017_06        6       0
 9       15  2017 2017_06        6       0
10       20  2017 2017_06        6       7
11        0  2017 2017_07        7      20
12        5  2017 2017_07        7       3
13       10  2017 2017_07        7       8
14       15  2017 2017_07        7       0
15       20  2017 2017_07        7       0
16        0  2017 2017_08        8       0
17        5  2017 2017_08        8       0
18       10  2017 2017_08        8       0
19       15  2017 2017_08        8       0
20       20  2017 2017_08        8       3