count frequency by year with dplyr (conditional count)-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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