extract last row for each subject from a data frame

Question

I have a data frame in R like this. I would like to extract the last visit for each subject.

SUBJID VISIT

   40161       3  
   40161       4  
   40161       5  
   40161       6  
   40161       9  
   40201       3  
   40202       6  
   40202       8  
   40241       3  
   40241       4 

The desired output is as follows

SUBJID VISIT

   40161     9  
   40201     3  
   40202     8  

How should I do this in R? Thanks very much for your help.

Answer 1

While agstudy is correct, there is another way with the stats package and the aggregate function.

df <- read.table(text="SUBJID VISIT
40161 3
40161 4
40161 5
40161 6
40161 9
40201 3
40202 6
40202 8
40241 3
40241 4", header=TRUE)


aggregate(VISIT ~ SUBJID, df, max)

  SUBJID VISIT
1  40161     9
2  40201     3
3  40202     8
4  40241     4

Answer 2

To show another alternative, because I like the simplicity of its syntax, you can use data.table too. Assuming your data.frame is called "df":

library(data.table)
# data.table 1.8.7  For help type: help("data.table")
DT <- data.table(df, key = "SUBJID")
DT[, list(VISIT = max(VISIT)), by = key(DT)]
#    SUBJID V1
# 1:  40161  9
# 2:  40201  3
# 3:  40202  8
# 4:  40241  4

And, while we are sharing the many ways to do this in R, if you're comfortable with SQL syntax, you can also use sqldf as follows:

library(sqldf)
sqldf("select SUBJID, max(VISIT) `VISIT` from df group by SUBJID")
  SUBJID VISIT
1  40161     9
2  40201     3
3  40202     8
4  40241     4

Answer 3

Because we can, another base option:

 do.call(rbind,
         lapply(split(dat, dat$SUBJID), 
                function(x) tail(x$VISIT, 1) ) )
#      [,1]
#40161    9
#40201    3
#40202    8
#40241    4

---

EDIT

As @BenBolker suggests:

 do.call(rbind,
             lapply(split(dat, dat$SUBJID), 
                    function(x) tail(x, 1) ) )

should work for all columns if you have more.