Transform a matrix to two columns-CodePudding

I have a table with three columns like below

Group 1	Group 2	Group 3
1	2	3
5	21	32
10	32	50
12	20	60

I want to transform it to matrix shown below

Value	Group
1	Group 1
5	Group 1
10	Group 1
12	Group 1
2	Group 2
21	Group 2
32	Group 2
20	Group 2
3	Group 3
32	Group 3
50	Group 3
60	Group 3

How can I do this in R ?

CodePudding user response：

With base R, you can use:

> dt <- data.frame(Group1 = c(1,5,10,12), Group2 = c(2, 21, 32, 20), Group3 = c(3, 32, 50, 60))
> dt
  Group1 Group2 Group3
1      1      2      3
2      5     21     32
3     10     32     50
4     12     20     60
> as.data.frame(cbind(unlist(dt, use.names = FALSE), rep(names(dt), each = nrow(dt))))
   V1     V2
1   1 Group1
2   5 Group1
3  10 Group1
4  12 Group1
5   2 Group2
6  21 Group2
7  32 Group2
8  20 Group2
9   3 Group3
10 32 Group3
11 50 Group3
12 60 Group3

Edit: to address the concern that column V1 above is of class character. If, instead, this is required to be numeric, then we could do:

dt1 <- as.data.frame(cbind(unlist(dt, use.names = FALSE), rep(names(dt), each = nrow(dt))))
dt1$V1 <- as.numeric(dt1$V1)

Or, simply:

stack(dt)

as per the comment by @markus

CodePudding user response：

use melt:

library(data.table)
dat <- as.data.table(matrix)
melt(dat, measure.vars = names(dat))

CodePudding user response：

Another way by using dplyr:

dat <- read.table(text = "Group1    Group2  Group3
 1  2   3
 5  21  32
 10 32  50
 12 20  60", header = TRUE)
dat
#   Group1 Group2 Group3
# 1      1      2      3
# 2      5     21     32
# 3     10     32     50
# 4     12     20     60

newdat <- dat %>% pivot_longer(cols = 1:3, 
                               names_to = "Group", 
                               values_to = "Value") %>% 
                  arrange(Group) %>% 
                  relocate(Value)
newdat

# A tibble: 12 x 2
# Value Group 
# <int> <chr> 
# 1     1 Group1
# 2     5 Group1
# 3    10 Group1
# 4    12 Group1
# 5     2 Group2
# 6    21 Group2
# 7    32 Group2
# 8    20 Group2
# 9     3 Group3
# 10    32 Group3
# 11    50 Group3
# 12    60 Group3

CodePudding user response：

With tidyr:

library(tidyr)

data%>% 
gather(Value, Group) %>% 
relocate(Value, .after = Group)

Output:

       Group  Value
1      1 Group1
2      5 Group1
3     10 Group1
4     12 Group1
5      2 Group2
6     21 Group2
7     32 Group2
8     20 Group2
9      3 Group3
10    32 Group3
11    50 Group3
12    60 Group3

Sample data:

data <- read.table(text = "Group1    Group2  Group3
 1  2   3
 5  21  32
 10 32  50
 12 20  60", header = TRUE)