Find unique entries in otherwise identical rows

user_9434803 0
Viewed 456

I am currently trying to find a way to find unique column values in otherwise duplicate rows in a dataset.

My dataset has the following properties:

  • The dataset's columns comprise an identifier variable (ID) and a large number of response variables (x1 - xn).
  • Each row should represent one individual, meaning the values in the ID column should all be unique (and not repeated).
  • Some rows are duplicated, with repeated entries in the ID column and seemingly identical response item values (x1 - xn). However, the dataset is too large to get a full overview over all variables.

As demonstrated in the code below, if rows are truly identical for all variables, then the duplicate row can be removed with the dplyr::distinct() function. In my case, not all "duplicate" rows are removed by distinct(), which can only mean that not all entries are identical.

I want to find a way to identify which entries are unique in these otherwise duplicate rows.

Example:

library(dplyr)
library(janitor)

df <- data.frame(
    "ID" = rep(1:3, each = 2),
    "x1" = rep(4:6, each = 2),
    "x2" = c("a", "a", "b", "b", "c", "d"),
    "x3" = c(7, 10, 8, 8, 9, 11),
    "x4" = rep(letters[4:6], each = 2),
    "x5" = c("x", "p", "y", "y", "z", "q"),
    "x6" = rep(letters[7:9], each = 2)
)

# The dataframe with all entries
df

A data.frame: 6 × 7
ID  x1  x2  x3  x4  x5  x6
1   4   a   7   d   x   g
1   4   a   10  d   p   g
2   5   b   8   e   y   h
2   5   b   8   e   y   h
3   6   c   9   f   z   i
3   6   d   11  f   q   i


# The dataframe
df %>% 
# with duplicates removed
distinct() %>%
# filtered for columns only containing duplicates in the ID column
janitor::get_dupes(ID)

ID  dupe_count  x1  x2  x3  x4  x5  x6
1   2           4   a   7   d   x   g
1   2           4   a   10  d   p   g
3   2           6   c   9   f   z   i
3   2           6   d   11  f   q   i

In the example above I demonstrate how dplyr::distinct() will remove fully duplicate rows (ID = 2), but not rows that are different in some columns (rows where ID = 1 and 3, and columns x2, x3 and x5).

What I want is an overview over which columns that are not duplicates for each value:

df %>% 
distinct() %>%
janitor::get_dupes(ID) %>% 
# Here I want a way to find columns with unidentical entries:
find_nomatch()

ID x2 x3 x5
 1     7  x
 1    10  p
 3  c  9  z
 3  d 11  q
dplyr r
4 Answers

A data.table alternative. Coerce data frame to a data.table (setDT). Melt data to long format (melt(df, id.vars = "ID")).

Within each group defined by 'ID' and 'variable' (corresponding to the columns in the wide format) (by = .(ID, variable)), count number of unique values (uniqueN(value)) and check if it's equal to the number of rows in the subgroup (== .N). If so (if), select the entire subgroup (.SD).

Finally, reshape the data back to wide format (dcast).

library(data.table)
setDT(df)
d = melt(df, id.vars = "ID")
dcast(d[ , if(uniqueN(value) == .N) .SD, by = .(ID, variable)], ID + rowid(ID, variable) ~ variable)
#    ID ID_1   x2 x3 x5
# 1:  1    1 <NA>  7  x
# 2:  1    2 <NA> 10  p
# 3:  3    1    c  9  z
# 4:  3    2    d 11  q
user_18517120

A bit more simple than yours I think:

library(dplyr)
library(janitor)

df <- data.frame(
    "ID" = rep(1:3, each = 2),
    "x1" = rep(4:6, each = 2),
    "x2" = c("a", "a", "b", "b", "c", "d"),
    "x3" = c(7, 10, 8, 8, 9, 11),
    "x4" = rep(letters[4:6], each = 2),
    "x5" = c("x", "p", "y", "y", "z", "q"),
    "x6" = rep(letters[7:9], each = 2)
)

d <- df %>% 
  distinct() %>% 
  janitor::get_dupes(ID) 

d %>% 
  group_by(ID) %>% 
  # Check for each id which row elements are different from the of the first
  group_map(\(.x, .id) apply(.x, 1, \(.y) .x[1, ] != .y))%>% 
  do.call(what = cbind) %>% # Bind results for all ids 
  apply(1, any) %>% # return true if there are differences anywhere
  c(T, .) %>% # Keep id column 
  `[`(d, .)
#>   ID x2 x3 x5
#> 1  1  a  7  x
#> 2  1  a 10  p
#> 3  3  c  9  z
#> 4  3  d 11  q

Created on 2022-01-18 by the reprex package (v2.0.1)

Edit

d %>% 
  group_by(ID) %>% 
  # Check for each id which row elements are different from the of the first
  group_map(\(.x, .id) apply(.x, 1, \(.y) !Vectorize(identical)(unlist(.x[1, ]), .y))) %>% 
  do.call(what = cbind) %>% # Bind results for all ids 
  apply(1, any) %>% # return true if there are differences anywhere
  c(T, .) %>% # Keep id column 
  `[`(d, .)
#>   ID x2 x3 x5
#> 1  1  a  7  x
#> 2  1  a 10  p
#> 3  3  c  9  z
#> 4  3  d 11  q

Created on 2022-01-19 by the reprex package (v2.0.1)

user_119329360

I have been working on this issue for some time and I found a solution, though it tooks more step than I would've though necessary. I can only presume there's a more elegant solution out there. Anyway, this should work:

df <- df %>%
distinct() %>%
janitor::get_dupes(ID) 

# Make vector of unique values from the duplicated ID values
l <- distinct(df, ID) %>% unlist()

# Lapply on each ID
df <- lapply(
    l, 
    function(x) {
        # Filter rows for the duplicated ID
        dplyr::filter(df, ID == x) %>%
        # Transpose dataframe (converts it into a matrix) 
        t() %>%
        # Convert back to data frame
        as.data.frame() %>%
        # Filter columns that are not identical
        dplyr::filter(!if_all(everything(), ~ . == V1)) %>%
        # Transpose back
        t() %>%
        # Convert back to data frame
        as.data.frame()
    }
) %>% 
# Bind the dataframes in the list together
bind_rows() %>%
# Finally the columns are moved back in ascending order
relocate(x2, .before = x3)

#Remove row names (not necessary)
row.names(df) <- NULL

df

A data.frame: 4 × 3
x2  x3  x5
NA  7   x
NA  10  p
c   9   z
d   11  q

Feel free to comment

user_94348030

If you just want to keep the first instance of each identifier:

df <- data.frame(
    "ID" = rep(1:3, each = 2),
    "x1" = rep(4:6, each = 2),
    "x2" = rep(letters[1:3], each = 2),
    "x3" = c(7, 10, 8, 8, 9, 11),
    "x4" = rep(letters[4:6], each = 2)
)

df  %>% 
    distinct(ID, .keep_all = TRUE)

Output:

  ID x1 x2 x3 x4
1  1  4  a  7  d
2  2  5  b  8  e
3  3  6  c  9  f
user_125450410
Related