Add a second to duplicated POSIXct dates

Question

I am trying to add single seconds to any repeated dates in my data frame.

i.e. from this:

       value                date
       18        2013-07-09 16:49:23
       62        2013-07-09 18:01:36
       64        2013-07-09 18:46:51
       29        2013-07-09 18:46:51
       22        2013-07-09 18:46:51
....

I would like to obtain this:

       value                date
       18        2013-07-09 16:49:23
       62        2013-07-09 18:01:36
       64        2013-07-09 18:46:51
       29        2013-07-09 18:46:52
       22        2013-07-09 18:46:53
....

I understand I can simply add + 1 or +2 to the POSIXct format to add seconds- however I don't know how to select the duplicates. Note that my dataframe is a few hundred of rows long, and a date can appear up to 20 times in a row.

I am thinking of doing something along these lines:

for (item in duplicated(dataframe$date)) {
    if (item == TRUE) {
        for (n in 1:#length of duplicated dates) {
        dataframe[index(item) +n]$date <- (dataframe[index(item) +n]$date +n)
        } } }

Thank you for your help!

Henrik · Answer 1 · 2013-12-03T16:30:13.197

You may try to use rle to count the length of runs of equal dates. Then use the lengths of the repeats together with sequence to calculate the number of seconds you need to add.

r <- rle(as.numeric(df$date))$lengths
r
# [1] 1 1 3

to.add <- sequence(r) - 1
to.add
# [1] 0 0 0 1 2

df$date2 <- df$date + to.add

# Suggestion from @agstudy to make it more general:
df$date2 <- df$date + as.difftime(to.add, unit = "secs")

df[ , c("date", "date2")]
#                  date               date2
# 1 2013-07-09 16:49:23 2013-07-09 16:49:23
# 2 2013-07-09 18:01:36 2013-07-09 18:01:36
# 3 2013-07-09 18:46:51 2013-07-09 18:46:51
# 4 2013-07-09 18:46:51 2013-07-09 18:46:52
# 5 2013-07-09 18:46:51 2013-07-09 18:46:53

You may also have a look at some example on fixing duplicate time index in ?zoo (not tested on your data).

@agstudy, thanks a lot for your suggestion. Just give me two seconds and I will update. — Henrik, Dec 03 '13 at 16:19

score 5 · Accepted Answer · answered Dec 03 '13 at 16:13

You can use make.index.unique in the xts package.

x <- structure(list(value = c(18, 62, 64, 29, 22),
  date = structure(c(1373406563, 1373410896, 1373413611, 1373413611, 1373413611),
  class = c("POSIXct", "POSIXt"), tzone = "")), .Names = c("value", "date"),
  row.names = c(NA, -5L), class = "data.frame")
x$date.unique <- make.index.unique(x$date,1)
x
#   value                date         date.unique
# 1    18 2013-07-09 16:49:23 2013-07-09 16:49:23
# 2    62 2013-07-09 18:01:36 2013-07-09 18:01:36
# 3    64 2013-07-09 18:46:51 2013-07-09 18:46:51
# 4    29 2013-07-09 18:46:51 2013-07-09 18:46:52
# 5    22 2013-07-09 18:46:51 2013-07-09 18:46:53

Add a second to duplicated POSIXct dates

2 Answers2