title: Combining Pandas GroupBy and transform with multiindex dataframes
body: <p>I am fairly new to Python Pandas and I have problems getting the Pandas <code>GroupBy</code> combined with <code>transform</code> to act the way I want to. I have been unable to find an answer posted already, but I may have missed something.</p>
  
  <p>I have a DataFrame with a large number of entries, structured like follows:</p>
  
  <pre><code>GLT_City = pd.read_csv('GlobalLandTemperaturesByCity.csv', sep=',')
  GLT_City.head()
  
     AvgTemp  AvgTempUncert   City    Country Lat     Long    year    month   day
  0   6.068   1.737          Århus    Denmark 57.05N  10.33E  1743    11  01
  5   5.788   3.624          Århus    Denmark 57.05N  10.33E  1744    04  01
  6   10.644  1.283          Århus    Denmark 57.05N  10.33E  1744    05  01
  7   14.051  1.347          Århus    Denmark 57.05N  10.33E  1744    06  01
  8   16.082  1.396          Århus    Denmark 57.05N  10.33E  1744    07  01
  10  12.781  1.454          Århus    Denmark 57.05N  10.33E  1744    09  01
  11  7.950   1.630          Århus    Denmark 57.05N  10.33E  1744    10  01
  12  4.639   1.302          Århus    Denmark 57.05N  10.33E  1744    11  01
  </code></pre>
  
  <p>I want to compute the weighted average temperature for each city, for each month, and add this as a new column to my original data frame in the smoothest possible way, using <code>transform()</code>, for reasons further down the line.</p>
  
  <p>First, I define a function to compute a weighted average:</p>
  
  <pre><code>def wavg(group,data_name,weight_name, sigma=None):
      data = group[data_name]
      weight = group[weight_name]
      #Check whether we have actual weights or measurement uncertainties
      if sigma=='sigma':
          weight = 1./weight
  
      try:
          return (data * weight).sum() / weight.sum()
      except ZeroDivisionError:
          return data.mean()
  </code></pre>
  
  <p>I then want to combine <code>GroupBy</code> and <code>transform()</code> to apply this function to my data frame and add the result as a new column, like:</p>
  
  <pre><code>GLT_City['WeightedMonthlyMean'] = GLT_City.groupby(['City','month']).transform(wavg, 'AvgTemp','AvgTempUncert', sigma='sigma')
  </code></pre>
  
  <p>Now this results in a very lengthy error message copy-pasted below</p>
  
  <pre><code>---------------------------------------------------------------------------
  TypeError                                 Traceback (most recent call last)
  pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc (pandas/_libs/index.c:5126)()
  
  pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item (pandas/_libs/hashtable.c:14010)()
  
  TypeError: an integer is required
  
  During handling of the above exception, another exception occurred:
  
  KeyError                                  Traceback (most recent call last)
  &lt;ipython-input-61-cef679f52b5f&gt; in &lt;module&gt;()
  ----&gt; 1 GLT_City['WeightedMonthlyMean'] = GLT_City.groupby(['City','month']).transform(wavg, 
  'AvgTemp','AvgTemp', sigma='sigma')
  
  ~/anaconda/envs/python36/lib/python3.6/site-
  packages/pandas/core/groupby.py in transform(self, func, *args, **kwargs)
     3814                 result = getattr(self, func)(*args, **kwargs)
     3815         else:
  -&gt; 3816             return self._transform_general(func, *args, **kwargs)
     3817 
     3818         # a reduction transform
  
  ~/anaconda/envs/python36/lib/python3.6/site-packages/pandas/core/groupby.py in _transform_general(self, func, *args, **kwargs)
     3765                 # Try slow path and fast path.
     3766                 try:
  -&gt; 3767                     path, res = self._choose_path(fast_path, slow_path, group)
     3768                 except TypeError:
     3769                     return self._transform_item_by_item(obj, fast_path)
  
  ~/anaconda/envs/python36/lib/python3.6/site-packages/pandas/core/groupby.py in _choose_path(self, fast_path, slow_path, group)
     3861     def _choose_path(self, fast_path, slow_path, group):
     3862         path = slow_path
  -&gt; 3863         res = slow_path(group)
     3864 
     3865         # if we make it here, test if we can use the fast path
  
  ~/anaconda/envs/python36/lib/python3.6/site-packages/pandas/core/groupby.py in &lt;lambda&gt;(group)
     3856             fast_path = lambda group: func(group, *args, **kwargs)
     3857             slow_path = lambda group: group.apply(
  -&gt; 3858                 lambda x: func(x, *args, **kwargs), axis=self.axis)
     3859         return fast_path, slow_path
     3860 
  
  ~/anaconda/envs/python36/lib/python3.6/site-packages/pandas/core/frame.py in apply(self, func, axis, broadcast, raw, reduce, args, **kwds)
     4260                         f, axis,
     4261                         reduce=reduce,
  -&gt; 4262                         ignore_failures=ignore_failures)
     4263             else:
     4264                 return self._apply_broadcast(f, axis)
  
  ~/anaconda/envs/python36/lib/python3.6/site-packages/pandas/core/frame.py in _apply_standard(self, func, axis, ignore_failures, reduce)
     4356             try:
     4357                 for i, v in enumerate(series_gen):
  -&gt; 4358                     results[i] = func(v)
     4359                     keys.append(v.name)
     4360             except Exception as e:
  
  ~/anaconda/envs/python36/lib/python3.6/site-packages/pandas/core/groupby.py in &lt;lambda&gt;(x)
     3856             fast_path = lambda group: func(group, *args, **kwargs)
     3857             slow_path = lambda group: group.apply(
  -&gt; 3858                 lambda x: func(x, *args, **kwargs), axis=self.axis)
     3859         return fast_path, slow_path
     3860 
  
  &lt;ipython-input-58-181ef4bb1f30&gt; in wavg(group, data_name, weight_name, sigma)
       10 
       11     #Extracting data and weights.
  ---&gt; 12     data = group[data_name]
       13     weight = group[weight_name]
       14     #Check whether we have actual weights, or measurement uncertainties
  
  ~/anaconda/envs/python36/lib/python3.6/site-packages/pandas/core/series.py in __getitem__(self, key)
      599         key = com._apply_if_callable(key, self)
      600         try:
  --&gt; 601             result = self.index.get_value(self, key)
      602 
      603             if not is_scalar(result):
  
  ~/anaconda/envs/python36/lib/python3.6/site-
  packages/pandas/core/indexes/base.py in get_value(self, series, key)
     2475         try:
     2476             return self._engine.get_value(s, k,
  -&gt; 2477                                           
  tz=getattr(series.dtype, 'tz', None))
     2478         except KeyError as e1:
     2479             if len(self) &gt; 0 and self.inferred_type in ['integer', 'boolean']:
  
   pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value (pandas/_libs/index.c:4404)()
  
  pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value (pandas/_libs/index.c:4087)()
  
  pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc (pandas/_libs/index.c:5210)()
  
  KeyError: ('AvgTemp', 'occurred at index AvgTemp')
  </code></pre>
  
  <p>So this is obviously not working, but it's not clear to me why. Any pointers/solutions would be most welcome.</p>
  
  <p>I can use the <code>apply()</code> method, to get the desired output, but since I am averaging over groups, I can't really merge this with the original dataframe, since the series produced by <code>apply()</code> will be of a different size.</p>