Reputation: 35
Reshaping data set in reshape package
I am trying to reshape my data using Reshape package. I struggle to reshape it. I would be very grateful if you could help me.
Data are as follows:
structure(list(ID = 1:3, group = 1:3, v1 = c(1L, 1L, 1L), v2 = c(1L, 1L, 0L), v1.1 = 1:3, v2.1 = c(1L, 1L, 1L), v1.2 = c(1L, 0L, 1L ), v2.2 = c(0L, 1L, 1L), v1.3 = c(1L, 1L, 1L), v2.3 = c(1L, 1L, 1L)), .Names = c("ID", "group", "v1", "v2", "v1.1", "v2.1", "v1.2", "v2.2", "v1.3", "v2.3"), class = "data.frame", row.names = c(NA, -3L))
ID group X1 X2 X3 X4
ID group v1 v2 v1 v2 v1 v2 v1 v2
1 1 1 1 1 1 1 0 1 1
2 2 1 1 2 1 0 1 1 1
3 3 1 0 3 1 1 1 1 1
I want to have data like this. Many thanks for your help
ID group X v1 v2
1 1 1 1 1
1 1 2 1 1
1 1 3 1 0
1 1 4 1 1
2 2 1 1 1
2 2 2 2 1
2 2 3 0 1
2 2 4 1 1
3 3 1 1 0
3 3 2 3 1
3 3 3 1 1
3 3 4 1 1
Upvotes: 0
Views: 127
Answers (3)
Reputation: 193677
You could try merged.stack from my "splitstackshape" package, which you could apply like this:
library(splitstackshape)
merged.stack(
df, var.stubs = c("v1", "v2"),
sep = "var.stubs")[, .time_1 := NULL][, ind := sequence(.N),
by = c("ID", "group")][]
# ID group v1 v2 ind
# 1: 1 1 1 1 1
# 2: 1 1 1 1 2
# 3: 1 1 1 0 3
# 4: 1 1 1 1 4
# 5: 2 2 1 1 1
# 6: 2 2 2 1 2
# 7: 2 2 0 1 3
# 8: 2 2 1 1 4
# 9: 3 3 1 0 1
# 10: 3 3 3 1 2
# 11: 3 3 1 1 3
# 12: 3 3 1 1 4
Alternatively, in the same package, there's Reshape, which is a wrapper that attempts to simplify the use of base R's reshape(). It would be slower than merged.stack in the long run though.
To use it, first rename the columns named "v1" and "v2" to "v1.0" and "v2.0":
setnames(df, c("v1", "v2"), c("v1.0", "v2.0"))
Reshape(df, var.stubs = c("v1", "v2"), sep = ".")
# ID group time v1 v2
# 1: 1 1 1 1 1
# 2: 2 2 1 1 1
# 3: 3 3 1 1 0
# 4: 1 1 2 1 1
# 5: 2 2 2 2 1
# 6: 3 3 2 3 1
# 7: 1 1 3 1 0
# 8: 2 2 3 0 1
# 9: 3 3 3 1 1
# 10: 1 1 4 1 1
# 11: 2 2 4 1 1
# 12: 3 3 4 1 1
Another alternative (since you seem to be insisting on a "reshape2" solution) is to first melt the data, then do some modifications to the data to get it ready for dcast.
Here's the approach (starting with the original "df" data, not the one where we've renamed the columns above):
library(reshape2)
## melt the data
dfL <- melt(df, id.vars = c("ID", "group"))
## replace "v1" and "v2" with "v1.0" and "v2.0"
dfL$variable <- gsub("(v1$|v2$)", "\\1.0", dfL$variable)
## split that column into two parts and bind back with the original df
dfL <- cbind(dfL, colsplit(dfL$variable, "\\.", c("var", "time")))
## reshape from long to wide
dcast(dfL, ID + group + time ~ var, value.var = "value")
# ID group time v1 v2
# 1 1 1 0 1 1
# 2 1 1 1 1 1
# 3 1 1 2 1 0
# 4 1 1 3 1 1
# 5 2 2 0 1 1
# 6 2 2 1 2 1
# 7 2 2 2 0 1
# 8 2 2 3 1 1
# 9 3 3 0 1 0
# 10 3 3 1 3 1
# 11 3 3 2 1 1
# 12 3 3 3 1 1
You'll have to increment the "time" column if you want exactly the same output that you show in your question.
Upvotes: 1
Reputation: 24623
Try:
nddf = data.frame(ID=numeric(), group=numeric(), x=numeric(), v1=numeric(), v2=numeric())
for(i in 1:nrow(ddf)){
nddf[nrow(nddf)+1,]=c(ddf[i,'ID'], ddf[i,'group'], 1, ddf[i,3], ddf[i,4])
nddf[nrow(nddf)+1,]=c(ddf[i,'ID'], ddf[i,'group'], 2, ddf[i,5], ddf[i,6])
nddf[nrow(nddf)+1,]=c(ddf[i,'ID'], ddf[i,'group'], 3, ddf[i,7], ddf[i,8])
nddf[nrow(nddf)+1,]=c(ddf[i,'ID'], ddf[i,'group'], 4, ddf[i,9], ddf[i,10])
}
nddf
ID group x v1 v2
1 1 1 1 1 1
2 1 1 2 1 1
3 1 1 3 1 0
4 1 1 4 1 1
5 2 2 1 1 1
6 2 2 2 2 1
7 2 2 3 0 1
8 2 2 4 1 1
9 3 3 1 1 0
10 3 3 2 3 1
11 3 3 3 1 1
12 3 3 4 1 1
Upvotes: 0
Reputation: 59425
This seems to work.
df <- structure(list(ID = 1:3, group = 1:3, v1 = c(1L, 1L, 1L), v2 = c(1L, 1L, 0L), v1.1 = 1:3, v2.1 = c(1L, 1L, 1L), v1.2 = c(1L, 0L, 1L ), v2.2 = c(0L, 1L, 1L), v1.3 = c(1L, 1L, 1L), v2.3 = c(1L, 1L, 1L)), .Names = c("ID", "group", "v1", "v2", "v1.1", "v2.1", "v1.2", "v2.2", "v1.3", "v2.3"), class = "data.frame", row.names = c(NA, -3L))
result <- reshape(df,idvar=1:2,
varying=list(c(3,5,7,9),c(4,6,8,10)),
timevar="X",
direction="long")
result <- with(result,result[order(ID,group,X),])
result
# ID group X v1 v2
# 1.1.1 1 1 1 1 1
# 1.1.2 1 1 2 1 1
# 1.1.3 1 1 3 1 0
# 1.1.4 1 1 4 1 1
# 2.2.1 2 2 1 1 1
# 2.2.2 2 2 2 2 1
# 2.2.3 2 2 3 0 1
# 2.2.4 2 2 4 1 1
# 3.3.1 3 3 1 1 0
# 3.3.2 3 3 2 3 1
# 3.3.3 3 3 3 1 1
# 3.3.4 3 3 4 1 1
Normally, I'd recommend melt(...) in the reshape2 package, but with more than one set of "value" columns (v1 and v2) this might be quicker.
Upvotes: 2