如何为Pandas DataFrame添加总计列并保持数值格式？

我是Python新手，接手了同事的代码，想要给DataFrame加上总计行和总计列，但添加总计列的代码运行报错，出现大量Traceback信息。

原代码 #

csmposting = create_csm_table(csm)
csmposting['TOTAL'] = csmposting.sum(axis=1)
csmposting.loc['TOTAL'] = csmposting.sum(axis=0).astype(int)

测试情况 #

• 去掉第二行代码（添加总计列的部分），能成功生成总计行，但没有总计列：

Unit1   779 285 16  2   3
Unit2   290 20  3   3   0
Unit3   0   0   3   7   0
Unit4   547 97  8   10  0
TOTAL   1616    402 30  22  3

• 尝试修改代码为csmposting.loc['TOTAL1'] = csmposting.sum(axis=1)，结果只是在表格底部添加了一行，且所有数值都带上了小数位：

Unit1   779.0   285.0   16.0    2.0 3.0
Unit2   290.0   20.0    3.0 3.0 0.0
Unit3   0.0 0.0 3.0 7.0 0.0
Unit4   547.0   97.0    8.0 10.0    0.0
TOTAL1  NaN NaN NaN NaN NaN
TOTAL   1616.0  402.0   30.0    22.0    3.0

完整报错信息 #

KeyError                                  Traceback (most recent call last)
/usr/local/lib64/python3.6/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   2897             try:
-> 2898                 return self._engine.get_loc(casted_key)
   2899             except KeyError as err:

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'TOTAL'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
/usr/local/lib64/python3.6/site-packages/pandas/core/generic.py in _set_item(self, key, value)
   3575         try:
-> 3576             loc = self._info_axis.get_loc(key)
   3577         except KeyError:

/usr/local/lib64/python3.6/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   2895                 )
-> 2896             casted_key = self._maybe_cast_indexer(key)
   2897             try:

/usr/local/lib64/python3.6/site-packages/pandas/core/indexes/category.py in _maybe_cast_indexer(self, key)
    436     def _maybe_cast_indexer(self, key):
-> 437         code = self.categories.get_loc(key)
    438         code = self.codes.dtype.type(code)

/usr/local/lib64/python3.6/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   2899             except KeyError as err:
-> 2900                 raise KeyError(key) from err
   2901 

KeyError: 'TOTAL'

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)
<ipython-input-83-abfa7a59ea03> in <module>
      7 
      8 csmposting.loc['TOTAL'] = csmposting.sum(axis=0).astype(int)
----> 9 csmposting['TOTAL'] = csmposting.sum(axis=1)   # Convert row totals to integer
     10 
     11 csmposting

/usr/local/lib64/python3.6/site-packages/pandas/core/frame.py in __setitem__(self, key, value)
   3042         else:
   3043             # set column
-> 3044             self._set_item(key, value)
   3045 
   3046     def _setitem_slice(self, key: slice, value):

/usr/local/lib64/python3.6/site-packages/pandas/core/frame.py in _set_item(self, key, value)
   3119         self._ensure_valid_index(value)
   3120         value = self._sanitize_column(key, value)
-> 3121         NDFrame._set_item(self, key, value)
   3122 
   3123         # check if we are modifying a copy

/usr/local/lib64/python3.6/site-packages/pandas/core/generic.py in _set_item(self, key, value)
   3577         except KeyError:
   3578             # This item wasn't present, just insert at end
-> 3579             self._mgr.insert(len(self._info_axis), key, value)
   3580             return
   3581 

/usr/local/lib64/python3.6/site-packages/pandas/core/internals/managers.py in insert(self, loc, item, value, allow_duplicates)
   1190 
   1191         # insert to the axis; this could possibly raise a TypeError
-> 1192         new_axis = self.items.insert(loc, item)
   1193 
   1194         if value.ndim == self.ndim - 1 and not is_extension_array_dtype(value.dtype):

/usr/local/lib64/python3.6/site-packages/pandas/core/indexes/category.py in insert(self, loc, item)
    738         if (code == -1) and not (is_scalar(item) and isna(item)):
    739             raise TypeError(
-> 740                 "cannot insert an item into a CategoricalIndex "
    741                 "that is not already an existing category"
    742             )

TypeError: cannot insert an item into a CategoricalIndex that is not already an existing category

请问如何成功添加总计列并保持原有数值格式？

报错原因分析 #

从报错信息*TypeError: cannot insert an item into a CategoricalIndex that is not already an existing category*可以看出，你的DataFrame的列索引是Categorical类型，这种索引不允许直接添加不在原有类别中的新列名（比如'TOTAL'）。

另外，你尝试的csmposting.loc['TOTAL1'] = csmposting.sum(axis=1)方向错误，loc是按行索引赋值，而你要添加的是列。

解决步骤 #

方法一：转换列索引为常规字符串类型（推荐） #

先把Categorical类型的列索引转为常规索引，之后就能自由添加新列：

csmposting = create_csm_table(csm)
# 将列索引转换为常规字符串类型
csmposting.columns = csmposting.columns.astype(str)
# 添加总计列，计算每行的和并转为整数
csmposting['TOTAL'] = csmposting.sum(axis=1).astype(int)
# 添加总计行，计算每列的和并转为整数
csmposting.loc['TOTAL'] = csmposting.sum(axis=0).astype(int)

方法二：保留Categorical列索引 #

如果必须保持列索引的Categorical类型，可以先给索引添加新类别，再插入总计列：

csmposting = create_csm_table(csm)
# 给列索引的类别列表添加'TOTAL'
csmposting.columns = csmposting.columns.add_categories('TOTAL')
# 添加总计列
csmposting['TOTAL'] = csmposting.sum(axis=1).astype(int)
# 添加总计行
csmposting.loc['TOTAL'] = csmposting.sum(axis=0).astype(int)

验证结果 #

两种方法都能得到带有整数格式的总计行和列，示例结果如下：

列1   列2  列3  列4  列5  TOTAL
Unit1   779  285  16   2   3   1085
Unit2   290   20   3   3   0    316
Unit3     0    0   3   7   0     10
Unit4   547   97   8  10   0    662
TOTAL  1616  402  30  22   3   2073

内容的提问来源于stack exchange，提问作者Syl