Create new dataframe with repeated values based on Date Column in R

user_13701033 0
Viewed 311

I have a very basic question. I have following sample dataframe with two columns; "Date", and "Value":

df <- structure(list(Date = structure(c(17075, 17083), class = "Date"), Value = c(0.16, 0.17)), row.names = c(NA, 2L), class = "data.frame")

I want to create a new dataframe with repeated values around a particular date. Dates should not be repeated but change according to number of steps taken to repeat values.

For example, for above dataframe, if I want a repeat of +-3 steps around the date then output dataframe should be as below:

out <- structure(list(Date = structure(c(17072,17073,17074,17075,17076,17077,17078,17080,17081,17082,17083,17084,17085,17086 , class = "Date"), Value = c(0.16,0.16,0.16,0.16,0.16,0.16,0.16,0.17,0.17,0.17,0.17,0.17,0.17, 0.17)), row.names = c(NA, 14L), class = "data.frame")

How can we do this task? I tried using rep function but didn't work for me.

dplyr lubridate dataframe r
2 Answers

If we have a n as 3, we can use rowwise and get the sequence of 'Date' in a list and later unnest the list column

library(dplyr)
library(tidyr)
library(lubridate)
n <- 3
df %>% 
  rowwise %>%
  mutate(Date =  list(seq(Date - days(n), length.out = 2 *n +1, 
        by = 'day'))) %>% 
  ungroup %>%
  unnest(c(Date))

-output

# A tibble: 14 x 2
#   Date       Value
#   <date>     <dbl>
# 1 2016-09-28  0.16
# 2 2016-09-29  0.16
# 3 2016-09-30  0.16
# 4 2016-10-01  0.16
# 5 2016-10-02  0.16
# 6 2016-10-03  0.16
# 7 2016-10-04  0.16
# 8 2016-10-06  0.17
# 9 2016-10-07  0.17
#10 2016-10-08  0.17
#11 2016-10-09  0.17
#12 2016-10-10  0.17
#13 2016-10-11  0.17
#14 2016-10-12  0.17

It may be a bit more faster with map

library(purrr)
df %>%
  mutate(Date = map(Date, ~ seq(.x - days(n), 
            length.out = 2 * n + 1, by = 'day'))) %>% 
  unnest(Date)
user_37322710

Messy Base R:

n <- 3
unique_dates <- unique(df$Date)
within(
  merge(
    df, 
    do.call(
      rbind,
      lapply(
        split.default(unique_dates, seq_len(length(unique_dates))),
        function(x){
          y <- as.Date(x)
          z <- sort(c(y - seq_len(n), y, y + seq_len(n)))
          data.frame(Date = y, Dates = z)
        }
      )
    ),
    by = "Date", 
    all = TRUE
  ),
  {
    Date <- Dates
    rm(Dates)
  }
)
user_92031580
Related