I want to add a new column in my dataframe that lists all rows of a column that have common values, but I am having troubles with it.

This is what my dataframe looks like:

This is how it should look like.

The new Connections column should contain all the locations over common books (which are the locations that have the same books, for each location). So, since L1 has in storage the same book as L2,L5, L11 and L4, they should be added in the new column.

Thanks!

CodePudding user response：

In base R, we could do

m1 <- crossprod(table(df1[2:1]))
diag(m1) <- 0
stack(apply(m1, 1, \(x) toString(names(x)[x > 0])))[2:1]

CodePudding user response：

in the tidyverse we can do the following:

# load data as given above:
library(tidyverse)
books <- structure(list(Library = c("L1", "L1", "L2", "L34", "L5", "L23", 
                                    "L4", "L11"), 
                        Book = c("M45", "M3", "M45", "M12", "M45", "M12", 
                                 "M3", "M45")), 
                   row.names = c(NA, -8L), class = "data.frame")

connects <- books %>%
  group_by(Book) %>%
  summarise(Connections = paste(Library, collapse = ","))

left_join(books, connects, by="Book") %>% 
  group_by(Library) %>%
  mutate(Connections = 
               str_replace(Connections,
                           paste0(Library,",?|,?",Library, "$"), "")) %>%
  summarise(Connections = paste(Connections, collapse = ","))