pandas named aggregation without multilevel dataframe

I am trying to remove the multi level but unable to do so.

import pandas as pd
k = pd.DataFrame([['x',2], ['y',4],['x',6]], columns=['name','value'])
    
agg_item={'value': [('n', 'count')]}
    
k=k[['name','value']].groupby(['name'],dropna=False).agg(agg_item).reset_index()
k
   name value
           n
0   x   2
1   y   1

k.columns
​

MultiIndex([( 'name',  ''),
            ('value', 'n')],
           )

How do I get sql like table with only ‘name’ and ‘n’ columns?

Desired output:

   name n
0   x   2
1   y   1

>Solution :

By using a list in your dictionary, you request to have a multindex.

You should use this syntax instead:

agg_item={'n': ('value', 'count')}
    
(k[['name','value']]
  .groupby(['name'],dropna=False)
  .agg(**agg_item).
  reset_index()
)

NB. Don’t forget to unpack the dictionary as parameters

Or without dictionary:

(k[['name','value']]
  .groupby(['name'],dropna=False)
  .agg(n=('value', 'count')).
  reset_index()
)

Output:

  name  n
0    x  2
1    y  1