CODEWITHSUNDEEP
rdataframetidyverse
Euler_Salter
Euler_Salter

Reputation: 3571

Merge two columns containing NA values in complementing rows

Suppose I have this dataframe

df <- data.frame(
    x=c(1, NA, NA, 4, 5, NA),
    y=c(NA, 2, 3, NA, NA, 6)

which looks like this

   x  y
1  1 NA
2 NA  2
3 NA  3
4  4 NA
5  5 NA
6 NA  6

How can I merge the two columns into one? Basically the NA values are in complementary rows. It would be nice to also obtain (in the process) a flag column containing 0 if the entry comes from x and 1 if the entry comes from y.

Upvotes: 4

Views: 3889

Answers (3)

akrun
akrun

Reputation: 887901

We can also use base R methods with max.col on the logical matrix to get the column index, cbind with row index and extract the values that are not NA

df$merged <- df[cbind(seq_len(nrow(df)), max.col(!is.na(df)))]
df$flag <- +(!is.na(df$y))
df
#   x  y merged flag
#1  1 NA      1    0
#2 NA  2      2    1
#3 NA  3      3    1
#4  4 NA      4    0
#5  5 NA      5    0
#6 NA  6      6    1

Or we can use fcoalesce from data.table which is written in C and is multithreaded for numeric and factor types.

library(data.table)
setDT(df)[, c('merged', 'flag' ) := .(fcoalesce(x, y), +(!is.na(y)))]
df
#    x  y merged flag
#1:  1 NA      1    0
#2: NA  2      2    1
#3: NA  3      3    1
#4:  4 NA      4    0
#5:  5 NA      5    0
#6: NA  6      6    1

Upvotes: 1

Nareman Darwish
Nareman Darwish

Reputation: 1261

You can do that using dplyr as follows;

library(dplyr)

# Creating dataframe
df <- 
  data.frame(
    x = c(1, NA, NA, 4, 5, NA),
    y = c(NA, 2, 3, NA, NA, 6))

df %>%
  # If x is null then replace it with y
  mutate(merged = coalesce(x, y),
         # If x is null then put 1 else put 0
         flag = if_else(is.na(x), 1, 0))

# x  y merged flag
# 1 NA      1    0
# NA  2      2    1
# NA  3      3    1
# 4 NA      4    0
# 5 NA      5    0
# NA  6      6    1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

We can try using the coalesce function from the dplyr package:

df$merged <- coalesce(df$x, df$y)
df$flag <- ifelse(is.na(df$y), 0, 1)
df

   x  y merged flag
1  1 NA      1    0
2 NA  2      2    1
3 NA  3      3    1
4  4 NA      4    0
5  5 NA      5    0
6 NA  6      6    1

Upvotes: 5

Related Questions