如何用数据集b更新数据集a？求推荐合适工具包

Hey there! From what you've described, you're looking to update a master dataset (a) using values from another dataset (b), matching on the id column and applying custom overwrite rules (like updating some columns for specific IDs while leaving others untouched). Here are the most popular, user-friendly tools to get this done:

Pandas is the standard for data manipulation in Python, and it has flexible methods to handle this exact scenario.

Basic Update (All Matching Columns) #

If you want to overwrite all columns in a with values from b where IDs match, use the update() method:

import pandas as pd

# Load your datasets into DataFrames
a = pd.DataFrame({
    'id': [1, 2, 3],
    'x1': ['a', 'b', 'b'],
    'x2': [3, 2, 1]
})

b = pd.DataFrame({
    'id': [1, 3],
    'x1': ['c', '3'],
    'x2': [3, 4]
})

# Set 'id' as the index for easy matching
a.set_index('id', inplace=True)
b.set_index('id', inplace=True)

# Update values in a with matching values from b
a.update(b)

# Reset index to get 'id' back as a column
a.reset_index(inplace=True)

print(a)

Custom Column-Specific Updates #

To replicate your exact desired output (update x1 for ID=1 but leave x1 for ID=3 untouched, while updating x2 for all matching IDs), use conditional logic with apply() or merge-based filling:

# Reset indices first if you ran the previous code
a = a.reset_index(drop=True)
b = b.reset_index(drop=True)

# Update x1 only for ID=1
a['x1'] = a.apply(
    lambda row: b[b['id'] == row['id']]['x1'].values[0] if row['id'] == 1 else row['x1'],
    axis=1
)

# Update x2 for all matching IDs (use b's value if available, else keep a's)
merged = a.merge(b, on='id', how='left')
a['x2'] = merged['x2_y'].fillna(merged['x2_x'])

print(a)

In R, the dplyr package (part of the tidyverse ecosystem) has dedicated functions for row-wise updates, making this task straightforward.

Using rows_update() #

This function is built specifically for updating a master table with values from a secondary table:

library(dplyr)

# Create your datasets
a <- tibble(
  id = c(1, 2, 3),
  x1 = c("a", "b", "b"),
  x2 = c(3, 2, 1)
)

b <- tibble(
  id = c(1, 3),
  x1 = c("c", "3"),
  x2 = c(3, 4)
)

# To replicate your exact output, split updates by column rules
b_x1_update <- b %>% filter(id == 1) %>% select(id, x1)
b_x2_update <- b %>% select(id, x2)

updated_a <- a %>%
  rows_update(b_x1_update, by = "id") %>%
  rows_update(b_x2_update, by = "id")

print(updated_a)

Flexible Join & Coalesce Approach #

For more control over individual columns, use a left join and coalesce() to pick values from b where available:

updated_a <- a %>%
  left_join(b, by = "id", suffix = c("_a", "_b")) %>%
  mutate(
    x1 = ifelse(id == 1, x1_b, x1_a), # Custom rule for x1
    x2 = coalesce(x2_b, x2_a) # Use b's x2 if present, else keep a's
  ) %>%
  select(id, x1, x2) # Keep only the original columns

print(updated_a)

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