title: Adding a calculated column of an equation using pandas groupby()
body: <p>I'm new to working with pandas and I am trying to add a column with groupby() based off of a Spotfire calculated column formula </p>
  
  <p>Suppose I had a table with the following data (df1):</p>
  
  <pre><code>'Well ID','Assay','Source','Treat','BkgrdSub Fluorescence','Calced'
  'A1',4,'Source 1','OPA',-215.75,0.035583351
  'A2',4,'Source 1','OPA',-160.75,0.130472288
  'A3',4,'Source 1','OPA',343.25,1
  'H10',6,'Source 1','OPP',9896,1
  'H11',6,'Source 1','OPP',9892,0.999605226
  'H12',6,'Source 1','CN',-1,1
  'A1',4,'Source 2','OPA',-170,0.03682641
  'A2',4,'Source 2','OPA',-86,0.083431583
  'A3',4,'Source 2','OPA',1566,1
  'H10',6,'Source 2','ZI',4885,0.809271732
  'H11',6,'Source 2','ZI',6092,1
  'H12',6,'Source 2','CN',78,1
  'A1',4,'Source 3','OPA',-114.5,0.037329147
  'A2',4,'Source 3','OPA',-114.5,0.037329147
  'A3',4,'Source 3','OPA',3028.5,1
  'H10',6,'Source 3','ZIII',4245.375,0.85305734
  'H11',6,'Source 3','ZIII',5017.375,1
  'H12',6,'Source 3','CN',20.375,1
  'A1',4,'Source 4','OPA',-183.375,0.017731683
  'A2',4,'Source 4','OPA',-102.375,0.044831047
  'A3',4,'Source 4','OPA',2752.625,1
  'H10',6,'Source 4','ZIIII',2635.75,0.697943562
  'H11',6,'Source 4','ZIIII',3878.75,1
  'H12',6,'Source 4','CN',-10.25,1
  'A1',4,'Source 5','OPA',-236.375,0
  'A2',4,'Source 5','OPA',-199.375,0.028094153
  'A3',4,'Source 5','OPA',1080.625,1
  'H10',6,'Source 5','ZV',3489,0.952202946
  'H11',6,'Source 5','ZV',3676,1
  'H12',6,'Source 5','CN',31,1
  'A1',4,'Source 6','OPA',-221.375,0.008870491
  'A2',4,'Source 6','OPA',-150.375,0.050857481
  'A3',4,'Source 6','OPA',1454.625,1
  'H10',6,'Source 6','ZVI',2224.375,1
  'H11',6,'Source 6','ZVI',1418.375,0.672457584
  'H12',6,'Source 6','CN',716.375,1
  </code></pre>
  
  <p>I want to be able to add one calculated column that is defined by the Spotfire equation:</p>
  
  <pre><code>([BkgrdSub Fluorescence] - Min([BkgrdSub Fluorescence])) / Max([BkgrdSub Fluorescence] - Min([BkgrdSub Fluorescence])) OVER ([Treat],[Source],[Assay])
  </code></pre>
  
  <p>I was building the script one step at a time and I got to the point where I tried to run this with groupby():</p>
  
  <pre><code>import pandas as pd
  
  df1.insert(5,"Scaled BckgrdSub Fluorescence min","")
  df1['Scaled BckgrdSub Fluorescence min'] = df1.groupby(['Treat','Source','Assay'])['BkgrdSub Fluorescence'].transform('min')
  df1.insert(6,"Scaled BckgrdSub Fluorescence eq","")
  df1['Scaled BckgrdSub Fluorescence eq'] = df1[['BkgrdSub Fluorescence'] - ['Scaled BckgrdSub Fluorescence min']].groupby(df1['Treat'],df1['Source'],df1['Assay']).transform('max')
  </code></pre>
  
  <p>But I get the error:</p>
  
  <pre><code>TypeError: unsupported operand type(s) for -: 'list' and 'list'
  </code></pre>
  
  <p>Which I understand means that I can't subtract a list from a list. So obviously the syntax doesn't support equations within the groupby() function. </p>
  
  <p>I also tried to avoid this syntax error by avoiding using groupby() with 'Scaled BckgrdSub Fluorescence' being the desired outcome column:</p>
  
  <pre><code>df1.insert(5,"Scaled BckgrdSub Fluorescence min","")
  df1['Scaled BckgrdSub Fluorescence min'] = df1.groupby(['Treat','Source','Assay'])['BkgrdSub Fluorescence'].transform('min')
  df1.insert(6,"Scaled BckgrdSub Fluorescence eq","")
  df1['Scaled BckgrdSub Fluorescence eq'] = df1['BkgrdSub Fluorescence'] - df1['Scaled BckgrdSub Fluorescence min']
  df1.insert(7,"Scaled BckgrdSub Fluorescence max","")
  df1['Scaled BckgrdSub Fluorescence max'] = df1.groupby(['Treat','Source','Assay'])['Scaled BckgrdSub Fluorescence eq'].transform('max')
  df1.insert(8,"Scaled BckgrdSub Fluorescence","")
  df1['Scaled BckgrdSub Fluorescence'] = df1['Scaled BckgrdSub Fluorescence eq'] / df1['Scaled BckgrdSub Fluorescence max']
  </code></pre>
  
  <p>However, this isn't the same outcome as the calculated column you get in Spotfire.</p>
  
  <p>The expected output, taken from Spotfire, for the calculated column is the already shown in the 'Calced' column.</p>
  
  <p>So my question is, is there a simple way to add the column that I want with the groupby() function in a few lines while remaining accurate?</p>