如何将Pandas中类似SQL GROUP_CONCAT的聚合结果作为新列添加至原DataFrame

Great question! The key here is using pandas' transform method instead of apply or agg directly—since transform returns a Series with the same length as your original DataFrame, which you can easily assign as a new column. Alternatively, you can compute the aggregated values first and merge them back into the original DataFrame. Let's walk through both approaches with your sample data.

Step 1: Create the Sample DataFrame #

First, let's set up your example DataFrame to work with:

import pandas as pd

df = pd.DataFrame({
    'team': ['A', 'A', 'A', 'B', 'A'],
    'user': ['elmer', 'daffy', 'bugs', 'dawg', 'foghorn']
})

Method 1: Using transform (Simplest Approach) #

The transform method applies your aggregation function to each group and broadcasts the result to every row in that group. This means you can directly assign the result as a new column:

# Add the concatenated users as a new column
df['group_concat(user)'] = df.groupby('team')['user'].transform(lambda x: ','.join(x))

After running this, your original DataFrame will look like this:

Method 2: Aggregate First, Then Merge #

If you prefer to first create a summary DataFrame of aggregated values, you can merge it back into the original DataFrame using the team column as the key:

# Compute the aggregated values and reset the index to get a DataFrame
aggregated = df.groupby('team')['user'].agg(lambda x: ','.join(x)).reset_index(name='group_concat(user)')

# Merge the aggregated data back into the original DataFrame
df = df.merge(aggregated, on='team', how='left')

This will give you the exact same result as the transform method.

Why Your Original Methods Didn't Return a DataFrame #

When you use df.groupby('team').apply(lambda x: ','.join(x.user)), it returns a Series (indexed by team), not a DataFrame. Using reset_index() on that Series would convert it to a DataFrame, but transform is more efficient if you just want to add the column directly to your original data.

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