How to count frequency of unique pair combinations from a column of comma-separated values?

user_8146132 0
Viewed 110

I have a dataframe with a column ("combo") of comma-separated values indicating event-types that co-occur within the same timeperiod. I am looking for a way to count the frequency of pair-wise combinations (order does not matter; i.e., AB and BA are equivalent).

toy dataframe

+------------------------+
|combo      startts endts|
| A,B       02:20  02:23 |
| A,B,D     02:23  02:25 |
| A,C       02:27  02:28 |
+------------------------+

Ideal output:


+---------------------------+
|combo      | count         |
+---------------------------+
|  AB       | 2             |
|  AC       | 1             |
|  AD       | 1             |
|  BC       | 0             |
|  BD       | 1             |
|  CD       | 0             |
+-----------+---------------+

I was thinking of first generating all unique pair-wise combinations, putting that in a column, and then doing a conditional mutate with a regex pattern match; however, I wasn't sure how to refer to the column name abstractly rather than a particular string pattern (or if that is even possible).

Thank you for your time.

combinations frequency count r
1 Answers

You can try with this

Input:

df <- read.table(text = "combo      startts endts
A,B       02:20  02:23
A,B,D     02:23  02:25
A,C       02:27  02:28", header = TRUE)

Solution:

# user defined functions
pastecollapse <- function(...) paste(..., collapse = "")
sortedcomb2collapse <- function(x) combn(sort(x), m = 2, FUN = pastecollapse)

# get combos
combos <- strsplit(df$combo, split = ",")

# all possible combos
allcombos <- sortedcomb2collapse(unique(unlist(combos)))

# existing combos
mycombos  <- unlist(lapply(combos, sortedcomb2collapse))

# count combos (show missing combos)
as.data.frame(table(combo = factor(mycombos, levels = allcombos)), responseName = "count")

#>   combo count
#> 1    AB     2
#> 2    AC     1
#> 3    AD     1
#> 4    BC     0
#> 5    BD     1
#> 6    CD     0

Similarly, with tidyverse:

library(tidyr)
library(dplyr)

df_sep <- df %>% separate_rows(combo)
allcombos <- df_sep %>% pull(combo) %>% unique %>% sortedcomb2collapse

df_sep %>% 
 group_by(startts, endts) %>% 
 summarise(combo = sortedcomb2collapse(combo), .groups = "drop") %>% 
 mutate(combo = factor(combo, levels = allcombos)) %>% 
 count(combo, name = "count", .drop = FALSE)
#> # A tibble: 6 x 2
#>   combo count
#>   <fct> <int>
#> 1 AB        2
#> 2 AC        1
#> 3 AD        1
#> 4 BC        0
#> 5 BD        1
#> 6 CD        0

Note: in your expected output one possible combination was missing (CD). Was it a mistake?

user_99182650
Related