Combining data sets without using row.name

Question

I start with a data.frame (or a data_frame) containing my dependent Y variable for analysis, my independent X variables, and some "Z" variables -- extra columns that I don't need for my modeling exercise.

What I would like to do is:

1. Create an analysis data set without the Z variables;
2. Break this data set into random training and test sets;
3. Find my best model;
4. Predict on both the training and test sets using this model;
5. Recombine the training and test sets by rows; and finally
6. Recombine these data with the Z variables, by column.

It's the last step, of course, that presents the problem -- how do I make sure that the rows in the recombined training and test sets match the rows in the original data set? We might try to use the row.names variable from the original set, but I agree with Hadley that this is an error-prone kludge (my words, not his) -- why have a special column that's treated differently from all other data columns?

One alternative is to create an ID column that uniquely identifies each row, and then keep this column around when dividing into the train and test sets (but excluding it from all modeling formulas, of course). This seems clumsy as well, and would make all my formulas harder to read.

This must be a solved problem -- could people tell me how they deal with this? Especially using the plyr/dplyr/tidyr package framework?

Answer 1

You need neither to use the row names or to create an additonal ID column. Here is an approach based on the indices of the training set.

An example data set:

set.seed(1)
dat <- data.frame(Y = rnorm(10),
                  X1 = rnorm(10),
                  X2 = rnorm(10),
                  Z1 = rnorm(10),
                  Z2 = rnorm(10))

Now, your steps:

1. Create an analysis data set without the Z variables

   dat2 <- dat[grep("Z", names(dat), invert = TRUE)]
   dat2
   #             Y          X1          X2
   # 1  -0.6264538  1.51178117  0.91897737
   # 2   0.1836433  0.38984324  0.78213630
   # 3  -0.8356286 -0.62124058  0.07456498
   # 4   1.5952808 -2.21469989 -1.98935170
   # 5   0.3295078  1.12493092  0.61982575
   # 6  -0.8204684 -0.04493361 -0.05612874
   # 7   0.4874291 -0.01619026 -0.15579551
   # 8   0.7383247  0.94383621 -1.47075238
   # 9   0.5757814  0.82122120 -0.47815006
   # 10 -0.3053884  0.59390132  0.41794156
   

2. Break this data set into random training and test sets

   train_idx <- sample(nrow(dat2), 0.8 * nrow(dat2))
   train_idx
   # [1]  7  4  3 10  9  2  1  5
   
   train <- dat2[train_idx, ]
   train
   #             Y          X1          X2
   # 7   0.4874291 -0.01619026 -0.15579551
   # 4   1.5952808 -2.21469989 -1.98935170
   # 3  -0.8356286 -0.62124058  0.07456498
   # 10 -0.3053884  0.59390132  0.41794156
   # 9   0.5757814  0.82122120 -0.47815006
   # 2   0.1836433  0.38984324  0.78213630
   # 1  -0.6264538  1.51178117  0.91897737
   # 5   0.3295078  1.12493092  0.61982575
   
   test_idx <- setdiff(seq(nrow(dat2)), train_idx)
   test_idx
   # [1] 6 8
   
   test <- dat2[test_idx, ]
   test
   #            Y          X1          X2
   # 6 -0.8204684 -0.04493361 -0.05612874
   # 8  0.7383247  0.94383621 -1.47075238
   

3. Find my best model

   ...

4. Predict on both the training and test sets using this model

   ...

5. Recombine the training and test sets by rows

   idx <- order(c(train_idx, test_idx))
   dat3 <- rbind(train, test)[idx, ]
   identical(dat3, dat2)
   # [1] TRUE
   

6. Recombine these data with the Z variables, by column

   dat4 <- cbind(dat3, dat[grep("Z", names(dat))])
   identical(dat, dat4)
   # [1] TRUE
   

In summary, we can use the indices of the training and test data to combine the data in the rows in the original order.