title: Pandas groupby: change values in one column based on values in another column
body: <p>I'll try to explain my problem as best as possible. But I'm new to Pandas, so please bear with me. I have a Pandas dataframe <code>df</code>:</p>
  
  <pre><code>    Random_ID   Seq_ID  Type    Seq Token
  0   8             1     User    First
  1   8             2     Agent   Second
  2   8             3     User    Second
  3   8             4     User    Second
  4   8             5     Agent   Second
  5   13            1     User    First
  6   13            2     Agent   Second
  7   13            3     User    Second
  8   13            4     Agent   Second
  9   13            5     User    Second
  10  13            6     Agent   Second
  11  13            7     User    Second
  12  13            8     Agent   Second
  13  13            9     User    Second
  14  13            10    Agent   Second
  </code></pre>
  
  <p>I have been trying to change the values of <code>Seq Token</code>(User_First, Agent_Last...) based on the occurrence of <code>User</code> and <code>Agent</code> in <code>Type</code> in each group of <code>df.groupby('Random_ID')</code>. To illustrate further, take the last row of each group:</p>
  
  <pre><code>grouped = df.groupby('Random ID').last()
  </code></pre>
  
  <p>which gives:</p>
  
  <pre><code>          Seq_ID       Type     Seq Token
  Random_ID           
  8          5           Agent    Second
  13         10          Agent    Second
  </code></pre>
  
  <p>Here, if <code>Type=Agent</code>, then <code>Seq token</code> should be <code>Agent_Final</code>. Then, the <code>df</code> should look like:</p>
  
  <pre><code>    Random_ID   Seq_ID  Type    Seq Token
  0   8             1     User    First
  1   8             2     Agent   Second
  2   8             3     User    Second
  3   8             4     User    Second
  4   8             5     Agent   Agent_Final
  5   13            1     User    First
  6   13            2     Agent   Second
  7   13            3     User    Second
  8   13            4     Agent   Second
  9   13            5     User    Second
  10  13            6     Agent   Second
  11  13            7     User    Second
  12  13            8     Agent   Second
  13  13            9     User    Second
  14  13            10    Agent   Agent_Final
  </code></pre>
  
  <p>I've tried the following:</p>
  
  <pre><code>grouped = df.groupby('Random_ID', as_index=False).last()['Type']
  for i in grouped:
      if i == 'Agent':
          df['Seq Token'] =  'Agent_Final'
  </code></pre>
  
  <p>but this assigns all items in <code>Seq token</code> as <code>'Agent_Final'</code>:</p>
  
  <pre><code>     Random_ID  Seq_ID  Type    Seq Token
  0       8         1     User    Agent_Final
  1       8         2     Agent   Agent_Final
  2       8         3     User    Agent_Final
  3       8         4     User    Agent_Final
  4       8         5     Agent   Agent_Final
  </code></pre>
  
  <p>I read about <code>groupby</code> and it creates a copy of the original <code>df</code> and does not allow changing it unless one explicitly changes a <code>df[column]</code>. I hope this makes sense.</p>
  
  <p>I've managed to set the first row "group values" to <code>"First"</code> using <code>np.where()</code> like this:</p>
  
  <pre><code>df['Seq Token'] = np.where((np.logical_and(np.equal(df['Type'],'User'), np.equal(df['Seq_ID'],1))), 'First', 'Second')
  </code></pre>
  
  <p>You can see that I've implemented this in the <code>df</code> already. Note that I used the <code>Seq_ID</code> value to get the first row in the group. </p>
  
  <p>If there is a way to chain <code>np.where()</code> in such as way that I can assign <code>Seq Token</code> as <code>User_First</code>(same as First), <code>User_Middle</code>(if <code>Type=User</code> occurs in the middle), <code>Agent_Middle</code>(if <code>Type=Agent</code> occurs in the middle), <code>Agent_Last</code>(as explained above: if Agent is last), then it would be the most ideal solution. However, any other solutions are welcome too.</p>
  
  <p>Thanks in advance!</p>