Creating a new data frame based on the range of numeric values of a specific column in R

Question

I am working with a dataset that looks like this...

group  col_2  col_3   col_4
A       TT     12      21
A       RR     11      21
A       LL     13      22
A       QQ     11      24
A       PP     14      25
A       RR     15      26
A       TT     17      28
A       LL     16      29
B       DD     12      23
B       QQ     14      23
B       PP     13      25 
B       HH     11      25
B       LL     15      26
B       DD     17      28
B       QQ     14      29
B       HH     13      30
C       MM     18      21
C       JJ     15      22
C       LL     17      23
C       NN     14      24
C       EE     19      25
C       KK     15      28
C       NN     17      28
C       UU     10      29 
D       II     14      21
D       OO     15      23
D       PP     16      24 
D       LL     17      25 
D       MM     18      26
D       AA     10      28
D       HH     12      29
D       JJ     13      30

So, I need to create a new data frame grouping by the group column and using the values of col_4.

As you can see range of values in col_4 goes from 21-30. I need to select three values for each group. One value is supposed to be on the range from 21-22, the second value is supposed to be on the range from 25-26 and the third values needs to be a value on the range from 29-30. If there are two possibilities I need to randomly select one values and if there are no possibilities I need the output to be NA. For example, you can see that group A has three possible outputs for the first range with values in col_4 of 21, 21, 22. Then I need to select just one, randomly. You can also see that group B doe not has any values in column_4 between 21-22, so I need the output to be NA.

The second important issue is that I need to have only three rows per group. So I want my data (the output) to look like this.

group  col_2  col_3   col_4  range_2122 group col_2 col_3 col_4 range_2526 group col_2 col_3 col_4 range2930
A       TT     12      21     21          A     RR    15    26    26         A    LL   16      29      29
B       NA     NA      NA     NA          B     HH    11    25    25         B    HH   13      30      30
C etc.
D etc.

A second option is to get an output like this...

group  col_2  col_3   col_4  range
 A       TT     12      21     21
 A       RR     15      26     26
 A       LL     16      29     29
 B       NA     NA      NA     NA
 B       HH     11      25     25
 B       HH     13      30     30
 C  etc..

Your resulting names are not unique, something R and dplyr don't really like. — r2evans, Sep 20 '22 at 13:34

score 1 · Answer 1 · answered Sep 20 '22 at 13:42

The below comes fairly close to what you want using dplyr and tidyr. As has been mentioned, the example output you gave has non-unique names which are not a good idea.

Hopefully you can use this as a starting point.

library(dplyr)

df %>%
  dplyr::mutate(grouping_col = case_when(
    col_4 %in% 21:22 ~ "range_2122",
    col_4 %in% 25:26 ~ "range_2526",
    col_4 %in% 29:30 ~ "range_2930",
    TRUE ~ NA_character_
  )) %>%
  # Get all combinations of group and range (ensures NAs where missing)
  right_join(tidyr::expand(., group, grouping_col), by = c("grouping_col", "group")) %>%
  filter(!is.na(grouping_col)) %>%
  # Group back and randomly select a row
  group_by(group, grouping_col) %>%
  slice_sample(n = 1)

r2evans · Answer 2 · 2022-10-06T14:54:14.607

Here's a dplyr suggestion:

library(dplyr)
L <- list(c(21, 22), c(25, 26), c(29, 30))
set.seed(42)
lapply(L, function(z) {
  nm <- paste0("range_", paste(z, collapse = ""))
  out <- group_by(dat, group) %>%
    filter(between(col_4, z[1], z[2])) %>%
    slice(sample(n(), 1)) %>%
    mutate({{nm}} := sample(col_4, 1))
}) %>%
  Reduce(function(a, b) suppressWarnings(
    merge(a, b, by = "group", all = TRUE, check.names = FALSE, suffixes = c("", ""))
  ), .)
#   group col_2 col_3 col_4 range_2122 col_2 col_3 col_4 range_2526 col_2 col_3 col_4 range_2930
# 1     A    TT    12    21         10    RR    15    26         24    LL    16    29         18
# 2     B  <NA>    NA    NA         NA    PP    13    25          7    HH    13    30         15
# 3     C    MM    18    21          4    EE    19    25          4    UU    10    29          3
# 4     D    II    14    21         18    LL    17    25         25    JJ    13    30          9

We have to go away from pure-tidyverse to violate same-name conventions.

Does this work, Ajrhjnd? If not, you won't get it fixed without specific feedback. — r2evans, Oct 06 '22 at 14:31

Creating a new data frame based on the range of numeric values of a specific column in R

2 Answers2