在R语言中基于order_id与extension_of匹配条件聚合订单数据的方法

To solve your problem of summarizing orders where an order_id matches the extension_of value of another order (e.g., summing total cost), here's a step-by-step solution using both tidyverse/dplyr and base R methods.

Step 1: Set Up the Sample Data #

First, let's correctly define your sample data frame to match the columns you specified (order_id, customer_id, extension_of, quantity, cost, duration):

# Sample data frame
orders <- data.frame(
  order_id = c(1, 2, 3),
  customer_id = c(123, 456, 789),
  extension_of = c(NA, NA, 1),  # Order 3 extends Order 1
  quantity = c(1, 1, 1),
  cost = c(100, 100, 100),
  duration = c(30, 30, 30)
)

Step 2: Summarize Using dplyr (Tidyverse) #

This is the most intuitive and readable approach for data manipulation in R:

1. Load the dplyr package (install it first if you haven't with install.packages("dplyr")).
2. Create a parent_order column: for each order, use its own order_id if it's not an extension, otherwise use the extension_of value as the parent.
3. Group by the parent order and calculate your desired summaries (sum of cost, quantity, etc.).

library(dplyr)

# Generate summary
orders_summary <- orders %>%
  mutate(parent_order = ifelse(is.na(extension_of), order_id, extension_of)) %>%
  group_by(parent_order) %>%
  summarize(
    total_cost = sum(cost),
    total_quantity = sum(quantity),
    total_duration = sum(duration),
    # Keep the customer ID associated with the parent order
    parent_customer_id = first(customer_id[order_id == parent_order])
  ) %>%
  ungroup()

# View the result
print(orders_summary)

Output: #

# A tibble: 2 × 5
  parent_order total_cost total_quantity total_duration parent_customer_id
         <dbl>      <dbl>          <dbl>          <dbl>              <dbl>
1            1        200              2             60                123
2            2        100              1             30                456

Step 3: Summarize Using Base R #

If you prefer not to use external packages, here's how to do it with base R functions:

# Create parent_order column
orders$parent_order <- ifelse(is.na(orders$extension_of), orders$order_id, orders$extension_of)

# Aggregate using base R's aggregate() function
base_summary <- aggregate(
  cbind(cost, quantity, duration) ~ parent_order,
  data = orders,
  FUN = sum
)

# Add parent customer ID if needed
base_summary$parent_customer_id <- sapply(base_summary$parent_order, function(x) {
  orders$customer_id[orders$order_id == x][1]
})

# View the result
print(base_summary)

Output: #

parent_order cost quantity duration parent_customer_id
1            1  200        2       60                123
2            2  100        1       30                456

Key Notes #

• The parent_order column links each order to its original parent order (whether it's an extension or the original itself).
• You can easily adjust the summarize() or aggregate() sections to include other metrics (like average cost, maximum duration, etc.) by changing the function (e.g., mean(cost), max(duration)).
• If multiple customers are linked to the same parent order, you might want to adjust the customer ID logic to handle that (e.g., unique(customer_id) instead of first()).

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