RandomDude RandomDude - 1 month ago 14
R Question

Melt 4 columns into 3 while combining the two variable columns

The following sample data.frame:

Date <- seq(as.Date("2016/9/1"), as.Date("2016/9/10"), "days")
A <- sample(0:200, 10)
B <- sample(0:400, 10)
A_limit <- rep(200,10)
B_limit <- rep(400,10)
data_sample <- data.frame(Date,A,B,A_limit,B_limit)

> Date A B A_limit B_limit
1 2016-09-01 175 270 200 400
2 2016-09-02 160 50 200 400
3 2016-09-03 173 25 200 400
...


and I would like to reshape it into the form:

> Date limit variable value
1 2016-09-01 200 A 175
2 2016-09-02 200 A 160
3 2016-09-03 200 A 173
...
31 2016-09-01 400 B 270
32 2016-09-02 400 B 50
33 2016-09-03 400 B 25
....


I manage to get it done but it seems to me my way is far to complicated:

library("reshape2")
data_sample_2 <- data_sample %>% melt(id=c("Date","A","B"))

levels(data_sample_2$variable) <- c(levels(data_sample_2$variable),"A","B")
data_sample_2$variable[data_sample_2$variable == "A_limit"] <- as.factor("A")
data_sample_2$variable[data_sample_2$variable == "B_limit"] <- as.factor("B")

names(data_sample_2)[names(data_sample_2) == "value"] <- "limit"
names(data_sample_2)[names(data_sample_2) == "variable"] <- "variable_1"

data_sample_3 <- data_sample_2 %>% melt(id=c("Date","variable_1","limit"))
data_sample_3 <- droplevels(data_sample_3)
data_sample_4 <- data_sample_3[data_sample_3$variable_1 == data_sample_3$variable,]

data_sample_4$variable_1 <- NULL


I just started using the
reshape2
package so please let me know about any way i can improve this data.frame transformation (no matter how obvious it may seem).

Answer

You can do this via base R simply by stacking everything, i.e.

df1 <- data.frame(Date = data_sample$Date, limit = stack(data_sample[-(1:3)])[[1]], 
                  variable = stack(data_sample[2:3])[[2]], 
                  value = stack(data_sample[2:3])[[1]], 
                                     stringsAsFactors = FALSE)

head(df1)
#        Date  limit variable value
#1 2016-09-01    200   A       67
#2 2016-09-02    200   A      100
#3 2016-09-03    200   A      166
#4 2016-09-04    200   A      116
#5 2016-09-05    200   A       89
#6 2016-09-06    200   A      138

tail(df1)
#         Date  limit variable value
#15 2016-09-05    400   B      208
#16 2016-09-06    400   B      387
#17 2016-09-07    400   B      125
#18 2016-09-08    400   B      116
#19 2016-09-09    400   B      120
#20 2016-09-10    400   B      241
Comments