如何在Polars中实现与Excel一致的日期月份差计算

需求说明 #

需要计算两列日期的月份差值，而非天数，但尝试多种方法后未能得到和Excel一致的结果。

测试数据 #

import polars as pl
from datetime import datetime

test_df = pl.DataFrame(
    {
        "dt_end": [datetime(2022, 1, 1).date(), datetime(2022, 1, 2).date()],
        "dt_start": [datetime(2021, 5, 7).date(), datetime(2020, 7, 8).date()],
    }
)

初始尝试：得到天数差值 #

直接对日期列相减，得到的是天数差：

test_df.with_columns(
    diff_months = pl.col('dt_end') - pl.col('dt_start')
)

输出：

dt_end      dt_start    diff_months
date        date        duration[ms]
2022-01-01  2021-05-07  239d
2022-01-02  2020-07-08  543d

错误尝试：直接用duration构造月份差 #

以下代码无法正常运行：

test_df.with_columns(
    diff_months = pl.duration(months = pl.col('dt_end') - pl.col('dt_start'))
)

更新：年月份相减法（与Excel结果不一致） #

通过年份和月份的差值计算，但结果和Excel不匹配：

test_df.with_columns(
    diff_months = (pl.col('dt_end').dt.year() - pl.col('dt_start').dt.year()) * 12 +
                    (pl.col('dt_end').dt.month().cast(pl.Int32) - pl.col('dt_start').dt.month().cast(pl.Int32)) 
)

输出：

dt_end      dt_start    diff_months
date        date        i32
2022-01-01  2021-05-07  8
2022-01-02  2020-07-08  18

更新2：按平均天数换算（结果不准确） #

尝试用每月平均30.42天换算成月份，结果仍不符合预期：

test_df.with_columns(
    diff_months = pl.col('dt_end') - pl.col('dt_start')
).with_columns(
    diff_months = (pl.col('diff_months').dt.days()/30.42).floor().cast(pl.Int8)
)

输出：

dt_end      dt_start    diff_months
date        date        i8
2022-01-01  2021-05-07  7
2022-01-02  2020-07-08  17

更新3：匹配Excel参考值的对比测试 #

使用包含Excel参考值的实际数据进行测试：

# 测试数据
snapshot_df = pl.DataFrame(
    {
        "Reporting Month": [datetime(2000, 7, 20).date(), datetime(2000, 8, 20).date(), datetime(2000, 9, 20).date()],
        "Origination date": [datetime(1999, 12, 19).date(), datetime(1999, 12, 19).date(), datetime(1999, 12, 19).date()],
        "Excel_Reference_Age": [7,8,9]
    }
)

对比三种计算方式的结果：

snapshot_df.with_columns(
    diff_months = pl.col('Reporting Month') - pl.col('Origination date')
).with_columns(
    diff_months = (pl.col('diff_months').dt.days()/30.42).floor().cast(pl.Int16),

    diff_months_1 = (pl.col('Reporting Month').dt.year() - pl.col('Origination date').dt.year()) * 12 +
                    (pl.col('Reporting Month').dt.month().cast(pl.Int32) - pl.col('Origination date').dt.month().cast(pl.Int32)) ,

    diff_months_2 = pl.date_range(pl.col("Origination date"), pl.col("Reporting Month"), "1mo").list.lengths()
).select('Excel_Reference_Age','diff_months','diff_months_1','diff_months_2')

输出：

Excel_Reference_Age  diff_months  diff_months_1  diff_months_2
i64                  i16          i32            u32
7                    7            7              8
8                    8            8              9
9                    9            9              10

结论 #

从对比结果可以看出，diff_months_1（年份差乘12加月份差）的结果和Excel参考值完全一致，说明Excel计算月份差的逻辑是仅计算年份和月份的差值，不考虑具体日期。

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