How to transform a dataframe into a matrix of combinations

Andres Gonzalez 0
Viewed 33

I have a data frame of one row that contains the results of an equation of two variables (let's call them X and Y; for simplicity, the equation is a sum). I need to transform that dataframe into a matrix where I can see the combinations of those variables. This is a reproducible example:

x1_y1 <- 2 #Here x = 1, y = 1
x1_y2 <- 3 #Here x = 1, y = 2
x2_y1 <- 3 #Here x = 2, y = 1
x2_y2 <- 4 #Here x = 2, y = 2

df <- data.frame(x1_y1, x1_y2, x2_y1, x2_y2)

I would like the output to be something like this:

enter image description here

Does anybody know how to achieve this? Thanks in advance!

matrix r
3 Answers

A tapply attempt after extracting the name components via strcapture:

tapply(unlist(df), strcapture("(.+)_(.+)", names(df), proto=list(x="",y="")), I)
#    y
#x    y1 y2
#  x1  2  3
#  x2  3  4
thelatemail0

This works ...

base R

(Similar to thelatemail's answer, though ever-so-slightly different ... and written at the same time :-)

tmp <- cbind(strcapture("(.+)_(.+)", names(df), list(x="", y="")), val = unlist(df, use.names = FALSE))
tmp <- reshape2::dcast(x ~ y, value.var = "val", data = tmp)
rownames(tmp) <- tmp[[1]]
tmp <- tmp[,-1]
as.matrix(tmp)
#    y1 y2
# x1  2  3
# x2  3  4

dplyr

Perhaps a bit contrived ...

library(dplyr)
library(tidyr)
tmp <- df %>%
  pivot_longer(everything()) %>%
  separate(name, sep = "_", into = c("x", "y")) %>%
  pivot_wider(x, names_from = "y", values_from = "value") %>%
  as.data.frame()
rownames(tmp) <- tmp[[1]]
tmp <- tmp[,-1]
as.matrix(tmp)
#    y1 y2
# x1  2  3
# x2  3  4
r2evans0

A tidyr solution:

library(tidyr)

df %>%
  pivot_longer(everything(), names_to = c("x", ".value"), names_sep = "_") %>%
  tibble::column_to_rownames("x")

#    y1 y2
# x1  2  3
# x2  3  4

The output is still a data.frame. You could pass it to as.matrix() if necessary.

Darren Tsai0
Related