CODEWITHSUNDEEP
rreshape2
Unstack
Unstack

Reputation: 561

How to rearrange data into one record per row?

I'm having an issue with understanding how to satisfactorily rearrange my data using R. I have the data:

id     animal    colour    value1    value2    value3   etc.
1      pig       blue      3.1       4.0       1.2            
1      pig       orange    3.05      4.1       NA                
1      pig       pink      3.05      3.9       1.25            
1      pig       mauve     2.95      4.0       NA                 
1      elephant  blue      15.03     4.03      11.0          
1      elephant  pink      15.13     4.0       14.95
2      pig       blue      4.1       ...

and I would like to obtain something like (column names/order don't matter):

id     pig_blue_value1  pig_blue_value2  pig_blue_value3  pig_orange_value1  pig_orange_value2 etc.
1      3.1              4.0              1.2              3.05               4.1
2      4.1              ...

I've played around with melting and casting from reshape2, but have not managed anything satisfactory so far. Thanks.

bluemoo <- data.frame(id=c(1,1,1,1,1,1,2),animal=c("pig","pig","pig","pig","elephant","elephant","pig"),colour=c("blue","orange","pink","mauve","blue","pink","blue"),value1=c(3.1,3.05,3.05,2.95,15.03,15.13,4.1),value2=c(4.0,4.1,3.9,4.0,4.03,4.0,NA),value3=c(1.2,NA,1.25,NA,11.0,14.95,NA))

Upvotes: 1

Views: 47

Answers (1)

David Arenburg
David Arenburg

Reputation: 92302

Try this using the newest data.table version on CRAN

library(data.table) # v 1.9.6+
dcast(setDT(bluemoo), id ~ animal + colour, value.var = names(bluemoo)[-(1:3)])
#    id value1_elephant_blue value1_elephant_pink value1_pig_blue value1_pig_mauve value1_pig_orange value1_pig_pink value2_elephant_blue
# 1:  1                15.03                15.13             3.1             2.95              3.05            3.05                 4.03
# 2:  2                   NA                   NA             4.1               NA                NA              NA                   NA
#    value2_elephant_pink value2_pig_blue value2_pig_mauve value2_pig_orange value2_pig_pink value3_elephant_blue value3_elephant_pink
# 1:                    4               4                4               4.1             3.9                   11                14.95
# 2:                   NA              NA               NA                NA              NA                   NA                   NA
#    value3_pig_blue value3_pig_mauve value3_pig_orange value3_pig_pink
# 1:             1.2               NA                NA            1.25
# 2:              NA               NA                NA              NA

Upvotes: 4

Related Questions