Mutate one column by filtering others in dplyr

Question

I have a dataset with several columns that are mutually exclusive. One of the columns contains the answer for that observation (e.g. "abc") while the rest of columns contains negative numbers that represent NA. As column A in the toy dataset below, a column always contains the same answer .

I want to create a new variable by merging the column with the answer for each observation. So far, I did it as below, but in the real dataset there are 50 columns for 2000 observations, so I'm looking for a more optimized (and more elegant) way to do it.

data <- tibble::tribble(
  ~id, ~A, ~B, ~C,
  "a", "ES01", "-1", "-2",
  "b", "-1","-3", "CH041",
  "c", "-2", "DDE24", "-1",
  "d", "ES01", "-3", "-1"
)

data %>% 
  dplyr::mutate(across(A:C, ~ ifelse(str_starts(.,"-"), "", .))) %>%
  dplyr::mutate(regions = paste0(A, B, C))

Thank you.

Answer 1

You can use tidyr::pivot_longer to transform your data into long format and then use filter afterwards:

data %>%
  pivot_longer(-id, names_to = "col", values_to = "answer") %>%
  filter(!grepl("\\d", answer))

# A tibble: 4 x 3
  id    col   answer
  <chr> <chr> <chr> 
1 a     A     abc   
2 b     C     def   
3 c     B     ghi   
4 d     A     abc