CODEWITHSUNDEEP
rdplyr
stackinator
stackinator

Reputation: 5819

dplyr collapse 'tail' rows into larger groups

library(tidyverse)
df <- tibble(a = as.factor(1:20), b = c(50, 20, 13, rep(2, 10), rep(1, 7)))

How do I make dplyr look at this data frame df and collapse all these occurences of 2 into a single summed group, and collapse all the occurrences of 1 into a single summed group? And also keep the rest of the data frame.

Turn this:

# A tibble: 20 x 2
   a         b
   <fct> <dbl>
 1 1        50
 2 2        20
 3 3        13
 4 4         2
 5 5         2
 6 6         2
 7 7         2
 8 8         2
 9 9         2
10 10        2
11 11        2
12 12        2
13 13        2
14 14        1
15 15        1
16 16        1
17 17        1
18 18        1
19 19        1
20 20        1

into this:

# A tibble: 5 x 2
   a         b
   <fct> <dbl>
 1 1        50
 2 2        20
 3 3        13
 4 grp2     20
 5 grp1      7

[Edit] - I fixed the example data. Sorry about that.

Upvotes: 0

Views: 92

Answers (3)

G. Grothendieck
G. Grothendieck

Reputation: 269905

We group by a manufactured sortkey to maintain sort order. We used the fact that b is in descending order in the input but if that is not the case in your actual data then replace sortkey = -b with the more general sortkey = data.table::rleid(b) or the longer sortkey = cumsum(coalesce(b != lag(b), FALSE)) .

We also convert b to the group names giving a new a. It wasn't clear which groups are to be converted to grp... form. Hard-coded 1 and 2? Any group with more than one row? Groups at the end with more than one row? At any rate it would be easy enough to change the condition in the if_else once that were clarified.

Finally perform the summation and then remove the sortkey.

df %>% 
  group_by(sortkey = -b, a = paste0(if_else(b %in% 1:2, "grp", ""), b)) %>%
  summarize(b = sum(b)) %>%
  ungroup %>%
  select(-sortkey)

giving:

# A tibble: 5 x 2
  a         b
  <chr> <int>
1 50       50
2 20       20
3 13       13
4 grp2     20
5 grp1      7

Upvotes: 2

arg0naut91
arg0naut91

Reputation: 14764

This is an approach which gives you the desired names for groups & where you don't need to think in advance how many cases like that you would need (e.g. it would create grp3, grp4, ... depending on the number in b).

library(dplyr)

df %>%
  mutate(
    grp = as.numeric(lag(df$b) != df$b),
    grp = cumsum(ifelse(is.na(grp), 0, grp))
  ) %>% group_by(grp) %>%
  mutate(
    a = ifelse(n() > 1, paste0("grp", b), a),
    b = sum(b)
  ) %>% ungroup() %>% distinct(a, b)

Output:

  a         b
  <chr> <dbl>
1 1        50
2 2        20
3 3        13
4 grp2     20
5 grp1      7

Note that the code could be also condensed but that leads to a certain lack of readability in my opinion:

df %>%
  group_by(grp = cumsum(ifelse(is.na(as.numeric(lag(df$b) != df$b)), 0, as.numeric(lag(df$b) != df$b)))) %>%
  mutate(
    a = ifelse(n() > 1, paste0("grp", b), a),
    b = sum(b)
  ) %>% ungroup() %>% distinct(a, b)

Upvotes: 1

Shree
Shree

Reputation: 11150

Here's a way. I have converted a from factor to character to make things easier. You can convert it back to factor if you want. Also your test data was a bit wrong.

df <- tibble(a = as.character(1:20), b = c(50, 20, 13, rep(2, 10), rep(1, 7)))

df %>% 
  mutate(
    a = case_when(
      b == 1 ~ "grp1",
      b == 2 ~ "grp2",
      TRUE ~ a
    )
  ) %>% 
  group_by(a) %>% 
  summarise(b = sum(b))

# A tibble: 5 x 2
  a         b
  <chr> <dbl>
1 1        50
2 2        20
3 3        13
4 grp1      7
5 grp2     20

Upvotes: 1

Related Questions