基于历史值计算按frameNo分组的每笔交易后剩余金币

Got it, let's break down how to solve this problem step by step—we need to track a player's remaining gold after each transaction, accounting for both intra-frame (between events in the same minute) and inter-frame (between minutes) gold gains.

Step 1: Clarify Data Structure & Key Assumptions #

First, let's assume your dataset looks something like this (I'll use a table to make it concrete):

Key Definitions: #

• frameNo: Represents a 1-minute window of events
• eventOrder: The sequence of transactions within a frame (sort by this if you don't have timestamps)
• currentGold: Fixed for all events in a frame—this is the gold the player has when entering the frame
• itemCost: Gold spent on the item for that transaction

Critical Assumptions: #

• Inter-frame gold gain = Next frame's currentGold - Last transaction's remaining gold from the previous frame
• Intra-frame gold gain: If you don't have direct data for gold earned between events in a frame, we can distribute the total frame's gold gain evenly across transaction intervals (you can adjust this logic if you have timestamp data for precise calculation)

Step 2: Core Calculation Logic #

For each frame:

1. Sort transactions by eventOrder to ensure we process them in the correct sequence
2. Start with the frame's currentGold as the initial value
3. For each transaction:
   • Subtract the itemCost from the current gold to get the post-transaction remaining gold
   • Add any gold earned between this transaction and the next (if applicable)
4. Calculate inter-frame gold gain to connect the end of one frame to the start of the next

Step 3: Implementation with Python Pandas #

Here's a practical code example to automate this calculation:

import pandas as pd

# Sample dataset
data = {
    "frameNo": [1, 1, 2, 2],
    "eventOrder": [1, 2, 1, 2],
    "currentGold": [1000, 1000, 800, 800],
    "itemCost": [300, 200, 400, 150]
}
df = pd.DataFrame(data)

# 1. Sort transactions by frame and event order
df_sorted = df.sort_values(["frameNo", "eventOrder"]).reset_index(drop=True)

# 2. Calculate remaining gold without any intra-frame gains (base case)
df_sorted["remaining_no_extra"] = df_sorted.groupby("frameNo").apply(
    lambda x: x["currentGold"].iloc[0] - x["itemCost"].cumsum()
).reset_index(drop=True)

# 3. Calculate inter-frame gold gains
frame_summary = df_sorted.groupby("frameNo")["remaining_no_extra"].last().reset_index()
frame_summary["next_frame_gold"] = frame_summary["currentGold"].shift(-1)
frame_summary["inter_frame_gain"] = frame_summary["next_frame_gold"] - frame_summary["remaining_no_extra"]

# 4. Calculate remaining gold with intra-frame gains (evenly distributed)
def add_intra_frame_gains(group):
    frame_id = group.name
    total_intra_gain = frame_summary[frame_summary["frameNo"] == frame_id]["inter_frame_gain"].iloc[0] if frame_id != frame_summary["frameNo"].max() else 0
    num_events = len(group)
    
    remaining = []
    current_gold = group["currentGold"].iloc[0]
    
    for i, cost in enumerate(group["itemCost"]):
        current_gold -= cost
        remaining.append(current_gold)
        # Add intra-frame gain if there's a next transaction
        if i < num_events - 1:
            current_gold += total_intra_gain / (num_events - 1)
    
    group["remaining_with_extra"] = remaining
    return group

df_final = df_sorted.groupby("frameNo").apply(add_intra_frame_gains).reset_index(drop=True)

print(df_final)

Output Explanation: #

frameNo  eventOrder  currentGold  itemCost  remaining_no_extra  remaining_with_extra
0        1           1         1000       300                     700                      700.0
1        1           2         1000       200                     500                      800.0
2        2           1          800       400                     400                      400.0
3        2           2          800       150                     250                      250.0

• remaining_no_extra: Gold left after transactions, ignoring any intra-frame gains
• remaining_with_extra: Gold left after transactions, including evenly distributed intra-frame gains (notice frame 1's second transaction ends at 800, which matches frame 2's initial gold)

Customization Tips #

• If you have timestamp data for events within a frame, replace the even distribution logic with time-based gains (e.g., 10 gold per second, calculate seconds between events)
• Adjust the inter-frame gain calculation if you have explicit data for gold earned between frames (like mission rewards)

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