如何在Pandas DataFrame中按同一字符批量拆分多列

问题场景 #

有多列需要按冒号（:）拆分，部分列的记录含2个冒号（需拆成3段），部分含1个冒号（需拆成2段）。当前逐个列编写拆分代码的方式过于繁琐，希望实现批量处理。

示例数据 #

DATE      TALK_TIME   CONSULT_TIME
0     2023-11-21       NaN        05:10 
1     2023-11-21  00:04:16          NaN
2     2023-11-21       NaN          NaN
3     2023-11-21  00:24:30          NaN
4     2023-11-21  00:04:08          NaN

现有繁琐代码 #

# doing this for every column that needs to split.. very meticulous

df[['TALK_TIME1', 'TALK_TIME2', 'TALK_TIME3']] = df['TALK_TIME'].str.split(':', expand=True)
df[['CONSULT_TIME1', 'CONSULT_TIME2']] = df['CONSULT_TIME'].str.split(':', expand=True)

期望输出 #

DATE         TALK_TIME1   TALK_TIME2  TALK_TIME3  CONSULT_TIME1  CONSULT_TIME2
0     2023-11-21        NaN       NaN        NaN            05             10
1     2023-11-21        00         04         16           NaN             NaN
2     2023-11-21        NaN       NaN        NaN           NaN             NaN
3     2023-11-21        00         24         30           NaN             NaN
4     2023-11-21        00         04         08           NaN             NaN

高效批量拆分方案 #

通过循环遍历目标列，自动完成拆分、命名新列并合并到原DataFrame，无需逐个列手动配置：

import pandas as pd

# 构造示例数据（实际使用时替换为你的DataFrame）
data = {
    'DATE': ['2023-11-21'] * 5,
    'TALK_TIME': [pd.NA, '00:04:16', pd.NA, '00:24:30', '00:04:08'],
    'CONSULT_TIME': ['05:10 ', pd.NA, pd.NA, pd.NA, pd.NA]
}
df = pd.DataFrame(data)

# 指定需要拆分的列名列表
cols_to_split = ['TALK_TIME', 'CONSULT_TIME']

for col in cols_to_split:
    # 按冒号拆分列，expand=True自动生成多列
    split_result = df[col].str.split(':', expand=True)
    # 给拆分后的列命名：原列名+序号（从1开始）
    split_result.columns = [f"{col}{i+1}" for i in split_result.columns]
    # 将拆分后的列合并到原DataFrame
    df = pd.concat([df, split_result], axis=1)

# 可选：移除原拆分列，保留拆分后的新列
df = df.drop(cols_to_split, axis=1)

print(df)

代码说明

• 只需维护cols_to_split列表，新增需要拆分的列直接添加即可，无需修改循环逻辑
• 自动适配不同的拆分长度：原列含2个冒号时生成3个新列，含1个冒号时生成2个新列
• 拆分后的列名规则统一为原列名+数字序号，避免手动命名的麻烦

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