如何基于邮箱或手机号关联生成客户唯一标识ID？

Hey there! Let's figure out how to assign unique IDs to customers who share either an email OR a phone number—even if they've switched one of these details over time. Your current code only groups rows where both email AND phone match, which isn't what you need, so let's fix that.

The Problem Breakdown #

You need to treat customers as the same if they share any identifier (email or phone), not both. This is a classic connected components problem: think of each email/phone as a node, and each row as an edge linking a customer's email to their phone. All nodes connected by edges belong to the same customer group.

Solution Using igraph (Clean & Efficient) #

We'll use the igraph package to map these connections and assign group IDs. Here's step-by-step code:

First, load the required packages:

library(dplyr)
library(igraph)
library(tidyr)

Define your test data (formatted properly):

test_data <- tibble(
  E.mail = c("mortena", "kaspera", "christoffera", "mortenb", "mortena", "kasperb", "christoffera"),
  Phone = c("3076", "2688", "1212", "3076", "3075", "2688", "1213"),
  Name = c("morten", "kasper", "christoffer", "morten", "morten", "kasper", "christoffer")
)

Now, build the graph and assign IDs:

# 1. Create a list of edges linking each email to its corresponding phone
edges <- test_data %>%
  select(E.mail, Phone) %>%
  rename(from = E.mail, to = Phone)

# 2. Build the graph and find connected components (each component = 1 customer)
customer_graph <- graph_from_data_frame(edges, directed = FALSE)
customer_components <- components(customer_graph)

# 3. Create a map of each identifier (email/phone) to its component ID
id_mapping <- tibble(
  identifier = names(customer_components$membership),
  ID = customer_components$membership
)

# 4. Link the IDs back to your original data
result <- test_data %>%
  # Join on email first
  left_join(id_mapping, by = c("E.mail" = "identifier")) %>%
  # Join on phone as backup (though same component, so redundant but safe)
  left_join(id_mapping, by = c("Phone" = "identifier")) %>%
  # Use either ID (they'll be the same for connected components)
  mutate(ID = coalesce(ID.x, ID.y)) %>%
  # Keep only the columns you need
  select(E.mail, Phone, Name, ID)

Check the Result #

Running this code will give you exactly the output you want:

# A tibble: 7 × 4
  E.mail       Phone Name        ID
  <chr>        <chr> <chr>    <int>
1 mortena      3076  morten       1
2 kaspera      2688  kasper       2
3 christoffera 1212  christoffer  3
4 mortenb      3076  morten       1
5 mortena      3075  morten       1
6 kasperb      2688  kasper       2
7 christoffera 1213  christoffer  3

Why This Works #

• We model each email/phone as a node, and each row as a connection between two nodes.
• Connected components (all nodes linked directly or indirectly) represent the same customer. This automatically handles cases where a customer has multiple emails, multiple phones, or switches between them.

This approach is efficient even for large datasets, and it aligns perfectly with your requirement to group by phone first (and email when phones are unique)—since the graph doesn't care which identifier is shared, it just groups all linked identifiers together.

内容的提问来源于stack exchange，提问作者Morten Andersen