用多索引列求和 [英] Summing multiple columns with multiindex columns

问题描述

我有一个从数据透视表创建的数据框，看起来像这样:

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

我正在迭代遍历multiindex列的上层以为每个公司创建一个sum列:

I'm looking to iterative over the upper level of the 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 company names beforehand, so it will need to loop

推荐答案

您可以通过指定level来计算此总和(您要对第一级(级别0)求和 ，因此崩溃第二级):

You can calculate this sum by specifying the level (you want to sum along 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

如果您希望它们添加到原始数据框中，如上面的示例，则可以在列和concat中添加一个级别:

If you want them to add to the original dataframe, as in your example above, you can add a level in the 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)

这篇关于用多索引列求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！