How should I modify to only count the frequency of paired of value without considering the location/names of column?

user_8834178 0
Viewed 57

I want to count the frequency of paired value in two column, but I want to ignore the paired location. Such as the example below, the general aggregate or table function would reports three paired values (-0.25, 0.9), (0.9, -0.25) and (-0.77,2.9), but what I want to get is only two pairs which are (-0.25, 0.9) and (-0.77,2.9). How should I modify to only count the frequency of paired of value without considering the column location/names?

data <- data.frame(col1=c(-.25, 0.9, -.25, -.77, -.25),
                   col2=c(0.9, -.25, 0.9, 2.9, 0.9))
r
3 Answers

Update

Given data data <- data.frame(col1 = c("a", "a", "c", "c", "a", "c"), col2 = c("c", "a", "a", "c", "c", "c")), we can try

aggregate(
  freq ~ .,
  transform(
    data,
    col1 = pmin(col1, col2),
    col2 = pmax(col1, col2),
    freq = 1
  ),
  sum
)

which gives

  col1 col2 freq
1    a    a    1
2    a    c    3
3    c    c    2

Try this

> data[!duplicated(cbind(do.call(pmax, data), do.call(pmin, data))), ]
   col1 col2
1 -0.25  0.9
4 -0.77  2.9
user_121587570

One solution. First, we paste together the two columns:

paste(data$col1, data$col2)
[1] "-0.25 0.9" "0.9 -0.25" "-0.25 0.9" "-0.77 2.9" "-0.25 0.9"

Then split them into a list:

str_split(paste(data$col1, data$col2), " ")
[[1]]
[1] "-0.25" "0.9"  

[[2]]
[1] "0.9"   "-0.25"

[[3]]
[1] "-0.25" "0.9"  

[[4]]
[1] "-0.77" "2.9"  

[[5]]
[1] "-0.25" "0.9"

Create a custom function to sort and paste the values back together and sapply to the list:

count_function = function(x) {
    x = sort(x)
    paste(x, collapse=", ")
}
sapply(str_split(paste(data$col1, data$col2), " "), count_function)
[1] "-0.25, 0.9" "-0.25, 0.9" "-0.25, 0.9" "-0.77, 2.9" "-0.25, 0.9"

Then take the unique values of this vector:

> table(sapply(str_split(paste(data$col1, data$col2), " "), count_function))

-0.25, 0.9 -0.77, 2.9 
         4          1
user_57847570

Here is a tidyverse solution:

library(tidyverse)
data <- data.frame(col1=c(-.25, 0.9, -.25, -.77, -.25),
                   col2=c(0.9, -.25, 0.9, 2.9, 0.9))
data %>%
  # Create min, max (col1, col2 respectively); uses temporary column to
  # not clobber the values in col2.
  mutate(
    col2.tmp = pmax(col1, col2),
    col1 = pmin(col1, col2),
    col2 = col2.tmp) %>%
  # Remopve temporary column.
  select(-col2.tmp) %>%
  # Determine frequency of pair.
  count(col1, col2, name = "frequency") %>%
  arrange(desc(col1))

#    col1 col2 frequency
# 1 -0.25  0.9         4
# 2 -0.77  2.9         1
user_44115590
Related