如何按条件将DataFrame中Item的Amt减去对应FET的Amt值？

Got it, let's work through this problem together. I'm assuming you're using pandas for your DataFrame since that's the most common tool for this kind of task. Here's how you can get exactly the result you want:

First, let's start with a sample version of your original DataFrame to match your example:

import pandas as pd

# Sample data matching your description
data = {
    'Item': ['RK', 'FET01', 'CS', 'AS', 'FET02'],
    'Amt': [200, 10, 150, 250, 15]
}
df = pd.DataFrame(data)

Step 1: Identify Main Items and Corresponding FET Rows #

First, we'll mark which rows are main items (not starting with "FET") and grab the Amt value from the following FET row if it exists:

# Flag rows that are main items (non-FET)
df['is_main_item'] = ~df['Item'].str.startswith('FET')

# Get the Amt from the next row (potential FET row)
next_row_amt = df['Amt'].shift(-1)
# Check if the next row is a FET row
next_is_fet = df['Item'].shift(-1).str.startswith('FET', na=False)

Step 2: Calculate the Modified Amt #

Now we'll apply the logic: subtract the FET Amt from the main item's Amt only if there's a corresponding FET row below it. Otherwise, keep the original Amt:

df['Modified_Amt'] = df.apply(
    lambda row: row['Amt'] - next_row_amt[row.name] 
    if row['is_main_item'] and next_is_fet[row.name] 
    else row['Amt'],
    axis=1
)

Step 3: Get the Final Cleaned Result #

If you only want to keep the main items with their adjusted values (and drop the FET rows), filter the DataFrame:

final_df = df[df['is_main_item']].drop('is_main_item', axis=1)

The resulting final_df will look exactly like what you need:

Note for Different Mapping Scenarios #

If your FET rows aren't directly below their corresponding main items (e.g., FET rows are mapped to main items via a code in their name, like FETRK for RK), you can use a mapping dictionary instead:

# Extract main item code from FET rows (e.g., "FETRK" becomes "RK")
df['main_code'] = df['Item'].apply(lambda x: x[3:] if x.startswith('FET') else x)

# Create a map of main item codes to their FET Amt
fet_amt_map = df[df['Item'].startswith('FET')].set_index('main_code')['Amt'].to_dict()

# Calculate modified Amt using the map
df['Modified_Amt'] = df.apply(
    lambda row: row['Amt'] - fet_amt_map.get(row['Item'], 0) 
    if not row['Item'].startswith('FET') 
    else row['Amt'],
    axis=1
)

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