expanding (exploding?) a data.frame by adding a variable containing a sequence number with dplyr

Question

I have simple data frame, let say:

dd <- data.frame(id = letters[1:4], v1 = c(0.3,0.1,0.7,1.3))
dd
  id  v1
1  a 0.3
2  b 0.1
3  c 0.7
4  d 1.3

For every row of this data frame, I want to "explode" it by adding a new variable which gives a sequence of numbers. I succeeded doing this, but it's my code is not ideal and hardly expendable:

dd %>% 
  mutate("0"=0,"5"=5,"10"=10) %>% 
  reshape2::melt(id.vars=c("id", "v1")) %>% 
  select(-variable) 
   id  v1 value
1   a 0.3     0
2   b 0.1     0
3   c 0.7     0
4   d 1.3     0
5   a 0.3     5
6   b 0.1     5
7   c 0.7     5
8   d 1.3     5
9   a 0.3    10
10  b 0.1    10
11  c 0.7    10
12  d 1.3    10

So, in this example, for every row, I add a column called value which have all three values within c(0,5,10)

This code is not ideal because the actual sequence I want is pretty much 1:70 and I don't want to write all 70 new variables in my mutate manually. There sure is a better way of doing this, can you help me?

I don't have to stay in dplyr but I want ot be able to pipe my code.

Thanks

Gregor Thomas · Accepted Answer · 2019-03-20 13:27:53Z

6

library(tidyr)
dd %>% crossing(value = c(0, 5, 10))
   id  v1 value
1   a 0.3     0
2   a 0.3     5
3   a 0.3    10
4   b 0.1     0
5   b 0.1     5
6   b 0.1    10
7   c 0.7     0
8   c 0.7     5
9   c 0.7    10
10  d 1.3     0
11  d 1.3     5
12  d 1.3    10

answered Mar 20, 2019 at 13:27

Gregor Thomas

147k22 gold badges185 silver badges320 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ronak Shah · Accepted Answer · 2019-03-20 13:33:41Z

3

One option is add values in a dataframe and do a merge in base R.

merge(dd, data.frame(value = c(0, 5, 10)))

#   id  v1 value
#1   a 0.3     0
#2   b 0.1     0
#3   c 0.7     0
#4   d 1.3     0
#5   a 0.3     5
#6   b 0.1     5
#7   c 0.7     5
#8   d 1.3     5
#9   a 0.3    10
#10  b 0.1    10
#11  c 0.7    10
#12  d 1.3    10

edited Mar 20, 2019 at 13:33

answered Mar 20, 2019 at 13:28

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Comments

arg0naut91 · Accepted Answer · 2019-03-20 13:54:38Z

2

With data.table:

setDT(dd)[, .(value = c(0, 5, 10)), by = .(id, v1)]

Output:

    id  v1 value
 1:  a 0.3     0
 2:  a 0.3     5
 3:  a 0.3    10
 4:  b 0.1     0
 5:  b 0.1     5
 6:  b 0.1    10
 7:  c 0.7     0
 8:  c 0.7     5
 9:  c 0.7    10
10:  d 1.3     0
11:  d 1.3     5
12:  d 1.3    10

If you load magrittr (or if you have dplyr or tidyverse already loaded) you can then continue with the pipes setDT(dd)[, .(value = c(0, 5, 10)), by = .(id, v1)] %>% ...

answered Mar 20, 2019 at 13:54

arg0naut91

14.8k2 gold badges22 silver badges41 bronze badges

Comments

www · Accepted Answer · 2019-03-20 13:29:08Z

1

We can also use expand from the tidyr package.

library(tidyverse)

dd <- data.frame(id = letters[1:4], v1 = c(0.3,0.1,0.7,1.3))

dd %>% 
  expand(nesting(id, v1), value = seq(0, 10, by = 5)) %>%
  arrange(value, id)
# # A tibble: 12 x 3
#    id       v1 value
#    <fct> <dbl> <dbl>
#  1 a       0.3     0
#  2 b       0.1     0
#  3 c       0.7     0
#  4 d       1.3     0
#  5 a       0.3     5
#  6 b       0.1     5
#  7 c       0.7     5
#  8 d       1.3     5
#  9 a       0.3    10
# 10 b       0.1    10
# 11 c       0.7    10
# 12 d       1.3    10

answered Mar 20, 2019 at 13:29

www

39.3k12 gold badges52 silver badges94 bronze badges

Comments

Sotos · Accepted Answer · 2019-03-20 13:40:06Z

1

Joining the party with splitstackshape package,

library(splitstackshape)

transform(expandRows(dd, count = 3, count.is.col = FALSE), value = c(0, 5, 10))

#    id  v1 value
#1    a 0.3     0
#1.1  a 0.3     5
#1.2  a 0.3    10
#2    b 0.1     0
#2.1  b 0.1     5
#2.2  b 0.1    10
#3    c 0.7     0
#3.1  c 0.7     5
#3.2  c 0.7    10
#4    d 1.3     0
#4.1  d 1.3     5
#4.2  d 1.3    10

answered Mar 20, 2019 at 13:40

Sotos

51.6k6 gold badges36 silver badges69 bronze badges

Comments

Jonas · Accepted Answer · 2019-03-20 14:05:55Z

1

A solution with using lapply, a very powerful tool:

dd <- data.frame(id = letters[1:4], v1 = c(0.3,0.1,0.7,1.3))
newdf<- lapply(c(0,5,10), function(value) {
   cbind(dd, data.frame(value=rep(value,NROW(dd))))
})
newdf <- do.call('rbind',newdf)

#    id  v1 value
#1   a 0.3     0
#2   b 0.1     0
#3   c 0.7     0
#4   d 1.3     0
#5   a 0.3     5
#6   b 0.1     5
#7   c 0.7     5
#8   d 1.3     5
#9   a 0.3    10
#10  b 0.1    10
#11  c 0.7    10
#12  d 1.3    10

edited Mar 20, 2019 at 14:05

answered Mar 20, 2019 at 13:29

Jonas

1826 bronze badges

Collectives™ on Stack Overflow

expanding (exploding?) a data.frame by adding a variable containing a sequence number with dplyr

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related