Python/Pandas convert pivot table into percentages based on row total-CodePudding

I think this should be simple, but in Python I just can't figure out how to take a pivot table and get percentages based on row totals.

Pivot Table:

df.pivot_table(index='Name', columns='scenario',
               values='y', aggfunc=np.count_nonzero, margins=True, margins_name='Total').fillna(0)

Which gets:

But what I want is each cell divided by row totals:

How can I do this? Thank you in advance.

CodePudding user response：

You can use the .pipe method combined with .div to perform this column-wise division on all of the columns.
You can then use .applymap to apply a string fomratting to each of your values to get the values to appear as percentages (note that they become strings and are no longer mathematically useful)

out = (
    df.pivot_table(
        index='Name', columns='scenario', values='y', 
        aggfunc=np.count_nonzero, margins=True, 
        margins_name='Total', fill_value=0
    )
    .pipe(lambda d: d.div(d['Total'], axis='index'))
    .applymap('{:.0%}'.format)
)

example

df = pd.DataFrame({
    'a': [1, 0, 0, 1, 5],
    'b': [20, 20, 10, 50, 15], 
   'c': [50, 20, 50, 100, 20]
})

print(df)
   a   b    c
0  1  20   50
1  0  20   20
2  0  10   50
3  1  50  100
4  5  15   20


out = (
    df.pipe(lambda d: d.div(d['c'], axis='index'))
    .applymap('{:.0%}'.format)
)

print(out)
     a     b     c
0   2%   40%  100%
1   0%  100%  100%
2   0%   20%  100%
3   1%   50%  100%
4  25%   75%  100%