CODEWITHSUNDEEP
rdplyrcount
HSC
HSC

Reputation: 129

count frequency by year with dplyr (conditional count)

I want to count the use of Tool A by year and keep zeros.

ID <- c(1,1,2,2,2,3,4,5,5,5)
Tool <- c("A","B","A","B","A","A","B","A","A","A")
Year <- c(2000,2001,2001,2001,2002,2002,2001,2000,2001,2002)
df <- data.frame(ID,Tool,Year)
library(tidyverse)
df %>% group_by(ID) %>% summarise(toolA = sum(Tool == "A")) %>% count(toolA)
# A tibble: 4 x 2
  toolA     n
  <int> <int>
1     0     1
2     1     2
3     2     1
4     3     1

I want to add year columns, so that I can have a table as below

tool A Count 2000 2001 2002
0 1 0 0 0
1 2 1 0 1
2 1 0 1 1
3 1 1 1 1

The numbers under years means the number of use in a year.(Not a person) How would you do?

Upvotes: 0

Views: 609

Answers (3)

ekoam
ekoam

Reputation: 8844

Here is another tidyverse method. Simply speaking, we would pivot the dataframe from wide to long and then summarize. Frist summarization gets rid of all the other non-"A"s. Second summarization condenses the result table into unique bins identified by each toolA and produces a count.

library(dplyr)
library(tidyr)

df %>% 
  mutate(value = +(Tool == "A")) %>%
  pivot_wider(names_from = Year, values_fill = 0L) %>%
  group_by(ID) %>% 
  summarize(across(-Tool, sum)) %>% 
  group_by(toolA = rowSums(across(-ID))) %>% 
  summarize(count = n(), across(-c(ID, count), sum))

Output

# A tibble: 4 x 5
  toolA count `2000` `2001` `2002`
  <dbl> <int>  <int>  <int>  <int>
1     0     1      0      0      0
2     1     2      1      0      1
3     2     1      0      1      1
4     3     1      1      1      1

Upvotes: 1

Ben
Ben

Reputation: 30514

I might try this approach with tidyverse. Create a list column with the Year when grouping by ID. After including the count n as you have done, use unnest_longer to recover the years. I added an extra column for situations where count is zero called "None". A final pivot_wider would put the data into wide form again.

library(tidyverse)

df %>% 
  group_by(ID) %>% 
  summarise(toolA = sum(Tool == "A"),
            Years = list(Year[Tool == "A"])) %>%
  add_count(toolA) %>%
  unnest_longer(Years) %>%
  replace_na(list(Years = "None")) %>%
  mutate(value = 1) %>%
  pivot_wider(id_cols = c(toolA, n), names_from = Years, names_prefix = "Year_", values_from = value, values_fill = 0)%>%
  arrange(toolA)

Output

  toolA     n Year_2000 Year_2001 Year_2002 Year_None
  <int> <int>     <dbl>     <dbl>     <dbl>     <dbl>
1     0     1         0         0         0         1
2     1     2         1         0         1         0
3     2     1         0         1         1         0
4     3     1         1         1         1         0

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 389275

Maybe this is too convoluted and a better/easier solution exists.

library(dplyr)
library(tidyr)

dataA <- df %>% 
  group_by(ID) %>% 
  summarise(toolA = sum(Tool == "A")) %>% 
  count(toolA)

df %>%
  group_by(ID, Year) %>%
  summarise(toolA = sum(Tool == "A"), .groups = 'drop') %>%
  pivot_wider(names_from = Year, values_from = toolA, values_fill = 0) %>%
  select(-ID) %>%
  mutate(toolA = rowSums(.)) %>%
  right_join(dataA, by = 'toolA') %>%
  select(toolA, n, everything()) %>%
  arrange(toolA) %>%
  group_by(toolA, n) %>%
  summarise(across(.fns = sum), .groups = 'drop')

#  toolA     n `2000` `2001` `2002`
#  <dbl> <int>  <int>  <int>  <int>
#1     0     1      0      0      0
#2     1     2      1      0      1
#3     2     1      0      1      1
#4     3     1      1      1      1

Upvotes: 1

Related Questions