Re: [R] extracting the last row of each group in a data frame
I usually use the following function: last.record <- function(data, id, ..., na.last=TRUE, decreasing=FALSE){ #*** Make vector of variables to sort by v <- c(id, unlist(list(...))) #*** Sort Data Frame data <- data[do.call(order, c(data[,v, drop=FALSE], na.last=na.last, decreasing=decreasing)),] #*** Extract last record for each id data[!duplicated(data[,id], fromLast=TRUE),] } DataData Frame from which the record is to be extracted Id ID variable from which the record is to be extracted. The data frame is automatically sorted by this variable. May be either a character string or an integer. ... Names of variables (or indices) in additon to id by which data should be sorted. na.last Argument passed to order(). Determines if missing values are placed at the end of the sorting. Decreasing Argument passed to order(). Determines if data frame is sorted in descending order. So, in your example > df <- data.frame(Name = c("A", "A", "A", "B", "B", "C", "D"), Value = c(1, 2, 3, 4, 8, 2, 3)) > last.record(df, "Name", "Value") > last.record(df, 1, 2) -Original Message- From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org] On Behalf Of Hao Cen Sent: Monday, November 16, 2009 2:43 PM To: r-help@r-project.org Subject: [R] extracting the last row of each group in a data frame Hi, I would like to extract the last row of each group in a data frame. The data frame is as follows Name Value A 1 A 2 A 3 B 4 B 8 C 2 D 3 I would like to get a data frame as Name Value A 3 B 8 C 2 D 3 Thank you for your suggestions in advance Jeff __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. === P Please consider the environment before printing this e-mail Cleveland Clinic is ranked one of the top hospitals in America by U.S.News & World Report (2009). Visit us online at http://www.clevelandclinic.org for a complete listing of our services, staff and locations. Confidentiality Note: This message is intended for use\...{{dropped:13}} __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] extracting the last row of each group in a data frame
Thanks to all who helped. These are all great suggestions. Jeff -Original Message- From: Gabor Grothendieck [mailto:ggrothendi...@gmail.com] Sent: Monday, November 16, 2009 6:27 PM To: Hao Cen Cc: r-help@r-project.org Subject: Re: [R] extracting the last row of each group in a data frame Try this: > aggregate(DF[-1], DF[1], tail, 1) Name Value 1A 3 2B 8 3C 2 4D 3 On Mon, Nov 16, 2009 at 2:42 PM, Hao Cen wrote: > Hi, > > I would like to extract the last row of each group in a data frame. > > The data frame is as follows > > Name Value > A 1 > A 2 > A 3 > B 4 > B 8 > C 2 > D 3 > > I would like to get a data frame as > Name Value > A 3 > B 8 > C 2 > D 3 > > Thank you for your suggestions in advance > > Jeff > > __ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] extracting the last row of each group in a data frame
Try this: > aggregate(DF[-1], DF[1], tail, 1) Name Value 1A 3 2B 8 3C 2 4D 3 On Mon, Nov 16, 2009 at 2:42 PM, Hao Cen wrote: > Hi, > > I would like to extract the last row of each group in a data frame. > > The data frame is as follows > > Name Value > A 1 > A 2 > A 3 > B 4 > B 8 > C 2 > D 3 > > I would like to get a data frame as > Name Value > A 3 > B 8 > C 2 > D 3 > > Thank you for your suggestions in advance > > Jeff > > __ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] extracting the last row of each group in a data frame
I would use pkg:plyr, but just to show how versatile R is: ind <- cumsum(rle(as.numeric(dat$Name))$lengths) dat[ind, ] where I'm assuming that your data frame is called 'dat'. -Peter Ehlers Hao Cen wrote: Hi, I would like to extract the last row of each group in a data frame. The data frame is as follows Name Value A 1 A 2 A 3 B 4 B 8 C 2 D 3 I would like to get a data frame as Name Value A 3 B 8 C 2 D 3 Thank you for your suggestions in advance Jeff __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] extracting the last row of each group in a data frame
jeffc wrote: > > Hi, > > I would like to extract the last row of each group in a data frame. > > The data frame is as follows > > Name Value > A 1 > A 2 > A 3 > B 4 > B 8 > C 2 > D 3 > > I would like to get a data frame as > Name Value > A 3 > B 8 > C 2 > D 3 > > Thank you for your suggestions in advance > > Jeff > Try using the base function by() or ddply() from Hadley Wickham's plyr package: require( plyr ) tstData <- structure(list(Name = structure(c(1L, 1L, 1L, 2L, 2L, 3L, 4L), .Label = c("A", "B", "C", "D"), class = "factor"), Value = c(1L, 2L, 3L, 4L, 8L, 2L, 3L)), .Names = c("Name", "Value"), class = "data.frame", row.names = c(NA, -7L)) lastRows <- ddply( tstData, 'Name', function( group ){ return( data.frame( Value = tail( group[['Value']], n = 1 ) ) ) }) lastRows Name Value 1A 3 2B 8 3C 2 4D 3 Hope this helps! -Charlie - Charlie Sharpsteen Undergraduate Environmental Resources Engineering Humboldt State University -- View this message in context: http://old.nabble.com/extracting-the-last-row-of-each-group-in-a-data-frame-tp26378194p26378404.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] extracting the last row of each group in a data frame
Dear Jeff, Here is a suggestion using tapply: data.frame(last = with(x, tapply(Value, Name, function(x) x[length(x)]))) See ?tapply for more information. HTH, Jorge On Mon, Nov 16, 2009 at 2:42 PM, Hao Cen <> wrote: > Hi, > > I would like to extract the last row of each group in a data frame. > > The data frame is as follows > > Name Value > A 1 > A 2 > A 3 > B 4 > B 8 > C 2 > D 3 > > I would like to get a data frame as > Name Value > A 3 > B 8 > C 2 > D 3 > > Thank you for your suggestions in advance > > Jeff > > __ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > [[alternative HTML version deleted]] __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] extracting the last row of each group in a data frame
On Nov 16, 2009, at 2:42 PM, Hao Cen wrote: Hi, I would like to extract the last row of each group in a data frame. The data frame is as follows Name Value A 1 A 2 A 3 B 4 B 8 C 2 D 3 by(dfname$Value, dfname$Name, tail, 1) #which gets you a list Or: aggregate(dfname$Value, list(dfname$Name), tail, 1) #which returns a data.frame Group.1 x 1 A 3 2 B 8 3 C 2 4 D 3 I would like to get a data frame as Name Value A 3 B 8 C 2 D 3 -- David Winsemius, MD Heritage Laboratories West Hartford, CT __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] extracting the last row of each group in a data frame
Hi, You could try plyr, library(plyr) ddply(d,.(Name), tail,1) Name Value 1A 3 2B 8 3C 2 4D 3 HTH, baptiste 2009/11/16 Hao Cen : > Hi, > > I would like to extract the last row of each group in a data frame. > > The data frame is as follows > > Name Value > A 1 > A 2 > A 3 > B 4 > B 8 > C 2 > D 3 > > I would like to get a data frame as > Name Value > A 3 > B 8 > C 2 > D 3 > > Thank you for your suggestions in advance > > Jeff > > __ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
[R] extracting the last row of each group in a data frame
Hi, I would like to extract the last row of each group in a data frame. The data frame is as follows Name Value A 1 A 2 A 3 B 4 B 8 C 2 D 3 I would like to get a data frame as Name Value A 3 B 8 C 2 D 3 Thank you for your suggestions in advance Jeff __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.