I would like to use the reshape2 package in R to change my long table into a wide table.
I have a data set from database which is like this (example):
id1 | id2 | info | action_time |
1 | a | info1 | time1 |
1 | a | info1 | time2 |
1 | a | info1 | time3 |
2 | b | info2 | time4 |
2 | b | info2 | time5 |
And now I want it to be like this:
id1 | id2 | info |action_time 1|action_time 2|action_time 3|
1 | a | info1 | time1 | time2 | time3 |
2 | b | info2 | time4 | time5 | |
I have tried several times and looked up some examples on some website using reshape() or dcast() but couldn't find such example like this. The number of action_time for each id is different and for some of the ids they may have more than 10 action_times so in that case the reshaped data set will have more than 10 columns of action_time.
Anyone can think of a handy way of doing this? If there is a way of doing this in excel(Pivot Table?) it would be awesome as well. Thank heaps
Try:
library(dplyr)
library(tidyr)
df %>%
group_by(id1) %>%
mutate(action_no = paste("action_time", row_number())) %>%
spread(action_no, action_time)
Which gives:
#Source: local data frame [2 x 6]
#
# id1 id2 info action_time 1 action_time 2 action_time 3
#1 1 a info1 time1 time2 time3
#2 2 b info2 time4 time5 NA
Data
df <- structure(list(id1 = c(1, 1, 1, 2, 2), id2 = structure(c(1L,
1L, 1L, 2L, 2L), .Label = c("a", "b"), class = "factor"), info = structure(c(1L,
1L, 1L, 2L, 2L), .Label = c("info1", "info2"), class = "factor"),
action_time = structure(1:5, .Label = c("time1", "time2",
"time3", "time4", "time5"), class = "factor")), .Names = c("id1",
"id2", "info", "action_time"), class = "data.frame", row.names = c(NA, -5L))
Using tidyr
require(tidyr)
# replicate data
df <- structure(list(id1 = c(1, 1, 1, 2, 2), id2 = structure(c(1L,
1L, 1L, 2L, 2L), .Label = c(" a ", " b "), class = "factor"),
info = structure(c(1L, 1L, 1L, 2L, 2L), .Label = c(" info1 ",
" info2 "), class = "factor"), action_time = structure(1:5, .Label = c(" time1 ",
" time2 ", " time3 ", " time4 ", " time5 "
), class = "factor")), .Names = c("id1", "id2", "info", "action_time"
), class = "data.frame", row.names = c(NA, -5L))
# create additional column on action_time sequence
action_no <- paste("action_time",
unlist(sapply(rle(df$id1)$lengths, function(x) seq(1, x))))
y <- cbind(df, action_no)
# spread into final dataframe
z <- spread(y, action_no, action_time)
Final output
> z
id1 id2 info action_time 1 action_time 2 action_time 3
1 1 a info1 time1 time2 time3
2 2 b info2 time4 time5 <NA>
