pandas transform dataframe pivot table

Question

I can transform the following dataframe:

   VALUE       COUNT  RECL_LCC  RECL_PI
0      1  15,686,114         3        1
1      2  27,537,963         1        1
2      3  23,448,904         1        2
3      4   1,213,184         1        3
4      5  14,185,448         3        2
5      6  13,064,600         3        3
6      7  27,043,180         2        2
7      8  11,732,405         2        1
8      9  14,773,871         2        3

into something like this:

RECL_PI            1           2           3
RECL_LCC                                    
1         27,537,963  23,448,904   1,213,184
2         11,732,405  27,043,180  14,773,871
3         15,686,114  14,185,448  13,064,600

by using pandas pivot table:

plot_table = LCC_PI_df.pivot_table(index=['RECL_LCC'], columns='RECL_PI', values='COUNT', aggfunc='sum')

Is there a quick way to create the pivot table with percentage of row totals instead of raw sum of counts?

Does row totals mean (27,537,963 + 23,448,904 + 1,213,184) for the first row, and so on? And you want to replace numbers in the row to percentages? — Jihun
– Jihun, Commented Oct 31, 2014 at 5:11

Jihun · Accepted Answer · 2014-10-31 05:43:36Z

3

According to comments, I think you can do that like following. Note that I converted the COUNT column to integers to do this :

#convert strings of the COUNT column to integers
import locale
locale.setlocale( locale.LC_ALL, 'en_US.UTF-8' ) 
LCC_PI_df.COUNT = LCC_PI_df.COUNT.apply(locale.atoi)

plot_table = LCC_PI_df.pivot_table(index=['RECL_LCC'], columns='RECL_PI', values='COUNT', aggfunc='sum')
#Calculate percentages
plot_table = plot_table.apply(lambda x : x / x.sum(), axis=1)

edited Oct 31, 2014 at 5:43

answered Oct 31, 2014 at 5:28

Jihun

1,4851 gold badge12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pandas transform dataframe pivot table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related