How to keep unique rows in a data frame using `dplyr::distinct()` while specifying precision on numerical variables?

user_5851905 0
Viewed 108

Here's an example of keeping unique rows based on the Sepal.Length column only using dplyr::distinct(). This removes already many rows, from 150 rows in the original iris data set to 35 rows after the application of dplyr::distinct().

Instead of having dplyr::distinct() making an exact comparison, I would like to provide a level of numerical precision on the deviations, let us say 10%.

For example, the first two observations shown below for the Sepal.Length variable, show 5.1 and 4.9, respectively. That means a difference of 0.2. If we make that relative to the highest value, i.e. 5.1, then we have a relative difference of 0.2 / 5.1 = 0.03921569 (3.9%).

Hence, according to the criterion of 10%, these two observations should be considered equal, as 3.9% < 10 %.

So, is there a simple and idiomatic way of doing this? I mean without resorting to explicitly having me calculating distances between all observations for each variable of interest and then applying filters...?

Am I underestimating the problem, and this really requires, perhaps, clustering of the values to define "equal" groups, and is therefore a difficult problem that goes beyond the scope of dplyr, and distinct() particularly?

If I am answering my own question with these suppositions, I think it might still be useful to leave it here posted for others that might be approaching this question as naively as me.

library(dplyr, warn.conflicts = FALSE)
distinct(iris, Sepal.Length, .keep_all = TRUE)
#>    Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
#> 1           5.1         3.5          1.4         0.2     setosa
#> 2           4.9         3.0          1.4         0.2     setosa
#> 3           4.7         3.2          1.3         0.2     setosa
#> 4           4.6         3.1          1.5         0.2     setosa
#> 5           5.0         3.6          1.4         0.2     setosa
#> 6           5.4         3.9          1.7         0.4     setosa
#> 7           4.4         2.9          1.4         0.2     setosa
#> 8           4.8         3.4          1.6         0.2     setosa
#> 9           4.3         3.0          1.1         0.1     setosa
#> 10          5.8         4.0          1.2         0.2     setosa
#> 11          5.7         4.4          1.5         0.4     setosa
#> 12          5.2         3.5          1.5         0.2     setosa
#> 13          5.5         4.2          1.4         0.2     setosa
#> 14          4.5         2.3          1.3         0.3     setosa
#> 15          5.3         3.7          1.5         0.2     setosa
#> 16          7.0         3.2          4.7         1.4 versicolor
#> 17          6.4         3.2          4.5         1.5 versicolor
#> 18          6.9         3.1          4.9         1.5 versicolor
#> 19          6.5         2.8          4.6         1.5 versicolor
#> 20          6.3         3.3          4.7         1.6 versicolor
#> 21          6.6         2.9          4.6         1.3 versicolor
#> 22          5.9         3.0          4.2         1.5 versicolor
#> 23          6.0         2.2          4.0         1.0 versicolor
#> 24          6.1         2.9          4.7         1.4 versicolor
#> 25          5.6         2.9          3.6         1.3 versicolor
#> 26          6.7         3.1          4.4         1.4 versicolor
#> 27          6.2         2.2          4.5         1.5 versicolor
#> 28          6.8         2.8          4.8         1.4 versicolor
#> 29          7.1         3.0          5.9         2.1  virginica
#> 30          7.6         3.0          6.6         2.1  virginica
#> 31          7.3         2.9          6.3         1.8  virginica
#> 32          7.2         3.6          6.1         2.5  virginica
#> 33          7.7         3.8          6.7         2.2  virginica
#> 34          7.4         2.8          6.1         1.9  virginica
#> 35          7.9         3.8          6.4         2.0  virginica
dplyr r
1 Answers

In terms of identifying relative distance from column max and using this as some sort of grouping you could use the following approach for one column. The important part is to round the calculation result to i.e. 10% steps mean one digit after the decimal separator:

iris %>%
    dplyr::transmute(Sepal.Length = round((max(Sepal.Length) - Sepal.Length) / max(Sepal.Length), 1)) %>%
    dplyr::distinct()

and if you like to use it for all numerical columns a generalization of the above solution looks like this

iris %>%
    dplyr::mutate(across(where(is.numeric), ~ round((max(.x) - .x) / max(.x), 1))) %>%
    dplyr::distinct()

if you want to add new columns with the max distance of eacht column, this can be an option:

iris  %>%
    mutate(across(where(is.numeric), 
                       .fns = list(dist_max = ~round((max(.x) - .x) / max(.x), 1)),
                       .names = "{fn}_{col}" ))

Note sure if any of these answers you question?

user_135199610
Related