Python Pandas：提取指定行值合并至最大日期行并删除其余行

需求说明 #

按ID分组，将每组内所有行的非空字段值合并到该组中Completed_Date最大的行，最终仅保留这一行，删除组内其他行。

示例数据集 #

ID  MILESTONE_NAME    DESC              Completed_Date   DECISION_DATE  SUBMISSION_DATE  EPUBLISHED_DATE
 1  DECISION         Final Decision       6/6/2017        6/6/2017
 1  DECISION         Response Received    6/5/2017        6/5/2017
 2  SUBMIT           Submission           1/1/2019                        1/1/2019
 2  SUBMIT           Re-Submission        1/20/2019                       1/20/2019
 3  EPUBLICATION     E-Published          2/2/2021                                      2/2/2021
 3  SUBMIT           First Submission     12/1/2020                       12/1/2020

预期输出 #

ID  MILESTONE_NAME    DESC              Completed_Date  DECISION_DATE  EPUBLICATION_DATE  SUBMISSION_DATE  
     1  DECISION        Final Decision       6/6/2017        6/6/2017     
     2  SUBMIT          Re-Submission        1/20/2019                                        1/20/2019                 
     3  EPUBLICATION    E-Published          12/1/2020                         2/2/2021       12/1/2020    

解决方案 #

方法1：使用Python Pandas #

通过分组处理优先保留最新行数据，再补充组内其他行的非空字段值：

import pandas as pd

# 加载示例数据
data = [
    [1, "DECISION", "Final Decision", "6/6/2017", "6/6/2017", None, None],
    [1, "DECISION", "Response Received", "6/5/2017", "6/5/2017", None, None],
    [2, "SUBMIT", "Submission", "1/1/2019", None, "1/1/2019", None],
    [2, "SUBMIT", "Re-Submission", "1/20/2019", None, "1/20/2019", None],
    [3, "EPUBLICATION", "E-Published", "2/2/2021", None, None, "2/2/2021"],
    [3, "SUBMIT", "First Submission", "12/1/2020", None, "12/1/2020", None]
]
columns = ["ID", "MILESTONE_NAME", "DESC", "Completed_Date", "DECISION_DATE", "SUBMISSION_DATE", "EPUBLISHED_DATE"]
df = pd.DataFrame(data, columns=columns)

# 转换日期列格式，方便比较
df["Completed_Date"] = pd.to_datetime(df["Completed_Date"])
for col in ["DECISION_DATE", "SUBMISSION_DATE", "EPUBLISHED_DATE"]:
    df[col] = pd.to_datetime(df[col], errors="coerce")

# 分组处理函数：保留最新行，补充其他行非空值
def process_group(group):
    latest_row = group.loc[group["Completed_Date"].idxmax()]
    for col in group.columns:
        if pd.isna(latest_row[col]):
            non_null_vals = group[col].dropna()
            if not non_null_vals.empty:
                latest_row[col] = non_null_vals.iloc[0]
    return latest_row

# 应用分组并整理结果
result = df.groupby("ID").apply(process_group).reset_index(drop=True)

# 转换日期列回字符串格式（可选）
for col in ["Completed_Date", "DECISION_DATE", "SUBMISSION_DATE", "EPUBLISHED_DATE"]:
    result[col] = result[col].dt.strftime("%m/%d/%Y").replace("NaT", "")

print(result.to_string(index=False))

方法2：使用SQL #

通过CTE获取每组最新日期，再关联原表合并非空字段：

WITH latest_dates AS (
    SELECT ID, MAX(Completed_Date) AS max_completed_date
    FROM milestones
    GROUP BY ID
),
grouped_data AS (
    SELECT 
        m.ID,
        -- 优先取最新行的名称和描述，无值则取组内其他行
        COALESCE(
            MAX(CASE WHEN m.Completed_Date = ld.max_completed_date THEN m.MILESTONE_NAME END),
            MAX(m.MILESTONE_NAME)
        ) AS MILESTONE_NAME,
        COALESCE(
            MAX(CASE WHEN m.Completed_Date = ld.max_completed_date THEN m.DESC END),
            MAX(m.DESC)
        ) AS DESC,
        ld.max_completed_date AS Completed_Date,
        MAX(m.DECISION_DATE) AS DECISION_DATE,
        MAX(m.SUBMISSION_DATE) AS SUBMISSION_DATE,
        MAX(m.EPUBLISHED_DATE) AS EPUBLISHED_DATE
    FROM milestones m
    JOIN latest_dates ld ON m.ID = ld.ID
    GROUP BY m.ID, ld.max_completed_date
)
SELECT * FROM grouped_data;

注：SQL中MAX函数自动忽略NULL值，可直接提取组内非空字段值；对于需要优先保留最新行的字段，用CASE筛选后再用COALESCE补值。

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