Dividing multiindex columns by sum to create percentages

Question

I have a dataframe that is created from a pivot table, and looks similar to this:

import pandas as pd
d = {('company1', 'False Negative'): {'April- 2012': 112.0, 'April- 2013': 370.0, 'April- 2014': 499.0, 'August- 2012': 431.0, 'August- 2013': 496.0, 'August- 2014': 221.0},
('company1', 'False Positive'): {'April- 2012': 0.0, 'April- 2013': 544.0, 'April- 2014': 50.0, 'August- 2012': 0.0, 'August- 2013': 0.0, 'August- 2014': 426.0},
('company1', 'True Positive'): {'April- 2012': 0.0, 'April- 2013': 140.0, 'April- 2014': 24.0, 'August- 2012': 0.0, 'August- 2013': 0.0,'August- 2014': 77.0},
('company2', 'False Negative'): {'April- 2012': 112.0, 'April- 2013': 370.0, 'April- 2014': 499.0, 'August- 2012': 431.0, 'August- 2013': 496.0, 'August- 2014': 221.0},
('company2', 'False Positive'): {'April- 2012': 0.0, 'April- 2013': 544.0, 'April- 2014': 50.0, 'August- 2012': 0.0, 'August- 2013': 0.0, 'August- 2014': 426.0},
('company2', 'True Positive'): {'April- 2012': 0.0, 'April- 2013': 140.0, 'April- 2014': 24.0, 'August- 2012': 0.0, 'August- 2013': 0.0,'August- 2014': 77.0},}

df = pd.DataFrame(d)

                company1    company2
                FN  FP  TP  FN  FP  TP
April- 2012     112 0   0   112 0   0
April- 2013     370 544 140 370 544 140
April- 2014     499 50  24  499 50  24
August- 2012    431 0   0   431 0   0
August- 2013    496 0   0   496 0   0
August- 2014    221 426 77  221 426 77

I'm looking to iterative over the upper level of the multiindex column to divide each company by it's sum to create a percentage:

                company1     company2
                FN  FP  TP   FN    FP   TP
April- 2012     1   0   0    1   0  0
April- 2013     .35 .51 .13  .35   .51  .13
April- 2014     .87 .09 .03  .87   .09  .03
etc.

I don't know the company names beforehand. This is a variation of a question asked yesterday: Summing multiple columns with multiindex columns

Yeah, that's the question. How does one do that? I can't figure out how to line them up without producing errors. — DataSwede, Aug 27 '14 at 15:14
related: http://stackoverflow.com/questions/13940753/aligning-dataframes-with-same-columns-different-index-levels — Paul H, Aug 27 '14 at 19:01

score 6 · Accepted Answer · answered Aug 27 '14 at 15:14

6

You can divide by the sum using the div method (with that you can specify the level to match):

df.div(df.sum(axis=1, level=0), level=0)

answered Aug 27 '14 at 15:14

joris

133,120
36
247
202

This was absolutely perfect. Thanks again! – DataSwede Aug 27 '14 at 15:17

score 1 · Answer 2 · edited May 23 '17 at 10:29

While the solution by joris works nicely, I want to add that this will not work as soon as the Multiindex has more levels. Here are my solutions partially based on a StackOverflow post (Normalize DataFrame by group) and the Pandas docs (http://pandas.pydata.org/pandas-docs/stable/groupby.html).

d = {
('X', 'company1', 'False Negative'): {'April- 2012': 112.0, 'April- 2013': 370.0, 'April- 2014': 499.0, 'August- 2012': 431.0, 'August- 2013': 496.0, 'August- 2014': 221.0},
('X', 'company1', 'False Positive'): {'April- 2012': 0.0, 'April- 2013': 544.0, 'April- 2014': 50.0, 'August- 2012': 0.0, 'August- 2013': 0.0, 'August- 2014': 426.0},
('X', 'company1', 'True Positive'): {'April- 2012': 0.0, 'April- 2013': 140.0, 'April- 2014': 24.0, 'August- 2012': 0.0, 'August- 2013': 0.0,'August- 2014': 77.0},
('X', 'company2', 'False Negative'): {'April- 2012': 112.0, 'April- 2013': 370.0, 'April- 2014': 499.0, 'August- 2012': 431.0, 'August- 2013': 496.0, 'August- 2014': 221.0},
('X', 'company2', 'False Positive'): {'April- 2012': 0.0, 'April- 2013': 544.0, 'April- 2014': 50.0, 'August- 2012': 0.0, 'August- 2013': 0.0, 'August- 2014': 426.0},
('X', 'company2', 'True Positive'): {'April- 2012': 0.0, 'April- 2013': 140.0, 'April- 2014': 24.0, 'August- 2012': 0.0, 'August- 2013': 0.0,'August- 2014': 77.0},
('Y','company1', 'False Negative'): {'April- 2012': 112.0, 'April- 2013': 370.0, 'April- 2014': 499.0, 'August- 2012': 431.0, 'August- 2013': 496.0, 'August- 2014': 221.0},
('Y','company1', 'False Positive'): {'April- 2012': 0.0, 'April- 2013': 544.0, 'April- 2014': 50.0, 'August- 2012': 0.0, 'August- 2013': 0.0, 'August- 2014': 426.0},
('Y','company1', 'True Positive'): {'April- 2012': 0.0, 'April- 2013': 140.0, 'April- 2014': 24.0, 'August- 2012': 0.0, 'August- 2013': 0.0,'August- 2014': 77.0},
('Y','company2', 'False Negative'): {'April- 2012': 112.0, 'April- 2013': 370.0, 'April- 2014': 499.0, 'August- 2012': 431.0, 'August- 2013': 496.0, 'August- 2014': 221.0},
('Y','company2', 'False Positive'): {'April- 2012': 0.0, 'April- 2013': 544.0, 'April- 2014': 50.0, 'August- 2012': 0.0, 'August- 2013': 0.0, 'August- 2014': 426.0},
('Y','company2', 'True Positive'): {'April- 2012': 0.0, 'April- 2013': 140.0, 'April- 2014': 24.0, 'August- 2012': 0.0, 'August- 2013': 0.0,'August- 2014': 77.0},
}

df = pd.DataFrame(d)
# extrapolation of original method: not working!
# df.div(df.sum(axis=1,level=[0,1]), level=[0,1]) #  does not work

# alternative 1: replicating the sums for each company to fit the number of columns using numpy
df.div(np.repeat(df.sum(axis=1,level=[0,1]).values, 3, axis=1), axis=1)

# alternative 2: stacking, grouping, transforming and unstacking
df.columns.names = ['top', 'company', 'result'] # naming column levels for convenience 
df.\
    stack(["top", "company", "result"]).\
    groupby(level=[0,1,2]).\
    transform(lambda x: (x / x.sum(axis=0))).\
    unstack(["top", "company", "result"])

Dividing multiindex columns by sum to create percentages

2 Answers2