基于分组后其他列创建Pandas DataFrame新列的技术问询

Hey there! Let's walk through how to implement both of your requirements using pandas. Here's the step-by-step solution:

1. Create the "Value_New" column #

The goal here is to swap the Value between the BUY and SELL entries within each index group. There are two reliable ways to do this:

Option 1: Reverse values in each group (simple, if group order is consistent) #

If each index group always has a BUY entry followed by a SELL entry (like your sample data), you can simply reverse the Value order within each group using transform:

df['Value_New'] = df.groupby('index')['Value'].transform(lambda x: x.iloc[::-1])

Option 2: Explicit mapping (robust, regardless of group order) #

If the order of BUY/SELL in groups might vary, use this method to explicitly map each direction to the opposite value:

# Build a dictionary: {index: {'BUY': sell_value, 'SELL': buy_value}}
value_mapping = df.groupby('index').apply(
    lambda group: {
        'BUY': group[group['Direction'] == 'SELL']['Value'].iloc[0],
        'SELL': group[group['Direction'] == 'BUY']['Value'].iloc[0]
    }
).to_dict()

# Apply the mapping to create Value_New
df['Value_New'] = df.apply(lambda row: value_mapping[row['index']][row['Direction']], axis=1)

2. Create the "Metric_New" column #

We need to populate this column with the Metric value from the SELL entry of each index group, for all rows in the group. Here are two straightforward approaches:

Option 1: Map using a pre-extracted series #

First, extract the SELL metrics per index, then map them back to the original dataframe:

# Get SELL Metric values indexed by their group's index
sell_metrics = df[df['Direction'] == 'SELL'].set_index('index')['Metric']

# Map each row's index to its corresponding SELL Metric
df['Metric_New'] = df['index'].map(sell_metrics)

Option 2: Use groupby transform #

This method directly computes the value within each group using transform:

df['Metric_New'] = df.groupby('index')['Metric'].transform(
    lambda group: group[group['Direction'] == 'SELL'].iloc[0]
)

Final Result #

After running either set of code, your dataframe will match the expected outputs you provided. For example, after adding both columns, the first few rows will look like:

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