python - 如何将两个数据帧与 'wildcards' 合并？

Question

我有一个像这样的简单数据框:

   p     b
0  a   buy
1  b   buy
2  a  sell
3  b  sell

和这样的查找表:

   p     b    v
0  a   buy  123
1  a  sell  456
2  a     *  888
4  b     *  789

我如何合并(连接)两个数据帧，但尊重 b 列中的“通配符”，即预期结果是:

   p     b    v
0  a   buy  123
1  b   buy  789
2  a  sell  456
3  b  sell  789

我能想到的最好的就是这个，但它非常丑陋和冗长:

data = pd.DataFrame([
        ['a', 'buy'],
        ['b', 'buy'],         
        ['a', 'sell'],
        ['b', 'sell'],              
    ], columns = ['p', 'b'])
lookup = pd.DataFrame([
        ['a', 'buy', 123],
        ['a', 'sell', 456],
        ['a', '*', 888],
        ['b', '*', 789],        
], columns = ['p','b', 'v'])

x = data.reset_index()
y1 = pd.merge(x, lookup, on=['p', 'b'], how='left').set_index('index')
y2 = pd.merge(x[y1['v'].isnull()], lookup, on=['p'], how='left' ).set_index('index')
data['v'] = y1['v'].fillna(y2['v'])

有没有更聪明的方法？

Answer 1

我认为更简洁的方法是先清除通配符:

In [11]: wildcards = lookup[lookup["b"] == "*"]

In [12]: wildcards.pop("b")  # ditch the * column, it'll confuse the later merge

现在您可以将两个合并(不需要 set_index)与 update 结合起来:

In [13]: res = df.merge(lookup, how="left")

In [14]: res
Out[14]:
   p     b      v
0  a   buy  123.0
1  b   buy    NaN
2  a  sell  456.0
3  b  sell    NaN

In [15]: res.update(df.merge(wildcards, how="left"), overwrite=False)

In [16]: res
Out[16]:
   p     b      v
0  a   buy  123.0
1  b   buy  789.0
2  a  sell  456.0
3  b  sell  789.0