在R数据框中比对Name列并补全缺失Counts值的技术问询

Got it, let's work through this problem. Your goal is to ensure every name from df1 is included in your final dataset, using df2's counts where available, and falling back to df3's counts whenever df2 is missing a name. Here are two practical approaches:

Using dplyr (Tidyverse) #

If you're using the tidyverse ecosystem, this is a clean, readable way to handle the task:

# Load the dplyr package (install first if needed: install.packages("dplyr"))
library(dplyr)

# Step 1: Keep all names from df1, pull in counts from df2 (NA where missing)
temp_df <- df1 %>%
  left_join(df2, by = "Name")

# Step 2: Pull in counts from df3, then fill missing values from df2 with df3's data
final_df <- temp_df %>%
  left_join(df3, by = "Name") %>%
  mutate(Counts = case_when(
    !is.na(Counts.x) ~ Counts.x,  # Use df2's count if available
    TRUE ~ Counts.y               # Fall back to df3's count otherwise
  )) %>%
  select(Name, Counts)  # Keep only the columns we need

How this works: #

• left_join(df2) ensures we retain every name from df1, even if it's not in df2 (those get NA for Counts).
• We then join with df3 to get the full set of counts, then use case_when to prioritize df2's values over df3's.

Using Base R #

If you prefer not to use external packages, base R's merge function can handle this too:

# Step 1: Merge df1 and df2, keeping all rows from df1
merged_df <- merge(df1, df2, by = "Name", all.x = TRUE)

# Step 2: Merge with df3 to get the complete count data
merged_df <- merge(merged_df, df3, by = "Name", all.x = TRUE)

# Step 3: Replace NA counts from df2 with df3's values
merged_df$Counts <- ifelse(
  is.na(merged_df$Counts.x),
  merged_df$Counts.y,
  merged_df$Counts.x
)

# Step 4: Clean up to get the final dataframe
final_df <- merged_df[, c("Name", "Counts")]

Key Notes: #

• Both methods center around prioritizing df1's name list to make sure no names are left out.
• Since df3 contains every name, you don't have to worry about remaining NA values after the fill.

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