Creating a "different" field

Question

Right now I have the following data.frame file that was created by original.df %.% group_by(Category) %.% tally() %.% arrange(desc(n)) .

 DF <- structure(list(Category = c("E", "K", "M", "L", "I", "A", "S", "G", "N", "Q"), n = c(163051, 127133, 106680, 64868, 49701, 47387, 47096, 45601, 40056, 36882)), .Names = c("Category", "n"), row.names = c(NA, 10L), class = c("tbl_df", "tbl", "data.frame" )) Category n 1 E 163051 2 K 127133 3 M 106680 4 L 64868 5 I 49701 6 A 47387 7 S 47096 8 G 45601 9 N 40056 10 Q 36882 

I want to create a "Other" field from the lower rank categories by n. i.e.

  Category n 1 E 163051 2 K 127133 3 M 106680 4 L 64868 5 I 49701 6 Other 217022 

I'm doing now

 rbind(filter(DF, rank(rev(n)) <= 5), summarise(filter(DF, rank(rev(n)) > 5), Category = "Other", n = sum(n))) 

which collapses all categories that are not in the top five in the Other category.

But I'm curious if there is a better way in dplyr or some other existing package. By "better" I mean shorter / more readable. I'm also interested in methods with clever or more flexible ways of choosing Other .

Answer 1

Different package versions / different syntaxes:

 library(data.table) dt = as.data.table(DF) dt[order(-n), # your data is already sorted, so this does nothing for it if (.BY[[1]]) .SD else list("Other", sum(n)), by = 1:nrow(dt) <= 5][, !"nrow", with = F] # Category n #1: E 163051 #2: K 127133 #3: M 106680 #4: L 64868 #5: I 49701 #6: Other 217022 

Answer 2

This is a different approach, assuming that each category (at least at least 5) occurs only once:

 df %.% arrange(desc(n)) %.% #you could skip this step since you arranged the input df already according to your question mutate(Category = ifelse(1:n() > 5, "Other", Category)) %.% group_by(Category) %.% summarize(n = sum(n)) # Category n #1 E 163051 #2 I 49701 #3 K 127133 #4 L 64868 #5 M 106680 #6 Other 217022 

Edit:

I just noticed that my output is not ordered by decreasing n . After running the code, I found out that the order is preserved until there is group_by(Category) , but when I run summarize , the order will go away (or rather, it is ordered by Category ). Should it be like that?

Here are three more ways:

 m <- 5 #number of top results to show in final table (excl. "Other") n <- m+1 #preserves the order (or better: reesatblishes it by index) df <- arrange(df, desc(n)) %.% #this could be skipped if data already ordered mutate(idx = 1:n(), Category = ifelse(idx > m, "Other", Category)) %.% group_by(Category) %.% summarize(n = sum(n), idx = first(idx)) %.% arrange(idx) %.% select(-idx) #doesnt preserve the order (same result as in first dplyr solution, ordered by Category) df[order(df$n, decreasing=T),] #this could be skipped if data already ordered df[n:nrow(df),1] <- "Other" df <- aggregate(n ~ Category, data = df, FUN = "sum") #preserves the order (without extra index) df[order(df$n, decreasing=T),] #this could be skipped if data already ordered df[n:nrow(df),1] <- "Other" df[n,2] <- sum(df$n[df$Category == "Other"]) df <- df[1:n,] 

Answer 3

This function changes the column, replacing the odd entries with Other , either by specifying the minimum frequency, or by indicating the resulting number of categories.

 #' @title Group infrequent entries into 'Other category' #' @description Useful when you want to constrain the number of unique values in a column. #' @param .data Data containing variable. #' @param var Variable containing infrequent entries, to be collapsed into "Other". #' @param n Threshold for total number of categories above "Other". #' @param count Threshold for total count of observations before "Other". #' @param by Extra variables to group by when calculating \code{n} or \code{count}. #' @param copy Should \code{.data} be copied? Currently only \code{TRUE} is supported. #' @param other.category Value that infrequent entries are to be collapsed into. Defaults to \code{"Other"}. #' @return \code{.data} but with \code{var} changed to be grouped into smaller categories. #' @export mutate_other <- function(.data, var, n = 5, count, by = NULL, copy = TRUE, other.category = "Other"){ stopifnot(is.data.table(.data), is.character(other.category), identical(length(other.category), 1L)) had.key <- haskey(.data) if (!isTRUE(copy)){ stop("copy must be TRUE") } out <- copy(.data) if (had.key){ orig_key <- key(out) } else { orig_key <- "_order" out[, "_order" := 1:.N] setkeyv(out, "_order") } if (is.character(.data[[var]])){ stopifnot(!("nvar" %in% names(.data)), var %in% names(.data)) N <- .rank <- NULL n_by_var <- out %>% .[, .N, keyby = c(var, by)] %>% .[, .rank := rank(-N)] out <- merge(out, n_by_var, by = c(var, by)) if (missing(count)){ out[, (var) := dplyr::if_else(.rank <= n, out[[var]], other.category)] } else { out[, (var) := dplyr::if_else(N >= count, out[[var]], other.category)] } out <- out %>% .[, N := NULL] %>% .[, .rank := NULL] setkeyv(out, orig_key) if (!had.key){ out[, (orig_key) := NULL] setkey(out, NULL) } out } else { warning("Attempted to use by = on a non-character vector. Aborting.") return(.data) } } 

https://github.com/HughParsonage/hutils/blob/master/R/mutate_other.R