如何使用Pandas提取#之间的字符串并拼接至新列？

需要从code列中提取所有被#包裹的代码片段，将它们用逗号加空格拼接后生成新列concat。

原始数据 #

期望结果 #

方法一：Python Pandas 实现 #

用正则表达式提取#间内容后拼接成字符串：

import pandas as pd
import re

# 构造原始数据
data = {
    "id": [0,1,2,3,4,5,6,7,8,9],
    "code": [
        "(#M05Q01900R00100# = 1) AND (#M05Q01950R00200# = 0)",
        "(#M05Q01900R00100# = 1) AND ((#M05Q01950R00100# = 0) OR (#M05Q01950R00200# = 0))",
        "(#M05Q01600R00100# = 1)",
        "(#M05Q01125R00200# = 1)",
        "(#M05Q01129R00100# = 1) AND (#M05Q01130R00300# = 0)",
        "(#M05Q01130R00400# = 1)",
        "(#M05Q01132R00100# = 1)",
        "(#M05Q01132R00400# = 1)",
        "(#M05Q01132R00400# = 1)",
        "(#M05Q01140R00200# = 1)"
    ]
}

df = pd.DataFrame(data)

# 提取#间内容并拼接
def extract_and_concat(text):
    matches = re.findall(r'#(.*?)#', text)
    return ', '.join(matches)

df['concat'] = df['code'].apply(extract_and_concat)

print(df)

方法二：SQL 实现（MySQL 8.0+） #

通过递归CTE拆分匹配项后聚合拼接：

WITH RECURSIVE split_data AS (
    SELECT 
        id,
        code,
        REGEXP_SUBSTR(code, '#([^#]+)#', 1, 1, 'c', 1) AS match_val,
        1 AS match_idx
    FROM your_table
    UNION ALL
    SELECT 
        id,
        code,
        REGEXP_SUBSTR(code, '#([^#]+)#', 1, match_idx + 1, 'c', 1) AS match_val,
        match_idx + 1 AS match_idx
    FROM split_data
    WHERE match_val IS NOT NULL
)
SELECT 
    id,
    code,
    GROUP_CONCAT(match_val ORDER BY match_idx SEPARATOR ', ') AS concat
FROM split_data
GROUP BY id, code
ORDER BY id;

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