Pandas:按多个分隔符对列进行排序和拆分

Question

我有一个数据帧，它有一个非常不一致的列。例如：

df = pd.DataFrame(columns=["CID", "CM"], data=[['xxx-1','skill_start=skill1,skill2,||skill_complete=skill1,'],['xxx-2','survey=1||skill_start=skill1,skill3||skill_complete=skill3'],['xxx-3','skill_start=skill2,skill3||skill_complete=skill2,skill3||abandon_custom=0']])

我正在尝试拆分CM列。我试过了，它让我非常接近：

df = df.join(metrics['CM'].str.split('\|\|', expand=True).add_prefix('CM'))

但由于数据不一致，列不能整齐地排列。我如何以一种有序的方式将其拆分？

所需输出示例：

['CID', 'survey', 'skill_start', 'skill_complete', 'abandon_custom'],['xxx-1','NaN','skill1,skill2','skill1','NaN'],['xxx-2','1','skill1,skill3','skill3','NaN'],['xxx-3','NaN','skill2,skill3','skill2,skill3','0']

Answer 1

我解决了！

解决方案是使用正则表达式提取器创建一个新的数据帧，只包含我正在查找的值，在需要的地方使用get_dummies，然后将其连接回主数据帧。

skill_start = df['CM'].str.extract(r'skill_start=(?P<skill_start>.*?)\|\|')
surveys = df['CM'].str.extract(r'survey_response=(?P<survey_response>[1|2|3|4|5])')
skill_complete = df['CM'].str.extract(r'skill_complete=(?P<skill_complete>.*?)\|\|')
escalated_custom = df['CM'].str.extract(r'escalated_custom=(?P<escalated_custom>[0|1])')
abandoned_custom = df['CM'].str.extract(r'abandoned_custom=(?P<abandoned_custom>[0|1])')

skill_start = pd.concat([skill_start,skill_start.skill_start.str.get_dummies(sep=',')],1)
skill_start = skill_start.add_prefix('skill_start:')
skill_complete = pd.concat([skill_complete,skill_complete.skill_complete.str.get_dummies(sep=',')],1)
skill_complete = skill_complete.add_prefix('skill_complete:')

new_df = df.join(surveys).join(skill_start).join(skill_complete).join(escalated_custom).join(abandoned_custom)

Answer 2

您是否尝试过使用多个分隔符，但不确定这是否是您要查找的内容：

df1 = df['CM'].str.split('\|\||,|=', expand=True).add_prefix('CM_')
df = pd.concat([df['CID'], df1], axis=1)
print(df)

     CID         CM_0    CM_1         CM_2            CM_3            CM_4            CM_5            CM_6  CM_7
0  xxx-1  skill_start  skill1       skill2                  skill_complete          skill1                  None
1  xxx-2       survey       1  skill_start          skill1          skill3  skill_complete          skill3  None
2  xxx-3  skill_start  skill2       skill3  skill_complete          skill2          skill3  abandon_custom     0