How can I find common values in multiple dataframes?

Question

I have databases of previous addresses for individuals and I want to identify if two or more people have lived in the same state and also who those people are. Right how the ID is just in the name of the database.

A mock data set is below.

Anna <- data.frame(Name = c('124 anne st', '400 rose pl', '45 prince st'), 
                  city= c('san francisco', 'brooklyn', 'minneapolis'),
                  state= c('CA', 'NY', 'MN'))

Yusuf <- data.frame(Name = c('12 fort st', '56 melrose pl', '123 main st'), 
                  city= c('new haven', 'atlanta', 'minneapolis'),
                  state= c('CT', 'GA', 'MN'))

Robin <- data.frame(Name = c('28 greene st', '67 apple pl', '222 bellvue ave'), 
                  city= c('houston', 'new york', 'minneapolis'),
                  state= c('TX', 'NY', 'MN'))

What I would like to identify is that

1. anna and robin have lived in NY and
2. anna, robin, and yusuf have lived in MN

However when I use Reduce, I only get back "MN" and I can't identify who lived there.

statesincommon <- 
  Reduce(intersect, list (Anna[, 3], 
                         Yusuf[, 3],
                         Robin[, 3]))

Answer 1

Probably the most sane thing to do is to first create a proper dataframe which combines your databases. The first only works if Anna, Yusuf, and Robin are the only data.frame-objects in the global environment.

# Create a named list of data frames automatically
df_list <- mget(ls())

# Filter only data frames (optional, in case there are other objects)
df_list <- df_list[sapply(df_list, is.data.frame)]

# Apply the function to add a new "Source" column dynamically
df_list <- lapply(names(df_list), function(name) {
  df <- df_list[[name]]  # Extract the data frame
  df$Source <- name      # Add the source column
  df                      # Return the modified data frame
})

# Combine all into one data frame
combined_df <- do.call(rbind, df_list)

# Count occurrences of each state
state_counts <- table(combined_df$state)

# Filter rows where state appears at least twice
filtered_df <- subset(combined_df, state %in% names(state_counts[state_counts >= 2]))

You could also do it with a function:

common_states <- function(...) {
  states <- setNames(lapply(list(...), \(x) unique(x$state)), as.character(match.call())[-1])
  Filter(length, sapply(unique(unlist(states)), \(s) {
    residents <- names(states)[sapply(states, \(x) s %in% x)]
    if (length(residents) > 1) residents
  }, simplify = FALSE))
}

> common_states(Anna, Yusuf, Robin)
$NY
[1] "Anna"  "Robin"

$MN
[1] "Anna"  "Yusuf" "Robin"

Or with tidyverse:

library(dplyr)

# Group by state and filter those with multiple people
result <- bind_rows(list(Anna = Anna, Yusuf = Yusuf, Robin = Robin), .id = "Person") %>%
  distinct(Person, state) %>%
  group_by(state) %>%
  filter(n() > 1) %>%
  summarise(Individuals = toString(unique(Person)))

>result 
# A tibble: 2 × 2
  state Individuals       
  <chr> <chr>             
1 MN    Anna, Yusuf, Robin
2 NY    Anna, Robin   

Answer 2

Create a vector of the input data frame names nms and then use mget to create a named list of those data frames, bind them together into a single data frame and split the names by state. This gives a list L of states for each name. If only the names with 2 or more states are wanted use Filter as shown below to get L2. We can also display L as a bipartite graph g using igraph. The same igraph code would work with L2 in place of L to just display the subgraph corresponding to the states with 2 or more names.

library (dplyr)
library (igraph)

nms <- c("Yusuf", "Robin", "Anna")
L <- nms %>%
  mget(.GlobalEnv) %>%
  bind_rows(.id = "name") %>%
  with(split(name, state))
str(L)
## List of 6
##  $ CA: chr "Anna"
##  $ CT: chr "Yusuf"
##  $ GA: chr "Yusuf"
##  $ MN: chr [1:3] "Yusuf" "Robin" "Anna"
##  $ NY: chr [1:2] "Robin" "Anna"
##  $ TX: chr "Robin"

L2 <- Filter(function(x) length(x) > 1, L)
str(L2)
## List of 2
##  $ MN: chr [1:3] "Yusuf" "Robin" "Anna"
##  $ NY: chr [1:2] "Robin" "Anna"

s <- stack(L)
g <- graph_from_data_frame(s, directed = F)
V(g)$type <- V(g)$name %in% s[,2]
V(g)$color <- ifelse(V(g)$type, "lightblue", "lightpink")
plot(g, layout = layout_as_bipartite, vertex.label.cex = 0.7,
  edge.color = "black")