用分组最大值填充缺失值并获取对应行的Pandas技术需求

输入数据 #

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],
    "Length": [14, 17, 13, 10, 19, 16],
    "net/gross": [np.nan, "gross", "net", "gross", np.nan, "net"]
})

实现步骤与完整代码 #

核心逻辑 #

1. 提前记录所有原始NaN的位置，用于后续标记修改行；
2. 按colony分组，将measure列的NaN填充为组内最大值；
3. 提取每个分组中measure最大值对应的net/gross和Length值，用来填充对应列的NaN；
4. 根据原始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],
    "Length": [14, 17, 13, 10, 19, 16],
    "net/gross": [np.nan, "gross", "net", "gross", np.nan, "net"]
})

# 提前记录原始NaN的位置，避免填充后无法识别修改行
original_measure_nan = df['measure'].isna()
original_netgross_nan = df['net/gross'].isna()

# 1. 填充measure列的NaN为对应colony分组的最大值
df['measure'] = df.groupby('colony')['measure'].transform(lambda x: x.fillna(x.max()))

# 2. 获取每个分组中measure最大值对应的net/gross和Length值
# 提取每组measure最大的行（若有多个最大值取第一行）
group_max_rows = df.groupby('colony').apply(lambda x: x[x['measure'] == x['measure'].max()].iloc[0])
# 创建映射字典用于填充
net_gross_map = group_max_rows['net/gross'].to_dict()
length_map = group_max_rows['Length'].to_dict()

# 创建length_adj列，初始复制Length列
df['length_adj'] = df['Length']
# 填充net/gross列的NaN
df.loc[original_netgross_nan, 'net/gross'] = df.loc[original_netgross_nan, 'colony'].map(net_gross_map)
# 同步填充length_adj列对应行
df.loc[original_netgross_nan, 'length_adj'] = df.loc[original_netgross_nan, 'colony'].map(length_map)

# 3. 创建remarks列，标记修改行
df['remarks'] = np.where(original_measure_nan | original_netgross_nan, 'max_filled', 'unchanged')

# 调整列顺序匹配期望输出
df = df[['colony', 'measure', 'net/gross', 'length_adj', 'remarks']]
print(df)

运行结果 #

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

关键说明 #

• 使用groupby.transform确保每个分组内的NaN都被该组的measure最大值填充，保持数据的分组一致性；
• 提前记录原始NaN位置，避免填充后无法识别修改行，保证remarks列标记准确；
• 通过分组提取最大值对应行的方式，确保net/gross和length_adj的填充值与该组measure最大值行完全匹配。

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