Search code examples
rdplyrmultidplyr

how to merge two data frame by rows of x and y but columns should be side (df1$x) by side (df2$y)?

I have two dataframes with same name of columns and rows. I would like to merge them by rows but columns need to be side by side as of df$x and df$y.

I tried so far but not getting output as required.

merge(df.test1, df.test2, by.x = "V1", by.y = "V1")

Output

    V1  2800M_15-0.5-1.x    2800M_15-0.5-2.x    2800M_15-0.5-3.x    cA_15-0.5-1.x   cA_15-0.5-2.x   2800M_15-0.5-1.y    2800M_15-0.5-2.y    2800M_15-0.5-3.y    cA_15-0.5-1.y   cA_15-0.5-2.y
1   RowA    1   1   1   1   1   1   1   1   1   1
2   RowB    1   1   1   1   1   1   1   1   1   1
3   RowC    1   1   1   1   1   1   1   1   1   1

Required Output

    V1  2800M_15-0.5-1.x    2800M_15-0.5-1.y    2800M_15-0.5-2.x    2800M_15-0.5-2.y    2800M_15-0.5-3.x    2800M_15-0.5-3.y    cA_15-0.5-1.x   cA_15-0.5-1.y   cA_15-0.5-2.x   cA_15-0.5-2.y
1   RowA    1   1   1   1   1   1   1   1   1   1
2   RowB    1   1   1   1   1   1   1   1   1   1
3   RowC    1   1   1   1   1   1   1   1   1   1
Solution

  • An option would be to reorder the columns based on the names i.e. order on the substring of column names without the .x or .y at the end, and use the order index as column indexing for rearranging the columns

    out <- merge(df.test1, df.test2, by.x = "V1", by.y = "V1")
out1 <- out[c(1, order(sub("\\.[xy]$", "", names(out)[-1])) + 1)]