Filtering Data by Month and Day with Pasted Year

tidyverse
1

I am getting the following error when I run my data through my script:

Error in filter():
! Problem while computing ..1 = SampleDate >= as.Date(paste(year(SampleDate), 3, 15, sep = "-")).
Caused by error in charToDate():
! character string is not in a standard unambiguous format

My code previously did not have this error prior to the update to Funny Looking Kid. (Is anyone else hating this recent update?) It is a filter that selects a range of dates based on month and day and then pastes the associated year back in to create a date with month, day, and year.

S1 <- S1 %>%
mutate(
year=year(SampleDate), # extract parts
month=month(SampleDate),
day=day(SampleDate)
)

S1 <-S1 %>%
filter(SampleDate >= as.Date(paste(year(SampleDate), 03, 15, sep = "-")),
SampleDate <= as.Date(paste(year(SampleDate), 09, 15, sep = "-")))

I cannot share my data file, but all dates are in Date class and are formatted as 2013-05-07 (YMD)

I have tried resolving this using the anytime package:
S1 <-S1 %>%
filter(SampleDate >= as.Date(paste(year(anydate(SampleDate)), 03, 15, sep = "-")),
SampleDate <= as.Date(paste(year(anydate(SampleDate)), 09, 15, sep = "-")))

and by reformatting in YMD order:
S1 <-S1 %>%
filter(SampleDate >= as.Date(paste(year(SampleDate), sep = "-", 03, 15)),
SampleDate <= as.Date(paste(year(SampleDate), sep = "-", 09, 15)))

I get the same error no matter what I try. I would really appreciate any work around anyone can offer!

2

if you can share the data that would be better , but look at the below example data frame :

df <- data.frame(day=seq(as.Date('2022-01-01'), by = 'week', length.out=10),
sales=c(40, 35, 39, 44, 48, 51, 23, 29, 60, 65))
day sales
1 2022-01-01 40
2 2022-01-08 35
3 2022-01-15 39
4 2022-01-22 44
5 2022-01-29 48
6 2022-02-05 51
7 2022-02-12 23
8 2022-02-19 29
9 2022-02-26 60
10 2022-03-05 65

to filter date > 2022-01-29 :
df %>% filter(day>"2022-01-29")
day sales
1 2022-02-05 51
2 2022-02-12 23
3 2022-02-19 29
4 2022-02-26 60
5 2022-03-05 65

to have the data between two dates :

df %>% filter(day>"2022-01-22"&day<"2022-02-26")
day sales
1 2022-01-29 48
2 2022-02-05 51
3 2022-02-12 23
4 2022-02-19 29

hope this helpful :slight_smile:

Kind Regards

3

I don't think this will work since I am assessing data over several years.

Here is a sample df. I need to filter for samples taken from 15 March through 15 September for all years and have the date in the same format in the resulting data frame.

df <- data.frame(SampleDate = c(3/19/2013,
12/6/2011,
6/15/2015,
4/17/2013,
4/5/2011,
2/21/2013,
9/21/2015,
8/5/2013,
10/7/2014,
3/22/2011,
10/20/2014,
10/5/2010,
12/3/2013,
6/7/2011,
4/20/2015,
4/17/2012,
1/8/2013,
10/4/2011,
11/4/2014,
5/23/2011,
1/6/2015,
12/20/2011,
6/3/2014,
6/5/2012,
2/7/2012,
11/8/2011,
11/5/2012,
1/18/2011,
5/23/2011,
5/8/2012,
1/6/2015,
10/7/2014,
8/19/2013,
10/18/2011,
1/19/2016,
11/22/2011,
12/7/2015,
8/16/2011,
3/8/2011,
11/4/2014,
4/3/2012,
3/19/2012,
1/18/2011,
10/7/2014,
7/21/2014,
12/15/2014,
9/17/2012,
6/21/2013), Result = c(0.6,
0.4,
0.3,
0.3,
0.1,
0.3,
0.2,
0.2,
0.2,
0.1,
0.2,
0.2,
0.5,
0.1,
0.1,
0.2,
0.4,
0.1,
0.1,
0.1,
0.6,
0.4,
0.1,
0.2,
0.4,
0.3,
0.4,
0.1,
0.1,
0.1,
0.3,
0.2,
0.2,
0.1,
0.3,
0.3,
0.4,
0.1,
0.2,
0.1,
0.5,
0.5,
0.1,
0.2,
0.1,
0.3,
0.2,
0.2))

