Categorization based on value ranges in multiple columns using dplyr

user_8700651 0
Viewed 648

I need to categorize the rows of an R dataframe based on a set of category criteria given in another dataframe. The criteria define several categories baed on value ranges of several columns ("traits") in the main dataframe.

Using mtcars as an example dataframe to be categorized, here is the dataframe defining the categories:

criteria <- data.frame(category = c("high", "high", "high", "medium", "medium", "low", "low"),
                       trait    = c("mpg", "cyl", "wt", "mpg", "cyl", "mpg", "cyl"),
                       min.val  = c(20, 6, NA, 20, 4, 15, 6),
                       max.val  = c(NA, 8, 3, NA, 6, 20, 8))

This means, for example, that for a row to be categorized as "high", it needs to have mpg greater than 20, cyl between 6 and 8, and wt less than 3. The output would be identical to the original mtcars dataframe, but with an additional column named "category" that contained values of "high", "medium", "low", and NA for anything that didn't meet any the criteria of any of the categories.

The solution needs to be independent of (1) category name and (2) trait column name so a user can just supply the criteria table with custom category names and any selection of trait columns they wish.

I have a feeling that the solution might involve a complex application of dplyr::filter_at(), but can't figure out how to apply this function to multiple columns, each with a different set of criteria.

dplyr r
1 Answers

One dplyr and purrr solution could be:

criteria_up <- criteria %>%
 group_by(category) %>%
 mutate(min.val = ifelse(!is.na(min.val), paste(trait, min.val, sep = " >= "), NA_character_),
        max.val = ifelse(!is.na(max.val), paste(trait, max.val, sep = " <= "), NA_character_)) %>%
 summarise(val = paste(paste(na.omit(min.val), collapse = " & "), 
                       paste(na.omit(max.val), collapse = " & "), 
                       sep = " & "))

map2_dfr(.x = criteria_up %>%
          pull(val),
         .y = criteria_up %>%
          pull(category),
         ~ mtcars %>%
          filter(!!rlang::parse_expr(.x)) %>%
          mutate(category = !!.y)) %>%
 full_join(mtcars)

    mpg cyl  disp  hp drat    wt  qsec vs am gear carb category
1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4     high
2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4     high
3  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2      low
4  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1      low
5  19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4      low
6  17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4      low
7  16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3      low
8  17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3      low
9  15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3      low
10 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2      low
11 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2      low
12 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2      low
13 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4      low
14 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6      low
15 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8      low
16 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4   medium
17 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4   medium
18 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1   medium
19 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1   medium
20 24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2   medium
21 22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2   medium
22 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1   medium
23 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2   medium
24 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1   medium
25 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1   medium
26 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1   medium
27 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2   medium
28 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2   medium
29 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2   medium
30 14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4     <NA>
31 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4     <NA>
32 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4     <NA>
33 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4     <NA>
34 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4     <NA>
user_59645570
Related