group_by n unique sequential values of a variable

user_8305581 0
Viewed 119

It's easy to group_by unique values of a variable:

library(tidyverse)
library(gapminder)
gapminder %>%
  group_by(year)

If we wanted to make a group ID just to show us what the groups would be:

gapminder %>% 
  select(year) %>% 
  distinct %>%
  mutate(group = group_indices(., year))
A tibble: 12 x 2
    year group
   <int> <int>
 1  1952     1
 2  1957     2
 3  1962     3
 4  1967     4
 5  1972     5
 6  1977     6
 7  1982     7
 8  1987     8
 9  1992     9
10  1997    10
11  2002    11
12  2007    12

But what if I want to group by pairs ("group2"), triplets ("group3"), etc. of sequential years? How could I produce the following tibble using dplyr/tidyverse?

A tibble: 12 x 2
    year group group2 group3 group5
   <int> <int>  <int>  <int>  <int>
 1  1952     1      1      1      1
 2  1957     2      1      1      1
 3  1962     3      2      1      1
 4  1967     4      2      2      1
 5  1972     5      3      2      1
 6  1977     6      3      2      2
 7  1982     7      4      3      2
 8  1987     8      4      3      2
 9  1992     9      5      3      2
10  1997    10      5      4      2
11  2002    11      6      4      3
12  2007    12      6      4      3
dplyr r
3 Answers

With ceiling() you can create groups very easily.

gapminder %>% 
    select(year) %>% 
    distinct() %>%
    mutate(group1 = group_indices(., year)) %>%
    mutate(group2=ceiling(group1 / 2)) %>%
    mutate(group3=ceiling(group1 / 3)) %>%
    mutate(group4=ceiling(group1 / 4)) %>%
    mutate(group5=ceiling(group1 / 5))

# A tibble: 12 x 6
    year group1 group2 group3 group4 group5
   <int>  <int>  <dbl>  <dbl>  <dbl>  <dbl>
 1  1952      1      1      1      1      1
 2  1957      2      1      1      1      1
 3  1962      3      2      1      1      1
 4  1967      4      2      2      1      1
 5  1972      5      3      2      2      1
 6  1977      6      3      2      2      2
 7  1982      7      4      3      2      2
 8  1987      8      4      3      2      2
 9  1992      9      5      3      3      2
10  1997     10      5      4      3      2
11  2002     11      6      4      3      3
12  2007     12      6      4      3      3
user_68363770

Here's an alternative solution, where you can specify the number of groups you want in the beginning and the process creates the corresponding groups:

library(tidyverse)
library(gapminder)

# input number of groups
nn = 5

gapminder %>% 
  select(year) %>% 
  distinct() %>%
  mutate(X = seq_along(year),
         d = map(X, ~data.frame(t(ceiling(.x/2:nn))))) %>%
  unnest() %>%
  setNames(c("year", paste0("group",1:nn)))

# # A tibble: 12 x 6
#    year group1 group2 group3 group4 group5
#    <int>  <int>  <dbl>  <dbl>  <dbl>  <dbl>
# 1  1952      1      1      1      1      1
# 2  1957      2      1      1      1      1
# 3  1962      3      2      1      1      1
# 4  1967      4      2      2      1      1
# 5  1972      5      3      2      2      1
# 6  1977      6      3      2      2      2
# 7  1982      7      4      3      2      2
# 8  1987      8      4      3      2      2
# 9  1992      9      5      3      3      2
#10  1997     10      5      4      3      2
#11  2002     11      6      4      3      3
#12  2007     12      6      4      3      3
user_51934850

Here's a function that does the job

group_by_n = function(x, n) {
    ux <- match(x, sort(unique(x)))
    ceiling(ux / n)
}

It does not require that x be ordered, or that values be evenly spaced or even numeric values. Use as, e.g.,

mutate(gapminder, group3 = group_by_n(year, 3))
user_5473310
Related