BUG: reset_index with NaN in MultiIndex

[wangshun98]

This is related to the following commit.
jreback@625ee94

When one level in the MultiIndex is all NaN, line 2819 in DataFrame.reset_index() and subfunction _maybe_cast, will have a problem, because values is empty.
2819: values = values.take(labels)
IndexError: cannot do a non-empty take from an empty axes

A possible fix is to add the following lines before 2819:
if mask.all():
values = labels * np.nan
return values

[jreback]

can U give an example of your use case
an all nan level in a multi index is really really hard to support properly

[wangshun98]

This comes out of a database query of mixed fields of dates and values. And I want to use the combination of dates as index so I can easily join the values from multiple queries. however, sometimes all dates in one column are all null.

NaN in MultiIndex is already supported. That 3 lines I suggested would make it work with a level with all NaN. We can test case other functionalities. But after I made that change in my local copy, I haven't experienced other issues with all NaN MultiIndex yet.

[jreback]

go ahead and do a PR (with a test case) and i'll take a look

[boombard]

I can confirm I am also experiencing precisely this issue with an all-NaN level in my multi-index. Looks to me like those three lines should work if included.

[gryBox]

@jreback

The code below returns the error: IndexError: cannot do a non-empty take from an empty axes.

import pandas as pd
import numpy as np
import random
import string



# Create 12 random strings 3 char long 
rndm_strgs = [''.join(random.SystemRandom().choice(string.ascii_uppercase + string.digits) for _ in range(3)) for i in range(12)]            
rndm_strgs[0] = None
rndm_strgs[5] = None

# Make Dataframe
df = pd.DataFrame({'A' : list('pandasisgood'),
                   'B' : np.nan,
                   'C' : rndm_strgs,
                   'D' : np.random.rand(12)})

# Set an Index -> Columns have Nans
df_w_idx = df.set_index(['A','B','C'])

# This is where it fails
df_w_idx.reset_index()

This is the desired result:

for clmNm in df_w_idx.index.names:
    print(clmNm)

    print(clmNm)

    df_w_idx[clmNm] = df_w_idx.index.get_level_values(clmNm)


df_w_idx = df_w_idx.reset_index(drop=True).copy()
df_w_idx

[tzinckgraf]

@jreback I have the same issue and was playing around with a solution.

The reset_index function in frame.py function has the following code

            # if we have the labels, extract the values with a mask
            if labels is not None:
                mask = labels == -1
                values = values.take(labels)

see here.

The mask variable is all True only if all the labels are -1. Assuming this means all the values were np.nan, one solution I was playing with is as follows

            # if we have the labels, extract the values with a mask
            if labels is not None:
                mask = labels == -1
                if mask.all():
                    values = (np.nan * mask).values()
                else:
                    values = values.take(labels)

Please advise. More than happy to create a pull request and create all appropriate test cases.

[jreback]

@tzinckgraf you can just do the take if len(values); .take() is picky :>

sure why don't you put up a PR with a test (you can use the above example).