Collapse specific rows/cases of dataframe

user_1035346 0
Viewed 277

I want to collapse some specific rows of a data.frame (preferably using dplyr in ). Collapsing should aggregate some columns by the functions sum(), others by mean().

As an example, let's add a unique character-based ID to the iris dataset.

iris_df <- iris[1:5,]
iris_df$ID <- paste("ID_",1:nrow(iris_df),sep="")

That's from where we start:

structure(list(Sepal.Length = c(5.1, 4.9, 4.7, 4.6, 5), 
               Sepal.Width = c(3.5, 3, 3.2, 3.1, 3.6),
               Petal.Length = c(1.4, 1.4, 1.3, 1.5, 1.4), 
               Petal.Width = c(0.2, 0.2, 0.2, 0.2, 0.2),
               Species = structure(c(1L, 1L, 1L, 1L, 1L),
                                   .Label = c("setosa", "versicolor", "virginica"), class = "factor"),
               ID = c("ID_1", "ID_2", "ID_3", "ID_4","ID_5")),
          row.names = c(NA, 5L), class = "data.frame")

Now, I'd like to collapse the cases where ID==ID_1 + ID==ID_2. For that purpose, the Sepal values should be aggregated as means and the Petal values as sums. The ID should become "ID_1+ID_2" (so aggregation by paste()?)

This is how the final result should look like:

structure(list(Sepal.Length = c(5.0, 4.7, 4.6, 5), 
               Sepal.Width = c(3.25, 3.2, 3.1, 3.6),
               Petal.Length = c(2.8, 1.3, 1.5, 1.4), 
               Petal.Width = c(0.4, 0.2, 0.2, 0.2),
               Species = structure(c(1L, 1L, 1L, 1L),
                                   .Label = c("setosa", "versicolor", "virginica"), class = "factor"),
               ID = c("ID_1+ID_2", "ID_3", "ID_4","ID_5")),
          row.names = c(NA, 4L), class = "data.frame")

Can this be done using dplyr (using group_by() and summarize()) package?

Update: As some additional note, the desired procedure should acknowledge that the row index are not known apriori, e.g. just that ID_x and ID_y need to be collapsed (and ID_x might be row i and ID_y at row j).

collapse dplyr r
2 Answers

We could create a grouping based on the presence of those ID using %in%

library(dplyr)
library(stringr)
df1 %>% 
   group_by(grp = case_when(ID %in% c("ID_1", "ID_2") ~ 0L, 
        TRUE ~ row_number()), Species) %>% 
   summarise(across(starts_with("Sepal"), mean), 
   across(starts_with("Petal"), sum), ID = str_c(ID, collapse="+"), 
         .groups = 'drop') %>% 
   select(-grp)

-output

# A tibble: 4 x 6
  Species Sepal.Length Sepal.Width Petal.Length Petal.Width ID       
  <fct>          <dbl>       <dbl>        <dbl>       <dbl> <chr>    
1 setosa           5          3.25          2.8         0.4 ID_1+ID_2
2 setosa           4.7        3.2           1.3         0.2 ID_3     
3 setosa           4.6        3.1           1.5         0.2 ID_4     
4 setosa           5          3.6           1.4         0.2 ID_5

if there is only a single 'Species', then we could also use first

df1 %>% 
   group_by(grp = case_when(ID %in% c("ID_1", "ID_2") ~ 0L, 
        TRUE ~ row_number())) %>%  
   summarise(across(starts_with("Sepal"), mean), 
   across(starts_with("Petal"), sum), Species = first(Species), 
        ID = str_c(ID, collapse="+"), 
         .groups = 'drop') %>% 
   select(-grp)
# A tibble: 4 x 6
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species ID       
         <dbl>       <dbl>        <dbl>       <dbl> <fct>   <chr>    
1          5          3.25          2.8         0.4 setosa  ID_1+ID_2
2          4.7        3.2           1.3         0.2 setosa  ID_3     
3          4.6        3.1           1.5         0.2 setosa  ID_4     
4          5          3.6           1.4         0.2 setosa  ID_5

Or another option is to create a new level by collapsing the IDs or interest in fct_collapse

library(forcats)
df1 %>%
   group_by(grp = fct_collapse(ID, other = c("ID_1", "ID_2"))) %>% 
   summarise(across(starts_with("Sepal"), mean), 
   across(starts_with("Petal"), sum), Species = first(Species), 
        ID = str_c(ID, collapse="+"), 
         .groups = 'drop') %>% 
   select(-grp)
# A tibble: 4 x 6
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species ID       
         <dbl>       <dbl>        <dbl>       <dbl> <fct>   <chr>    
1          5          3.25          2.8         0.4 setosa  ID_1+ID_2
2          4.7        3.2           1.3         0.2 setosa  ID_3     
3          4.6        3.1           1.5         0.2 setosa  ID_4     
4          5          3.6           1.4         0.2 setosa  ID_5
user_37322710

Here is one way how you can achieve your desired output:

library(dplyr)
df %>% 
    slice(1:2) %>% 
    mutate(across(1:4, mean),
           ID = paste(ID[1], ID[2], sep = "+"),
           across(3:4, sum)) %>% 
    slice(1) %>% 
    bind_rows(df) %>% 
    slice(c(1, 4:6))

output:

  Sepal.Length Sepal.Width Petal.Length Petal.Width Species        ID
1          5.0        3.25          2.8         0.4  setosa ID_1+ID_2
2          4.7        3.20          1.3         0.2  setosa      ID_3
3          4.6        3.10          1.5         0.2  setosa      ID_4
4          5.0        3.60          1.4         0.2  setosa      ID_5
user_133216470
Related