Apply sum on pivot table columns

Question

The following code:

df = pd.pivot_table(df, values=['FF', 'FS', 'FD', 'G'], 
                        index=['name','points'], 
                        aggfunc=[np.sum]).sort_values(by='name',ascending=True)

Generates this pivot_table:

                             sum
                             FD   FF    FS   G
                  points
 Abner Vinícius   9.10       0.0  1.0   0.0  1.0
                  7.20       2.0  1.0   0.0  0.0
                  2.90       0.0  1.0   2.0  0.0
...

Is there a way I can sum all ocurrences from columns 'FD', 'FF' and 'FS' (7.0), divide this sum by the total occurences of column 'G' (1.0), and have the result (7.0) as a new index column ('FTotal_By_GTotal'), ending up with:

                                                    sum
                                                   FD   FF    FS   G
 name              FTotal_By_GTotal    points
 Abner Vinícius    7.0                  9.10       0.0  1.0   0.0  1.0
                                        7.20       2.0  1.0   0.0  0.0
                                        2.90       0.0  1.0   2.0  0.0

EDIT:

df.to_dict():

{('sum', 'FD'): {...('Abner Vinícius', 9.1): 0.0, ('Abner Vinícius', 7.2): 2.0, ('Abner Vinícius', 2.9): 0.0, ('Abner Vinícius', 2.1): 1.0, ('Abner Vinícius', 0.3): 0.0, ('Abner Vinícius', 0.0): 0.0, ('Abner Vinícius', -0.1): 0.0, ('Abner Vinícius', -0.5): 0.0... ('sum', 'FF'): {..('Abner Vinícius', 9.1): 1.0, ('Abner Vinícius', 7.2): 1.0, ('Abner Vinícius', 2.9): 1.0, ('Abner Vinícius', 2.1): 0.0, ('Abner Vinícius', 0.3): 0.0, ('Abner Vinícius', 0.0): 0.0, ('Abner Vinícius', -0.1): 0.0, ('Abner Vinícius', -0.5): 0.0.....}

df.columns:

print (df.columns())
TypeError: 'MultiIndex' object is not callable

Hi, interesting, not sure if this might be of interest https://stackoverflow.com/questions/15570099/pandas-pivot-tables-row-subtotals — IronMan, Sep 21 '20 at 22:11
Can you do `df.to_dict()` or `df.columns` to give us the input data as it is a multi-index? — David Erickson, Sep 22 '20 at 00:08
please refer to my edit. the actual `df` is gigantic, and I've edited so you can get the gist of it. — 8-Bit Borges, Sep 22 '20 at 00:16

score 1 · Accepted Answer · answered Sep 22 '20 at 02:55

You could drop the column level 'sum' and do the aggregations on the resultant dataframe.

Data:

df = pd.DataFrame({"name": ["A", "A", "A", "B", "B", "B"],
                   "points": [9.1, 7.2, 2.9, 8.5, 6.1, 3.7], 
                   "FD": [0.0,2.0, 0.0, 1.0, 1.0, 0.0], 
                   "FF": [1., 1., 1., 1., 1., 1.], 
                   "FS": [0.0, 0.0, 2.0, 0.0, 1.0, 0.0], 
                   "G": [1., 0., 0., 1., 0., 1.]})


df = pd.pivot_table(df, values=['FF', 'FS', 'FD', 'G'], 
                        index=['name','points'], 
                        aggfunc=[np.sum]).sort_values(by='name',ascending=True)
df

             sum               
              FD   FF   FS    G
name points                    
A    2.9     0.0  1.0  2.0  0.0
     7.2     2.0  1.0  0.0  0.0
     9.1     0.0  1.0  0.0  1.0
B    3.7     0.0  1.0  0.0  1.0
     6.1     1.0  1.0  1.0  0.0
     8.5     1.0  1.0  0.0  1.0

FTotal_By_GTotal:

# Drop 'sum'
df = df.droplevel(0, axis=1)


# Aggregate calc
totals = df.groupby('name').apply(lambda x: x[['FF', 'FD', 'FS']].sum().sum() / x["G"].sum())
totals.name = "FTotal_By_GTotal"

# Merge to existing data
df.reset_index().merge(totals, on="name").set_index(['name', 'FTotal_By_GTotal', 'points'])


                               FD   FF   FS    G
name FTotal_By_GTotal points                    
A    7.0              2.9     0.0  1.0  2.0  0.0
                      7.2     2.0  1.0  0.0  0.0
                      9.1     0.0  1.0  0.0  1.0
B    3.0              3.7     0.0  1.0  0.0  1.0
                      6.1     1.0  1.0  1.0  0.0
                      8.5     1.0  1.0  0.0  1.0

Apply sum on pivot table columns

1 Answers1