<h4 dir="auto">In a nutshell:</h4>
<ul dir="auto">
<li><code class="notranslate">groupby</code> on a single categorical column with prescribed categories incorrectly returns results for <strong>all</strong> categories, even those that are not actually present in the DataFrame.</li>
<li>In addition, when aggregating after grouping on categoricals (<code class="notranslate">groupby.sum</code> and the likes), with both prescribed and non-prescribed categories, we get values for <strong>all possible combinations</strong> of categories, including those not present in the DataFrame.</li>
</ul>
<h4 dir="auto">Problem description and code samples</h4>
<h5 dir="auto">Case 1: group by a single column</h5>
<p dir="auto">Consider the following code where we define a DataFrame with a categorical column with prescribed categories.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import pandas as pd

labels = pd.Series(list('abcbabcab'))
df = pd.DataFrame({'label1': labels,
                   'x': [0, 1, 1, 1, 1, 0, 1, 1, 1]})
df.label1 = labels.astype('category', categories=list('abcdef'))"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>

<span class="pl-s1">labels</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">Series</span>(<span class="pl-en">list</span>(<span class="pl-s">'abcbabcab'</span>))
<span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>({<span class="pl-s">'label1'</span>: <span class="pl-s1">labels</span>,
                   <span class="pl-s">'x'</span>: [<span class="pl-c1">0</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>, <span class="pl-c1">0</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>]})
<span class="pl-s1">df</span>.<span class="pl-s1">label1</span> <span class="pl-c1">=</span> <span class="pl-s1">labels</span>.<span class="pl-en">astype</span>(<span class="pl-s">'category'</span>, <span class="pl-s1">categories</span><span class="pl-c1">=</span><span class="pl-en">list</span>(<span class="pl-s">'abcdef'</span>))</pre></div>
<p dir="auto">When we group by the categorical <code class="notranslate">label1</code> column and aggregate, we incorrectly get results for <strong>all</strong> prescribed categories, including those that are not present in the DataFrame (see rows below where the value of x is <code class="notranslate">NaN</code>):</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In[6]: df.groupby('label1').sum()
Out[6]:
          x
label1     
a       2.0
b       3.0
c       2.0
d       NaN
e       NaN
f       NaN"><pre class="notranslate"><span class="pl-v">In</span>[<span class="pl-c1">6</span>]: <span class="pl-s1">df</span>.<span class="pl-en">groupby</span>(<span class="pl-s">'label1'</span>).<span class="pl-en">sum</span>()
<span class="pl-v">Out</span>[<span class="pl-c1">6</span>]:
          <span class="pl-s1">x</span>
<span class="pl-s1">label1</span>     
<span class="pl-s1">a</span>       <span class="pl-c1">2.0</span>
<span class="pl-s1">b</span>       <span class="pl-c1">3.0</span>
<span class="pl-s1">c</span>       <span class="pl-c1">2.0</span>
<span class="pl-s1">d</span>       <span class="pl-v">NaN</span>
<span class="pl-s1">e</span>       <span class="pl-v">NaN</span>
<span class="pl-s1">f</span>       <span class="pl-v">NaN</span></pre></div>
<p dir="auto">The elements in excess are already present in the <code class="notranslate">groupby</code> object:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In[7]: df.groupby('label1').groups
Out[7]:
{'a': Int64Index([0, 4, 7], dtype='int64'),
 'b': Int64Index([1, 3, 5, 8], dtype='int64'),
 'c': Int64Index([2, 6], dtype='int64'),
 'd': Int64Index([], dtype='int64'),
 'e': Int64Index([], dtype='int64'),
 'f': Int64Index([], dtype='int64')}"><pre class="notranslate"><span class="pl-v">In</span>[<span class="pl-c1">7</span>]: <span class="pl-s1">df</span>.<span class="pl-en">groupby</span>(<span class="pl-s">'label1'</span>).<span class="pl-s1">groups</span>
