pandas - Compare two Dataframes based on column value(String, Substring) and update another column value

Question

Dataframes df1,df2, where df1 Name column has a partial matching string on df2 Name column value. On the partial match of name column values, then compare the price column value of both data frames and if it is the same price then update column(Flag) in df1 as 'Delete'

df1

Name	Price	Flag
VENTILLA HOME FARR	662324.21	Delete
VENTILLA HOME FARR	-277961.62
VENTILLA HOME FARR	776011.5
VARAMANT METRO PLANET	662324.21
VARAMANT METRO PLANET	55555.21	Delete
VARAMANT METRO PLANET	267117.5499
FANTHOM STREET LLB	83265.2799
FANTHOM STREET LLB	-444452.96	Delete
FANTHOM STREET LLB	267117.5499

question from:https://stackoverflow.com/questions/65926090/compare-two-dataframes-based-on-column-valuestring-substring-and-update-anoth

Answer 1

the solution I share here for this problem is based on the set, so if the Name of dataframe 1 is at least sharing one word with the Name of dataframe 2, and also their Price is equal then we edit the Flag column in the dataframe 1 by "Delete" otherwise we made it as "None"

This The Code Source :

def check(row):
  df1_Name = set(map(lambda word: word.lower(),row.Name.split(' ')))
  df1_price = row.Price
  df1_flag = row.Flag

  for df2_Name, df2_Price in df2[['Name', 'Price']].values:
    df2_Name = set(map(lambda word: word.lower(),df2_Name.split(' ')))
    if len(df1_Name.intersection(df2_Name)) > 1 and df1_price  == df2_Price:
      return 'Delete'
  return '' 

df1["Flag"]= df1.apply(checkMatch,axis=1)