Find a string in one row, but then replace value in next row-CodePudding

I am trying to find a way to search for a string, in my eg "Prep" and then replace the cell in the row below with a specific value, in my eg "SINGLE".

This is my example input and output. I can grep in $V4 and find the values, but I can't seem to work out how to replace the row below with my desired text.

Can anyone give me a tip on what I'm doing wrong? I've tried a number of mutate functions and can't find one to work.

input = structure(list(V1 = c("Fred", "", "John", "", "Max", "", "Tim", 
""), V2 = c("Chicago", "", "Boston", "", "London", "", "Paris", 
""), V3 = c("", "Red", "", "Yellow", "", "Red", "", "Blue"), 
    V4 = c("Final", "TEAM", "Prep", "TEAM", "Prep", "TEAM", "Final", 
    "SINGLE")), row.names = c(NA, 8L), class = "data.frame")
output = structure(list(V1 = c("Fred", "", "John", "", "Max", "", "Tim", 
""), V2 = c("Chicago", "", "Boston", "", "London", "", "Paris", 
""), V3 = c("", "Red", "", "Yellow", "", "Red", "", "Blue"), 
    V4 = c("Final", "TEAM", "Prep", "SINGLE", "Prep", "SINGLE", 
    "Final", "SINGLE")), row.names = 9:16, class = "data.frame")

CodePudding user response：

Here is a potential solution based on the lag() function from the dplyr package (https://dplyr.tidyverse.org/reference/lead-lag.html):

library(dplyr)
input <- structure(list(V1 = c("Fred", "", "John", "", "Max", "", "Tim", 
                                    ""), V2 = c("Chicago", "", "Boston", "", "London", "", "Paris", 
                                                ""), V3 = c("", "Red", "", "Yellow", "", "Red", "", "Blue"), 
                             V4 = c("Final", "TEAM", "Prep", "TEAM", "Prep", "TEAM", "Final", 
                                    "SINGLE")), row.names = c(NA, 8L), class = "data.frame")
output <- structure(list(V1 = c("Fred", "", "John", "", "Max", "", "Tim", 
                               ""), V2 = c("Chicago", "", "Boston", "", "London", "", "Paris", 
                                           ""), V3 = c("", "Red", "", "Yellow", "", "Red", "", "Blue"), 
                        V4 = c("Final", "TEAM", "Prep", "SINGLE", "Prep", "SINGLE", 
                               "Final", "SINGLE")), row.names = 9:16, class = "data.frame")


answer <- input %>%
  mutate(V4 = ifelse(lag(V4, default = first(V4)) == "Prep", "SINGLE", V4))

all_equal(output, answer)
#> [1] TRUE

^{Created on 2022-11-10 by the reprex package (v2.0.1)}