How to flatten multi index (that really has one level) in Pandas? value_counts only returns multi-index

user_913098 0
Viewed 325

Pandas 1.1.4

Consider

df4 = pd.DataFrame({
    'a': [1, 2, 3, 4]
})

counts = df4.value_counts().astype(np.int)

print(counts.index)

counts.reindex(counts.index.to_flat_index())

print(counts.index)

print(counts.index.to_flat_index())

output:

MultiIndex([(4,),
            (3,),
            (2,),
            (1,)],
           names=['a'])
MultiIndex([(4,),
            (3,),
            (2,),
            (1,)],
           names=['a'])
Index([(4,), (3,), (2,), (1,)], dtype='object')

I want to be able to interchange with

df5 = pd.Series([5, 6, 7, 8], index=[1, 2, 3, 4])
print(df5.index)

whose index is

Int64Index([1, 2, 3, 4], dtype='int64').

Please notice df4's index is the same as df5's, but the value_counts makes it into a multi index.

How to I work around this? No flatten I try seems to work.

multi-index pandas python
2 Answers

You can extract the index from the 0th level of the multiindex like so:

>>> counts.index.get_level_values(0)
Int64Index([4, 3, 2, 1], dtype='int64', name='a')

Then you can swap the index out for these values like so:

new_index = pd.Series([5, 6, 7, 8], index=[1, 2, 3, 4])
new_counts = counts.set_axis(
    counts.index.get_level_values(0).map(new_index)
)

print(new_counts)
a
8    1
7    1
6    1
5    1
dtype: int64

But you can simply call .value_counts() on the column itself to avoid having play around with the multiindex at all:

df['a'].value_counts()
user_142784480

When pandas creates a dataframe from a dictionary like in this case, it assumes the keys of the passed dict should be the columns of the resulting DataFrame. If the keys should be rows you should initialise your dataframe as follows:

df4 = pd.DataFrame({'a': [1, 2, 3, 4]}, orient='index')

when you then run :

print(counts.index.to_flat_index())

you get

Index([(1, 2, 3, 4)], dtype='object')
user_81501860
Related