CODEWITHSUNDEEP
rdate
Mike
Mike

Reputation: 1069

R: Applying a function between two dates

I have two data frames, as follows:
A data frame with trades: tradeData (sample):

Login   OpenTime    CloseTime   Decision
859     13/01/2014  13/01/2014  1
859     16/01/2014  16/01/2014  1
859     21/01/2014  21/01/2014  1
859     21/01/2014  21/01/2014  1
859     22/01/2014  22/01/2014  1
859     23/01/2014  23/01/2014  1
859     27/01/2014  27/01/2014  1
859     03/02/2014  03/02/2014  1
859     04/02/2014  05/02/2014  1
859     07/02/2014  07/02/2014  1
859     11/02/2014  13/02/2014  1
939     06/02/2014  28/02/2014  1
939     06/02/2014  28/02/2014  1
939     06/02/2014  28/02/2014  1
1455    03/04/2014  03/04/2014  1
1455    04/04/2014  04/04/2014  1
1455    04/04/2014  07/04/2014  1
1455    08/04/2014  08/04/2014  1
1455    08/04/2014  08/04/2014  1
1455    09/04/2014  30/04/2014  1
1455    30/04/2014  30/04/2014  1

and another data frame with dates: datesData (sample): 

Login       B_A        A_B
859     22/01/2014  23/01/2014
859     03/02/2014  07/02/2014
859     11/02/2014  12/02/2014
939     06/02/2014  01/01/2200
1455    04/04/2014  08/04/2014
1455    09/05/2014  30/06/2014

Any trade (that would be a row in the tradeData data frame) that opens between the two dates in any row in the datesData data frame and matches the Login, should receive a 0 in the decision column. It must open on or after the date in the B_A column and open before the date in the A_B column. This decision column is pre-populated with 1's, so all I need to do is insert the 0's

The resulting tradeData data frame would look as follows:

Login   OpenTime    CloseTime   Decision
859     13/01/2014  13/01/2014  1
859     16/01/2014  16/01/2014  1
859     21/01/2014  21/01/2014  1
859     21/01/2014  21/01/2014  1
859     22/01/2014  22/01/2014  0
859     23/01/2014  23/01/2014  1
859     27/01/2014  27/01/2014  1
859     03/02/2014  03/02/2014  0
859     04/02/2014  05/02/2014  0
859     07/02/2014  07/02/2014  1
859     11/02/2014  13/02/2014  0
939     06/02/2014  28/02/2014  0
939     06/02/2014  28/02/2014  0
939     06/02/2014  28/02/2014  0
1455    03/04/2014  03/04/2014  1
1455    04/04/2014  04/04/2014  0
1455    04/04/2014  07/04/2014  0
1455    08/04/2014  08/04/2014  1
1455    08/04/2014  08/04/2014  1
1455    09/04/2014  30/04/2014  0
1455    30/04/2014  30/04/2014  1

So, for example, the fifth row in the tradeData data frame opens on 22/01/2014 and before 23/01/2014 (the first row in the datesDate data frame) and matches the login in that row, so it receives a 0.

Any help would be awesome! Let me know if anything is unclear.

Thanks!

Mike

Upvotes: 1

Views: 899

Answers (2)

alex23lemm
alex23lemm

Reputation: 5675

Here is an etc. solution using the sqldf package.

tradeData$OpenTime <- as.Date(trade.data$OpenTime, format="%d/%m/%Y")
datesData$B_A <- as.Date(datasData$B_A, format="%d/%m/%Y")
datesData$A_B <- as.Date(datasData$A_B, format="%d/%m/%Y")

sqldf(c("UPDATE tradeData
      SET Decision = 0
      WHERE EXISTS (SELECT * FROM datesData WHERE
                      tradeData.Login = datesData.Login AND
                      tradeData.OpenTime >= datesData.B_A  AND
                      tradeData.OpenTime < datesData.A_B)",
      "SELECT * FROM tradeData"))

#    Login   OpenTime  CloseTime Decision
# 1    859 2014-01-13 13/01/2014        1
# 2    859 2014-01-16 16/01/2014        1
# 3    859 2014-01-21 21/01/2014        1
# 4    859 2014-01-21 21/01/2014        1
# 5    859 2014-01-22 22/01/2014        0
# 6    859 2014-01-23 23/01/2014        1
# 7    859 2014-01-27 27/01/2014        1
# 8    859 2014-02-03 03/02/2014        0
# 9    859 2014-02-04 05/02/2014        0
# 10   859 2014-02-07 07/02/2014        1
# 11   859 2014-02-11 13/02/2014        0
# 12   939 2014-02-06 28/02/2014        0
# 13   939 2014-02-06 28/02/2014        0
# 14   939 2014-02-06 28/02/2014        0
# 15  1455 2014-04-03 03/04/2014        1
# 16  1455 2014-04-04 04/04/2014        0
# 17  1455 2014-04-04 07/04/2014        0
# 18  1455 2014-04-08 08/04/2014        1
# 19  1455 2014-04-08 08/04/2014        1
# 20  1455 2014-04-09 30/04/2014        1
# 21  1455 2014-04-30 30/04/2014        1

Upvotes: 2

Colonel Beauvel
Colonel Beauvel

Reputation: 31181

One way would be to use data.table package:

library(data.table)

# convert to dates usefull columns
setDT(tradeData)
setkey(tradeData, Login)
tradeData[,OpenTime:=as.Date(OpenTime, format="%d/%m/%Y")]

# convert to dates usefull columns
df1 = datesData 
df1$B_A = as.Date(df1$B_A, format="%d/%m/%Y")
df1$A_B = as.Date(df1$A_B, format="%d/%m/%Y")

tradeData[,Decision:=sapply(OpenTime,function(d){ 
                          dt=df1[df1$Login==Login,]
                          as.integer(!any(d>=dt$B_A & d<dt$A_B))
                  }),
      by=Login]

Result look like this:

> tradeData
    Login   OpenTime  CloseTime Decision
 1:   859 2014-01-13 13/01/2014        1
 2:   859 2014-01-16 16/01/2014        1
 3:   859 2014-01-21 21/01/2014        1
 4:   859 2014-01-21 21/01/2014        1
 5:   859 2014-01-22 22/01/2014        0
 6:   859 2014-01-23 23/01/2014        1
 7:   859 2014-01-27 27/01/2014        1
 8:   859 2014-02-03 03/02/2014        0
 9:   859 2014-02-04 05/02/2014        0
10:   859 2014-02-07 07/02/2014        1
11:   859 2014-02-11 13/02/2014        0
12:   939 2014-02-06 28/02/2014        0
13:   939 2014-02-06 28/02/2014        0
14:   939 2014-02-06 28/02/2014        0
15:  1455 2014-04-03 03/04/2014        1
16:  1455 2014-04-04 04/04/2014        0
17:  1455 2014-04-04 07/04/2014        0
18:  1455 2014-04-08 08/04/2014        1
19:  1455 2014-04-08 08/04/2014        1
20:  1455 2014-04-09 30/04/2014        1
21:  1455 2014-04-30 30/04/2014        1

Upvotes: 3

Related Questions