Reputation: 720
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
Upvotes: 1
Views: 248
Reputation: 887991
The first dataset can be reshaped better with data.table::transpose
, then bind the second dataset with intersec
ting 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
Upvotes: 2
Reputation: 18752
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
Upvotes: 2
Reputation: 66
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))
Upvotes: 2
Reputation: 102920
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
Upvotes: 2