title: python pandas how to merge/join two tables based on substring?
body: <p>Let's say I have two dataframes, and the column names for both are:</p>
  
  <pre><code>table 1 columns:
  [ShipNumber, TrackNumber, Comment, ShipDate, Quantity, Weight]
  table 2 columns:
  [ShipNumber, TrackNumber, AmountReceived]
  </code></pre>
  
  <p>I want to merge the two tables when either 'ShipNumber' or 'TrackNumber' from table 2 can be found in 'Comment' from table 1.</p>
  
  <p>Also, I'll explain why </p>
  
  <pre><code>merged = pd.merge(df1,df2,how='left',left_on='Comment',right_on='ShipNumber')
  </code></pre>
  
  <p>does not work in this case. </p>
  
  <p>"Comment" column is a block of texts that can contain anything, so I cannot do an exact match like tab2.ShipNumber == tab1.Comment, because tab2.ShipNumber or tab2.TrackNumber can be found as a substring in tab1.Comment. </p>
  
  <p>The desired output table should have all the unique columns from two tables:</p>
  
  <pre><code>output table column names:
  [ShipNumber, TrackNumber, Comment, ShipDate, Quantity, Weight, AmountReceived]
  </code></pre>
  
  <p>I hope my question makes sense... 
  Any help is really really appreciated!</p>
  
  <p><strong>note</strong></p>
  
  <p>The ultimate goal is <strong>to merge two sets with (shipnumber==shipnumber |tracknumber == tracknumber | shipnumber in comments | tracknumber in comments)</strong>, but I've created two subsets for the first two conditions, and now I'm working on the 3rd and 4th conditions.</p>