Pandas按指定列分组求和并保留其他列的技术问题

Hey there! Let's tackle your problem head-on. First, I'll explain why your groupby operations were dropping NaN values, then show you two solid solutions to get exactly the output you want.

Why Are NaN Records Disappearing in Groupby? #

Pandas' groupby() function automatically excludes groups with NaN values in the grouping keys by default. The reason behind this is that in Python, NaN != NaN—a NaN doesn't equal any value, including itself. So pandas can't reliably group rows with NaN together, so it just skips those groups entirely. That's why all your previous attempts lost the rows where ColToKeep was NaN.

Solution 1: Use dropna=False in Groupby (Recommended) #

Starting with pandas 1.1.0, you can use the dropna=False parameter to force groupby to treat NaN as a valid grouping key. We'll group by both User and ColToKeep, sum your target columns, and keep the ColToKeep values (using first since all values in a valid group will be the same, or all NaN).

Here's the code with your sample data:

import pandas as pd

# Build your sample DataFrame
data = {
    'User': ['ABC', 'ABC', 'ABC', 'ABA', 'ABA', 'AAA', 'AAA', 'BBB', 'XYZ', 'XYZ'],
    'Col1ToSum': [10, 15, 15, 100, 80, 40, 20, 10, 10, 10],
    'Col2ToSum': [440, 410, -200, 110, -10, 10, 10, 15, 10, 10],
    'ColToKeep': [1.015, 1.015, 1.015, 2.24, 2.24, pd.NA, pd.NA, pd.NA, 1.1, 1.5]
}
df = pd.DataFrame(data)

# Group and aggregate as needed
result = df.groupby(
    ['User', 'ColToKeep'],
    as_index=False,
    dropna=False
).agg(
    Col1ToSum=('Col1ToSum', 'sum'),
    Col2ToSum=('Col2ToSum', 'sum'),
    ColToKeep=('ColToKeep', 'first')  # Preserve the ColToKeep value
)

print(result)

This will output exactly what you're expecting:

User  Col1ToSum  Col2ToSum ColToKeep
0  ABC         45        650     1.015
1  ABA        180        100      2.24
2  AAA         60         20      <NA>
3  BBB         10         15      <NA>
4  XYZ         10         10       1.1
5  XYZ         10         10       1.5

Solution 2: Manual NaN Grouping (For Older Pandas Versions) #

If you're stuck with a pandas version before 1.1.0 (when dropna=False was added), you can work around it by replacing NaNs with a unique placeholder, grouping, then converting back to NaN:

# Replace NaN with a temporary placeholder
df['temp_group'] = df['ColToKeep'].fillna('__NaN_GROUP__')

# Group and aggregate
result = df.groupby(['User', 'temp_group'], as_index=False).agg(
    Col1ToSum=('Col1ToSum', 'sum'),
    Col2ToSum=('Col2ToSum', 'sum'),
    ColToKeep=('ColToKeep', 'first')
).drop('temp_group', axis=1)

# Convert placeholder back to NaN
result['ColToKeep'] = result['ColToKeep'].replace('__NaN_GROUP__', pd.NA)

print(result)

This will give you the same correct output as Solution 1.

Key Notes #

• When a User has different ColToKeep values (like XYZ with 1.1 and 1.5), grouping by both columns automatically splits them into separate rows—perfect for your requirement.
• The dropna=False approach is cleaner and more efficient, so use that if your pandas version supports it.

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