使用group_split拆分tibble遇异常，需优化分组使子表均为2行

数据结构 #

structure(list(ID = c("35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24", "35124-54739-2024-02-24"), Book = c("bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365", "bet365"), Home = c("Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky", "Kentucky"), Away = c("Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama", "Alabama"), Team = c("Alabama", "Alabama", "Kentucky", "Kentucky", "Alabama", "Alabama", "Kentucky", "Kentucky", "Alabama", "Kentucky", "Alabama", "Kentucky", "Alabama", "Kentucky", "Alabama", "Kentucky", "Alabama", "Kentucky"), Price = c(110L, 130L, -175L, -150L, 100L, 140L, -190L, -140L, -105L, -130L, -110L, -110L, -130L, -105L, -135L, 100L, -140L, 105L), Points = c(1, -1, 1, -1, 1.5, -1.5, 1.5, -1.5, 2, -2, 2.5, -2.5, 3, -3, 3.5, -3.5, 4, -4)), row.names = c(NA, -18L), class = c("tbl_df", "tbl", "data.frame"))

尝试的拆分代码及问题 #

最初用以下代码拆分：

ncaab_spread_all %>% 
    group_split(ID, Book, abs(Points))

结果里第3至7个子tibble符合预期（每个子表2行，涵盖两支队伍的配对正负Points值），但第1、2个子tibble各有4行——原因是原始数据中同一队伍在相同Points绝对值下有重复记录，导致分组后每个绝对值下包含4行，不符合要求。

解决方案 #

给每个Team+abs(Points)的重复记录分配组号，再基于组号拆分，确保每个子表仅含2行：

library(dplyr)

ncaab_spread_all %>%
  # 为同一队伍、同一Points绝对值的重复记录分配组序号
  group_by(ID, Book, Team, abs(Points)) %>%
  mutate(group_id = row_number()) %>%
  ungroup() %>%
  # 按ID、Book、Points绝对值、组序号拆分
  group_split(ID, Book, abs(Points), group_id)

逻辑说明 #

1. group_by(ID, Book, Team, abs(Points))将数据按赛事ID、博彩公司、队伍、Points绝对值分组；
2. row_number()为每组内的重复记录分配连续序号，区分同一队伍在相同Points绝对值下的不同记录；
3. 最终按ID+Book+abs(Points)+group_id拆分，确保每个子表包含同一组序号下的两支队伍记录，固定为2行，完全符合预期格式。

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