R: aggregating values between two time periods -

i have dataframe trades, along open times of these trades. i'm trying find equity @ time of trade. however, have equity @ beginning of each hour, i'm adding deposits/withdrawals between start of hour , time of trade equity.

i have method doing loop, indicated below:

 (i in 1:nrow(tradedata))         {              tradedata$equityusd [i] = hourlydata$equityusd[which(as.character(hourlydata$period) ==              min(hourlydata$period[as.character(hourlydata$period) >= as.character(tradedata$opentime [i])]))-1] #find equity of start of hour           tradedata$equityusd [i] = tradedata$equityusd [i] + sum(tradedata$profitusd[tradedata$closetime <= tradedata$opentime[i] &               tradedata$closetime >= tradehour$tradehour[i]]) #add profit realised closed trades before current trade, in current hour           tradedata$equityusd [i] = tradedata$equityusd [i] + sum(currentnetdep$depositwithdrawal[currentnetdep$time <= tradedata$opentime [i] &currentnetdep$time >= tradehour$tradehour[i]])      #add deposits/withdrawals closed trades before current trade, in current hour         } 

the hourlydata dataframe contains equity @ beginning of every hour, on hour.

this works, placing equity @ time of trade tradedata table. however, incredibly slow, if client has lot of trades in time period.

here sample of hourlydata dataframe:

login   netexposure equityusd        period            173929  108791.2    1000         2014-01-16 18:00:00.000 173929  108792.0    1200         2014-01-16 19:00:00.000 173929  108737.6    1500         2014-01-16 20:00:00.000 173929  108794.4    1300         2014-01-16 21:00:00.000 173929  108913.6    1400         2014-01-16 22:00:00.000 173929  108947.2    1600         2014-01-16 23:00:00.000     

and sample of deposit data:

 login    deposittime             depositamount  173929   2014-01-16 19:30:00      100  173929   2014-01-16 19:50:00      -200 

and sample of tradedata dataframe:

login        opentime            closetime              equity  173929      2014-01-16 18:30:00  2014-01-16 33:00:00     1000 173929      2014-01-16 18:35:00  2014-01-16 18:40:00     1000      173929      2014-01-16 19:38:00  2014-01-16 22:32:00     1200 + 100 deposit     173929      2014-01-16 19:40:00  2014-01-16 19:50:00     1200 + 100 deposit      173929      2014-01-16 19:51:30  2014-01-16 19:59:40     1200 + 100 - 200 deposit 

the first trade has equity equal equity @ beginning of hour. same true of second trade third trade has equity equal equity @ beginning of hour + deposit made in same hour, before trade. same true of fourth trade, contains 2 deposits post beginning of hour, prior current trade

please not i've adjusted answer no longer require profit calculation

does know of more efficient manner in this? also, please let me know if unclear in above.

thanks!

mike

i use ave , cumsum sum deposits within each hour, add these equity @ beginning of hour.

i think want.

# hourly data hourlydata <- read.table(header=true, text=" login   netexposure equityusd        period            173929  108791.2    1000         '2014-01-16 18:00:00.000' 173929  108792.0    1200         '2014-01-16 19:00:00.000' 173929  108737.6    1500         '2014-01-16 20:00:00.000' 173929  108794.4    1300         '2014-01-16 21:00:00.000' 173929  108913.6    1400         '2014-01-16 22:00:00.000' 173929  108947.2    1600         '2014-01-16 23:00:00.000'                              ") hourlydata$period <- strptime(hourlydata$period, format="%y-%m-%d %h:%m:%os")  # trade data tradedata <- read.table(header=true, text=" login        opentime            closetime               173929      '2014-01-16 18:30:00'  '2014-01-16 18:33:00' 173929      '2014-01-16 18:35:00'  '2014-01-16 18:40:00' 173929      '2014-01-16 19:38:00'  '2014-01-16 22:32:00' 173929      '2014-01-16 19:40:00'  '2014-01-16 19:50:00' 173929      '2014-01-16 19:51:30'  '2014-01-16 19:59:40'                          ") tradedata$opentime <- strptime(tradedata$opentime, format="%y-%m-%d %h:%m:%os") tradedata$closetime <- strptime(tradedata$closetime, format="%y-%m-%d %h:%m:%os")  # deposit data depositdata <- read.table(header=true, text=" login deposittime depositamount  173929 '2014-01-16 19:30:00' 100  173929 '2014-01-16 19:50:00' -200                          ") depositdata$deposittime <- strptime(depositdata$deposittime, format="%y-%m-%d %h:%m:%os")  # merge merged <- merge(x=hourlydata, y=depositdata,                  by.x=c("login", "period"), by.y=c("login", "deposittime"),                 all=true)  # running sum of deposits within each hour merged$running <- ave(merged$depositamount,                       merged$login, format(merged$period, "%y-%m-%d %h"),                       fun=function(x) {                           xx <- ifelse(is.na(x), 0, x)                           cumsum(xx)                       })  # carry-forward top-of-the-hour equity merged$equityusd1 <- ave(merged$equityusd,                       merged$login, format(merged$period, "%y-%m-%d %h"),                       fun=function(x) ifelse(is.na(x), x[1], x))  # add running sum merged$equityusd1 <- merged$equityusd1 + merged$running