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'].
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
This should get rid of MultiIndex for CID and allow you to access it via df['CID']
df = df.rename(columns={('CID',''):'CID'})
