使用Python Pandas为DataFrame创建连胜（Winning Streak）列

Got it, let's break down how to solve this problem—we need to add columns tracking consecutive winning streaks for each column in your DataFrame, where a "win" means the column holds the maximum value in its row (including ties for first place).

Step 1: Set Up Sample Data #

First, let's create a sample DataFrame matching your AA/BB/CC structure to test our logic:

import pandas as pd

# Sample input data
data = {
    'AA': [10, 8, 12, 9, 9, 11],
    'BB': [10, 9, 10, 9, 8, 10],
    'CC': [8, 7, 10, 10, 7, 11]
}
df = pd.DataFrame(data)

Step 2: Core Logic to Calculate Streaks #

The solution relies on three key steps:

1. Identify which columns are "winners" in each row (equal to the row's maximum value)
2. Track when a column's winning status changes to create groups of consecutive wins
3. Count cumulative wins within each group, resetting to 0 when the column isn't a winner

Here's the code to implement this:

# 1. Create a boolean mask where True means the column is a winner in the row
win_masks = df.eq(df.max(axis=1), axis=0)

# 2. Calculate consecutive wins for each column
for col in df.columns:
    win_mask = win_masks[col]
    # Create group IDs: each group starts when the win status changes (win ↔ non-win)
    group_ids = (win_mask != win_mask.shift(1)).cumsum()
    # Count cumulative wins in each group, set non-win rows to 0
    consecutive_wins = win_mask.groupby(group_ids).cumsum().where(win_mask, 0)
    # Add the streak column to the original DataFrame
    df[f'CW_{col}'] = consecutive_wins

Step 3: Verify the Result #

If we print the updated DataFrame, we get:

AA  BB  CC  CW_AA  CW_BB  CW_CC
0  10  10   8      1      1      0
1   8   9   7      0      2      0
2  12  10  10      1      0      0
3   9   9  10      0      0      1
4   9   8   7      1      0      0
5  11  10  11      1      0      1

Let's walk through the logic with this output:

• Row 0: AA and BB tie for max, so their streaks start at 1; CC gets 0
• Row 1: BB is the sole max, so its streak increments to 2; AA/CC get 0
• Row 2: AA is the sole max, so its streak resets to 1; BB/CC get 0
• Row 3: CC is the sole max, streak starts at 1; AA/BB get 0
• Row 4: AA is the sole max, streak resets to 1; BB/CC get 0
• Row 5: AA and CC tie for max, both start new streaks at 1; BB gets 0

Alternative: Concise apply Approach #

If you prefer a more streamlined style, you can use apply to process all columns at once:

def calculate_streak(col, parent_df):
    win_mask = col == parent_df.max(axis=1)
    group_ids = (win_mask != win_mask.shift(1)).cumsum()
    return win_mask.groupby(group_ids).cumsum().where(win_mask, 0)

# Generate all streak columns and merge with original DataFrame
streak_cols = df.apply(lambda x: calculate_streak(x, df), axis=0).add_prefix('CW_')
df = pd.concat([df, streak_cols], axis=1)

This produces the exact same result as the loop approach—pick whichever fits your coding style better!

内容的提问来源于stack exchange，提问作者B.A.