Summing Multiple Columns Using Multiple Indexes
I have a dataframe that is created from a pivot table and looks something like 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 am looking to iterate over the top level of a multiindex column to create a sum column for each company:
company1 company2
FN FP TP SUM FN FP TP SUM
April- 2012 112 0 0 112 112 0 0 112
April- 2013 370 544 140 1054 370 544 140 1054
April- 2014 499 50 24 573 499 50 24 573
August- 2012 431 0 0 431 431 0 0 431
August- 2013 496 0 0 496 496 0 0 496
August- 2014 221 426 77 724 221 426 77 724
I don't know the names of the companies in advance, so it needs a cycle
+1
source to share
1 answer
You can calculate this sum by specifying level
(you want to sum over the first level (level 0), so collapsing the second level):
In [29]: df.sum(axis=1, level=0)
Out[29]:
company1 company2
April- 2012 112 112
April- 2013 1054 1054
April- 2014 573 573
August- 2012 431 431
August- 2013 496 496
August- 2014 724 724
If you want them to add the original dataframe like in the above example, you can add a layer on columns and concat:
sums = df.sum(level=0, axis=1)
sums.columns = pd.MultiIndex.from_product([sums.columns, ['SUM']])
df = pd.concat([df, sums], axis=1)
+6
source to share