Pandas：基于两列值为每个分组生成统一新列

Got it, let's fix this without losing any of your existing columns. The perfect tool here is Pandas' groupby() paired with transform()—this combo keeps your original DataFrame structure completely intact, so you won't lose rows or other columns that have unique values per row.

Step-by-Step Solution #

First, let's confirm your sample data setup:

import pandas as pd
import numpy as np

df = pd.DataFrame(data = {'subject': [1, 1, 1, 2, 2, 2, 3, 3, 3], 'val': [np.nan, 2, np.nan, np.nan, np.nan, 7, np.nan, np.nan, 10]})

To add the total column exactly as you want, use one of these approaches:

# Option 1: Explicitly grab the non-NaN value from each group
df['total'] = df.groupby('subject')['val'].transform(lambda x: x.dropna().iloc[0])

# Option 2: Simpler shortcut (works because each group has exactly one non-NaN value)
df['total'] = df.groupby('subject')['val'].transform('max')
# Or use 'first'—both ignore NaNs and pick the single valid value
df['total'] = df.groupby('subject')['val'].transform('first')

Why This Works #

• groupby('subject') clusters your DataFrame by each unique subject ID.
• transform() runs the function (like max or the lambda) on each group, then "broadcasts" the result back to every row in the original group. This means all your original rows and columns stay right where they are.
• Since each subject only has one non-NaN value in val, using max or first is a cleaner shortcut—they automatically skip NaNs and grab the valid value for the group.

Final Result #

After running the code, your DataFrame will match exactly what you wanted:

subject  val  total
0        1  NaN      2
1        1  2.0      2
2        1  NaN      2
3        2  NaN      7
4        2  NaN      7
5        2  7.0      7
6        3  NaN     10
7        3  NaN     10
8        3  10.0     10

No more losing columns like you did with dropna()—all your existing data stays intact.

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