How to find unique rows based on max date value

Question

I have the following dataset:

df

email_id           date
[email protected]   23-12-2018 21:33
[email protected]   23-12-2018 21:34
[email protected]   23-12-2018 21:35
[email protected]   23-12-2018 21:36
[email protected]   23-12-2018 21:37
[email protected]   23-12-2018 21:09
[email protected]   23-12-2018 21:10
[email protected]   23-12-2018 21:11
[email protected]   23-12-2018 21:12
[email protected]   23-12-2018 21:13
[email protected] 23-12-2018 21:44
[email protected] 23-12-2018 21:45
[email protected] 23-12-2018 21:46
[email protected] 23-12-2018 21:47

I am trying to find unique emails having the latest time stamp. The output is something as shown below:

email_id    date
[email protected]   23-12-2018 21:37
[email protected]   23-12-2018 21:13
[email protected] 23-12-2018 21:47

Can this be done using dplyr or should i trying some sql group by query? Help needed.

`df %>% group_by(email_id) %>% slice(which.max(date))` that is — Axeman, Aug 29 '18 at 13:40
Try `sqldf("select email_id,max(date) from df group by email_id")` — Saurabh Chauhan, Aug 29 '18 at 13:46

score 1 · Accepted Answer · answered Aug 29 '18 at 13:42

Using data.table:

DT[, date := as.POSIXct(date, "%d-%m-%Y %H:%M", tz = "")]
DT[, .SD[which.max(date)], email_id]
          email_id                date
1:   [email protected] 2018-12-23 21:37:00
2:   [email protected] 2018-12-23 21:13:00
3: [email protected] 2018-12-23 21:47:00

Where:

DT <- fread("email_id,           date
[email protected],   23-12-2018 21:33
[email protected],   23-12-2018 21:34
[email protected],   23-12-2018 21:35
[email protected],   23-12-2018 21:36
[email protected],   23-12-2018 21:37
[email protected],   23-12-2018 21:09
[email protected],   23-12-2018 21:10
[email protected],   23-12-2018 21:11
[email protected],   23-12-2018 21:12
[email protected],   23-12-2018 21:13
[email protected], 23-12-2018 21:44
[email protected], 23-12-2018 21:45
[email protected], 23-12-2018 21:46
[email protected], 23-12-2018 21:47")

How to find unique rows based on max date value

1 Answers1