Yam Pak Ching - 1 year ago 52

R Question

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 Source

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
```