0

I have two data frames. In the first one there are dates and times (there are not always present 24 entries for each day):

datetime
2011-01-01 00:00:00
2011-01-01 01:00:00
2011-01-01 02:00:00
2011-01-02 00:00:00
...

The second one contains only dates and a value:

date          value
2011-01-01    1
2011-01-02    4
2011-01-03    3
2011-01-04    7
...

Dates are stored as POSIXlt types. I want to attach the value from the second data frame to the first one, comparing only the date part. The result should be this:

datetime               value
2011-01-01 00:00:00    1
2011-01-01 01:00:00    1
2011-01-01 02:00:00    1
2011-01-02 00:00:00    4
...
robert
  • 3,539
  • 3
  • 35
  • 56

1 Answers1

2

Try

 merge(df2, transform(df1, date=as.Date(datetime)), by='date')[,(3:2)]
 #             datetime value
 #1 2011-01-01 00:00:00     1
 #2 2011-01-01 01:00:00     1
 #3 2011-01-01 02:00:00     1
 #4 2011-01-02 00:00:00     4

data

 df1 <- structure(list(datetime = structure(list(sec = c(0, 0, 0, 0), 
 min = c(0L, 0L, 0L, 0L), hour = c(0L, 1L, 2L, 0L), mday = c(1L, 
 1L, 1L, 2L), mon = c(0L, 0L, 0L, 0L), year = c(111L, 111L, 
 111L, 111L), wday = c(6L, 6L, 6L, 0L), yday = c(0L, 0L, 0L, 
 1L), isdst = c(0L, 0L, 0L, 0L), zone = c("EST", "EST", "EST", 
"EST"), gmtoff = c(NA_integer_, NA_integer_, NA_integer_, 
NA_integer_)), .Names = c("sec", "min", "hour", "mday", "mon", 
"year", "wday", "yday", "isdst", "zone", "gmtoff"), class = c("POSIXlt", 
"POSIXt"))), .Names = "datetime", row.names = c(NA, -4L), class = "data.frame")

 df2 <- structure(list(date = structure(c(14975, 14976, 14977, 14978), class = "Date"), 
   value = c(1L, 4L, 3L, 7L)), .Names = c("date", "value"), row.names = c(NA, 
 -4L), class = "data.frame")
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Nice one liner. +1. What if date in the 2nd data frame in character? I was thinking about this scenario, and I would use `separate` to create date in character. – jazzurro Oct 25 '14 at 14:23
  • @jazzurro Thanks. If it is character, you can either convert to POSIXct or `date` objects, and compare, or perhaps extract the date part using `regex` and compare with the `df1` date (which also presumed to be character). – akrun Oct 25 '14 at 14:25
  • @jazzurro BTW I updated here http://stackoverflow.com/questions/26502659/getting-information-from-rows-above-and-below?noredirect=1#comment41676015_26502659 I think there is some room for improvement using `dplyr` or `data.table`. – akrun Oct 25 '14 at 14:26
  • @akrun Thank you. Can you briefly explain what the `(3:2)` means in your code? – robert Oct 25 '14 at 14:28
  • @franz1 I was just reordering the columns based on the expected result you showed. The `merge` returns three columns, of which the `first` one is not of interest to you. By using `(3:2)` I am reversing the order of columns in the result. – akrun Oct 25 '14 at 14:30
  • @akrun Speaking of data.table, I wanna ask some advice. I have been going through the CRAN manual to learn the package. But it is not like the one for dplyr, and I find it is a bit hard to go through. What would you recommend for some learning? – jazzurro Oct 25 '14 at 14:33
  • @jazzurro I was only following `stackoverflow` Q&A for learning. I think `data.table` also has `FAQ`, which might be a good place to look for. From my POV, I often split the codes especially difficult ones and see what it returns. – akrun Oct 25 '14 at 14:35
  • @akrun When I see SO questions, I can read codes quite a bit. I am translating from dplyr to data.table in my mind. I guess I should keep learning from real questions here. Thanks for the advice. – jazzurro Oct 25 '14 at 14:37