如何基于choice向量首次出现的1按player生成new_vec向量

First, let's clarify the problem with your sample data (I noticed a couple of lines have extra values—assuming those are typos, I’ll proceed with the logical structure where each row represents player, trial, choice).

The core goal is: for each player, assign 1 to new_vec for all trials before their first choice of 1, and 0 for the trial where 1 first appears and all subsequent trials. If a player never has a choice of 1, all their new_vec values stay 1.

Using Python with Pandas #

Pandas makes this task straightforward with grouping and cumulative operations. Here’s a clean, efficient implementation:

import pandas as pd

# Fixed sample data (corrected typos for consistency)
data = [
    [1, 1, 1], [1, 2, 0],
    [2, 1, 2], [2, 2, 1],
    [3, 1, 3], [3, 2, 0],
    [4, 1, 4], [4, 2, 0],
    [5, 1, 5], [5, 2, 0],
    [6, 1, 0], [6, 2, 1],
    [7, 1, 0], [7, 2, 0],
    [8, 1, 0], [8, 2, 1],
    [9, 1, 0], [9, 2, 0],
    [10, 1, 0], [10, 2, 1]
]

df = pd.DataFrame(data, columns=['player', 'trial', 'choice'])

# Define a function to generate new_vec for each player group
def calculate_new_vec(group):
    # Track if we've passed the first occurrence of 1
    has_seen_first_1 = (group['choice'] == 1).cumsum() > 0
    # Assign 1 before first 1, 0 after or at first 1; all 1s if no 1 exists
    return (~has_seen_first_1).astype(int)

# Apply the function to each player group and merge back into the DataFrame
df['new_vec'] = df.groupby('player').apply(calculate_new_vec).reset_index(drop=True)

print(df)

How This Works #

• groupby('player'): Isolates each player’s sequence of trials and choices.
• (group['choice'] ==1).cumsum(): Counts how many times 1 has appeared up to each row. For rows before the first 1, this sum is 0; from the first 1 onward, it’s ≥1.
• ~has_seen_first_1: Flips the boolean (True becomes False, False becomes True) so we get True for rows before the first 1.
• astype(int): Converts booleans to integers (True → 1, False → 0) to create new_vec.

Sample Output #

Here’s what the resulting DataFrame will look like:

Alternative: Using SQL #

If you’re working with a database, you can use window functions to achieve the same result:

WITH player_first_1 AS (
    SELECT 
        player,
        MIN(trial) AS first_trial_with_1
    FROM your_table
    WHERE choice = 1
    GROUP BY player
)
SELECT 
    t.player,
    t.trial,
    t.choice,
    CASE 
        WHEN pf1.first_trial_with_1 IS NULL THEN 1 -- No 1 exists for this player
        WHEN t.trial < pf1.first_trial_with_1 THEN 1 -- Before first 1
        ELSE 0 -- At or after first 1
    END AS new_vec
FROM your_table t
LEFT JOIN player_first_1 pf1 ON t.player = pf1.player
ORDER BY t.player, t.trial;

This query first identifies the earliest trial where each player chose 1, then uses a CASE statement to assign the correct new_vec value.

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