I have a df containing values for several ids and multiple variables. There is a reserve code, "no", the same code for all variables, that I would like to recode to different values based on another df, reserves.
library(dplyr)
df <- tribble(~id, ~var1, ~var2, ~var3,
10, "1.1", "no", "3.45",
11, "2.3", "4.67", "5.00",
12, "no", "no", "7.65",
13, "7.8", "7.8", "no")
reserves <- tribble(~var, ~replacement,
"var1", "0.01",
"var2", "0.45",
"var3", "0.02")
I am looking for a tidy solution to this. I know I can do this manually, as in:
df$var1[data$var1=="no"] <- reserves$replacement[reserves$var==var1]
...
But I would prefer a lookup-style solution that I could pipe into the rest of my workflow. Below is my desired result.
df <- tribble(~id, ~var1, ~var2, ~var,
10, "1.1", "0.45", "3.45",
11, "2.3", "4.67", "5.00",
12, "0.01", "0.45", "7.65",
13, "7.8", "7.8", "0.02")
So, replacing the "no" values with values specified in another table. Can I get a hand with this?
Alternatively please try the below code
library(tidyverse)
df <- df %>%
pivot_longer(cols = starts_with('var'), names_to = 'var')
df %>% left_join(reserves, by=c('var')) %>%
mutate(value=ifelse(value=='no' & !is.na(replacement), replacement, value)) %>%
pivot_wider(id_cols = id, names_from = var, values_from = value)
Created on 2023-09-29 with reprex v2.0.2
# A tibble: 4 × 4
id var1 var2 var
<dbl> <chr> <chr> <chr>
1 10 1.1 0.45 3.45
2 11 2.3 4.67 5.00
3 12 0.01 0.45 7.65
4 13 7.8 7.8 no