title: Sum unique values by group with pandas
body: <p>I got a dataframe like this:</p>
  
  <pre><code>data = {
      'YEAR' :    [2018,2018,2017,2018,2018,2018],
      'SEASON':   ['SPRING', 'SPRING', 'WINTER', 'SPRING', 'SPRING', 'SPRING'],
      'CODE':     ['A', 'A', 'A', 'B', 'C', 'D'],
      'BUDGET':   [500,200,300,4000,700,0],
      'QUANTITY': [1000,1000,1000,2000,300,4000]
  }
  
  df = pd.DataFrame(data)
  
  '''
     BUDGET CODE  QUANTITY  SEASON  YEAR
  0     500    A      1000  SPRING  2018
  1     200    A      1000  SPRING  2018
  2     300    A      1000  WINTER  2017
  3    4000    B      2000  SPRING  2018
  4     700    C       300  SPRING  2018
  5       0    D      4000  SPRING  2018
  '''
  </code></pre>
  
  <p>For each <strong>CODE</strong> I got its correct <strong>BUDGET</strong> quantity, <em>but</em> unfortunately on the <strong>QUANTITY</strong> column I got the total quantity for that code within every [Year, Season]. </p>
  
  <p>I'm working on a function to aggregate my dataframe at different levels given as input: for example I'm giving the function a list like </p>
  
  <pre><code>my_list = [
    ['YEAR']
    ['YEAR', 'SEASON']
  ]
  </code></pre>
  
  <p>and the function will output a series of dataframes grouped by each sublist.</p>
  
  <p>The problem here is I can aggregate <strong>CODE</strong> with <em>pd.Series.nunique</em> and I can <em>sum</em> the <strong>BUDGET</strong> column, but if I <em>sum</em> also the <strong>QUANTITY</strong> column I will obviously sum up more than I want. What I would need is some sort of <em>sumUniques</em> function by uniques <strong>YEAR</strong>, <strong>SEASON</strong>, <strong>CODE</strong>.</p>
  
  <pre><code>def sumUniques(x):
      return '???'
  
  print(df.groupby(['YEAR', 'SEASON']).agg({
      'CODE': pd.Series.nunique,
      'BUDGET': sum,
      'QUANTITY' : sumUniques
  }))
  
  '''
               CODE  BUDGET QUANTITY
  YEAR SEASON                       
  2017 WINTER     1     300      ???
  2018 SPRING     4    5400      ???
  
  
  --&gt; EXPECTED RESULT:
               CODE  BUDGET QUANTITY
  YEAR SEASON                       
  2017 WINTER     1     300      1000
  2018 SPRING     4    5400      7300
  
  '''
  </code></pre>
  
  <p>I'm asking myself which could be the best way to achieve this and I came upon <a href="https://stackoverflow.com/a/46059606/7330125" title="Zero&#39;s answer">Zero's answer to <em>"Pandas: sum values from column to unique values"</em></a>: I've tried it out, by it seems like either I'm not applying it correctly or that does not apply my problem, as it raises a <strong>key error</strong>:</p>
  
  <pre><code>print(df.groupby(['YEAR', 'SEASON']).agg({
      'CODE': pd.Series.nunique,
      'BUDGET': sum,
      'QUANTITY' : lambda x: x.groupby('CODE').QUANTITY.first().sum()
  }))
  
  '''
  KeyError: 'CODE'
  '''
  </code></pre>
  
  <p>I wonder what's the best way to make this work, hope this will be of help for others too!</p>