Group rows by ID in R-CodePudding

I have the following data frame comprising of the person ID, his closest relationships, and the weight of each relationship.

dd <- data.frame(
  ID= c(101,101,101,102,102,103),
  Relationship= c("Spouse","Friend","Neighbour", "Friend", "Friend", "Spouse"),
  Weight = c(5,1,3,5,3,5)
 )

I wish to rearrange such that (i) each row has a unique ID, and (ii) a Relationship column that specifies all the relationships this ID has, separated by - and descendingly ordered by its weight, such as the below:

desired <- data.frame(
  ID= c(101,102,103),                                                  #unique
  Relationship= c("Spouse-Neighbour-Friend","Friend-Friend","Spouse"), #by order of weight (descending)
 )

CodePudding user response：

dd <- data.frame(
  ID= c(101,101,101,102,102,103),
  Relationship= c("Spouse","Friend","Neighbour", "Friend", "Friend", "Spouse"),
  Weight = c(5,1,3,5,3,5)
)

dd %>% 
  group_by(ID) %>% 
  summarise(Relationship = paste0(Relationship, collapse = "-"),
            Weight = sum(Weight)) %>% 
  arrange(desc(Weight)) %>% 
  select(-Weight) -> desired

desired  
# A tibble: 3 x 2
#      ID Relationship           
#   <dbl> <chr>                  
# 1   101 Spouse-Friend-Neighbour
# 2   102 Friend-Friend          
# 3   103 Spouse

CodePudding user response：

With data.table:

setDT(dd)
setorder(dd, 'Weight', order = -1)
dd[, paste(Relationship, collapse = "-"), by = ID]
#       ID                      V1
#    <num>                  <char>
# 1:   101 Friend-Neighbour-Spouse
# 2:   102           Friend-Friend
# 3:   103                  Spouse