Search code examples
rdataframereshape2

Reshape a dataframe with X1, Y1,X2,Y2 to X, Y1, Y2 in R

I have a dataframe df with XY combinations as follows

> df <- data.frame(X1=c(1:4),Y1=c(16:13),X2=c(4:7),Y2=c(-1:-4))
> df
  X1 Y1 X2 Y2
1  1 16  4 -1
2  2 15  5 -2
3  3 14  6 -3
4  4 13  7 -4

and want to reshape dfto df2by merging X1 and X2to a new variable X adding NA where Y1 or Y2 is left without value.

The result would look like this

> df2
  X  Y1  Y2
1 1  16  NA
2 2  15  NA
3 3  14  NA
4 4  13  -1
5 5  NA  -2
6 6  NA  -3
7 7  NA  -4

What is the most efficient way to do this?

Solution

  • You can use dplyr::full_join:

    df2 <- dplyr::full_join(df[, c("X1", "Y1")], df[, c("X2", "Y2")], by = c("X1" = "X2"))
names(df2)[1] <- "X"
df2
#  X Y1 Y2
#1 1 16 NA
#2 2 15 NA
#3 3 14 NA
#4 4 13 -1
#5 5 NA -2
#6 6 NA -3
#7 7 NA -4