Options for data.frame arrangement (created from list) using mdply and mutate

Question

I am using mdply with mutate to get a list into a data.frame using info from here:

# predictions
X1        DAY MONTH YEAR pLength pred
1.1.1.V1   1     1    1    0.00 1.00
1.1.1.V2   1     1    1    0.25 2.00
1.1.1.V3   1     1    1    1.00 1.00
2.2.1.V1   2     2    1    0.00 2.00
2.2.1.V2   2     2    1    0.50 2.50
2.2.1.V3   2     2    1    1.00 3.00
2.3.2.V1   2     3    2    0.00 2.00
2.3.2.V2   2     3    2    0.65 2.35
2.3.2.V3   2     3    2    1.00 3.00

Though I would like the result to be arranged like this:

# DFx
DAY MONTH YEAR pLength V1 V2 V3
1     1    1    0.00  1   NA NA
1     1    1    0.25 NA 2.00 NA
1     1    1    1.00 NA   NA  1
2     2    1    0.00  2   NA NA
2     2    1    0.50 NA 2.50 NA
2     2    1    1.00 NA   NA  3
2     3    2    0.00  2   NA NA
2     3    2    0.65 NA 2.35 NA
2     3    2    1.00 NA   NA  3

Is there something I could differently in the code below to arrive the format of DFx? I have tried casting predictions unsuccessfully. Or, are there options besides mutate to use with mdply that might allow the end result I am looking for?

EDIT: My current solution is to save predictions as a csv, open it in Excel, do a text to columns to split it leaving a column with just the variable name (i.e. V1, V2, V3) named variable, bring that back into r, and finally dcast dcast(pred, DATE+pLength ~ variable).

df1 <- structure(list(DAY = c(1L, 2L, 2L, 1L, 2L, 2L, 1L, 2L, 2L, 1L, 
2L, 2L, 1L, 2L, 2L, 1L, 2L, 2L), MONTH = c(1L, 2L, 3L, 1L, 2L, 
3L, 1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L), YEAR = c(1L, 
1L, 2L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 
1L, 2L, 1L, 1L, 2L, 1L, 1L, 2L), pLength = c(0L, 
0L, 0L, 1L, 1L, 1L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 0L, 1L, 1L, 
1L), variable = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 
2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L), .Label = c("V1", "V2", "V3"
), class = "factor"), value = c(1L, 2L, 2L, 3L, 1L, 1L, 2L, 3L, 
3L, 2L, 2L, 2L, 3L, 1L, 1L, 1L, 3L, 3L)), .Names = c("DAY", "MONTH", 
"YEAR", "pLength", "variable", "value"), row.names = c(NA, -18L
), class = "data.frame")

df2 <- structure(list(DAY = c(1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L), MONTH = c(1L, 
1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L), YEAR = c(1L, 1L, 1L, 1L, 1L, 
1L, 2L, 2L, 2L), pLength = c(0, 0.25, 1, 0, 0.5, 1, 0, 0.65, 
1), X1 = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), X2 = c(0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L), X3 = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L), X4 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L)), .Names = c("DAY", 
"MONTH", "YEAR", "pLength", "X1", "X2", "X3", "X4"), class = "data.frame", row.names = c(NA, 
-9L))

# choose colums from df2 that will be used to receive the predicted values
recvars <- c("DAY", "MONTH", "YEAR", "pLength")
rec <- df2[recvars]
recList <- dlply(rec, c("DAY", "MONTH", "YEAR", "pLength"))

# create list of models that predict the value by pLength
models <- dlply(df1, c("DAY", "MONTH", "YEAR", "variable"), function(df) 
  lm(value ~ pLength, data = df))

# get predicted values
predictions <- mdply(cbind(mod = models, df = recList), function(mod, df) {
  mutate(df, pred = predict(mod, newdata = df))
})

score 1 · Accepted Answer · answered Dec 19 '13 at 22:18

1

If all you want to do is convert predictions into DFx, can't you do it this way?

DFx <- predictions
DFx <- cbind(DFx,
            V1=ifelse(substr(DFx$X1,7,8)=="V1",DFx$pred,NA),
            V2=ifelse(substr(DFx$X1,7,8)=="V2",DFx$pred,NA),
            V3=ifelse(substr(DFx$X1,7,8)=="V3",DFx$pred,NA))
DFx <- DFx[,-6]   # delete "pred" column

answered Dec 19 '13 at 22:18

jlhoward

58,004
7
97
140

this solution works. However, my real dataset has variables with names that are of different lengths and there about 20 of them. I think at that point it would still be quicker to go my silly Excel route. – nofunsally Dec 20 '13 at 13:41

Options for data.frame arrangement (created from list) using mdply and mutate

1 Answers1