How to rearrange format of datatable in R?

Question

I need to arrange the table into following format to make others clear to see which name is duplicate and the corresponding value

#original df
df <- data.frame(name=c('a','a','a','b','b'),
             value=(c(1,2,3,4,5)),stringsAsFactors = FALSE)
#df
name    value
a        1
a        2
a        3
b        4
b        5

#target df
name1    value1         name2    value2    name3    value3
a        1               a        2          a        3
b        4               b        5          NA       NA

Wish the solution can extend columns automatically. If 'a' is duplicated 4 times then the target df should be 8 columns (4 pairs of 'name' and 'value')

Thank you

score 3 · Accepted Answer · answered Jul 05 '18 at 06:44

Here is a data.table solution. You can use rleid to create an index (id) within each name, then pivot the data using name against that id

library(data.table)
setDT(df)
df[, id := rleid(value), by=name]
dcast(df, name ~ id, value.var="value")

output:

   name 1 2  3
1:    a 1 2  3
2:    b 4 5 NA

How to rearrange format of datatable in R?

1 Answers1