如何拆分DataFrame透视后的层级列并提取行级绝对最小time_delta

1. 合并层级列名 #

透视表生成的多层列可以通过拼接顶层与底层列名实现扁平化，直接修改列名即可：

# 合并层级列名
merged_wide.columns = [f"{col[0]}{col[1]}" for col in merged_wide.columns]

2. 按行添加time_delta绝对值最小值 #

筛选所有以time_delta开头的列，对每行计算绝对值的最小值：

# 计算每行time_delta的绝对值最小值
merged_wide['min_abs_time_delta'] = merged_wide.filter(like='time_delta').abs().min(axis=1)

完整修改后的代码 #

import pandas as pd

gp_columns = {
'name': ['companyA', 'companyB'],
'firm_ID' : [1, 2],
'timestamp_one' : ['2016-04-01', '2017-09-01']
}

fund_columns = {
    'firm_ID': [1, 1, 2, 2, 2],
    'department_ID' : [10, 11, 20, 21, 22],
    'timestamp_mult' : ['2015-01-01', '2016-03-01', '2016-10-01', '2017-02-01', '2018-11-01'],
    'number' : [400, 500, 1000, 3000, 4000]
    }

gp_df = pd.DataFrame(gp_columns)
fund_df = pd.DataFrame(fund_columns)
gp_df['timestamp_one'] = pd.to_datetime(gp_df['timestamp_one'])
fund_df['timestamp_mult'] = pd.to_datetime(fund_df['timestamp_mult'])

merged_df = gp_df.merge(fund_df)

merged_df_v1 = merged_df.copy()
merged_df_v1['incidence_num'] = merged_df.groupby('firm_ID')['department_ID']\
                              .transform('cumcount') + 1  # 直接加1简化代码
merged_df_v1['time_delta'] = merged_df_v1['timestamp_mult'] - merged_df_v1['timestamp_one']

merged_wide = pd.pivot(merged_df_v1, index = ['name','firm_ID', 'timestamp_one'], \
                       columns = 'incidence_num', \
                       values = ['department_ID', 'time_delta', 'timestamp_mult', 'number'])

# 合并层级列名
merged_wide.columns = [f"{col[0]}{col[1]}" for col in merged_wide.columns]

# 添加每行的最小绝对值time_delta
merged_wide['min_abs_time_delta'] = merged_wide.filter(like='time_delta').abs().min(axis=1)

# 重置索引得到目标结构
result_df = merged_wide.reset_index()
print(result_df)

说明 #

• 列名合并后，会生成department_ID1、time_delta2这类符合需求的列名，列数量自动匹配每家公司的部门数。
• filter(like='time_delta')精准筛选出所有时间差列，abs().min(axis=1)指定按行计算绝对值的最小值，确保是每行自身的最小值而非整个DataFrame的全局最小值。

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