Cumulative count of observations per day with tally/dplyr, filling missing values

Viewed 531

I have a list of observations per user; each user may have several observations of foo per day. For each distinct day, I'd like a cumulative count of the foo values. This is what I got so far:

library(tidyverse)
library(lubridate)

df = tribble(
  ~user_id, ~foo, ~bar, ~created_at,
  1, "a", "b", "2018-07-30",
  1, "a", "c", "2018-07-31",
  1, "a", "c", "2018-07-31",
  1, "b", "a", "2018-08-01",
  1, "b", "c", "2018-08-02",
  1, "b", "a", "2018-08-03",
  1, "a", "a", "2018-08-03",
  2, "b", "b", "2018-07-30",
  2, "b", "c", "2018-07-31",
  2, "a", "a", "2018-08-01",
  2, "a", "a", "2018-08-01",
  2, "a", "c", "2018-08-02",
  2, "a", "c", "2018-08-02",
  2, "a", "a", "2018-08-03"
) %>% mutate_at("created_at", as_datetime)

df %>%
  mutate(cutoff_date = created_at %>% date) %>% 
  group_by(user_id, foo, cutoff_date) %>% 
  tally %>%
  mutate(foo_cnt = cumsum(n)) %>%
  select(-n) %>% 
  arrange(user_id, cutoff_date, foo)

This gives me:

   user_id foo   cutoff_date foo_cnt
     <dbl> <chr> <date>        <int>
 1      1. a     2018-07-30        1
 2      1. a     2018-07-31        3
 3      1. b     2018-08-01        1
 4      1. b     2018-08-02        2
 5      1. a     2018-08-03        4
 6      1. b     2018-08-03        3
 7      2. b     2018-07-30        1
 8      2. b     2018-07-31        2
 9      2. a     2018-08-01        2
10      2. a     2018-08-02        4
11      2. a     2018-08-03        5

Great, so I know that until August 3, user 1 has seen a four times, and b three times. I would now like to know, for each date that occurs in my data (I do not care about missing dates):

  • The total count of the specific foo observation until the date
  • The relative amount of the observation compared to the others

That is, the output should be:

  user_id cutoff_date foo foo_cnt foo_cnt_total foo_pct
1      1. 2018-07-30  a         1             1     100
2      1. 2018-07-30  b         0             0       0
3      1. 2018-07-31  a         3             4     100
4      1. 2018-07-31  b         0             0       0
5      1. 2018-08-01  a         3             7    87.5
6      1. 2018-08-01  b         1             1    12.5
...

In row 5, it's 87.5% because the user has seen a seven times and b once until that point.

I have an idea of how to get there, but I am struggling with the including other values of foo for a date that is present in the data, but where there is no observation of foo. I have looked into complete() but I cannot figure out how to use it to fill the remaining values.

For example, when I add either of those, I get no additional columns:

complete(nesting(user_id, foo), cutoff_date)
complete(user_id, cutoff_date, foo)

What am I missing?


Update: I have added ungroup as suggested, and now I get the total count per day as well. I have used fill to fill previous values for the same value of foo:

df %>%
  mutate(cutoff_date = created_at %>% date) %>% 
  group_by(user_id, foo, cutoff_date) %>%
  tally %>%
  mutate(foo_cnt = cumsum(n)) %>%
  select(-n) %>% 
  ungroup() %>% 
  complete(nesting(user_id, foo), cutoff_date) %>% 
  arrange(user_id, cutoff_date, foo) %>% 
  group_by(user_id, foo) %>% 
  fill(foo_cnt) %>% 
  ungroup() %>% 
  group_by(user_id, cutoff_date) %>% 
  mutate(foo_cnt_total = sum(foo_cnt, na.rm = TRUE))

   user_id foo   cutoff_date foo_cnt foo_cnt_total
     <dbl> <chr> <date>        <int>         <int>
 1      1. a     2018-07-30        1             1
 2      1. a     2018-07-31        3             3
 3      1. a     2018-08-01        3             4
 4      1. a     2018-08-02        3             5
 5      1. a     2018-08-03        4             7
 6      1. b     2018-07-30       NA             1
 7      1. b     2018-07-31       NA             3
 8      1. b     2018-08-01        1             4
 9      1. b     2018-08-02        2             5
10      1. b     2018-08-03        3             7

However, the value of b should not start with NA. What would be needed here?

2 Answers

You can specify the filling used in the complete-call and with a slightly rearranging of the order of the different steps, get the desired output:

