如何在R中按条件折叠类别并聚合wt字段？（基于dplyr）

Hey there! Let's work through this problem step by step. First, let's start with a simulated dataset that matches your example scenario so we're on the same page:

library(dplyr)

# Simulated test data matching your description
df <- tibble(
  group2 = c("G1", "G2", "G3", "G4", "G5"),
  N = c(0, 15, 12, 3, 6),
  wt = c(5, 3, 4, 2, 1)
)

The Approach #

The core idea is to create a grouping identifier that lumps together rows that need to be merged. Here's how we can do it with dplyr:

1. Mark initial groups: First, we assign a unique ID to rows where N >= 10 (these are our "base" groups that we'll merge smaller rows into). Rows with N < 10 get marked as NA since they need to be merged with a neighboring group.
2. Fill missing group IDs: We fill those NA values upward to attach small-N rows to the next valid base group below them.
3. Handle edge cases: For small-N rows at the start of the dataset (like your first row), we attach them to the next valid group using lead().
4. Aggregate the data: Finally, we group by our created ID, combine the group2 labels, and sum the wt values.

The Code #

# Create merge group identifiers
df_with_groups <- df %>%
  mutate(
    # Assign base group IDs to rows with N >= 10
    group_id = ifelse(N >= 10, row_number(), NA)
  ) %>%
  # Fill NA group IDs upward to attach small-N rows to the next base group below
  fill(group_id, .direction = "up") %>%
  # Handle small-N rows at the start (no base group above them)
  mutate(group_id = ifelse(is.na(group_id), lead(group_id), group_id))

# Aggregate to get the final result
final_result <- df_with_groups %>%
  group_by(group_id) %>%
  summarize(
    combined_group = paste(group2, collapse = "+"),
    total_wt = sum(wt)
  ) %>%
  select(-group_id)

# View the result
final_result

Output #

# A tibble: 2 × 2
  combined_group total_wt
  <chr>             <dbl>
1 G1+G2                 8
2 G3+G4+G5              7

Handling Hierarchical Groups (If You Have a Higher Level Group) #

If your data has an upper-level group (like group1) and you need to apply this logic within each group1, just add a group_by(group1) step at the beginning:

# Example with a higher-level group
df_with_group1 <- tibble(
  group1 = c("A", "A", "A", "B", "B", "B"),
  group2 = c("G1", "G2", "G3", "G4", "G5", "G6"),
  N = c(0, 15, 12, 3, 6, 20),
  wt = c(5, 3, 4, 2, 1, 7)
)

# Apply logic within each group1
grouped_result <- df_with_group1 %>%
  group_by(group1) %>%
  mutate(
    group_id = ifelse(N >= 10, row_number(), NA)
  ) %>%
  fill(group_id, .direction = "up") %>%
  mutate(group_id = ifelse(is.na(group_id), lead(group_id), group_id)) %>%
  group_by(group1, group_id) %>%
  summarize(
    combined_group = paste(group2, collapse = "+"),
    total_wt = sum(wt)
  ) %>%
  ungroup() %>%
  select(-group_id)

Let me know if you need to tweak this logic for edge cases (like multiple consecutive small-N rows at the end of a group)!

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