R语言数据处理：列值匹配替换与跨表手机号关联取数

Got it, let's tackle these two R data manipulation tasks step by step. I'll start with the specific example you provided, then cover the general case of replacing column values after matching another dataset.

1. Add Age Column to Table_1 by Matching Phone Numbers #

First, let's recreate your sample data in R so we can test the solutions directly:

# Create sample data frames as per your input
Table_1 <- data.frame(
  PhNo = c(987677632, 342343255, 986444656, 875445555),
  Name = c("Rajeev", "Simon", "Jack", "Rahul"),
  stringsAsFactors = FALSE
)

Table_2 <- data.frame(
  `Ph No` = c(986444656, 875445555, 987677632, 342343255),
  Age = c(24, 26, 23, 22),
  stringsAsFactors = FALSE
)

We have two options here—using base R or the tidyverse package. Both will get the job done:

Option 1: Base R with merge() #

Since the phone number columns have slightly different names (PhNo vs Ph No), we'll specify which columns to match with by.x and by.y. The all.x = TRUE ensures we keep all rows from Table_1 even if there's no match (though in your case, all numbers have matches):

# Merge tables to add Age to Table_1
Table_1_with_Age <- merge(Table_1, Table_2, by.x = "PhNo", by.y = "Ph No", all.x = TRUE)

# Check the result
print(Table_1_with_Age)

Option 2: Tidyverse with dplyr::left_join() #

If you prefer a more readable, pipe-based workflow, use left_join() from the dplyr package. We map the mismatched column names directly in the by argument:

library(dplyr)

# Join and add Age column
Table_1_with_Age <- Table_1 %>%
  left_join(Table_2, by = c("PhNo" = "Ph No"))

# View the updated table
glimpse(Table_1_with_Age)

2. Replace All Values in a Column After Matching Another Dataset #

For the general case where you need to replace an entire column's values after matching a key column to another dataset, here are two reliable methods:

First, let's set up a sample scenario:

# Example data: Replace "Status" in Table_A with "New_Status" from Table_B
Table_A <- data.frame(
  ID = c(1, 2, 3, 4),
  Product = c("Laptop", "Phone", "Tablet", "Headphones"),
  Status = rep("Old", 4), # Column we want to replace
  stringsAsFactors = FALSE
)

Table_B <- data.frame(
  ID = c(1, 2, 3, 4),
  New_Status = c("Active", "Discontinued", "Active", "Discontinued"),
  stringsAsFactors = FALSE
)

Method 1: Base R with match() #

Use match() to find the position of each matching key in the second dataset, then use those positions to pull the replacement values:

# Match IDs and replace the Status column
Table_A$Status <- Table_B$New_Status[match(Table_A$ID, Table_B$ID)]

# Check the updated table
print(Table_A)

Method 2: Tidyverse with mutate() + left_join() #

This method is more explicit and works well if you're already using tidyverse tools. We join the datasets, replace the target column, then clean up the temporary column:

library(dplyr)

# Join, replace, and clean up
Table_A_updated <- Table_A %>%
  left_join(Table_B, by = "ID") %>%
  mutate(Status = New_Status) %>%
  select(-New_Status) # Remove the temporary New_Status column

# View the result
glimpse(Table_A_updated)

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