I have a table with different columns that include the ID, Year of observation and the value observed. For simplicity, the following is a small sample that conveys my problem:
df = data.frame(
ID=c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3), Year=c(2010,2011,2012,2013,2014,2010,2011,2012,2013,2014, 2010,2011,2012,2013,2014),
Value = c(1,1,0,0,0, 0,1,1,1,1, 1,0,0,0,0)
)
My goal is that grouping by ID, in a new column, let's call it NewCol, if Year == 2012 & Value == 0 then NewCol = 0 and else 1. Getting that part I have no problem, however, I want the rest of NewCol filled with the same value (Either 1s or 0s) according to the condition set. And it would looked like this:
data.frame(
ID=c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3),
Year=c(2010,2011,2012,2013,2014,2010,2011,2012,2013,2014, 2010,2011,2012,2013,2014),
Value = c(1,1,0,0,0, 0,1,1,1,1, 1,0,0,0,0),
NewCol = c(0,0,0,0,0,1,1,1,1,1,0,0,0,0,0)
)
I have tried:
df %>% group_by(ID) %>% mutate(NewCol = ifelse(Year == 2012 & Value == 0, 0, 1))
but it only works for the one row that satisfies the condition.
I tried this but as mentioned from the comment by zack with >1 it does not work.
Appreaciate any help.
You can wrap your condition by any() so that it returns a single logical value per group.
library(dplyr)
df %>%
group_by(ID) %>%
mutate(NewCol = ifelse(any(Year == 2012 & Value == 0), 0, 1))
# A tibble: 15 × 4
# Groups: ID [3]
ID Year Value NewCol
<dbl> <dbl> <dbl> <dbl>
1 1 2010 1 0
2 1 2011 1 0
3 1 2012 0 0
4 1 2013 0 0
5 1 2014 0 0
6 2 2010 0 1
7 2 2011 1 1
8 2 2012 1 1
9 2 2013 1 1
10 2 2014 1 1
11 3 2010 1 0
12 3 2011 0 0
13 3 2012 0 0
14 3 2013 0 0
15 3 2014 0 0