Python - Pandas分组聚合后的小计

Question

这是我正在使用的数据样本：

SCENARIO    DATE    POD         AREA    IDOC    STATUS  TYPE
AAA   02.06.2015    JKJKJKJKJKK 4210    713375  51         1
AAA   02.06.2015    JWERWERE    4210    713375  51         1
AAA   02.06.2015    JAFDFDFDFD  4210    713375  51         9
BBB   02.06.2015    AAAAAAAA    5400    713504  51        43
CCC   05.06.2015    BBBBBBBBBB  4100    756443  51       187
AAA   05.06.2015    EEEEEEEE    4100    756457  53       228

我已经使用pandas编写了以下代码进行分组：

```python ```

import pandas as pd
import numpy as np

xl = pd.ExcelFile("MRD.xlsx")
df = xl.parse("Sheet3") 
#print (df.column.values)

# The following gave ValueError: Cannot label index with a null key
# dfi = df.pivot('SCENARIO)

# Here i do not actually need it to count every column, just a specific one
table = df.groupby(["SCENARIO", "STATUS", "TYPE"]).agg(['count'])
writer = pd.ExcelWriter('pandas.out.xlsx', engine='xlsxwriter')
table.to_excel(writer, sheet_name='Sheet1')
writer.save()


table2 = pd.DataFrame(df.groupby(["SCENARIO", "STATUS", "TYPE"])['TYPE'].count())
print (table2)
writer2 = pd.ExcelWriter('pandas2.out.xlsx', engine='xlsxwriter')
table2.to_excel(writer2, sheet_name='Sheet1')
writer2.save()

这将产生一个结果：

SCENARIO  STATUS  TYPE    TYPE
AAA       51      1       2
                  9       1
          53      228     1
BBB       51      43      1
CCC       51      187     1
Name: TYPE, dtype: int64   

如何在每个分组中添加小计？理想情况下，我希望实现类似于以下内容的效果：

SCENARIO  STATUS  TYPE    TYPE
AAA       51      1       2
                  9       1
          Total           3
          53      228     1
          Total           1
BBB       51      43      1
          Total           1
CCC       51      187     1
          Total           1
Name: TYPE, dtype: int64   

这是可能的吗？

- onlyf

---

如果 Total 在 TYPE 层级中，是否有问题？ - jezrael

Answer 1

使用：

#if necessary convert TYPE column to string
df['TYPE'] = df['TYPE'].astype(str)
df = df.groupby(["SCENARIO", "STATUS", "TYPE"])['TYPE'].count()

#aggregate sum by first 2 levels
df1 = df.groupby(["SCENARIO", "STATUS"]).sum()
#add 3 level of MultiIndex 
df1.index = [df1.index.get_level_values(0),
            df1.index.get_level_values(1),
            ['Total'] * len(df1)]

#thanks MaxU for improving
#df1 = df1.set_index(np.array(['Total'] * len(df1)), append=True) 

print (df1)
SCENARIO  STATUS       
AAA       51      Total    3
          53      Total    1
BBB       51      Total    1
CCC       51      Total    1
Name: TYPE, dtype: int64

---

#join together and sorts
df = pd.concat([df, df1]).sort_index(level=[0,1])
print (df)
SCENARIO  STATUS  TYPE 
AAA       51      1        2
                  9        1
                  Total    3
          53      228      1
                  Total    1
BBB       51      43       1
                  Total    1
CCC       51      187      1
                  Total    1
Name: TYPE, dtype: int64

- jezrael

---

运行 df = pd.concat([df, df1]).sort_index(level=[0,1]) 时出现错误信息：TypeError: '<' not supported between instances of 'tuple' and 'int'。有人可以提供帮助吗？ - beginofwork

@beginofwork - 问题在于两个DataFrame都不是MultiIndex，所以在执行 df = pd.concat([df, df1]) 后，会得到混合的元组索引和非MultiIndex值。 - jezrael

如何获取混合元组索引和非MultiIndex值？ - beginofwork

@beginofwork - 看起来答案中的数据不同，如果检查 print(df.index) 和 print(df1.index)，两者都没有 MultiIndex。原因不明确 - 可能是代码的某个部分缺失，也可能是输入数据不同。 - jezrael

@jezrael，df 是 Int64Index，而 df1 是 MultiIndex。 - beginofwork

@beginofwork - 是的，这是错误的原因。解决方案是在两个中创建MultiIndex或Index - 需要相同类型的索引。如何操作取决于数据。 - jezrael

Answer 2

使用pandas透视表也可以实现同样的功能：

table = pd.pivot_table(df, values=['TYPE'], index=['SCENARIO', 'STATUS'], aggfunc='count')
table

- Jayshwor Khadka

Answer 3

Chris Moffitt创建了一个名为sidetable的库，以简化这个过程，可以与groupby对象一起使用，使用访问器使其非常容易。话虽如此，我认为接受的答案和评论是一座金矿，值得先去看看。

- Canute S