Douglas Karabasz
2012-Aug-05 21:49 UTC
[R] R: Help xts object Subset Date by Day of the Week
I have a xts object made of daily closing prices I have acquired using
quantmod.
Here is my code:
library(xts)
library(quantmod)
library(lubridate)
# Gets SPY data
getSymbols("SPY")
# Subset Prices to just closing price
SP500 <- Cl(SPY)
# Show day of the week for each date using 2-6 for monday-friday
SP500wd <- wday(SP500)
# Add Price and days of week together
SP500wd <- cbind(SP500, SP500wd)
# subset Monday into one xts object
SPmon <- subset(SP500wd, SP500wd$..2=="2")
I then used the package lubridate to show the days of the week. Due to the
requirement of an xts objects to be numeric you will see each day is
represented as a number so that Monday is =2, Tuesday=3, Wednesday=4,
Thursday=5, Friday=6, Saturday=7. Since this is a financial index you will
only see the numbers 2-6 or Monday-Friday.
I want to subset the data by using the day column. I would like some help
to figure out the best way to accomplish a few objectives.
1. Subset the data so that I only show Monday in sequence. However, I
do want to make sure that it shows the date, price and the ..2 colum(which
is the day of week) after Sub setting the data (I have it done but not sure
if it is the best way)
2. Rearrange the object (hopefully without destroying the xts object)
so that my data lines up like a weekly calendar. So it would look like the
follow.
Long Date Monday
Monday Price
Monday Day Index
Long Date Tuesday
Tuesday Price
Tuesday Day Index
Long Date Wednesday
Wednesday Price
Wednesday Index
Long Date Thursday
Thursday Price
Thursday Index
Friday
Friday Price
Friday Index
1/5/2009
92.85
2
1/6/2009
93.47
3
1/7/2009
90.67
4
1/8/2009
84.4
5
1/9/2009
89.09
6
1/12/2009
86.95
2
1/13/2009
87.11
3
1/14/2009
84.37
4
1/15/2009
91.04
5
1/16/2009
85.06
6
MLK Mondy
MLK Monday
MLK Monday
1/20/2009
80.57
3
1/21/2009
84.05
4
1/22/2009
82.75
5
1/23/2009
83.11
6
1/26/2009
83.68
2
1/27/2009
84.53
3
1/28/2009
87.39
4
1/29/2009
84.55
5
1/30/2009
82.83
6
Thank you,
Douglas
[[alternative HTML version deleted]]
R. Michael Weylandt
2012-Aug-06 21:30 UTC
[R] R: Help xts object Subset Date by Day of the Week
On Sun, Aug 5, 2012 at 4:49 PM, Douglas Karabasz <douglas at sigmamonster.com> wrote:> I have a xts object made of daily closing prices I have acquired using > quantmod. > > > > Here is my code: > > library(xts) > > library(quantmod) > > library(lubridate) > > > > # Gets SPY data > > getSymbols("SPY") > > # Subset Prices to just closing price > > SP500 <- Cl(SPY) > > # Show day of the week for each date using 2-6 for monday-friday > > SP500wd <- wday(SP500) > > # Add Price and days of week together > > SP500wd <- cbind(SP500, SP500wd) > > # subset Monday into one xts object > > SPmon <- subset(SP500wd, SP500wd$..2=="2") > > > > > > I then used the package lubridate to show the days of the week. Due to the > requirement of an xts objects to be numeric you will see each day is > represented as a number so that Monday is =2, Tuesday=3, Wednesday=4, > Thursday=5, Friday=6, Saturday=7. Since this is a financial index you will > only see the numbers 2-6 or Monday-Friday. > > I want to subset the data by using the day column. I would like some help > to figure out the best way to accomplish a few objectives. > > 1. Subset the data so that I only show Monday in sequence. However, I > do want to make sure that it shows the date, price and the ..2 colum(which > is the day of week) after Sub setting the data (I have it done but not sure > if it is the best way)I think what you do works, this might also be a one liner: SPY[format(index(SPY), "%a") == "Mon", ] Alternatively split.default(SPY, format(index(SPY), "%a")) creates a list of xts objects split by day of the week (Note you need split.default here because split.xts does something different)> > 2. Rearrange the object (hopefully without destroying the xts object) > so that my data lines up like a weekly calendar. So it would look like the > follow.Unfortunately, your formatting got all chewed up by the R-help server, which doesn't like HTML so I'm not quite sure what you want here. Possibly some black magic like this? SPY.CL <- Cl(SPY) length(SPY.CL) <- 7*floor(length(SPY.CL)/7) dim(SPY.CL) <- c(length(SPY.CL)/7, 7) But note that this looses time stamps because each row can only have a single time stamp. You might also try to.weekly() Cheers, Michael> > > > > Long Date Monday > > Monday Price > > Monday Day Index > > Long Date Tuesday > > Tuesday Price > > Tuesday Day Index > > Long Date Wednesday > > Wednesday Price > > Wednesday Index > > Long Date Thursday > > Thursday Price > > Thursday Index > > Friday > > Friday Price > > Friday Index > > > 1/5/2009 > > 92.85 > > 2 > > 1/6/2009 > > 93.47 > > 3 > > 1/7/2009 > > 90.67 > > 4 > > 1/8/2009 > > 84.4 > > 5 > > 1/9/2009 > > 89.09 > > 6 > > > 1/12/2009 > > 86.95 > > 2 > > 1/13/2009 > > 87.11 > > 3 > > 1/14/2009 > > 84.37 > > 4 > > 1/15/2009 > > 91.04 > > 5 > > 1/16/2009 > > 85.06 > > 6 > > > MLK Mondy > > MLK Monday > > MLK Monday > > 1/20/2009 > > 80.57 > > 3 > > 1/21/2009 > > 84.05 > > 4 > > 1/22/2009 > > 82.75 > > 5 > > 1/23/2009 > > 83.11 > > 6 > > > 1/26/2009 > > 83.68 > > 2 > > 1/27/2009 > > 84.53 > > 3 > > 1/28/2009 > > 87.39 > > 4 > > 1/29/2009 > > 84.55 > > 5 > > 1/30/2009 > > 82.83 > > 6 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Thank you, > > Douglas > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.