Using cut() with group_by()

Question

I am trying to bin a continuous variable into intervals, varying the cut value based on the group of the observation. There has been a similar question asked previously, but it only dealt with a single column, while I was wanting to find a solution which could be generalised to work with he group_by() function in dplyr, which allows multiple columns to be selected for the grouping.

Here is a basic example dataset:

df <- data.frame(group = c(rep("Group 1", 10),
                           rep("Group 2", 10)),
                 subgroup = c(1,2),
                 value = 1:20)

creates:

     group subgroup value
1  Group 1        1     1
2  Group 1        2     2
3  Group 1        1     3
4  Group 1        2     4
5  Group 1        1     5
6  Group 1        2     6
7  Group 1        1     7
8  Group 1        2     8
9  Group 1        1     9
10 Group 1        2    10
11 Group 2        1    11
12 Group 2        2    12
13 Group 2        1    13
14 Group 2        2    14
15 Group 2        1    15
16 Group 2        2    16
17 Group 2        1    17
18 Group 2        2    18
19 Group 2        1    19
20 Group 2        2    20

For the purpose of this question, lets assume that we want to split the groups into a value of 1 or 2, depending on whether the value is above or below the mean value of the group. The grouping should be done by group and subgroup, with an expected output of:

     group subgroup value cut
1  Group 1        1     1   1
2  Group 1        2     2   1
3  Group 1        1     3   1
4  Group 1        2     4   1
5  Group 1        1     5   1
6  Group 1        2     6   2
7  Group 1        1     7   2
8  Group 1        2     8   2
9  Group 1        1     9   2
10 Group 1        2    10   2
11 Group 2        1    11   1
12 Group 2        2    12   1
13 Group 2        1    13   1
14 Group 2        2    14   1
15 Group 2        1    15   1
16 Group 2        2    16   2
17 Group 2        1    17   2
18 Group 2        2    18   2
19 Group 2        1    19   2
20 Group 2        2    20   2

I was hoping for an output along the lines of:

df %>%
  group_by(group, subgroup) %>%
  # INSERT MAGIC FUNCTION TO BIN DATA

something like `mutate(bin = cut(value, breaks = c(-Inf, mean(value), Inf), labels = c(1,2)))` if you want to use `cut` — talat, Apr 13 '18 at 10:13
@docendodiscimus that feels so obvious now you write it out, silly me. If you put this as an answer I will mark as accepted: using cut keeps it flexible if there need to be more breaks values assigned. — Michael Harper, Apr 13 '18 at 10:16

score 6 · Accepted Answer · answered Apr 13 '18 at 10:19

6

If you want to use cut, you could do it this way:

df %>% 
  group_by(group, subgroup) %>% 
  mutate(bin = cut(value, breaks = c(-Inf, mean(value), Inf), labels = c(1,2)))

answered Apr 13 '18 at 10:19

talat

68,970
21
126
157

If I do this but select breaks = 3, is there a way of finding out the intervals that the function chose? – Camila Vargas Restrepo Jul 24 '18 at 17:21
@CamilaVargasRestrepo, if you leave out the `labels = ...` in the function call, you'll get labes that tell you the actual intervals. – talat Jul 25 '18 at 07:35

score 5 · Answer 2 · answered Apr 13 '18 at 10:12

For such a case you don't necessarily need cut. Using:

df %>%
  group_by(group, subgroup) %>%
  mutate(cut_grp = (value > mean(value)) + 1)

gives:

# A tibble: 20 x 4
# Groups:   group, subgroup [4]
   group   subgroup value cut_grp
   <fct>      <dbl> <int>   <dbl>
 1 Group 1       1.     1      1.
 2 Group 1       2.     2      1.
 3 Group 1       1.     3      1.
 4 Group 1       2.     4      1.
 5 Group 1       1.     5      1.
 6 Group 1       2.     6      1.
 7 Group 1       1.     7      2.
 8 Group 1       2.     8      2.
 9 Group 1       1.     9      2.
10 Group 1       2.    10      2.
11 Group 2       1.    11      1.
12 Group 2       2.    12      1.
13 Group 2       1.    13      1.
14 Group 2       2.    14      1.
15 Group 2       1.    15      1.
16 Group 2       2.    16      1.
17 Group 2       1.    17      2.
18 Group 2       2.    18      2.
19 Group 2       1.    19      2.
20 Group 2       2.    20      2.

Using cut() with group_by()

2 Answers2

Linked

Related