如何用双层索引DataFrame为另一DataFrame填充最近批次码

现有数据集 #

1. 批次码记录表（bcr_df） #

以date（批次更新日期）和tank（水箱编号）为双层索引，存储各水箱批次码的不定期更新记录，样例：

batchcode
date    tank    
2016-01-02  LD  TRE-20160102-A-1
            LA  TRE-20160102-B-1
2016-01-09  T8  TRE-20160109-C-1
            LB  TRE-20160109-C-1
2016-01-25  LA  TRE-20160125-D-1
2016-01-27  LD  TRE-20160102-A-2
            LC  TRE-20160102-A-3
2016-02-02  LD  TRE-20160102-E-1
            LA  TRE-20160125-D-2
            LB  TRE-20160109-C-2

2. 日常活动记录表（daily_df） #

记录投喂、温度观测等日常数据，需新增batchcode列，规则为：匹配tank后，取该日期之前最近的历史批次码，样例：

Date    Tank    Property          Value   
0   2015-12-06  LC  Green Water (g)  50.0    
1   2015-12-07  LC  Green Water (g)  50.0    
2   2015-12-08  LC  Green Water (g)  50.0    
3   2015-12-09  LC  Green Water (g)  50.0    
4   2015-12-10  LC  Green Water (g)  50.0    

问题痛点 #

逐行匹配效率极低；尝试get_indexer无法适配双层索引；仅以date为索引会触发非唯一日期警告。

高效解决方案 #

步骤1：预处理批次码表（核心前提） #

确保双层索引单调有序，这是实现高效向前填充的必要条件：

bcr_file = f'data/batch_code_records.csv'
bcr_df = pd.read_csv(bcr_file, dtype='str')
bcr_df['date'] = pd.to_datetime(bcr_df['change_date'])
# 移除无关列
bcr_df.drop(['unique_id', 'species', 'parent_indicator',
       'change_date', 'entered_by', 'fish_class', 'origin_parents_wild_breed',
       'change_reason_1', 'change_reason_2', 'batch_new', 'comments',
       'fish_count', 'source_population_or_wild', 'source_batch_1',
       'source_batch_2', 'batch_previous_1', 'batch_previous_2', 'tank_from_1',
       'tank_from_2'], axis=1, inplace=True)
# 设置双层索引并排序
bcr_df.set_index(['date', 'tank'], inplace=True)
bcr_df = bcr_df.sort_index()

步骤2：标准化日常记录表的索引格式 #

统一列名并构造与批次码表一致的双层索引，确保匹配维度对齐：

# 统一列名，避免索引匹配出错
daily_df = daily_df.rename(columns={'Date': 'date', 'Tank': 'tank'})
# 构造临时双层索引用于匹配
daily_df_temp = daily_df.set_index(['date', 'tank'])

步骤3：批量向前匹配批次码 #

利用reindex+method='pad'实现向量化匹配，无需逐行循环：

# 匹配最近的历史批次码，无匹配则返回NaN
daily_df['batchcode'] = bcr_df['batchcode'].reindex(daily_df_temp.index, method='pad')

可选：处理无匹配的边界情况 #

对于日期早于该水箱首次批次记录的条目，可填充默认值：

daily_df['batchcode'] = daily_df['batchcode'].fillna('UNKNOWN')

关键疑问解答 #

1. 是否需要将tank转为列？
   不需要。双层索引是同时匹配日期和水箱的最优结构，只要保证索引有序，就能高效完成批量匹配。
2. 如何在双层索引中查找最近历史日期？
   通过sort_index确保索引有序后，reindex结合method='pad'会自动在双层索引的维度内，为每个(date, tank)组合找到最近的历史记录。

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