CODEWITHSUNDEEP
r
user11384926
user11384926

Reputation: 1

joining 2 tables with conditions and different number of rows

I want to join two data.tables with different number of rows

Table 1= 18000 rows
Table 2= 3000 rows

For matching the two tables , there are different conditions First condition: ID of table 2 should be similar to ID of table 1 Second condition : date of table 2 should be equal or superior of start date of table 1 and inferior to the end date of table 1 I tried a code like this and it did not work

match<-Table1[Table2, on = c("ID.currency==ID.currency",  "start date<=date table2","end date>date table2")]

Thank you very much for your help

Table 1: structure(list(ID.currency.startdate.open.high.low.close.volume.enddate.number.of.trades = structure(c(4L, 1L, 2L, 3L, 5L), .Label = c("EURCHF,01/11/2017 01:00,1.1639,1.1656,1.1619,1.162,5500,01/11/2017 01:59,1555", "EURCHF,01/11/2017 02:00,1.1651,1.1666,1.1633,1.164,6000,01/11/2017 02:59,1900", "EURCHF,01/11/2017 03:00,1.1645,1.1662,1.1641,1.1657,6100,01/11/2017 03:59,3000", "EURUSD,01/11/2017 00:00,1.1619,1.1646,1.1659,1.1607,5000,01/11/2017 00:59,1500", "EURUSD,01/11/2017 04:00,1.1619,1.1646,1.1659,1.1607,7000,01/11/2017 04:59,3500" ), class = "factor")), row.names = c(NA, 5L), class = "data.frame")

Table2: structure(list(user.name.ID.currency.date.score = structure(c(2L, 3L, 4L, 1L, 5L), .Label = c("Alpha,EURCHF,31/11/2017 00:57:00,2.98", "Alpha,EURUSD,01/11/2017 00:44,2.5", "Beta,EURCHF,31/11/2017 00:57:00,1.78", "Beta,EURUSD,01/11/2017 04:12,3.23", "Beta,EURUSD,31/12/2018 00:17,0.678" ), class = "factor")), row.names = c(NA, 5L), class = "data.frame")

Output:

structure(list(ID.currency.startdate.open.high.low.close.volume.enddate.number.of.trades.user.name.ID.currency.date.score = structure(c(4L, 1L, 2L, 3L, 5L), .Label = c("EURCHF,01/11/2017 01:00,1.1639,1.1656,1.1619,1.162,5500,01/11/2017 01:59,1555,NA,NA,NA,NA", "EURCHF,01/11/2017 02:00,1.1651,1.1666,1.1633,1.164,6000,01/11/2017 02:59,1900,NA,NA,NA,NA", "EURCHF,01/11/2017 03:00,1.1645,1.1662,1.1641,1.1657,6100,01/11/2017 03:59,3000,NA,NA,NA,NA", "EURUSD,01/11/2017 00:00,1.1619,1.1646,1.1659,1.1607,5000,01/11/2017 00:59,1500,Alpha,EURUSD,01/11/2017 00:44,2.5", "EURUSD,01/11/2017 04:00,1.1619,1.1646,1.1659,1.1607,7000,01/11/2017 04:59,3500,Beta,EURUSD,01/11/2017 04:12,3.23" ), class = "factor")), row.names = c(NA, 5L), class = "data.frame")

Upvotes: 0

Views: 81

Answers (1)

user11384926
user11384926

Reputation: 1

Here are the details of my sessionInfo sessionInfo() R version 3.5.2 (2018-12-20) Platform: i386-w64-mingw32/i386 (32-bit) Running under: Windows 7 (build 7601) Service Pack 1

Upvotes: 0

Related Questions