<span class="pl-v">Out</span>[<span class="pl-c1">7</span>]:
{<span class="pl-s">'a'</span>: <span class="pl-v">Int64Index</span>([<span class="pl-c1">0</span>, <span class="pl-c1">4</span>, <span class="pl-c1">7</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>),
 <span class="pl-s">'b'</span>: <span class="pl-v">Int64Index</span>([<span class="pl-c1">1</span>, <span class="pl-c1">3</span>, <span class="pl-c1">5</span>, <span class="pl-c1">8</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>),
 <span class="pl-s">'c'</span>: <span class="pl-v">Int64Index</span>([<span class="pl-c1">2</span>, <span class="pl-c1">6</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>),
 <span class="pl-s">'d'</span>: <span class="pl-v">Int64Index</span>([], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>),
 <span class="pl-s">'e'</span>: <span class="pl-v">Int64Index</span>([], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>),
 <span class="pl-s">'f'</span>: <span class="pl-v">Int64Index</span>([], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>)}</pre></div>
<p dir="auto">The above doesn't happen if the categories for the <code class="notranslate">label1</code> column are not prescribed or if the column is not converted to a categorical at all.</p>
<h5 dir="auto">Case 2: group by multiple columns</h5>
<p dir="auto">Consider now the case where we have two categorical columns:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="df = pd.DataFrame({'label1': labels,
                   'label2': labels,
                   'x': [0, 1, 1, 1, 1, 0, 1, 1, 1]})
df.label1 = labels.astype('category', categories=list('abcdef'))
df.label2 = labels.astype('category', categories=list('abcdef'))"><pre class="notranslate"><span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>({<span class="pl-s">'label1'</span>: <span class="pl-s1">labels</span>,
                   <span class="pl-s">'label2'</span>: <span class="pl-s1">labels</span>,
                   <span class="pl-s">'x'</span>: [<span class="pl-c1">0</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>, <span class="pl-c1">0</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>, <span class="pl-c1">1</span>]})
<span class="pl-s1">df</span>.<span class="pl-s1">label1</span> <span class="pl-c1">=</span> <span class="pl-s1">labels</span>.<span class="pl-en">astype</span>(<span class="pl-s">'category'</span>, <span class="pl-s1">categories</span><span class="pl-c1">=</span><span class="pl-en">list</span>(<span class="pl-s">'abcdef'</span>))
<span class="pl-s1">df</span>.<span class="pl-s1">label2</span> <span class="pl-c1">=</span> <span class="pl-s1">labels</span>.<span class="pl-en">astype</span>(<span class="pl-s">'category'</span>, <span class="pl-s1">categories</span><span class="pl-c1">=</span><span class="pl-en">list</span>(<span class="pl-s">'abcdef'</span>))</pre></div>
<p dir="auto">Contrary to the single column case above, we do get the correct group labels when we group by both categorical columns <code class="notranslate">label1</code> and <code class="notranslate">label2</code>:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In[11]: df.groupby(['label1', 'label2']).groups
Out[11]:
{('a', 'a'): Int64Index([0, 4, 7], dtype='int64'),
 ('b', 'b'): Int64Index([1, 3, 5, 8], dtype='int64'),
 ('c', 'c'): Int64Index([2, 6], dtype='int64')}"><pre class="notranslate"><span class="pl-v">In</span>[<span class="pl-c1">11</span>]: <span class="pl-s1">df</span>.<span class="pl-en">groupby</span>([<span class="pl-s">'label1'</span>, <span class="pl-s">'label2'</span>]).<span class="pl-s1">groups</span>
