这里有这个数据框:
smallerDF <- structure(list(category = c("Opponent", "Opponent", "Opponent",
"Opponent", "P1", "P2", "P3", "P2", "P2", "Opponent", "Opponent",
"P1"), Event = c("Good Pass", "Good Pass", "Good Pass", "Turnover",
"Good Pass", "Good Pass", "Good Pass", "Good Pass", "Bad Pass",
"Intercepted Pass", "Bad Pass", "Good Pass"), Value = c(2, 2,
2, -3, 2, 2, 2, 2, -2, 1, -2, 2), `Score Sum` = c(2, 4, 6, 3,
2, 4, 6, 8, 6, 1, -1, 2)), row.names = c(NA, -12L), class = c("tbl_df",
"tbl", "data.frame"))
包含4列12行。第三列是根据事件分配的值。在第4列中，我试图添加值以获得滚动和。所以每当对手出现一个事件时，他们的当前值就会被添加到他们之前的得分总和中，P1/P2/P3也是如此。直到第10行，我已经能够将总数滚动到我所期望的结果。
我在这里写了以下代码:
for (i in 1:nrow(smallerDF)) {
#print(i)
if (smallerDF$Event[i] == "Good Pass") {
smallerDF$Value[i] <- 2
}
if (smallerDF$Event[i] == "Bad Pass") {
smallerDF$Value[i] <- -2
}
if (smallerDF$Event[i] == "Intercepted Pass") {
smallerDF$Value[i] <- 1
}
if (smallerDF$Event[i] == "Turnover") {
smallerDF$Value[i] <- -3
}
if (smallerDF$category[i] == "Opponent") {
#print(i)
if (i != 1 && smallerDF$category[i-1] == "Opponent") {
smallerDF$`Score Sum`[i] <- smallerDF$Value[i] + smallerDF$`Score Sum`[i-1]
}
}
else if (smallerDF$category[i] %in% dfList) {
if (i != 1 && smallerDF$category[i-1] %in% dfList) {
smallerDF$`Score Sum`[i] <- smallerDF$Value[i] + smallerDF$`Score Sum`[i-1]
}
}
}
这一直工作到第10行，因为我使用[I -1]，但我不知道如何让第10行引用回第4行(最后一次使用对手)，将单元格[10,3]添加到单元格[4,4]上。
最终结果应该像
category Event Value `Score Sum`
<chr> <chr> <dbl> <dbl>
1 Opponent Good Pass 2 2
2 Opponent Good Pass 2 4
3 Opponent Good Pass 2 6
4 Opponent Turnover -3 3
5 P1 Good Pass 2 2
6 P2 Good Pass 2 4
7 P3 Good Pass 2 6
8 P2 Good Pass 2 8
9 P2 Bad Pass -2 6
10 Opponent Intercepted Pass 1 4
11 Opponent Bad Pass -2 2
12 P1 Good Pass 2 8
我尝试合并使用这段代码
dt <- data.table(smallerDF)
newDT <- dt[ , .SD[.N] , by = c("category") ]
，但这只返回类别中每个不同值的最后一行，而不是最近/以前出现的类别。
任何帮助都将非常感激。由于
我认为这里的基本前提是分组计算(在for循环中不容易)，并且应该根据category是否为"Opponnent"(将"P1","P2"等集中在一起)进行分组。
数据准备:从上面数据集的前两列开始:
smallerDF <- structure(list(category = c("Opponent", "Opponent", "Opponent", "Opponent", "P1", "P2", "P3", "P2", "P2", "Opponent", "Opponent", "P1"), Event = c("Good Pass", "Good Pass", "Good Pass", "Turnover", "Good Pass", "Good Pass", "Good Pass", "Good Pass", "Bad Pass", "Intercepted Pass", "Bad Pass", "Good Pass")), row.names = c(NA, -12L), class = c("tbl_df", "tbl", "data.frame"))
我将添加"时间"列:一些实用程序(例如，base::merge)不遵守行顺序，尽管尽了最大努力。我认为无论如何，有一个"时间"通常更安全。组件，以消除意外的重新排序。下面的data.table和dplyr解决方案都没有无意中重新排序，但这仍然不是一个可怕的想法。
smallerDF$time <- seq_len(nrow(smallerDF))
基地R
这可能是三个函数中最不直观的一个，因为R中的分组函数看起来令人生畏。这些包括ave,aggregate,by,tapply等。现在我将继续使用ave，因为它是最简单的，也许是最容易阅读的。
首先，我们将创建一个"合并/join"表Value(其他方法也可以引入这些值，参见https://stackoverflow.com/a/68999591/3358272;@ViníciusFélix的答案是使用case_when用于此目的的一个很好的例子)。其次，我们将按"对手vs非对手"进行聚合。
values <- data.frame(
Event = c("Good Pass", "Bad Pass", "Intercepted Pass", "Turnover"),
Value = c(2, -2, 1, -3)
)
smallerDF2 <- merge(smallerDF, values, by = "Event", all.x = TRUE, sort = FALSE)
## feel free to verify that `smallerDF2` is no longer in the original order,
## despite `sort=FALSE`. Order is not guaranteed with `base::merge`, period.
smallerDF2 <- smallerDF2[order(smallerDF2$time),]
smallerDF2
# Event category time Value
# 1 Good Pass Opponent 1 2
# 2 Good Pass Opponent 2 2
# 3 Good Pass Opponent 3 2
# 9 Turnover Opponent 4 -3
# 5 Good Pass P1 5 2
# 6 Good Pass P2 6 2
# 7 Good Pass P3 7 2
# 4 Good Pass P2 8 2
# 10 Bad Pass P2 9 -2
# 12 Intercepted Pass Opponent 10 1
# 11 Bad Pass Opponent 11 -2
# 8 Good Pass P1 12 2
smallerDF2$`Score Sum2` <- ave(smallerDF2$Value, smallerDF2$category == "Opponent", FUN = cumsum)
smallerDF2
# Event category time Value Score Sum2
# 1 Good Pass Opponent 1 2 2
# 2 Good Pass Opponent 2 2 4
# 3 Good Pass Opponent 3 2 6
# 9 Turnover Opponent 4 -3 3
# 5 Good Pass P1 5 2 2
# 6 Good Pass P2 6 2 4
# 7 Good Pass P3 7 2 6
# 4 Good Pass P2 8 2 8
# 10 Bad Pass P2 9 -2 6
# 12 Intercepted Pass Opponent 10 1 4
# 11 Bad Pass Opponent 11 -2 2
# 8 Good Pass P1 12 2 8
data.table
library(data.table)
smallerDT <- as.data.table(smallerDF)
smallerDT[values, Value := Value, on = .(Event)]
smallerDT[, `Score Sum2` := cumsum(Value), by = .(category == "Opponent")]
dplyr
library(dplyr)
left_join(smallerDF, values, by = "Event") %>%
group_by(g = (category == "Opponent")) %>%
mutate(`Score Sum` = cumsum(Value)) %>%
ungroup() %>%
select(-g)
tidyverse解决方案
smallerDF %>%
#Removing original values from your data
select(-Value,-`Score Sum`) %>%
#Creating Value variable with case_when
mutate(
Value = case_when(
Event == "Good Pass" ~ 2,
Event == "Bad Pass" ~ -2,
Event == "Intercepted Pass" ~ 1,
Event == "Turnover" ~ -3
),
#Creating auxiliar logical variable (opponent or not oppponent)
Opponent = if_else(category == "Opponent",TRUE,FALSE)
) %>%
#Creating cumulative sum by either Opponent or not oppponent
group_by(Opponent) %>%
mutate(`Score sum` = cumsum(Value))
与产出<标题>
A tibble: 12 x 4
category Event Value `Score Sum`
<chr> <chr> <dbl> <dbl>
1 Opponent Good Pass 2 2
2 Opponent Good Pass 2 4
3 Opponent Good Pass 2 6
4 Opponent Turnover -3 3
5 P1 Good Pass 2 2
6 P2 Good Pass 2 4
7 P3 Good Pass 2 6
8 P2 Good Pass 2 8
9 P2 Bad Pass -2 6
10 Opponent Intercepted Pass 1 1
11 Opponent Bad Pass -2 -1
12 P1 Good Pass 2 2