Split up rows by count in column

Question

I got rows that are laid out as follows:

I would like this data to be turned into:

I have not tried anything myself yet in R because all I can think of is iterating over the frame in a loop and my guess is that there are more effective methods in R. Would any of you know haw this can be achieved effectively?

To those requiring a working sample here is @Ronack's method to generate it:

df = structure(list(Date = c("23-10-2019", "24-10-2019"),
                    `30s` = c(3, 2),
                    `40s` = c(2, 1)),
               class = "data.frame", row.names = c(NA, -2L))

Please provide the (input) data by using the output of `dput` — starja, Oct 06 '20 at 09:37
Does this answer your question? [Reshaping data.frame from wide to long format](https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format) — user438383, Oct 06 '20 at 09:39
@user438383 it did not answer it. Ronak eventually gave a working solution. — SomeDutchGuy, Oct 06 '20 at 09:59

score 3 · Accepted Answer · answered Oct 06 '20 at 09:41

Use pivot_longer + uncount from tidyr :

library(tidyr)

df %>%
  pivot_longer(cols = -Date, names_to = 'measure') %>%
  uncount(value)

#  Date      measure
#  <chr>      <chr>  
#1 23-10-2019 30s    
#2 23-10-2019 30s    
#3 23-10-2019 30s    
#4 23-10-2019 40s    
#5 23-10-2019 40s    
#6 24-10-2019 30s    
#7 24-10-2019 30s    
#8 24-10-2019 40s

data

df <- structure(list(Date = c("23-10-2019", "24-10-2019"), `30s` = c(3, 
2), `40s` = c(2, 1)), class = "data.frame", row.names = c(NA, -2L))

As an extra to those getting an error "Error in rep(seq_nrow(data), w) : invalid 'times' argument", make sure all NA values are removed from your production data set. — SomeDutchGuy, Oct 06 '20 at 09:58

score 1 · Answer 2 · answered Oct 06 '20 at 10:19

1

With the data.table package you could do:

require(data.table)

setDT(df)
m = melt(df, id.vars = 'Date', variable.name = 'measure')
m[, .(Date = rep(Date, value)), .(measure)]

data (from @ronak)

df = structure(list(Date = c("23-10-2019", "24-10-2019"),
                    `30s` = c(3, 2),
                    `40s` = c(2, 1)),
               class = "data.frame", row.names = c(NA, -2L))

answered Oct 06 '20 at 10:19

andschar

3,504
2
27
35

I haven't down-voted, though I agree it would have been good to add a sample data.frame in the first place. Anyway, you added it now. – andschar Oct 06 '20 at 10:44

Split up rows by count in column

2 Answers2