在R语言中如何将数据集匹配到区域内对应坐标位置的实现方法

The issue with your initial inner_join is that joining only on Zone creates a cartesian product—every row in df1 for a Zone pairs with every row in df2 for the same Zone, leading to far more rows than you want. To fix this, we need an additional key to pair rows 1-to-1 within each Zone, then adjust df2 to match the row count of df1 per Zone based on your desired logic.

Core Idea #

Add a row index within each Zone to both data frames, then align df2's rows to match df1's length per Zone (either by recycling rows, taking the last N rows, or another custom rule).

Solution 1: Recycle df2 Rows to Match df1's Length #

This repeats df2's rows as needed to cover all rows in df1 for each Zone:

library(dplyr)

# Step 1: Add row index within each Zone to df1
df1_with_idx <- df1 %>%
  group_by(Zone) %>%
  mutate(idx = row_number()) %>%
  ungroup()

# Step 2: Calculate how many rows we need per Zone from df2
df1_zone_counts <- df1_with_idx %>%
  count(Zone, name = "needed_rows")

# Step 3: Expand df2 to have enough rows per Zone
expanded_df2 <- df2 %>%
  group_by(Zone) %>%
  # Repeat rows enough times to cover df1's row count
  mutate(repeat_times = ceiling(df1_zone_counts$needed_rows[df1_zone_counts$Zone == first(Zone)] / n())) %>%
  slice(rep(row_number(), repeat_times)) %>%
  # Reset index to match df1's idx
  mutate(idx = row_number()) %>%
  # Keep only the exact number of rows needed
  slice(1:df1_zone_counts$needed_rows[df1_zone_counts$Zone == first(Zone)]) %>%
  ungroup() %>%
  select(-repeat_times)

# Step 4: Join the data frames
df3 <- df1_with_idx %>%
  left_join(expanded_df2, by = c("Zone", "idx")) %>%
  select(-idx)

# Result:
#   Zone ID X Y
# 1    1  1 1 1
# 2    1  2 2 2
# 3    1  3 3 3
# 4    1  4 1 1  # Recycles first row of df2 Zone1
# 5    2  5 4 4
# 6    2  6 5 5
# 7    2  7 6 6

Solution 2: Align to Match Your Expected Output #

Your expected result uses the last 3 rows of df2 Zone2 and repeats the second row for df1 Zone1's extra row. To replicate this custom pairing:

library(dplyr)

# Step 1: Add row index to df1
df1_with_idx <- df1 %>%
  group_by(Zone) %>%
  mutate(idx = row_number()) %>%
  ungroup()

# Step 2: Customize df2 rows per Zone to match your expected output
custom_df2 <- df2 %>%
  group_by(Zone) %>%
  case_when(
    # For Zone1: take rows 1,2,3,2 (repeat second row)
    Zone == 1 ~ slice(c(1,2,3,2)),
    # For Zone2: take last 3 rows
    Zone == 2 ~ slice(tail(row_number(), 3))
  ) %>%
  mutate(idx = row_number()) %>%
  ungroup()

# Step3: Join
df3 <- df1_with_idx %>%
  left_join(custom_df2, by = c("Zone", "idx")) %>%
  select(-idx)

# Result matches your expected output:
#   Zone ID X Y
# 1    1  1 1 1
# 2    1  2 2 2
# 3    1  3 3 3
# 4    1  4 2 2
# 5    2  5 5 5
# 6    2  6 6 6
# 7    2  7 7 7

Key Takeaway #

Always use an additional grouping key (like row index within Zone) when merging to avoid cartesian products. Adjust df2's row selection/repetition based on your specific pairing rule (recycling, custom row picks, etc.).

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