This question already has an answer here:
-
Reshape wide format, to multi-column long format
4 answers
I have a question about re-shaping (if that's the right word) a data frame to a transposed version of it. I want to take something like:
A B C
1 6 1
1 18 1
1 21 1
3 18 1
3 21 1
4 6 1
4 18 1
4 20 1
4 21 1
And turn it into a dataframe like:
A B_1 C_1 B_2 C_2 B_3 C_3 ...
1 6 1 18 1 21 1
3 18 1 21 1
4 6 1 18 1 20 1 21 1
Is there some go-to function in R that I'm unaware of or should I loop over this process?
Thanks!
DF <- read.table(text="A B C
1 6 1
1 18 1
1 21 1
3 18 1
3 21 1
4 6 1
4 18 1
4 20 1
4 21 1",header=TRUE)
First create an index variable:
library(plyr)
DF <- ddply(DF,.(A),transform,No=seq_along(A))
# A B C No
# 1 1 6 1 1
# 2 1 18 1 2
# 3 1 21 1 3
# 4 3 18 1 1
# 5 3 21 1 2
# 6 4 6 1 1
# 7 4 18 1 2
# 8 4 20 1 3
# 9 4 21 1 4
Now melt the data.frame and cast to wide format:
library(reshape2)
DF <- melt(DF,id.vars=c("A","No"))
res <- dcast(DF,A~No+variable)
# A 1_B 1_C 2_B 2_C 3_B 3_C 4_B 4_C
# 1 1 6 1 18 1 21 1 NA NA
# 2 3 18 1 21 1 NA NA NA NA
# 3 4 6 1 18 1 20 1 21 1
Order in the formula matters:
res <- dcast(DF,A~variable+No)
# A B_1 B_2 B_3 B_4 C_1 C_2 C_3 C_4
# 1 1 6 18 21 NA 1 1 1 NA
# 2 3 18 21 NA NA 1 1 NA NA
# 3 4 6 18 20 21 1 1 1 1
