Assign value to other group members with conditions in a complex data structure

Question

A MWE is as follows:

df = tibble(
      Group = c(rep(1,9), rep(2,9)),
      Stage = rep(c(1,1,1,rep(2,6)),2),
      Period = rep(c(rep(1,6),2,2,2),2),
      Role = rep(c(NA,NA,NA,0,1,0,0,0,1),2),
      Value = round(rnorm(n=18,mean=10,sd=5), digits = 1)
)

# A tibble: 18 x 5
   Group Stage Period  Role Value
   <dbl> <dbl>  <dbl> <dbl> <dbl>
 1     1     1      1    NA  10.8
 2     1     1      1    NA  15.5
 3     1     1      1    NA   7.4
 4     1     2      1     0   3.7
 5     1     2      1     1   6.7
 6     1     2      1     0   7.5
 7     1     2      2     0  15.1
 8     1     2      2     0   8.2
 9     1     2      2     1   5.1
10     2     1      1    NA  17.3
11     2     1      1    NA  14.9
12     2     1      1    NA  11.1
13     2     2      1     0  13.3
14     2     2      1     1   3.6
15     2     2      1     0   7.2
16     2     2      2     0  13.1
17     2     2      2     0  16.5
18     2     2      2     1  11

Notes on df: In df, for each group, they go through Stage 1 and 2 with multiple periods. In stage 2, one group member is assigned a unique role.

My objective: In stage 2, I want to assign Role 1's value to other group members in each period for each group. The desired outcome would be:

# A tibble: 18 x 5
   Group Stage Period  Role Value newValue
   <dbl> <dbl>  <dbl> <dbl> <dbl>  <dbl>
 1     1     1      1    NA  10.8   10.8
 2     1     1      1    NA  15.5   15.5
 3     1     1      1    NA   7.4    7.4
 4     1     2      1     0   3.7    6.7
 5     1     2      1     1   6.7    6.7
 6     1     2      1     0   7.5    6.7
 7     1     2      2     0  15.1    5.1
 8     1     2      2     0   8.2    5.1 
 9     1     2      2     1   5.1    5.1
10     2     1      1    NA  17.3   17.3
11     2     1      1    NA  14.9   14.9
12     2     1      1    NA  11.1   11.1
13     2     2      1     0  13.3    3.6
14     2     2      1     1   3.6    3.6
15     2     2      1     0   7.2    3.6  
16     2     2      2     0  13.1   11 
17     2     2      2     0  16.5   11
18     2     2      2     1  11     11

I can simply separate df into two tibbles by Stage, and then solve the problem with this method suggested by David Arenburg as follows:

df_Stage2 <- df %>% filter (Stage == 2) %>%
  group_by(Group, Period) %>%
  mutate(newValue = Value[Role == 1]) %>%
  ungroup

However, I believe there's a way to do it with the whole data structure. I tried the if_else and case_when methods, for example:

df <- df %>%
  group_by(Group, Period) %>%
  mutate(
    newValue = if_else(Stage == 1, -99, Value[Role == 1])
  ) %>%
  ungroup

However, either method does not work. Is there a way to solve this problem without separating df into two dfs by Stage? Thanks a lot!

In addition, in the solution suggested by David Arenburg, he used Value[Role == 1L] instead of Value[Role == 1], would anyone please explain to me what the L does here?

Sorry, it should be `df_Stage2` instead. I'll show the desired outcome in the question. — Lin Jing, Sep 28 '21 at 14:49

score 1 · Accepted Answer · answered Sep 29 '21 at 07:00

You may use match here -

library(dplyr)

df %>%
  group_by(Group, Stage, Period) %>%
  mutate(NewValue = ifelse(Stage == 2, Value[match(1, Role)], Value)) %>%
  ungroup()

#   Group Stage Period  Role Value NewValue
#   <int> <int>  <int> <int> <dbl>    <dbl>
# 1     1     1      1    NA  10.8     10.8
# 2     1     1      1    NA  15.5     15.5
# 3     1     1      1    NA   7.4      7.4
# 4     1     2      1     0   3.7      6.7
# 5     1     2      1     1   6.7      6.7
# 6     1     2      1     0   7.5      6.7
# 7     1     2      2     0  15.1      5.1
# 8     1     2      2     0   8.2      5.1
# 9     1     2      2     1   5.1      5.1
#10     2     1      1    NA  17.3     17.3
#11     2     1      1    NA  14.9     14.9
#12     2     1      1    NA  11.1     11.1
#13     2     2      1     0  13.3      3.6
#14     2     2      1     1   3.6      3.6
#15     2     2      1     0   7.2      3.6
#16     2     2      2     0  13.1     11  
#17     2     2      2     0  16.5     11  
#18     2     2      2     1  11       11

Thanks so much! Looks so simple. So, in my attempts to solve the problem with `if_else`, it should work if I add `Stage` into my `group_by` function, right? — Lin Jing, Sep 29 '21 at 07:44
That is correct. It would work for this example but it would fail if you have more than 1 value with `Role = 1`. `match` by default returns the index for the 1st one. — Ronak Shah, Sep 29 '21 at 10:00

Assign value to other group members with conditions in a complex data structure

1 Answers1