CODEWITHSUNDEEP
pythonpandasmulti-index
briba
briba

Reputation: 2987

Unstack MultiIndex data frame

I am writting a test case with this multi-index data frame but I am unable to unstack. The following function produces exactly the layout I am reading from an excel file just this like this sample: Sample file

def mocked_df():
    people = ['USER 1', 'USER 2', 'USER 3',
              'USER 4', 'USER 5', 'USER 6']
    flag_and_states = [['A', 'B'], ['AL', 'AR', 'CA', 'CO']]

    # Building multi-index frame
    index = pd.MultiIndex.from_product([people])
    columns = pd.MultiIndex.from_product(flag_and_states, names=['Flag', 'Name'])
    data = [[1, 0, 0, 0, 0, 0, 0, 0],
            [0, 0, 0, 0, 0, 1, 0, 0],
            [0, 0, 0, 0, 0, 1, 0, 0],
            [0, 0, 1, 0, 0, 0, 0, 0],
            [0, 0, 0, 0, 0, 0, 1, 0],
            [0, 0, 0, 1, 0, 0, 0, 0]]

    # Return data frame with multi-index
    return pd.DataFrame(
        columns=columns,
        index=index,
        data=data
    )

After this I am trying to unstack the data frame:

df = mocked_df()
df = df.unstack().reset_index()

But I have the following error:

ValueError: not enough values to unpack (expected 2, got 0)

Any ideas? The same unstack call works when used on the sample Excel file also.

Thank you very much!

Upvotes: 1

Views: 551

Answers (2)

jezrael
jezrael

Reputation: 862591

Here is problem one level MultiIndex in index, so unstack failed with very weird errors.

print (df.index.nlevels)
1
#correct 2 level MultiIndex in columns
print (df.columns.nlevels)
2

print (df.index)
MultiIndex([('USER 1',),
            ('USER 2',),
            ('USER 3',),
            ('USER 4',),
            ('USER 5',),
            ('USER 6',)],
           )

#correct 2 level MultiIndex in columns
print (df.columns)
MultiIndex([('A', 'AL'),
            ('A', 'AR'),
            ('A', 'CA'),
            ('A', 'CO'),
            ('B', 'AL'),
            ('B', 'AR'),
            ('B', 'CA'),
            ('B', 'CO')],
           names=['Flag', 'Name'])

Solution is create index by list only index = people, because in index is no MultiIndex:

def mocked_df():
    people = ['USER 1', 'USER 2', 'USER 3',
              'USER 4', 'USER 5', 'USER 6']
    flag_and_states = [['A', 'B'], ['AL', 'AR', 'CA', 'CO']]

    # Building multi-index frame
    index = people
    columns = pd.MultiIndex.from_product(flag_and_states, names=['Flag', 'Name'])
    data = [[1, 0, 0, 0, 0, 0, 0, 0],
            [0, 0, 0, 0, 0, 1, 0, 0],
            [0, 0, 0, 0, 0, 1, 0, 0],
            [0, 0, 1, 0, 0, 0, 0, 0],
            [0, 0, 0, 0, 0, 0, 1, 0],
            [0, 0, 0, 1, 0, 0, 0, 0]]

    # Return data frame with multi-index
    return pd.DataFrame(
        columns=columns,
        index=index,
        data=data
    )

Then unstack working correctly:

df = mocked_df()
df = df.unstack().reset_index()
print (df.head(10))
  Flag Name level_2  0
0    A   AL  USER 1  1
1    A   AL  USER 2  0
2    A   AL  USER 3  0
3    A   AL  USER 4  0
4    A   AL  USER 5  0
5    A   AL  USER 6  0
6    A   AR  USER 1  0
7    A   AR  USER 2  0
8    A   AR  USER 3  0
9    A   AR  USER 4  0

Upvotes: 2

Neo
Neo

Reputation: 627

You do not provide with a desired output, what about if you try:

df = df.stack().reset_index()

Upvotes: 0

Related Questions