Fuzzy Match two dataframe based on list value column

Question

I have two Dataframes that I am trying to merge by match to values in a column. The column to match are header1 in Df1 and header2 in Df2 (the value is list). I am looking for an adapted approach.

Df1:                                          Df2:
|--------------|---------------|              |--------------|------------------------|       
|      id_1    |     header1   |              |      id_2    |    header2             |
|--------------|---------------|              |--------------|------------------------|
|      112     |      item1    |              |      32      |['item1','item2',...]   |
|--------------|---------------|              |--------------|------------------------|
|      56      |      mass     | 
|--------------|---------------|

Wanted results:

Df_merged:                                       
|--------------|---------------|--------------|------------------------|       
|      id_1    |     header1   |      id_2    |    header2             |
|--------------|---------------|--------------|------------------------|
|      112     |      item1    |      32      |['item1','item2',...]   |
|--------------|---------------|--------------|------------------------|

Does anyone know how to merge these two dataframe ?. I have absolutely no idea how to handle this. Thanks a lot in advance.

score 1 · Accepted Answer · answered Jun 18 '20 at 10:05

1

Use DataFrame.explode with reassign header2 to header1 for avoid lost original column header2 and then use DataFrame.merge:

df = df1.merge(df2.assign(header1 = df2['header2']).explode('header1'), on='header1')

answered Jun 18 '20 at 10:05

jezrael

822,522
95
1,334
1,252

1

Thank you ! this is what I need. – elitebook190 Jun 18 '20 at 10:17
@elitebook190 - Thanks, glad to help. Don't forget to accept the answer, if it suits you! :) – jezrael Jun 18 '20 at 10:20
I have another question if i want to use the fuzz.ratio of the string with fuzzywuzzy ? any idea – elitebook190 Jun 18 '20 at 10:21
@elitebook190 - Then is possible check [this](https://stackoverflow.com/questions/13636848/is-it-possible-to-do-fuzzy-match-merge-with-python-pandas) – jezrael Jun 18 '20 at 10:23
OK, thank you! So Sorry Another question: how about list in header1 and list in header 2. check if the common element exists. – elitebook190 Jun 18 '20 at 12:58
@elitebook190 - Use `df = df1.assign(new= df1['header1']).explode('new').merge(df2.assign(new= df2['header2']).explode('new'), on='new')` – jezrael Jun 18 '20 at 12:59
Me again you think difflib is more useful with 0.95 ratio? – elitebook190 Jun 18 '20 at 14:06

Fuzzy Match two dataframe based on list value column

1 Answers1