Create a vector in R by summing rows based on multiple criteria -


i have financial data in 15 minute intervals, want convert intervals 15 minutes 30 minutes before conduct rest of analysis. such, sum traded volumes 2 adjacent 15 minute intervals , take closing price of second 15 minute sub-interval (ie end of 30 minute period).

i have shown below example of data (df) , desired output (df.30min) using sapply function. works fine example below, given analysing 10 years of daily data 50 companies , 27 intervals per day processing time excessive, 1 year of data. have similar issues if try loop.

i new r hoping there easy solution using 1 of built in functions.

in actual dataset there 27 x 15 minute intervals (10:00-16:45). final "30 minute" dataset have 1 15 minute interval 13:30-13:45. also, there may other anomalies stock exchange opened late / closed or stock put on trading halt partway through day. (i have managed map times in data correct interval using lookup table match function.) given imperfect structure of data after solution not reliant on complete set , number of 15 minute intervals. in excel use sumifs function.

set.seed(1) df <- data.frame(   company = rep(c("co a", "co b", "co c"), each = 8),   date = as.date(rep(c("2005-01-01", "2005-01-02"), times = 3, each = 4)),   time = as.factor(c("10:00:00", "10:15:00", "10:30:00", "10:45:00")),   interval = as.factor(c(1,1,2,2)),   interval.end = as.factor(c(0,1)),   close = abs(round(rnorm(24),1))*10+100,   volume = abs(round(rnorm(24),1))*10)  > df    company       date     time interval interval.end close volume 1     co 2005-01-01 10:00:00        1            0   106      6 2     co 2005-01-01 10:15:00        1            1   102      1 3     co 2005-01-01 10:30:00        2            0   108      2 4     co 2005-01-01 10:45:00        2            1   116     15 5     co 2005-01-02 10:00:00        1            0   103      5 6     co 2005-01-02 10:15:00        1            1   108      4 7     co 2005-01-02 10:30:00        2            0   105     14 8     co 2005-01-02 10:45:00        2            1   107      1 9     co b 2005-01-01 10:00:00        1            0   106      4 10    co b 2005-01-01 10:15:00        1            1   103      1 11    co b 2005-01-01 10:30:00        2            0   115     14 12    co b 2005-01-01 10:45:00        2            1   104      4 13    co b 2005-01-02 10:00:00        1            0   106      4 14    co b 2005-01-02 10:15:00        1            1   122      1 15    co b 2005-01-02 10:30:00        2            0   111     11 16    co b 2005-01-02 10:45:00        2            1   100      8 17    co c 2005-01-01 10:00:00        1            0   100      2 18    co c 2005-01-01 10:15:00        1            1   109      3 19    co c 2005-01-01 10:30:00        2            0   108      7 20    co c 2005-01-01 10:45:00        2            1   106      6 21    co c 2005-01-02 10:00:00        1            0   109      7 22    co c 2005-01-02 10:15:00        1            1   108      7 23    co c 2005-01-02 10:30:00        2            0   101      4 24    co c 2005-01-02 10:45:00        2            1   120      8  df.30min <- df[-which(df$interval.end == 0),]  df.30min$volume <-sapply(seq_len(nrow(df.30min)),             function(i) sum(df$volume[df$company == df.30min$company[i] &                                         df$date == df.30min$date[i] &                                         df$interval == df.30min$interval[i]]))  > df.30min    company       date     time interval interval.end close volume 2     co 2005-01-01 10:15:00        1            1   102      7 4     co 2005-01-01 10:45:00        2            1   116     17 6     co 2005-01-02 10:15:00        1            1   108      9 8     co 2005-01-02 10:45:00        2            1   107     15 10    co b 2005-01-01 10:15:00        1            1   103      5 12    co b 2005-01-01 10:45:00        2            1   104     18 14    co b 2005-01-02 10:15:00        1            1   122      5 16    co b 2005-01-02 10:45:00        2            1   100     19 18    co c 2005-01-01 10:15:00        1            1   109      5 20    co c 2005-01-01 10:45:00        2            1   106     13 22    co c 2005-01-02 10:15:00        1            1   108     14 24    co c 2005-01-02 10:45:00        2            1   120     12

using library dplyr, can try this:

library(dplyr) df %>% arrange(company, date, time, interval, interval.end) %>% group_by(company, date, interval) %>% summarise(time = time[2], interval.end = interval.end[2], close = close[2], volume = sum(volume)) source: local data frame [12 x 7] groups: company, date [?]     company       date interval     time interval.end close volume     (fctr)     (date)   (fctr)   (fctr)       (fctr) (dbl)  (dbl) 1     co 2005-01-01        1 10:15:00            1   102      7 2     co 2005-01-01        2 10:45:00            1   116     17 3     co 2005-01-02        1 10:15:00            1   108      9 4     co 2005-01-02        2 10:45:00            1   107     15 5     co b 2005-01-01        1 10:15:00            1   103      5 6     co b 2005-01-01        2 10:45:00            1   104     18 7     co b 2005-01-02        1 10:15:00            1   122      5 8     co b 2005-01-02        2 10:45:00            1   100     19 9     co c 2005-01-01        1 10:15:00            1   109      5 10    co c 2005-01-01        2 10:45:00            1   106     13 11    co c 2005-01-02        1 10:15:00            1   108     14 12    co c 2005-01-02        2 10:45:00            1   120     12

if data frame arranged properly, can rid of arrange part above.

note: assuming there 2 intervals (0, 1) , therefore using hardcoded value of 2. if not case, can use proper subsetting.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more

ruby on rails - Seeing duplicate requests handled with Unicorn -

recently upgraded our ec2 instances c3.large in order boost more unicorn workers handle increased traffic our site. (6xworkers per machine - 2 ec2 instances). when did this, started seeing duplicate records being created! looks somehow, maybe 2 unicorn workers attempting process same request? in nginx logs see 1 [post] request particular rails controller - yet 2 records being created in database. i'm hitting kind of race condition issue - have no idea how debug this. doesn't happen time, when it's frustrating well. noticed 2 database records within 5 seconds of each other. (and yes disable button when user clicks submit button). unicorn 4.8.2 ruby 1.9.3 rails 3.2.14 any great! thanks! although may not fix problem, upgrade ruby 2.0.0. there blog mentioning idea upgrade ruby 2.0.0 if using unicorn. for example: https://www.digitalocean.com/community/articles/how-to-optimize-unicorn-workers-in-a-ruby-on-rails-app specifically: if using ruby 1.9, ...
Read more