按年月匹配比较两个DataFrame数值，解决索引与维度不匹配问题

现有两个DataFrame： #

dfr1

qq
date                  
1956-01-01  685.519348
1956-01-02  731.868500
1956-01-03  510.579375
1956-01-04  412.347250
1956-01-05  358.297625
2010-12-27  3.992000
2010-12-28  1.099583
2010-12-29  104.428958
2010-12-30  4.932750
2010-12-31  101.737292
2013-12-27  7.992000
2013-12-28  105.099583
2013-12-29  104.428958
2013-12-30  102.932750
2013-12-31  101.737292

dfr2

q_t
01-01  61.629342
01-02  61.409750
01-03  61.309208
01-04  61.161462
01-05  61.020508
12-27  69.065375
12-28  68.935908
12-29  68.603104
12-30  68.474458
12-31  68.209075

需求与问题 #

需要判断dfr1中qq值是否≤dfr2对应月日的q_t值，但直接对比时因维度不同、索引不匹配报错：

dfr1.compare(dfr2)
# 报错：ValueError: Can only compare identically-labeled (both index and columns) DataFrame objects

dfr1['new'] = dfr1 < dfr2
# 报错：ValueError: Can only compare identically-labeled (both index and columns) DataFrame objects

解决方案 #

核心思路是统一两者的匹配键（提取dfr1索引的月日，和dfr2的索引对齐），具体步骤如下：

1. 确保dfr1索引为datetime类型（如果原始索引不是的话）：

import pandas as pd

# 转换索引为datetime格式
dfr1.index = pd.to_datetime(dfr1.index)

2. 提取月日并匹配dfr2的q_t值：

# 从dfr1的日期索引中提取月日字符串，格式和dfr2索引一致
dfr1['month_day'] = dfr1.index.strftime('%m-%d')

# 根据月日匹配dfr2的q_t值，添加到dfr1中
dfr1['q_t'] = dfr1['month_day'].map(dfr2['q_t'])

3. 执行比较并生成结果列：

# 判断qq是否小于等于q_t，生成布尔结果列
dfr1['qq_le_qt'] = dfr1['qq'] <= dfr1['q_t']

最终结果示例 #

处理后dfr1会新增匹配和结果列：

qq month_day      q_t  qq_le_qt
date                  
1956-01-01  685.519348    01-01  61.629342     False
1956-01-02  731.868500    01-02  61.409750     False
1956-01-03  510.579375    01-03  61.309208     False
1956-01-04  412.347250    01-04  61.161462     False
1956-01-05  358.297625    01-05  61.020508     False
2010-12-27    3.992000    12-27  69.065375      True
2010-12-28    1.099583    12-28  68.935908      True
2010-12-29  104.428958    12-29  68.603104     False
2010-12-30    4.932750    12-30  68.474458      True
2010-12-31  101.737292    12-31  68.209075     False
2013-12-27    7.992000    12-27  69.065375      True
2013-12-28  105.099583    12-28  68.935908     False
2013-12-29  104.428958    12-29  68.603104     False
2013-12-30  102.932750    12-30  68.474458     False
2013-12-31  101.737292    12-31  68.209075     False

如果不需要中间列，可最后删除：

dfr1.drop(['month_day', 'q_t'], axis=1, inplace=True)

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