Find min for each group

Question

I am trying to get the FL at the minimum start time to paste in StartTimeFL for each group. I found the min start time by using the following code:

DF$StartTime<-with(DF, ave(DateTime, AlertID, FUN=min))

An example of the dataframe I have is:

AlertID     DateTime                FL     StartTime               StartTimeFL
  FI         2017-06-07 23:00:45     300    2017-06-07 23:00:45    
  FI         2017-06-07 23:00:49     400    2017-06-07 23:00:45 
  FI         2017-06-07 23:00:53     300    2017-06-07 23:00:45 
  DJ         2017-05-07 03:00:00     500    2017-05-07 03:00:00
  DJ         2017-05-07 03:00:04     400    2017-05-07 03:00:00

The final dataframe I want will look like this:

AlertID     DateTime                FL     StartTime               StartTimeFL
  FI         2017-06-07 23:00:45     300    2017-06-07 23:00:45     300    
  FI         2017-06-07 23:00:49     400    2017-06-07 23:00:45     300
  FI         2017-06-07 23:00:53     300    2017-06-07 23:00:45     300
  DJ         2017-05-07 03:00:00     500    2017-05-07 03:00:00     500
  DJ         2017-05-07 03:00:04     400    2017-05-07 03:00:00     500

The code I am using so far (as shown below) only puts the StartTimeFL in the row where the start time is for each group.

DF$StartTimeFL<-with(DF, QNHCorrectedAlt[ifelse(DateTime==StartTime, TRUE,NA)])

Answer 1

You could do both steps using dplyr:

library(dplyr);
df %>%
    group_by(AlertID) %>%
    mutate(StartTime = min(as.POSIXct(DateTime)), StartTimeFL = FL[which.min(StartTime)])
## A tibble: 5 x 5
## Groups:   AlertID [2]
#  AlertID DateTime               FL StartTime           StartTimeFL
#  <fct>   <fct>               <int> <dttm>                    <dbl>
#1 FI      2017-06-07 23:00:45   300 2017-06-07 23:00:45         300
#2 FI      2017-06-07 23:00:49   400 2017-06-07 23:00:45         300
#3 FI      2017-06-07 23:00:53   300 2017-06-07 23:00:45         300
#4 DJ      2017-05-07 03:00:00   500 2017-05-07 03:00:00         500
#5 DJ      2017-05-07 03:00:04   400 2017-05-07 03:00:00         500

---

Sample data

df <- read.table(text = "AlertID     DateTime                FL
  FI         '2017-06-07 23:00:45'     300
  FI         '2017-06-07 23:00:49'     400
  FI         '2017-06-07 23:00:53'     300
  DJ         '2017-05-07 03:00:00'     500
  DJ         '2017-05-07 03:00:04'     400", header = T)

Answer 2

Here is a solution with data.table

library("data.table")

DF <- fread(
"AlertID     DateTime                FL
  FI         2017-06-07_23:00:45     300
  FI         2017-06-07_23:00:49     400
  FI         2017-06-07_23:00:53     300
  DJ         2017-05-07_03:00:00     500
  DJ         2017-05-07_03:00:04     400")

DF[, StartTime:=min(DateTime), AlertID]
DF[, StartFL:=FL[DateTime==StartTime], AlertID][]
# > DF[, StartFL:=FL[DateTime==StartTime], AlertID][]
#    AlertID            DateTime  FL           StartTime StartFL
# 1:      FI 2017-06-07_23:00:45 300 2017-06-07_23:00:45     300
# 2:      FI 2017-06-07_23:00:49 400 2017-06-07_23:00:45     300
# 3:      FI 2017-06-07_23:00:53 300 2017-06-07_23:00:45     300
# 4:      DJ 2017-05-07_03:00:00 500 2017-05-07_03:00:00     500
# 5:      DJ 2017-05-07_03:00:04 400 2017-05-07_03:00:00     500

For an existing dataframe DF you can do:

library("data.table")
setDT(DF)
DF[, StartTime:=min(DateTime), AlertID]
DF[, StartFL:=FL[DateTime==StartTime], AlertID]
DF[]

Answer 3

I have found an answer to my solution so thought I would share it. I used both formulas I posted in the question and then went on to create a dataframe containing one line for each AlertID with it's Start time and corresponding FL.

UniqueIDFL<-data.frame(DF)
UniqueIDFL<-UniqueIDFL[UniqueIDFL$DateTime==UniqueIDFL$StartTime,]

I then went on to merge these two data frames back together based on AlertID and Start Time.

DF<-merge(DF, UniqueIDFL, by=c("AlertID", "StartTime"),all.x=TRUE)

May not be the most elegant solution but it did the job!