Delete dates in a database in R

Question

How do I delete the April dates that are in the date2 column? Here is a small example, but I have a much larger database. So, would I be able to do this quickly?

Thanks!

data <- structure(
      list(Id=c(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1),
           date1 = c("2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20",
                     "2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20",
                     "2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20",
                     "2021-06-20","2021-06-20","2021-06-20","2021-06-20"),
           date2 = c("2021-07-01","2021-07-01","2021-07-01","2021-07-01","2021-04-02",
                     "2021-04-02","2021-06-02","2021-04-02","2021-04-02","2021-04-02","2021-04-03",
                     "2021-05-03","2021-06-03","2021-04-03","2021-04-03","2021-04-08","2021-04-08",
                     "2021-06-09","2021-05-09","2021-08-10","2021-06-10"),
           DR01= c(4,5,6,7,3,2,7,4,2,1,2,3,4,6,7,8,4,2,6,4,3),DR02 = c(9,5,4,3,3,2,1,5,3,7,2,3,4,7,7,8,4,2,6,4,3)),
      class = "data.frame", row.names = c(NA, -21L))

Does this answer your question? [Filter data.frame rows by a logical condition](https://stackoverflow.com/questions/1686569/filter-data-frame-rows-by-a-logical-condition) — user438383, Aug 30 '21 at 18:29

score 4 · Answer 1 · answered Aug 30 '21 at 23:10

We could use month function in lubridate and then filter:

library(dplyr)
library(lubridate)

data %>% 
    filter(month(date2)!=4)

   Id      date1      date2 DR01 DR02
1   1 2021-06-20 2021-07-01    4    9
2   1 2021-06-20 2021-07-01    5    5
3   1 2021-06-20 2021-07-01    6    4
4   1 2021-06-20 2021-07-01    7    3
5   1 2021-06-20 2021-06-02    7    1
6   1 2021-06-20 2021-05-03    3    3
7   1 2021-06-20 2021-06-03    4    4
8   1 2021-06-20 2021-06-09    2    2
9   1 2021-06-20 2021-05-09    6    6
10  1 2021-06-20 2021-08-10    4    4
11  1 2021-06-20 2021-06-10    3    3

score 3 · Accepted Answer · answered Aug 30 '21 at 18:26

3

Extract the month part after converting to Date class and use !=

data2 <- subset(data, format(as.Date(date2), '%m') != '04')

-output

data2
 Id      date1      date2 DR01 DR02
1   1 2021-06-20 2021-07-01    4    9
2   1 2021-06-20 2021-07-01    5    5
3   1 2021-06-20 2021-07-01    6    4
4   1 2021-06-20 2021-07-01    7    3
7   1 2021-06-20 2021-06-02    7    1
12  1 2021-06-20 2021-05-03    3    3
13  1 2021-06-20 2021-06-03    4    4
18  1 2021-06-20 2021-06-09    2    2
19  1 2021-06-20 2021-05-09    6    6
20  1 2021-06-20 2021-08-10    4    4
21  1 2021-06-20 2021-06-10    3    3

answered Aug 30 '21 at 18:26

akrun

874,273
37
540
662

Perfect Akrun, thanks! Just one thing: If my database was date 2 with the format 01-07-2021, would it change the code you entered? – Antonio Aug 30 '21 at 18:32
@Jose yes, dates are always a problem in formatting. You can specify the `format` in `as.Date(date2, format = '%m-%d-%Y')` if it is in m-d-y format or specify `%d-%m-%Y` not clear when you specify 01-07 as it can be both day and month – akrun Aug 30 '21 at 18:33
1

Thanksss akrun! – Antonio Aug 30 '21 at 18:40

score 2 · Answer 3 · answered Aug 30 '21 at 23:17

Another option without using any dates:

data[!grepl("-04-", data$date2), ]

We interprete date2 as string and look for any cell without a "-04-". This returns

   Id      date1      date2 DR01 DR02
1   1 2021-06-20 2021-07-01    4    9
2   1 2021-06-20 2021-07-01    5    5
3   1 2021-06-20 2021-07-01    6    4
4   1 2021-06-20 2021-07-01    7    3
7   1 2021-06-20 2021-06-02    7    1
12  1 2021-06-20 2021-05-03    3    3
13  1 2021-06-20 2021-06-03    4    4
18  1 2021-06-20 2021-06-09    2    2
19  1 2021-06-20 2021-05-09    6    6
20  1 2021-06-20 2021-08-10    4    4
21  1 2021-06-20 2021-06-10    3    3

Delete dates in a database in R

3 Answers3