title: Pandas way to make new column that index matches a key for all duplicate values in another column
body: <p>What is the Pandas way to <em>make a new column</em> that finds and return any duplicate values in another column based on a single key?</p>
  
  <p>The raw data is like this. Call it <code>MaterialsReport_df</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/7iNi2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7iNi2.png" alt="enter image description here"></a></p>
  
  <p>Notice how my tag appears twice for the last two rows. I want to concatenate their sites in my final report. Generally, I also need to return whatever the site(s) are to match the tags which <em>already exist</em> in my final report called <code>BoxTrackingReport_df</code>, in the target column <code>All Box Tracked Sites</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/K36DM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/K36DM.png" alt="enter image description here"></a></p>
  
  <p>I'm currently creating a dictionary like <code>{tag, [site 1, site 2, site 3]}</code> and then iterating through the dictionary to return all possible sites:</p>
  
  <pre class="lang-py prettyprint-override"><code>BoxTrackingReport_df['All Box Tracked Sites'] = [site_dict.get(x) for x in BoxTrackingReport_df['Tag']]
  </code></pre>
  
  <p>I thought there would be a simpler way because my dictionary code is torturous. My attempt at a Pandas way:</p>
  
  <pre class="lang-py prettyprint-override"><code>BoxTrackingReport_df['All Box Tracked Sites'] = [x + "  " for x in MaterialsReport_df.loc[BoxTrackingReport_df['Tag'] == MaterialsReport_df['Tag'], ['Scoring Site Including X']]]
  </code></pre>
  
  <p>The attempt gave me this error:</p>
  
  <pre class="lang-py prettyprint-override"><code>ValueError: Can only compare identically-labeled Series objects
  </code></pre>
  
  <p>[Edited for detail]</p>