如何基于player_id仅更新DataFrame中活跃玩家的统计数值？

Hey there! Let's tackle this problem where we need to update cumulative stats by adding the latest game's data for players who participated, while keeping non-participants' data intact. Here's how you can do it with Pandas:

Step 1: Define Your Input DataFrames #

First, let's recreate the input data as runnable Pandas code so you can test it directly:

import pandas as pd

# Cumulative stats DataFrame
stats = pd.DataFrame({
    'player_id': [28920, 33351, 30267, 34885, 31988, 5908],
    'player_name': ['S. Smith', 'T. Mancini', 'C. Gentry', 'H. Kim', 'J. Schoop', 'J.J. Hardy'],
    'gp': [1, 0, 0, 1, 0, 1],
    'ab': [2, 0, 0, 0, 0, 3],
    'run': [1, 0, 0, 0, 0, 0],
    'hit': [3, 0, 0, 0, 0, 0]
})

# Latest game's stats DataFrame
game = pd.DataFrame({
    'player_id': [28920, 33351, 34885, 5908],
    'player_name': ['S. Smith', 'T. Mancini', 'H. Kim', 'J.J. Hardy'],
    'gp': [1, 1, 1, 1],
    'ab': [4, 1, 1, 4],
    'run': [1, 0, 2, 0],
    'hit': [1, 1, 0, 0]
})

Step 2: Choose Your Solution #

We have two straightforward approaches to get the desired result:

Approach 1: Index Alignment + Direct Addition (Most Efficient) #

This leverages Pandas' built-in index alignment to automatically match players and add values, with fill_value=0 preserving non-participants' original data:

# Set multi-index to align players correctly
stats_indexed = stats.set_index(['player_id', 'player_name'])
game_indexed = game.set_index(['player_id', 'player_name'])

# Add the values, reset index to get back the original column structure
updated_stats = stats_indexed.add(game_indexed, fill_value=0).astype(int).reset_index()

# Print the result
print(updated_stats)

Approach 2: Merge + Column-wise Addition (More Intuitive for Beginners) #

If you prefer to see every step explicitly, use a left merge to combine the data, then add the relevant columns:

# Left merge to keep all players from stats, match game data where available
merged = stats.merge(game, on=['player_id', 'player_name'], how='left', suffixes=('_cumulative', '_latest'))

# Add the cumulative and latest values for each stat column
for stat_col in ['gp', 'ab', 'run', 'hit']:
    merged[stat_col] = merged[f'{stat_col}_cumulative'].add(merged[f'{stat_col}_latest'], fill_value=0).astype(int)

# Keep only the final columns we need
updated_stats = merged[['player_id', 'player_name', 'gp', 'ab', 'run', 'hit']]

# Print the result
print(updated_stats)

Step 3: Verify the Output #

Both approaches will produce your desired updated stats:

player_id player_name  gp  ab  run  hit
0      28920    S. Smith   2   6    2    4
1      33351  T. Mancini   1   1    0    1
2      30267   C. Gentry   0   0    0    0
3      34885      H. Kim   2   1    2    0
4      31988    J. Schoop   0   0    0    0
5       5908  J.J. Hardy   2   7    0    0

Quick Notes #

• Approach 1 is faster for large datasets since it uses Pandas' optimized index operations.
• Approach 2 is easier to debug if you need to check intermediate steps.

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