Home Create a new column a fill with values from a set of multiple columns conditional on column names

Questions

Create a new column a fill with values from a set of multiple columns conditional on column names

December 28, 2021

I have this dataframe:

df <- df <- structure(list(A01 = c(0L, 0L, 2L, 0L, 4L, 1L, 10L, 10L), A02 = c(0L, 
-1L, 0L, 1L, 4L, 4L, 9L, 12L), A03 = c(-5L, -4L, 2L, -4L, 3L, 
2L, 8L, 12L), A04 = c(-1L, -3L, 3L, 1L, 3L, -3L, 9L, 12L), A05 = c(-1L, 
-3L, 1L, -1L, 3L, 0L, 7L, 10L), A06 = c(2L, -3L, 3L, 1L, 4L, 
0L, 7L, 12L), A07 = c(3L, -3L, 3L, 1L, 4L, 0L, 7L, 9L), X = c(2L, 
2L, 6L, 7L, 12L, 15L, 22L, 24L)), class = "data.frame", row.names = c(NA, 
-8L))

  A01 A02 A03 A04 A05 A06 A07  X
1   0   0  -5  -1  -1   2   3  2
2   0  -1  -4  -3  -3  -3  -3  2
3   2   0   2   3   1   3   3  6
4   0   1  -4   1  -1   1   1  7
5   4   4   3   3   3   4   4 12
6   1   4   2  -3   0   0   0 15
7  10   9   8   9   7   7   7 22
8  10  12  12  12  10  12   9 24

I would like to apply this code:
from a former question of mine Mutate a new column and paste value from existing columns conditional on string in column names

library(dplyr)
library(stringr)
df %>% 
  rowwise %>%
  mutate(new_col = get(str_c('A0', X))) %>%
  ungroup

I get the error:

Error: Problem with `mutate()` column `new_col`.
i `new_col = get(str_c("A0", X))`.
x object 'A012' not found
i The error occurred in row 5.
Run `rlang::last_error()` to see where the error occurred.

And I know the reason: The reason is that the code try to get a A012 column because of row 5 in X column = 12. But there is no column A012.

Desired_output:

   A01 A02 A03 A04 A05 A06 A07  X new_col
1   0   0  -5  -1  -1   2   3  2    0
2   0  -1  -4  -3  -3  -3  -3  2    -1
3   2   0   2   3   1   3   3  6    3
4   0   1  -4   1  -1   1   1  7    1
5   4   4   3   3   3   4   4 12    NA
6   1   4   2  -3   0   0   0 15    NA
7  10   9   8   9   7   7   7 22    NA
8  10  12  12  12  10  12   9 24    NA

>Solution :

One option to achieve your desired result would be via an if condition:

library(dplyr)
library(stringr)
df %>% 
  rowwise() %>%
  mutate(new_col = if (str_c('A0', X) %in% names(.)) get(str_c('A0', X)) else NA) %>%
  ungroup()
#> # A tibble: 8 × 9
#>     A01   A02   A03   A04   A05   A06   A07     X new_col
#>   <int> <int> <int> <int> <int> <int> <int> <int>   <int>
#> 1     0     0    -5    -1    -1     2     3     2       0
#> 2     0    -1    -4    -3    -3    -3    -3     2      -1
#> 3     2     0     2     3     1     3     3     6       3
#> 4     0     1    -4     1    -1     1     1     7       1
#> 5     4     4     3     3     3     4     4    12      NA
#> 6     1     4     2    -3     0     0     0    15      NA
#> 7    10     9     8     9     7     7     7    22      NA
#> 8    10    12    12    12    10    12     9    24      NA