In a dataframe with a date column in POSIXct format, what is the date associated to a specific number in another column?

user_15030195 0
Viewed 81

This question follow-up of a previous question: how to find the maximum value of a column dataframe in a specific POSIXct time interval in r?

In my dataframe CORtrial I have two columns, rDate in POSixct format which goes from 2015-07-27 17:45:00 until 2017-08-31 16:55:00 and REFN630 in numeric format. A value of REFN630 is recorded in rDate in a time interval of 5 in 5 minutes.

That's the structure of my dataframe:

dput(head(CORtrial,10))
structure(list(rDate = structure(c(1438019100, 1438019400, 1438019700, 
1438020000, 1438020300, 1438020600, 1438020900, 1438021200, 1438021500, 
1438021800), class = c("POSIXct", "POSIXt"), tzone = ""), REFN630 = c(0.0111940298507463, 
0.0671936758893281, 0.0143198090692124, 0.0087719298245614, 0.00936768149882904, 
0.00985221674876847, 0.00775193798449612, 0.00815217391304348, 
0.00859598853868195, 0.00911854103343465)), row.names = c(NA, 
-10L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x0000024f693c1ef0>)

I got the maximum of REFN630 between the rDate 2015-10-27 22:25:00 and 2015-07-27 22:50:00 using this code:

df %>%
  filter(ymd_hms(rDate) %within% 
           interval(ymd_hms("2015-07-27 22:25:00"), ymd_hms("2015-07-27 22:50:00"))) %>%
  summarise(Maximum = max(REFN630, na.rm = TRUE)) 

   Maximum
1 0.01431981

Now I want to know exactly what rDate is associated with the maximum value extracted from the code above.

Thank you in advance.

posixct dataframe r
1 Answers

Instead of summarise, can use slice_max to get the row with the max 'REFN630'

library(dplyr)
df %>%
  filter(ymd_hms(rDate) %within% 
           interval(ymd_hms("2015-07-27 22:25:00"), 
        ymd_hms("2015-07-27 22:50:00")))  %>% 
  slice_max(order_by = REFN630, n = 1)

Or another option is which.max to get the index of max and use that to subset the 'rDate'

df %>%
  filter(ymd_hms(rDate) %within% 
           interval(ymd_hms("2015-07-27 22:25:00"), 
        ymd_hms("2015-07-27 22:50:00"))) %>%
  summarise(rDate = rDate[which.max(REFN630)])
user_37322710
Related