4

If you want to leave SampleDate in its original format, then it can't be a date value in R. You can make a new column that stores the date value of SampleDate and filter on that.

library(lubridate)
library(dplyr)

df <- data.frame(SampleDate = c('3/19/2013',
                                '12/6/2011',
                                '6/15/2015',
                                '4/17/2013',
                                '4/5/2011',
                                '2/21/2013',
                                '9/21/2015',
                                '8/5/2013',
                                '10/7/2014',
                                '3/22/2011',
                                '10/20/2014',
                                '10/5/2010',
                                '12/3/2013',
                                '6/7/2011',
                                '4/20/2015',
                                '4/17/2012',
                                '1/8/2013',
                                '10/4/2011',
                                '11/4/2014',
                                '5/23/2011',
                                '1/6/2015',
                                '12/20/2011',
                                '6/3/2014',
                                '6/5/2012',
                                '2/7/2012',
                                '11/8/2011',
                                '11/5/2012',
                                '1/18/2011',
                                '5/23/2011',
                                '5/8/2012',
                                '1/6/2015',
                                '10/7/2014',
                                '8/19/2013',
                                '10/18/2011',
                                '1/19/2016',
                                '11/22/2011',
                                '12/7/2015',
                                '8/16/2011',
                                '3/8/2011',
                                '11/4/2014',
                                '4/3/2012',
                                '3/19/2012',
                                '1/18/2011',
                                '10/7/2014',
                                '7/21/2014',
                                '12/15/2014',
                                '9/17/2012',
                                '6/21/2013'), 
                 Result = c(0.6,
                            0.4,
                            0.3,
                            0.3,
                            0.1,
                            0.3,
                            0.2,
                            0.2,
                            0.2,
                            0.1,
                            0.2,
                            0.2,
                            0.5,
                            0.1,
                            0.1,
                            0.2,
                            0.4,
                            0.1,
                            0.1,
                            0.1,
                            0.6,
                            0.4,
                            0.1,
                            0.2,
                            0.4,
                            0.3,
                            0.4,
                            0.1,
                            0.1,
                            0.1,
                            0.3,
                            0.2,
                            0.2,
                            0.1,
                            0.3,
                            0.3,
                            0.4,
                            0.1,
                            0.2,
                            0.1,
                            0.5,
                            0.5,
                            0.1,
                            0.2,
                            0.1,
                            0.3,
                            0.2,
                            0.2))
df <- df |> mutate(Date2=mdy(SampleDate))
df <- df %>%
  filter(Date2 >= as.Date(paste(year(Date2), 03, 15, sep = "-")),
         Date2 <= as.Date(paste(year(Date2), 09, 15, sep = "-")))
df
#>    SampleDate Result      Date2
#> 1   3/19/2013    0.6 2013-03-19
#> 2   6/15/2015    0.3 2015-06-15
#> 3   4/17/2013    0.3 2013-04-17
#> 4    4/5/2011    0.1 2011-04-05
#> 5    8/5/2013    0.2 2013-08-05
#> 6   3/22/2011    0.1 2011-03-22
#> 7    6/7/2011    0.1 2011-06-07
#> 8   4/20/2015    0.1 2015-04-20
#> 9   4/17/2012    0.2 2012-04-17
#> 10  5/23/2011    0.1 2011-05-23
#> 11   6/3/2014    0.1 2014-06-03
#> 12   6/5/2012    0.2 2012-06-05
#> 13  5/23/2011    0.1 2011-05-23
#> 14   5/8/2012    0.1 2012-05-08
#> 15  8/19/2013    0.2 2013-08-19
#> 16  8/16/2011    0.1 2011-08-16
#> 17   4/3/2012    0.5 2012-04-03
#> 18  3/19/2012    0.5 2012-03-19
#> 19  7/21/2014    0.1 2014-07-21
#> 20  6/21/2013    0.2 2013-06-21

Created on 2022-08-29 with reprex v2.0.2

5

S1 <- S1 |> mutate(Date2=mdy(SampleDate))
This line of code resulted in a column of NAs

6

I cannot be sure what the problem is without seeing exactly what you did. One possibility is that your SampleDate is a number. In the data I used, the SampleDates are enclosed in quotes. In the data you posted, the first value of SampleDate is 3/19/2013, without quotes. The / symbols will be interpreted as division operators, giving a value of 7.847651e-05 and mdy() will return NA from that.

7

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.