CODEWITHSUNDEEP
rdplyrtidyr
Josh Pause
Josh Pause

Reputation: 181

How do I select all unique combinations of two columns in an R data frame?

I have a correlation matrix that I put in a dataframe like so: 

row | var1 | var2 | cor
1   | A    | B    | 0.6
2   | B    | A    | 0.6
3   | A    | C    | 0.4
4   | C    | A    | 0.4

These results are duplicated into 2 rows each, with both combinations of "var1" and "var2". I only need one, preferably with the lower variable first (e.g. rows 1 and 3).

I've been playing with dplyr for two hours and reading old threads, but not finding what I need. 

# get correlation of every concept versus every concept
data.cor <- data.jobs %>% 
  select(-y,-X) %>%
  as.matrix %>%
  cor %>%
  as.data.frame %>%
  rownames_to_column(var = 'var1') %>%
  gather(var2, value, -var1)

I would like output to look like so: 

row | var1 | var2 | cor
1   | A    | B    | 0.6
3   | A    | C    | 0.4

I am trying to do this without resorting to a loop.

Upvotes: 9

Views: 11791

Answers (5)

tmfmnk
tmfmnk

Reputation: 40121

A dplyr possibility could be:

df %>%
 group_by(grp = paste0(pmin(var1, var2), pmax(var1, var2))) %>%
 slice(1) %>%
 ungroup() %>%
 select(-grp)

    row var1  var2    cor
  <int> <chr> <chr> <dbl>
1     1 A     B       0.6
2     3 A     C       0.4

Or:

df %>%
 group_by(grp = paste0(pmin(var1, var2), pmax(var1, var2))) %>%
 filter(row_number() == min(row_number())) %>%
 ungroup() %>%
 select(-grp)

Or:

df %>%
 group_by(grp = paste0(pmin(var1, var2), pmax(var1, var2))) %>%
 summarise_all(list(first)) %>%
 ungroup() %>%
 select(-grp)

Upvotes: 4

Shree
Shree

Reputation: 11150

Here's one way with tidyverse -

dat2 <- dat %>% 
  filter(!duplicated(paste0(pmax(var1, var2), pmin(var1, var2))))


# A tibble: 2 x 3
  var1  var2    cor
  <chr> <chr> <dbl>
1 A     B     0.600
2 A     C     0.400

Data -

dat <- data_frame(
  var1 = LETTERS[c(1,2,1,3)],
  var2 = LETTERS[c(2,1,3,1)],
  cor = c(0.6,0.6,0.4,0.4))

Note: cleaned up the logic thanks to @tmfmnk

Upvotes: 14

tomasu
tomasu

Reputation: 1438

Here is yet another tidyverse partial solution, as I have dropped the row column:

library(tidyverse)

data.cor <-
  read.table(
    h = T,
    sep = "|",
    stringsAsFactors = F,
    text = "row | var1 | var2 | cor
            1   | A    | B    | 0.6
            2   | B    | A    | 0.6
            3   | A    | C    | 0.4
            4   | C    | A    | 0.4"
  ) %>%
  mutate_if(is.character, ~ trimws(.))

data.cor
#>   row var1 var2 cor
#> 1   1    A    B 0.6
#> 2   2    B    A 0.6
#> 3   3    A    C 0.4
#> 4   4    C    A 0.4

df <- data.cor %>%
  gather(var, val, var1:var2) %>%
  distinct(cor, val) %>%
  group_by(cor) %>%
  mutate(x = paste("var", 1:n(), sep = "")) %>%
  spread(x, val) %>% 
  ungroup()

df
#> # A tibble: 2 x 3
#>     cor var1  var2 
#>   <dbl> <chr> <chr>
#> 1   0.4 A     C    
#> 2   0.6 A     B

Created on 2019-04-18 by the reprex package (v0.2.1)

Upvotes: 0

Luis
Luis

Reputation: 639

A solution is to order var1 and var2 (the ordering is alphabetical) then use unique. I did this with data.table out of convenience, but it could be done with dplyr no problem.

library(data.table)

dt = data.table(var1 = c("A", "B", "A", "C"), var2 = c("B", "A", "C", "A"), cor = c(0.6 ,0.6, 0.4, 0.4))

dt[, var1_alt := min(var1, var2), by = 1:nrow(dt)]
dt[, var2_alt := max(var1, var2), by = 1:nrow(dt)]

dt = unique(dt[, .(var1 = var1_alt, var2 = var2_alt, cor)])

Upvotes: 1

www
www

Reputation: 39154

A solution using tidyverse.

library(tidyverse)

dat2 <- dat %>%
  mutate(Var = map2_chr(var1, var2, ~toString(sort(c(.x, .y))))) %>%
  distinct(Var, .keep_all = TRUE) %>%
  select(-Var)
dat2
#   row var1 var2 cor
# 1   1    A    B 0.6
# 2   3    A    C 0.4

DATA

dat <- read.table(text = "row | var1 | var2 | cor
1   | A    | B    | 0.6
2   | B    | A    | 0.6
3   | A    | C    | 0.4
4   | C    | A    | 0.4",
                  sep = "|", stringsAsFactors = FALSE, header = TRUE, strip.white = TRUE)

Upvotes: 5

Related Questions