在R中基于姓名条件合并真假球员统计数据的预处理方案

Step 1: Merge Existing Data Rows #

Got it, let's start with fixing your current dataset. You need to combine rows for specific players (like "Bob" and "Bill"), merge their numerical stats (columns 3 through 40), and keep the correct Name and Team for the real player.

We'll use dplyr for clean, straightforward data manipulation—if you don't have it installed, run install.packages("dplyr") first. Here's how to apply this to your sample data, scaled to your actual column range:

library(dplyr)

# Your sample dataset (replace with your real data)
df <- data.frame(Name = c("Bob","Ben","Bill"), 
                 Team = c("Dogs","Cats","Birds"), 
                 Runs = c(6, 4, 2),
                 Hits = c(3, 1, 4), # Example of another numerical column (up to column 40)
                 stringsAsFactors = FALSE)

# Define mapping: fake player name -> real player name
player_mapping <- c("Bill" = "Bob")

# Merge the rows:
merged_df <- df %>%
  # Replace fake names with their real counterparts
  mutate(Name = ifelse(Name %in% names(player_mapping), player_mapping[Name], Name)) %>%
  # Group by real player's name and team (assuming real players have consistent team data)
  group_by(Name, Team) %>%
  # Sum all numerical columns (columns 3 to the last column)
  summarize(across(3:last_col(), sum), .groups = "drop")

# Check the result
merged_df

This will fold Bill's stats into Bob's row and remove the redundant Bill entry. If your numerical columns need a different aggregation (like averages instead of sums), just swap sum in the across() function with the right method (e.g., mean).

Step 2: Automate for Future Data Scraping #

To avoid manual fixes every week, let's build this logic into a reusable preprocessing function that you can run right after grabbing new data. Here's how:

# Master mapping table—update this once if new fake players pop up
master_player_mapping <- c(
  "Bill" = "Bob",
  # Add more mappings here as needed: e.g., "FakeJane" = "Jane"
)

# Reusable cleaning function
clean_player_data <- function(raw_scraped_data) {
  cleaned_data <- raw_scraped_data %>%
    # Replace fake names using the master mapping
    mutate(Name = ifelse(Name %in% names(master_player_mapping), 
                         master_player_mapping[Name], 
                         Name)) %>%
    # Group and aggregate stats
    group_by(Name, Team) %>%
    summarize(across(3:last_col(), sum), .groups = "drop")
  
  return(cleaned_data)
}

# Usage example for future weeks:
# new_week_data <- your_scraping_function() # Your existing code to grab data
# cleaned_new_data <- clean_player_data(new_week_data)

Now every time you pull new weekly data, this function will automatically merge fake players into their real counterparts—no manual row editing required. Just keep the master_player_mapping updated if new fake names crop up.

Bonus: Fix Inconsistent Team Values #

If the fake player has a mismatched Team value (unlikely, but possible), you can adjust the code to prioritize the real player's team:

merged_df <- df %>%
  mutate(
    # Flag which entries belong to real players
    is_real = !Name %in% names(player_mapping),
    # Replace fake names
    Name = ifelse(Name %in% names(player_mapping), player_mapping[Name], Name)
  ) %>%
  group_by(Name) %>%
  # Keep the team from the real player, sum stats
  summarize(
    Team = first(Team[is_real]),
    across(3:last_col(), sum),
    .groups = "drop"
  )

This ensures you don't end up with an incorrect team for the merged player.

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