How to count number of values in columns based on a category in R?

Question

Suppose we have a data frame df like

book_id book_category book_word_hi book_word_bye book_word_yes
1       drama         3            0             4
2       action        1            4             5
3       drama         5            3             2

I would like to count the number of values within the book_word columns and sum them in a table for each book_category.

So the output here should look something like:

drama: 17 
action: 10

Does anyone know how to do this?

score 6 · Accepted Answer · answered May 05 '22 at 14:13

6

This is a short and simple one-liner in base R, without requiring any additional packages.

tapply(rowSums(df[3:5]), df[2], sum)
#> book_category
#> action  drama 
#>     10     17

answered May 05 '22 at 14:13

Allan Cameron

147,086
7
49
87

score 4 · Answer 2 · answered May 05 '22 at 14:10

Here, there can be also other columns like book_word_foo as well, which will be counted:

library(tidyverse)

data <- tribble(
  ~book_id, ~book_category, ~book_word_hi, ~book_word_bye, ~book_word_yes,
  1, "drama", 3, 0, 4,
  2, "action", 1, 4, 5,
  2, "drama", 5, 3, 2,
)

data %>%
  pivot_longer(-c(book_id, book_category)) %>%
  group_by(book_category) %>%
  summarise(n = sum(value))
#> # A tibble: 2 × 2
#>   book_category     n
#>   <chr>         <dbl>
#> 1 action           10
#> 2 drama            17

^{Created on 2022-05-05 by the reprex package (v2.0.0)}

benson23 · Answer 3 · 2022-08-02T13:52:00.187

4

First sum up every columns that starts_with the string "book_word". Then group_by and sum up the values per book_category.

library(dplyr)

df %>% 
  mutate(book_sum = rowSums(across(starts_with("book_word")))) %>% 
  group_by(book_category) %>% 
  summarize(sum = sum(book_sum))

# A tibble: 2 × 2
  book_category   sum
  <chr>         <int>
1 action           10
2 drama            17

edited Aug 02 '22 at 13:52

answered May 05 '22 at 14:15

benson23

16,369
9
19
38

score 3 · Answer 4 · answered May 05 '22 at 14:10

3

Use the function summarise_at link to docs:

df %>%
  summarise_at(c("book_word_hi","book_word_bye","book_word_yes"), sum, na.rm = FALSE)

Could also be combined with a group_by

answered May 05 '22 at 14:10

Mads

85
1
7

Thanks. Is there a way to adapt the code using column index rather than column name? E.g. `c([3:5])`? @Mads – HelpMe May 05 '22 at 14:16
Yes I think so. Check the docs that I linked to. You can use select-style statements like: ```summarise_at(vars(book_word_hi:book_word_yes), mean, na.rm = FALSE)```to select many variables at once – Mads May 05 '22 at 14:20
You can also use column indexing. Checkout this other SO answer: https://stackoverflow.com/questions/45883513/using-dplyr-summarise-at-with-column-index – Mads May 05 '22 at 14:21

score 2 · Answer 5 · answered May 05 '22 at 17:29

2

Using aggregate from base R

aggregate(book_sum ~ book_category, transform(data, book_sum = rowSums(data[3:5])), sum)
  book_category book_sum
1        action       10
2         drama       17

answered May 05 '22 at 17:29

akrun

874,273
37
540
662

How to count number of values in columns based on a category in R?

5 Answers5