merge dataframes in r using values in the first dataframe and column names in the second one

Question

I have two dataframes df1 and df2. In my real dataframes, I have indices, so I added them here in the reproducible example too.

object <- c("x1", "x2", "x3")
color <- c("red", "green", "blue")
shape <- c("square", "circle", "square")

df1 <- data.frame(object, color, shape, row.names = NULL)
df1

            object color  shape
    1           x1   red square
    2           x2 green circle
    3           x3  blue square

param <- c("weight", "len", "volume")
x1 <- c("value", "value", "value")
x2 <- c("value", "value", "value")
x3 <- c("value", "value", "value")
x4 <- c("value", "value", "value")
x5 <- c("value", "value", "value")

df2 <- data.frame(param, x1, x2, x3, x4, x5, row.names = NULL)
df2

     param    x1    x2    x3    x4    x5
  1 weight value value value value value
  2 len    value value value value value
  3 volume value value value value value

df1's object column has the same values and the df2's column names (except the param column). df2 has more X columns than the number of df1's rows. x1, x2, x3, ... orders do not match in two data frames. I need to combine two datasets matching x's and get something like this:

            x1     x2     x3
weight   value  value  value
len      value  value  value
volume   value  value  value
color      red  green   blue
shape   square circle square

Answer 1

The first dataset can be reshaped better with data.table::transpose, then bind the second dataset with intersecting columns from 'df1new', and set the 'param' column to row names attribute with column_to_rownames (from tibble)

library(dplyr)
df1new <- data.table::transpose(df1, make.names = 'object', keep.names ='param') 
bind_rows(df2 %>%
       select(any_of(names(df1new))), df1new) %>%
       column_to_rownames('param')

-output

#            x1     x2     x3
#weight  value  value  value
#len     value  value  value
#volume  value  value  value
#color     red  green   blue
#shape  square circle square

Answer 2

Here is an option using dcast + melt like below

library(reshape2)
p <- dcast(melt(df1, id = "object", variable.name = "param"), param ~ object)
rbind(df2[names(p)], p)

which gives

   param     x1     x2     x3
1 weight  value  value  value
2    len  value  value  value
3 volume  value  value  value
4  color    red  green   blue
5  shape square circle square

Answer 3

dplyr-only version requiring two steps (longer then wider) rather than a single data.table::transpose. Would love to know if it's possible in dplyr in one step

library(dplyr)

df1 %>%
  pivot_longer(names_to = 'param', cols = -object) %>%
  pivot_wider(id_cols = param, names_from = object) %>%
  bind_rows(df2, .) %>%
  select(c('param', df1$object))

Answer 4

library(dplyr)
library(tibble)

as.data.frame(t(df1[-1])) %>% 
  `names<-`(df1$object) %>% 
  rownames_to_column("param") %>%
  bind_rows(df2, .) %>% 
  select(where(~all(!is.na(.))))

Output

   param     x1     x2     x3
1 weight  value  value  value
2    len  value  value  value
3 volume  value  value  value
4  color    red  green   blue
5  shape square circle square