CODEWITHSUNDEEP
rtidyverse
arteteco
arteteco

Reputation: 79

Create rows to fill missing values grouping by date

Let's say I have such a data frame.

Here there are people, weeks and a number which is cumulative for the weeks passing by.

    df <- tribble(
  ~week, ~author, ~n,
  "W1",  "John", 1,
  "W1",  "Jack", 2,
  "W2",  "John", 2,
  "W2",  "Annie", 3,
  "W2",  "Peter", 1,
  "W3",  "Annie", 4,
  "W3",  "John",  3,
  "W3",  "Jack", 3
)

Now, what I'd like to do is group_by week, and have all the authors (Jack, John, Annie and Peter) for every week, keeping the number of last week in case there was no add up.

I can use complete() or fill(),to keep the number from last week, after sort(df, week, author), I guess, but I don't understand how to add authors in case that week they haven't produced anything.

This is the result I'd like to see

df <- tribble(
  ~week, ~author, ~n,
  W1,  "John", 1,
  W1,  "Jack", 2, 
  W1,  "Annie", 0,
  W1,  "Peter", 0,
  W2,  "John", 2,
  W2,  "Annie", 3,
  W2,  "Peter", 1,
  W2,  "Jack", 2
  W3,  "Annie", 4,
  W3,  "John",  3,
  W3,  "Jack", 3,
  W3,  "Peter", 1
)

Thanks!

Upvotes: 0

Views: 45

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388817

We can first complete the missing author from each week, fill them with previous value and replace NA values with 0. 

library(dplyr)
library(tidyr)

df %>%
  complete(week, author) %>%
  group_by(author) %>%
  fill(n) %>%
  mutate(n = replace_na(n, 0))


#   week  author     n
#   <chr> <chr>  <dbl>
# 1 W1    Annie      0
# 2 W1    Jack       2
# 3 W1    John       1
# 4 W1    Peter      0
# 5 W2    Annie      3
# 6 W2    Jack       2
# 7 W2    John       2
# 8 W2    Peter      1
# 9 W3    Annie      4
#10 W3    Jack       3
#11 W3    John       3
#12 W3    Peter      1

Upvotes: 2

Related Questions