How to get the last value of each group in R

How to get the last value of each group in r, the aggregate() or group by() functions in R can be used to get the last value of each group.

Let’s have a look at how to?

Obtain the most recent value for each group – group by a single column.

Get each group’s most recent value – group by multiple columns

Let’s start by making a data frame.

df1<-data.frame(Name=c('A','B','C','D','E','F','G','H','I','J','K','L'),                State=c('S1','S1','S2','S2','S3','S3','S3','S4','S4','S4','S4','S4'),
                Sales=c(124,224,231,212,123,71,39,131,188,186,198,134))
df1
Name State Sales
1     A    S1   124
2     B    S1   224
3     C    S2   231
4     D    S2   212
5     E    S3   123
6     F    S3    71
7     G    S3    39
8     H    S4   131
9     I    S4   188
10    J    S4   186
11    K    S4   198
12    L    S4   134

Get the Last value of a group in sales or get the group’s most recent value.

Approach 1:

The aggregate function, which is categorised by state and name, is mentioned, as well as the function last, which returns the last result of each group.

aggregate(df1$Sales, by=list(df1$State), FUN=last)

The data frame will be

  Group.1   x
1      S1 224
2      S2 212
3      S3  39
4      S4 134

Approach 2:

Using the dplyr package’s group by() method

library(dplyr)
df1 %>% group_by(State) %>% summarise(Last_value = last(Sales))

The data frame will be

State Last_value
  <chr>      <dbl>
1 S1           224
2 S2           212
3 S3            39
4 S4           134

Pretty cool that it’s working well.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

3 × four =