Add a new column with the same value within each group based on value from specific row

Question

I have a data frame with a grouping variable ID, a factor F and a value V that looks something like this:

df <- data.frame(ID = c(rep(1, 3), rep(2, 3)),
                 F = factor(c("A","B","X","C","D","X")),
                 V = c(30, 32, 25, 31, 37, 24)
                  )
> df
  ID F  V
1  1 A 30
2  1 B 32
3  1 X 25
4  2 C 31
5  2 D 37
6  2 X 24

Now, I would like to add a new column New, which has the same value within each group (by ID) based on the value for V in the row where F==X using the tidyverse environment. Ideally, those rows would be removed afterwards so that the new data frame looks like this:

> df
  ID F  V New
1  1 A 30  25
2  1 B 32  25
3  2 C 31  24
4  2 D 37  24

I know that I have to use the group_by() function and probably also mutate(), but I couldn't quite manage to get my desired result.

Answer 1

df %>%
   group_by(ID) %>%
   mutate(New = V[F =='X']) %>%
   filter(F != 'X')

# A tibble: 4 × 4
# Groups:   ID [2]
     ID F         V   New
  <dbl> <fct> <dbl> <dbl>
1     1 A        30    25
2     1 B        32    25
3     2 C        31    24
4     2 D        37    24