当两个DataFrame的两列值匹配时填充数据的实现方法

需求说明 #

当两个DataFrame的SITE和week列值同时匹配时，使用df1_small中的LAL值，填充df2_large中对应的空LAL列。

示例输入数据 #

df1_small（匹配规则数据源）

week    SITE          LAL
0   1       BARTON CHAPEL 1.1
1   2       BARTON CHAPEL 1.8
2   3       BARTON CHAPEL 1.4
3   1       PENASCAL I    1.7
4   2       PENASCAL I    2.9
5   3       PENASCAL I    2.2

df2_large（待填充目标数据）

SITE          hour day week POWER  LAL
0   BARTON CHAPEL 1    1   1    54    
1   BARTON CHAPEL 2    1   1    32    
2   BARTON CHAPEL 3    1   1    56    
3   BARTON CHAPEL 4    1   1    81    
4   BARTON CHAPEL 5    1   1    90    
5   BARTON CHAPEL 6    1   1    12    
6   BARTON CHAPEL 7    1   1    10    
7   BARTON CHAPEL 8    1   1    73    
8   BARTON CHAPEL 9    1   1    55    
9   BARTON CHAPEL 10   1   1    66    
10  PENASCAL I     1    1   1    39    
11  PENASCAL I     2    1   1    90    
12  PENASCAL I     3    1   1    13    
13  PENASCAL I     4    1   1    44    
14  PENASCAL I     5    1   1    51    

期望输出结果 #

SITE          hour day week POWER  LAL
0   BARTON CHAPEL 1    1   1    54    1.1
1   BARTON CHAPEL 2    1   1    32    1.1
2   BARTON CHAPEL 3    1   1    56    1.1
3   BARTON CHAPEL 4    1   1    81    1.1
4   BARTON CHAPEL 5    1   1    90    1.1
5   BARTON CHAPEL 6    1   1    12    1.1
6   BARTON CHAPEL 7    1   1    10    1.1
7   BARTON CHAPEL 8    1   1    73    1.1
8   BARTON CHAPEL 9    1   1    55    1.1
9   BARTON CHAPEL 10   1   1    66    1.1
10  PENASCAL I     1    1   1    39    1.7
11  PENASCAL I     2    1   1    90    1.7
12  PENASCAL I     1    1   2    13    2.9
13  PENASCAL I     2    1   2    44    2.9
14  PENASCAL I     3    1   2    51    2.9

解决方案 #

方法1：构建映射字典填充（适合小数据集） #

先将df1_small转换成以(SITE, week)为键、LAL为值的字典，再逐行匹配填充：

import pandas as pd

# 构建映射字典
lal_map = df1_small.set_index(['SITE', 'week'])['LAL'].to_dict()

# 填充df2_large的LAL列
df2_large['LAL'] = df2_large.apply(lambda row: lal_map.get((row['SITE'], row['week'])), axis=1)

方法2：左连接合并填充（适合大数据集，效率更高） #

利用pandas的merge做左连接，直接匹配对应值填充：

import pandas as pd

# 左连接保留df2_large所有行，匹配SITE和week列
merged = df2_large.merge(df1_small, on=['SITE', 'week'], how='left', suffixes=('', '_source'))

# 用匹配到的LAL值替换原空列
df2_large['LAL'] = merged['LAL_source']

两种方法都能实现需求，其中merge方法的执行效率远高于apply，数据量越大优势越明显。

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