LangChain Pandas Agent未遵循指令问题排查与优化咨询

问题概述 #

使用基于Azure OpenAI GPT-4的LangChain Pandas Agent处理员工数据DataFrame时，存在两个核心问题：

• 子串查询匹配错误：查询如“John Doe”这类姓名时，Agent执行完整子串匹配df['NAME'].str.contains('John Doe', na=ignore, case=False)，无法匹配“John W. Doe”“John Jr. Doe”这类含中间缀的姓名，正确逻辑应为拆分关键词组合查询：df['NAMES'].str.contains('John', na=False, case=False) & df['NAMES'].str.contains('Doe', na=False, case=False)
• 并列极值未处理：查询周工时最高/最低员工时，即使要求检查并列，Agent仍执行df['DURATION_HOURS_WEEK'].nlargest(1)仅返回单条结果，忽略并列情况。

现有配置 #

前缀提示词 #

You are a pandas agent. You must work with the DataFrame df containing information about the company's employees.

Your answer must only include information retrieved from df, and you must not create mockup or sample data. You will be penalized if you do.

The user may ask you questions using a substring of the names of our employees.

Follow these useful instructions when retrieving information regarding an employee name:

If an exact match is found, retrieve the information in natural language.

If not, then include a str.contains search ignoring NaNs and case insensitive in this fashion. For example, if they ask for Alice West, look for:

df['NAMES'].str.contains('alice', case=False, na=False) & df['NAMES'].str.contains('west', case=False, na=False)

and retrieve the information found. If we have more than 20 rows, just retrieve the information on the first 20 rows.

When sorting information like retrieving the highest or lowest values of a column, always check for ties. If there are ties, retrieve the first 3 rows of information.

For instance, if the maximum hours of weekly work happens to be 10 but more than one employee has that, then print up to 20 rows.

后缀提示词 #

You must answer in natural language and must never make up information. You will be penalized if you do.

核心代码 #

import pandas as pd
from langchain.agents import create_pandas_dataframe_agent, AgentType
from langchain.chat_models import AzureChatOpenAI

# 假设_model已初始化
# _model = AzureChatOpenAI(...)

data = {
    "NAMES": ["John W. Doe", "Alice Smith", "John Adams Jr.", "Alice Johnson", "John Jr. Doe"],
    "CITY": ["New York", "Los Angeles", "Chicago", "Houston", "Chicago"],
    "STATION": ["Station A", "Station B", "Station C", "Station D", "Station E"],
    "STARTING_YEAR": [2015, 2017, 2015, 2018, 2019],
    "DURATION_HOURS_WEEK": [40, 35, 40, 30, 45]
}

df = pd.DataFrame(data)

agent = create_pandas_dataframe_agent(
            llm=_model,
            df=df,
            suffix=suffix,
            include_df_in_prompt=True,
            agent_type=AgentType.OPENAI_FUNCTIONS,
            prefix=prefix,
            max_iterations=5,
            verbose=True)

解决方案建议 #

方案1：优化提示词（低成本快速调整） #

强化指令的强制性，避免LLM忽略规则：

• 姓名查询部分：明确要求“必须将用户输入的姓名拆分为独立关键词（如将‘John Doe’拆为‘John’和‘Doe’），分别对NAMES列执行不区分大小写、忽略NaN的str.contains查询，再用&组合条件，禁止使用完整子串进行匹配”
• 极值处理部分：明确要求“先获取目标列的极值（最大值/最小值），再筛选所有列值等于该极值的记录，若结果超过3条则返回前3条，最多不超过20条”

修改后的前缀核心片段示例：

Follow these NON-NEGOTIABLE instructions when handling queries:
1. For employee name queries:
   - First check for exact matches in the `NAMES` column.
   - If no exact match, split the input name into individual keywords (e.g., "John Doe" becomes ["John", "Doe"]).
   - For each keyword, run `df['NAMES'].str.contains(keyword, case=False, na=False)`, then combine all conditions with `&`.
   - Return up to 20 matching rows.
2. For max/min value queries (e.g., highest weekly hours):
   - First calculate the extreme value (max or min) of the target column.
   - Filter all rows where the column equals this extreme value.
   - Return up to 3 rows (or up to 20 if there are many ties).

方案2：自定义工具/函数（更可靠，强制逻辑执行） #

自定义工具可以将核心逻辑固化，避免LLM偏离规则，是更优的长期方案。示例如下：

步骤1：定义自定义工具

from langchain.tools import tool

@tool
def search_employee_by_name_keywords(keywords: list[str]) -> pd.DataFrame:
    """
    按姓名关键词查询员工信息，支持多个关键词组合匹配
    参数：
        keywords: 姓名拆分后的关键词列表（如["John", "Doe"]）
    """
    mask = pd.Series([True]*len(df))
    for keyword in keywords:
        mask &= df['NAMES'].str.contains(keyword, case=False, na=False)
    return df[mask].head(20)

@tool
def get_extreme_value_employees(column: str, mode: str) -> pd.DataFrame:
    """
    获取指定列极值（最高/最低）对应的员工，自动处理并列情况
    参数：
        column: 目标列名（如"DURATION_HOURS_WEEK"）
        mode: 取值"max"或"min"，表示查询最高或最低值
    """
    if mode == "max":
        extreme_val = df[column].max()
    elif mode == "min":
        extreme_val = df[column].min()
    else:
        raise ValueError("mode must be 'max' or 'min'")
    result = df[df[column] == extreme_val]
    return result.head(20)

步骤2：创建带自定义工具的Agent

from langchain.agents import AgentExecutor, Tool
from langchain.prompts import ChatPromptTemplate
from langchain.agents import create_openai_functions_agent

# 定义工具列表
tools = [
    Tool(
        name="search_employee_by_name_keywords",
        func=search_employee_by_name_keywords,
        description="当需要按姓名关键词查询员工时使用，接收拆分后的关键词列表"
    ),
    Tool(
        name="get_extreme_value_employees",
        func=get_extreme_value_employees,
        description="当需要查询某列最高或最低值对应的员工时使用，接收列名和'max'/'min'模式"
    )
]

# 构建提示词
prompt = ChatPromptTemplate.from_messages([
    ("system", "你是处理员工数据的助手，必须使用提供的工具来回答问题，不得自行编写Pandas代码"),
    ("user", "{input}"),
    ("assistant", "{agent_scratchpad}")
])

# 创建Agent
agent = create_openai_functions_agent(_model, tools, prompt)
agent_executor = AgentExecutor(agent=agent, tools=tools, verbose=True)

# 使用示例
agent_executor.invoke({"input": "查询名字包含John和Doe的员工周工时"})
agent_executor.invoke({"input": "查询周工时最高的员工，包含并列情况"})

总结 #

• 若只是临时调整，优化提示词能快速见效，但存在LLM仍可能忽略规则的风险；
• 自定义工具/函数能将核心逻辑强制固化，确保Agent严格遵循规则，是长期更可靠的方案，尤其适合业务逻辑固定的场景。

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