Find the minimum date between two maximum dates based off unique values in a column

user_16336328 0
Viewed 426

Data example.

date1 = seq(as.Date("2019/01/01"), by = "month", length.out = 29)
date2= seq(as.Date("2019/05/01"), by = "month", length.out = 29)

subproducts1=rep("1",29)
subproducts2=rep("2",29)

b1 <- c(rnorm(29,5))
b2 <- c(rnorm(29,5))

dfone <- data.frame("date"= c(date1,date2),
                "subproduct"= 
                  c(subproducts1,subproducts2),
                "actuals"= c(b1,b2))

Max Date for Subproduct 1 is May 2021 and max date for Subproduct 2 is Sept 2021.

Question: Is there a way to:

  1. Find the max date for both unique subproduct and
  2. Find the minimum date from the two max dates all in one step?

The final result should be May 2021 in this case and able to handle multiple subproducts.

dplyr data-manipulation dataframe r
6 Answers

We may use slice_max after grouping by 'subproduct', pull the date and get the min, assign it to a new object

library(dplyr)
dfone %>%
    group_by(subproduct) %>%
    slice_max(n = 1, order_by = date) %>%
    ungroup %>%
    pull(date) %>%
    min -> Min_date

-output

 Min_date
[1] "2021-05-01"

Another option is to arrange the rows and filter using duplicated

dfone %>%
    arrange(subproduct, desc(date)) %>% 
    filter(!duplicated(subproduct)) %>% 
    pull(date) %>%
    min
user_37322710

For your first goal, you can try subset + ave like below

out1 <- subset(
    dfone,
    ave(date, subproduct, FUN = max) == date
)

which gives

         date subproduct  actuals
29 2021-05-01          1 5.728420
58 2021-09-01          2 3.455491

For your second goal, based on out1, you can try

out2 <- subset(
    out1,
    date == min(date)
)

which gives

         date subproduct  actuals
29 2021-05-01          1 5.083229
user_121587570

This could also be done in base R. In the end I used Reduce so that the solution can be generalized to any number of subproducts and dates and not just 2 values as is the case here.

Reduce(function(x, y) min(x, y),
       lapply(unique(dfone$subproduct), \(x){
         max(dfone$date[dfone$subproduct == x])
       }))

[1] "2021-05-01"
user_143145200

For the sake of completeness, here are also data.table and sqldf solutions:

1. data.table

library(data.table)
setDT(dfone)[, max(date), by = subproduct][, min(V1)]

[1] "2021-05-01"

2. sqldf

sqldf::sqldf("
select min(date) from (
  select max(date) as date from dfone group by subproduct
)", method = "Date")

   min(date)
1 2021-05-01
user_38170040

Another attempt - sort the dfone data by date descending, find the first instance of each subproduct, and take the minimum:

with(dfone[order(dfone$date, decreasing=TRUE),], 
     min(date[match(unique(subproduct), subproduct)]))
#[1] "2021-05-01"
user_4968030

Though the question has been marked as solved, yet one more hack where you can use {} anonymous call

library(dplyr)

dfone %>% group_by(subproduct) %>%
  summarise(d = max(date), .groups = 'drop') %>%
  {min(.$d)}

#> [1] "2021-05-01"

Created on 2021-07-16 by the reprex package (v2.0.0)

user_28848590
Related