Replace values across time series columns based on another column

Question

My question is similar to another one: R replace specific value in many columns across dataframe

However I need to replace values based on a vector from another column rather than with NA or a constant.

Can you please help?

#there are many more yrs in the data set
yr1<-c("1","missing")
yr2<-c("3","4")
right<-c("2","3")
df<-data.frame(yr1,yr2,right)

df<-df %>%
  mutate(
      across(starts_with('yr'), ~replace(.x, ~.x=="missing", right) ))

Where right is another column where I want to "lookup" the value to replace the "missing" value.

Answer 1

base R option:

cols <- grep("^yr\\d+$", names(df))
df[cols][df[cols] == "missing"] <- df[df[cols] == "missing", "right"]

Output:

  yr1 yr2 right
1   1   3     2
2   3   4     3

Answer 2

You can try joining players table twice and games table once:

SET @userid := 1;

SELECT g.id, p1.*
FROM players p1
 JOIN players p2
   ON p1.gameId=p2.gameId
 JOIN games g 
   ON p1.gameId=g.id
WHERE p1.userId= @userId;

Demo fiddle