Add rows with value=0 to have the same number of rows by group dplyr R

0
votes

I have a dataframe with groups by country and rows with date, in some countries there are no rows for some dates, I would like to fill these missing rows with a 0 value

For example:

dd <- tibble( region=rep("Aus",8), date=c("2000-11-01","2000-12-01","2001-01-01","2001-02-01","2001-03-01","2001-04-01","2001-05-01","2001-06-01"), value=runif(8))
dd <- bind_rows(dd,tibble( region=rep("Est",6), date=c("2000-11-01","2001-01-01","2001-02-01","2001-04-01","2001-05-01","2001-06-01"), value=runif(6)))

I need to add two rows for the group Est with missing dates 2000-12-01 2001-03-01 with value=0

I can't figure out how to make this for a general case using dplyr or base

1
rdplyr

1 Answers

1
votes
dd %>%
  pivot_wider(names_from = date, values_fill = 0) %>%
  pivot_longer(cols = -region)

which gives:

# A tibble: 16 x 3
   region name        value
   <chr>  <chr>       <dbl>
 1 Aus    2000-11-01 0.313 
 2 Aus    2000-12-01 0.842 
 3 Aus    2001-01-01 0.314 
 4 Aus    2001-02-01 0.757 
 5 Aus    2001-03-01 0.968 
 6 Aus    2001-04-01 0.169 
 7 Aus    2001-05-01 0.697 
 8 Aus    2001-06-01 0.873 
 9 Est    2000-11-01 0.132 
10 Est    2000-12-01 0     
11 Est    2001-01-01 0.0785
12 Est    2001-02-01 0.378 
13 Est    2001-03-01 0     
14 Est    2001-04-01 0.580 
15 Est    2001-05-01 0.536 
16 Est    2001-06-01 0.109