Long to wide with multiple columns

Question

Suggestions on how to smoothly switch from foo to foo2 (preferably with tidyr or reshape2 packages)?

This is similar to this question , but not quite so, I think, because I do not want a column of columns, just expand a few columns. This is also kind of this question , but again, I don’t think I want the columns to change with the row value, as in this answer. Or, the correct answer to this question is to convince me of this, as with one of the others. The solution in the second question “two dcasts plus merge” is the most attractive now, because I understand it.

Foo:

foo = data.frame(group=c('a', 'a', 'b', 'b', 'c', 'c'), times=c('before', 'after', 'before', 'after', 'before', 'after'), action_rate=c(0.1,0.15, 0.2, 0.18,0.3, 0.35), num_users=c(100, 100, 200, 200, 300, 300)) foo <- transform(foo, action_rate_c95 = 1.95 * sqrt(action_rate*(1-action_rate)/num_users)) > foo group times action_rate num_users action_rate_c95 1 a before 0.10 100 0.05850000 2 a after 0.15 100 0.06962893 3 b before 0.20 200 0.05515433 4 b after 0.18 200 0.05297400 5 c before 0.30 300 0.05159215 6 c after 0.35 300 0.05369881 

foo2:

 foo2 <- data.frame(group=c('a', 'b', 'c'), action_rate_before=c(0.1,0.2, 0.3), action_rate_after=c(0.15, 0.18,0.35), action_rate_c95_before=c(0.0585,0.055, 0.05159), action_rate_c95_after=c(0.069, 0.0530,0.0537), num_users=c(100, 200, 300)) > foo2 group action_rate_before action_rate_after action_rate_c95_before 1 a 0.1 0.15 0.0585 2 b 0.2 0.18 0.0550 3 c 0.3 0.35 0.05159 action_rate_c95_after num_users 1 0.0690 100 2 0.0530 200 3 0.0537 300 

Answer 1

Here is another alternative using tidyr :

 library(tidyr) foo %>% gather(key, value, -group, -times, -num_users) %>% unite(col, key, times) %>% spread(col, value) 

What gives:

 # group num_users action_rate_after action_rate_before action_rate_c95_after #1 a 100 0.15 0.1 0.06962893 #2 b 200 0.18 0.2 0.05297400 #3 c 300 0.35 0.3 0.05369881 # action_rate_c95_before #1 0.05850000 #2 0.05515433 #3 0.05159215 

Answer 2

You can use data.table instead of reshape2 because its dcast() function accepts several variables and works faster:

 require(data.table) setDT(foo) dcast(foo,group+num_users~times,value.var=c("action_rate","action_rate_c95")) group num_users action_rate_after action_rate_before action_rate_c95_after action_rate_c95_before 1: a 100 0.15 0.1 0.06962893 0.05850000 2: b 200 0.18 0.2 0.05297400 0.05515433 3: c 300 0.35 0.3 0.05369881 0.05159215 

Answer 3

Here is the base R option with reshape

 reshape(foo, idvar=c("group", "num_users"), timevar="times", direction="wide") # group num_users action_rate.before action_rate_c95.before action_rate.after #1 a 100 0.1 0.05850000 0.15 #3 b 200 0.2 0.05515433 0.18 #5 c 300 0.3 0.05159215 0.35 # action_rate_c95.after #1 0.06962893 #3 0.05297400 #5 0.05369881