snr999 snr999 - 7 months ago 17
R Question

How to add 15 mins interval to date time series for every 2&3 row

I have dataframe as below

Date_time name
11/1/2016 6:00 A
11/1/2016 6:00 A
11/1/2016 6:00 A
11/1/2016 6:45 A
11/2/2016 11:00 B
11/2/2016 11:00 B
11/2/2016 11:00 B
11/2/2016 11:45 B


above df should be in 15 interval
we knoe after 00 mins, next one should be 15 & 30 & 45 intervals
O/p should be

Date_time name New_Date_time
11/1/2016 6:00 A 11/1/2016 6:00
11/1/2016 6:00 A 11/1/2016 6:15
11/1/2016 6:00 A 11/1/2016 6:30
11/1/2016 6:45 A 11/1/2016 6:45
11/2/2016 11:00 B 11/2/2016 11:00
11/2/2016 11:00 B 11/2/2016 11:15
11/2/2016 11:00 B 11/2/2016 11:30
11/2/2016 11:45 B 11/2/2016 11:45


Thanks in advance

Answer Source

I am assuming your variable Date_time is a character and that there are always 4 observations for each name. You can then do the following:

## Your data:
df <-  structure(list(Date_time = c("11/1/2016 6:00", "11/1/2016 6:00", 
    "11/1/2016 6:00", "11/1/2016 6:45", "11/2/2016 11:00", "11/2/2016 11:00", 
    "11/2/2016 11:00", "11/2/2016 11:45"), name = c("A", "A", "A", 
    "A", "B", "B", "B", "B")), .Names = c("Date_time", "name"), 
    class = "data.frame", row.names = c(NA, -8L))

library(dplyr)
library(lubridate)
df <- df %>%
        dplyr::mutate(Date_time = as.POSIXct(Date_time, format = "%m/%d/%Y %H:%M")) %>% 
        group_by(name) %>% 
        dplyr::mutate(New_Date_time = min(Date_time) + lubridate::minutes(c(0,15,30,45)))

df
# Source: local data frame [8 x 3]
# Groups: name [2]
# 
#             Date_time  name       New_Date_time
#                <dttm> <chr>              <dttm>
# 1 2016-11-01 06:00:00     A 2016-11-01 06:00:00
# 2 2016-11-01 06:00:00     A 2016-11-01 06:15:00
# 3 2016-11-01 06:00:00     A 2016-11-01 06:30:00
# 4 2016-11-01 06:45:00     A 2016-11-01 06:45:00
# 5 2016-11-02 11:00:00     B 2016-11-02 11:00:00
# 6 2016-11-02 11:00:00     B 2016-11-02 11:15:00
# 7 2016-11-02 11:00:00     B 2016-11-02 11:30:00
# 8 2016-11-02 11:45:00     B 2016-11-02 11:45:00

Edit:

If the number of observations varies, then just replace c(0,15,30,45) by seq(from = 0, by = 15, length.out = n()), i.e.:

df <- df %>%
        dplyr::mutate(Date_time = as.POSIXct(Date_time, format = "%m/%d/%Y %H:%M")) %>% 
        group_by(name) %>% 
        dplyr::mutate(New_Date_time = min(Date_time) + 
                        lubridate::minutes(seq(from = 0, by = 15, length.out = n())))