data filter by date in R -

i have data this:

date             expiry          close 2009-05-01       2009-06-26       12 2009-05-01       2009-05-26       22 2009-05-01       2010-05-23       36 2009-05-01       2009-07-26       32 2009-12-01       2009-12-26       33 2009-12-01       2010-01-24       36 2009-12-01       2010-02-26       32 

now want filter data(row wise) dates expiry lies in same month of date or expiry lie in next immediate month of date. if expiry lie beyond next immediate month of date, want exclude them.

so here want desired data:

2009-05-01       2009-06-26       12    #next immediate month   2009-05-01       2009-05-26       22    #same month 2009-12-01       2009-12-26       33    #same month 2009-12-01       2010-01-24       36    #next immediate month 

i have both date , expiry in posixlt format. please help. have such 80000 observations.

i'd try readable , straightforward.

first, clean dataframe:

df <- data.frame(date= c('2009-05-01', '2009-05-01', '2009-05-01', '2009-05-01', '2009-12-01', '2009-12-01', '2009-12-01'),                  expiry=c('2009-06-26', '2009-05-26','2010-05-23', '2009-07-26', '2009-12-26', '2010-01-24', '2010-02-26'),                  close=c(12,22,36,32,33,36,32))  df$date <- as.date(df$date) df$expiry <- as.date(df$expiry) 

in case come across this:

2000-12-01       2010-02-26 

the next line remove these situations years jump.

df <- df[which( (year(df$expiry) - year(df$date)>=0) & (year(df$expiry) - year(df$date)<2) ),] 

first case: rows difference 1 month in same year.

df1 <- df[which( (month(df$expiry) - month(df$date)>=0) & (month(df$expiry) - month(df$date)<2) & (year(df$expiry) == year(df$date))),] 

next case: rows difference 1 month, between jan of expiry , dec of date. since months, 12 + 1 = 1.

df2 <- df[which( (month(df$expiry) ==1) & (month(df$date)==12) & (year(df$expiry) == (year(df$date)+1))),]  total <- rbind(df1, df2)   total 

should give

        date     expiry close 1 2009-05-01 2009-06-26    12 2 2009-05-01 2009-05-26    22 5 2009-12-01 2009-12-26    33 6 2009-12-01 2010-01-24    36 

hope helps.