首页 文章

计算组平均值，然后根据组滞后

提问于
浏览 次
0

我想尽可能使用 dplyr 包来解决这个问题 .

我有一个幻想足球统计数据集 . 每个记录都是玩家对一个赛季（一周）比赛的统计数据，包括该球员本周值得的幻想足球分数 .

这是我正在使用的数据的片段：

Player  Week  year Fantasy.Points Avg.Fantasy.Ponts
 1 Aaron Hernandez     1  2011           16.3          9.678571
 2 Aaron Hernandez     2  2011           12.2          9.678571
 3 Aaron Hernandez     5  2011            5.6          9.678571
 4 Aaron Hernandez     6  2011           10.8          9.678571
 5 Aaron Hernandez     8  2011            7.1          9.678571
 6 Aaron Hernandez     9  2011            9.5          9.678571
 7 Aaron Hernandez    10  2011            4.1          9.678571
 8 Aaron Hernandez    11  2011            4.4          9.678571
 9 Aaron Hernandez    12  2011            6.2          9.678571
10 Aaron Hernandez    13  2011            4.3          9.678571
11 Aaron Hernandez    14  2011            8.4          9.678571
12 Aaron Hernandez    15  2011           20.5          9.678571
13 Aaron Hernandez    16  2011            3.7          9.678571
14 Aaron Hernandez    17  2011           22.4          9.678571
15 Aaron Hernandez     1  2012           12.4          8.755556
16 Aaron Hernandez     6  2012            9.0          8.755556
17 Aaron Hernandez     7  2012            5.4          8.755556
18 Aaron Hernandez    12  2012            3.6          8.755556
19 Aaron Hernandez    13  2012            9.7          8.755556
20 Aaron Hernandez    14  2012           17.8          8.755556

字段 Avg.Fantasy.Points 是该记录中该玩家一年中平均得分的点数 . 例如，Aaron Hernandez在2011赛季平均值9.678571分，比2012赛季平均值8.755556分 .

我有兴趣计算一个玩家前一年平均得分点数的专栏 . 在上面的例子中，Aaron Hernandez在2012年的记录显示，前一年的平均值为9.68571 .

r dplyr

2 回答

  • 1

    我找到了一个解决方法，类似于SQL中的子查询 .

    说 df_te 是上面代码段中的数据框：

    df_te %>%
    left_join(
       mutate(next.year = year + 1) %>%    #add a column for the next year
       group_by(Player, year) %>%
       mutate(Previous.Avg.Fantasy.Points = first(Avg.Fantasy.Points) %>%   #Copy of 'Avg.Fantasy.Points' column, with the name I'd like to have for new column
       filter(row_number() == 1) %>%  #Only keep one row per player/year group to avoid duplication upon join
       select(Player, next.year, Previous.Avg.Fantasy.Points)   #keep only columns I'd like to join in
    by = c("Player" = "Player", "year" = "next.year")  #By joining 'year' on LHS table with 'next.year' on RHS table, can get the previous year's average points.     
)
    回复于
  • 0

    由于您使用的是 dplyr 包，我想介绍 lag 函数的使用 . 它可以移动给定行数的值 . 默认值为1.最后一行 select(c(colnames(dt), "Pre.Avg.Fantasy.Ponts")) 仅用于调整列的顺序 . dt2 是最终输出 .

    library(dplyr)

dt2 <- dt %>%
  group_by(Player, year) %>%
  summarise(Avg.Fantasy.Ponts = first(Avg.Fantasy.Ponts)) %>%
  mutate(Pre.Avg.Fantasy.Ponts = lag(Avg.Fantasy.Ponts)) %>%
  select(-Avg.Fantasy.Ponts) %>%
  right_join(dt, by = c("Player", "year")) %>%
  select(c(colnames(dt), "Pre.Avg.Fantasy.Ponts"))

    数据

    dt <- read.table(text = "          Player  Week  year Fantasy.Points Avg.Fantasy.Ponts
 1 'Aaron Hernandez'     1  2011           16.3          9.678571
                 2 'Aaron Hernandez'     2  2011           12.2          9.678571
                 3 'Aaron Hernandez'     5  2011            5.6          9.678571
                 4 'Aaron Hernandez'     6  2011           10.8          9.678571
                 5 'Aaron Hernandez'     8  2011            7.1          9.678571
                 6 'Aaron Hernandez'     9  2011            9.5          9.678571
                 7 'Aaron Hernandez'    10  2011            4.1          9.678571
                 8 'Aaron Hernandez'    11  2011            4.4          9.678571
                 9 'Aaron Hernandez'    12  2011            6.2          9.678571
                 10 'Aaron Hernandez'    13  2011            4.3          9.678571
                 11 'Aaron Hernandez'    14  2011            8.4          9.678571
                 12 'Aaron Hernandez'    15  2011           20.5          9.678571
                 13 'Aaron Hernandez'    16  2011            3.7          9.678571
                 14 'Aaron Hernandez'    17  2011           22.4          9.678571
                 15 'Aaron Hernandez'     1  2012           12.4          8.755556
                 16 'Aaron Hernandez'     6  2012            9.0          8.755556
                 17 'Aaron Hernandez'     7  2012            5.4          8.755556
                 18 'Aaron Hernandez'    12  2012            3.6          8.755556
                 19 'Aaron Hernandez'    13  2012            9.7          8.755556
                 20 'Aaron Hernandez'    14  2012           17.8          8.755556",
                 header = TRUE, stringsAsFactors = FALSE)
    回复于

相关问题