how to filter the dataframe based on two date columns in R

Question

I have the below code where I am trying to compare 2 date columns and filter the data frame based on the condition.

Input:

id ST_DATE     ND_DATE      SYSDATE    S_END_DATE 
1  2017-07-28  2017-08-27   2016-11-09 2017-01-08
2  2017-07-28  2017-08-27   2015-12-16 2017-10-19   

chargefmv1$ST_DATE=as.Date(chargefmv1$ST_DATE , format = "%Y-%m-%d")
chargefmv1$S_END_DATE=as.Date(chargefmv1$S_END_DATE , format = "%Y-%m-%d")
chargefmv1$ND_DATE=as.Date(chargefmv1$ND_DATE , format = "%Y-%m-%d")
chargefmv1$SYSDATE=as.Date(chargefmv1$SYSDATE , format = "%Y-%m-%d")


chargefmv2 <- chargefmv1 %>% filter((as.Date(ST_DATE) <= as.Date(S_END_DATE))
                                    && (as.Date(ND_DATE) >= as.Date(SYSDATE)))

It is not throwing me any error or returning the results. Can anyone help me ?

You should provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input data. It's likely a problem with your data but that's not included in the question. — MrFlick, Nov 09 '17 at 16:34
If by "not returning the results" you mean nothing showing up in the console when you run it, it shouldn't, because you are storing the results in `chargefmv2` — acylam, Nov 09 '17 at 16:35
See if my solution gets you what you want. For future questions, you might also want to provide the expected output, so people can more easily compare their solutions with what you expect. — acylam, Nov 09 '17 at 17:33

acylam · Accepted Answer · 2017-11-09T17:41:28.500

The issue is in the use of && versus &.

From ?`&&`:

& and && indicate logical AND and | and || indicate logical OR. The shorter form performs elementwise comparisons in much the same way as arithmetic operators. The longer form evaluates left to right examining only the first element of each vector.

So we actually want &, because you are performing elementwise comparisons. The following works:

chargefmv2 = chargefmv1 %>% 
  filter((ST_DATE <= S_END_DATE) & (ND_DATE >= SYSDATE))

Result:

  id    ST_DATE    ND_DATE    SYSDATE S_END_DATE
1  2 2017-07-28 2017-08-27 2015-12-16 2017-10-19

Note:

Another thing to note is that you have already previously converted the date columns with as.Date, so there is no need for additional as.Date's in filter.

To get rid of the multiple as.Date statements altogether, you can use mutate_at (safer) or mutate_if from dplyr:

chargefmv2 = chargefmv1 %>% 
  mutate_at(vars(ST_DATE:S_END_DATE), as.Date, format = "%Y-%m-%d") %>%
  filter((ST_DATE <= S_END_DATE) & (ND_DATE >= SYSDATE))

Data:

chargefmv1 = read.table(text = "id ST_DATE     ND_DATE      SYSDATE    S_END_DATE 
                1  2017-07-28  2017-08-27   2016-11-09 2017-01-08
                2  2017-07-28  2017-08-27   2015-12-16 2017-10-19 ", header = TRUE, stringsAsFactors= FALSE)

# No need to run these if you use `mutate_if`
chargefmv1$ST_DATE=as.Date(chargefmv1$ST_DATE , format = "%Y-%m-%d")
chargefmv1$S_END_DATE=as.Date(chargefmv1$S_END_DATE , format = "%Y-%m-%d")
chargefmv1$ND_DATE=as.Date(chargefmv1$ND_DATE , format = "%Y-%m-%d")
chargefmv1$SYSDATE=as.Date(chargefmv1$SYSDATE , format = "%Y-%m-%d")

how to filter the dataframe based on two date columns in R

1 Answers1