# Combining data sets without using row.name

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?

## 1 Answer

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
#   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
#  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)
#  TRUE

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

dat4 <- cbind(dat3, dat[grep("Z", names(dat))])
identical(dat, dat4)
#  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.

• Sven, thanks -- this is brilliant. The key is the order() command, which I had under-appreciated until now. Is this how everyone does it? – David Pepper Mar 1 '15 at 7:32
• @DavidEpstein I cannot tell you how others manage their data. This is how I would do it. – Sven Hohenstein Mar 1 '15 at 7:48