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

Scott Warchal · Answer 1 · 2022-12-05T14:47:00.660

7

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

gdata::interleave(df1, df2)

edited Dec 05 '22 at 14:47

answered Jun 14 '16 at 16:31

Scott Warchal

1,028
10
15

Edward R. Mazurek · Accepted Answer · 2016-06-14T16:28:14.110

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

RHertel · Answer 3 · 2016-06-14T17:05:54.977

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

score 3 · Answer 4 · answered Jun 14 '16 at 16:31

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)

How can I interleave rows from 2 data frames together?

4 Answers4