主页 / coding / 问题 / 78916449
Accepted
Astral
Asked: 2024-08-27 06:15:29 +0800 CST

R 体育数据框-合并和汇总多列数据

  • 772

我有一个包含如下体育数据的 R 数据框：

 HomeTeam   AwayTeam   HomeWin     Draw     AwayWin
1 Barcelona    Madrid    2.30      3.28       3.27
2    Madrid Liverpool    8.79      5.12       1.36
3 Liverpool Barcelona    3.41      3.34       2.21
4    Madrid Barcelona    1.38      5.08       7.92
5 Liverpool    Madrid    1.53      4.07       6.83
6 Barcelona Liverpool    3.35      3.62       2.12

因此，主队和客队有单独的列，然后主队、平局和客队获胜赔率有 3 列。

我想创建一个新的数据框来显示每支球队的平均获胜赔率。因此，我想获取每支球队的获胜赔率（无论是主场还是客场）并计算平均值。例如，就巴塞罗那而言，平均获胜赔率为 (2.30 + 2.21 + 7.92 + 3.35) / 4 = 3.95。我想制作的表格如下所示：

      Team       AverageWinOdds
1   Barcelona           3.95
2    Madrid             5.07
3   Liverpool           2.11

我怎样才能做到这一点？

3 个回答

  1. Best Answer

    1）使用最后注释中可重现显示的数据reframe创建一个两列数据框，然后summarize使用它。

    library(dplyr)

dat %>%
  reframe(Team = c(HomeTeam, AwayTeam), Win = c(HomeWin, AwayWin)) %>%
  summarize(avgWin = mean(Win), .by = Team)
##        Team avgWin
## 1 Barcelona 3.9450
## 2    Madrid 5.0675
## 3 Liverpool 2.1050

    2）另一种方法是pivot_longer创建一个长格式数据框，然后summarize像以前一样。

    library(dplyr)
library(tidyr)

dat %>%
  pivot_longer(cols = 1:2, values_to = "Team") %>%
  mutate(Win = ifelse(name == "HomeTeam", HomeWin, AwayWin)) %>%
  summarize(avgWin = mean(Win), .by = Team)
## # A tibble: 3 × 2
##   Team      avgWin
##   <chr>      <dbl>
## 1 Barcelona   3.94
## 2 Madrid      5.07
## 3 Liverpool   2.10

    笔记

    易于重现的输入数据形式：

    dat <- data.frame(
  HomeTeam = c("Barcelona", "Madrid", "Liverpool", "Madrid", "Liverpool", "Barcelona"),
  AwayTeam = c("Madrid", "Liverpool", "Barcelona", "Barcelona", "Madrid", "Liverpool"),
  HomeWin = c(2.3, 8.79, 3.41, 1.38, 1.53, 3.35),
  Draw = c(3.28, 5.12, 3.34, 5.08, 4.07, 3.62),
  AwayWin = c(3.27, 1.36, 2.21, 7.92, 6.83, 2.12)
)
    • 4

  2. 在基础上，我们可以做

    （1）

    # X := your data 
list(X[c("HomeTeam", "HomeWin")], X[c("AwayTeam", "AwayWin")]) |>
  lapply(setNames, c("Team", "Win")) |> 
  do.call(what="rbind") |>
  aggregate(cbind(AvgWinOdds = Win)~Team, data = _, mean)

    或(2)

    （使用@ThomasIsCoding 的unlist()方法）

    aggregate(list(AvgWinOdds = unlist(X[grep("Win", n<-colnames(X))])), 
          list(Team = unlist(X[grep("Team", n)])), 
          mean)

    或(3)

    reshape使用@thelatemail 建议的有点麻烦的-function

    reshape(X, 
        varying = list(c("HomeTeam","AwayTeam"), c("HomeWin","AwayWin")), 
        v.names = c("Team","Win"), 
        direction = "long") |> 
  aggregate(cbind(AvgWin = Win)~Team, data = _, FUN = mean)

    给予

           Team AvgWinOdds
1 Barcelona     3.9450
2 Liverpool     2.1050
3    Madrid     5.0675

    （不同方法的顺序不同）。

    • 3

  3. 你可以尝试

    library(dplyr)

df %>%
  {
    bind_cols(
      Team = unlist(select(., ends_with("Team"))),
      AvgWinOdds = unlist(select(., ends_with("Win"))),
    )
  } %>%
  summarise(AvgWinOdds = mean(AvgWinOdds), .by = Team)

    给出

    # A tibble: 3 × 2
  Team      AvgWinOdds
  <chr>          <dbl>
1 Barcelona       3.94
2 Madrid          5.07
3 Liverpool       2.10

    数据

    > dput(df)
structure(list(HomeTeam = c("Barcelona", "Madrid", "Liverpool",
"Madrid", "Liverpool", "Barcelona"), AwayTeam = c("Madrid", "Liverpool", 
"Barcelona", "Barcelona", "Madrid", "Liverpool"), HomeWin = c(2.3,
8.79, 3.41, 1.38, 1.53, 3.35), Draw = c(3.28, 5.12, 3.34, 5.08,
4.07, 3.62), AwayWin = c(3.27, 1.36, 2.21, 7.92, 6.83, 2.12)), class = "data.frame", row.names = c("1",
"2", "3", "4", "5", "6"))
    • 3

相关问题