PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, November 1, 2022

[FIXED] how to calculate % of row and % of column for a python pivot with row and column headings?

 November 01, 2022     pandas, percentage, pivot, python     No comments   

Issue

For this simple pivot, how to turn value into % of row, and likewise to % of column?

import pandas as pd
df = pd.DataFrame({'A' : ['one', 'one', 'two', 'three'] * 3,
                   'B' : ['A', 'B', 'C'] * 4,
               'C' : range(12)})
pd.pivot_table(df, index='A', columns='B', aggfunc=sum)

Somehow after some searching I don't find answer to this simple question.

Expected result (if getting % of column)

      A   B  C
ONE   50% 24% 50%
THREE 13% 31% 42%
TWO  36% 45% 8%

Thanks


Solution

You can the desired output by using pd.crosstab:

import numpy as np
import pandas as pd

df = pd.DataFrame({'A' : ['one', 'one', 'two', 'three'] * 3,
                   'B' : ['A', 'B', 'C'] * 4,
               'C' : range(12)})

pd.crosstab(df.A, df.B, values=df.C, aggfunc=np.sum, normalize='columns')

This should yield:

B             A         B         C
A                                  
one    0.500000  0.227273  0.500000
three  0.166667  0.318182  0.423077
two    0.333333  0.454545  0.076923

You can reformat the output to display the percentages in hundredth with applymap:

pd.crosstab(df.A, df.B,values=df.C,aggfunc=np.sum,normalize='columns').applymap(lambda x: "{0:.0f}%".format(100*x))

This should yield:

B        A    B    C
A                   
one    50%  23%  50%
three  17%  32%  42%
two    33%  45%   8%

Edit:

If the normalize parameter is not working, you can get the percentage with apply:

pd.crosstab(df.A, df.B, values=df.C, aggfunc=np.sum).apply(lambda x: x/x.sum()).applymap(lambda x: "{:.0f}%".format(100*x))

I hope this proves useful.



Answered By - Abdou
Answer Checked By - David Goodson (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing