Pandas : jointure sur une chaîne de caractères partielle, comme Excel VLOOKUP

Question

J'essaie d'effectuer une action en Python qui est très similaire à VLOOKUP dans Excel. Il y a eu beaucoup de questions à ce sujet sur StackOverflow mais elles sont toutes légèrement différentes de ce cas d'utilisation. J'espère que quelqu'un pourra me guider dans la bonne direction. J'ai les deux dataframes pandas suivants :

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

J'aimerais maintenant créer un nouveau cadre de données (df3) dans lequel je combine les deux sur la base des numéros de facture. Le problème est que les numéros de facture ne sont pas toujours une "correspondance complète", mais parfois une "correspondance partielle" dans df2['Ref']. Ainsi, la combinaison sur "Invoice" ne donne pas le résultat souhaité parce qu'elle ne copie pas les données pour les factures 20562 et 20563, voir ci-dessous :

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

Existe-t-il un moyen d'adhérer à une correspondance partielle ? Je sais comment "nettoyer" df2['Ref'] avec des regex, mais ce n'est pas la solution que je recherche. Avec une boucle for, j'arrive à faire un bout de chemin mais ce n'est pas très Pythonique.

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

Est-ce que str.contains() peut être utilisé d'une manière plus élégante ? Merci d'avance pour votre aide !

Answer 1

C'est une façon d'utiliser pd.Series.apply ce qui n'est qu'une boucle à peine voilée. Une "fusion partielle de chaînes" est ce que vous recherchez, mais je ne suis pas sûr qu'elle existe sous une forme vectorisée.

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

Answer 2

Voici deux solutions alternatives, toutes deux utilisant le logiciel Pandas 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')

Les deux df_m4 y df_m5 imprimera

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

Note : La solution regex présentée suppose que les numéros de facture sont toujours composés de 5 chiffres et ne retient que la première de ces occurrences. La solution 1 est plus robuste, car elle compare directement les chaînes de caractères. La solution par expressions rationnelles pourrait être améliorée pour être plus robuste si nécessaire.