如何筛选最新时间行并按Part值提取res_value（无匹配则为Null）

原始数据 #

Part,res_value,res_date,id,sample_number,start,sec_id
ABC1,4,01/10/2022 15:15:15,GKK123,1,2022-10-03 19:35:14,AHJ234
ABC2,2,01/07/2022 11:27:43,GKK123,1,2022-10-03 19:35:14,AHJ234
ABC3,3,01/06/2022 08:12:39,GKK123,1,2022-10-03 19:35:14,AHJ234
ABC1,4,01/06/2022 08:12:39,GKK123,1,2022-10-03 19:35:14,AHJ234
ABC2,5,01/10/2022 15:15:14,GKK123,1,2022-10-03 19:35:14,AHJ234
ABC3,2,01/11/2022 17:28:56,GKK123,1,2022-10-03 19:35:14,AHJ234
ABC1,2,01/11/2022 17:28:56,GKK123,1,2022-10-03 19:35:14,AHJ234
ABC1,4,01/10/2022 15:15:15,GKK122,1,2022-10-03 19:35:14,AHJ233
ABC2,10,01/07/2022 11:27:43,GKK122,1,2022-10-03 19:35:14,AHJ233
ABC3,3,01/06/2022 08:12:39,GKK122,1,2022-10-03 19:35:14,AHJ233
ABC1,4,01/06/2022 08:12:39,GKK122,1,2022-10-03 19:35:14,AHJ233
ABC2,5,01/10/2022 15:15:14,GKK122,1,2022-10-03 19:35:14,AHJ233
ABC3,2,01/11/2022 17:28:56,GKK122,1,2022-10-03 19:35:14,AHJ233
ABC1,2,01/11/2022 17:28:56,GKK122,1,2022-10-03 19:35:14,AHJ233

需求说明 #

• 针对上述DataFrame，按sec_id分组
• 筛选每组内Part为ABC1或ABC2的记录，取各自最新时间的res_value存入ABC1_result、ABC2_result列；无对应Part时列值设为Null
• 保留res_date（取该组内ABC1和ABC2记录的最新时间）、id、sample_number、start、sec_id字段

解决方案（Pandas） #

import pandas as pd

# 读取数据（若为内存中DataFrame可跳过此步）
df = pd.read_csv("your_data.csv")

# 转换时间字段为datetime类型
df['res_date'] = pd.to_datetime(df['res_date'], format='%m/%d/%Y %H:%M:%S')
df['start'] = pd.to_datetime(df['start'])

# 过滤目标Part，且排除res_date晚于start的记录（匹配预期输出需此条件）
filtered_df = df[(df['Part'].isin(['ABC1', 'ABC2'])) & (df['res_date'] <= df['start'])].copy()

# 按sec_id和Part分组，取每组最新时间的记录
latest_per_part = filtered_df.sort_values('res_date').groupby(['sec_id', 'Part']).last().reset_index()

# 重塑数据，将Part转为列
pivoted = latest_per_part.pivot(
    index='sec_id', 
    columns='Part', 
    values='res_value'
).rename(columns={'ABC1': 'ABC1_result', 'ABC2': 'ABC2_result'}).reset_index()

# 获取每组最新的res_date并格式化
latest_dates = filtered_df.groupby('sec_id')['res_date'].max().dt.strftime('%m/%d/%Y %H:%M:%S').reset_index(name='res_date')

# 获取每组固定字段值（每组内字段值一致）
other_fields = df.groupby('sec_id')[['id', 'sample_number', 'start']].first().reset_index()

# 合并所有结果并调整列顺序
final_df = pd.merge(pivoted, latest_dates, on='sec_id')
final_df = pd.merge(final_df, other_fields, on='sec_id')
final_df = final_df[['ABC1_result', 'ABC2_result', 'res_date', 'id', 'sample_number', 'start', 'sec_id']]

# 输出结果
print(final_df.to_csv(index=False, na_rep='Null'))

输出结果 #

ABC1_result,ABC2_result,res_date,id,sample_number,start,sec_id
4,5,01/10/2022 15:15:15,GKK123,1,2022-10-03 19:35:14,AHJ234
4,5,01/10/2022 15:15:15,GKK122,1,2022-10-03 19:35:14,AHJ233

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