R语言dplyr：基于列正负值提取数据及分组处理异常解决

问题说明 #

使用dplyr处理两个分组数据集时出现结果不一致：第一个数据集处理正常，第二个数据集仅输出2行，但预期应为4行。核心差异在于：

• 第一个数据集每个Team的Price列仅包含正值或负值
• 第二个数据集每个Team的Price列同时存在正负值，业务逻辑要求每对Price需包含一个正值和一个负值

数据集 #

第一个数据集dput #

structure(list(ID = c("4116fa25f9789e2ce647d5920e9500b3", 
"4116fa25f9789e2ce647d5920e9500b3", 
"4116fa25f9789e2ce647d5920e9500b3", "4116fa25f9789e2ce647d5920e9500b3", 
"4116fa25f9789e2ce647d5920e9500b3", "4116fa25f9789e2ce647d5920e9500b3", 
"4116fa25f9789e2ce647d5920e9500b3", "4116fa25f9789e2ce647d5920e9500b3"
), Home = c("Milwaukee Brewers", "Milwaukee Brewers", "Milwaukee Brewers", 
"Milwaukee Brewers", "Milwaukee Brewers", "Milwaukee Brewers", 
"Milwaukee Brewers", "Milwaukee Brewers"), Away = c("Los Angeles Angels", 
"Los Angeles Angels", "Los Angeles Angels", "Los Angeles Angels", 
"Los Angeles Angels", "Los Angeles Angels", "Los Angeles Angels", 
"Los Angeles Angels"), Team = c("Los Angeles Angels", "Milwaukee Brewers", 
"Los Angeles Angels", "Milwaukee Brewers", "Los Angeles Angels", 
"Milwaukee Brewers", "Los Angeles Angels", "Milwaukee Brewers"
), Price = c(-190, 160, -175, 150, -170, 145, -170, 143), Points = 
c(1.5, 
-1.5, 1.5, -1.5, 1.5, -1.5, 1.5, -1.5)), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -8L), groups = 
structure(list(
ID = c("4116fa25f9789e2ce647d5920e9500b3", 
"4116fa25f9789e2ce647d5920e9500b3"
), Team = c("Los Angeles Angels", "Milwaukee Brewers"), .rows = 
structure(list(
    c(1L, 3L, 5L, 7L), c(2L, 4L, 6L, 8L)), ptype = integer(0), class = 
c("vctrs_list_of", 
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -2L), .drop = TRUE))

第二个数据集dput #

structure(list(ID = c("2f95b45e6f5446c06d55e2eb646da6fd", 
"2f95b45e6f5446c06d55e2eb646da6fd", 
"2f95b45e6f5446c06d55e2eb646da6fd", "2f95b45e6f5446c06d55e2eb646da6fd", 
"2f95b45e6f5446c06d55e2eb646da6fd", "2f95b45e6f5446c06d55e2eb646da6fd", 
"2f95b45e6f5446c06d55e2eb646da6fd", "2f95b45e6f5446c06d55e2eb646da6fd"
), Home = c("Baltimore Orioles", "Baltimore Orioles", "Baltimore Orioles", 
"Baltimore Orioles", "Baltimore Orioles", "Baltimore Orioles", 
"Baltimore Orioles", "Baltimore Orioles"), Away = c("Toronto Blue Jays", 
"Toronto Blue Jays", "Toronto Blue Jays", "Toronto Blue Jays", 
"Toronto Blue Jays", "Toronto Blue Jays", "Toronto Blue Jays", 
"Toronto Blue Jays"), Team = c("Baltimore Orioles", "Toronto Blue Jays", 
"Baltimore Orioles", "Toronto Blue Jays", "Baltimore Orioles", 
"Toronto Blue Jays", "Baltimore Orioles", "Toronto Blue Jays"
), Price = c(-175, 145, 155, -180, -170, 145, 158, -190), Points = 
c(1.5, 
-1.5, -1.5, 1.5, 1.5, -1.5, -1.5, 1.5)), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -8L), groups = 
structure(list(
ID = c("2f95b45e6f5446c06d55e2eb646da6fd", 
"2f95b45e6f5446c06d55e2eb646da6fd"
), Team = c("Baltimore Orioles", "Toronto Blue Jays"), .rows = 
structure(list(
    c(1L, 3L, 5L, 7L), c(2L, 4L, 6L, 8L)), ptype = integer(0), class = 
c("vctrs_list_of", 
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -2L), .drop = TRUE))

当前代码与结果 #

当前处理代码 #

df %>%
  group_by(ID, Team) %>% 
  slice_max(Price, with_ties = FALSE) %>% 
  arrange(ID) %>% 
  group_by(ID) %>% 
  mutate(Value = function(Price[1], Price[2]))

第一个数据集处理结果（正常） #

# A tibble: 2 × 7
# Groups:   ID [1]
ID                               Home              Away               
Team               Price Points Value
<chr>                            <chr>             <chr>              
<chr>              <dbl>  <dbl> <dbl>
1 4116fa25f9789e2ce647d5920e9500b3 Milwaukee Brewers Los Angeles Angels 
Los Angeles Angels  -170    1.5 0.014
2 4116fa25f9789e2ce647d5920e9500b3 Milwaukee Brewers Los Angeles Angels 
Milwaukee Brewers    160   -1.5 0.014

第二个数据集处理结果（异常） #

# A tibble: 2 × 7
# Groups:   ID [1]
ID                               Home              Away              
Team              Price Points  Value
<chr>                            <chr>             <chr>              
<chr>             <dbl>  <dbl>  <dbl>
1 2f95b45e6f5446c06d55e2eb646da6fd Baltimore Orioles Toronto Blue Jays 
Baltimore Orioles   158   -1.5 -0.257
2 2f95b45e6f5446c06d55e2eb646da6fd Baltimore Orioles Toronto Blue Jays 
Toronto Blue Jays   145   -1.5 -0.257

问题原因与解决方案 #

问题原因 #

原代码仅按ID和Team分组后取最大Price，第二个数据集每个Team同时存在正负Price，此时slice_max(Price)会直接选取数值最大的那一行（比如Baltimore Orioles的158比所有负值都大），导致每个Team仅保留1行，最终输出2行，不符合预期。

调整后代码 #

df %>%
  # 新增按Price的符号分组，确保每个Team的正负Price各成一组
  group_by(ID, Team, sign(Price)) %>% 
  # 对每个符号组取最大Price（负值组取最接近0的，正值组取最大的）
  slice_max(Price, with_ties = FALSE) %>% 
  arrange(ID, Team, sign(Price)) %>% 
  group_by(ID) %>% 
  # 按业务逻辑计算Value，替换为实际的计算函数
  mutate(Value = your_value_calculation_function(Price))

代码解释 #

• 新增sign(Price)作为分组依据，将每个Team下的正负Price拆分为独立分组
• slice_max(Price)会在每个符号组内选取最大的Price：负值组中取最接近0的数值（比如-170比-175大），正值组中取最大的数值（比如158比155大）
• 最终每个Team会保留2行（正负各1行），两个Team共输出4行，符合业务预期

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