Assuming this is my dataset.
Type Drat Qsec Wt
Mazda 3.9 16.46 2.620
Toyota 3.85 17.02 2.875
Ford 3.15 3.44 17.02
Duster 3.21 15.84 18.3
I am interested in tranforming this dataset to a format like this
Type.x Drat.x Qsec.x Wt.x Type.y Drat.y Qsec.y Wt.y
Mazda 3.9 16.46 2.620 Toyota 3.85 17.02 2.875
Mazda 3.9 16.46 2.620 Ford 3.15 3.44 17.02
Mazda 3.9 16.46 2.620 Duster 3.21 15.84 18.3
Toyota 3.85 17.02 2.875 Ford 3.15 3.44 17.02
Toyota 3.85 17.02 2.875 Duster 3.21 15.84 18.3
Ford 3.15 3.44 17.02 Duster 3.21 15.84 18.3
1st row - 2nd row
1st row - 3rd row
1st row - 4th row
2nd row - 3rd row
2nd row - 4th row
3rd row - 4th row
I am not exactly clear how to do this efficiently. Any suggestions are much appreciated.
Assume your dataframe is called df; I have created an example df like this:
df <- mtcars[2:5,c("drat","qsec","wt")]
df$Type <- rownames(df)
> df
drat qsec wt Type
Mazda RX4 Wag 3.90 17.02 2.875 Mazda RX4 Wag
Datsun 710 3.85 18.61 2.320 Datsun 710
Hornet 4 Drive 3.08 19.44 3.215 Hornet 4 Drive
Hornet Sportabout 3.15 17.02 3.440 Hornet Sportabout
We can use dplyr with a sequence of combinations, transformations, and joins.
library(dplyr)
t(combn(df$Type,2)) %>%
as.data.frame() %>%
rename(Type.x = V1,Type.y = V2) %>%
inner_join(df,by = c("Type.x" = "Type")) %>%
inner_join(df,by = c("Type.y" = "Type"))
Type.x Type.y drat.x qsec.x wt.x drat.y qsec.y wt.y
1 Mazda RX4 Wag Datsun 710 3.90 17.02 2.875 3.85 18.61 2.320
2 Mazda RX4 Wag Hornet 4 Drive 3.90 17.02 2.875 3.08 19.44 3.215
3 Mazda RX4 Wag Hornet Sportabout 3.90 17.02 2.875 3.15 17.02 3.440
4 Datsun 710 Hornet 4 Drive 3.85 18.61 2.320 3.08 19.44 3.215
5 Datsun 710 Hornet Sportabout 3.85 18.61 2.320 3.15 17.02 3.440
6 Hornet 4 Drive Hornet Sportabout 3.08 19.44 3.215 3.15 17.02 3.440