Group column by regular interval

Question

I'm struggling to solve a seemingly simple task. In this type of data:

df <- data.frame(
  A = c(1,2,3,4,1,2,3,2,2,2,2,1,1,1,1,6,7)
)

I want to add an id variable that groups column A by a regular interval, say, of 5. An additional difficulty is that the number of rows is 17, thus not a multiple of 5:

df <- data.frame(
  A = c(1,2,3,4,1,2,3,2,2,2,2,1,1,1,1,6,7),
  id = c("a","a","a","a","a", 
         "b","b","b","b","b",
         "c","c","c","c","c",
         "d","d"))

How can this be done?

You say you're struggling with it—what have you tried? – camille Jan 14 '22 at 20:08 — camille, Jan 14 '22 at 20:08

score 3 · Answer 1 · answered Jan 14 '22 at 16:54

base R

df$id <- rep(letters, each = 5, length = nrow(df))

dplyr

library(dplyr)
df %>% 
  mutate(id = rep(letters, each = 5, length = n()))

output

score 2 · Answer 2 · answered Jan 14 '22 at 16:56

2

df <- data.frame(A = c(1, 2, 3, 4, 1, 2, 3, 2, 2, 2, 2, 1, 1, 1, 1, 6, 7))

library(dplyr)

df %>% 
  mutate(grp = (row_number() - 1) %/% 5)
#>    A grp
#> 1  1   0
#> 2  2   0
#> 3  3   0
#> 4  4   0
#> 5  1   0
#> 6  2   1
#> 7  3   1
#> 8  2   1
#> 9  2   1
#> 10 2   1
#> 11 2   2
#> 12 1   2
#> 13 1   2
#> 14 1   2
#> 15 1   2
#> 16 6   3
#> 17 7   3

^{Created on 2022-01-14 by the reprex package (v2.0.1)}

or

df %>% 
  mutate(grp = letters[(row_number() - 1) %/% 5 + 1])

   A grp
1  1   a
2  2   a
3  3   a
4  4   a
5  1   a
6  2   b
7  3   b
8  2   b
9  2   b
10 2   b
11 2   c
12 1   c
13 1   c
14 1   c
15 1   c
16 6   d
17 7   d

answered Jan 14 '22 at 16:56

Yuriy Saraykin

8,390
1
7
14

Thanks a lot. What's the meaning of `%/%`? – Chris Ruehlemann Jan 14 '22 at 17:00
`%/%` --> the remainder of an integer division, @ChrisRuehlemann! For instance, `3 %% 2` is equal to 1. – PaulS Jan 14 '22 at 17:04
`%/%` returns the integer part of the division. This leads to the desired sequence of numbers, a multiple of 5 for `c(0:16)` – Yuriy Saraykin Jan 14 '22 at 17:13

AndrewGB · Answer 3 · 2022-01-14T17:55:32.713

A data.table solution:

library(data.table)

setDT(df)[, grp := rep(letters, each = 5, length = .N)]

Output

    A grp
 1: 1   a
 2: 2   a
 3: 3   a
 4: 4   a
 5: 1   a
 6: 2   b
 7: 3   b
 8: 2   b
 9: 2   b
10: 2   b
11: 2   c
12: 1   c
13: 1   c
14: 1   c
15: 1   c
16: 6   d
17: 7   d

Benchmark

It looks like base R is the quickest for doing this:

bm <- microbenchmark::microbenchmark(
  andrew.data.table = setDT(df)[, grp := rep(letters, each = 5, length = .N)],
  yuriy = df %>%
    mutate(grp = letters[(row_number() - 1) %/% 5 + 1]),
  paul = df %>%
    mutate(id = rep(letters, each=5)[1:n()]),
  Maël_baseR = df$id <- rep(letters, each = 5, length = nrow(df)),
  Maël_dplyr = df %>%
    mutate(id = rep(letters, each = 5, length = n())),
  times=1000L
)

autoplot(bm)

score 1 · Accepted Answer · answered Jan 14 '22 at 16:55

A possible solution:

library(dplyr)

df <- data.frame(
  A = c(1,2,3,4,1,2,3,2,2,2,2,1,1,1,1,6,7)
)

df %>% 
  mutate(id = rep(letters, each=5)[1:n()])

#>    A id
#> 1  1  a
#> 2  2  a
#> 3  3  a
#> 4  4  a
#> 5  1  a
#> 6  2  b
#> 7  3  b
#> 8  2  b
#> 9  2  b
#> 10 2  b
#> 11 2  c
#> 12 1  c
#> 13 1  c
#> 14 1  c
#> 15 1  c
#> 16 6  d
#> 17 7  d

Group column by regular interval

4 Answers4

base R

dplyr