Conditionally mutate existing values by group R

July 7, 2023

I know this is similar to previous questions, but this seems a little different to me because it’s referring to existing values. I have R simplified R dataframe below.

set.seed(2342)
df <- tibble(bp = rep(c("vt1", "vt2"), 4),
             id = rep(c(1, 2), each = 4, 1),
             alg_vt1 = rep(c("a", "b"), each = 2, 2),
             alg_vt2 = rep(c("a", "b"), 4)) %>% 
    mutate(vo2 = round(runif(nrow(.), 10, 20)),
           vo2 = if_else(bp == "vt2", vo2 * 2, vo2))

Here’s what the sample data frame looks like (side note, how do I get the spacing in my dataframe correct when pasting a data frame into Stack Overflow?)

bp  id  alg_vt1 alg_vt2 vo2
vt1 1   a   a   18
vt2 1   a   b   36
vt1 1   b   a   16
vt2 1   b   b   30
vt1 2   a   a   19
vt2 2   a   b   32
vt1 2   b   a   10
vt2 2   b   b   34

I want to mutate a new column equal to the vo2 at vt2 for each combination of id, alg_vt1, and alg_vt2. That is, I want a data frame that looks like this:

bp  id  alg_vt1 alg_vt2 vo2 vo2_vt2
vt1 1   a   a   18  36
vt2 1   a   b   36  36
vt1 1   b   a   16  30
vt2 1   b   b   30  30
vt1 2   a   a   19  32
vt2 2   a   b   32  32
vt1 2   b   a   10  34
vt2 2   b   b   34  34

I’ve tried variations of

df %>% 
    group_by(id, alg_vt1, alg_vt2) %>% 
    mutate(vo2_vt2 =

but whatever I put after that gives me an error or I don’t get the output I expect. I know there are hacky ways I could figure this out, but I’m sure there’s a "tidyverse" way to solve this problem. I feel like filter() or pick() could get me there, but I can’t quite put this all together.

>Solution :

tidyr::fill() can do the job.

library(tidyverse)

df |> 
  mutate(vo2_vt2 = ifelse(bp == "vt1", NA, vo2)) |> 
  fill(vo2_vt2, .direction = "up")
#> # A tibble: 8 × 6
#>   bp       id alg_vt1 alg_vt2   vo2 vo2_vt2
#>   <chr> <dbl> <chr>   <chr>   <dbl>   <dbl>
#> 1 vt1       1 a       a          18      36
#> 2 vt2       1 a       b          36      36
#> 3 vt1       1 b       a          16      30
#> 4 vt2       1 b       b          30      30
#> 5 vt1       2 a       a          19      32
#> 6 vt2       2 a       b          32      32
#> 7 vt1       2 b       a          10      34
#> 8 vt2       2 b       b          34      34

Regarding your question about pasting data.frames into SO: Use reprex to prepare your question/answer it will take care of the formatting.