Dates are not keeping specified format in R data frame

Question

Simply put, I'm grabbing dates for events that meet certain conditions in df1 and putting them in a new data frame (df2). The formatting of dates in df2 should be the same formatting in df1 ("2000-09-12", or %Y-%m-%d). However, the dates in df2 read "11212", "11213", etc.

to generate data:

"Date"<-c("2000-09-08", "2000-09-11","2000-09-12","2000-09-13","2000-09-14","2000-09-15","2000-09-18","2000-09-19","2000-09-20","2000-09-21", "2000-09-22","2000-09-25")
"Event"<-c("A","N","O","O","O","O","N","N","N","N","N","A")
df1<-data.frame(Date,Event)
df1
         Date Event
1  2000-09-08     A
2  2000-09-11     N
3  2000-09-12     O
4  2000-09-13     O
5  2000-09-14     O
6  2000-09-15     O
7  2000-09-18     N
8  2000-09-19     N
9  2000-09-20     N
10 2000-09-21     N
11 2000-09-22     N
12 2000-09-25     A

here is the code:

"df2"<-data.frame()
"tmp"<-data.frame(1,2)
i<-c(1:4)
for (x in i)
  {
  date1<- df1$Date[df1$Event=="O"][x]
  date2<- df1$Date[df1$Event=="A" & df1$Date => date1] [1]
  as.numeric(difftime(date2, date1))->tmp[1,2]
  as.Date(as.character(df1$Date[df1$Event=="O"][x]), "%Y-%m-%d")->tmp[1,1] ##the culprit
  rbind(df2, tmp)->df2
}

Loop output looks like this:

I want it to look like this:

            X1 X2
1 "2000-09-12" 13
2 "2000-09-13" 12
3 "2000-09-14" 11
4 "2000-09-14" 10

This is going to be difficult to help with unless you provide a fully reproducible example. — joran, Mar 16 '17 at 14:39
hmm, simply transforming the numeric input to date should work right? e.g., `as.Date(11212)` — timfaber, Mar 16 '17 at 15:01
@timfaber: that code produces "Error in as.Date.numeric(11212) : 'origin' must be supplied" — Kirsten, Mar 16 '17 at 15:04
`as.Date(11212, origin="1970-01-01")` is what you wante. See the detail section in `?as.Date` for more info. — lmo, Mar 16 '17 at 15:14
http://stackoverflow.com/questions/31274536/strange-behavior-involving-dates-origin-must-be-supplied — timfaber, Mar 16 '17 at 15:18

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

If I understand correctly, the OP wants to find for each "O" event the difference in days to the next following "A" event.

This can be solved using a rolling join. We extract the "O" events and the "A" events into two separate data.tables and join both on date.

This will avoid all the hassle with the data format and works also if df1 is not already ordered by Date.

library(data.table)
setDT(df1)[Event == "A"][df1[Event == "O"], 
                         on = "Date", roll = -Inf, .(Date, x.Date - i.Date)]

         Date      V2
1: 2000-09-12 13 days
2: 2000-09-13 12 days
3: 2000-09-14 11 days
4: 2000-09-15 10 days

Note that roll = -Inf rolls backwards (next observation carried backward (NOCB)) because the date of the next "A" event is required.

Data

Date <- as.Date(c("2000-09-08", "2000-09-11","2000-09-12","2000-09-13","2000-09-14","2000-09-15",
                  "2000-09-18","2000-09-19","2000-09-20","2000-09-21", "2000-09-22","2000-09-25"))
Event <- c("A","N","O","O","O","O","N","N","N","N","N","A")
df1 <- data.frame(Date,Event)

Dates are not keeping specified format in R data frame

1 Answers1

Data