Pandas透视表多级索引子总计

Question

我尝试使用Pandas创建一个简单的Excel样式的带有小计的数据透视表，但我找不到一种方法。我已经尝试了Wes在另一个与小计相关的问题中提出的解决方案，但那并没有给出期望的结果。以下是复现步骤：

创建示例数据：

sample_data = {'customer': ['A', 'A', 'A', 'B', 'B', 'B', 'A', 'A', 'A', 'B', 'B', 'B'], 'product': ['astro','ball','car','astro','ball', 'car', 'astro', 'ball', 'car','astro','ball','car'],
'week': [1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2],
'qty': [10, 15, 20, 40, 20, 34, 300, 20, 304, 23, 45, 23]}

df = pd.DataFrame(sample_data)

创建带有边距的数据透视表（仅包含总计，不包含按客户(A、B)小计的内容）。

piv = df.pivot_table(index=['customer','product'],columns='week',values='qty',margins=True,aggfunc=np.sum)

    week           1    2   All
customer    product         
A   astro         10    300 310
    ball          15    20  35
    car           20    304 324
B   astro         40    23  63
    ball          20    45  65
    car           34    23  57
All              139    715 854

接着，我尝试了Wes Mckiney在另一个帖子中提到的方法，使用堆栈函数：

piv2 = df.pivot_table(index='customer',columns=['week','product'],values='qty',margins=True,aggfunc=np.sum)

piv2.stack('product')

结果的格式符合我的要求，但是“全部”行没有求和：

    week               1    2   All
customer    product         
A                    NaN    NaN    669.0
        astro       10.0    300.0   NaN
        ball        15.0    20.0    NaN
        car         20.0    304.0   NaN
B                    NaN    NaN    185.0
        astro        40.0   23.0    NaN
        ball         20.0   45.0    NaN
        car         34.0    23.0    NaN
All                  NaN    NaN     854.0
        astro        50.0   323.0   NaN
        ball         35.0   65.0    NaN
        car         54.0    327.0   NaN

如何使其像Excel中那样工作，下面是示例？所有小计和总计都能正常工作吗？我错过了什么吗？ed excel示例 只是想指出，我可以使用For循环，在每次迭代时按客户进行过滤，稍后进行连接，但我希望可能有更直接的解决方案，谢谢。

- Clayton Faria

Answer 1

你可以一步完成它，但由于字母排序，你必须对索引名称进行战略性考虑：

piv = df.pivot_table(index=['customer','product'],
                     columns='week',
                     values='qty',
                     margins=True,
                     margins_name='Total',
                     aggfunc=np.sum)

(pd.concat([piv, 
            piv.query('customer != "Total"')
               .sum(level=0)
               .assign(product='total')
               .set_index('product', append=True)])
   .sort_index())

输出:

week                1    2  Total
customer product                 
A        astro     10  300    310
         ball      15   20     35
         car       20  304    324
         total     45  624    669
B        astro     40   23     63
         ball      20   45     65
         car       34   23     57
         total     94   91    185
Total             139  715    854

- Scott Boston

---

嗨，Scott，你提供的解决方案简单而优雅，对我的真实数据框架非常有效。你能否推荐一些学习你解决问题所采取的步骤的资料？非常感谢。 - Clayton Faria

你好，Clayton。我是从回答Stack Overflow上的问题中学到这些步骤的。不过，www.dunderdata.com有一些很棒的培训信息。 - Scott Boston

@ScottBoston 如何访问一个值，例如 [B, Total] 中的值（输出=63）？ - elsadek

使用元组。df.loc[('B','total'), :] - Scott Boston

@ScottBoston 谢谢，我刚刚发现元组在多重索引数据框中的使用。 - elsadek

Answer 2

@Scott Boston的回答非常完美和优雅。供参考，如果你只将客户分组并使用pd.concat()合并结果，我们会得到以下结果。

piv = df.pivot_table(index=['customer','product'],columns='week',values='qty',margins=True,aggfunc=np.sum)
piv3 =  df.pivot_table(index=['customer'],columns='week',values='qty',margins=True,aggfunc=np.sum)
piv4 = pd.concat([piv, piv3], axis=0)

piv4
week    1   2   All
(A, astro)  10  300 310
(A, ball)   15  20  35
(A, car)    20  304 324
(B, astro)  40  23  63
(B, ball)   20  45  65
(B, car)    34  23  57
(All, ) 139 715 854
A   45  624 669
B   94  91  185
All 139 715 854

- r-beginners

---

这也是我很高兴刚学到的另一种可能性。谢谢。 - Clayton Faria