在R语言中使用dplyr生成英超每轮赛事后的球队排名列

Got it, let's work through this step by step. Your original code only tracks points for home teams, but we need to include away teams too to build a full league table for each round. Here's a complete solution that follows your exact ranking rules:

library(tidyverse)
library(worldfootballR)

# Get and clean the initial match data
df_premiere_20 <- get_match_results(country = 'ENG', gender = 'M', season_end_year = c(2020)) %>% 
  select(Season_End_Year, Wk, Date, Home, HomeGoals, Away, AwayGoals)

# Step 1: Reshape data to track every team's performance per round
df_team_rounds <- df_premiere_20 %>%
  # Convert each match into two rows (home + away team)
  pivot_longer(
    cols = c(Home, Away),
    names_to = "Home_Away",
    values_to = "Team"
  ) %>%
  # Calculate points for the current match
  mutate(
    Points = case_when(
      Home_Away == "Home" & HomeGoals > AwayGoals ~ 3,
      Home_Away == "Home" & HomeGoals == AwayGoals ~ 1,
      Home_Away == "Home" & HomeGoals < AwayGoals ~ 0,
      Home_Away == "Away" & AwayGoals > HomeGoals ~ 3,
      Home_Away == "Away" & AwayGoals == HomeGoals ~ 1,
      Home_Away == "Away" & AwayGoals < HomeGoals ~ 0
    ),
    # Track only away goals for each match (0 if team was home)
    Match_Away_Goals = ifelse(Home_Away == "Away", AwayGoals, 0)
  ) %>%
  # Sort to ensure cumulative calculations are in order
  arrange(Team, Wk) %>%
  # Calculate cumulative points and total away goals per team
  group_by(Team) %>%
  mutate(
    Cumulative_Points = cumsum(Points),
    Total_Away_Goals = cumsum(Match_Away_Goals)
  ) %>%
  ungroup()

# Step 2: Calculate weekly rankings
df_with_rankings <- df_team_rounds %>%
  # Sort by round, then your ranking criteria
  arrange(Wk, desc(Cumulative_Points), desc(Total_Away_Goals), Team) %>%
  # Group by each round to assign ranks
  group_by(Wk) %>%
  mutate(
    Rank = row_number()  # Assigns 1 to top team, 20 to bottom
  ) %>%
  ungroup()

# View the result (filter to a specific week to check)
df_with_rankings %>% filter(Wk == 5) %>% select(Wk, Team, Cumulative_Points, Total_Away_Goals, Rank)

Key Details Explained: #

• Reshaping the Data: Using pivot_longer converts each match into two rows, so we can track both home and away teams' performance—critical for building a full league table.
• Points Calculation: The case_when statement handles both home and away scenarios to correctly assign 3, 1, or 0 points per match.
• Cumulative Metrics: cumsum computes running totals for points and away goals, ensuring we have the correct values up to each round.
• Ranking Logic: By sorting first by desc(Cumulative_Points), then desc(Total_Away_Goals), then Team (ascending alphabetical order), row_number() assigns ranks exactly as you specified. Each round will have ranks from 1 to 20.

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