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

Question

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)

what is your final expected result? – Onyambu May 19 '22 at 12:53 — Onyambu, May 19 '22 at 12:53
I have updated it with the final result! – melange164 May 19 '22 at 14:48 — melange164, May 19 '22 at 14:48

langtang · Accepted Answer · 2022-05-19T14:54:39.720

2

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

edited May 19 '22 at 14:54

answered May 19 '22 at 13:06

langtang

22,248
1
12
27

Hi thanks I think it largely works but I need the ```rate.yr1``` and ```rate.yr2``` to come back also. How do I bring back those columns with the corresponding yr? – melange164 May 19 '22 at 14:35
I mean the ```type.yr1``` and ```type.yr2``` – melange164 May 19 '22 at 14:49
sure, see update. – langtang May 19 '22 at 14:53
Sorry for the delayed reply - it works great, thanks! – melange164 May 25 '22 at 12:55

score 1 · Answer 2 · answered May 19 '22 at 15:31

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

score 0 · Answer 3 · answered May 19 '22 at 14:48

0

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)])
}

answered May 19 '22 at 14:48

shaun_m

1,213
3
13

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

3 Answers3