4

I currently have a column "Month" & a column "DayWeek" with the Month and Day of the week written out. Using the code below I can get a column with a 1 for each Wednesday in Feb, May, Aug & Nov. Im struggling to find a way to get a column with 1s just for the first Wednesday of each of the 4 months I just mentioned. Any ideas or do I have to create a loop for it?

testPrices$Rebalance <- ifelse((testPrices$Month=="February" & testPrices$DayWeek == "Wednesday"),1,ifelse((testPrices$Month=="May" & testPrices$DayWeek == "Wednesday"),1,ifelse((testPrices$Month=="August" & testPrices$DayWeek == "Wednesday"),1,ifelse((testPrices$Month=="November" & testPrices$DayWeek == "Wednesday"),1,0))))
bartektartanus
  • 12,415
  • 4
  • 70
  • 92
Trevor Nederlof
  • 2,098
  • 3
  • 18
  • 36

2 Answers2

5

Well, without a reproducible example, I couldn't come up with a complete solution, but here is a way to generate the first Wednesday date of each month. In this example, I start at 1 JAN 2013 and go out 36 months, but you can figure out what's appropriate for you. Then, you can check against the first Wednesday vector produced here to see if your dates are members of the first Wednesday of the month group and assign a 1, if so.

# I chose this as an origin
orig <- "2013-01-01"

# generate vector of 1st date of the month for 36 months
d <- seq(as.Date(orig), length=36, by="1 month")

# Use that to make a list of the first 7 dates of each month
d <- lapply(d, function(x) as.Date(seq(1:7),origin=x)-1) 

# Look through the list for Wednesdays only, 
# and concatenate them into a vector
do.call('c', lapply(d, function(x) x[strftime(x,"%A")=="Wednesday"]))

Output:

 [1] "2013-01-02" "2013-02-06" "2013-03-06" "2013-04-03" "2013-05-01" "2013-06-05" "2013-07-03"
 [8] "2013-08-07" "2013-09-04" "2013-10-02" "2013-11-06" "2013-12-04" "2014-01-01" "2014-02-05"
[15] "2014-03-05" "2014-04-02" "2014-05-07" "2014-06-04" "2014-07-02" "2014-08-06" "2014-09-03"
[22] "2014-10-01" "2014-11-05" "2014-12-03" "2015-01-07" "2015-02-04" "2015-03-04" "2015-04-01"
[29] "2015-05-06" "2015-06-03" "2015-07-01" "2015-08-05" "2015-09-02" "2015-10-07" "2015-11-04"
[36] "2015-12-02"

Note: I adapted this code from answers found here and here.

Community
  • 1
  • 1
Jota
  • 16,103
  • 7
  • 54
  • 89
3

I created a sample dataset to work with like this (Thanks @Frank!):

orig <- "2013-01-01"
d <- data.frame(date=seq(as.Date(orig), length=1000, by='1 day'))
d$Month <- months(d$date)
d$DayWeek <- weekdays(d$date)
d$DayMonth <- as.numeric(format(d$date, '%d'))

From a data frame like this, you can extract the first Wednesday of specific months using subset, like this:

subset(d, Month %in% c('January', 'February') & DayWeek == 'Wednesday' & DayMonth < 8)

This takes advantage of the fact that the day number (1..31) will always be between 1 to 7, and obviously there will be precisely one such day. You could do similarly for 2nd, 3rd, 4th Wednesday, changing the condition to accordingly, for example DayMonth > 7 & DayMonth < 15.

Community
  • 1
  • 1
janos
  • 109,862
  • 22
  • 193
  • 214
  • @Frank Ah, you're absolutely right! I corrected my answer now. – janos Jul 12 '14 at 16:42
  • I really liked how you used subset to do this, makes so much sense. I will incorporate this into my code, makes it really easy to find specific dates. Thanks again. – Trevor Nederlof Jul 13 '14 at 22:22