How can I interleave rows from 2 data frames together?

Question

How can I interleave rows from 2 data frames together like a perfect riffle shuffle?

Example data:

df1 <- data.frame(df = 1, id = 1:5, chr = 'puppies')
df2 <- data.frame(df = 2, id = 1:5, chr = 'kitties')

df1:

  df id     chr
1  1  1 puppies
2  1  2 puppies
3  1  3 puppies
4  1  4 puppies
5  1  5 puppies

df2:

  df id     chr
1  2  1 kitties
2  2  2 kitties
3  2  3 kitties
4  2  4 kitties
5  2  5 kitties

Desired result:

      df    id     chr
1      1     1 puppies
2      2     1 kitties
3      1     2 puppies
4      2     2 kitties
5      1     3 puppies
6      2     3 kitties
7      1     4 puppies
8      2     4 kitties
9      1     5 puppies
10     2     5 kitties

Proudfoot

Answer 1

Assign row numbers to each data frame independently, then bind the rows and sort/arrange by row number and data frame id. In this example, row numbers are trivial since the ids are sequential and act as row number. But in the general case, row numbers should be used.

Here's an example using dplyr:

df1 %>%
  mutate(row_number = row_number()) %>%
  bind_rows(df2 %>% mutate(row_number = row_number())) %>%
  arrange(row_number, df)

Output:

      df    id     chr row_number
   (dbl) (int)   (chr)      (int)
1      1     1 puppies          1
2      2     1 kitties          1
3      1     2 puppies          2
4      2     2 kitties          2
5      1     3 puppies          3
6      2     3 kitties          3
7      1     4 puppies          4
8      2     4 kitties          4
9      1     5 puppies          5
10     2     5 kitties          5

Proudfoot

Answer 2

A non-dplyr solution would be to use the interleave function in the gdata package.

gdata::interleave(df1, df2)

Fowle

Answer 3

In base R, I find that such manipulations are easier with matrices than with data.frames. This quite lengthy one-liner should work:

setNames(data.frame(t(matrix(unlist(t(cbind(df1,df2))),ncol(df1)))),names(df1))
#   df id     chr
#1   1  1 puppies
#2   2  1 kitties
#3   1  2 puppies
#4   2  2 kitties
#5   1  3 puppies
#6   2  3 kitties
#7   1  4 puppies
#8   2  4 kitties
#9   1  5 puppies
#10  2  5 kitties

Amen

Answer 4

Here is a base R method that I believe works. There is a bit of setup cost. I also have to use character vectors rather than factors (stringsAsFactors=F)

# setup a blank data.frame of the proper dimensions
df <- data.frame(df=1:(nrow(df1) + nrow(df2)), 
                 id=1:(nrow(df1) + nrow(df2)),
                 chr=1:(nrow(df1) + nrow(df2)))

# fill it in with subscripting
df[1:(nrow(df1) + nrow(df2)) %% 2 == 1,] <- df1
df[1:(nrow(df1) + nrow(df2)) %% 2 == 0,] <- df2
df
   df id     chr
1   1  1 puppies
2   2  1 kitties
3   1  2 puppies
4   2  2 kitties
5   1  3 puppies
6   2  3 kitties
7   1  4 puppies
8   2  4 kitties
9   1  5 puppies
10  2  5 kitties

data

df1 <- data.frame(df = 1, id = 1:5, chr = 'puppies', stringsAsFactors=F)
df2 <- data.frame(df = 2, id = 1:5, chr = 'kitties', stringsAsFactors=F)

Hooch

Answer 5

A tidyverse solution would be vec_interleave(), which works on data frames (in addition to vectors):

library(vctrs)
vec_interleave(df1, df2)

Pursy