Forming a new column from whichever of two columns isn’t NA

Question

I have a simplified dataframe:

test <- data.frame(
        x = c(1,2,3,NA,NA,NA),
        y = c(NA, NA, NA, 3, 2, NA),
        a = c(NA, NA, NA, NA, NA, TRUE)
        )

I want to create a new column rating that has the value of the number in either column x or column y. The dataset is such a way that whenever there's a numeric value in x, there's a NA in y. If both columns are NAs, then the value in rating should be NA.

In this case, the expected output is: 1,2,3,3,2,NA

you do `test$rating <- ifelse(is.na(test$x), test$y, test$x)` — Ric, Dec 23 '22 at 15:29

score 4 · Accepted Answer · answered Dec 23 '22 at 15:32

4

With coalesce:

library(dplyr)
test %>% 
  mutate(rating = coalesce(x, y))

   x  y    a rating
1  1 NA   NA      1
2  2 NA   NA      2
3  3 NA   NA      3
4 NA  3   NA      3
5 NA  2   NA      2
6 NA NA TRUE     NA

answered Dec 23 '22 at 15:32

Maël

45,206
3
29
67

score 1 · Answer 2 · answered Dec 23 '22 at 15:31

1

library(dplyr)
test %>%
  mutate(rating = if_else(is.na(x), 
                          y, x))
   x  y    a rating
1  1 NA   NA      1
2  2 NA   NA      2
3  3 NA   NA      3
4 NA  3   NA      3
5 NA  2   NA      2
6 NA NA TRUE     NA

answered Dec 23 '22 at 15:31

Chris Ruehlemann

20,321
4
12
34

score 0 · Answer 3 · edited Dec 23 '22 at 16:38

0

test <- data.frame(
 x = c(1,2,3,NA,NA,NA),
  y = c(NA, NA, NA, 3, 2, NA),
  a = c(NA, NA, NA, NA, NA, TRUE)
)

test$rating <- dplyr::coalesce(test$x, test$y)

edited Dec 23 '22 at 16:38

Dan Adams

4,971
9
28

answered Dec 23 '22 at 16:01

User 123732

124
9

2

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Dec 24 '22 at 00:27
1

Ok sorry I'll make note – User 123732 Dec 24 '22 at 01:24

score 0 · Answer 4 · answered Dec 23 '22 at 16:11

Here several solutions.

# Input
test <- data.frame(
  x = c(1,2,3,NA,NA,NA),
  y = c(NA, NA, NA, 3, 2, NA),
  a = c(NA, NA, NA, NA, NA, TRUE)
)

# Base R solution
test$rating <- ifelse(!is.na(test$x), test$x,
                      ifelse(!is.na(test$y), test$y, NA))

# dplyr solution
library(dplyr)
test <- test %>%
  mutate(rating = case_when(!is.na(x) ~ x,
                            !is.na(y) ~ y,
                            TRUE ~ NA_real_))

# data.table solution
library(data.table)
setDT(test)
test[, rating := ifelse(!is.na(x), x, ifelse(!is.na(y), y, NA))]

^{Created on 2022-12-23 with reprex v2.0.2}

Forming a new column from whichever of two columns isn’t NA

4 Answers4