title: Transferring columns to a new df with exploded cells and repeated title name
body: <p>I have a dataframe of <strong>journal title</strong> and <strong>era_subject</strong> (discipline of that journal) that is not tidy, containing multiple values in the same cell of the <code>era_subjects</code> Series.</p>
  <pre><code>df = pd.DataFrame({
  'title':['Veterinary pathology', 'Clothing and textiles research journal'],
  'era_subjects':[&quot;[['07', 'Agricultural and Veterinary Sciences'], ['04', 'Fisheries Sciences'], ['0707', 'Veterinary Sciences']]&quot;,&quot;[['1203', 'Design Practice and Management'], ['12', 'Built Environment and Design']]&quot;],
  'cpu_rank': ['1', '2'],
  'subscribed': ['True', 'False'],
  'downloads': ['800', '550']})
  </code></pre>
  <p><a href="https://i.stack.imgur.com/xxfCW.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xxfCW.jpg" alt="df with multiple values in Series era_subjects" /></a></p>
  <p>I wrote a function to extract and return only the broadest-level, two-digit subject strings from <code>era_subjects</code> (there can be multiple).  The result of my function on <code>row 0</code> for example is a cell containing:</p>
  <pre><code>['Agricultural and Veterinary Sciences', 'Fisheries Sciences']
  </code></pre>
  <p>I then used the technique outlined in the Medium article <a href="https://sureshssarda.medium.com/pandas-splitting-exploding-a-column-into-multiple-rows-b1b1d59ea12e" rel="nofollow noreferrer" title="Medium: Pandas: Splitting (Exploding) a column into multiple rows">here</a> to explode that resulting cell into a <strong>new_df</strong> having multiple rows with the journal name repeated when necessary:</p>
  <p><a href="https://i.stack.imgur.com/JvEkU.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JvEkU.jpg" alt="df with columns title and era_split. values in column title are repeated" /></a></p>
  <p>Now, I want to supplement this <strong>new_df</strong> with information from the original <strong>df</strong>, such as <code>subscribed</code> for a journal title.  I can't use <strong>new_df</strong> <code>title</code> as the index to look up since it's repeated (rows 0 and 1, for example).</p>
  <p>After a lot of trial and error, and a walk down a dead end for the <code>join</code> and <code>merge</code> methods which I couldn't figure out, I've done this:</p>
  <pre><code>for i in df.set_index('title').index:
      temp_sub = df.set_index('title').loc[i, 'subscribed']
      
      temp_filt = (new_df['title'] == i)
      new_df.loc[temp_filt, 'subscribed'] = temp_sub
  </code></pre>
  <p>Use title in the original <strong>df</strong> (which will be unique for each row) to hold the subscribed status of that title, then filter the <strong>new_df</strong> on that title and set the subscribed status.</p>
  <p>Questions:</p>
  <ol>
  <li>I'm sure there is a better way to do this. How else could I have
  done it?</li>
  <li><code>subscribed</code> is one of seven or so columns I want to bring over
  from the original <strong>df</strong> based on the journal title.  Can I do that efficiently, or do I have
  to do seven separate temp variables and assignments?</li>
  </ol>
  <h2>Edit: Adding desired final new_df</h2>
  <pre><code>new_df = pd.DataFrame({
  'title':['Veterinary pathology', 'Veterinary pathology', 'Clothing and textiles research journal'],
  'era_subjects':[&quot;Agricultural and Veterinary Sciences&quot;, 'Fisheries Sciences', 'Built Environment and Design'],
  'cpu_rank': ['1', '1', '2'],
  'subscribed': ['True', 'True', 'False'],
  'downloads': ['800', '800', '550']})
  </code></pre>
  <p><a href="https://i.stack.imgur.com/vUGQ5.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vUGQ5.jpg" alt="enter image description here" /></a></p>