Add percent of total column to Pandas pivot_table

Question

I can't seem to figure out how to add a % of total column for each date_submitted group to the below pandas pivot table:

In [177]: pass_rate_pivot

date_submitted  audit_status
04-11-2014      audited         140
                is_adserver       7
                rejected         75
                unauditable     257
04-18-2014      audited         177
                is_adserver      10
                pending          44
                rejected         30
                unauditable     226
04-25-2014      audited          97
                is_adserver       5
                pending          33
                rejected          9
                unauditable     355
Name: site_domain, dtype: int64

In [177]: pass_rate_pivot.to_dict()


Out[177]:
{('04-11-2014', 'audited'): 140,
 ('04-11-2014', 'is_adserver'): 7,
 ('04-11-2014', 'rejected'): 75,
 ('04-11-2014', 'unauditable'): 257,
 ('04-18-2014', 'audited'): 177,
 ('04-18-2014', 'is_adserver'): 10,
 ('04-18-2014', 'pending'): 44,
 ('04-18-2014', 'rejected'): 30,
 ('04-18-2014', 'unauditable'): 226,
 ('04-25-2014', 'audited'): 97,
 ('04-25-2014', 'is_adserver'): 5,
 ('04-25-2014', 'pending'): 33,
 ('04-25-2014', 'rejected'): 9,
 ('04-25-2014', 'unauditable'): 355}

joris · Accepted Answer · 2014-04-29T22:55:49.887

Is this what you want? (for each group dividing the element with the sum of all elements in that group):

In [62]: pass_rate_pivot.groupby(level=0).transform(lambda x: x/x.sum())
Out[62]: 
04-11-2014  audited        0.292276
            is_adserver    0.014614
            rejected       0.156576
            unauditable    0.536534
04-18-2014  audited        0.363450
            is_adserver    0.020534
            pending        0.090349
            rejected       0.061602
            unauditable    0.464066
04-25-2014  audited        0.194389
            is_adserver    0.010020
            pending        0.066132
            rejected       0.018036
            unauditable    0.711423
dtype: float64

If you want to add this as a column, you can indeed concat both serieses to one dataframe as suggested by @exp1orer:

pd.concat([pass_rate_pivot,pass_rate_pivot_pct], axis=1)

If pass_rate_pivot would already be a dataframe, you could just assign a new column like pass_rate_pivot['pct'] = pass_rate_pivot['original column'].groupby(...

OP wanted them in the same table I believe, how about adding `pandas.concat([pass_rate_pivot,pass_rate_pivot_pct], axis=1)` to the end of your code? — exp1orer, Apr 29 '14 at 22:16
in master/0.14 you will be able to do ``df.groupby(level=0).rank(pct=True)`` — Jeff, Apr 29 '14 at 22:22
@Jeff `rank` is still something else I think (it is giving the order of sorting) — joris, Apr 29 '14 at 22:35

exp1orer · Answer 2 · 2014-04-29T22:08:33.987

0

The most natural way is to do it as you create the pivot table. Here I assume that date_submitted is a column (not in the index) using reset_index. And make sure that your values are in a column (here I call that 'value_col'). Then

def calc_group_pct(df,value_var = 'value_col'):
    df['pct'] = df[value_var]/float(df[value_var].sum())
    return df
df.groupby('date_submitted').apply(calc_group_pct)

edited Apr 29 '14 at 22:08

answered Apr 29 '14 at 22:01

exp1orer

11,481
7
38
51

To do the groupby, the grouper does not need to be a column, this can also be the index, or a level of the index (the `level` kwarg for the last case) – joris Apr 29 '14 at 22:03
You're totally right. I got confused because I had recently tried to groupby an index level and a column simultaneously which (correct if wrong) is not allowed. Edited to reflect that. – exp1orer Apr 29 '14 at 22:08

Add percent of total column to Pandas pivot_table

2 Answers2

Linked