如何在R中简化体育赔率数据框结构？
2026-7-3
解决体育赔率DataFrame格式转换问题
核心思路
将长格式的赔率数据重塑为宽格式：以handicap（分数线）为行索引，以博彩商bookie为列，单元格填充对应Over/Under的赔率组合（格式为Over赔率/Under赔率），缺失项用NA填充。
完整R代码实现
library(tidyverse) # 加载你提供的原始数据 data = structure(list(timestamp = c("2024-01-02T05:39:12", "2024-01-02T05:39:12", "2024-01-02T07:00:24", "2024-01-02T07:00:24", "2024-01-02T02:35:05", "2024-01-02T02:35:05", "2024-01-02T04:43:05", "2024-01-02T04:43:05", "2024-01-02T07:26:10", "2024-01-02T04:42:14", "2024-01-02T07:26:12", "2024-01-02T07:26:12", "2024-01-01T23:16:02", "2024-01-01T23:16:02", "2024-01-02T04:43:07", "2024-01-02T04:43:07", "2024-01-01T23:40:17", "2024-01-01T23:40:17"), participant = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), participant_name = c("JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid", "JoelEmbiid"), name = c("Over", "Under", "Over", "Under", "Over", "Under", "Over", "Under", "Over", "Under", "Over", "Under", "Over", "Under", "Over", "Under", "Over", "Under"), handicap = c(34.5, 34.5, 34.5, 34.5, 34.5, 34.5, 52.5, 52.5, 34.5, 34.5, 33.5, 33.5, 34.5, 34.5, 35.5, 35.5, 52.5, 52.5 ), sportsbooks.bookie_key = c("Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points", "Points"), odds = c(-116L, -114L, -120L, -106L, -135L, 105L, -110L, -120L, -120L, -115L, -135L, 105L, -115L, -115L, 100L, -130L, -110L, -120L), bookie = c("pinnacle", "pinnacle", "fanduel", "fanduel", "draftkings", "draftkings", "draftkings", "draftkings", "betmgm", "betmgm", "barstool", "barstool", "barstool", "barstool", "barstool", "barstool", "barstool", "barstool"), prop_id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), unique_id = c(1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1, 1.1)), class = "data.frame", row.names = c("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18")) # 步骤1：去重，保留同一分数线+博彩商+玩法的最新记录 clean_data <- data %>% arrange(desc(timestamp)) %>% distinct(handicap, bookie, name, .keep_all = TRUE) # 步骤2：重塑为目标宽格式 formatted_data <- clean_data %>% select(handicap, bookie, name, odds) %>% # 将Over/Under转为列，便于拼接赔率 pivot_wider(names_from = name, values_from = odds) %>% # 拼接Over和Under赔率，缺失任意一项则设为NA mutate(odds_combined = ifelse(!is.na(Over) & !is.na(Under), paste(Over, Under, sep = "/"), NA)) %>% select(handicap, bookie, odds_combined) %>% # 最终转成宽表：分数线为行，博彩商为列 pivot_wider(names_from = bookie, values_from = odds_combined) %>% # 按分数线升序排序 arrange(handicap) # 查看结果（NA显示为<NA>） print(formatted_data, na.print = "<NA>")
代码说明
- 去重处理：原始数据存在同一博彩商、同一分数线的重复记录（不同时间戳），通过降序排序+去重保留最新数据。
- 第一次格式转换：将
Over和Under从行转为列，让同一分数线+博彩商的两个赔率处于同一行。 - 赔率拼接：用
paste将Over和Under赔率合并为X/Y格式，缺失任意一项则标记为NA。 - 最终宽格式转换：将博彩商从行转为列，完成目标格式的构建。
- 排序：按分数线升序排列，和你提供的示例格式一致。
输出结果示例
运行代码后会得到如下格式的DataFrame：
handicap pinnacle fanduel draftkings betmgm barstool 1 33.5 <NA> <NA> <NA> <NA> -135/105 2 34.5 -116/-114 -120/-106 -135/105 -120/-115 -115/-115 3 35.5 <NA> <NA> <NA> <NA> 100/-130 4 52.5 <NA> <NA> -110/-120 <NA> -110/-120
内容的提问来源于stack exchange，提问作者Brendan Sullivan
相关产品推荐
相关产品推荐
