Summarize with the latest record for each group

user_9329813 0
Viewed 93

I have a dataframe:

df <- data.frame(Xdate = c("21-jul-2020", "29-jul-2020", "20-jul-2020", "13-may-2020" ),
                 names = c("peter", "lisa","peter", "lisa"),
                 score = c(1,3,5,7))

What is the most elegant way of getting the latest score out:

df_result <- data.frame(names = c("peter", "lisa"),
                        score = c(1, 3))

The latest score for peter is 1 and were achieved the 21-jul-2020 and the latest score by lisa is 3 and is achieved the 29-jul-2020.

dplyr dataframe filter r
4 Answers

You can use slice_max() in dplyr, which supersedes top_n() after version 1.0.0, to select the most recent date.

library(dplyr)

df %>%
  mutate(Xdate = as.Date(Xdate, "%d-%b-%Y")) %>%
  group_by(names) %>%
  slice_max(Xdate, n = 1) %>%
  ungroup()

# # A tibble: 2 x 3
#   Xdate      names score
#   <date>     <chr> <dbl>
# 1 2020-07-29 lisa      3
# 2 2020-07-21 peter     1
user_100689850

Here is a dplyr solution.

library(dplyr)

df %>%
  mutate(Xdate = as.Date(df$Xdate, "%d-%b-%Y")) %>%
  group_by(names) %>%
  arrange(Xdate) %>%
  summarise_all(last)
## A tibble: 2 x 3
#  names Xdate      score
#  <chr> <date>     <dbl>
#1 lisa  2020-07-29     3
#2 peter 2020-07-21     1

A base R one-liner could be

aggregate(score ~ names, data = df[order(df$Xdate),], function(x) x[length(x)])
#  names score
#1  lisa     3
#2 peter     1
user_82454060

Here is one alternative from dplyr package

library(dplyr)
    df$Xdate <- as.Date(df$Xdate, format = "%d-%b-%Y")
    df %>% 
        group_by(names) %>% 
        arrange(desc(Xdate)) %>% 
        mutate(names = first(names),
               score = first(score)) %>% 
        select(!Xdate) %>% 
        distinct(names, score)%>%
        ungroup()

# names score
# <fct> <dbl>
#1 lisa      3
#2 peter     1

or

df %>% group_by(names) %>% arrange(desc(Xdate)) %>% filter(row_number() == 1)

or

df %>% group_by(names) %>% arrange(desc(Xdate)) %>% top_n(n = -1)
user_133305970

Using ave in base R :

subset(transform(df, Xdate = as.Date(Xdate, "%d-%b-%Y")), 
                     Xdate == ave(Xdate, names, FUN = max))

#       Xdate names score
#1 2020-07-21 peter     1
#2 2020-07-29  lisa     3

With transform we first convert Xdate to date, using ave we get max date for each names and subset those values.

user_39629140
Related