Extract last value from vector in data frame

Question

I have one data set.This data set contain two columns first is column with ID and second is with VALUE.You can see code and table below :

DATA_TEST <- data.frame(
  ID = c("03740270423222","03740270423222","03740270423222","03740270423222","01380926325248","01380926325248","01380926325248"),
  VALUE = c("100","200","300","200","300","200","300"))

But here in table there are a lot of duplicate, so my intention is only to extract last value separably by each ID so final result should be like in table below:

So can anybody help me how to resolve this problem ?

Darren Tsai · Accepted Answer · 2020-08-16T11:00:19.277

2

A base R solution with aggregate() and tail()

aggregate(VALUE~ ID, DATA_TEST, tail, 1)

#               ID VALUE
# 1 01380926325248   300
# 2 03740270423222   200

or by dplyr package.

library(dplyr)

option 1: summarise() + last()

DATA_TEST %>%
  group_by(ID) %>%
  summarise(VALUE = last(VALUE))

option 2: slice_tail() <=> slice(n())

DATA_TEST %>%
  group_by(ID) %>%
  slice_tail()

edited Aug 16 '20 at 11:00

answered Aug 16 '20 at 10:35

Darren Tsai

32,117
5
21
51

score 1 · Answer 2 · answered Aug 16 '20 at 10:42

In data.table:

DATA_TEST<-data.frame(
  ID=c("03740270423222","03740270423222","03740270423222","03740270423222","01380926325248","01380926325248","01380926325248"),
  VALUE=c("100","200","300","200","300","200","300")
)

library(data.table)

DT <- as.data.table(DATA_TEST)

DT[, .(VALUE = last(VALUE)), by = ID]

               ID VALUE
1: 03740270423222   200
2: 01380926325248   300

Extract last value from vector in data frame

2 Answers2