R pivot table for summing outcomes of overlapping sets?

Question

Below is the "head" of my datatable, called off.

            callgroup agent_idx binary_outcome
1715         8       453              0
1716         5       486              0
1717        50       367              0
1718        60       513              0
1719         7       282              0
1720         8       660              0

Each row represents a specific agent, callgroup, and binary outcome (sale/no sale). I'd like to find the sum of binary outcomes for each agent:callgroup pair. Ideally, the output would look something like:

callgroup  agent_idx  total_succeses
8          453        25
8          486        37
...

I've seen a couple semi-similar questions on here, such as Multiple Pivot Table - R (table) (only the binary outcome summation is not observed.) And another, with a completely different approach How to sum a variable by group

Here is my best attempt:

off %>% group_by(c(callgroup, agent_idx)) %>% 
  summarise(successes = sum(binary_outcome))

>>>
Error: Problem with `mutate()` input `..1`.
x Input `..1` can't be recycled to size 1409261.
i Input `..1` is `c(callgroup, agent_idx)`.
i Input `..1` must be size 1409261 or 1, not 2818522.
Run `rlang::last_error()` to see where the error occurred.

Any ideas?

score 2 · Accepted Answer · answered Sep 28 '20 at 18:05

2

Your attempt is almost correct, but you don't need to use c() in group_by(). Try this instead:

off %>% 
  group_by(callgroup, agent_idx) %>% 
  summarise(successes = sum(binary_outcome))

answered Sep 28 '20 at 18:05

stlba

667
3
13

Ah, so close! Thanks :) – jbuddy_13 Sep 28 '20 at 18:08
You can select this answer to mark the question closed if your issue is fixed :) – stlba Sep 28 '20 at 18:09
I will! Apparently, I need to wait 10 min to do so. Weird.. – jbuddy_13 Sep 28 '20 at 18:10

score 1 · Answer 2 · answered Sep 28 '20 at 18:26

1

Your method (corrected as stlba mentioned) is a way to do it, but I prefer dplyr::count as below.

off %>% count(callgroup,
              agent_idx,
              wt = binary_outcome,
              name = "total_succeses")

answered Sep 28 '20 at 18:26

SirTain

369
2
6

R pivot table for summing outcomes of overlapping sets?

2 Answers2