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
fooobservation 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?