repeat dataframe n times whilst adding column

Question

This is my reproducible code:

df <- data.frame(x = c(1, 2), y = c(3, 4))

df1 <- df %>% mutate(z = 1)
df2 <- df %>% mutate(z = 2)
df3 <- df %>% mutate(z = 3)

df <- rbind(df1, df2, df3)

df

I repeat the original data frame df 3 times, whilst adding one column where the number in the column indicated the repetition. In my use case, I have to do this more than 3 times. I could use a loop but is there a neater way? I guess i cannot use expand.grid.

`cbind(df, z = rep(1:n, each = nrow(df)))` where `n` is how many repeats you want. — qdread, Dec 07 '18 at 17:04
@qdread This will not work in general, because `cbind` converts to matrix, instead of data.frame — Corey Levinson, Dec 07 '18 at 17:08
@CoreyLevinson I think `cbind` has a `data.frame` method so it should work. — qdread, Dec 07 '18 at 17:10
@CoreyLevinson as long as you start with a data frame, `cbind` will give you a data frame back. `class(cbind(mtcars, 1))`. — Gregor Thomas, Dec 07 '18 at 17:11

ebeneditos · Answer 1 · 2018-12-08T09:01:45.507

5

You can also do it with a merge:

dfz <- data.frame(z = 1:3)

merge(df, dfz)

#   x y z
# 1 1 3 1
# 2 2 4 1
# 3 1 3 2
# 4 2 4 2
# 5 1 3 3
# 6 2 4 3

edited Dec 08 '18 at 09:01

answered Dec 07 '18 at 17:11

ebeneditos

2,542
1
15
35

score 3 · Accepted Answer · answered Dec 07 '18 at 17:08

3

We can create a list column and unnest

library(tidyverse)
df %>%
   mutate(z = list(1:3)) %>%
   unnest %>%
   arrange(z)
#  x y z
#1 1 3 1
#2 2 4 1
#3 1 3 2
#4 2 4 2
#5 1 3 3
#6 2 4 3

answered Dec 07 '18 at 17:08

akrun

874,273
37
540
662

1

Another 'verse idiom for z being a df-counter: `dplyr::bind_rows(rep(list(df), 3), .id="z")` – Frank Dec 07 '18 at 17:12
@Frank Thanks, my first thought was along the `bind_rows` way – akrun Dec 07 '18 at 17:13

acylam · Answer 3 · 2018-12-07T20:40:44.767

3

We can also do a cross join with sqldf. This creates a Cartesian Product of df and the reps tables:

library(sqldf)
reps <- data.frame(z = 1:3)

sqldf("select * from df, reps order by z")

or simply with map_dfr from purrr:

library(purrr)

map_dfr(1:3, ~cbind(df, z = .))

Output:

edited Dec 07 '18 at 20:40

answered Dec 07 '18 at 17:21

acylam

18,231
5
36
45

1

haha that would have been another option for sql dinosaurs like me – cs0815 Dec 07 '18 at 17:24

score 1 · Answer 4 · answered Dec 07 '18 at 18:54

Yet another option using base R

n <- 3
do.call(rbind, 
        Map(`[<-`, replicate(n = n, 
                             expr = df, 
                             simplify = FALSE), 
            "z", 
            value = seq_len(n)))
#  x y z
#1 1 3 1
#2 2 4 1
#3 1 3 2
#4 2 4 2
#5 1 3 3
#6 2 4 3

score 1 · Answer 5 · answered Dec 07 '18 at 19:04

A few other ways not covered yet:

# setup
df = data.frame(x = c(1, 2), y = c(3, 4))
n = 3

# simple row indexing, add column manually
result = df[rep(1:nrow(df), 3), ]
result$id = rep(1:n, each = nrow(df))

# cross join in base
merge(df, data.frame(id = 1:n), by = NULL)

# cross join in tidyr
tidyr::crossing(df, data.frame(id = 1:n))

# dplyr version of the row-index method above
slice(df, rep(1:n(), n)) %>% mutate(id = rep(1:n, each = nrow(df)))

Inspiration drawn heavily from an old question of mine, How can I repeat a data frame?. Basically the same question but without the id column requirement.

repeat dataframe n times whilst adding column

5 Answers5