Merge two data frames based on instring value

Question

I have complex text as a value in col1 in DF1 and i want it merge with clo1 and 2 from DF2. I am wondering if there a fast way to merge it with python

import pandas as pd

DF1 = pd.DataFrame({'Col1':['AB/B/C','DE/f','G/T/R']})

DF2 = pd.DataFrame({'Col1':['AB','B','C','D'],
                   'Test2':[1,2,3,4]})

print(DF1)
print(DF2)

Output:

Thank you in advance

Does this answer your question? [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — Yefet, Jun 02 '21 at 14:46

score 1 · Accepted Answer · answered Jun 02 '21 at 15:04

This should get you close to what you want. You may have to clean up some rows to get your final dataframe

dft = DF1.Col1.str.split('/').explode()
dft = DF1.merge(dft, left_index=True, right_index=True)
dft.merge(DF2, left_on='Col1_y', right_on='Col1', how='left')

   Col1_x Col1_y Col1  Test2
0  AB/B/C     AB   AB    1.0
1  AB/B/C      B    B    2.0
2  AB/B/C      C    C    3.0
3    DE/f     DE  NaN    NaN
4    DE/f      f  NaN    NaN
5   G/T/R      G  NaN    NaN
6   G/T/R      T  NaN    NaN
7   G/T/R      R  NaN    NaN

Thank you. This works!!! – user3193813 Jun 02 '21 at 18:03 — user3193813, Jun 02 '21 at 18:03

Merge two data frames based on instring value

1 Answers1