title: Create conditional column using GroupBy
body: <p>I want to create a new column in my data frame based on grouping variable in a column in the data frame and then check for condition in another column in the data frame.</p>
  
  <p>I have tried to use np.where with pandas pd.groupby to create a Status column in the data frame where I am checking if the next value in the column is greater than the current one based on grouping each of the Sensor_ID's and based on this I am trying to assign if the Status is set to reset or not_reset, however I haven't been successful with the code.</p>
  
  <pre><code>import pandas as pd
  df = pd.DataFrame(data = {'Sensor_ID':['A1', 'A1', 'A1', 'A2','A2', 'A2', 'A2', 'A3', 'A3', 'A3', 'A3', 'A3'], 'Reading':[81, 83.5, 87, 90, 81, 82, 85, 78, 79, 78, 80, 78]})
  df
  
     Sensor_ID  Reading
  0         A1     81.0
  1         A1     83.5
  2         A1     87.0
  3         A2     90.0
  4         A2     81.0
  5         A2     82.0
  6         A2     85.0
  7         A3     78.0
  8         A3     79.0
  9         A3     78.0
  10        A3     80.0
  11        A3     78.0
  </code></pre>
  
  <p>I want to create the following condition below using np.where, but I want to use the Sensor_ID as a grouping variable.</p>
  
  <pre><code>df['Status'] = np.where(df.Reading.shift(-1) &gt; df.Reading, 'not_reset', 'reset')
  </code></pre>
  
  <p>I used np.where with groupby and transform</p>
  
  <pre><code>df['Status'] = np.where(df.groupby('Sensor_ID')['Reading'].transform(df['Reading'].shift(-1) &gt; df['Reading'], 'not_reset', 'reset'))
  
  TypeError: 'Series' objects are mutable, thus they cannot be hashed
  </code></pre>
  
  <p>I have also tried to use apply and transform with groupby but I get error:</p>
  
  <pre><code>df['Status'] = df.groupby('Sensor_ID').apply(lambda row: 'not_reset' if row['Reading'].shift(-1) &gt; row['Reading'] else 'reset')
  
  ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). --&gt; As its comparing the whole series.
  </code></pre>
  
  <p>and</p>
  
  <pre><code>df['Status'] = df.groupby('Sensor_ID').transform(df['Reading'].shift(-1) &gt; df['Reading'], 'not_reset', 'reset')
  TypeError: 'Series' objects are mutable, thus they cannot be hashed
  </code></pre>
  
  <p>Expected output:</p>
  
  <pre><code>       Sensor_ID  Reading     Status
  0             A1     81.0  not_reset
  1             A1     83.5  not_reset
  2             A1     87.0  not_reset
  3             A2     90.0  not_reset
  4             A2     81.0      reset
  5             A2     82.0  not_reset
  6             A2     85.0  not_reset
  7             A3     78.0  not_reset
  8             A3     79.0  not_reset
  9             A3     78.0      reset
  10            A3     80.0  not_reset
  11            A3     78.0      reset
  </code></pre>