CODEWITHSUNDEEP
pythonpandasdataframe
ScientiaEtVeritas
ScientiaEtVeritas

Reputation: 5278

Pandas: getting rid of the multiindex

After grouping and counting a dataframe I'm trying to remove the multiindex like this:

df = df[['CID','FE', 'FID']].groupby(by=['CID','FE']).count()
              .unstack().reset_index()

Printing the columns (df.colums) shows that it is still a MultiIndex.

MultiIndex(levels=[['FID', 'CID'], [...]]

I can't access the column CID via df['CID'].

Upvotes: 36

Views: 48848

Answers (2)

jezrael
jezrael

Reputation: 862731

I think you need if is necessary convert MultiIndex to Index:

df.columns = df.columns.map(''.join)

Or if need remove level use droplevel:

df.columns = df.columns.droplevel(0)

If need access to values is possible use xs:

df = df.xs('CID', axis=1, level=1)

You can also check:

What is the difference between size and count in pandas?

EDIT:

For remove MultiIndex is another solution select by ['FID'].

df = df.groupby(by=['CID','FE'])['FID'].count().unstack().reset_index()

Samples (also added rename_axis for nicer output):

df = pd.DataFrame({'CID':[2,2,3],
                   'FE':[5,5,6],
                   'FID':[1,7,9]})

print (df)
   CID  FE  FID
0    2   5    1
1    2   5    7
2    3   6    9

df = df.groupby(by=['CID','FE'])['FID']
       .count()
       .unstack()
       .reset_index()
       .rename_axis(None, axis=1)

print (df)    
   CID    5    6
0    2  2.0  NaN
1    3  NaN  1.0

Upvotes: 63

Allen Qin
Allen Qin

Reputation: 19947

This should get rid of MultiIndex for CID and allow you to access it via df['CID']

df = df.rename(columns={('CID',''):'CID'})

Upvotes: 0

Related Questions