rdplyrrowsum

sum across columns based on range given in another text column in r


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.```

Solution

  • 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