如何合并含缺失值且共享ID/Contest_no的多份Survey DataFrame

Hey there! Let's break down how to solve this data merging problem step by step—your core issue is not using the right combination of keys to match records, which leads to misaligned rows or duplicated entries.

First, let's clarify: your DataFrames share two critical identifiers (ID for respondents and Contest_no for repeated surveys), and sometimes additional sub-dimensions like Option. Merging only on ID will never work because it creates unwanted Cartesian products between rows of the same respondent.

1. Fixing Merges with Missing Values #

For your first example with DF1, DF2, DF3, the problem was using full_join only on ID. Instead, you need to merge on both ID and Contest_no to align each respondent's answers across contests properly.

Solution Code #

Using dplyr's chained full_join operations:

library(dplyr)

# Merge all DataFrames using both ID and Contest_no as keys
DF_merged <- DF1 %>%
  full_join(DF2, by = c("ID", "Contest_no")) %>%
  full_join(DF3, by = c("ID", "Contest_no"))

# Verify the result matches your expected DF_merged
str(DF_merged)

Why This Works #

• full_join preserves all rows from all DataFrames. When both ID and Contest_no match exactly, columns are merged; when a match is missing (like DF2 has no row for ID=x1, Contest_no=2), it fills the missing columns with NA—exactly the alignment you need.

2. Fixing Row Duplication in Full Datasets #

Your second example (df1 and df2) has no missing values, but merging causes row duplication because each ID + Contest_no + Option group has multiple rows (for Chosen_option 0 and 1). To fix this, you need to add Option to your merge keys to match each row precisely.

Solution Code #

library(dplyr)

# Merge using ID, Contest_no, AND Option as keys
df_merged <- df1 %>%
  full_join(df2, by = c("ID", "Contest_no", "Option"))

# Check row count—should match the original 12 rows
nrow(df_merged)

Optional: Clean Up Duplicate Column Names #

Since df1 and df2 have identical column names (like Chosen_option, Combination), full_join adds .x (from df1) and .y (from df2) suffixes. You can rename these for clarity:

df_merged <- df_merged %>%
  rename(
    Chosen_option_df1 = Chosen_option.x,
    Chosen_option_df2 = Chosen_option.y,
    Combination_df1 = Combination.x,
    Combination_df2 = Combination.y
    # Repeat for other shared column names
  )

3. General Workflow for This Type of Data #

Follow these steps every time you merge survey data with repeated measurements:

• Identify your unique key combination: Always use ID + repeated survey identifier (like Contest_no) + any sub-dimensions (like Option) to ensure each row has a one-to-one match.
• Choose the right join type:
  • Use full_join to keep all records (including missing ones)
  • Use inner_join if you only want records present in all DataFrames
  • Use left_join if you want to retain all rows from your primary DataFrame
• Batch merge multiple DataFrames: If you have more than 3 DataFrames, use purrr::reduce to simplify the process:

  library(purrr)
  library(dplyr)
  
  # Put all your DataFrames into a list
  df_list <- list(DF1, DF2, DF3, df1, df2)
  # Batch merge with your key combination
  df_merged_all <- reduce(df_list, full_join, by = c("ID", "Contest_no"))
  

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