In R, replace values across time series based on another column-CodePudding

Actually this is linked to my previous question: Replace values across time series columns based on another column

However I need to modify values across a time series data set but based on a condition from the same row but across another set of time series columns. The dataset looks like this:

#there are many more years (yrs) in the data set
product<-c("01","02")
yr1<-c("1","7")
yr2<-c("3","4")

#these follow the number of years
type.yr1<-c("mixed","number")
type.yr2<-c("number","mixed")

#this is a reference column to pull values from in case the type value is "mixed" 
mixed.rate<-c("1 5GBP","7 3GBP")

df<-data.frame(product,yr1,yr2,type.yr1,type.yr2,mixed.rate)

Where the value 1 should be replaced by "1 5GBP" and 4 should be "7 3GBP". I am thinking of something like the below -- could anyone please help?

df %>% 
mutate(across(c(starts_with('yr'),starts_with('type'), ~ifelse(type.x=="mixed", mixed.rate.x, .x)))

The final result should be:

product<-c("01","02")
yr1<-c("1 5GBP","7")
yr2<-c("3","7 3GBP")
type.yr1<-c("mixed","number")
type.yr2<-c("number","mixed")
mixed.rate<-c("1 5 GBP","7 3GBP")

df<-data.frame(product,yr1,yr2,type.yr1,type.yr2,mixed.rate)

CodePudding user response：

If you're happy to use base R instead of dplyr then the following will produce your required output:

for (i in 1:2) {
  df[,paste0('yr',i)] <- if_else(df[,paste0('type.yr',i)]=='mixed',df[,'mixed.rate'],df[,paste0('yr',i)])
}

CodePudding user response：

If I understand you correctly, I think you might benefit from pivoting longer, replacing the values in a single if_else, and swinging back to wide.

df %>% 
  pivot_longer(cols = -c(product,mixed.rate), names_to=c(".value", "year"), names_pattern = "(.*)(\\d)") %>% 
  mutate(yr=if_else(type.yr=="mixed",mixed.rate,yr)) %>% 
  pivot_wider(names_from=year, values_from=c(yr,type.yr),names_sep = "")

Output:

  product mixed.rate yr1     yr2    type.yr1 type.yr2
  <chr>   <chr>      <chr>   <chr>  <chr>    <chr>   
1 01      1 5 GBP    1 5 GBP 3      mixed    number  
2 02      7 3GBP     7       7 3GBP number   mixed

CodePudding user response：

You can use pivot_longer to have all yrs in one column and type.yrs in another column. Then record 1 into 1 5GBP and 4 into 7 3GBP if the type.yr column is mixed. then pivot_wider

df %>%
  pivot_longer(contains('yr'), names_to = c('.value','grp'),
               names_pattern = '(\\D )(\\d )') %>%
  mutate(yr = ifelse(type.yr == 'mixed', recode(yr, '1' = '1 5GBP', '4' = '7 3GBP'), yr)) %>%
  pivot_wider(c(product, mixed.rate), names_from = grp,
              values_from = c(yr, type.yr), names_sep = '')

# A tibble: 2 x 6
  product mixed.rate yr1    yr2    type.yr1 type.yr2
  <chr>   <chr>      <chr>  <chr>  <chr>    <chr>   
1 01      1 5GBP     1 5GBP 3      mixed    number  
2 02      7 3GBP     7      7 3GBP number   mixed