A more elegant way to combine two vectors as separate columns (or dataframes), match the rows, and have NA where they do not match

user_9439426 0
Viewed 477

I have two vectors of the same 'thing' that I want to combine into a dataframe. Each vector will become its own column, but they will match up the rows are the same and introduce NA values for one vector where it does not match the second vector. Since the data starts as just two vectors, there are no common id values or anything to match up other than the vector values.

I got this to work in a toy data test using a simple and straightforward approach, but would like to know if there is a more direct and elegant way to do this.

My current approach requires assigning a unique value by which I can then merge the two vectors, but I am curious if I can do this without it and rely instead on the vector values. My other attempts tried to not adopt a new id value, exploring functions like merge and join, cbind, rbind, bind_rows, bind_cols, intersect and union. Perhaps I wasn't using them as well as I could. I found some other useful posts on SO (like this one), but they all already start with a unique identifier.

Here is my toy data test with a final output how I want it to look. It does not matter to me if the final output has an id column or not. Note, my actual data will be character, hence my use of letters here.

# create toy data
x <- letters[1:5]
y <- letters[2:6]
# combine into dataframe, keep only unique values & assign id
xy <- data.frame(xy=unique(c(x,y))); xy
xy$id <- 1:length(xy$xy); xy
# match id back to original toy data as dataframes
x <- data.frame(x)
x$id <- match(x$x, xy$xy)
y <- data.frame(y)
y$id <- match(y$y, xy$xy)
# merge using id
xy2 <- merge(x, y, by="id", all=TRUE)
xy2
# results in
  id    x    y
1  1    a  <NA>
2  2    b    b
3  3    c    c
4  4    d    d
5  5    e    e
6  6 <NA>    f
match dataframe vector r merge
2 Answers

Using tidyverse you can try using full_join and create keys based on your 2 vectors:

library(tidyverse)

full_join(data.frame(key=x, x),
          data.frame(key=y, y), by="key") %>%
  select(-key)

Alternatively, you can just use merge in base R:

merge(data.frame('key'=x, x), data.frame('key'=y, y), by='key', all=T)[-1]

Output

     x    y
1    a <NA>
2    b    b
3    c    c
4    d    d
5    e    e
6 <NA>    f
user_34606700

Here's an alternative one-liner in base R:

cbind(x[match(unique(c(x, y)), x)], y[match(unique(c(x, y)), y)])
#>      [,1] [,2]
#> [1,] "a"  NA  
#> [2,] "b"  "b" 
#> [3,] "c"  "c" 
#> [4,] "d"  "d" 
#> [5,] "e"  "e" 
#> [6,] NA   "f"
user_125003150
Related