title: Python & Pandas - merging csv's based on string search
body: <p>With Python I'm seeking to create a script that compares data in two different csvs. The first csv, <code>filedata.csv</code>, contains strings of filepaths containing information on user names and user ids. The second csv, <code>roster.csv</code>, contains those same fields broken up into different columns. I would like to search through the filepath string in <code>filedata.csv</code> for matches in <code>roster.csv</code>, and then write the columns from <code>roster.csv</code> into <code>filedata.csv</code>. Below are the csv structures, and the desired output.</p>
  <p><code>filedata.csv</code></p>
  <pre><code>filename
  C:\johndoe_0001_paper1.doc
  C:\janedoe_0002_paper2.doc
  C:\johnsmith_0003_paper3.pdf
  </code></pre>
  <p><code>roster.csv</code></p>
  <pre><code>first_name, last_name, user_id
  john, doe, 0001
  jane, doe, 0002
  john, smith, 0003
  </code></pre>
  <p>Desired output for <code>filedata.csv</code>:</p>
  <pre><code>filename, first_name, last_name, user_id
  C:\johndoe_0001_paper1.doc, john, doe, 0001
  C:\janedoe_0002_paper2.doc, jane, doe, 0002
  C:\johnsmith_0003_paper3.pdf, john, smith, 0003
  </code></pre>
  <p>I attempted the following code with Pandas to see if I can search through the strings in <code>filenames.csv</code> for matches from <code>roster.csv</code>:</p>
  <pre><code>import pandas as pd
  
  df = pd.read_csv('filenames.csv')
  filenames = str(df['filename'])
  
  roster = pd.read_csv('roster.csv')
  roster_last_name = str(roster['last_name'])
  roster_first_name = str(roster['first_name'])
  roster_user_id = str(roster['user_id'])
  
  print(df.loc([filenames]).str.contains([roster_last_name]))
  </code></pre>
  <p>But get the following error:</p>
  <pre><code>TypeError: unhashable type: 'list'
  </code></pre>
  <p>Likewise I've tried something simpler, but with no success, as &quot;False&quot; is always returned:</p>
  <pre><code>if roster_last_name in filenames:
      print(&quot;True&quot;)
  else:
      print(&quot;False&quot;)
  </code></pre>
  <p>I'm sure I'm missing something simple, but unsure how to proceed. All suggestions are greatly appreciated.</p>