0

I'm very new to R. I have a rather large data frame on trades and need to sort out the ones that occurred on the 3rd fridays only (for now).

What would be the best way of doing it? Thank you in advance.

Below is a small sample

    symbol  t_date  exchange    price   hv10    hv20

      AA    4/25/2011   NYSE    16.89   0.3797  0.3024
      AA    4/26/2011   NYSE    17.031  0.3859  0.3022
      AA    4/27/2011   NYSE    17.18   0.2141  0.2991
      AA    4/28/2011   NYSE    17.09   0.209   0.2974
      AA    4/29/2011   NYSE    17      0.2129  0.2975
      AA    5/2/2011    NYSE    17.22   0.2169  0.2999
      AA    5/3/2011    NYSE    17.67   0.1768  0.3139
Improve this question
4
  • 1
    possible duplicate of How to figure third Friday of a month in R Commented May 2, 2014 at 14:26
  • @Henrik no. the goal is to write a new data set with all the relevant info. Not to just figure the dates Commented May 2, 2014 at 14:28
  • Using Henrik's suggestion, it's data_set[t_date == getNthDayOfWeek(third, Fri, Apr, 2011), ] Commented May 2, 2014 at 14:30
  • @maloneypatr . There are 36 month of info. the above will only take care of one month Commented May 2, 2014 at 14:34

2 Answers 2

Reset to default
2

Here's a loop to get you the Fridays using Henrik's suggestion.

years <- 2011:2013
months <- month.abb

third_fridays_inner <- c()
third_fridays_outer <- c()

for(year in years){
  require(RcppBDT)

  theYear <- year 

  for(month in months){
      third_friday <- 
        getNthDayOfWeek(third, 5, do.call(get, list(month)), theYear)
      third_fridays_inner <- c(third_fridays_inner, third_friday)
  }

  third_fridays_outer <- unique(c(third_fridays_outer, third_fridays_inner))

}

third_fridays <- as.Date(third_fridays_outer, origin = '1970-01-01')
third_fridays

 [1] "2011-01-21" "2011-02-18" "2011-03-18" "2011-04-15" "2011-05-20" "2011-06-17"
 [7] "2011-07-15" "2011-08-19" "2011-09-16" "2011-10-21" "2011-11-18" "2011-12-16"
[13] "2012-01-20" "2012-02-17" "2012-03-16" "2012-04-20" "2012-05-18" "2012-06-15"
[19] "2012-07-20" "2012-08-17" "2012-09-21" "2012-10-19" "2012-11-16" "2012-12-21"
[25] "2013-01-18" "2013-02-15" "2013-03-15" "2013-04-19" "2013-05-17" "2013-06-21"
[31] "2013-07-19" "2013-08-16" "2013-09-20" "2013-10-18" "2013-11-15" "2013-12-20"

Then, you have to convert the date in your original data set.

data_set$t_date <- as.Date(data_set$t_date, foramt = '%m/%d/%Y')

Then, you can filter down your original data set to third_fridays.

library(dplyr)
data_set %.% filter(t_date %in% third_fridays)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

How do I filter out blocks of data between two consecutive dates from the list. For example: between 1st date and 2nd and write it to separate data frame, then 2nd and 3rd, and so on
Hey Alex- Sorry for the long delay...as I just saw this now. I would look into the cut function. Something like: cut(t_date, breaks = third_fridays). You might have to convert the date into a numeric, but I think the logic is captured within that function.
1

You can add day of the week and then day number to the data frame. Then filter on day = Fri and day number is 15-21 I'm pretty sure this could be done more succinctly but hopefully this will give you more of an idea of what is happening.

#Put the date into ISO format
df$t_date <- as.Date(df$t_date,format="%m/%d/%Y")

#Add in the day abbreviation of week
df$day <- format(df$t_date,"%a")

#Add in the day number of week
df$dayno <- format(df$t_date,"%d")

#filter the data frame to just fridays
df <- df[df$day =="Fri" ,]

#filter the data frame to just the day numbers that occur in the third week
df <- df[(df$dayno>=15 & df$dayno <= 21) ,]
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.