如何用Pandas GroupBy按单列分组，聚合列最值并保留其他列且无多级列

现有如下Pandas DataFrame：

import pandas as pd

account_number = [1234, 5678, 9012, 1234.0, 5678, 9012, 1234.0, 5678, 9012, 1234.0, 5678, 9012]
client_name = ["Ford", "GM", "Honda", "Ford", "GM", "Honda", "Ford", "GM", "Honda", "Ford", "GM", "Honda"]
database = ["DB_Ford", "DB_GM", "DB_Honda", "DB_Ford", "DB_GM", "DB_Honda", "DB_Ford", "DB_GM", "DB_Honda", "DB_Ford", "DB_GM", "DB_Honda"]
server = ["L01SQL04", "L01SQL08", "L01SQL12", "L01SQL04", "L01SQL08", "L01SQL12", "L01SQL04", "L01SQL08", "L01SQL12", "L01SQL04", "L01SQL08", "L01SQL12"]
order_num = [2145479, 2145506, 2145534, 2145603, 2145658, 2429513, 2145489, 2145516, 2145544, 2145499, 2145526, 2145554]
customer_dob = ["1967-12-01", "1963-07-09", "1986-12-05", "1967-11-01", None, "1986-12-05", "1967-12-01", "1963-07-09", "1986-12-05", "1967-12-01", "1963-07-09", "1986-12-04"]
purchase_date = ["2022-06-18", "2022-04-11", "2021-01-18", "2022-06-20", "2022-04-11", "2021-01-18", "2022-06-22", "2022-04-13", "2021-01-18", "2022-06-24", "2022-04-18", "2021-01-18"]

d = {
    "account_number": account_number, 
    "client_name" : client_name,
    "database" : database,
    "server" : server,
    "order_num" : order_num,
    "customer_dob" : customer_dob,
    "purchase_date" : purchase_date,
}
df = pd.DataFrame(data=d)

dates = ["customer_dob", "purchase_date"]
for date in dates:
    df[date] = pd.to_datetime(df[date])

需求是：按account_number分组，对customer_dob取最大值、purchase_date取最小值，同时保留其他列（这些列在同一account_number下值均一致），但之前的尝试出现多级列或数据问题，需要得到单级列的完整结果。

之前的尝试问题：

• 第一种尝试指定部分列后聚合，生成多级列且无法看到日期列的实际值：

result = df.groupby("account_number")["client_name", "database", "server", "order_num"].agg({"customer_dob": "max", "purchase_date": "min"}).reset_index()

• 第二种尝试包含日期列，但聚合参数写错（使用了不存在的patient_dob和insert_date列名），仍生成多级列且数据重复：

result = df.groupby("account_number")["client_name", "database", "server", "order_num", "customer_dob", "purchase_date"].agg(
    {"patient_dob": "max", "insert_date": "min"}).reset_index()

方法一：聚合日期列后与去重数据合并 #

先按需求聚合日期列，再将原数据中每个account_number对应的唯一其他列信息合并：

# 聚合日期列
date_agg = df.groupby("account_number").agg(
    customer_dob=("customer_dob", "max"),
    purchase_date=("purchase_date", "min")
).reset_index()

# 获取每个account_number对应的唯一其他列数据
other_cols = df.drop(columns=["customer_dob", "purchase_date"]).drop_duplicates("account_number")

# 合并结果
result = pd.merge(other_cols, date_agg, on="account_number")

方法二：直接在agg中指定所有列的聚合规则 #

因为其他列在同一account_number下值一致，可用first/last/max等聚合方式（结果相同），同时指定日期列的规则：

result = df.groupby("account_number").agg(
    client_name=("client_name", "first"),
    database=("database", "first"),
    server=("server", "first"),
    order_num=("order_num", "first"),  # 注意：若order_num同一账号下有不同值，需根据业务需求调整聚合方式
    customer_dob=("customer_dob", "max"),
    purchase_date=("purchase_date", "min")
).reset_index()

方法三：用transform生成聚合列后去重 #

通过transform将聚合后的日期值广播到每一行，再去重得到每个账号的唯一记录：

# 生成聚合后的日期列
df["customer_dob_max"] = df.groupby("account_number")["customer_dob"].transform("max")
df["purchase_date_min"] = df.groupby("account_number")["purchase_date"].transform("min")

# 保留需要的列并去重
result = df.drop(columns=["customer_dob", "purchase_date"]).rename(
    columns={"customer_dob_max": "customer_dob", "purchase_date_min": "purchase_date"}
).drop_duplicates("account_number")

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