Is there some function to keep unique values in R dplyr with group_by?

user_997979 0
Viewed 445

I have a data.frame (or tiibble or whatever) with an id variable. Often I made some operation for this id with dplyr::group_by, so

data %>%
    group_by(id) %>%
    summarise/mutate/...()

Often, I have other non-numeric variables that are unique for each id, such as the project or country to which the id belongs and other characteristics of the id (such as gender, etc.). When I use the summarise function above, these other variables ares lost unless I specify, either

data %>%
    group_by(id) %>%
    summarise(across(c(project, country, gender, ...), unique),...)

or

data %>%
    group_by(id, project, country, gender, ...) %>%
    summarise()

Is there some functions which detect these variables which are unique for each id, so that one does not have to specify them?

Thank you!

PS: I am asking mainly on dplyr and group_by related functions, but other environments like R-base or data.table are wellcome also.

dplyr tidyverse r group-by
2 Answers

I did not test it extensively yet it should do the job

library(dplyr)

myData <- tibble(X = c(1, 1, 2, 2, 2, 3),
                 Y = LETTERS[c(1, 1, 2, 2, 2, 3)],
                 R = rnorm(6))
myData
#> # A tibble: 6 x 3
#>       X Y          R
#>   <dbl> <chr>  <dbl>
#> 1     1 A      0.463
#> 2     1 A     -0.965
#> 3     2 B     -0.403
#> 4     2 B     -0.417
#> 5     2 B     -2.28 
#> 6     3 C      0.423

group_by_id_vars <- function(.data, ...) {
  # group by the prespecified ID variables
  .data <- .data %>% group_by(...)
  
  # how many groups do these ID determine
  ID_groups <- .data %>% n_groups()
  
  # Get the number of groups if the initial grouping variables are combined
  # with other variables
  groupVars <- sapply(substitute(list(...))[-1], deparse) #specified grouping Variable
  nms <- names(.data) # all variables in .data
  res <- sapply(nms[!nms %in% groupVars], 
                function(x) {
                  .data %>%
                    # important to specify add = TRUE to combine the variable 
                    # with the IDs
                    group_by(across(all_of(x)), .add = TRUE) %>% 
                    n_groups()})
  
  # which combinations are identical, i.e. this variable does not increase the
  # number of groups in the data if combined with IDvars
  v <- names(res)[which(res == ID_groups)]
  
  # group the data accordingly
  .data <- .data %>% ungroup() %>% group_by(across(all_of(c(groupVars, v))))
  return(.data)
}

myData %>% 
  group_by_id_vars(X) %>% 
  summarise(n = n())
#> `summarise()` regrouping output by 'X' (override with `.groups` argument)
#> # A tibble: 3 x 3
#> # Groups:   X [3]
#>       X Y         n
#>   <dbl> <chr> <int>
#> 1     1 A         2
#> 2     2 B         3
#> 3     3 C         1
user_81073620

This is a bit more advanced in application, but what you are looking for are linear combinations of your grouping variables. You can convert these to factors and then use some linear algebra.

You can use findLinearCombos() from caret to locate these. It takes a bit of work to get it all organized how I think you want it though.

Something like this may do the trick. I also have not extensively tested this.

Packages

library(dplyr)
library(caret)
library(purrr)

Function

group_by_lc <- function(.data, ..., .add = FALSE, .drop = group_by_drop_default(.data)) {
  
  # capture the ... and convert to a character vector
  .groups <- rlang::ensyms(...)
  .groups_chr <- map_chr(.groups, rlang::as_name)
  
  # convert all character and factor variables to a numeric
  d <- .data %>% 
    mutate(across(where(is.factor), as.character), 
           across(where(is.character), as.factor),
           across(where(is.factor), as.integer))
  
  
  # find linear combinations of the character / factor variables
  lc <- caret::findLinearCombos(d)
  
  # see if any of your grouping variables have linear combinations
  find_group_match <- function(known_groups, lc_pair) {
    if (any(lc_pair %in% known_groups)) unique(c(lc_pair, known_groups)) else NULL
  }
  
  # convert column indices to names
  lc_pairs <- map(lc$linearCombos, ~ names(d)[.x])
  
  # iteratively look for linear combinations of known grouping variabels
  lc_cols <- reduce(lc_pairs, find_group_match, .init = .groups_chr)

  # find new grouping variables
  added_groups <- rlang::syms(lc_cols[!(lc_cols %in% .groups_chr)])

  # apply the grouping to your groups and the linear combinations
  group_by(.data, !!!.groups, !!!added_groups, .add = .add, .drop = .drop)
    
}

Usage

data <- tibble(V = LETTERS[1:10], W = letters[1:10], X = paste0(V, W), Y = rep(LETTERS[1:5], each = 2), Z = runif(10))
group_by_lc(data, W)

Result

You can see how it added in all the other grouping variables. You can rework this all in other ways, the key part is building that added_groups list to find them.

# A tibble: 10 x 5
# Groups:   W, X, V [10]
   V     W     X     Y          Z
   <chr> <chr> <chr> <chr>  <dbl>
 1 A     a     Aa    A     0.884 
 2 B     b     Bb    A     0.133 
 3 C     c     Cc    B     0.194 
 4 D     d     Dd    B     0.407 
 5 E     e     Ee    C     0.256 
 6 F     f     Ff    C     0.0976
 7 G     g     Gg    D     0.635 
 8 H     h     Hh    D     0.0542
 9 I     i     Ii    E     0.0104
10 J     j     Jj    E     0.464
user_109174790
Related