I have a dataframe
df = pd.DataFrame({'date':['2021-06','2021-06','2021-09','2021-08','2021-09'],'type':['t1','t1','t1','t2','t2'], 'other_col':['a','b','b','a','c']})
and would like to pivot it such that I get the following output.
date 2021-06 2021-08 2021-09
t1 count 2 0 1
mean 100% 0% 50%
t2 count 0 1 1
mean 0% 100% 50%
But I could not find out how to do it.
You can use a list of aggfunc then stack the column header
count = (df.pivot_table(index='type', columns='date', values='other_col',
fill_value=0 ,aggfunc=['count'])
.stack(level=0))
mean = (count.div(count.sum()).mul(100)
.applymap("{:,.2f}%".format)
.rename(index={'count': 'mean'}))
out = pd.concat([count, mean]).sort_index()
print(count)
date 2021-06 2021-08 2021-09
type
t1 count 2 0 1
t2 count 0 1 1
print(mean)
date 2021-06 2021-08 2021-09
type
t1 mean 100.00% 0.00% 50.00%
t2 mean 0.00% 100.00% 50.00%
print(out)
date 2021-06 2021-08 2021-09
type
t1 count 2 0 1
mean 100.00% 0.00% 50.00%
t2 count 0 1 1
mean 0.00% 100.00% 50.00%
I don't there's a single aggfunc that'll do this, but you could do:
df1 = df.pivot_table(index='type', columns='date', values='other_col', aggfunc='count').fillna(0)
df1.index = pd.MultiIndex.from_arrays([df1.index, ['count']*len(df1)])
df2 = (df1 / df1.sum(axis=0) * 100).astype(str)+'%'
df2.index = pd.MultiIndex.from_arrays([df2.index.get_level_values(0), ['mean']*len(df2)])
pd.concat([df1, df2]).sort_index()
which'll give
date 2021-06 2021-08 2021-09
type
t1 count 2.0 0.0 1.0
mean 100.0% 0.0% 50.0%
t2 count 0.0 1.0 1.0
mean 0.0% 100.0% 50.0%
Suppose you forgot to include values:
df['values'] = [1,2,3,4,5]
So:
pt = pd.pivot_table(df,aggfunc=[np.mean, 'count'], values='values',columns='date').T
pt.index = pt.index.swaplevel(0,1)