CODEWITHSUNDEEP
r
blackhawks797
blackhawks797

Reputation: 73

R: summing up daily data into weekly data

I have a large data file of stock data.

I have organized it by ticker and data:

Ticker   Date        Vol.
A        20050501     23
A        20050502     45
AB       20050501    100
AV       20050503    100
BC       20050501     50

How would I group and sum the data into weekly volume while still keeping the same alphabetical and date format? For example, summing 23 and 45 and putting an entry that would be as below:

A    20050501  68
AB   20050501  100

I would want to list all the weekly sums of volume for A, followed by all the weekly sums for AB, etc.

Thank you.

Upvotes: 0

Views: 640

Answers (1)

bjoseph
bjoseph

Reputation: 2166

Here is a solution using lubridate and dplyr:

require(lubridate)
require(dplyr)
set.seed(1)
Ticker<-rep(c("a","b","c"),6)
Vol.<-rnorm(18,100,100)
Date<-c("20050501","20050502","20050503","20050504","20050505","20050506","20050507","20050508","20050509",
         "20050510","20050511","20050512","20050513","20050514","20050515","20050516","20050517","20050518")
dat<-data.frame(Ticker,Date,Vol.)
dat$Date<-ymd(dat$Date)
dat$Week<-week(dat$Date)
dat_summary<-dat%>%
  group_by(Week,Ticker)%>%
  summarize(sumweek=sum(Vol.))

dat_summary
#Source: local data frame [9 x 3]
#Groups: Week

#  Week Ticker   sumweek
#1   18      a 296.88270
#2   18      b 251.31511
#3   18      c  16.43714
#4   19      a 218.20407
#5   19      b 425.01059
#6   19      c 314.51562
#7   20      a 133.38258
#8   20      b -23.08902
#9   20      c 406.87671

if you have more than one year in your data, then you will want to create a "year column" dat$Year<-year(dat$Date) and then group by "week and year" group_by(Week,Year,Ticker) .

Upvotes: 1

Related Questions