2

I want to change a table from long to wide. It has multiple values and a categorical variables.

table looks like: 

df <- data.frame(name = c("john", "mary", "john", "mary"), 
variable = c("math", "math", "science", "science"), 
grade = c("sixth", "sixth", "sixth", "sixth"), 
val1 = c(78, 88, 97, 100), val2 = c(92, 83, 69, 94))

What I want is this: 

want <- data.frame(name = c("john", "mary"), grade = c("sixth", "sixth"),
math.val1 = c(78, 88), math.val2 = c(92, 83), science.val1 = c(97, 100), 
science.val2 = c(69, 94))

Without the grade column, I am able to achieve this easily: 

reshape(df, idvar='name', timevar='variable', direction='wide')

With the "grade" column, I get : 

  name grade.math val1.math val2.math grade.science val1.science val2.science
1 john      sixth        78        92         sixth           97           69
2 mary      sixth        88        83         sixth          100           94

How can I correct this?

Thanks.

vagabond
  • 3,526
  • 5
  • 43
  • 76
  • 2
    In R the term "aggregate" means you are collapsing data with grouping criterai with a summary measure such as `mean` or `sd`. This instance is not well described as "aggregation". – IRTFM Mar 19 '15 at 23:39
  • right. i've corrected it. – vagabond Mar 20 '15 at 01:14
  • honestly, this wasn't a good question - should I close / delete it? Is that the right thing to do? – vagabond Mar 20 '15 at 01:31
  • Too late now. Questions with upvoted answers cannot be deleted... and I didn't think it was a bad question. – IRTFM Mar 20 '15 at 01:56

2 Answers2

3

You need to include 'grade' into idvar.

reshape(df, idvar=c('name', 'grade'), timevar='variable', direction='wide')
Michele Usuelli
  • 1,970
  • 13
  • 15
3

Using the devel version of data.table i.e. v1.9.5, reshaping multiple value columns is possible with dcast. It can be installed from here

library(data.table)
dcast(setDT(df), name+grade~variable, value.var=c('val1', 'val2'))
#   name grade math_val1 science_val1 math_val2 science_val2
#1: john sixth        78           97        92           69
#2: mary sixth        88          100        83           94
akrun
  • 874,273
  • 37
  • 540
  • 662
  • I tried this reading in the other similar question . Could not install from github due to admin restrictions. Is this being updated in r-cran soon ? – vagabond Mar 20 '15 at 02:57
  • @vagabond I don't know when it will be updated. This `dcast` approach is very new. – akrun Mar 20 '15 at 02:58