Merge 2 date datasets

Question

Reproductible code

library("zoo")  
library("xts")
x <- structure(c("2012-09-27 09:08:37", "2012-09-29 10:06:33", "2012-10-01 09:44:36","2012-10-04 14:37:05", "2012-10-15 13:18:21", "2012-10-17 17:33:46","2012-10-18 11:52:13", "2016-10-06 15:11:01", "2016-10-07 13:00:09","2016-10-07 12:20:57"), class = c("xts", "zoo"), .indexCLASS = "Date", tclass = "Date", .indexTZ = "UTC", tzone = "UTC", format = "%Y-%m-%d %H:%M:%S", index = structure(c(1348704000,1348876800, 1349049600, 1349308800, 1350259200, 1350432000, 1350518400,1475712000, 1475798400, 1475798400), tzone = "UTC", tclass = "Date"), .Dim = c(10L,1L)) 
y <- structure(c("1961-08-04 10:00:00", "1971-01-01 11:00:00", "1978-01-01 11:00:00","1979-01-01 11:00:00", "1983-01-01 11:00:00", "1984-01-01 11:00:00","1985-01-01 11:00:00", "2016-10-07 20:28:24", "2016-10-07 18:27:54","2016-10-08 00:38:40"), class = c("xts", "zoo"), .indexCLASS = "Date", tclass = "Date", .indexTZ = "UTC", tzone = "UTC", format = "%Y-%m-%d %H:%M:%S", index = structure(c(-265420800,31536000, 252460800, 283996800, 410227200, 441763200, 473385600,1475798400, 1475798400, 1475884800), tzone = "UTC", tclass = "Date"), .Dim = c(10L,1L)) 
x+y
#expected result
#  get results
#what happens
#Error in `+.default`(x, y) : non-numeric argument to binary operator

Basically, i have 2 dates that i want to multiply, so that i will see just the ones that are intersecting.

You can find the code in this gist https://gist.github.com/baditaflorin/46b35b3044f69ed329e4c44067b7b246

Now the tclass is Date, i had imported the data from a csv file using

x <- xts(csv_file, format = "%Y-%m-%d %H:%M:%S",order.by=as.Date(csv_file))

I also tried to convert the data but got error

x2 <- as.Date.POSIXlt( x , format = "%Y-%m-%d %H:%M:%S" , tz = "GMT") 

Error in as.POSIXlt.default(x, tz, ...) : 
  do not know how to convert 'x' to class “POSIXlt”

These are my session informations:

R version 3.3.1 (2016-06-21)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04.1 LTS

locale:
[1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8         LC_MONETARY=en_US.UTF-8   
[6] LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8           LC_NAME=C                  LC_ADDRESS=C                       LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

 attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] devtools_1.12.0 xts_0.9-7       zoo_1.7-13     

loaded via a namespace (and not attached):
[1] httr_1.2.1      R6_2.2.0        tools_3.3.1     withr_1.0.2     curl_2.1        memoise_1.0.0   grid_3.3.1      digest_0.6.10  
[9] lattice_0.20-34

What is your actual question? Can you provide a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) (in your question please, not in a gist)? Also, the code in your gist has a syntax error (the `b <- structure...` line is not complete). The error you have stated in your question title does not match anything in your question. What does it mean to add a date? — mathematical.coffee, Oct 11 '16 at 22:57
use `dput()` on a subset of `x` to show us what's going on please. Without some info on `x`, we can only guess. — Jason, Oct 11 '16 at 23:03
I modified the gist to include the correct code for b. @mathematical.coffee i am trying to emulate this [https://campus.datacamp.com/courses/manipulating-time-series-data-in-r-with-xts-zoo/first-order-of-business-basic-manipulations?ex=12] , but with my dataset, not the xample dataset — Badita Florin, Oct 11 '16 at 23:17
You have character vectors of datetimes indexed by date. For none of these types does multiplying or adding make sense. If you just want to parse to datetime, `as.POSIXct(coredata(x), tz = 'GMT')` — alistaire, Oct 11 '16 at 23:26
@alistaire thanks, this works. But when i want to calculate, i still get a error xx <- as.POSIXct(coredata(x), tz = 'GMT') > yy <- as.POSIXct(coredata(y), tz = 'GMT') > xx+yy Error in `+.POSIXt`(xx, yy) : binary '+' is not defined for "POSIXt" objects — Badita Florin, Oct 11 '16 at 23:30
Right, because that makes no sense. What is 2012-09-27 09:08:37 + 1961-08-04 10:00:00 supposed to give? A datetime in 3974? That's a useless operation, and thus no method has been written for it in R. You could write your own, if you like. — alistaire, Oct 11 '16 at 23:35
@BaditaFlorin please update your question to clarify what you are trying to do (it still is not clear), and move the code from the gist to the question (many people will not bother to click through to the link, so will not help you). — mathematical.coffee, Oct 11 '16 at 23:36
I want to compare 2 datasets and see the dates that are in the first and second database. the same as they are doing here https://campus.datacamp.com/courses/manipulating-time-series-data-in-r-with-xts-zoo/first-order-of-business-basic-manipulations?ex=12 Maybe there are other ways of doing it, but i don`t know, so i am trying to do it with what i know, meaning xts ( just a little, i am still a noob) @mathematical.coffee — Badita Florin, Oct 11 '16 at 23:38

score 2 · Accepted Answer · answered Oct 11 '16 at 23:44

2

From your comments you want either an intersection or an inner join on the index columns of the two xts-objects.

> intersect( index(x), index(y) )
[1] 17081
> as.Date( intersect( index(x), index(y) ) )
[1] "2016-10-07"

The merge in xts defaults to "outer" which is unlike the base R merge functions, so adding a "join" specification is needed:

> merge(x,y, join="inner")
           x                     y                    
2016-10-07 "2016-10-07 13:00:09" "2016-10-07 20:28:24"
2016-10-07 "2016-10-07 12:20:57" "2016-10-07 18:27:54"

answered Oct 11 '16 at 23:44

IRTFM

258,963
21
364
487

thanks, it worked the first time, now is not working anymore, i tryied the last one, with merge and i get this error 4996 2013-10-24 11:05:04 2016-10-07 20:28:24 4997 2013-10-23 18:11:03 2016-10-07 20:28:24 4998 2013-10-23 09:16:10 2016-10-07 20:28:24 4999 2013-10-16 12:47:09 2016-10-07 20:28:24 5000 2013-10-16 11:55:17 2016-10-07 20:28:24 [ reached getOption("max.print") -- omitted 4608424 rows ] From what i see it is doing everything with everything – Badita Florin Oct 12 '16 at 00:17
After clearing all objects from workspace. I will stop trying and do some more tutorials, maybe there is a better way – Badita Florin Oct 12 '16 at 00:18

Merge 2 date datasets

1 Answers1