title: Match two data frames by substring in python
body: <p>I have two big data frames (1000s of rows), and I need to match them by substring, for example:</p>
  <p>df1:</p>
  <pre><code>Id    Title
  1     The house of pump
  2     Where is Andijan
  3     The Joker
  4     Good bars in Andijan
  5     What a beautiful house
  </code></pre>
  <p>df2:</p>
  <pre><code>Keyword
  house
  andijan
  joker
  </code></pre>
  <p>And the expected output is:</p>
  <pre><code>Id    Title                    Keyword
  1     The house of pump        house
  2     Where is Andijan         andijan
  3     The Joker                joker
  4     Good bars in Andijan     andijan
  5     What a beautiful house   house
  </code></pre>
  <p>Now, I've sort of written a very non-efficient way to match it, but for the actual size of the data frames it runs for an extremely long time:</p>
  <pre><code>for keyword in df2.to_dict(orient='records'):
      df1['keyword'] = np.where(creative_df['title'].str.contains(keyword['keyword']), keyword['keyword'], df1['keyword'])
  </code></pre>
  <p>Now, I'm sure there's a more pandas-friendy and efficient way to do the same, and also have it run in a reasonable time.</p>