bestBy: Find the "best" record within subgroups of a dataframe.

In caroline: A Collection of Database, Data Structure, Visualization, and Utility Functions for R

Find the "best" record within subgroups of a dataframe.

Description

Finding the an extreme record for each group within a dataset is a more challenging routine task in R and SQL. This function provides a easy interface to that functionality either using R (fast for small data frames) or SQL (fastest for large data)

Usage

bestBy(df, by, best, top=1, clmns=names(df), rebind=TRUE, inverse=FALSE, sql=FALSE)

Arguments

df	a data frame.
by	the factor (or name of a factor in df) used to determine the grouping.
best	the column to sort on (both globally and for each sub/group)
top	the top N from each grouping, as determined by "by".
clmns	the colums to include in the output.
rebind	whether or not to 'rebind' the grouped results back into the same data.frame (not available in sql=T)
inverse	the sorting order of the sort column as specified by 'best' (ie, converts 'best' to 'worst')
sql	whether or not to use SQLite to perform the operation.

Value

A data frame of 'best' records from each factor level

Author(s)

David Schruth

See Also

groupBy

Examples

blast.results <- data.frame(score=c(1,2,34,4,5,3,23), 
                            query=c('z','x','y','z','x','y','z'), 
                            target=c('a','b','c','d','e','f','g')
                            )
best.hits.R <- bestBy(blast.results, by='query', best='score', inverse=TRUE)
best.hits.R

top3.hits.4ea <- bestBy(blast.results, by='query', best='score', inverse=TRUE, top=3, rebind=FALSE)
top3.hits.4ea

## or using SQLite
best.hits.sql <- bestBy(blast.results, by='query', best='score', inverse=TRUE, sql=TRUE)
best.hits.sql

caroline documentation built on July 6, 2026, 9:07 a.m.