Filtering dates in dplyr

Question

My tbl_df:

    > p2p_dt_SKILL_A%>%     + select(Patch,Date,Prod_DL)%>%     + head()       Patch       Date Prod_DL     1  P1 2015-09-04    3.43     2 P11 2015-09-11    3.49     3 P12 2015-09-18    3.45 ...     4 P13 2015-12-06    3.57     5 P14 2015-12-13    3.43     6 P15 2015-12-20    3.47 

I want to select all rows based on the date for example if Date is greater than 2015-09-04 and less than 2015-09-18

The result should be:

      Patch       Date          Prod_DL       P1        2015-09-04    3.43       P11       2015-09-11    3.49 

I tried the following but it returns empty empty vector.

p2p_dt_SKILL_A%>%                 select(Patch,Date,Prod_DL)%>%                 filter(Date > "2015-09-04" & Date <"2015-09-18") 

Just returns:

> p2p_dt_SKILL_A%>% +                 select(Patch,Date,Prod_DL)%>% +                 filter(Date > 2015-09-12 & Date <2015-09-18) Source: local data table [0 x 3]  Variables not shown: Patch (fctr), Date (date), Prod_DL (dbl) 

Also tried with quotes.

And using lubridate

p2p_dt_SKILL_A%>%                 select(Patch,Date,Prod_DL)%>%                 #filter(Date > 2015-09-12 & Date <2015-09-18)%>%                 filter(Patch %in% c("BVG1"),month(p2p_dt_SKILL_A$Date) == 9)%>%                 arrange(Date) 

But this gives me whole September data.

Is there a more efficient way like using the between operator from dplyr on Date types variables?

Answer 1

If Date is properly formatted as a date, your first try works:

p2p_dt_SKILL_A <-read.table(text="Patch,Date,Prod_DL P1,9/4/2015,3.43 P11,9/11/2015,3.49 P12,9/18/2015,3.45 P13,12/6/2015,3.57 P14,12/13/2015,3.43 P15,12/20/2015,3.47 ",sep=",",stringsAsFactors =FALSE, header=TRUE)  p2p_dt_SKILL_A$Date <-as.Date(p2p_dt_SKILL_A$Date,"%m/%d/%Y")  p2p_dt_SKILL_A%>%                 select(Patch,Date,Prod_DL)%>%                 filter(Date > "2015-09-04" & Date <"2015-09-18")   Patch       Date Prod_DL 1 P11 2015-09-11    3.49 

---

---

Still works if data is of type tbl_df.

p2p_dt_SKILL_A <-tbl_df(p2p_dt_SKILL_A)  p2p_dt_SKILL_A%>%                 select(Patch,Date,Prod_DL)%>%                 filter(Date > "2015-09-04" & Date <"2015-09-18") Source: local data frame [1 x 3]    Patch       Date Prod_DL   (chr)     (date)   (dbl) 1 P11 2015-09-11    3.49 

Answer 2

Another more verbose option would be to use the function between, a shortcut for x >= left & x <= right. We need to change the days to account for the = sign, and to use as.Date (explanation here).

p2p_dt_SKILL_A%>%                 select(Patch,Date,Prod_DL)%>%                 filter(between(Date, as.Date("2015-09-05"),as.Date("2015-09-17")))