I was wondering how to generate a new dataframe containing all pairwise combinations of the value and the combined row and column name of another dataframe. To explain as an example I have the following dataframe:

# dataframe with col names a1:a5

df <- data.frame(a1 = c(4, 2, 6, 9, 13),
                 a2 = c(56, 1, 47, 2, 3),
                 a3 = c(4, 6, 9, 11, 85),
                 a4 = c(6, 15, 4, 12, 3),
                 a5 = c(54, 94, 3, 2, 75))
# and with rownames a1:a5
rownames(df) <- c("a1","a2","a3","a4","a5")

df now looks like this:

I need a new dataframe of all possible combinations (so 25x2) looking like this:

Thank you.

CodePudding user response：

You could convert the data to a table and back to a data.frame.

as.data.frame(as.table(t(df)))

#    Var1 Var2 Freq
# 1    a1   a1    4
# 2    a2   a1   56
# 3    a3   a1    4
# 4    a4   a1    6
# 5    a5   a1   54
# 6    a1   a2    2
# 7    a2   a2    1
# 8    a3   a2    6
# 9    a4   a2   15
# 10   a5   a2   94
# ...

CodePudding user response：

You can also put it in a long format:

library(tidyr)
library(dplyr)
 
 df %>%
  mutate(col1 = rownames(.)) %>%
  pivot_longer( -c(col1), values_to = "Value", names_to = "col2") %>%
  mutate(step = paste0(col1,col2)) %>%
  select(step, Value) %>%
  arrange(step)

# A tibble: 25 x 2
   step  Value
   <chr> <dbl>
 1 a1a1      4
 2 a1a2     56
 3 a1a3      4
 4 a1a4      6
 5 a1a5     54
 6 a2a1      2
 7 a2a2      1
 8 a2a3      6
 9 a2a4     15
10 a2a5     94
# ... with 15 more rows