2

Here is the code: EDIT: SEE REPRODUCIBLE CODE BELOW

>require("quantmod")   
>
> corn <- as.xts(read.zoo("~/CORN.csv", sep=",", format ="%m/%d/%Y", header=TRUE))
> 
> head(corn)
           [,1]
1962-01-03 4.03
1962-01-04 3.99
1962-01-05 4.02
1962-01-08 4.03
1962-01-09 4.05
1962-01-10 4.07
> 
> corn <- to.weekly(corn)[,4]
> 
> head(corn)
           corn.Close
1962-01-05       4.02
1962-01-12       4.08
1962-01-19       4.11
1962-01-26       4.11
1962-02-02       4.08
1962-02-09       4.05

How do you start every week on Tuesday? Something along the lines of 

indexAt='startof("Tuesday")'

where indexAt is a parameter variable in the to.weekly() function.

The purpose for this is to line up with weekly COT data.

EDIT ##################

I've created some confusion by not providing reproducible code, so here is some with a partial solution incorporated based on suggestions by J. Winchester below:

> getSymbols("GLD")
[1] "GLD"
> GLD <- GLD[,4]
> head(GLD, n=2)
           GLD.Close
2007-01-03     62.28
2007-01-04     61.65
> tues <- weekdays(time(GLD)) == "Tuesday"
> gold <- merge(GLD, tues)
> head(gold, n=5)
           GLD.Close tues
2007-01-03     62.28    0
2007-01-04     61.65    0
2007-01-05     60.17    0
2007-01-08     60.48    0
2007-01-09     60.85    1
Milktrader
  • 9,278
  • 12
  • 51
  • 69

2 Answers2

3

How about this (based on the dummy data frame from Alaiacano).

corn$tuesdays <- weekdays(corn$dates) == "Tuesday"

# count days from any partial week at the beginning
start_len <- ifelse(corn$tuesdays[1], 0, rle(corn$tuesdays)$lengths[1])

# assign a week value to every row
corn$week <- c(rep(0, start_len), 1 + seq_len(nrow(corn) - start_len) %/% 7)

# concatenate the start date of the first (possibly incomplete) week
# to the start dates for all the following weeks
week_starts <- as.Date(corn$dates[corn$tuesdays])
if(start_len > 0) week_starts <- c(week_starts[1] - 7, week_starts) 

# calculate weekly means and assemble to a data frame
corn_values <- aggregate(value~week, data = corn, FUN = mean)$value
corn_weekly <- data.frame(week_starts, corn_values)
J. Win.
  • 6,662
  • 7
  • 34
  • 52
  • I've incorporated part of your solution above with reproducible code. – Milktrader Mar 29 '11 at 01:53
  • After I substituted corn$dates with time(corn), I got this to work up until the weekly means calculation, but these are issues particular to my data. It works on the dummy data. There are no parameter values to pass to to.weekly or some similar function to have the week start on Tuesday, so these extra steps are required. Thanks. – Milktrader Mar 31 '11 at 01:14
0

There may be a more elegant soltuion, but this should do the trick

# generate some fake data for 2011
corn <- data.frame(dates=as.Date("2011-01-01") + seq(0,365), value=runif(366))

# get the day of week
corn$dow <- chron::day.of.week(as.numeric(strftime(corn$dates, "%Y")),
                               as.numeric(strftime(corn$dates, "%m")),
                               as.numeric(strftime(corn$dates, "%d")))
# take subset     
corn <- subset(corn, dow==2)

Output looks like this:

> head(corn)
        dates      value dow
11 2011-01-11 0.54688767   2
17 2011-01-17 0.22249506   2
22 2011-01-22 0.61725913   2
28 2011-01-28 0.45681763   2
36 2011-02-05 0.77839486   2
41 2011-02-10 0.07201445   2

You can drop the day of week and/or use the row.names instead of corn$dates

alaiacano
  • 693
  • 1
  • 5
  • 10
  • generating a bunch of NAs on my data and seems erratic (not every seven days) on the dummy data. – Milktrader Mar 28 '11 at 21:29
  • I'm afraid I tried your solution on a data.frame that I had in my environment with the same name. My question is to a zoo, xts object so I miswrote when I stated your solution yielded a bunch of NAs. The corn$dates variable doesn't exist. You can use time(corn) to get the date variable though. – Milktrader Mar 29 '11 at 01:10