Pandas中TypeError: unhashable type: 'Series'报错排查求助

问题背景 #

• 两个DataFrame：
  • merged_df：889行数据，8列均为object类型，需新增Action列
  • SAP_only_EAs：仅包含UNIQUE_ID列，共64行
• 通过merged_df.apply()调用自定义函数action_col生成Action列，当函数中加入elif uniqueID in SAP_only_EAs['UNIQUE_ID']判断时，触发TypeError: unhashable type: 'Series'；注释该条件后代码正常运行
• 曾尝试将SAP_only_EAs['UNIQUE_ID']转为列表，但未解决问题

报错原因 #

直接使用x in pd.Series()时，Pandas默认行为是检查x是否存在于Series的索引而非值中。此外，Series作为容器进行成员检查时，若传入的uniqueID被识别为不可哈希类型（如内部处理时误判为Series），会触发哈希错误。同时，直接对Series做成员检查的效率远低于集合。

解决方案 #

1. 提前将SAP_only_EAs['UNIQUE_ID']转换为集合（集合的成员检查时间复杂度为O(1)，远优于列表和Series）
2. 在action_col函数中使用该集合进行判断，而非直接操作Series

修改后的完整代码 #

import pandas as pd
import datetime
from dateutil.relativedelta import relativedelta

def current_month():
    return datetime.datetime.now().strftime("%m/%Y")

def get_current_date():
    return datetime.datetime.now().strftime("%d/%m/%Y")

def three_month_ahead():
    current = datetime.datetime.now()
    three_month = current + relativedelta(months=3)
    return three_month.strftime("%m/%Y")

def next_month_expiry():
    current = datetime.datetime.now()
    nextmonth = current + relativedelta(months=1)
    return nextmonth.strftime("%m/%Y")

def year_ahead():
    current = datetime.datetime.now()
    year_on = current + relativedelta(months = 12)
    return year_on.strftime("%d/%m/%Y")

# 将UNIQUE_ID转换为集合，用于高效成员检查
sap_only_eas_set = set(SAP_only_EAs['UNIQUE_ID'].tolist())

def action_col(expiry_date, Status, uniqueID):
    three_months_ahead = pd.to_datetime(three_month_ahead(), format='%m/%Y')
    next_month = pd.to_datetime(next_month_expiry(), format='%m/%Y')
    current_date_today = pd.to_datetime(get_current_date(), format='%d/%m/%Y')
    year_on = pd.to_datetime(year_ahead(), format='%d/%m/%Y')

    expiry_date = pd.to_datetime(expiry_date, format='%d/%m/%Y')  # 转换到期日为datetime类型

    if f"{expiry_date.year}-{expiry_date.month:02}" == f"{three_months_ahead.year}-{three_months_ahead.month:02}":
        return 'Send 3 month request'
    elif expiry_date.month == next_month.month and expiry_date.year == next_month.year:
        return 'Send 1 month reminder'
    elif expiry_date < current_date_today and Status == "Not Suspended":
        return 'DBS expired: Suspend & update iAdmin notes'
    elif (year_on < expiry_date < current_date_today) and Status == "Suspended":
        return 'No action needed - correct suspensions in place'
    elif uniqueID in sap_only_eas_set:  # 使用集合进行成员判断
        return 'No action needed - SAP only assessor'
    elif (expiry_date < year_on) and Status == "Suspended":
        return 'DBS expired for over a year – look at whether account closure is appropriate'
    else:
        return 'No action required – valid DBS check'


merged_df['Action'] = merged_df.apply(lambda row: action_col(row['EXPIRY_DATE'], row['Status'], row['UNIQUE_ID']), axis=1)

原始报错信息 #

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
c:\Users\orla_quidos\Code\DBS project\STATUS section of the scheduled job python script.ipynb Cell 9 line 5
     48     else:
     49         return 'No action required – valid DBS check'
---> 51 merged_df['Action'] = merged_df.apply(lambda row: action_col(row['EXPIRY_DATE'], row['Status'], row['UNIQUE_ID']), axis=1)

File c:\Users\orla_quidos\anaconda3\lib\site-packages\pandas\core\frame.py:9568, in DataFrame.apply(self, func, axis, raw, result_type, args, **kwargs)
   9557 from pandas.core.apply import frame_apply
   9559 op = frame_apply(
   9560     self,
   9561     func=func,
   (...)
   9566     kwargs=kwargs,
   9567 )
-> 9568 return op.apply().__finalize__(self, method="apply")

File c:\Users\orla_quidos\anaconda3\lib\site-packages\pandas\core\apply.py:764, in FrameApply.apply(self)
    761 elif self.raw:
    762     return self.apply_raw()
-> 764 return self.apply_standard()

File c:\Users\orla_quidos\anaconda3\lib\site-packages\pandas\core\apply.py:891, in FrameApply.apply_standard(self)
    890 def apply_standard(self):
-> 891     results, res_index = self.apply_series_generator()
...
-> 371     hash(key)
    372     try:
    373         key = ensure_python_int(key)

TypeError: unhashable type: 'Series'

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