is there a way to group by two variables which interchange in R

user_12097388 0
Viewed 123

Say I have:

df<-data.frame(ID=c(102, 102, 167,143,143,148,148 ),
               ID2=c(167, 167, 102,148,148,143,143 ),
               group=c(1,1,1,2,2,2,2))


   ID ID2 group
1 102 167     1
2 102 167     1
3 167 102     1
4 143 148     2
5 143 148     2
6 148 143     2
7 148 143     2

usually I can use add_group_indices to group by ID and ID2, but in this case I want to group by the values within them (i.e. 102 always attaches to 167 and vice versa), same with 143 and 148. I want to identify these groupings by that group variable.

r
3 Answers

We can create two new variables based on pmin/pmax to get the group_indices

library(dplyr)
df %>% 
  mutate(ID_new = pmin(ID, ID2), ID2_new = pmax(ID, ID2)) %>% 
  mutate(group = group_indices(., ID_new, ID2_new)) %>%
  select(-ends_with('new'))
#    ID ID2 group
#1 102 167     1
#2 102 167     1
#3 167 102     1
#4 143 148     2
#5 143 148     2
#6 148 143     2
#7 148 143     2

In the devel version of dplyr, we can use cur_group_id after creating a group

library(stringr)
df %>%
    group_by(grp = str_c(pmin(ID, ID2), pmax(ID, ID2))) %>% 
    mutate(group = cur_group_id()) %>%
    ungroup %>% 
    select(-grp)
user_37322710

Here is an alternative dplyr/purrr solution

df %>%
    mutate(group = as.integer(factor(map2_chr(
        ID, ID2, ~paste(sort(c(.x, .y)), collapse = "_")))))
#   ID ID2 group
#1 102 167     1
#2 102 167     1
#3 167 102     1
#4 143 148     2
#5 143 148     2
#6 148 143     2
#7 148 143     2

Explanation: We use map2_chr to loop through columns ID and ID2, sort and concatenate entries to create a unique key; conversion to factor to integer then gives the group index.

user_65309700

Here is a base R solution

df$group <- as.numeric(factor(apply(df, 1, function(v) toString(sort(v)))))

such that

> df
   ID ID2 group
1 102 167     1
2 102 167     1
3 167 102     1
4 143 148     2
5 143 148     2
6 148 143     2
7 148 143     2
user_121587570
Related