How do I sum values from different rows in the tidyverse?

Question

I have a data frame that looks as follows:

   WORD       CATEGORY             n
   <fct>       <fct>           <int>
 1 A            X                  4
 2 B            X                  3
 3 C            X                  6
 4 C            Y                  3
 5 D            X                  2
 6 E            X                  2
 7 F            Y                  2

I want to add a column sum that adds together values in the column n based on CATEGORY. So in rows 3 and 4, for instance, the value of the sum column would be 9.

Here is what the full dataset would look like:

   WORD       CATEGORY             n  sum
   <fct>       <fct>           <int>  <int>
 1 A            X                  4  4
 2 B            X                  3  3
 3 C            X                  6  9 
 4 C            Y                  3  9
 5 D            X                  2  2 
 6 E            X                  2  2
 7 F            Y                  2  2

How do I do this in the tidyverse?

Does a `group_by(CATEGORY) %>% summarize(sum = sum(n))` answer your question, as e.g. [here](https://stackoverflow.com/a/27266488/3938360)? — Alexlok, Nov 18 '20 at 19:42
Rows 3 and 4 have different categories, though. So do you want to sum by CATEGORY or by WORD or sth. else? — deschen, Nov 18 '20 at 19:49
I want to sum by both word and category. I edited the question to make this clearer. — Namenlos, Nov 18 '20 at 21:49
the example you provided shows that it's summed only when category is different and it's the same word, is this what you want to achieve? — Abdessabour Mtk, Nov 18 '20 at 21:52

score 0 · Accepted Answer · answered Nov 18 '20 at 22:08

If we count the number of unique values in CATEGORY and add it to the grouping variables we can directly sum up the n's:

dt %>%
 group_by(WORD) %>%
 mutate(uni=length(unique(CATEGORY))) %>%
 group_by(WORD,uni) %>%
 mutate(sum=sum(n)) %>%
 ungroup %>%
 select(-uni)

# A tibble: 7 x 4
  WORD  CATEGORY     n   sum
  <fct> <fct>    <int> <int>
1 A     X            4     4
2 B     X            3     3
3 C     X            6     9
4 C     Y            3     9
5 D     X            2     2
6 E     X            2     2
7 F     Y            2     2

How do I sum values from different rows in the tidyverse?

1 Answers1