基于条件为DataFrame分组批量新增fixed spread列

Hey there! Let's work through this problem together. Since you mentioned the original condition wasn't fully detailed, I'll go with the most logical scenario based on your sample data: for each Issuance group, we want the 'fixed spread' to be the Spread value where the Issue Date matches the Date (you can see this is the first row of each group in your example).

Step 1: Prep Your Data (Convert Dates!) #

First, make sure your date columns are actual datetime objects (not strings)—this is crucial for accurate comparisons. Let's start by building your sample DataFrame and cleaning the dates:

import pandas as pd

# Sample data from your question
data = {
    'Issuance': [1,1,1,1,1,1,2,2,2],
    'Issue Date': ['12/31/2018']*6 + ['3/31/2019']*3,
    'Date': ['12/31/2018', '1/31/2019', '2/28/2019', '3/31/2019', '4/30/2019', '5/31/2019',
             '3/31/2019', '4/30/2019', '5/31/2019'],
    'Spread': [3.42, 3.45, 3.49, 3.52, 3.56, 3.59, 3.52, 3.56, 3.59]
}

df = pd.DataFrame(data)

# Convert date columns to datetime
df['Issue Date'] = pd.to_datetime(df['Issue Date'])
df['Date'] = pd.to_datetime(df['Date'])

Step 2: Add the 'fixed spread' Column #

We have two straightforward ways to do this:

Method 1: Use groupby + transform

This method lets us compute the fixed value per group and broadcast it to every row in the group:

def get_fixed_spread(group):
    # Get the Spread where Issue Date matches Date
    fixed = group[group['Issue Date'] == group['Date']]['Spread'].iloc[0]
    return [fixed] * len(group)

df['fixed spread'] = df.groupby('Issuance').apply(get_fixed_spread).explode()

Method 2: Extract Fixed Values First, Then Merge

If you prefer a more explicit approach, extract the fixed spread for each Issuance first, then merge it back to the original DataFrame:

# Get fixed spread per Issuance
fixed_spreads = df[df['Issue Date'] == df['Date']][['Issuance', 'Spread']].rename(columns={'Spread': 'fixed spread'})

# Merge back to original DataFrame
df = df.merge(fixed_spreads, on='Issuance', how='left')

Result #

Either method will give you this output:

If Your Condition is Different... #

If the actual condition isn't matching Issue Date and Date (e.g., you need the first row's Spread, or a specific date offset), just share the full details and I can tweak the code to fit your needs!

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