Create new column with matched values in R

usersIAk1 0
Viewed 32

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:

Library Book
L1 M45
L1 M3
L2 M45
L34 M12
L5 M45
L23 M12
L4 M3
L11 M45

This is how it should look like.

Location Connections
L1 L2,L5, L11,L4
L2 L1,L5, L11
L5 L23
L5 L1,L2, L11
L23 L34
L4 L1
L11 L1, L2,L5

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!

dplyr dataframe r
2 Answers

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]
akrun0

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 = ","))
pluke0
Related