Pandas跨DataFrame日期对比及区间计算报错求助

第一个错误：The truth value of a Series is ambiguous #

问题根源 #

直接用if语句判断整个Series的布尔结果，Pandas无法确定你要检查所有元素满足、还是任意元素满足，因此抛出歧义错误。比如这段代码：

if loan_data['Start Date'] > fiscal_periods.loc[0,'FP Start']:
    loan_data['P1 Start'] = loan_data['Start Date']
else:
    loan_data['P1 Start'] = fiscal_periods.loc[0, 'FP Start']

这里loan_data['Start Date'] > ...返回的是布尔Series，if语句无法直接处理这种批量判断逻辑。

修复方案 #

用Pandas的矢量化操作替代if-else，推荐两种方式：

1. np.where实现元素级判断：

fp_start = fiscal_periods.loc[0, 'FP Start']
loan_data['P1 Start'] = np.where(loan_data['Start Date'] > fp_start, 
                                 loan_data['Start Date'], 
                                 fp_start)

2. pd.Series.clip简化取值：如果只是取两个日期的较大值，用clip更简洁：

loan_data['P1 Start'] = loan_data['Start Date'].clip(lower=fp_start)

第二个错误：left side of interval must be <= right side #

问题根源 #

创建IntervalIndex时，存在起始日期大于结束日期的情况——要么是贷款数据里有结束日期早于开始日期的脏数据，要么是会计期间的日期序列顺序混乱。

修复方案 #

1. 先清理脏数据：

# 找出贷款中结束日期早于开始日期的无效记录
invalid_loans = tmp_loans[tmp_loans['Loan End'] < tmp_loans['Loan Start']]
print("无效贷款记录：", invalid_loans)

# 方案1：删除无效记录
tmp_loans = tmp_loans[tmp_loans['Loan End'] >= tmp_loans['Loan Start']]

# 方案2：交换错误的起止日期（如果是数据录入错误）
tmp_loans.loc[tmp_loans['Loan End'] < tmp_loans['Loan Start'], 
              ['Loan Start', 'Loan End']] = tmp_loans.loc[tmp_loans['Loan End'] < tmp_loans['Loan Start'], 
                                                          ['Loan End', 'Loan Start']].values

2. 正确创建会计期间的IntervalIndex：
   确保会计期间的日期有序，用from_arrays直接指定每个期间的起止（比from_breaks更直观）：

# 先对会计期间按起始日期排序
fiscal_periods = fiscal_periods.sort_values('Start Date').reset_index(drop=True)

# 创建会计期间的IntervalIndex，设置closed='both'表示包含首尾日期
fiscal_intervals = pd.IntervalIndex.from_arrays(
    fiscal_periods['Start Date'],
    fiscal_periods['End Date'],
    closed='both'
)

完整实现：计算贷款在各会计期间的重叠天数 #

结合上面的修正，完整流程示例：

import pandas as pd
import numpy as np

# 读取数据
fiscal_periods = pd.read_excel('FY22 Fiscal Periods.xlsx')
tmp_loans = pd.read_excel('TMP Loans.xlsx')

# 数据清理
tmp_loans = tmp_loans[tmp_loans['Loan End'] >= tmp_loans['Loan Start']]
fiscal_periods = fiscal_periods.sort_values('Start Date').reset_index(drop=True)

# 定义计算重叠天数的函数
def get_overlap(loan_start, loan_end, fp_start, fp_end):
    overlap_start = max(loan_start, fp_start)
    overlap_end = min(loan_end, fp_end)
    return (overlap_end - overlap_start).days + 1  # 包含起止当天

# 遍历匹配并计算
results = []
for _, loan in tmp_loans.iterrows():
    ls, le = loan['Loan Start'], loan['Loan End']
    for _, fp in fiscal_periods.iterrows():
        fps, fpe = fp['Start Date'], fp['End Date']
        # 判断是否存在重叠
        if le >= fps and ls <= fpe:
            results.append({
                'Loan ID': loan.get('Loan ID'),  # 替换成你的贷款标识字段
                'Fiscal Period': fp['Fiscal Period'],
                'Overlap Start': max(ls, fps),
                'Overlap End': min(le, fpe),
                'Overlap Days': get_overlap(ls, le, fps, fpe)
            })

# 转换为结果DataFrame
overlap_df = pd.DataFrame(results)
print(overlap_df.head())

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