Home > OS >  How can I subset my list to have only the last day of the month?
How can I subset my list to have only the last day of the month?

Time:02-26

          DGS1MO DGS3MO DGS1 DGS2 DGS3 DGS5 DGS7 DGS10 DGS20 DGS30
2001-07-31   3.67   3.54 3.53 3.79 4.06 4.57 4.86  5.07  5.61  5.51
2001-08-01   3.65   3.53 3.56 3.83 4.09 4.62 4.90  5.11  5.63  5.53
2001-08-02   3.65   3.53 3.57 3.89 4.17 4.69 4.97  5.17  5.68  5.57
2001-08-03   3.63   3.52 3.57 3.91 4.22 4.72 4.99  5.20  5.70  5.59
2001-08-06   3.62   3.52 3.56 3.88 4.17 4.71 4.99  5.19  5.70  5.59
2001-08-07   3.63   3.52 3.56 3.90 4.19 4.72 5.00  5.20  5.71  5.60
2001-08-08   3.61   3.49 3.46 3.77 4.05 4.61 4.87  4.99  5.61  5.52
2001-08-09   3.61   3.45 3.48 3.77 4.07 4.66 4.93  5.04  5.64  5.54
2001-08-10   3.58   3.43 3.45 3.73 4.03 4.61 4.88  4.99  5.61  5.52
2001-08-13   3.57   3.45 3.43 3.70 4.00 4.57 4.86  4.97  5.60  5.52
2001-08-14   3.54   3.43 3.46 3.74 4.03 4.59 4.87  4.97  5.61  5.51
2001-08-15   3.52   3.43 3.47 3.80 4.11 4.62 4.90  5.00  5.62  5.52
2001-08-16   3.48   3.39 3.43 3.75 4.04 4.58 4.84  4.95  5.58  5.48
2001-08-17   3.46   3.36 3.39 3.67 3.95 4.49 4.75  4.84  5.51  5.43
2001-08-20   3.48   3.42 3.44 3.74 4.02 4.55 4.81  4.91  5.55  5.46
2001-08-21   3.46   3.39 3.41 3.69 3.96 4.50 4.79  4.87  5.54  5.44
2001-08-22   3.46   3.38 3.44 3.76 4.03 4.53 4.81  4.91  5.53  5.44
2001-08-23   3.49   3.40 3.46 3.72 3.99 4.52 4.79  4.89  5.50  5.41
2001-08-24   3.49   3.42 3.48 3.76 4.03 4.55 4.82  4.93  5.54  5.45
2001-08-27   3.52   3.45 3.51 3.78 4.04 4.57 4.83  4.94  5.56  5.47
2001-08-28   3.53   3.41 3.46 3.71 3.97 4.48 4.73  4.85  5.49  5.41
2001-08-29   3.48   3.42 3.44 3.67 3.92 4.43 4.67  4.78  5.44  5.36
2001-08-30   3.41   3.36 3.38 3.61 3.88 4.42 4.68  4.79  5.45  5.37
2001-08-31   3.40   3.37 3.41 3.64 3.91 4.46 4.72  4.85  5.47  5.39
2001-09-04   3.43   3.44 3.55 3.83 4.10 4.63 4.88  4.99  5.59  5.50
2001-09-05   3.49   3.41 3.47 3.79 4.07 4.61 4.86  4.97  5.57  5.48
2001-09-06   3.44   3.34 3.40 3.65 3.93 4.48 4.73  4.86  5.50  5.41
2001-09-07   3.40   3.27 3.29 3.53 3.82 4.39 4.67  4.80  5.45  5.39
2001-09-10   3.40   3.26 3.31 3.53 3.82 4.41 4.69  4.84  5.50  5.43
2001-09-13   2.73   2.74 2.81 2.99 3.32 4.03 4.41  4.64  5.41  5.39
2001-09-14   2.54   2.64 2.73 2.87 3.17 3.92 4.31  4.57  5.38  5.35
2001-09-17   2.47   2.59 2.72 2.96 3.30 3.99 4.38  4.63  5.44  5.41
2001-09-18   2.34   2.48 2.69 2.96 3.31 4.01 4.46  4.72  5.59  5.55
2001-09-19   2.00   2.19 2.49 2.81 3.18 3.90 4.41  4.69  5.59  5.56
2001-09-20   2.04   2.22 2.56 2.91 3.27 3.97 4.47  4.75  5.67  5.62
2001-09-21   2.12   2.25 2.53 2.91 3.27 3.94 4.43  4.70  5.62  5.59
2001-09-24   2.38   2.38 2.56 2.94 3.30 4.00 4.47  4.73  5.61  5.58
2001-09-25   2.58   2.40 2.51 2.88 3.25 3.97 4.45  4.72  5.60  5.58
2001-09-26   2.51   2.38 2.48 2.82 3.18 3.91 4.39  4.65  5.52  5.50
2001-09-27   2.34   2.38 2.43 2.78 3.15 3.87 4.33  4.58  5.46  5.45
2001-09-28   2.28   2.40 2.49 2.86 3.22 3.93 4.37  4.60  5.45  5.42
2001-10-01   2.26   2.37 2.47 2.82 3.18 3.90 4.33  4.55  5.39  5.38
2001-10-02   2.27   2.26 2.43 2.77 3.14 3.87 4.31  4.53  5.36  5.34
2001-10-03   2.21   2.23 2.38 2.77 3.14 3.86 4.29  4.50  5.34  5.32
2001-10-04   2.22   2.21 2.37 2.75 3.14 3.88 4.29  4.53  5.33  5.31
2001-10-05   2.21   2.19 2.33 2.71 3.10 3.87 4.26  4.52  5.34  5.31
2001-10-09   2.24   2.22 2.35 2.74 3.16 3.96 4.35  4.62  5.42  5.39

