代码之家 › 专栏 › 技术社区 › PythonSherpa

pandas:join on partial string match,如excel vlookup

join dataframe pandas python-3.x python

PythonSherpa · 技术社区 · 8 年前

我试图在python中执行一个与excel中的vlookup非常相似的操作。stackoverflow上有很多与此相关的问题,但它们都与这个用例略有不同。希望有人能指引我正确的方向。我有以下两个熊猫数据帧:

df1 = pd.DataFrame({'Invoice': ['20561', '20562', '20563', '20564'],
                    'Currency': ['EUR', 'EUR', 'EUR', 'USD']})
df2 = pd.DataFrame({'Ref': ['20561', 'INV20562', 'INV20563BG', '20564'],
                    'Type': ['01', '03', '04', '02'],
                    'Amount': ['150', '175', '160', '180'],
                    'Comment': ['bla', 'bla', 'bla', 'bla']})

print(df1)
    Invoice Currency
0   20561   EUR
1   20562   EUR
2   20563   EUR
3   20564   USD

print(df2)
    Ref         Type    Amount  Comment
0   20561       01      150     bla
1   INV20562    03      175     bla
2   INV20563BG  04      160     bla
3   20564       02      180     bla

现在我想创建一个新的数据框(df3),在这里我根据发票号将两者结合起来。问题是发票号码并不总是一个“完全匹配”,但有时在DF2[ReF]中是一个“部分匹配”。因此“invoice”上的联接不会给出所需的输出,因为它不会复制发票20562和20563的数据,请参见以下内容:

df3 = df1.join(df2.set_index('Ref'), on='Invoice')

print(df3)
    Invoice Currency    Type    Amount  Comment
0   20561   EUR         01       150    bla
1   20562   EUR         NaN      NaN    NaN
2   20563   EUR         NaN      NaN    NaN
3   20564   USD         02       180    bla

有办法参加部分比赛吗?我知道如何用regex“清理”df2['ref'],但这不是我想要的解决方案。有了for循环,我有很长的路要走,但这不是很蟒蛇。

df4 = df1.copy()
for i, row in df1.iterrows():
    tmp = df2[df2['Ref'].str.contains(row['Invoice'])]
    df4.loc[i, 'Amount'] = tmp['Amount'].values[0]

print(df4)
Invoice     Currency    Amount
0   20561   EUR         150
1   20562   EUR         175
2   20563   EUR         160
3   20564   USD         180

str.contains()能否以更优雅的方式使用?非常感谢您的帮助!

2 回复 | 直到 7 年前

jpp 8 年前

这是一种使用 pd.Series.apply ,这只是一个薄纱圈。一个“部分字符串合并”是你正在寻找的,我不确定它是否以矢量化的形式存在。

df4 = df1.copy()

def get_amount(x):
    return df2.loc[df2['Ref'].str.contains(x), 'Amount'].iloc[0]

df4['Amount'] = df4['Invoice'].apply(get_amount)

print(df4)

  Currency Invoice Amount
0      EUR   20561    150
1      EUR   20562    175
2      EUR   20563    160
3      USD   20564    180

Tim Skov Jacobsen 7 年前

这里有两个替代方案,都使用熊猫 merge .

# Solution 1 (checking directly if 'Invoice' string is in the 'Ref' string)
df4 = df2.copy()
df4['Invoice'] = [val for idx, val in enumerate(df1['Invoice']) if val in df2['Ref'][idx]]
df_m4 = df1.merge(df4[['Amount', 'Invoice']], on='Invoice')

# Solution 2 (regex)
import re
df5 = df2.copy()
df5['Invoice'] = [re.findall(r'(\d{5})', s)[0] for s in df2['Ref']]
df_m5 = df1.merge(df5[['Amount', 'Invoice']], on='Invoice')

两个 df_m4 和 df_m5 将打印

  Currency Invoice Amount
0      EUR   20561    150
1      EUR   20562    175
2      EUR   20563    160
3      USD   20564    180

注释 :所提供的regex解决方案假定发票号码始终为5位,并且只接受第一次出现的情况。解决方案1更健壮,因为它直接比较字符串。如果需要的话,regex解决方案可以改进为更加健壮。