I am new to pivot table. I just want the count of each Age Grp by Mth. I tried
pivot1=pd.pivot_table(df,index=[ "Age Grp"], columns=["Mth"], values=["Age Grp"], aggfunc=pd.Series.nunique)
but get ValueError: Grouper for 'Age Grp' not 1-dimensional
DF
|Age Grp | Mth |
| 0-4 | 1 |
| 5 -9 | 5 |
| 0-4 | 10 |
| 10-14 | 5 |
Desired Outcome
Mth | 1 | 5 | 10 |
|Age Grp | |
| 0-4 | 1 | 0 | 1|
| 5 -9 | 0 | 1 | 0|
| 10-14 | 0 | 1 | 0|
CodePudding user response:
We can try crosstab
instead of pivot_table
with values
and an aggfunc
then use fillna
to replace the NaN
values with 0
:
ct_df = pd.crosstab(
df['Age Grp'], df['Mth'],
values=df['Age Grp'], aggfunc='nunique'
).fillna(0, downcast='infer')
ct_df
:
Mth 1 5 10
Age Grp
0-4 1 0 1
10-14 0 1 0
5-9 0 1 0
Or with groupby nunique
unstack
:
df_us = df.groupby(
['Age Grp', 'Mth']
)['Age Grp'].nunique().unstack(fill_value=0)
df_us
:
Mth 1 5 10
Age Grp
0-4 1 0 1
10-14 0 1 0
5-9 0 1 0
Setup and imports
import pandas as pd # 1.3.4
df = pd.DataFrame({
'Age Grp': ['0-4', '5-9', '0-4', '10-14'], 'Mth': [1, 5, 10, 5]
})
CodePudding user response:
Or:
df.groupby(['Age Grp', 'Mth']).size().unstack(fill_value=0)
Output:
Mth 1 5 10
Age Grp
0-4 1 0 1
10-14 0 1 0
5-9 0 1 0