Above I have my dataset that I am trying to subset. My goals is to subset the df to only include the last day of each month listed. For example 8/31/2021,9/27/2021...etc onward through the data. I have been able to do specific dates but I need something that is dynamic. Thanks in advance

CodePudding user response:

With lubridate:

library(lubridate)
df$DAY <- as.Date(df$DAY)
df[df$DAY == ceiling_date(df$DAY,'month') - days(1),]

          DAY DGS1MO DGS3MO DGS1 DGS2 DGS3 DGS5 DGS7 DGS10 DGS20 DGS30
1  2001-07-31   3.67   3.54 3.53 3.79 4.06 4.57 4.86  5.07  5.61  5.51
24 2001-08-31   3.40   3.37 3.41 3.64 3.91 4.46 4.72  4.85  5.47  5.39

df:

df <- read.table(text='
DAY     DGS1MO DGS3MO DGS1 DGS2 DGS3 DGS5 DGS7 DGS10 DGS20 DGS30
2001-07-31   3.67   3.54 3.53 3.79 4.06 4.57 4.86  5.07  5.61  5.51
2001-08-01   3.65   3.53 3.56 3.83 4.09 4.62 4.90  5.11  5.63  5.53
2001-08-02   3.65   3.53 3.57 3.89 4.17 4.69 4.97  5.17  5.68  5.57
2001-08-03   3.63   3.52 3.57 3.91 4.22 4.72 4.99  5.20  5.70  5.59
2001-08-06   3.62   3.52 3.56 3.88 4.17 4.71 4.99  5.19  5.70  5.59
2001-08-07   3.63   3.52 3.56 3.90 4.19 4.72 5.00  5.20  5.71  5.60
2001-08-08   3.61   3.49 3.46 3.77 4.05 4.61 4.87  4.99  5.61  5.52
2001-08-09   3.61   3.45 3.48 3.77 4.07 4.66 4.93  5.04  5.64  5.54
2001-08-10   3.58   3.43 3.45 3.73 4.03 4.61 4.88  4.99  5.61  5.52
2001-08-13   3.57   3.45 3.43 3.70 4.00 4.57 4.86  4.97  5.60  5.52
2001-08-14   3.54   3.43 3.46 3.74 4.03 4.59 4.87  4.97  5.61  5.51
2001-08-15   3.52   3.43 3.47 3.80 4.11 4.62 4.90  5.00  5.62  5.52
2001-08-16   3.48   3.39 3.43 3.75 4.04 4.58 4.84  4.95  5.58  5.48
2001-08-17   3.46   3.36 3.39 3.67 3.95 4.49 4.75  4.84  5.51  5.43
2001-08-20   3.48   3.42 3.44 3.74 4.02 4.55 4.81  4.91  5.55  5.46
2001-08-21   3.46   3.39 3.41 3.69 3.96 4.50 4.79  4.87  5.54  5.44
2001-08-22   3.46   3.38 3.44 3.76 4.03 4.53 4.81  4.91  5.53  5.44
2001-08-23   3.49   3.40 3.46 3.72 3.99 4.52 4.79  4.89  5.50  5.41
2001-08-24   3.49   3.42 3.48 3.76 4.03 4.55 4.82  4.93  5.54  5.45
2001-08-27   3.52   3.45 3.51 3.78 4.04 4.57 4.83  4.94  5.56  5.47
2001-08-28   3.53   3.41 3.46 3.71 3.97 4.48 4.73  4.85  5.49  5.41
2001-08-29   3.48   3.42 3.44 3.67 3.92 4.43 4.67  4.78  5.44  5.36
2001-08-30   3.41   3.36 3.38 3.61 3.88 4.42 4.68  4.79  5.45  5.37
2001-08-31   3.40   3.37 3.41 3.64 3.91 4.46 4.72  4.85  5.47  5.39
2001-09-04   3.43   3.44 3.55 3.83 4.10 4.63 4.88  4.99  5.59  5.50
2001-09-05   3.49   3.41 3.47 3.79 4.07 4.61 4.86  4.97  5.57  5.48
2001-09-06   3.44   3.34 3.40 3.65 3.93 4.48 4.73  4.86  5.50  5.41
2001-09-07   3.40   3.27 3.29 3.53 3.82 4.39 4.67  4.80  5.45  5.39
2001-09-10   3.40   3.26 3.31 3.53 3.82 4.41 4.69  4.84  5.50  5.43
2001-09-13   2.73   2.74 2.81 2.99 3.32 4.03 4.41  4.64  5.41  5.39
2001-09-14   2.54   2.64 2.73 2.87 3.17 3.92 4.31  4.57  5.38  5.35
2001-09-17   2.47   2.59 2.72 2.96 3.30 3.99 4.38  4.63  5.44  5.41
2001-09-18   2.34   2.48 2.69 2.96 3.31 4.01 4.46  4.72  5.59  5.55
2001-09-19   2.00   2.19 2.49 2.81 3.18 3.90 4.41  4.69  5.59  5.56
2001-09-20   2.04   2.22 2.56 2.91 3.27 3.97 4.47  4.75  5.67  5.62
2001-09-21   2.12   2.25 2.53 2.91 3.27 3.94 4.43  4.70  5.62  5.59
2001-09-24   2.38   2.38 2.56 2.94 3.30 4.00 4.47  4.73  5.61  5.58
2001-09-25   2.58   2.40 2.51 2.88 3.25 3.97 4.45  4.72  5.60  5.58
2001-09-26   2.51   2.38 2.48 2.82 3.18 3.91 4.39  4.65  5.52  5.50
2001-09-27   2.34   2.38 2.43 2.78 3.15 3.87 4.33  4.58  5.46  5.45
2001-09-28   2.28   2.40 2.49 2.86 3.22 3.93 4.37  4.60  5.45  5.42
2001-10-01   2.26   2.37 2.47 2.82 3.18 3.90 4.33  4.55  5.39  5.38
2001-10-02   2.27   2.26 2.43 2.77 3.14 3.87 4.31  4.53  5.36  5.34
2001-10-03   2.21   2.23 2.38 2.77 3.14 3.86 4.29  4.50  5.34  5.32
2001-10-04   2.22   2.21 2.37 2.75 3.14 3.88 4.29  4.53  5.33  5.31
2001-10-05   2.21   2.19 2.33 2.71 3.10 3.87 4.26  4.52  5.34  5.31
2001-10-09   2.24   2.22 2.35 2.74 3.16 3.96 4.35  4.62  5.42  5.39',header=T)

CodePudding user response:

With base R, you can do:

is_last_of_month <- function(x) {
    trunc(x) == as.Date(round(as.POSIXlt(x), units = "months")) - 1
}

x <- seq(as.Date("2022-02-24"), as.Date("2022-03-05"), by = 1)
## [1] "2022-02-24" "2022-02-25" "2022-02-26" "2022-02-27" "2022-02-28"
## [6] "2022-03-01" "2022-03-02" "2022-03-03" "2022-03-04" "2022-03-05"

is_last_of_month(x)
## [1] FALSE FALSE FALSE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE

We use trunc(x) instead of x on the left hand side of == to defend against the possibility of fractional days:

y <- as.Date("2022-02-28")   0.5
y
## [1] "2022-02-28"

is_last_of_month(y)
## [1] TRUE

## Whereas
y == as.Date(round(as.POSIXlt(y), units = "months")) - 1
## [1] FALSE

CodePudding user response:

A possible solution:

library(tidyverse)
library(lubridate)

df %>% 
  mutate(d = day(ymd(Date)), m = month(ymd(Date)), y = year(ymd(Date))) %>% 
  group_by(m, y) %>% 
  slice_max(d) %>% 
  ungroup %>% 
  select(-d, -m, -y)

#> # A tibble: 4 × 11
#>   Date       DGS1MO DGS3MO  DGS1  DGS2  DGS3  DGS5  DGS7 DGS10 DGS20 DGS30
#>   <chr>       <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2001-07-31   3.67   3.54  3.53  3.79  4.06  4.57  4.86  5.07  5.61  5.51
#> 2 2001-08-31   3.4    3.37  3.41  3.64  3.91  4.46  4.72  4.85  5.47  5.39
#> 3 2001-09-28   2.28   2.4   2.49  2.86  3.22  3.93  4.37  4.6   5.45  5.42
#> 4 2001-10-09   2.24   2.22  2.35  2.74  3.16  3.96  4.35  4.62  5.42  5.39
  • Related