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

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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}

CodePudding user response：

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)