count the frequency with little adjustment in the order within row

user_3659286 0
Viewed 17

I want to count the occurrences of instances that can have different orders in the given row.

df:
Col1
A;B;C
C;B;A
A;D
A;C;B
D;A
D;C

df_result:
Col    freq
A;B;C   3
A;D     2
D;C     1
r
3 Answers

Split the column at ;, loop over the list, sort, and paste the elements, get the frequency count with table and convert to data.frame in base R

as.data.frame(table(sapply(strsplit(df$Col1, ";"), \(x) 
      paste(sort(x), collapse = ";"))))

-output

   Var1 Freq
1 A;B;C    3
2   A;D    2
3   C;D    1

Or use separate_rows with count in tidyverse

library(dplyr)
library(tidyr)
library(stringr)
df %>%
  mutate(rn = row_number()) %>%
  separate_rows(Col1) %>% 
  group_by(rn) %>% 
  summarise(Col1 = str_c(sort(Col1), collapse = ";")) %>% 
  count(Col1, name = 'Freq')
# A tibble: 3 × 2
  Col1   Freq
  <chr> <int>
1 A;B;C     3
2 A;D       2
3 C;D       1

data

df <- structure(list(Col1 = c("A;B;C", "C;B;A", "A;D", "A;C;B", "D;A", 
"D;C")), class = "data.frame", row.names = c(NA, -6L))
user_37322710

Another possible solution:

library(tidyverse)

df %>% 
  count(Col1 = str_split(Col1, ";") %>% map_chr(~ sort(.x) %>% 
     str_c(collapse = ";")), name = "Freq")

#>    Col1 Freq
#> 1 A;B;C    3
#> 2   A;D    2
#> 3   C;D    1
user_115644870

Here is a combination with base R and dplyr: With the first base R part we reorder the strings alphabetically, the second part is to count with dplyr:

x <- df$Col1
df$Col1 <- unname(sapply(x, function(x) {
  paste(sort(trimws(strsplit(x[1], ';')[[1]])), collapse=';')} ))

library(dplyr)
df %>% 
  count(Col1)

  Col1 n
1 A;B;C 3
2   A;D 2
3   C;D 1
user_133216470
Related