基于Pandas DataFrame匹配项在第二个DataFrame创建聚合值列

Got it, let's break down how to build that aggregated DataFrame using pandas—since your real dataset has more variable types, I’ll cover flexible approaches that work across different column types too.

First, let's start by recreating your sample DataFrame so we can work with concrete code:

import pandas as pd

data = {
    'id': [1,2,3,4,5,6],
    'country': ['US','US','US','US','US','US'],
    'state': ['TX','CA','CA','TX','NY','TX'],
    'amt1': [20,30,40,80,20,10],
    'amt2': [40,900,230,670,120,80],
    'var1': ['a','b','c','d','a','c'],
    'var2': ['X','Y','X','X','Y','X']
}

df1 = pd.DataFrame(data)

1. Basic aggregation by a single column #

Let's say you want to group by state, sum the numeric columns (amt1, amt2), and count how many records are in each state:

agg_df_single = df1.groupby('state').agg(
    total_amt1=('amt1', 'sum'),
    total_amt2=('amt2', 'sum'),
    record_count=('id', 'count')
).reset_index()

print(agg_df_single)

This will output:

2. Multi-column grouping with mixed aggregation functions #

If you need to group by multiple columns (e.g., state + var2) and apply different functions to different columns (like summing one, averaging another, and counting unique values in a categorical column):

agg_df_multi = df1.groupby(['state', 'var2']).agg(
    sum_amt1=('amt1', 'sum'),
    avg_amt2=('amt2', 'mean'),
    unique_var1_count=('var1', pd.Series.nunique)
).reset_index()

print(agg_df_multi)

Output:

3. Custom aggregation functions #

For more complex logic (like calculating a ratio between two columns per group), you can define your own function:

# Custom function: Calculate average of (amt1/amt2) for each group
def avg_amt_ratio(group):
    return (group['amt1'] / group['amt2']).mean()

agg_df_custom = df1.groupby('state').agg(
    total_amt1=('amt1', 'sum'),
    avg_amt1_amt2_ratio=('amt1', avg_amt_ratio)  # Use any column as a placeholder; function accesses the whole group
).reset_index()

print(agg_df_custom)

Tips for your real dataset #

• For categorical columns (like var1, var2), common aggregations include nunique (count unique values), first/last (get first/last value in the group), or mode (most frequent value).
• For date columns (if you have them), use min/max to get the earliest/latest date in the group.
• If you need to keep non-aggregated columns alongside results, use transform instead of agg to broadcast aggregated values back to the original row count.

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