如何在Pandas中按签约日期生成月度debt_90_plus透视列

问题 #

我们有用于信用评分风险审计的银行原始数据，简化后的示例DataFrame如下：

import pandas as pd

d = {'contract_id':['1175082589', '1175082589', '1175082589','1175082589','1575082194','1575082194','1575082194','1575082194'],
    'date_of_contract_signature':['2019-05-01','2019-05-01','2019-05-01','2019-05-01','2019-06-01','2019-06-01','2019-06-01','2019-06-01'], 
'date_of_report': ['2019-05-01', '2019-06-01', '2019-07-01','2019-08-01','2019-06-01', '2019-07-01', '2019-08-01','2019-09-01'], 
'debt_90_plus':[0, 0, 34800,34800,0,0,56500,56500]}
df=pd.DataFrame(data=d)
print(df)

输出：

contract_id date_of_contract_signature date_of_report  debt_90_plus
0  1175082589                 2019-05-01     2019-05-01             0
1  1175082589                 2019-05-01     2019-06-01             0
2  1175082589                 2019-05-01     2019-07-01         34800
3  1175082589                 2019-05-01     2019-08-01         34800
4  1575082194                 2019-06-01     2019-06-01             0
5  1575082194                 2019-06-01     2019-07-01             0
6  1575082194                 2019-06-01     2019-08-01         56500
7  1575082194                 2019-06-01     2019-09-01         56500

我们期望得到按签约日期分组，以相对于签约日期的月份序号（1、2、3、4）为列，展示debt_90_plus月度数值的结果：

d1={'date_of_contract_signature': ['2019-05-01', '2019-06-01'], 1:[0, 0],2:[0,0],3:[34800,56500],4:[34800,56500]}
df1=pd.DataFrame(data=d1)
print(df1)

输出：

date_of_contract_signature  1  2      3      4
0                 2019-05-01  0  0  34800  34800
1                 2019-06-01  0  0  56500  56500

当前尝试了以下代码：

print(pd.pivot_table(df,index=['date_of_contract_signature','date_of_report']))

得到的结果不符合需求：

debt_90_plus
date_of_contract_signature date_of_report              
2019-05-01                 2019-05-01                 0
                           2019-06-01                 0
                           2019-07-01             34800
                           2019-08-01             34800
2019-06-01                 2019-06-01                 0
                           2019-07-01                 0
                           2019-08-01             56500
                           2019-09-01             56500

请问该如何解决此问题？

解决方案 #

要实现需求，需先计算每个报告日期相对于签约日期的月份序号，再通过透视表重组数据，步骤如下：

1. 转换日期格式并计算相对月份 #

先将日期列转为datetime类型，再计算两者的月份差（签约当月记为第1个月，所以结果加1）：

# 转换日期列格式
df['date_of_contract_signature'] = pd.to_datetime(df['date_of_contract_signature'])
df['date_of_report'] = pd.to_datetime(df['date_of_report'])

# 计算相对月份序号，兼容跨年度情况
df['month_num'] = (df['date_of_report'].dt.year - df['date_of_contract_signature'].dt.year) * 12 + \
                  (df['date_of_report'].dt.month - df['date_of_contract_signature'].dt.month) + 1

2. 透视表重组数据 #

以签约日期为索引、相对月份为列、debt_90_plus为值构建透视表，最后重置索引：

result = df.pivot_table(index='date_of_contract_signature',
                        columns='month_num',
                        values='debt_90_plus',
                        aggfunc='first').reset_index()

# 调整列名格式
result.columns = ['date_of_contract_signature'] + list(result.columns[1:])
print(result)

运行后得到的结果与期望完全一致：

date_of_contract_signature  1  2      3      4
0                 2019-05-01  0  0  34800  34800
1                 2019-06-01  0  0  56500  56500

说明 #

• 相对月份计算逻辑支持跨年度场景（比如2018-11-01签约，2019-01-01报告，相对月份为3）。
• aggfunc='first'是因为每个(签约日期, 相对月份)组合仅有一条数据，取第一个值即可，也可替换为'sum'或'mean'，结果一致。

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