Creating a unique ID variable as combination of variables

Question

I have a data frame (df) or data table (dt) with, let’s say 1000 variables and 1000 observations. I checked that there are no duplicates in the observations, so dt[!duplicated(dt)] has the same length as the original file.

I would like to create an ID variable for all this observation with a combination of some of the 1000 variables I have. Differently to other SO questions as I don’t know which variables are more suitable to create the ID and it is likely that I need a combination of, at least, 3 or 4 variables.

Is there any package/function in R that could get me the most efficient combination of variables to create an ID variable? In my real example I am struggling to create an ID manually, and probably it is not the best combination of variables.

Example with mtcars:

require(data.table)
example <- data.table(mtcars)
rownames(example) <- NULL # Delete mtcars row names
example <- example[!duplicated(example),]
example[,id_var_wrong := paste0(mpg,"_",cyl)]
length(unique(example$id_var_wrong)) # Wrong ID, there are only 27 different values for this variable despite 32 observations

example[,id_var_good := paste0(wt,"_",qsec)]
length(unique(example$id_var_good)) # Good ID as there are equal number of unique values as different observations.

Is there any function to find wt and qsec automatically and not manually?

user_3554291 · Answer

Based on @F. Privé's answer; You can optionally specify a 'startVar' if you have an idea of what might be best, otherwise to start it will just pick the var with the max number of distinct values.

library(dplyr)
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

columnsID <- function(dataset,
                      startVar = NULL,
                      frac = 1) {
  #Set up some temporary dataframes
  #Remove any total duplicates with distinct
  #Take a sample if not working on the full data
  tibb <- as_tibble(dataset) %>%
    distinct() %>%
    sample_frac(frac)

  #Set up the vars which will be used
  if (is.null(startVar)) {
    startVar <- names(which.max(lapply(tibb, n_distinct)))[[1]]
  }
  vars_agg <- c(startVar)
  vars_all <- names(tibb)

  #Filter out any rows which are already uniquely identified
  tibb <- tibb %>% group_by_at(vars(vars_agg)) %>%
    filter(n() > 1)

  while (nrow(tibb) > 0) {
    #Keep track of the vars we haven't used yet
    vars_unused <- setdiff(vars_all, vars_agg)

    #Find the variable which has the most distinct number of values on average
    #for the grouping we have so far
    var_best <-
      tibb %>%
      group_by(!!!syms(vars_agg)) %>%
      mutate_at(vars(vars_unused), funs(n_distinct(.))) %>%
      ungroup() %>%
      summarise_at(vars(vars_unused), funs(mean)) %>%
      which.max() %>%
      names()

    #Add the 'best variable' to the list
    vars_agg <- c(vars_agg, var_best)

    #Filter out any rows which are now uniquely identified
    tibb <- tibb %>%
      group_by_at(vars(vars_agg)) %>%
      filter(n() > 1)
  }
  vars_agg
}

columnsID(mtcars)
#> [1] "qsec" "mpg"

^{Created on 2019-04-02 by the reprex package (v0.2.1)}

Creating a unique ID variable as combination of variables

3 Answers