Count occuriences depending on condition & save in new column

[Bennibaba]

Hi all,

I have a list of names and dates. I want to group the entries by Name and count the number of Names for 'after 2016' and 'before 2016'. The count should be added to a new column.

I understand that I could use 'melt' to do that. But I am not quite sure how to do so. Since I also need to add additional conditions.

My input:

Name    Date
Marc    2006
Carl    2003
Carl    2002
Carl    1990
Marc    1999
Max     2016
Max     2014
Marc    2006
Carl    2003
Carl    2002
Carl    2019
Marc    1999
Max     2016
Max     2014

My Output:

      Before
      2016  Countall CountWithCondition
Marc    1    4     2
Marc    0    0    0
Carl    1    5    4
Carl    0    1    1
Max     1    2    0
Max     0    2    1

It would be nice, if someone could point me in the right direction.
Thanks!
So the Output should have 2 entries for each Name, one with a count of Names before 2016 and one after. Addtionally a column which just stats 1 for before 2016 and 0 for after. As well as maybe a second column with count, but with an aditional condition.

[siddharthprabhu]

Hi @Bennibaba. Welcome to RStudio Community.

This doesn't exactly match the output you've shared but have a look and let me know if it serves your purpose.

library(tidyverse)

data <- tibble(
  Name = c("Marc", "Carl", "Carl", "Carl", "Marc", "Max", "Max", 
           "Marc", "Carl", "Carl", "Carl", "Marc", "Max", "Max"),
  Date = c(2006, 2003, 2002, 1990, 1999, 2016, 2014, 
           2006, 2003, 2002, 2019, 1999, 2016, 2014)
)

data %>% 
  mutate(Condition = if_else(Date > 2016, "After 2016", "Before 2016")) %>% 
  mutate_at(c("Name", "Condition"), as_factor) %>% 
  count(Name, Condition, .drop = FALSE)

# A tibble: 6 x 3
  Name  Condition       n
  <fct> <fct>       <int>
1 Marc  Before 2016     4
2 Marc  After 2016      0
3 Carl  Before 2016     5
4 Carl  After 2016      1
5 Max   Before 2016     4
6 Max   After 2016      0

[Bennibaba]

@siddharthprabhu Thank you so much for the quick reply. It helps already a lot.

One additional question, is it possible to add a second Condition? Lets say, I have a third column with 'a, b, a, b, a, a' ...
Could I make a count for all (like the code you provided) but also have a additional column with a second count for both conditions Before/After AND the new third column?

The input would look similiar to this and the output similar to the one shown in the thread;

Name	Date	Second_Condition
Marc	2006	a
Carl	2003	a
Carl	2002	a
Carl	1990	b
Marc	1999	b
Max	2016	b
Max	2014	b
Marc	2006	a
Carl	2003	a
Carl	2002	b
Carl	2019	a
Marc	1999	b

[siddharthprabhu]

Would something like this work?

library(tidyverse)

data <- tibble(
  Name = c("Marc", "Carl", "Carl", "Carl", "Marc", "Max", "Max", 
           "Marc", "Carl", "Carl", "Carl", "Marc", "Max", "Max"),
  Date = c(2006, 2003, 2002, 1990, 1999, 2016, 2014, 
           2006, 2003, 2002, 2019, 1999, 2016, 2014),
  Second_Condition = c("a", "a", "a", "b", "b", "b", "b",
                       "a", "a", "b", "a", "b", "a", "b")
)

head(data)
# A tibble: 6 x 3
  Name   Date Second_Condition
  <chr> <dbl> <chr>           
1 Marc   2006 a               
2 Carl   2003 a               
3 Carl   2002 a               
4 Carl   1990 b               
5 Marc   1999 b               
6 Max    2016 b

data %>% 
  mutate(Condition = if_else(Date > 2016, "After 2016", "Before 2016")) %>% 
  mutate_at(c("Name", "Condition", "Second_Condition"), as_factor) %>% 
  count(Name, Condition, Second_Condition, .drop = FALSE) %>% 
  pivot_wider(names_from = Second_Condition, values_from = n) %>% 
  mutate(Total = rowSums(select(., -c("Name", "Condition")), na.rm = TRUE))

# A tibble: 6 x 5
  Name  Condition       a     b Total
  <fct> <fct>       <int> <int> <dbl>
1 Marc  Before 2016     2     2     4
2 Marc  After 2016      0     0     0
3 Carl  Before 2016     3     2     5
4 Carl  After 2016      1     0     1
5 Max   Before 2016     1     3     4
6 Max   After 2016      0     0     0

[Bennibaba]

@siddharthprabhu yes, that is perfect. And I can understand and reproduce it. Thank you so much!