Pandas按列名分组求和触发ValueError: len(index)!=len(labels)问题

问题场景 #

需求：对列名为浮点数的Pandas DataFrame，按列名四舍五入后的整数分组并求和。

最小可复现示例（MWE） #

import pandas as pd
temp = pd.DataFrame({911.7: {0: 0, 1: 1}, 911.9: {0: 2.0, 1: 0.0}, 912.0: {0: 0.5, 1: 0.5}})
round_to = 1
price_digits=1
rounded = [round(round(x / round_to) * round_to, price_digits) for x in temp.columns]
temp.groupby(by=rounded, axis=1).sum()

预期输出 #

912
0  2.5
1  1.5

实际报错 #

Traceback (most recent call last):
  File "D:\Anaconda3\lib\site-packages\IPython\core\interactiveshell.py", line 3331, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-17-983fbc3f7113>", line 1, in <module>
    temp.groupby(by=rounded, axis=1).sum()
  File "D:\Anaconda3\lib\site-packages\pandas\core\groupby\groupby.py", line 1378, in f
    return self._cython_agg_general(alias, alt=npfunc, **kwargs)
  File "D:\Anaconda3\lib\site-packages\pandas\core\groupby\generic.py", line 1004, in _cython_agg_general
    how, alt=alt, numeric_only=numeric_only, min_count=min_count
  File "D:\Anaconda3\lib\site-packages\pandas\core\groupby\generic.py", line 1033, in _cython_agg_blocks
    block.values, how, axis=1, min_count=min_count
  File "D:\Anaconda3\lib\site-packages\pandas\core\groupby\ops.py", line 587, in aggregate
    "aggregate", values, how, axis, min_count=min_count
  File "D:\Anaconda3\lib\site-packages\pandas\core\groupby\ops.py", line 530, in _cython_operation
    result, counts, values, codes, func, is_datetimelike, min_count
  File "D:\Anaconda3\lib\site-packages\pandas\core\groupby\ops.py", line 608, in _aggregate
    agg_func(result, counts, values, comp_ids, min_count)
  File "pandas\_libs\groupby.pyx", line 464, in pandas._libs.groupby._group_add
ValueError: len(index)!=len(labels)

特殊情况 #

当把第三列名改为912.3时，原代码可正常运行并得到预期输出：

import pandas as pd
round_to = 1
price_digits=1
temp = pd.DataFrame({911.7: {0: 0, 1: 1}, 911.9: {0: 2.0, 1: 0.0}, 912.3: {0: 0.5, 1: 0.5}})
rounded = [round(round(x / round_to) * round_to, price_digits) for x in temp.columns]
temp.groupby(by=rounded, axis=1).sum()

输出：

Out[14]: 
   912
0  2.5
1  1.5

报错原因 #

在Pandas 1.0.1版本中存在bug：当分组标签包含重复的浮点数键，且其中某个键与原DataFrame的列名完全一致时，内部处理会混淆原列索引与分组标签的对应关系，从而触发len(index)!=len(labels)错误。

原示例中，原列名包含912.0，而四舍五入后的分组标签也全为912.0，刚好触发该bug；修改第三列名为912.3后，原列名中没有912.0，bug不触发。

解决方案 #

方案1：将分组标签转为整数类型 #

因为需求是四舍五入到整数，直接把分组标签转为int类型，避免浮点数键的冲突：

import pandas as pd
temp = pd.DataFrame({911.7: {0: 0, 1: 1}, 911.9: {0: 2.0, 1: 0.0}, 912.0: {0: 0.5, 1: 0.5}})
round_to = 1
# 直接转换为整数，规避浮点数键冲突
rounded = [int(round(x / round_to) * round_to) for x in temp.columns]
result = temp.groupby(by=rounded, axis=1).sum()
print(result)

输出符合预期：

912
0  2.5
1  1.5

方案2：先重命名列再分组 #

先将原列名替换为四舍五入后的值，再按列名分组求和（通用场景下适用）：

import pandas as pd
temp = pd.DataFrame({911.7: {0: 0, 1: 1}, 911.9: {0: 2.0, 1: 0.0}, 912.0: {0: 0.5, 1: 0.5}})
round_to = 1
price_digits=1
# 重命名所有列
temp_renamed = temp.rename(columns=lambda x: round(round(x / round_to) * round_to, price_digits))
# 按列名分组求和
result = temp_renamed.groupby(by=temp_renamed.columns, axis=1).sum()
print(result)

方案3：升级Pandas版本 #

该bug在Pandas 1.1.0及以上版本中已被修复，升级到新版本后，原代码可直接正常运行。

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