R语言：匹配两个DataFrame并按条件插入行的技术需求

Let's walk through how to solve this problem. The core challenge is inserting rows from compare_df into base_df based on interval conditions while preserving the original order of base_df (since var1/var2 aren't sorted). Here's a step-by-step solution using tidyverse tools, which are great for this kind of data manipulation:

Step 1: Prepare the Data #

First, we'll reshape compare_df into a long format so we can easily iterate over each non-id column's values. We'll also split the combined val1_val2 strings into separate numeric columns.

library(dplyr)
library(tidyr)

# Reshape compare_df to long format and split value strings
compare_long <- compare_df %>%
  pivot_longer(cols = -id, names_to = "ind", values_to = "val") %>%
  separate(val, into = c("val1", "val2"), sep = "_", convert = TRUE)

Step 2: Process Each ID Group #

We'll group base_df by id to handle each group independently. For each group, we'll:

• Add a row index to track where to insert new rows
• Check each entry from compare_long to see if it fits between any two consecutive rows in the base group
• Collect valid insertions and merge them with the original rows while preserving order

final_df <- base_df %>%
  group_by(id) %>%
  group_modify(function(base_group, group_info) {
    # Get compare data for the current ID
    current_compare <- compare_long %>% filter(id == group_info$id)
    
    # Add row index to original base rows (to preserve order)
    base_with_index <- base_group %>%
      mutate(idx = row_number(), ind = "org")
    
    # Store all valid inserted rows here
    inserts <- list()
    
    # Loop through each compare entry for this ID
    for (i in seq(nrow(current_compare))) {
      comp_entry <- current_compare[i, ]
      val1 <- comp_entry$val1
      val2 <- comp_entry$val2
      entry_ind <- comp_entry$ind
      
      # Find which base row pair the compare entry fits into
      matching_rows <- base_with_index %>%
        mutate(
          next_var1 = lead(var1),
          next_var2 = lead(var2),
          meets_condition = (val1 > var1 & val1 < next_var1) & 
                            (val2 > var2 & val2 < next_var2)
        ) %>%
        filter(meets_condition) %>%
        pull(idx)
      
      # If there's a match, create the insertion row
      if (length(matching_rows) > 0) {
        inserts[[length(inserts) + 1]] <- tibble(
          idx = matching_rows + 0.5, # Insert between current and next row
          ind = entry_ind,
          var1 = val1,
          var2 = val2
        )
      }
    }
    
    # Combine original rows and inserts, sort by index, clean up
    bind_rows(base_with_index, bind_rows(inserts)) %>%
      arrange(idx) %>%
      select(-idx)
  }) %>%
  ungroup() %>%
  select(id, ind, var1, var2) # Reorder columns to match your expected output

Step 3: Verify the Result #

You can check if the output matches your expected final_df with:

all.equal(final_df, final_df) # Returns TRUE if they match

Key Notes: #

• Preserving Order: We use a row index (idx) instead of sorting by var1/var2 to keep the original order of base_df intact.
• Grouped Processing: Handling each id separately ensures we only compare entries that share the same id value.
• Flexibility: This approach works for any number of non-id columns in compare_df (not just xx/yy/zz), since we reshaped to long format.

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