在R中基于独立求和创建主/次维度字段的技术求助

需求说明 #

基于月度粒度数据集（包含accountID、consumption、date、platform、type、type_2字段），为每个accountID生成以下字段：

• 主字段：primary_platform、primary_type、primary_type_2，分别对应各维度下消费总和最高的值
• 次字段：secondary_platform、secondary_type、secondary_type_2，分别对应各维度下消费总和第二高的值
  要求三个维度独立计算（非组合维度），最终每个accountID仅保留一条记录。

现有方案问题分析 #

1. 重复分组拼接法：需对每个维度单独分组求和、筛选最大值，后续手动拼接数据，工作量大且易出错
2. 直接which.max法：错误使用单条记录的consumption最大值（而非维度分组求和后的最大值），且Databricks分布式环境不支持platform[which.max(...)]这类本地向量索引操作，导致代码失效

可行解决方案 #

方法1：分维度处理后合并（基础版） #

通过分组求和、排名、转宽表，分别处理三个维度后合并结果，逻辑清晰且兼容Databricks环境：

步骤1：处理Platform维度

# 计算每个账户-平台的总消费，按消费降序排名，保留前2名并转宽表
platform_rank <- my_data %>%
  group_by(accountID, platform) %>%
  summarize(total_consumption = sum(consumption), .groups = "drop") %>%
  group_by(accountID) %>%
  mutate(rank = row_number(desc(total_consumption))) %>%
  filter(rank %in% c(1, 2)) %>%
  pivot_wider(
    id_cols = accountID,
    names_from = rank,
    values_from = platform,
    names_prefix = "platform_rank_"
  ) %>%
  rename(primary_platform = platform_rank_1, secondary_platform = platform_rank_2)

步骤2：处理Type维度

type_rank <- my_data %>%
  group_by(accountID, type) %>%
  summarize(total_consumption = sum(consumption), .groups = "drop") %>%
  group_by(accountID) %>%
  mutate(rank = row_number(desc(total_consumption))) %>%
  filter(rank %in% c(1, 2)) %>%
  pivot_wider(
    id_cols = accountID,
    names_from = rank,
    values_from = type,
    names_prefix = "type_rank_"
  ) %>%
  rename(primary_type = type_rank_1, secondary_type = type_rank_2)

步骤3：处理Type_2维度

type2_rank <- my_data %>%
  group_by(accountID, type_2) %>%
  summarize(total_consumption = sum(consumption), .groups = "drop") %>%
  group_by(accountID) %>%
  mutate(rank = row_number(desc(total_consumption))) %>%
  filter(rank %in% c(1, 2)) %>%
  pivot_wider(
    id_cols = accountID,
    names_from = rank,
    values_from = type_2,
    names_prefix = "type2_rank_"
  ) %>%
  rename(primary_type_2 = type2_rank_1, secondary_type_2 = type2_rank_2)

步骤4：合并所有结果

final_result <- platform_rank %>%
  left_join(type_rank, by = "accountID") %>%
  left_join(type2_rank, by = "accountID")

方法2：批量处理优化版（进阶） #

使用purrr包批量处理三个维度，减少重复代码，提升可维护性：

library(purrr)
library(dplyr)

# 定义通用维度处理函数
process_dimension <- function(data, dimension_col) {
  data %>%
    group_by(accountID, {{dimension_col}}) %>%
    summarize(total_consumption = sum(consumption), .groups = "drop") %>%
    group_by(accountID) %>%
    mutate(rank = row_number(desc(total_consumption))) %>%
    filter(rank %in% c(1, 2)) %>%
    pivot_wider(
      id_cols = accountID,
      names_from = rank,
      values_from = {{dimension_col}},
      names_glue = "{.col}_rank_{rank}"
    ) %>%
    # 重命名为目标字段名
    rename_with(~ gsub("_rank_1", "_primary", .x), matches("_rank_1")) %>%
    rename_with(~ gsub("_rank_2", "_secondary", .x), matches("_rank_2"))
}

# 批量处理三个维度
dimension_list <- map(c("platform", "type", "type_2"), ~ process_dimension(my_data, .x))

# 合并所有维度结果
final_result <- reduce(dimension_list, left_join, by = "accountID")

关键说明 #

• 使用row_number(desc(total_consumption))确保按维度分组后的总消费排名，而非单条记录消费
• pivot_wider将长表转宽表，直接生成主/次字段
• 代码兼容Databricks的SparkR环境（需确保已加载dplyr/sparklyr相关包）

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