如何基于指定关键词将数据表单列拆分为多列？

需求说明 #

需要将数据表中的col列，在出现关键词AND、OR、PLUS的位置拆分，将拆分后的内容分别放入新列（col1、col2、col3...）中，示例如下：

原数据表A

目标数据表B

解决方案 #

1. 使用Python Pandas实现

核心思路是用正则表达式匹配关键词作为分隔符，拆分字符串后展开成多列，再和原ID列合并。

import pandas as pd
import re

# 构造原数据表
data = {
    'ID': [1,2,3,4,5,6],
    'col': [
        'THE BIG APPLE AND ORANGE OR PEAR',
        'BANNANA EATS GRAPE OR BLUEBERRY',
        'THE BEST FRUIT IS WATERMELON',
        'FRUITS OR CANDY ARE THE BEST OR WATER',
        'APPLE STRAWBERRY AND PLUM PLUS SUGAR OR PEACH',
        'MELON IN MY BELLY'
    ]
}
df = pd.DataFrame(data)

# 定义拆分的关键词正则（匹配AND/OR/PLUS，前后带空格，避免匹配单词中的子串）
split_pattern = r'\s+(AND|OR|PLUS)\s+'

# 拆分col列，得到列表形式的结果
split_cols = df['col'].str.split(split_pattern, expand=True)

# 重命名拆分后的列
split_cols.columns = [f'col{i+1}' for i in split_cols.columns]

# 和原ID列合并
result_df = pd.concat([df['ID'], split_cols], axis=1)

# 替换空值为空字符串（可选，根据需求调整）
result_df = result_df.fillna('')

print(result_df)

执行后即可得到目标数据表B的结构。

2. 使用SQL（以MySQL为例）实现

如果在数据库中处理，可以借助字符串函数和递归CTE拆分，以下是适配最多4列的实现方式：

WITH split_data AS (
    SELECT 
        ID,
        col,
        -- 第一次拆分，取第一个部分
        TRIM(SUBSTRING_INDEX(col, REGEXP_SUBSTR(col, ' AND | OR | PLUS '), 1)) AS col1,
        -- 剩余部分
        TRIM(SUBSTRING(col, LENGTH(SUBSTRING_INDEX(col, REGEXP_SUBSTR(col, ' AND | OR | PLUS '), 1)) + LENGTH(REGEXP_SUBSTR(col, ' AND | OR | PLUS ')))) AS remaining
    FROM tableA
    UNION ALL
    SELECT 
        ID,
        col,
        col1,
        TRIM(SUBSTRING_INDEX(remaining, REGEXP_SUBSTR(remaining, ' AND | OR | PLUS '), 1)) AS col2,
        TRIM(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, REGEXP_SUBSTR(remaining, ' AND | OR | PLUS '), 1)) + LENGTH(REGEXP_SUBSTR(remaining, ' AND | OR | PLUS ')))) AS remaining
    FROM split_data
    WHERE remaining != ''
    UNION ALL
    SELECT 
        ID,
        col,
        col1,
        col2,
        TRIM(SUBSTRING_INDEX(remaining, REGEXP_SUBSTR(remaining, ' AND | OR | PLUS '), 1)) AS col3,
        TRIM(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, REGEXP_SUBSTR(remaining, ' AND | OR | PLUS '), 1)) + LENGTH(REGEXP_SUBSTR(remaining, ' AND | OR | PLUS ')))) AS remaining
    FROM split_data
    WHERE remaining != '' AND col2 IS NOT NULL
    UNION ALL
    SELECT 
        ID,
        col,
        col1,
        col2,
        col3,
        TRIM(remaining) AS col4
    FROM split_data
    WHERE remaining != '' AND col3 IS NOT NULL
)
SELECT 
    ID,
    MAX(col1) AS col1,
    MAX(col2) AS col2,
    MAX(col3) AS col3,
    MAX(col4) AS col4
FROM split_data
GROUP BY ID, col
ORDER BY ID;

如果不确定最大拆分列数，可使用动态SQL适配；不同数据库的字符串函数存在差异（比如PostgreSQL用string_to_array更简便），需根据使用的数据库调整。

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