Create count table for specific condition and then add column that creates count by group as a whole in R

Question

I have a table like this:

data1 <- data.frame("State" = c("NJ", "NJ", "PA", "NJ", "TX"), "Filter" = c("Filter", "Filter", 
"No Filter", "Filter", "Filter"), "Threshold" = c("Exceeds","Exceeds", NA, "NL", "Exceeds"))

I'd like to create a count table that groups by State and Filter and then counts the number of times the Threshold is "Exceeds" and put that value in a new column. And then count the number of times a certain State and Filter combination occurs and put it in another column. An example of what I'm looking for is below.

final_data <- data.frame("State" = c("NJ", "NJ", "PA", "NJ", "TX"), "Filter" = c("Filter", 
"Filter", "No Filter", "Filter", "Filter"), "Threshold" = c("Exceeds", "Exceeds", NA, "NL", 
"Exceeds"), Count_Exceeds_Threshold = c(2, 2, 0, 0, 1), Count_Total = c(3, 3, 1, 3, 1))

I've tried figuring this out with group_by and tally() in dplyr, but I can't get it to work the way I want.

Thank you!!

score 2 · Answer 1 · answered Jul 27 '23 at 11:42

You can use add_count() for both purposes:

library(dplyr)

data1 %>%
  group_by(State, Filter) %>%
  add_count(wt = (Threshold == "Exceeds"), name = "Count_Exceeds_Threshold") %>%
  add_count(name = "Count_Total") %>%
  ungroup()

# # A tibble: 5 × 5
#   State Filter    Threshold Count_Exceeds_Threshold Count_Total
#   <chr> <chr>     <chr>                       <int>       <int>
# 1 NJ    Filter    Exceeds                         2           3
# 2 NJ    Filter    Exceeds                         2           3
# 3 PA    No Filter NA                              0           1
# 4 NJ    Filter    NL                              2           3
# 5 TX    Filter    Exceeds                         1           1

score 1 · Accepted Answer · answered Jul 27 '23 at 11:38

You can use mutate and .by for inline grouping, and count the number of times Threshold == "Exceeds". n() is used to get the number of rows by group.

library(dplyr)
data1 %>% 
  mutate(Count_Exceeds_Threshold = sum(Threshold == "Exceeds", na.rm = TRUE),
         Count_Total = n(), .by = c(State, Filter))

#   State    Filter Threshold Count_Exceeds_Threshold Count_Total
# 1    NJ    Filter   Exceeds                       2           3
# 2    NJ    Filter   Exceeds                       2           3
# 3    PA No Filter      <NA>                       0           1
# 4    NJ    Filter        NL                       2           3
# 5    TX    Filter   Exceeds                       1           1

Create count table for specific condition and then add column that creates count by group as a whole in R

2 Answers2