Filtering dataframe to only show 1 pair of two variables

user_11968604 0
Viewed 56

I have information on physicians working in different hospitals at different points in time. I would like to output a dataframe with that informs each pair of physician in each hospital. I would like to see each pair only once in the dataset; meaning that if physicians A and B work together in the same hospital I would like to see either the pair A-B or the pair B-A, but not both.

Consider the very simple example of hospitals x-y-w, periods 1-2 and physicians A-B-C-D.

mydf <- data.frame(hospital = c("x","x","x","x","x","y","y","y","w","w","w","w"), 
                period = c(1,1,1,2,2,1,2,2,1,1,2,2), 
                physician = c("A","B","C","A","B","A","A","C","C","D","A","D"))

Below I manage to get all pairs, however each pair shows twice (swapping between from and to). How could get each pair only showing up once in the output?

pairs_df <- mydf %>%
  rename(from = physician) %>%
  left_join(mydf, by=c("hospital","period")) %>%
  rename(to = physician) %>%
  filter(from!=to)
dplyr filter r
2 Answers

We can use pmin/pmax with duplicated to sort the elements rowwise between the 'from', 'to' columns, apply the duplicated, negate (!) in filter to return the unique rows

library(dplyr)
pairs_df %>% 
     filter(!duplicated(cbind(pmax(from, to), pmin(from, to))))

Or use base R

subset(pairs_df, !duplicated(cbind(pmax(from, to), pmin(from, to))))

-output

     hospital period from to
1         x      1    A  B
2         x      1    A  C
4         x      1    B  C
11        w      1    C  D
13        w      2    A  D

NOTE: Here, we assume that the columns are character class based on the input data i.e. data.frame construct uses stringsAsFactors = FALSE by default (>= R 4.0.0), but previously it was TRUE by default. If the columns are factor, then we could convert to character class with type.convert

pairs_df <- type.convert(pairs_df, as.is = TRUE)

Or before the filter convert those factor to character

pairs_df %>%
   mutate(across(where(is.factor), as.character)) %>%
   filter(!duplicated(cbind(pmax(from, to), pmin(from, to))))
user_37322710

Another option is using igraph

get.data.frame(
  simplify(
    graph_from_data_frame(
      pairs_df[c("from", "to", "hospital", "period")],
      directed = FALSE
    ),
    edge.attr.comb = "first"
  )
)

which gives

  from to hospital period
1    A  B        x      1
2    A  C        x      1
3    A  D        w      2
4    B  C        x      1
5    C  D        w      1
user_121587570
Related