使用Pandas计算MTD与YTD累计平均值报错，求解决方案

问题场景 #

想要使用Pandas DataFrame计算MTD（月初至今）和YTD（年初至今）的累计平均值，编写代码后出现索引不兼容错误，代码及报错信息如下：

原代码 #

import pandas as pd

# MTD AND YTD calculation

data = {'date' : ['2016/11/01', '2016/11/02', '2016/11/03', '2016/11/04', '2016/11/05', '2016/11/05', '2016/12/01', '2016/12/02', '2016/12/03', '2016/12/04', '2016/12/15', '2016/12/20', '2016/12/23', '2016/12/30','2017/01/01', '2017/01/02', '2017/01/03', '2017/01/04', '2017/01/15', '2017/01/20', '2017/01/23', '2017/01/30','2017/01/01', '2017/01/02', '2017/01/03', '2017/01/04', '2017/01/15', '2017/01/20', '2017/01/23', '2017/01/30', '2017/04/01', '2017/04/02', '2017/04/03', '2017/04/04', '2017/04/15', '2017/04/20', '2017/04/23', '2017/04/30','2017/04/01', '2017/04/02', '2017/04/03', '2017/04/04', '2017/04/15', '2017/04/20', '2017/04/23', '2017/04/30', '2017/05/01', '2017/05/02', '2017/05/03', '2017/05/04', '2017/05/15', '2017/05/20', '2017/05/23', '2017/05/30','2017/05/01', '2017/05/02', '2017/05/03', '2017/05/04', '2017/05/15', '2017/05/20', '2017/05/23', '2017/05/30'],
        'category': ['fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit'],

        'product': ['grapes', 'grapes', 'grapes', 'kiwi', 'kiwi', 'grapes', 'Apple', 'Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple', 'Orange', 'Orange', 'Orange','Orange', 'Orange', 'Orange','Orange', 'Orange', 'Apple', 'Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple', 'Orange', 'Orange', 'Orange','Orange', 'Orange', 'Orange','Orange', 'Orange', 'Apple', 'Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple', 'Orange', 'Orange', 'Orange','Orange', 'Orange', 'Orange','Orange', 'Orange'],
        'price': [10, 10, 20, 40, 60, 30, 10, 20, 10, 50, 10, 5, 10, 10, 10, 20, 10, 50, 10, 5, 10, 10, 20, 10, 5, 5, 10, 10, 20, 50, 10, 5, 20, 10, 10, 20, 50, 20, 5, 5, 10, 10, 20, 50, 30, 10, 20, 5, 5, 10, 20, 10, 20, 10, 40, 20, 10, 10, 20, 20, 10, 5]}


df = pd.DataFrame(data)

df.date = pd.to_datetime(df.date)
df['MTD'] = df.groupby([df.date.dt.to_period('m'),'product', 'category']).price.expanding().mean()
df['YTD'] = df.groupby([df.date.dt.to_period('A-MAR'),'product', 'category']).price.expanding().mean()

print(df)

报错信息 #

