How to lump together data frame rows into "other"

user_6105259 0
Viewed 121

Given a dataframe, how can I collapse several rows together and rename them as "other"? The function forcats::fct_lump() seems relevant in essence, but it operates on data with repetition, whereas my case is different.

Example

Some reproducible toy data about states in the USA.

library(dplyr, warn.conflicts = FALSE)
library(tidyr)
library(janitor, warn.conflicts = FALSE)

set.seed(2021)

create_weights <- function(x) ceiling(exp(-x/3)*1000)

df_uncounted <- 
  state.x77 %>%
  as_tibble(rownames = "state") %>%
  mutate(freq = sample(create_weights(1:n()), size = n())) %>%
  uncount(freq)

df_counted <-
  df_uncounted %>%
  summarise(janitor::tabyl(state)) %>%
  arrange(desc(percent))

df_counted
#> # A tibble: 50 x 3
#>    state         n percent
#>    <chr>     <dbl>   <dbl>
#>  1 Oklahoma    717  0.280 
#>  2 New York    514  0.201 
#>  3 Indiana     368  0.144 
#>  4 Maine       264  0.103 
#>  5 Florida     189  0.0737
#>  6 Colorado    136  0.0531
#>  7 Alabama      97  0.0378
#>  8 Oregon       70  0.0273
#>  9 Minnesota    50  0.0195
#> 10 Tennessee    36  0.0140
#> # ... with 40 more rows

Created on 2021-08-11 by the reprex package (v2.0.0)

For the sake of this question, the dataset df_counted is given.
When I examine such data, I typically want to keep only rows that represent the larger chunks, and collapse the rest into "other". In this example, I may desire to collapse the data according to two scenarios.

Scenario A

Rows 1:4 are the ones I want to keep as-is, whereas rows 5:50 I'd collapse into "other".

Desired output for scenario A:

# A tibble: 5 x 3
  state        n percent
  <chr>    <dbl>   <dbl>
1 Oklahoma   717   0.280
2 New York   514   0.201
3 Indiana    368   0.144
4 Maine      264   0.103
5 other      700   0.273

Scenario B

Any row that has a value lower than 0.01 in the percent column should be grouped as "other".

Desired output for scenario B

   state         n percent
   <chr>     <dbl>   <dbl>
 1 Oklahoma    717  0.280 
 2 New York    514  0.201 
 3 Indiana     368  0.144 
 4 Maine       264  0.103 
 5 Florida     189  0.0737
 6 Colorado    136  0.0531
 7 Alabama      97  0.0378
 8 Oregon       70  0.0273
 9 Minnesota    50  0.0195
10 Tennessee    36  0.0140
11 Montana      26  0.0101
12 other        96  0.0375

I suppose this is a pretty common procedure, but I didn't find a direct function that does so. My attempts to achieve the desired outputs included some very cumbersome code. way too complex for such a simple purpose.

Anyone knows of a straightforward way to achieve those desired outputs? Thanks!

dplyr r
3 Answers

It's just a case of creating appropriate groups and then summarising:

library(dplyr)

df_counted %>%
  group_by(state = ifelse(row_number() < 5, state, "other")) %>%
  summarise(across(everything(), sum)) %>%
  arrange(state == "other", -n)

# A tibble: 5 x 3
  state        n percent
  <chr>    <dbl>   <dbl>
1 Oklahoma   717   0.280
2 New York   514   0.201
3 Indiana    368   0.144
4 Maine      264   0.103
5 other      700   0.273

df_counted %>%
  group_by(state = ifelse(percent >= .01, state, "other")) %>%
  summarise(across(everything(), sum)) %>%
  arrange(state == "other", -n)

# A tibble: 12 x 3
   state         n percent
   <chr>     <dbl>   <dbl>
 1 Oklahoma    717  0.280 
 2 New York    514  0.201 
 3 Indiana     368  0.144 
 4 Maine       264  0.103 
 5 Florida     189  0.0737
 6 Colorado    136  0.0531
 7 Alabama      97  0.0378
 8 Oregon       70  0.0273
 9 Minnesota    50  0.0195
10 Tennessee    36  0.0140
11 Montana      26  0.0101
12 other        96  0.0375
user_28352610

In case you have curiosity of knowing how to do it with forcats:

# scenario A
df_counted %>%
  group_by(state = fct_lump_n(state, 4, w = percent)) %>%
  summarise(across(.fn = sum)) %>%
  arrange(state == "Other", -n)

# scenario B
df_counted %>%
  group_by(state = fct_lump_min(state, .01, w = percent)) %>%
  summarise(across(.fn = sum)) %>%
  arrange(state == "Other",-n)
user_38083940

Base R options using aggregate

aggregate(
  . ~ state,
  transform(
    df_counted,
    state = replace(state, seq_along(state) >= 5, "other")
  ),
  sum
)

which gives

     state   n   percent
1  Indiana 368 0.1435817
2    Maine 264 0.1030043
3 New York 514 0.2005462
4 Oklahoma 717 0.2797503
5    other 700 0.2731174

aggregate(
  . ~ state,
  transform(
    df_counted,
    state = replace(state, percent < 0.01, "other")
  ),
  sum
)

gives

       state   n    percent
1    Alabama  97 0.03784627
2   Colorado 136 0.05306282
3    Florida 189 0.07374171
4    Indiana 368 0.14358174
5      Maine 264 0.10300429
6  Minnesota  50 0.01950839
7    Montana  26 0.01014436
8   New York 514 0.20054623
9   Oklahoma 717 0.27975029
10    Oregon  70 0.02731174
11     other  96 0.03745611
12 Tennessee  36 0.01404604
user_121587570
Related