使用Pandas识别DataFrame中的5分制与10分制调研问题

需求 #

我把包含5分制和10分制的调研问题整合到了一个DataFrame里，想新增一列Type_of_Question，自动判断每个问题属于哪种评分制，没有对应刻度标识的问题就留空。需要兼容多种刻度表述形式，比如1–10、1 to 10、1 10这类不同写法。

示例输入（DataFrame的Question_Text列） #

on a scale of 1 – 10 how well would you rate the following statements.
on a scale of 1 to 10 how well would you rate the following statements.
on a scale of 1-10 how well would you rate the following statements.
on a scale of 1 10 how well would you rate the following statements.
on a scale of 1 – 5 how well would you rate the following statements.
on a scale of 1 to 5 how well would you rate the following statements.
on a scale of 1-5 how well would you rate the following statements.
on a scale of 1 5 how well would you rate the following statements.
please tell us how ready you feel for this (0 - 6 not ready, 6-8 somewhat ready, and 9-10 ready)
how useful did you find the today’s webinar?

预期输出（新增Type_of_Question列对应值） #

• 前4行：10 point scale
• 接下来4行：5 point scale
• 第9行：空值（多区间刻度，不属于标准5/10分制）
• 第10行：空值（无任何刻度标识）

实现方案：用正则表达式搞定 #

完全可以用正则实现，核心是匹配文本里1和5/10的组合，兼容各种分隔符（短横、长横、空格、to）。

Python代码示例 #

import pandas as pd
import re

# 构造示例数据
data = {
    'Question_Text': [
        'on a scale of 1 – 10 how well would you rate the following statements.',
        'on a scale of 1 to 10 how well would you rate the following statements.',
        'on a scale of 1-10 how well would you rate the following statements.',
        'on a scale of 1 10 how well would you rate the following statements.',
        'on a scale of 1 – 5 how well would you rate the following statements.',
        'on a scale of 1 to 5 how well would you rate the following statements.',
        'on a scale of 1-5 how well would you rate the following statements.',
        'on a scale of 1 5 how well would you rate the following statements.',
        'please tell us how ready you feel for this (0 - 6 not ready, 6-8 somewhat ready, and 9-10 ready)',
        'how useful did you find the today’s webinar?'
    ]
}
df = pd.DataFrame(data)

# 定义判断函数，用正则匹配刻度
def get_scale_type(text):
    # 匹配10分制：1 + 任意分隔符 + 10，忽略大小写
    if re.search(r'1\s*(?:-|–|to|\s)\s*10', text, re.IGNORECASE):
        return '10 point scale'
    # 匹配5分制：1 + 任意分隔符 + 5，忽略大小写
    elif re.search(r'1\s*(?:-|–|to|\s)\s*5', text, re.IGNORECASE):
        return '5 point scale'
    # 不匹配就返回空
    else:
        return ''

# 新增列
df['Type_of_Question'] = df['Question_Text'].apply(get_scale_type)

# 查看结果
print(df)

正则规则说明 #

• 1\s*：匹配数字1，后面允许任意数量的空格
• (?:-|–|to|\s)：匹配各种分隔符：短横(-)、长横(–)、to（不区分大小写）、空格，用非捕获组避免多余匹配结果
• \s*10/\s*5：匹配任意空格后接目标数字
• re.IGNORECASE：兼容To、TO这类大小写混合的写法

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