extracting matching variable in summarize()

Question

I have a example data set

gene_name	motif_id	matched_sequence
A	y1	CCC
A	y2	CCAAA
A	y3	AAG
A	y3	AT
B	y1	AAAA
B	y4	AAT
C	y5	AAGG

and trying to get dataset like in R :

gene_name	Node1	Node2	sequence	occurence
A	y1	y2	CCC, CCAAA	2
A	y1	y3	CCC,AAG,AAT	3
A	y2	y3	CCAAA,AGG,AAT	3
B	y1	y4	AAAA,AAT	2

motif_id column alway has a target and looking for common gene_name from each combination of start column without any overlaps and its list of sequence.

I have tried :

data%>% 
  group_by(gene_name, motif_id) %>% 
  summarize(matched_sequence = paste0(matched_sequence, collapse = ",")) %>% 
  mutate(count = n()) %>% filter(count>=2) %>%
  summarize(motif_id = combn(motif_id, 2, function(x) list(setNames(x, c('Node1', 'Node2')))), matched_sequence = toString(matched_sequence),
            .groups = 'keep') %>%
  tidyr::unnest_wider(motif_id) 

however failed to acquire sequence and occurence columns. Can anyone give me an advise?

Answer 1

We group by 'gene_name', keep only the groups where the number of distinct (n_distinct elements in 'motif_id' is greater than 1. get the pairwise combnations of 'unique' elements, create the 'sequence' by extracting the 'matched_sequence' that matches with the 'motif_id' values, get the lengths of the list in 'occurence', use unnest_wider to create columns from the list column, and convert the 'sequence' list to character column by pasteing the elements in the list

library(dplyr)
library(purrr)
library(tidyr)
library(stringr)
data %>%
   dplyr::group_by(gene_name) %>%  
   dplyr::filter(n() > 1, n_distinct(motif_id) > 1) %>% 
   dplyr::summarise(Node =  combn(unique(motif_id), 2, 
       simplify = FALSE),
    sequence = purrr::map(Node, ~ 
        matched_sequence[motif_id  %in% .x]),
     occurence = lengths(sequence), .groups = 'drop') %>%
   tidyr::unnest_wider(Node) %>%
   dplyr::mutate(sequence = purrr::map_chr(sequence, toString)) %>%
   dplyr::rename_with(~ stringr::str_c("Node", seq_along(.x)), starts_with("..."))

-output

# A tibble: 4 × 5
  gene_name Node1 Node2 sequence       occurence
  <chr>     <chr> <chr> <chr>              <int>
1 A         y1    y2    CCC, CCAAA             2
2 A         y1    y3    CCC, AAG, AT           3
3 A         y2    y3    CCAAA, AAG, AT         3
4 B         y1    y4    AAAA, AAT              2

data

data <- structure(list(gene_name = c("A", "A", "A", "A", "B", "B", "C"
), motif_id = c("y1", "y2", "y3", "y3", "y1", "y4", "y5"), 
matched_sequence = c("CCC", 
"CCAAA", "AAG", "AT", "AAAA", "AAT", "AAGG")), 
class = "data.frame", row.names = c(NA, 
-7L))