Merge rows when element coma separated found in another row

user_12559770 0
Viewed 87

Hello I have a dataframe such as :

  species family    Events      groups
1     SP1      A     10,22          G1
2     SP1      B         7          G2
3     SP1    C,D 4,5,6,1,3 G3,G4,G5,G6
4     SP2      A     22,10          G1
5     SP2    D,C 6,5,4,3,1 G4,G6,G5,G3
6     SP3      C 4,5,3,6,1    G3,G6,G5
7     SP3      E         7          G2
8     SP3      A        10          G1
9     SP4      C        7,22        G12

and I would like to simply merge row for each where there is at least one duplicated element in each columns (except species).

For instance I will merge the rows :

species family    Events      groups
SP1      A        10,22       G1
species family    Events      groups
SP2      A        22,10       G1
species family    Events      groups
SP3      A        10          G1

into

species      family    Events      groups
SP1,SP2,SP3  A         10,22       G1

SO if I do the same for each row I should get an expected output as :

species      family    Events      groups
SP1,SP2,SP3  A         10,22       G1
SP1,SP3      B,E       7           G2
SP1,SP2,SP3  C,D       1,3,4,5,6   G3,G4,G6,G5
SP4          C         7,22        G12

Note that SP4 has not been merged with any rows since its group was not present in any other rows.

Does someone have an idea please ? Thank you very much for your help and time

Here is the dataframe if it can helps:

structure(list(species = structure(c(1L, 1L, 1L, 2L, 2L, 3L, 
3L, 3L, 4L), .Label = c("SP1", "SP2", "SP3", "SP4"), class = "factor"), 
    family = structure(c(1L, 2L, 4L, 1L, 5L, 3L, 6L, 1L, 3L), .Label = c("A", 
    "B", "C", "C,D", "D,C", "E"), class = "factor"), Events = structure(c(2L, 
    7L, 5L, 3L, 6L, 4L, 7L, 1L, 8L), .Label = c("10", "10,22", 
    "22,10", "4,5,3,6,1", "4,5,6,1,3", "6,5,4,3,1", "7", "7,22"
    ), class = "factor"), groups = structure(c(1L, 3L, 4L, 1L, 
    6L, 5L, 3L, 1L, 2L), .Label = c("G1", "G12", "G2", "G3,G4,G5,G6", 
    "G3,G6,G5", "G4,G6,G5,G3"), class = "factor")), class = "data.frame", row.names = c(NA, 
-9L))

What I can do and tried :

So far I only know how to merge rows with exact duplicated value using something like that in dplyr :

desired_df <- df %>%
  group_by_at(vars(-species)) %>%
  summarize(species = toString(species)) %>%
  ungroup() %>%
  select(names(df))

but here we do not have exact duplicated values, instead I'm looking for between comma that can be present in another row.

dplyr tidyverse dataframe r
1 Answers

Here is an all-tidyverse solution (calling the input data frame dat).

Please note that this solution is not identical to the desired output you gave. This is because you stated the rule is to "merge rows for which there is at least one duplicated element for each column, except species." By that rule, rows 2 and 7 should not be merged because they share no family in common.

First, convert the three columns we are going to test for overlapping values into list-columns. Now each element of those columns is a list. I also coerced the Events column to a numeric so that it will sort properly.

library(tidyverse)

dat <- dat %>%
  mutate(across(c(family, Events, groups), ~ strsplit(as.character(.), split = ','))) %>%
  mutate(Events = map(Events, as.numeric))

Next, define a function to collapse each row of the data frame. The function takes argument i which is a row index. Within the function, we do two things:

  • First we use pmap_lgl to iterate across each row of the data frame to check for which rows all the three columns family, Events, and groups have at least one shared value with row i and therefore should be collapsed. For example, if i==1 this will return TRUE for rows 1,4,and 8.
  • Next, we filter dat for only those rows that returned TRUE, and apply a function to all columns of those rows. The function collapses all columns in those rows into comma-separated strings of the sorted unique values.
collapse_rows <- function(i) {
  rows_collapse <- pmap_lgl(dat, function(family, Events, groups, ...) 
    any(dat$family[[i]] %in% family) & any(dat$Events[[i]] %in% Events) & any(dat$groups[[i]] %in% groups))
  dat %>%
    filter(rows_collapse) %>%
    mutate(across(everything(), ~ paste(sort(unique(unlist(.))), collapse = ',')))
}

Finally we apply this function to each row index. We end up with duplicated rows, for example rows 1, 4, and 8 of the initial output will be identical. We use distinct to remove all of those duplicates.

dat_collapse <- map_dfr(1:nrow(dat), collapse_rows) %>% distinct

Final output:

     species family    Events      groups
1 SP1,SP2,SP3      A     10,22          G1
2         SP1      B         7          G2
3 SP1,SP2,SP3    C,D 1,3,4,5,6 G3,G4,G5,G6
4         SP3      E         7          G2
5         SP4      C      7,22         G12
user_28546080
Related