<span class="pl-v">Out</span>[<span class="pl-c1">11</span>]:
{(<span class="pl-s">'a'</span>, <span class="pl-s">'a'</span>): <span class="pl-v">Int64Index</span>([<span class="pl-c1">0</span>, <span class="pl-c1">4</span>, <span class="pl-c1">7</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>),
 (<span class="pl-s">'b'</span>, <span class="pl-s">'b'</span>): <span class="pl-v">Int64Index</span>([<span class="pl-c1">1</span>, <span class="pl-c1">3</span>, <span class="pl-c1">5</span>, <span class="pl-c1">8</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>),
 (<span class="pl-s">'c'</span>, <span class="pl-s">'c'</span>): <span class="pl-v">Int64Index</span>([<span class="pl-c1">2</span>, <span class="pl-c1">6</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>)}</pre></div>
<p dir="auto">But we still get incorrect results if we aggregate:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In[12]: df.groupby(['label1', 'label2']).sum()
Out[12]:
                 x
label1 label2     
a      a       2.0
       b       NaN
       c       NaN
       d       NaN
       e       NaN
       f       NaN
b      a       NaN
       b       3.0
       c       NaN
       d       NaN
       e       NaN
       f       NaN
c      a       NaN
       b       NaN
       c       2.0
       d       NaN
       e       NaN
       f       NaN
d      a       NaN
       b       NaN
       c       NaN
       d       NaN
       e       NaN
       f       NaN
e      a       NaN
       b       NaN
       c       NaN
       d       NaN
       e       NaN
       f       NaN
f      a       NaN
       b       NaN
       c       NaN
       d       NaN
       e       NaN
       f       NaN"><pre class="notranslate"><span class="pl-v">In</span>[<span class="pl-c1">12</span>]: <span class="pl-s1">df</span>.<span class="pl-en">groupby</span>([<span class="pl-s">'label1'</span>, <span class="pl-s">'label2'</span>]).<span class="pl-en">sum</span>()
<span class="pl-v">Out</span>[<span class="pl-c1">12</span>]:
                 <span class="pl-s1">x</span>
<span class="pl-s1">label1</span> <span class="pl-s1">label2</span>     
<span class="pl-s1">a</span>      <span class="pl-s1">a</span>       <span class="pl-c1">2.0</span>
       <span class="pl-s1">b</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">c</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">d</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">e</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">f</span>       <span class="pl-v">NaN</span>
<span class="pl-s1">b</span>      <span class="pl-s1">a</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">b</span>       <span class="pl-c1">3.0</span>
       <span class="pl-s1">c</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">d</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">e</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">f</span>       <span class="pl-v">NaN</span>
<span class="pl-s1">c</span>      <span class="pl-s1">a</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">b</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">c</span>       <span class="pl-c1">2.0</span>
       <span class="pl-s1">d</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">e</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">f</span>       <span class="pl-v">NaN</span>
<span class="pl-s1">d</span>      <span class="pl-s1">a</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">b</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">c</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">d</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">e</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">f</span>       <span class="pl-v">NaN</span>
<span class="pl-s1">e</span>      <span class="pl-s1">a</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">b</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">c</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">d</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">e</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">f</span>       <span class="pl-v">NaN</span>
<span class="pl-s1">f</span>      <span class="pl-s1">a</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">b</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">c</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">d</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">e</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">f</span>       <span class="pl-v">NaN</span></pre></div>
<p dir="auto"><strong>Note</strong>: The aggregation shows the same inccorect behaviour also when we <em>don't</em> prescribe the categories:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In[13]: df.label1 = labels.astype('category')
   ...: df.label2 = labels.astype('category')
In[14]: df.groupby(['label1', 'label2']).sum()
Out[14]:
                 x
label1 label2     
a      a       2.0
       b       NaN
       c       NaN
b      a       NaN
       b       3.0
       c       NaN
c      a       NaN
       b       NaN
       c       2.0
"><pre class="notranslate"><span class="pl-v">In</span>[<span class="pl-c1">13</span>]: <span class="pl-s1">df</span>.<span class="pl-s1">label1</span> <span class="pl-c1">=</span> <span class="pl-s1">labels</span>.<span class="pl-en">astype</span>(<span class="pl-s">'category'</span>)
   ...: <span class="pl-s1">df</span>.<span class="pl-s1">label2</span> <span class="pl-c1">=</span> <span class="pl-s1">labels</span>.<span class="pl-en">astype</span>(<span class="pl-s">'category'</span>)
<span class="pl-v">In</span>[<span class="pl-c1">14</span>]: <span class="pl-s1">df</span>.<span class="pl-en">groupby</span>([<span class="pl-s">'label1'</span>, <span class="pl-s">'label2'</span>]).<span class="pl-en">sum</span>()
<span class="pl-v">Out</span>[<span class="pl-c1">14</span>]:
                 <span class="pl-s1">x</span>
<span class="pl-s1">label1</span> <span class="pl-s1">label2</span>     
<span class="pl-s1">a</span>      <span class="pl-s1">a</span>       <span class="pl-c1">2.0</span>
       <span class="pl-s1">b</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">c</span>       <span class="pl-v">NaN</span>
<span class="pl-s1">b</span>      <span class="pl-s1">a</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">b</span>       <span class="pl-c1">3.0</span>
       <span class="pl-s1">c</span>       <span class="pl-v">NaN</span>
<span class="pl-s1">c</span>      <span class="pl-s1">a</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">b</span>       <span class="pl-v">NaN</span>
       <span class="pl-s1">c</span>       <span class="pl-c1">2.0</span></pre></div>
<h4 dir="auto">Expected Output</h4>
<p dir="auto">For <strong>Case 1</strong>:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In[6]: df.groupby('label1').sum()
Out[6]:
        x
label1   
a       2
b       3
c       2"><pre class="notranslate"><span class="pl-v">In</span>[<span class="pl-c1">6</span>]: <span class="pl-s1">df</span>.<span class="pl-en">groupby</span>(<span class="pl-s">'label1'</span>).<span class="pl-en">sum</span>()
<span class="pl-v">Out</span>[<span class="pl-c1">6</span>]:
        <span class="pl-s1">x</span>
<span class="pl-s1">label1</span>   
<span class="pl-s1">a</span>       <span class="pl-c1">2</span>
<span class="pl-s1">b</span>       <span class="pl-c1">3</span>
<span class="pl-s1">c</span>       <span class="pl-c1">2</span></pre></div>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In[7]: df.groupby('label1').groups
Out[7]:
{'a': Int64Index([0, 4, 7], dtype='int64'),
 'b': Int64Index([1, 3, 5, 8], dtype='int64'),
 'c': Int64Index([2, 6], dtype='int64')}"><pre class="notranslate"><span class="pl-v">In</span>[<span class="pl-c1">7</span>]: <span class="pl-s1">df</span>.<span class="pl-en">groupby</span>(<span class="pl-s">'label1'</span>).<span class="pl-s1">groups</span>
<span class="pl-v">Out</span>[<span class="pl-c1">7</span>]:
{<span class="pl-s">'a'</span>: <span class="pl-v">Int64Index</span>([<span class="pl-c1">0</span>, <span class="pl-c1">4</span>, <span class="pl-c1">7</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>),
 <span class="pl-s">'b'</span>: <span class="pl-v">Int64Index</span>([<span class="pl-c1">1</span>, <span class="pl-c1">3</span>, <span class="pl-c1">5</span>, <span class="pl-c1">8</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>),
 <span class="pl-s">'c'</span>: <span class="pl-v">Int64Index</span>([<span class="pl-c1">2</span>, <span class="pl-c1">6</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'int64'</span>)}</pre></div>
<p dir="auto">For <strong>Case 2</strong>:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In[19]: df.groupby(['label1', 'label2']).sum()
Out[19]:
               x
label1 label2   
a      a       2
b      b       3
c      c       2"><pre class="notranslate"><span class="pl-v">In</span>[<span class="pl-c1">19</span>]: <span class="pl-s1">df</span>.<span class="pl-en">groupby</span>([<span class="pl-s">'label1'</span>, <span class="pl-s">'label2'</span>]).<span class="pl-en">sum</span>()
<span class="pl-v">Out</span>[<span class="pl-c1">19</span>]:
               <span class="pl-s1">x</span>
<span class="pl-s1">label1</span> <span class="pl-s1">label2</span>   
<span class="pl-s1">a</span>      <span class="pl-s1">a</span>       <span class="pl-c1">2</span>
<span class="pl-s1">b</span>      <span class="pl-s1">b</span>       <span class="pl-c1">3</span>
<span class="pl-s1">c</span>      <span class="pl-s1">c</span>       <span class="pl-c1">2</span></pre></div>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
<h2 dir="auto">INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 3.6.1.final.0<br>
python-bits: 64<br>
OS: Windows<br>
OS-release: 10<br>
machine: AMD64<br>
processor: Intel64 Family 6 Model 69 Stepping 1, GenuineIntel<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: None<br>
LOCALE: None.None</p>
<p dir="auto">pandas: 0.20.3<br>
pytest: 3.0.7<br>
pip: 9.0.1<br>
setuptools: 27.2.0<br>
Cython: 0.25.2<br>
numpy: 1.12.1<br>
scipy: 0.19.0<br>
xarray: None<br>
IPython: 5.3.0<br>
sphinx: 1.5.6<br>
patsy: 0.4.1<br>
dateutil: 2.6.0<br>
pytz: 2017.2<br>
blosc: None<br>
bottleneck: 1.2.1<br>
tables: 3.2.2<br>
numexpr: 2.6.2<br>
feather: None<br>
matplotlib: 2.0.2<br>
openpyxl: 2.4.7<br>
xlrd: 1.0.0<br>
xlwt: 1.2.0<br>
xlsxwriter: 0.9.6<br>
lxml: 3.7.3<br>
bs4: 4.6.0<br>
html5lib: 0.999<br>
sqlalchemy: 1.1.9<br>
pymysql: None<br>
psycopg2: None<br>
jinja2: 2.9.6<br>
s3fs: None<br>
pandas_gbq: None<br>
pandas_datareader: None</p>
</details>
<h4 dir="auto">Code Sample, a copy-pastable example if possible</h4>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import pandas as pd                                                                                                                                                                                                                    
df = pd.DataFrame({'a': ['x','x','y'], 'b': [0,1,0], 'c': [7,8,9]})                                                                                                                                                                    
print(df.groupby(['a','b']).mean().reset_index())                                                                                                                                                                                      
df['a'] = df['a'].astype('category')                                                                                                                                                                                                   
print(df.groupby(['a','b']).mean().reset_index())"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>                                                                                                                                                                                                                    
<span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>({<span class="pl-s">'a'</span>: [<span class="pl-s">'x'</span>,<span class="pl-s">'x'</span>,<span class="pl-s">'y'</span>], <span class="pl-s">'b'</span>: [<span class="pl-c1">0</span>,<span class="pl-c1">1</span>,<span class="pl-c1">0</span>], <span class="pl-s">'c'</span>: [<span class="pl-c1">7</span>,<span class="pl-c1">8</span>,<span class="pl-c1">9</span>]})                                                                                                                                                                    
<span class="pl-en">print</span>(<span class="pl-s1">df</span>.<span class="pl-en">groupby</span>([<span class="pl-s">'a'</span>,<span class="pl-s">'b'</span>]).<span class="pl-en">mean</span>().<span class="pl-en">reset_index</span>())                                                                                                                                                                                      
<span class="pl-s1">df</span>[<span class="pl-s">'a'</span>] <span class="pl-c1">=</span> <span class="pl-s1">df</span>[<span class="pl-s">'a'</span>].<span class="pl-en">astype</span>(<span class="pl-s">'category'</span>)                                                                                                                                                                                                   
<span class="pl-en">print</span>(<span class="pl-s1">df</span>.<span class="pl-en">groupby</span>([<span class="pl-s">'a'</span>,<span class="pl-s">'b'</span>]).<span class="pl-en">mean</span>().<span class="pl-en">reset_index</span>())</pre></div>
<p dir="auto">Returns two different results:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   a  b  c
0  x  0  7
1  x  1  8
2  y  0  9

   a  b    c
0  x  0  7.0
1  x  1  8.0
2  y  0  9.0
3  y  1  NaN"><pre class="notranslate"><code class="notranslate">   a  b  c
0  x  0  7
1  x  1  8
2  y  0  9

   a  b    c
0  x  0  7.0
1  x  1  8.0
2  y  0  9.0
3  y  1  NaN
</code></pre></div>
<h4 dir="auto">Problem description</h4>
<p dir="auto">Performing a groupby with a categorical type returns all combination of the groupby columns. This is a problem in my actual application as it results in a massive dataframe that is mostly filled with nans. I would also prefer not to move off of category dtype since it provides necessary memory savings.</p>
<h4 dir="auto">Expected Output</h4>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="   a  b  c
0  x  0  7
1  x  1  8
2  y  0  9

   a  b  c
0  x  0  7
1  x  1  8
2  y  0  9"><pre class="notranslate"><code class="notranslate">   a  b  c
0  x  0  7
1  x  1  8
2  y  0  9

   a  b  c
0  x  0  7
1  x  1  8
2  y  0  9
</code></pre></div>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
<h2 dir="auto">INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 3.6.1.final.0<br>
python-bits: 64<br>
OS: Linux<br>
OS-release: 4.10.0-26-generic<br>
machine: x86_64<br>
processor: x86_64<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: en_US.UTF-8<br>
LOCALE: en_US.UTF-8</p>
<p dir="auto">pandas: 0.20.3<br>
pytest: None<br>
pip: 9.0.1<br>
setuptools: 33.1.1<br>
Cython: None<br>
numpy: 1.13.1<br>
scipy: 0.19.0<br>
xarray: None<br>
IPython: 6.1.0<br>
sphinx: None<br>
patsy: None<br>
dateutil: 2.6.1<br>
pytz: 2017.2<br>
blosc: None<br>
bottleneck: None<br>
tables: 3.4.2<br>
numexpr: 2.6.2<br>
feather: None<br>
matplotlib: 2.0.2<br>
openpyxl: None<br>
xlrd: 1.0.0<br>
xlwt: None<br>
xlsxwriter: 0.9.6<br>
lxml: None<br>
bs4: 4.5.3<br>
html5lib: 0.999999999<br>
sqlalchemy: None<br>
pymysql: None<br>
psycopg2: 2.7.1 (dt dec pq3 ext lo64)<br>
jinja2: 2.9.6<br>
s3fs: None<br>
pandas_gbq: None<br>
pandas_datareader: None</p>
</details>