I have two data-frames, each with the same groups. The first data frame consists of the base data, the second an independent set of break points for each group. I want to use those break points to divide the data into bins. Is there a way to use group_by() %>% cut() or some similar sequence to perform this programatically, without manually specifying the break points?
Here is a reproducible example using iris.
data("iris")
boundaries <- iris %>%
group_by(Species) %>%
summarize(mean = mean(Sepal.Length), upper=mean(Sepal.Length+0.2), lower=mean(Sepal.Length-0.2)) %>%
data.frame()
boundaries
Species mean upper lower
1 setosa 5.006 5.206 4.806
2 versicolor 5.936 6.136 5.736
3 virginica 6.588 6.788 6.388
It’s easy to apply a single set of break points across the data set:
boundsSample <- c(-Inf, as.numeric(boundaries[1,2:4]), Inf)
binned <- iris %>%
mutate(bin = cut(Sepal.Length, breaks=boundsSample, labels = c('1', '2', '3', '4')))
What I can’t figure out is how to do this by group, without manually extracting and specifying the break points for each group. I’d like to do something like this, but am not sure how to get the group_by() to also apply to the breaks:
binned <- iris %>%
group_by(Species) %>%
mutate(bin = cut(Sepal.Length, breaks=boundaries, labels = c('1', '2', '3', '4')))
I tried to work around this by joining the boundaries data frame into the main data, so that each point would be associated with its specific boundaries, but this gives me an error that the breaks are not unique:
joined <- left_join(iris, boundaries, by="Species") %>%
mutate(bin = cut(Sepal.Length,
breaks=c(-Inf, lower, mean, upper, Inf),
labels = c('1', '2', '3', '4')))
Your ‘joined’ approach was very nearly correct. You can get around the problem by using unique() on the variables which were duplicated by the join:
joined <- left_join(iris, boundaries, by="Species") %>%
mutate(bin = cut(Sepal.Length,
breaks=c(-Inf, unique(lower), unique(mean), unique(upper), Inf),
labels = c('1', '2', '3', '4')),
.by = Species)
You could get a similar result without joining using:
binned <- iris %>%
group_by(Species) %>%
mutate(bin = cut(Sepal.Length,
breaks=c(-Inf, boundaries[boundaries$Species == unique(Species),2:4], Inf),
labels = c('1', '2', '3', '4')))
One option would be to use split to split your dataset (and the boundaries) by groups, then bin each split individually:
library(dplyr, warn = FALSE)
library(purrr)
iris2 <- iris |>
slice_head(n = 10, by = Species)
boundaries <- iris2 %>%
group_by(Species) %>%
summarize(
mean = mean(Sepal.Length),
upper = mean(Sepal.Length + 0.2),
lower = mean(Sepal.Length - 0.2)
)
boundaries <- boundaries |>
split(~Species) |>
purrr::map((x) t(x[-1])[, 1])
iris2 %>%
split(~Species) %>%
purrr::imap((x, y) {
x |>
mutate(bin = cut(Sepal.Length,
breaks = c(-Inf, boundaries[[y]], Inf),
labels = FALSE,
right = FALSE
))
}) |>
bind_rows()
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species bin
#> 1 5.1 3.5 1.4 0.2 setosa 4
#> 2 4.9 3.0 1.4 0.2 setosa 3
#> 3 4.7 3.2 1.3 0.2 setosa 2
#> 4 4.6 3.1 1.5 0.2 setosa 1
#> 5 5.0 3.6 1.4 0.2 setosa 3
#> 6 5.4 3.9 1.7 0.4 setosa 4
#> 7 4.6 3.4 1.4 0.3 setosa 1
#> 8 5.0 3.4 1.5 0.2 setosa 3
#> 9 4.4 2.9 1.4 0.2 setosa 1
#> 10 4.9 3.1 1.5 0.1 setosa 3
#> 11 7.0 3.2 4.7 1.4 versicolor 4
#> 12 6.4 3.2 4.5 1.5 versicolor 4
#> 13 6.9 3.1 4.9 1.5 versicolor 4
#> 14 5.5 2.3 4.0 1.3 versicolor 1
#> 15 6.5 2.8 4.6 1.5 versicolor 4
#> 16 5.7 2.8 4.5 1.3 versicolor 1
#> 17 6.3 3.3 4.7 1.6 versicolor 3
#> 18 4.9 2.4 3.3 1.0 versicolor 1
#> 19 6.6 2.9 4.6 1.3 versicolor 4
#> 20 5.2 2.7 3.9 1.4 versicolor 1
#> 21 6.3 3.3 6.0 2.5 virginica 1
#> 22 5.8 2.7 5.1 1.9 virginica 1
#> 23 7.1 3.0 5.9 2.1 virginica 4
#> 24 6.3 2.9 5.6 1.8 virginica 1
#> 25 6.5 3.0 5.8 2.2 virginica 2
#> 26 7.6 3.0 6.6 2.1 virginica 4
#> 27 4.9 2.5 4.5 1.7 virginica 1
#> 28 7.3 2.9 6.3 1.8 virginica 4
#> 29 6.7 2.5 5.8 1.8 virginica 3
#> 30 7.2 3.6 6.1 2.5 virginica 4