How to merge pandas on string contains?

by

New Answer

Here is one approach based on pandas/numpy.

rhs = (df1.column_common
          .apply(lambda x: df2[df2.column_common.str.find(x).ge(0)]['column_b'])
          .bfill(axis=1)
          .iloc[:, 0])

(pd.concat([df1.column_a, rhs], axis=1, ignore_index=True)
 .rename(columns={0: 'column_a', 1: 'column_b'}))

  column_a column_b
0     John    Moore
1  Michael    Cohen
2      Dan    Smith
3   George      NaN
4     Adam    Faber

Old Answer

Here’s a solution for left-join behaviour, as in it doesn’t keep column_a values that do not match any column_b values. This is slower than the above numpy/pandas solution because it uses two nested iterrows loops to build a python list.

tups = [(a1, a2) for i, (a1, b1) in df1.iterrows() 
                 for j, (a2, b2) in df2.iterrows()
        if b1 in b2]

(pd.DataFrame(tups, columns=['column_a', 'column_b'])
   .drop_duplicates('column_a')
   .reset_index(drop=True))

  column_a column_b
0     John    Moore
1  Michael    Cohen
2      Dan    Smith
3     Adam    Faber

More Related Contents:

  1. Pandas Merging 101
  2. cartesian product in pandas
  3. Performant cartesian product (CROSS JOIN) with pandas
  4. pandas three-way joining multiple dataframes on columns
  5. Python: pandas merge multiple dataframes
  6. Apply pandas function to column to create multiple new columns?
  7. Merge two dataframes by index
  8. Combine two pandas Data Frames (join on a common column)
  9. Difference(s) between merge() and concat() in pandas
  10. Pandas left outer join multiple dataframes on multiple columns
  11. Pandas Merge on Name and Closest Date
  12. merging 2 dataframes vertically [duplicate]
  13. How to do “(df1 & not df2)” dataframe merge in pandas?
  14. How to merge multiple dataframes
  15. “Anti-merge” in pandas (Python)
  16. how to merge two data frames based on particular column in pandas python?
  17. Improve Pandas Merge performance
  18. Python Pandas merge only certain columns
  19. In Pandas, how to delete rows from a Data Frame based on another Data Frame?
  20. MemoryError when I merge two Pandas data frames
  21. how to concat two data frames with different column names in pandas? – python
  22. Anti-Join Pandas
  23. Check if rows in one dataframe exist in another dataframe
  24. Merging two tables with millions of rows in Python
  25. Selecting with complex criteria from pandas.DataFrame
  26. How to merge multiple dicts with same key or different key?
  27. Running get_dummies on several DataFrame columns?
  28. Get business days between start and end date using pandas
  29. Subtract two columns in dataframe
  30. What is the fastest and most efficient way to append rows to a DataFrame?

Leave a Comment