Add column but duplicate all other row values in dataframe in R

Question

I'd like to expand my dataframe by repeating a row 24 times while adding an additional column for "hour". Here is an example of my data:

set.seed(1)
mydata <- data.frame(Tmin = sample(0:3), Tmax = sample(4:7), Day = rep(1:4))

I want to expand this table such that each row is repeated with the same Tmin, Tmax, and Day 24 times, with an additional column mydata$hour where the numbers 1:24 are repeated for each day. All other values (Tmin, Tmax, Day) stay the same for each row. Thanks!

score 0 · Accepted Answer · answered Jul 29 '20 at 04:09

0

You can repeat each row index 24 times and then assign new hour column from 1 to 24 using recycling techinique.

newdata <- mydata[rep(seq_len(nrow(mydata)), each = 24),]
newdata$hour <- 1:24

Couple of tidyverse options :

library(dplyr)
mydata %>% tidyr::uncount(24) %>% group_by(Day) %>% mutate(hour = 1:24)

and

mydata %>% group_by(Day) %>% slice(rep(row_number(), 24)) %>% mutate(hour = 1:24)

answered Jul 29 '20 at 04:09

Ronak Shah

377,200
20
156
213

Thank you - I tried your first option but received this error: ```Error: Assigned data `value` must be compatible with existing data. x Existing data has 6912 rows. x Assigned data has 24 rows. i Only vectors of size 1 are recycled.``` – NorthLattitude Jul 29 '20 at 04:14
Your second option works - Thanks!! – NorthLattitude Jul 29 '20 at 04:24
1

@NorthLattitude It looks like your actual data is a tibble and not a dataframe. if you do `newdata <- data.frame(newdata)` and try it again it should work. – Ronak Shah Jul 29 '20 at 04:33

score 0 · Answer 2 · answered Jul 29 '20 at 07:22

Another alternative using lapply and dplyr::mutate .

library(dplyr)
    set.seed(1)
    mydata <- data.frame(Tmin = sample(0:3), Tmax = sample(4:7), Day = rep(1:4))
    newdata <- as.data.frame(lapply(mydata, rep, 24))
    newdata %>% 
        mutate(hour = rep(c(1:24), times = 4))

Add column but duplicate all other row values in dataframe in R

2 Answers2