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.

Ronak Shah · Accepted Answer · 2022-05-19 11:21:17Z

2

Using ifelse instead of replace should solve your problem.

library(dplyr)

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

#  yr1 yr2 right
#1   1   3     2
#2   3   4     3

In general, I would suggest to use replace only when you have a fixed (constant) value to replace for all other things using ifelse is better.

answered May 19, 2022 at 11:21

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

melange164 Over a year ago

Actually I have a followup question, if I need to mutate but based on a condition on another column, what do I need instead of other.column? @Ronak Shah Something like df %>% mutate(across(starts_with('yr'), ~ifelse(other.column=="C", right, .x)))

Ronak Shah Over a year ago

@melange164 Exactly, that should work if other.column is the name of the column.

Quinten · Accepted Answer · 2022-05-19 11:46:08Z

0

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

answered May 19, 2022 at 11:46

Quinten

42.8k12 gold badges58 silver badges117 bronze badges

Collectives™ on Stack Overflow

Replace values across time series columns based on another column

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related