Badita Florin Badita Florin - 1 month ago 10
R Question

Merge 2 date datasets

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

42- 42-
Answer

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"