df %>%
  mutate(cutoff_date = date(created_at)) %>% 
  count(user_id, foo, cutoff_date) %>%
  complete(nesting(user_id, foo), cutoff_date, fill = list(n = 0)) %>% 
  arrange(user_id, foo, cutoff_date) %>% 
  group_by(user_id, foo) %>% 
  mutate(foo_cnt = cumsum(n)) %>%
  group_by(user_id, cutoff_date) %>% 
  mutate(foo_cnt_total = sum(foo_cnt), 
         foo_pct = 100 * foo_cnt / foo_cnt_total) %>% 
  select(-n)

# A tibble: 20 x 6
# Groups:   user_id, cutoff_date [10]
#    user_id foo   cutoff_date foo_cnt foo_cnt_total foo_pct
#      <dbl> <chr> <date>        <dbl>         <dbl>   <dbl>
#  1       1 a     2018-07-30        1             1   100  
#  2       1 a     2018-07-31        3             3   100  
#  3       1 a     2018-08-01        3             4    75  
#  4       1 a     2018-08-02        3             5    60  
#  5       1 a     2018-08-03        4             7    57.1
#  6       1 b     2018-07-30        0             1     0  
#  7       1 b     2018-07-31        0             3     0  
#  8       1 b     2018-08-01        1             4    25  
#  9       1 b     2018-08-02        2             5    40  
# 10       1 b     2018-08-03        3             7    42.9
df = tribble(
    ~user_id, ~foo, ~bar, ~created_at,
    1, "a", "b", "2018-07-30",
    1, "a", "c", "2018-07-31",
    1, "a", "c", "2018-07-31",
    1, "b", "a", "2018-08-01",
    1, "b", "c", "2018-08-02",
    1, "b", "a", "2018-08-03",
    1, "a", "a", "2018-08-03",
    2, "b", "b", "2018-07-30",
    2, "b", "c", "2018-07-31",
    2, "a", "a", "2018-08-01",
    2, "a", "a", "2018-08-01",
    2, "a", "c", "2018-08-02",
    2, "a", "c", "2018-08-02",
    2, "a", "a", "2018-08-03"
) %>% mutate_at("created_at", as_datetime)

df %>%
    dplyr::mutate(cutoff_date = created_at %>% date) %>% 
    group_by(user_id, foo, cutoff_date) %>% 
    tally %>%
    dplyr::mutate(foo_cnt = cumsum(n)) %>%
    select(-n) %>% 
    arrange(user_id, cutoff_date, foo) %>% group_by(user_id) %>%
    complete(nesting(user_id, foo), cutoff_date, fill = list(foo_cnt = 0)) %>%
    arrange(user_id, cutoff_date, foo) %>% group_by(user_id, foo) %>%
    dplyr::mutate(foo_cnt_total = cumsum(foo_cnt)) %>% group_by(user_id, cutoff_date) %>%
    dplyr::mutate(foo_sum_del = sum(foo_cnt_total)) %>% group_by(user_id, foo, cutoff_date) %>%
    dplyr::mutate(foo_pct = foo_cnt_total/foo_sum_del*100) %>% ungroup() %>%
    select(-foo_sum_del)

result:

# A tibble: 20 x 6
   user_id foo   cutoff_date foo_cnt foo_cnt_total foo_pct
     <dbl> <chr> <date>        <dbl>         <dbl>   <dbl>
 1       1 a     2018-07-30        1             1   100  
 2       1 b     2018-07-30        0             0     0  
 3       1 a     2018-07-31        3             4   100  
 4       1 b     2018-07-31        0             0     0  
 5       1 a     2018-08-01        0             4    80  
 6       1 b     2018-08-01        1             1    20  
 7       1 a     2018-08-02        0             4    57.1
 8       1 b     2018-08-02        2             3    42.9
 9       1 a     2018-08-03        4             8    57.1
10       1 b     2018-08-03        3             6    42.9
11       2 a     2018-07-30        0             0     0  
12       2 b     2018-07-30        1             1   100  
13       2 a     2018-07-31        0             0     0  
14       2 b     2018-07-31        2             3   100  
15       2 a     2018-08-01        2             2    40  
16       2 b     2018-08-01        0             3    60  
17       2 a     2018-08-02        4             6    66.7
18       2 b     2018-08-02        0             3    33.3
19       2 a     2018-08-03        5            11    78.6
20       2 b     2018-08-03        0             3    21.4
Related