How to get values from table if they contains substrings from values in another table via pandas (da-CodePudding

dear friends) is there any right way to do that in pandas?

first table:

N   COLOR
1   117116C25
2   117116C28
3   JP-1989-1-9

second table:

ART
SH034-117116C28
SH091B-117116C28
SH091B-JP-1989-1-9
SH077-117116c28

to get dataframe like that (only art with substring from first table)?:

N   COLOR   ART
2   117116C28   SH034-117116C28
2   117116C28   SH091B-117116C28
2   117116C28   SH077-117116c28
3   JP-1989-1-9 SH091B-JP-1989-1-9

CodePudding user response：

Assuming the dataframes are named df1 and df2, you can extract the part and merge:

df1.merge(df2, left_on='COLOR',
          right_on=df2['ART'].str.split('-', 1).str[1].str.upper(),
          how='right')

output:

   N        COLOR                 ART
0  2    117116C28     SH034-117116C28
1  2    117116C28    SH091B-117116C28
2  3  JP-1989-1-9  SH091B-JP-1989-1-9
3  2    117116C28     SH077-117116c28