i want to create new column for row total based on column range given in text column (col_range). I tried the following and it throws me an error. Does anybody know how to do this.
d = data.frame(index_id = c("a1232","c198", "s345","2ert", "e234","e567"),
yr_ref = c(2023,2024,2025,2024,2027,NA),
temp2023 = c(2000,5000,2300,2000,1000,200),
temp2024 = c(3000,3000,3000,0,0,200),
temp2025 = c(2000,3000,0,800,8000,200),
temp2026 = c(300,200,1000,0,0,200),
temp2027= c(1300,1200,100,10,10,200),
col_range = c("temp2023:temp2023","temp2023:temp2024","temp2023:temp2025","temp2023:temp2024","temp2023:temp2027",NA))
d= d %>%
mutate(temp_total= ifelse(!is.na(yr_ref),
sum(c_across(col_range)),NA))
#### ERROR
Error in `mutate()`:
ℹ In argument: `temp_total = ifelse(!is.na(yr_ref), sum(c_across(col_range)), NA)`.
Caused by error in `sum()`:
! invalid 'type' (character) of argument
Run `rlang::last_trace()` to see where the error occurred.```
Inspired by @Isaac
library(tidyverse)
d %>%
rowwise() %>%
mutate(total = ifelse(!is.na(yr_ref),
map_dbl(col_range, ~ rowSums(across(str_split_1(.x, pattern = ":")[1]:str_split_1(.x, pattern = ":")[2]))), NA))
# A tibble: 6 x 9
# Rowwise:
index_id yr_ref temp2023 temp2024 temp2025 temp2026 temp2027 col_range total
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <dbl>
1 a1232 2023 2000 3000 2000 300 1300 temp2023:temp2023 2000
2 c198 2024 5000 3000 3000 200 1200 temp2023:temp2024 8000
3 s345 2025 2300 3000 0 1000 100 temp2023:temp2025 5300
4 2ert 2024 2000 0 800 0 10 temp2023:temp2024 2000
5 e234 2027 1000 0 8000 0 10 temp2023:temp2027 9010
6 e567 NA 200 200 200 200 200 NA NA