Pandas按指定列分组后用同组数据填充最新记录的空字段

实现逻辑 #

核心思路是仅定位每组中CreatedDate最新的行，先用分组聚合得到同组所有非空值的参考合集，再仅对目标行做空值填充，其余行保持原有数据不变。

完整实现代码 #

import pandas as pd

# 构造测试DataFrame（此处可替换为你的实际数据加载逻辑）
data = [{'PersonalID': 84062174,
  'Community': None,
  'Gender': 'male',
  'Date of Birth': '0000-00-00',
  'Title': None,
  'First Name': 'Geoff',
  'Last Name': 'Hawes',
  'Job Title': None,
  'Account': None,
  'Last Updated Date': '2021-06-22 0:00',
  'Exclude From Traffic': 'No',
  'Area Sales Manager': None,
  'CreatedDate': '2021-04-14 10:00',
  'Notes': None,
  'Home Phone': None,
  'Phone Type 1': 'Home',
  'Mobile Phone': 7805554444,
  'Extension': 'x9999',
  'Email': 'ghawes@gmail.com'},
 {'PersonalID': 83471000,
  'Community': None,
  'Gender': None,
  'Date of Birth': '0000-00-00',
  'Title': None,
  'First Name': 'Geoff',
  'Last Name': 'Hawes',
  'Job Title': 'title',
  'Account': None,
  'Last Updated Date': None,
  'Exclude From Traffic': 'No',
  'Area Sales Manager': None,
  'CreatedDate': '2021-04-16 10:00',
  'Notes': 'Project: MY project',
  'Home Phone': 7778881234.0,
  'Phone Type 1': 'Home',
  'Mobile Phone': 7805554444,
  'Extension': None,
  'Email': 'ghawes@gmail.com'},
 {'PersonalID': 83458399,
  'Community': None,
  'Gender': None,
  'Date of Birth': '0000-00-00',
  'Title': None,
  'First Name': 'Geoff',
  'Last Name': 'Hawes',
  'Job Title': None,
  'Account': 'third record',
  'Last Updated Date': None,
  'Exclude From Traffic': 'No',
  'Area Sales Manager': 'you',
  'CreatedDate': '2021-03-20 17:05',
  'Notes': 'Project: My Project2',
  'Home Phone': None,
  'Phone Type 1': 'Home',
  'Mobile Phone': 7805554444,
  'Extension': None,
  'Email': 'ghawes@gmail.com'},
 {'PersonalID': 82290675,
  'Community': None,
  'Gender': 'male',
  'Date of Birth': '0000-00-00',
  'Title': None,
  'First Name': 'trevor',
  'Last Name': 'Hawes',
  'Job Title': 'title',
  'Account': None,
  'Last Updated Date': '2021-06-22 0:00',
  'Exclude From Traffic': 'No',
  'Area Sales Manager': None,
  'CreatedDate': '2021-02-10 21:47',
  'Notes': None,
  'Home Phone': None,
  'Phone Type 1': 'Home',
  'Mobile Phone': 7806665555,
  'Extension': None,
  'Email': 'thawes@hotmail.com'},
 {'PersonalID': 82269976,
  'Community': None,
  'Gender': None,
  'Date of Birth': '0000-00-00',
  'Title': None,
  'First Name': 'trevor',
  'Last Name': 'Hawes',
  'Job Title': None,
  'Account': 'my Account',
  'Last Updated Date': None,
  'Exclude From Traffic': 'No',
  'Area Sales Manager': None,
  'CreatedDate': '2021-02-09 21:47',
  'Notes': 'Project: More about projects',
  'Home Phone': 8887774321.0,
  'Phone Type 1': 'Home',
  'Mobile Phone': 7806665555,
  'Extension': 'X5555',
  'Email': 'thawes@hotmail.com'},
 {'PersonalID': 76166887,
  'Community': None,
  'Gender': 'female',
  'Date of Birth': '0000-00-00',
  'Title': None,
  'First Name': 'Cathryn',
  'Last Name': 'Anderson',
  'Job Title': None,
  'Account': None,
  'Last Updated Date': '2021-02-12 0:00',
  'Exclude From Traffic': 'No',
  'Area Sales Manager': 'Beth',
  'CreatedDate': '2020-06-09 10:59',
  'Notes': None,
  'Home Phone': 9997774445.0,
  'Phone Type 1': 'Cell',
  'Mobile Phone': 7807770000,
  'Extension': None,
  'Email': 'canderson@gmail.com'}]
df = pd.DataFrame.from_dict(data)

# ------------------- 核心处理逻辑 -------------------
# 1. 转换日期字段为datetime格式，保证时间比较准确
df['CreatedDate'] = pd.to_datetime(df['CreatedDate'])
# 定义分组键
group_keys = ['First Name', 'Last Name', 'Email']

# 2. 生成每组的填充参考行：整合同组所有列的非空值
# 如果需要优先用同组创建时间更晚的行的数值填充，可先执行 df = df.sort_values('CreatedDate', ascending=True)
group_ref = df.groupby(group_keys).agg(
    lambda x: x.dropna().iloc[0] if x.notna().any() else None
).reset_index()

# 3. 定位每组中CreatedDate最新的行的索引
latest_row_idx = df.groupby(group_keys)['CreatedDate'].idxmax()

# 4. 仅填充最新行的空值，其余行保持不变
latest_rows = df.loc[latest_row_idx].copy()
# 用参考行填充最新行的空值
filled_latest = latest_rows.set_index(group_keys).fillna(
    group_ref.set_index(group_keys)
).reset_index()
# 把填充后的结果写回原DataFrame
df.loc[latest_row_idx] = filled_latest.reindex(df.columns, axis=1)

# 输出结果
print(df)

说明 #

• 若同组某列所有值均为空，填充后仍为空，符合业务逻辑
• 若需要优先取同组创建时间更晚的非空值作为填充值，可在生成参考行前先将数据按CreatedDate升序排序，再将agg逻辑中的iloc[0]改为iloc[-1]即可

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