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?

Can you add str(p2p_dt_SKILL_A) in your question. I want to see if Date is a `date` object or something else — Pierre Lapointe, Dec 11 '15 at 21:44
@PLapointe its already there in the return part. `Variables not shown: Patch (fctr), Date (date), Prod_DL (dbl)` ... Its a Date type — Shery, Dec 11 '15 at 21:57
The dates are not quoted (") in that part of your code. Using your code just above that, it works. Look at my answer below. — Pierre Lapointe, Dec 11 '15 at 21:59
@PLapointe tried with both quotes and unquotes...doesn't work... my dataframe is of type `tbl_df` — Shery, Dec 11 '15 at 22:03
Does this answer your question? [Subset a dataframe between 2 dates](https://stackoverflow.com/questions/23622338/subset-a-dataframe-between-2-dates) — camille, Sep 14 '21 at 19:05

score 45 · Accepted Answer · edited Aug 12 '20 at 16:45

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

score 19 · Answer 2 · edited Aug 12 '20 at 16:43

19

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")))

edited Aug 12 '20 at 16:43

M--

25,431
8
61
93

answered Nov 30 '17 at 09:17

mpalanco

12,960
2
59
67

Filtering dates in dplyr

2 Answers2

Linked