CODEWITHSUNDEEP
pythonpandasdataframe
ah bon
ah bon

Reputation: 10011

Place values from another dataframe if aother column is in another column in Python

Say I have a dataframe A as follows:

id        full_name
1           ABC Ltd.
2           NY BCD Ltd.
3           SH ZY Ltd.
4           Soho Company

also another dataframe B:

name                     id
 ABC
 NY BCD
 SH ZY
 Soho

If name in B is in full_name, then I I want to place the values of id from dataframe A to id of B. How can I do that in Python? Thanks.

Upvotes: 0

Views: 151

Answers (1)

pfreiberger
pfreiberger

Reputation: 90

How about this soultion with a fuzzy lookup?

import pandas as pd
import difflib 
df1 = pd.DataFrame({"id": [1, 2, 3, 4], "full_name": ["ABC Ltd.", "NY BCD Ltd.", "SH ZY Ltd.", "Soho Company"]})

df2 = pd.DataFrame({"name": ["ABC", "SH ZY", "NY BCD", "Soho"]})

df2["full_name"] = df2["name"].map(lambda x: difflib.get_close_matches(x, df1["full_name"], cutoff=0.5)[0])

df2 = pd.merge(df2, df1, how="left", on="full_name")

THe lookup looks for close matches and returns the first value, the mathc is only given if at least 50% of the string matches.

The end_result for df2 would look something like this:

     name     full_name  id
0     ABC      ABC Ltd.   1
1   SH ZY    SH ZY Ltd.   3
2  NY BCD   NY BCD Ltd.   2
3    Soho  Soho Company   4

Upvotes: 1

Related Questions