如何用Python函数将标准化字符串转换为Pandas查询语句？

核心规则梳理 #

先明确转换需遵循的固定规则：

• 仅提取**列名(columnX) + 比较描述 + 数值(nX)**的完整条件单元，孤立的columnX/nX（无对应比较逻辑）直接忽略
• 逻辑连接符映射：文本中出现or则用|，默认（含and或无逻辑词）用&
• 比较描述与运算符映射：
  • greater than → >
  • less than → <
  • equal to → ==
  • less than or equal to → <=
  • greater than or equal to → >=
• 同一列的多条件（如column2 less than n1 and greater than n2）需拆分为独立条件后用&连接

实现步骤（基于正则与字符串处理） #

1. 文本预处理 #

先去除输入中的HTML标签（如<p>、<strong>），将所有文本转为小写，统一格式以便后续匹配。

2. 提取有效条件单元 #

用正则表达式精准匹配完整的条件片段，正则模式：

(column\d+)\s+(greater than|less than|equal to|less than or equal to|greater than or equal to)\s+(n\d+)

该模式可捕获所有符合列名+比较描述+数值结构的有效条件。

3. 运算符映射 #

通过字典完成自然语言描述到Pandas运算符的转换：

op_map = {
    "greater than": ">",
    "less than": "<",
    "equal to": "==",
    "less than or equal to": "<=",
    "greater than or equal to": ">="
}

4. 逻辑连接符判断 #

检查预处理后的文本中是否包含or：

• 含or则用|连接条件
• 否则默认用&连接

5. 组装查询语句 #

将每个条件转为df["columnX"] op nX格式，用逻辑连接符拼接后包裹成最终的Pandas索引查询结构。

完整代码示例 #

import re

def text_to_pandas_query(raw_text):
    # 预处理：移除HTML标签，统一转小写
    clean_text = re.sub(r'<[^>]+>', '', raw_text).lower()
    
    # 匹配所有有效条件单元
    condition_pattern = r'(column\d+)\s+(greater than|less than|equal to|less than or equal to|greater than or equal to)\s+(n\d+)'
    matched_conditions = re.findall(condition_pattern, clean_text)
    
    # 运算符映射字典
    op_mapping = {
        "greater than": ">",
        "less than": "<",
        "equal to": "==",
        "less than or equal to": "<=",
        "greater than or equal to": ">="
    }
    
    # 生成单个条件字符串
    parsed_conditions = []
    for col_name, op_desc, val in matched_conditions:
        op = op_mapping[op_desc]
        parsed_conditions.append(f'df["{col_name}"]{op}{val}')
    
    # 确定逻辑连接符
    if 'or' in clean_text:
        joiner = ') | ('
    else:
        joiner = ') & ('
    
    # 组装最终查询语句
    if parsed_conditions:
        final_query = f'df[({joiner.join(parsed_conditions)})]'
    else:
        final_query = 'df[]'  # 无有效条件时返回空切片
    return final_query

# 测试示例
test_cases = [
    "some text column4 greater than n1 some text column2 less than n3 some other text column5 equal to n6",
    "some text column2 less than or equal to n1 some text contains or somewhere column5 greater than n2",
    "column2 less than n1 and greater than n2 some text column5 greater than n3"
]

for case in test_cases:
    print(text_to_pandas_query(case))

测试输出 #

对应测试用例的输出结果：

df[(df["column4"]>n1) & (df["column2"]<n3) & (df["column5"]==n6)]
df[(df["column2"]<=n1) | (df["column5"]>n2)]
df[(df["column2"]<n1) & (df["column2"]>n2) & (df["column5"]>n3)]

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