Search code examples
rformatreformatreformatting

combine different row's values in table in r

I need to re-format a table in R.

I have a table like this. 

ID  category   
1   a   
1   b   
2   c   
3   d   
4   a   
4   c  
5   a

And I want to reform it as 

ID  category1   category2  
1           a           b  
2           c        null  
3           d        null  
4           a           c  
5           a        null

Is this doable in R?

Solution

  • This is a very straightforward "long to wide" type of reshaping problem, but you need a secondary "id" (or "time") variable.

    You can try using getanID from my "splitstackshape" package and use dcast to reshape from long to wide. getanID will create a new column called ".id" that would be used as your "time" variable:

    library(splitstackshape)
dcast.data.table(getanID(mydf, "ID"), ID ~ .id, value.var = "category")
#    ID 1  2
# 1:  1 a  b
# 2:  2 c NA
# 3:  3 d NA
# 4:  4 a  c
# 5:  5 a NA