基于Rank列耗尽Jackpot金额并更新DataFrame的Accepted状态

Alright, let's tackle this problem head-on! The goal here is to process your DataFrame rows in order of their Rank priority, use each row's Commission to deduct from the total Jackpot, and mark Accepted as 'YES' only when the full commission can be covered—stopping as soon as the Jackpot runs out.

First, let's start with a sample test dataset that matches your scenario:

import pandas as pd

# Sample test data
data = {
    'Rank': [1, 2, 3, 4, 5],
    'Commission': [100, 200, 150, 300, 50],
    'Jackpot': [400, 400, 400, 400, 400],  # Assuming initial Jackpot is consistent across rows
    'Accepted': ['NO', 'NO', 'NO', 'NO', 'NO']
}
df = pd.DataFrame(data)

Method 1: Iterative Approach (Intuitive for Small Datasets) #

This method processes rows one by one in Rank order, updating the remaining Jackpot and Accepted status as we go. It's easy to follow and works perfectly for smaller datasets:

# Sort the DataFrame by Rank to ensure we process highest priority first
df_sorted = df.sort_values('Rank').reset_index(drop=True)

# Grab the initial Jackpot value (we'll assume it's the same for all rows)
remaining_jackpot = df_sorted['Jackpot'].iloc[0]

# Iterate through each row
for idx, row in df_sorted.iterrows():
    if remaining_jackpot <= 0:
        break  # Stop processing once Jackpot is exhausted
    
    # Check if current Commission can be fully covered by remaining Jackpot
    if row['Commission'] <= remaining_jackpot:
        df_sorted.loc[idx, 'Accepted'] = 'YES'
        remaining_jackpot -= row['Commission']
    else:
        # Not enough Jackpot left for this row, skip it
        continue

# If you need to restore the original row order, merge back with the original DataFrame
df_final = df.merge(df_sorted[['Rank', 'Accepted']], on='Rank', how='left')
df_final = df_final.drop('Accepted_x', axis=1).rename(columns={'Accepted_y': 'Accepted'})

print(df_final)

Output for the Sample Data: #

Method 2: Vectorized Approach (Efficient for Large Datasets) #

If you're working with a large dataset, a vectorized approach will be much faster. We'll calculate cumulative commissions and use that to determine which rows can be fully covered:

df_sorted = df.sort_values('Rank').reset_index(drop=True)
initial_jackpot = df_sorted['Jackpot'].iloc[0]

# Calculate cumulative Commission values
df_sorted['cumulative_commission'] = df_sorted['Commission'].cumsum()

# Mark rows where cumulative Commission is <= initial Jackpot as 'YES'
df_sorted['Accepted'] = df_sorted['cumulative_commission'].apply(lambda x: 'YES' if x <= initial_jackpot else 'NO')

# Clean up the temporary column and restore original order if needed
df_sorted = df_sorted.drop('cumulative_commission', axis=1)
df_final = df.merge(df_sorted[['Rank', 'Accepted']], on='Rank', how='left')
df_final = df_final.drop('Accepted_x', axis=1).rename(columns={'Accepted_y': 'Accepted'})

print(df_final)

This will give you the exact same result as the iterative method, but without the loop overhead.

Key Notes: #

• Rank Order: Always sort by Rank first—this ensures we process highest-priority rows before lower ones.
• Jackpot Assumption: We assumed the initial Jackpot value is consistent across all rows. If your DataFrame has a single Jackpot value stored elsewhere, just replace df_sorted['Jackpot'].iloc[0] with that value.
• Partial Deductions: If you ever need to handle partial deductions (i.e., mark a row as 'YES' even if only part of the Commission is covered), the iterative method can be adjusted easily by modifying the logic inside the loop.

内容的提问来源于stack exchange，提问作者Sander Van der Zeeuw