DataFrame.groupby fails with MultiIndex containing pd.NaT

[stevenmanton]

It seems that the groupby operation fails when the row index is a MultiIndex containing NaT values. For example, the following code fails (v0.15.2) with TypeError: 'numpy.ndarray' object is not callable:

midx = pd.MultiIndex(levels=[[pd.NaT, pd.datetime(2012,1,2), 
                     pd.datetime(2012,1,3)], ['a', 'b']],
                     labels=[[0, 1, 1, 2], [0, 0, 1, 0]], names=['date', None])
df = pd.Series(pd.np.random.rand(4), index=midx)
df.groupby(level=1)

However, it seems as though np.nan values are handled properly:

midx = pd.MultiIndex(levels=[[pd.np.nan, 10, 20], ['a', 'b']],
                     labels=[[0, 1, 1, 2], [0, 0, 1, 0]], names=['date', None])
df = pd.Series(pd.np.random.rand(4), index=midx)
df.groupby(level=1)

[shoyer]

I can reproduce this on master.

Thanks for the report!

[jreback]

iirc this is a dupe issue - if someone would like 2 find the reference

[jreback]

this is covered by #6996, #6992, will xref it there.

pull-requests are welcome

[jreback]

actually, will reopen in case it is slightly different.

[stevenmanton]

Thanks for looking into this! I've been banging into this all day as I've been working on some analysis. I took a look at the pandas source, but it's not clear to me where the bug is and how to go about fixing it. Nonetheless, I've found a pretty quick workaround that produces the behavior I would expect. Maybe this will help others with a similar problem or give some direction in fixing the issue. Essentially, the workaround drops the NaT value within the level.

Here's an example of the workaround that works for me:

midx = pd.MultiIndex(levels=[[pd.NaT, pd.datetime(2012,1,2), 
                     pd.datetime(2012,1,3)], ['a', 'b']],
                     labels=[[0, 1, 1, 2], [0, 0, 1, 0]], names=['date', None])
df = pd.Series(pd.np.random.rand(4), index=midx)
df.groupby(df.index.get_level_values(0)).count()

[mroeschke]

Looks to be fixed on master. I imagine this edge case could use a test.

In [8]: In [4]: pd.__version__
   ...: Out[4]: '0.25.0.dev0+85.g0eddba883'

In [9]: In [7]: midx = pd.MultiIndex(levels=[[pd.NaT, pd.datetime(2012,1,2),
   ...:    ...:                      pd.datetime(2012,1,3)], ['a', 'b']],
   ...:    ...:                      labels=[[0, 1, 1, 2], [0, 0, 1, 0]], names=['date', None])
   ...:    ...: df = pd.Series(pd.np.random.rand(4), index=midx)
   ...:    ...: df.groupby(level=1).mean()
/anaconda3/envs/pandas-dev/bin/ipython:3: FutureWarning: the 'labels' keyword is deprecated, use 'codes' instead
  # -*- coding: utf-8 -*-
Out[9]:
a    0.849207
b    0.877276
dtype: float64

[TrigonaMinima]

@mroeschke
tried the code on the pandas version 0.24.0, it ran successfully. Could you point me to the file where this test should be added?

[mroeschke]

pandas/tests/groupby/test_groupby.py