按Colony分组用最大值填充DataFrame的NaN并添加备注列

首先导入所需库并定义原始数据：

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "colony": [22, 22, 22, 33, 33, 33],
    "measure": [np.nan, 7, 11, 13, np.nan, 9],
    "net/gross": [np.nan, "gross", "net", "gross", np.nan, "net"]
})

步骤1：填充measure列的NaN为分组最大值 #

先保存原始列状态用于后续判断修改，再通过分组转换填充缺失值：

# 保存原始列状态
original_measure = df['measure'].copy()
original_netgross = df['net/gross'].copy()

# 按colony分组，用每组最大值填充measure的NaN
df['measure'] = df.groupby('colony')['measure'].transform(lambda x: x.fillna(x.max()))

步骤2：填充net/gross列的NaN为分组内measure最大值对应的取值 #

先提取每个分组中measure最大值所在行的net/gross值，再用该值填充对应分组的缺失值：

# 获取每个colony分组中，measure最大值对应的net/gross值
max_netgross_map = df.groupby('colony').apply(
    lambda grp: grp.loc[grp['measure'] == grp['measure'].max(), 'net/gross'].iloc[0]
).to_dict()

# 填充net/gross的NaN
df['net/gross'] = df.apply(
    lambda row: max_netgross_map[row['colony']] if pd.isna(row['net/gross']) else row['net/gross'],
    axis=1
)

步骤3：添加remarks列标记填充行 #

对比原始列的缺失状态，标记所有被填充过的行：

# 标记填充过的行为"max_filled"，其余为"unchanged"
df['remarks'] = np.where(
    original_measure.isna() | original_netgross.isna(),
    'max_filled',
    'unchanged'
)

最终结果 #

执行上述代码后，输出的DataFrame如下：

colony  measure net/gross     remarks
0      22     11.0       net  max_filled
1      22      7.0     gross  unchanged
2      22     11.0       net  unchanged
3      33     13.0     gross  unchanged
4      33     13.0     gross  max_filled
5      33      9.0       net  unchanged

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