-1

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.

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
potatout
  • 187
  • 1
  • 11

1 Answers1

-1

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
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 5
    Obviously there are more than one key... looks like long to wide dupe? – zx8754 Sep 02 '16 at 07:28
  • 1
    Thank you, akrun and zx8754, there is only one key column named "key". and obiviously, more the one key for this data frame.for instance, key = 1,2,3,4,5. that means i have group these data for 5 rows – potatout Sep 02 '16 at 07:37
  • Hi i have tried on my pc by using your code. but it does not work for my on my data. it says no rowid function and no data there – potatout Sep 02 '16 at 09:36
  • @YamPakChing My solution is based on the input you provided. The `rowid` is from the devel version of `data.table` which you need to install. – akrun Sep 02 '16 at 09:40
  • @YamPakChing I updated with an option that doesn't need the devel version. – akrun Sep 02 '16 at 09:44
  • 1
    Hi Thank you, akrun, i will vote for you. sorry, just one more question. since my true data includes character and other types. does it work for my data? – potatout Sep 02 '16 at 09:50
  • @YamPakChing It should work for your data as the `data.table` `dcast` can take multiple `value.var` and it can be of different types. – akrun Sep 02 '16 at 09:57