R基于不同列的运行计数

Question

我想根据 ColumnA 中某些内容之前在 ColumnB 中出现的次数来获取该内容的运行计数。理想情况下，该计数也可以是 ColumnC 的子集。

例如，我想在此处获取获胜者之前的损失或失败者之前的获胜次数的总计:

#create df
year <- c(2017, 2017, 2017, 2017, 2017, 2016, 2016, 2016, 2016, 2016)
winner <- c('sam', 'ryan', 'sally', 'sally', 'ryan', 'sally', 'mike', 'ryan', 'mike', 'sam')
loser <- c('mike', 'mike', 'ryan', 'sam', 'sam', 'mike', 'sally', 'mike', 'ryan', 'sally')
df <- data.frame(year, winner, loser)

#successul methods for getting winner's cumulative wins or loser's cumulative losses
df <- as.data.table(df)[, winner_wins := seq(.N), by = "winner"][]
df <- as.data.table(df)[, loser_losses := seq(.N), by = "loser"][]

#successul methods for getting winner's cumulative wins or loser's cumulative losses by year
df <- df %>% group_by(year, winner) %>% mutate(winner_wins = row_number())
df <- df %>% group_by(year, loser) %>% mutate(loser_losses = row_number())

#failed attempt to get winner's cumulative losses by year
df <- df %>% group_by(year) %>% mutate(winner_losses = cumsum(winner == loser & year == year))

我希望输出是我的原始数据框，但有四个新列:winner_cum_wins、winner_cum_losses、loser_cum_wins、loser_cum_losses。

Answer 1

这应该为您提供所需的所有数据框:

library(tidyverse)
df %>% 
    group_by(year) %>% 
    mutate(match_id_year = row_number()) %>% 
    gather(outcome, name, -year, -match_id_year) %>% 
    arrange(year, match_id_year) %>% 
    group_by(year, name) %>% 
    mutate(cum_wins_year = cumsum(outcome == "winner"),
           cum_losses_year = cumsum(outcome == "loser"))