Traceback (most recent call last):
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/frame.py", line 11000, in _reindex_for_setitem
    reindexed_value = value.reindex(index)._values
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/series.py", line 4672, in reindex
    return super().reindex(**kwargs)
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/generic.py", line 4966, in reindex
    return self._reindex_axes(
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/generic.py", line 4981, in _reindex_axes
    new_index, indexer = ax.reindex(
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/indexes/base.py", line 4223, in reindex
    target = self._wrap_reindex_result(target, indexer, preserve_names)
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/indexes/multi.py", line 2520, in _wrap_reindex_result
    target = MultiIndex.from_tuples(target)
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/indexes/multi.py", line 204, in new_meth
    return meth(self_or_cls, *args, **kwargs)
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/indexes/multi.py", line 559, in from_tuples
    arrays = list(lib.tuples_to_object_array(tuples).T)
  File "pandas/_libs/lib.pyx", line 2930, in pandas._libs.lib.tuples_to_object_array
ValueError: Buffer dtype mismatch, expected 'Python object' but got 'long'

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

Traceback (most recent call last):
  File "/home/ab/parry-data_processing/pa/poc.py", line 21, in <module>
    df['MTD'] = df.groupby([df.date.dt.to_period('m'),'product', 'category']).price.expanding().mean()
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/frame.py", line 3655, in __setitem__
    self._set_item(key, value)
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/frame.py", line 3832, in _set_item
    value = self._sanitize_column(value)
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/frame.py", line 4532, in _sanitize_column
    return _reindex_for_setitem(value, self.index)
  File "/home/ab/PycharmProjects/pa/lib/python3.9/site-packages/pandas/core/frame.py", line 11007, in _reindex_for_setitem
    raise TypeError(
TypeError: incompatible index of inserted column with frame index

错误原因 #

原代码中，groupby后调用expanding().mean()会返回一个多级索引Series，索引包含分组键（月份/财年、product、category）和原DataFrame的行索引。而原DataFrame是单级整数索引，直接将这个多级索引Series赋值给新列时，Pandas无法匹配两者的索引，因此抛出不兼容错误。

解决方案 #

使用groupby.transform()方法替代直接赋值。transform会将分组计算后的结果映射回原DataFrame的每一行，返回的Series索引与原DataFrame完全匹配，可直接赋值到新列。

修正后的代码 #

import pandas as pd

# MTD AND YTD calculation

data = {'date' : ['2016/11/01', '2016/11/02', '2016/11/03', '2016/11/04', '2016/11/05', '2016/11/05', '2016/12/01', '2016/12/02', '2016/12/03', '2016/12/04', '2016/12/15', '2016/12/20', '2016/12/23', '2016/12/30','2017/01/01', '2017/01/02', '2017/01/03', '2017/01/04', '2017/01/15', '2017/01/20', '2017/01/23', '2017/01/30','2017/01/01', '2017/01/02', '2017/01/03', '2017/01/04', '2017/01/15', '2017/01/20', '2017/01/23', '2017/01/30', '2017/04/01', '2017/04/02', '2017/04/03', '2017/04/04', '2017/04/15', '2017/04/20', '2017/04/23', '2017/04/30','2017/04/01', '2017/04/02', '2017/04/03', '2017/04/04', '2017/04/15', '2017/04/20', '2017/04/23', '2017/04/30', '2017/05/01', '2017/05/02', '2017/05/03', '2017/05/04', '2017/05/15', '2017/05/20', '2017/05/23', '2017/05/30','2017/05/01', '2017/05/02', '2017/05/03', '2017/05/04', '2017/05/15', '2017/05/20', '2017/05/23', '2017/05/30'],
        'category': ['fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit',
                    'fruit', 'fruit', 'fruit', 'fruit', 'fruit'],

        'product': ['grapes', 'grapes', 'grapes', 'kiwi', 'kiwi', 'grapes', 'Apple', 'Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple', 'Orange', 'Orange', 'Orange','Orange', 'Orange', 'Orange','Orange', 'Orange', 'Apple', 'Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple', 'Orange', 'Orange', 'Orange','Orange', 'Orange', 'Orange','Orange', 'Orange', 'Apple', 'Apple', 'Apple','Apple', 'Apple', 'Apple','Apple', 'Apple', 'Orange', 'Orange', 'Orange','Orange', 'Orange', 'Orange','Orange', 'Orange'],
        'price': [10, 10, 20, 40, 60, 30, 10, 20, 10, 50, 10, 5, 10, 10, 10, 20, 10, 50, 10, 5, 10, 10, 20, 10, 5, 5, 10, 10, 20, 50, 10, 5, 20, 10, 10, 20, 50, 20, 5, 5, 10, 10, 20, 50, 30, 10, 20, 5, 5, 10, 20, 10, 20, 10, 40, 20, 10, 10, 20, 20, 10, 5]}


df = pd.DataFrame(data)

df.date = pd.to_datetime(df.date)

# 计算MTD累计平均值：按月份、product、category分组，用transform映射回原行
df['MTD'] = df.groupby([df.date.dt.to_period('m'), 'product', 'category'])['price'].transform(lambda x: x.expanding().mean())

# 计算YTD累计平均值：按财年（到3月结束）、product、category分组，用transform映射回原行
df['YTD'] = df.groupby([df.date.dt.to_period('A-MAR'), 'product', 'category'])['price'].transform(lambda x: x.expanding().mean())

print(df)

说明 #

• transform接收一个函数，这里用lambda x: x.expanding().mean()对每个分组内的price列计算累计平均值。
• 返回的结果与原DataFrame行数一致，索引完全匹配，因此可以直接赋值给新列`