如何在Pandas DataFrame中按Parent列条件插入父级/子父级行
2026-7-17
问题说明
从P6导出进度表后，需要预处理数据以适配Milestones Professional应用程序。该程序要求DataFrame包含父行才能实现正确缩进。已创建Parent列作为分组依据，多级分组以Parent name > Sub-Parent name> ...格式标识。需要在对应分组上方插入父级/子父级行，并填充该组的最早开始日期与最晚结束日期，此前尝试条件查找Parent列创建对应行未成功。
当前DataFrame示例
import pandas as pd # 当前DataFrame示例数据 data = [ ['Milestone 1', 'Major Milestones', '01/01/2022', '01/02/2022'], ['Milestone 2', 'Major Milestones 2', '01/03/2022', '01/04/2022'], ['Milestone 3', 'Major Milestones>April MS', '01/05/2022', '01/06/2022'], ['Milestone 4', 'Major Milestones>April MS', '01/07/2022', '01/07/2022'], ['Milestone 5', 'Late Milestones', '01/08/2022', '01/09/2022'], ['Milestone 7', 'Late Milestones', '01/09/2022', '01/10/2022'], ['Milestones 8', 'Late Milestones>June MS', '01/13/2022', '01/14/2022'], ['Milestone 9', 'Late Milestones>June MS', '01/15/2022', '01/16/2022'], ['Milestone 10', 'Late Milestones>July MS', '01/19/2022', '01/20/2022'] ] df = pd.DataFrame(data, columns=['Activity', 'Parent', 'Start', 'Finish']) print(df)
输出：
Activity Parent Start Finish 0 Milestone 1 Major Milestones 01/01/2022 01/02/2022 1 Milestone 2 Major Milestones 2 01/03/2022 01/04/2022 2 Milestone 3 Major Milestones>April MS 01/05/2022 01/06/2022 3 Milestone 4 Major Milestones>April MS 01/07/2022 01/07/2022 4 Milestone 5 Late Milestones 01/08/2022 01/09/2022 5 Milestone 7 Late Milestones 01/09/2022 01/10/2022 6 Milestones 8 Late Milestones>June MS 01/13/2022 01/14/2022 7 Milestone 9 Late Milestones>June MS 01/15/2022 01/16/2022 8 Milestone 10 Late Milestones>July MS 01/19/2022 01/20/2022
理想DataFrame示例
# 理想DataFrame示例数据 data_1 = [ ['Major Milestones', '', '01/01/2022', '01/07/2022'], ['Milestone 1', 'Major Milestones', '01/01/2022', '01/02/2022'], ['Milestone 2', 'Major Milestones', '01/03/2022', '01/04/2022'], ['April MS', '', '01/05/2022', '01/07/2022'], ['Milestone 3', 'Major Milestones>April MS', '01/05/2022', '01/06/2022'], ['Milestone 4', 'Major Milestones>April MS', '01/07/2022', '01/07/2022'], ['Late Milestones', '', '01/08/2022', '01/20/2022'], ['Milestone 5', 'Late Milestones', '01/08/2022', '01/09/2022'], ['June MS', '', '01/11/2022', '01/14/2022'], ['Milestone 6', 'Late Milestones>June MS', '01/10/2022', '01/11/2022'], ['Milestone 7', 'Late Milestones>June MS', '01/09/2022', '01/10/2022'], ['Milestones 8', 'Late Milestones>June MS', '01/13/2022', '01/14/2022'], ['Milestone 9', 'Late Milestones>June MS', '01/15/2022', '01/16/2022'], ['July MS', '', '01/17/2022', '01/20/2022'], ['Milestone 10', 'Late Milestones>July MS', '01/19/2022', '01/20/2022'] ] df_1 = pd.DataFrame(data_1, columns=['Activity', 'Parent', 'Start', 'Finish']) print(df_1)
输出：
Activity Parent Start Finish 0 Major Milestones 01/01/2022 01/07/2022 1 Milestone 1 Major Milestones 01/01/2022 01/02/2022 2 Milestone 2 Major Milestones 01/03/2022 01/04/2022 3 April MS 01/05/2022 01/07/2022 4 Milestone 3 Major Milestones>April MS 01/05/2022 01/06/2022 5 Milestone 4 Major Milestones>April MS 01/07/2022 01/07/2022 6 Late Milestones 01/08/2022 01/20/2022 7 Milestone 5 Late Milestones 01/08/2022 01/09/2022 8 June MS 01/11/2022 01/14/2022 9 Milestone 6 Late Milestones>June MS 01/10/2022 01/11/2022 10 Milestone 7 Late Milestones>June MS 01/09/2022 01/10/2022 11 Milestones 8 Late Milestones>June MS 01/13/2022 01/14/2022 12 Milestone 9 Late Milestones>June MS 01/15/2022 01/16/2022 13 July MS 01/17/2022 01/20/2022 14 Milestone 10 Late Milestones>July MS 01/19/2022 01/20/2022
解决方案代码
import pandas as pd # 加载原始数据 data = [ ['Milestone 1', 'Major Milestones', '01/01/2022', '01/02/2022'], ['Milestone 2', 'Major Milestones 2', '01/03/2022', '01/04/2022'], ['Milestone 3', 'Major Milestones>April MS', '01/05/2022', '01/06/2022'], ['Milestone 4', 'Major Milestones>April MS', '01/07/2022', '01/07/2022'], ['Milestone 5', 'Late Milestones', '01/08/2022', '01/09/2022'], ['Milestone 7', 'Late Milestones', '01/09/2022', '01/10/2022'], ['Milestones 8', 'Late Milestones>June MS', '01/13/2022', '01/14/2022'], ['Milestone 9', 'Late Milestones>June MS', '01/15/2022', '01/16/2022'], ['Milestone 10', 'Late Milestones>July MS', '01/19/2022', '01/20/2022'] ] df = pd.DataFrame(data, columns=['Activity', 'Parent', 'Start', 'Finish']) # 1. 转换日期列为datetime类型，方便计算最早/最晚日期 df['Start'] = pd.to_datetime(df['Start'], format='%m/%d/%Y') df['Finish'] = pd.to_datetime(df['Finish'], format='%m/%d/%Y') # 2. 提取所有需要的父节点层级 parent_rows = [] for parent_str in df['Parent'].unique(): # 拆分父节点路径 levels = parent_str.split('>') # 处理每一级父节点 for i in range(len(levels)): current_parent = '>'.join(levels[:i+1]) # 筛选当前父节点下的所有子项 group = df[df['Parent'].str.startswith(current_parent + '>') | (df['Parent'] == current_parent)] # 计算最早开始和最晚结束 earliest_start = group['Start'].min() latest_finish = group['Finish'].max() # 添加父行数据 parent_rows.append({ 'Activity': levels[i], 'Parent': '', 'Start': earliest_start, 'Finish': latest_finish }) # 3. 去重父行（避免重复添加相同层级） parent_df = pd.DataFrame(parent_rows).drop_duplicates(subset=['Activity', 'Start', 'Finish']) # 4. 合并原始数据和父行数据 combined_df = pd.concat([df, parent_df], ignore_index=True) # 5. 生成排序键：根据Parent路径的层级和名称排序，确保父行在子项上方 def get_sort_key(row): if row['Parent'] == '': # 父行的排序键为自身名称（作为路径） return row['Activity'] else: # 子项的排序键为Parent路径 + 自身名称 return row['Parent'] + '>' + row['Activity'] combined_df['sort_key'] = combined_df.apply(get_sort_key, axis=1) combined_df = combined_df.sort_values('sort_key') # 6. 转换日期回字符串格式，并删除排序键列 combined_df['Start'] = combined_df['Start'].dt.strftime('%m/%d/%Y') combined_df['Finish'] = combined_df['Finish'].dt.strftime('%m/%d/%Y') combined_df = combined_df.drop('sort_key', axis=1).reset_index(drop=True) print(combined_df)
代码说明
- 日期转换：先将
Start和Finish转为datetime类型，方便计算分组的最早/最晚日期。 - 父行生成：遍历所有唯一的
Parent值，拆分层级后为每个层级生成父行，计算对应分组的最早开始和最晚结束日期。 - 去重与合并：去除重复的父行，合并原始数据与父行数据。
- 排序：通过生成排序键，确保父行始终出现在对应子项的上方，符合Milestones Professional的缩进要求。
内容的提问来源于stack exchange，提问作者bcoder
相关产品推荐
相关产品推荐
