Pandas分组聚合问题：合并xyz/ijk分组并计算Count与Avg

Let's break down how to solve this custom grouping problem. The core challenge is creating the right grouping keys that bundle xyz* and ijk* entries together, while keeping other values as-is, then applying the required aggregations.

First, let's recreate your original DataFrame for reference:

import pandas as pd

data = {
    "Start": [1,1,1,1,1,2,2,2,2,2],
    "End": ["abc1", "abc2", "xyz1", "xyz2", "ijk1", "abc1", "xyz1", "xyz2", "ijk1", "ijk2"],
    "N": [10,10,10,10,10,12,12,12,12,12],
    "Count": [2,2,2,2,2,3,1,1,6,1],
    "Avg": [0.5,0.5,0.5,0.5,0.5,0.4,0.1,0.4,0.5,0.7]
}
df = pd.DataFrame(data)

Step 1: Create Custom Grouping Labels #

We need to generate a new column that defines our groups. We'll use numpy.select to apply the grouping rules clearly:

import numpy as np

# Define the conditions and corresponding group labels
conditions = [
    df["End"].str.startswith("xyz"),
    df["End"].str.startswith("ijk")
]
choices = [
    "xyz_group",
    "ijk_group"
]

# Create the group column: use original End value if no conditions match
df["group"] = np.select(conditions, choices, default=df["End"])

Step 2: Group and Aggregate #

Now we group by both Start (since it's a top-level grouping in your data) and our new group column, then apply the required aggregations:

result = df.groupby(["Start", "group"]).agg(
    N=("N", "first"),  # Since N is consistent per Start, first/last/max all work
    Total_Count=("Count", "sum"),
    Avg_Value=("Avg", "mean")
).reset_index()

# Rename columns to match your expected structure (optional)
result = result.rename(columns={"group": "End"})

Final Result #

Running this code will give you the desired output:

Start        End   N  Total_Count  Avg_Value
0      1       abc1  10            2       0.50
1      1       abc2  10            2       0.50
2      1  ijk_group  10            2       0.50
3      1  xyz_group  10            4       0.50
4      2       abc1  12            3       0.40
5      2  ijk_group  12            7       0.60
6      2  xyz_group  12            2       0.25

Why This Works #

• The custom group column ensures all xyz* entries are lumped together, same for ijk*, while other values stay unique.
• Grouping by Start preserves the top-level grouping you have in your original data.
• Using .agg() lets us specify different aggregation functions for each column clearly, avoiding the issues you had with a simple .sum().

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