基于Pandas按ID汇总数据至新DataFrame的特殊需求实现

Let's tackle your problem step by step — you need to aggregate your DataFrame by ID, handle NaN values, and only sum values if they aren't identical across all rows in a group. Here's how to make it work:

Step 1: Define a Custom Aggregation Function #

First, we'll create a function that checks if all non-null values in a group are the same. If they are, we return that single value; otherwise, we sum the values. It also properly handles groups with all NaNs by returning NaN.

import pandas as pd
import numpy as np

def custom_agg(series):
    # Drop NaN values first
    non_null_vals = series.dropna()
    # If all values are NaN, return NaN
    if non_null_vals.empty:
        return np.nan
    # Check if all non-null values are identical
    if non_null_vals.nunique() == 1:
        return non_null_vals.iloc[0]
    # Otherwise, sum the values
    return non_null_vals.sum()

Step 2: Apply the Function to Your Data #

Now use this function with groupby().agg() to get your desired result. We'll also reset the index to make ID a regular column instead of the group index.

# Your original data
data = pd.DataFrame({
    "ID": [2,2,2,2,4,4,4,52,52,95,95,95,95,95],
    "Surface": [10,12,24,8,84,84,84,np.nan,96,8,6,12,30,12],
    "Volume": [25,30,60,20,200,200,200,np.nan,240,20,15,30,75,30]
})

# Perform grouped aggregation
df = data.groupby("ID").agg({
    "Surface": custom_agg,
    "Volume": custom_agg
}).reset_index()

print(df)

Output #

This will produce exactly the result you're looking for:

ID  Surface  Volume
0   2     54.0   135.0
1   4     84.0   200.0  # Same values across group, no summation
2  52     96.0   240.0  # NaN is ignored, only valid value is kept
3  95     68.0   170.0

Why Your Original Code Didn't Work #

Your initial approach using groupby().agg(sum) would sum all values in a group, even if they're identical (like ID 4 would give 252 for Surface instead of 84). The custom function solves this by first checking for duplicate values before deciding whether to sum or retain the single value.

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