如何在R中基于日期、球队及赛事计算球队连胜记录？

Let's break this down into clear, efficient steps that work well even for your 30k+ row dataset. We'll use the tidyverse suite (dplyr + tidyr) for fast, readable data manipulation.

Step 1: Prepare the Data #

First, we'll convert the date factor to a proper Date type and sort the data chronologically—this is critical for accurate streak calculations.

library(tidyverse)
library(lubridate) # For date parsing

# Load your dataset (using the dput you provided)
df <- structure(list(Date = structure(c(3L, 10L, 12L, 2L, 7L, 13L, 4L, 8L, 9L, 4L, 11L, 1L, 5L, 6L), .Label = c("03/10/2018", "04/10/2018", "04/11/2018", "09/10/2018", "10/11/2018", "13/09/2018", "16/09/2018", "16/10/2018", "20/09/2018", "21/10/2018", "22/10/2018", "28/09/2018", "30/09/2018"), class = "factor"), Home.Team = structure(c(1L, 3L, 1L, 2L, 3L, 2L, 3L, 1L, 3L, 1L, 2L, 3L, 2L, 3L), .Label = c("Everton", "Liverpool", "Man C"), class = "factor"), Away.Team = structure(c(7L, 1L, 4L, 2L, 3L, 5L, 6L, 7L, 1L, 4L, 2L, 3L, 5L, 6L), .Label = c("Arsenal", "Bournemouth", "Chelsea", "Man C", "Watford", "West Ham", "Wolves" ), class = "factor"), Competition = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 2L, 2L, 2L, 1L, 2L, 2L), .Label = c("Cup", "League" ), class = "factor"), Home.Goals = c(1L, 3L, 0L, 3L, 6L, 5L, 1L, 1L, 3L, 0L, 3L, 6L, 5L, 1L), Away.Goals = c(3L, 1L, 2L, 0L, 0L, 0L, 0L, 3L, 1L, 2L, 0L, 0L, 0L, 0L)), .Names = c("Date", "Home.Team", "Away.Team", "Competition", "Home.Goals", "Away.Goals" ), class = "data.frame", row.names = c(NA, -14L))

# Clean and sort data
df_clean <- df %>%
  mutate(Date = dmy(Date)) %>% # Parse dd/mm/yyyy format
  arrange(Date) %>%
  mutate(Match_ID = row_number()) # Add a unique ID for merging later

Step 2: Calculate Each Streak Type #

We'll compute the four required streaks one by one, then merge them back into the original dataset.

1. Home Team's Home Win Streak #

This counts consecutive home wins by the home team before the current match:

home_streaks <- df_clean %>%
  group_by(Home.Team) %>%
  mutate(
    home_win = ifelse(Home.Goals > Away.Goals, 1, 0),
    # Group consecutive wins/losses
    streak_group = cumsum(home_win != lag(home_win, default = 0)),
    # Calculate current streak length (includes current match if won)
    current_home_streak = ifelse(home_win == 1, sequence(rle(streak_group)$lengths), 0),
    # Streak BEFORE the current match: subtract 1 if current match is a win, else 0
    home_streak_before = ifelse(home_win == 1, current_home_streak - 1, 0)
  ) %>%
  ungroup() %>%
  select(Match_ID, home_streak_before)

2. Away Team's Away Win Streak #

Same logic, but for the away team's consecutive away wins:

away_streaks <- df_clean %>%
  group_by(Away.Team) %>%
  mutate(
    away_win = ifelse(Away.Goals > Home.Goals, 1, 0),
    streak_group = cumsum(away_win != lag(away_win, default = 0)),
    current_away_streak = ifelse(away_win == 1, sequence(rle(streak_group)$lengths), 0),
    away_streak_before = ifelse(away_win == 1, current_away_streak - 1, 0)
  ) %>%
  ungroup() %>%
  select(Match_ID, away_streak_before)

3. Home Team's Total Win Streak (Home + Away) #

We need to include all matches the home team has played (as home or away) to calculate their overall streak:

home_total_streaks <- bind_rows(
  # All matches where the team is home
  df_clean %>% select(Date, Team = Home.Team, Win = ifelse(Home.Goals > Away.Goals, 1, 0), Match_ID, is_home = TRUE),
  # All matches where the team is away
  df_clean %>% select(Date, Team = Away.Team, Win = ifelse(Away.Goals > Home.Goals, 1, 0), Match_ID, is_home = FALSE)
) %>%
  arrange(Date, Match_ID) %>%
  group_by(Team) %>%
  mutate(
    streak_group = cumsum(Win != lag(Win, default = 0)),
    current_total_streak = ifelse(Win == 1, sequence(rle(streak_group)$lengths), 0),
    total_streak_before = ifelse(Win == 1, current_total_streak - 1, 0)
  ) %>%
  ungroup() %>%
  filter(is_home) %>% # Keep only rows where the team is the home team
  select(Match_ID, home_total_streak_before = total_streak_before)

4. Away Team's Total Win Streak (Home + Away) #

Mirror the total streak logic for the away team:

away_total_streaks <- bind_rows(
  df_clean %>% select(Date, Team = Home.Team, Win = ifelse(Home.Goals > Away.Goals, 1, 0), Match_ID, is_away = FALSE),
  df_clean %>% select(Date, Team = Away.Team, Win = ifelse(Away.Goals > Home.Goals, 1, 0), Match_ID, is_away = TRUE)
) %>%
  arrange(Date, Match_ID) %>%
  group_by(Team) %>%
  mutate(
    streak_group = cumsum(Win != lag(Win, default = 0)),
    current_total_streak = ifelse(Win == 1, sequence(rle(streak_group)$lengths), 0),
    total_streak_before = ifelse(Win == 1, current_total_streak - 1, 0)
  ) %>%
  ungroup() %>%
  filter(is_away) %>% # Keep only rows where the team is the away team
  select(Match_ID, away_total_streak_before = total_streak_before)

Step 3: Merge All Streaks into the Final Dataset #

Combine all the calculated streaks back into the cleaned original data:

final_df <- df_clean %>%
  left_join(home_streaks, by = "Match_ID") %>%
  left_join(away_streaks, by = "Match_ID") %>%
  left_join(home_total_streaks, by = "Match_ID") %>%
  left_join(away_total_streaks, by = "Match_ID") %>%
  select(-Match_ID) # Remove the temporary match ID

# View the result
print(final_df)

Key Notes #

• Efficiency: This approach uses vectorized operations (no loops) which is fast enough for 30k+ rows.
• Chronological Order: We sorted the data first to ensure streak calculations follow the correct timeline.
• Streak Definition: The columns show the number of consecutive wins before the current match (not including the current match if it's a win). If you want to include the current match, just remove the -1 in the streak_before calculations.

内容的提问来源于stack exchange，提问作者Daniel Rawlings