Using mutate_at to create new column using multiple other columns in a data frame

Question

Given I have a data frame like this:

x <- data.frame(d1 = c(1,0,1,3,5), d2 = c(0,0,1,1,0))

#   d1 d2
# 1  1  0
# 2  0  0
# 3  1  1
# 4  3  1
# 5  5  0

How can I create a new column equal to 1 if either c1 or c2 is equal to 1, and 0 if neither is? Like this:

#   d1 d2 R
# 1  1  0 1
# 2  0  0 0
# 3  1  1 1
# 4  3  1 1
# 5  5  0 0

Using dplyr, I'm able to accomplish this by first splitting the data frame using filter_at then binding the resulting data frames together again.

x1 <- x %>%
  filter_at(vars(starts_with("d")), any_vars(. == 1))
x1$R <- 1

x2 <- x %>%
  filter_at(vars(starts_with("d")), all_vars(. != 1))
x2$R <- 0

x <- bind_rows(x1,x2)

#   d1 d2 R
# 1  1  0 1
# 2  1  1 1
# 3  3  1 1
# 4  0  0 0
# 5  5  0 0

However not only does this method seem like a roundabout way to complete this task, the data frame order gets changed. Not an issue in my case, but might be annoying in other situations. Does anyone know of a better way to do this?

Answer 1

If you are not sure how many d columns you'll have, you can reshape first.

library(dplyr)
library(tidyr)
x %>% 
  mutate(id = row_number()) %>% 
  pivot_longer(starts_with('d')) %>% 
  group_by(id) %>% 
  mutate(R = as.numeric(any(value == 1))) %>% 
  pivot_wider()

# A tibble: 5 x 4
# Groups:   id [5]
     id     R    d1    d2
  <int> <dbl> <dbl> <dbl>
1     1     1     1     0
2     2     0     0     0
3     3     1     1     1
4     4     1     3     1
5     5     0     5     0

Answer 2

If I am understanding your question correctly I would use the mutate function with an ifelse statement.

x <- data.frame(d1 = c(1,0,1,3,5), d2 = c(0,0,1,1,0))

# the | is a logical OR operator
x %>% mutate(R = ifelse(d1 ==1 | d2== 1,1,0))

   d1 d2 R
 1  1  0 1
 2  0  0 0
 3  1  1 1
 4  3  1 1
 5  5  0 0

Answer 3

Another option could be:

x %>%
 mutate(R = +(rowSums(select(., starts_with("d")) == 1) != 0))

  d1 d2 R
1  1  0 1
2  0  0 0
3  1  1 1
4  3  1 1
5  5  0 0

Answer 4

Learn More about ifelse here:

https://www.rdocumentation.org/packages/base/versions/3.6.1/topics/ifelse

Try this using data.table package:

library(data.table)
setDT(x)[,result:=ifelse(d1==1 | d2==1,1,0)]

Base R:

x$result <- ifelse(x$d1==1 | x$d2==1,1,0)

Answer 5

If the number of columns that start with d vary, then in base R you could do:

cbind(x, R = +Reduce("|",data.frame(x[startsWith(names(x),"d")]==1)))
  d1 d2 R
1  1  0 1
2  0  0 0
3  1  1 1
4  3  1 1
5  5  0 0