Yam Pak Ching Yam Pak Ching - 3 months ago 8
R Question

Merge data with same key to one row

I have a data frame with several data like this

Key A B C
1 1 2 3
1 4 6 8
1 3 2 1


i need to merge these data with same key into one row just like this

Key A1 B1 C1 A2 B2 C2 A3 B3 C3
1 1 2 3 4 6 8 3 2 1


is there any easy way to get this result what i need.

Answer

If this is only a single group of 'key', one option would be to use base R. We transpose (t) the dataset except the first column (df[-1]), concatenate to a vector, convert to data.frme and cbind with the first element of the first column.

res <- cbind(df1[1,1], as.data.frame.list(c(t(df1[-1]))))
names(res) <- c(names(df1)[1] ,make.unique(rep(names(df1)[-1],3)))
res
#  Key A B C A.1 B.1 C.1 A.2 B.2 C.2
#1   1 1 2 3   4   6   8   3   2   1

Or use data.table i.e. the development version v1.9.7 if there are many 'Key' elements

library(data.table)
dcast(setDT(df1), Key~rowid(Key), value.var = c("A", "B", "C"),sep="")
#   Key A1 A2 A3 B1 B2 B3 C1 C2 C3
#1:   1  1  4  3  2  6  2  3  8  1

if we have a data.table with version < v1.9.7, we need to create the sequence column grouped by 'Key'.

dcast(setDT(df1)[, rn := 1:.N , Key], Key ~rn, value.var = c("A", "B", "C"),sep="")
#   Key A1 A2 A3 B1 B2 B3 C1 C2 C3
#1:   1  1  4  3  2  6  2  3  8  1
Comments