Lisette Lisette -4 years ago 101
R Question

find only the group members in r

I'm stuck with defining group members to an individual. I was working in excel but that is failing since the number of individuals in a group varies between groups. I used this formula

=IFERROR(INDEX($A$1:$A$10727;SMALL(IF($S$1:$S$10727=$S2;ROW($S$1:$S$10727);"");Nth);1);"NA")


This returns the Nth individual in a group. This is not working since gives me all the individuals and I only want the group member, so not the individuals itself. So I was thinking to go to R, but I don't know where to start.

My data looks like this:

group ID
1 1
1 2
1 3
2 4
2 5
3 6
3 7
3 8
3 9
3 10


I would like this:

group ID gm1 gm2 gm3 gm4
1 1 2 3 NA NA
1 2 1 3 NA NA
1 3 1 2 NA NA
2 4 5 NA NA NA
2 5 4 NA NA NA
3 6 7 8 9 10
3 7 6 8 9 10
3 8 6 7 9 10
3 9 6 7 8 10
3 10 6 7 8 9


Is there a formula in R that gives me the group members?

Answer Source

Using dplyr and tidyr you could solve this in the following way. First we define a function that solves the problem for a single group, then we simply apply this function to all the groups using do.

library(dplyr)
df <- data.frame(group = rep(1:3, c(3, 2, 5)), ID = 1:10)

add_group_members <- function(df) {
   df_copy <- df 
   colnames(df_copy)[2] <- "gm_id"
   inner_join(df, df_copy, by = c("group" = "group")) %>% 
   filter(ID != gm_id) %>% 
   group_by(ID) %>% 
   mutate(gm = paste("gm", row_number(), sep = '')) %>% 
   tidyr::spread(key = gm, value = gm_id) %>% ungroup
}

df %>% group_by(group) %>% do(add_group_members(.)) %>% ungroup
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download