Subset data for the most recent month

user_2178850 0
Viewed 120

I have a .txt file

test.txt
V1 V2 Date
A B 2020-01-02
C D 2020-02-27
E F 2020-09-10
G H 2020-09-15

I want to subset data based on the most recent month. I did this which does the job but I want to extract the most recent month automatically rather than typing in manually and then extract the data

test$month <- factor(format(test$Date, "%B"),levels = month.name)
test.subset <- test[test$month == "September"]
r
3 Answers

We can arrange the Date class column and filter the formated value by comparing it with the last one

library(dplyr)
test %>%
   mutate(Date = as.Date(Date), Month = format(Date, '%B')) %>% 
   arrange(Date) %>%
   filter(Month == last(Month)) %>%
   select(-Month)

-output

#  V1 V2       Date
#1  E  F 2020-09-10
#2  G  H 2020-09-15

data

test <- structure(list(V1 = c("A", "C", "E", "G"), V2 = c("B", "D", "F", 
"H"), Date = c("2020-01-02", "2020-02-27", "2020-09-10", "2020-09-15"
)), class = "data.frame", row.names = c(NA, -4L))
user_37322710

Here is a base R option using subset + gsub

subset(
  transform(
    df,
    ym = gsub("\\d+$", "", Date)
  ),
  ym == max(ym),
  select = -ym
)

which gives

  V1 V2       Date
3  E  F 2020-09-10
4  G  H 2020-09-15

A data.table option

setDT(df)[
  ,
  `:=`(Year = year(as.IDate(Date)), Month = month(as.Date(Date)))
][
  .(max(Year), max(Month)),
  on = .(Year, Month)
][
  ,
  `:=`(Year = NULL, Month = NULL)
][]

gives

   V1 V2       Date
1:  E  F 2020-09-10
2:  G  H 2020-09-15

Data

> dput(df)
structure(list(V1 = c("A", "C", "E", "G"), V2 = c("B", "D", "F", 
"H"), Date = c("2020-01-02", "2020-02-27", "2020-09-10", "2020-09-15"
)), class = "data.frame", row.names = c(NA, -4L))
user_121587570

Using the structure shared by @ThomasIsCoding, and assuming the year is constant, one could just look for the row with the max month and filter for that:

# using datatable
library(data.table)
setDT(df)[month(as.IDate(Date)) == max(month(as.IDate(Date)))]

    V1 V2       Date
1:  E  F 2020-09-10
2:  G  H 2020-09-15
user_71757130
Related