I want to recode two columns indicating the status (x1 or x2 = either 3 or 0) of a whole group based on the value of another column of the oldest member of each group.
In the example below x1(x2) is the sum of key1(key2) inside each group (there are always three values/imputations per person). However, I only want to have either x1>0 or x2>0 for each group. In those groups where there is one person with key1=1 and one person with key2=1 (and therefore x1=3 AND x2=3) the oldest person should decide. If the oldest person has key1=1 and key2=0, like in group A, x1 should be 3 and x2 should be 0 for the whole group and so on.
Reproducible example:
id <- c("A11", "A12", "A13", "A21", "A22", "A23", "B11", "B12", "B13", "C11", "C12", "C13", "C21", "C22", "C23", "D11", "D12", "D13", "D21", "D22", "D23", "E11", "E12", "E13", "E21", "E22", "E23")
group <- c("A","A","A","A","A","A","B","B","B","C","C","C","C","C","C","D","D","D","D","D","D","E","E","E","E","E","E")
imputation <- c(rep(1:3, 9))
age <- c(45,45,45,17,17,17,20,20,20,70,70,70,60,60,60,25,25,25,30,30,30,28,28,28,34,34,34)
key1 <- c(1,1,1,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,0,0,0,1,1,1,0,0,0)
key2 <- c(0,0,0,1,1,1,0,0,0,1,1,1,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0)
x1 <- c(3,3,3,3,3,3,0,0,0,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3)
x2 <- c(3,3,3,3,3,3,0,0,0,3,3,3,3,3,3,3,3,3,3,3,3,0,0,0,0,0,0)
test <- data.frame(id, group, imputation, age, key1, key2, x1, x2)
Subset where x1 and x2 should be recoded:
> test %>% group_by(group) %>% filter(x1==x2 & x1>0 | x1==x2 & x2>0)
# A tibble: 18 x 8
# Groups: group [3]
id group imputation age key1 key2 x1 x2
<fct> <fct> <int> <dbl> <dbl> <dbl> <dbl> <dbl>
1 A11 A 1 45 1 0 3 3
2 A12 A 2 45 1 0 3 3
3 A13 A 3 45 1 0 3 3
4 A21 A 1 17 0 1 3 3
5 A22 A 2 17 0 1 3 3
6 A23 A 3 17 0 1 3 3
7 C11 C 1 70 0 1 3 3
8 C12 C 2 70 0 1 3 3
9 C13 C 3 70 0 1 3 3
10 C21 C 1 60 1 0 3 3
11 C22 C 2 60 1 0 3 3
12 C23 C 3 60 1 0 3 3
13 D11 D 1 25 1 0 3 3
14 D12 D 2 25 1 0 3 3
15 D13 D 3 25 1 0 3 3
16 D21 D 1 30 0 1 3 3
17 D22 D 2 30 0 1 3 3
18 D23 D 3 30 0 1 3 3
The output should be:
id group imputation age key1 key2 x1 x2
1 A11 A 1 45 1 0 3 0
2 A12 A 2 45 1 0 3 0
3 A13 A 3 45 1 0 3 0
4 A21 A 1 17 0 1 3 0
5 A22 A 2 17 0 1 3 0
6 A23 A 3 17 0 1 3 0
7 C11 C 1 70 0 1 0 3
8 C12 C 2 70 0 1 0 3
9 C13 C 3 70 0 1 0 3
10 C21 C 1 60 1 0 0 3
11 C22 C 2 60 1 0 0 3
12 C23 C 3 60 1 0 0 3
13 D11 D 1 25 1 0 0 3
14 D12 D 2 25 1 0 0 3
15 D13 D 3 25 1 0 0 3
16 D21 D 1 30 0 1 0 3
17 D22 D 2 30 0 1 0 3
18 D23 D 3 30 0 1 0 3
I guess it can be done with a combination of group_by, filter, mutate and ifelse, but I haven't figured it out yet. It is important, however, that it includes filter or something similar, because the observations with x1==x2 & x1>0 | x1==x2 & x2>0 are only a subset of my data frame.
