解决DataFrame日期列转Period('M')报错及分组聚合问题

我知道示例代码看起来效率不高，但实际场景中需要对某列应用正则函数，因此必须通过iterrows处理。现在的问题是：如何将data3 DataFrame的date列转换为Period('M')类型，以正常执行groupby操作？

相关代码 #

import pandas as pd
import datetime as dt
ts = dt.datetime.now()

data = pd.DataFrame({
    'status' :  ['pending', 'pending','pending'],
    'brand' : ['brand_1', 'brand_2', 'brand_3'],
    'date' : [pd.Timestamp(dt.datetime.now()),pd.Timestamp(dt.datetime.now()),pd.Timestamp(dt.datetime.now())]})

data2 = list()
for index, row in data.iterrows():
    a = row['status']
    b = row['brand']
    c = row['date'].date()
    data2.append((a,b,c))

data3 = pd.DataFrame(data=data2,columns=['status','brand','date'])

data3数据展示 #

status    brand        date
0  pending  brand_1  2023-08-04
1  pending  brand_2  2023-08-04
2  pending  brand_3  2023-08-04

报错的分组代码 #

a = data3.groupby([data3['date'].dt.to_period('M')], observed=True).aggregate({'brand':'count'})

报错信息 #

AttributeError                            Traceback (most recent call last)
Cell In[26], line 1
----> 1 a = data3.groupby([data3['date'].dt.to_period('M')], observed=True).aggregate({'brand':'count'})
      2 a

File c:\Users\fabio\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\core\generic.py:5989, in NDFrame.__getattr__(self, name)
   5982 if (
   5983     name not in self._internal_names_set
   5984     and name not in self._metadata
   5985     and name not in self._accessors
   5986     and self._info_axis._can_hold_identifiers_and_holds_name(name)
   5987 ):
   5988     return self[name]
-> 5989 return object.__getattribute__(self, name)

File c:\Users\fabio\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\core\accessor.py:224, in CachedAccessor.__get__(self, obj, cls)
    221 if obj is None:
    222     # we're accessing the attribute of the class, i.e., Dataset.geo
    223     return self._accessor
-> 224 accessor_obj = self._accessor(obj)
    225 # Replace the property with the accessor object. Inspired by:
    226 # https://www.pydanny.com/cached-property.html
    227 # We need to use object.__setattr__ because we overwrite __setattr__ on
    228 # NDFrame
...
    577 elif is_period_dtype(data.dtype):
    578     return PeriodProperties(data, orig)
-> 580 raise AttributeError("Can only use .dt accessor with datetimelike values")

AttributeError: Can only use .dt accessor with datetimelike values

问题原因 #

data3的date列存储的是Python原生datetime.date对象，而非pandas的datetime64类型，因此无法使用.dt访问器来转换周期。

解决方案 #

方案1：构建data3时保留pandas Timestamp类型 #

如果不需要将日期转成原生date对象，直接保留原有的pandas Timestamp：

data2 = list()
for index, row in data.iterrows():
    a = row['status']
    b = row['brand']
    c = row['date']  # 不调用.date()，保留Timestamp类型
    data2.append((a,b,c))

data3 = pd.DataFrame(data=data2,columns=['status','brand','date'])
# 直接执行分组
a = data3.groupby(data3['date'].dt.to_period('M'), observed=True).agg({'brand':'count'})

方案2：先将date列转换为pandas datetime类型 #

如果必须保留原生date对象的处理步骤，后续可以通过pd.to_datetime转换为pandas支持的日期类型：

# 转换date列为datetime64类型
data3['date'] = pd.to_datetime(data3['date'])
# 执行分组
a = data3.groupby(data3['date'].dt.to_period('M'), observed=True).agg({'brand':'count'})

方案3：直接将原生date对象转为Period #

通过apply和pd.Period直接构造月度周期列：

# 添加月度周期列
data3['month_period'] = data3['date'].apply(lambda x: pd.Period(x, freq='M'))
# 按新列分组
a = data3.groupby('month_period', observed=True).agg({'brand':'count'})

内容的提问来源于stack exchange，提问作者FábioRB