如何从PANDAS DataFrame创建具有正确顺序的字符串数据类型的枢轴表

Question

我有一个dataframe，如下所示，

这是表格格式中相同的数据，您可以复制/粘贴，

SourceName     SourceType   Edge       TargetName      TargetType
cardiac myosin     DISEASE  induce     myocarditis     DISEASE
cardiac myosin     DISEASE  induce     heart disease   DISEASE
nitric             CHEMICAL inhibit    chrysin         CHEMICAL
peptide magainin   CHEMICAL exhibited  tumor           DISEASE

以下是词典格式中相同的数据，您可以复制/粘贴，

{'id': [1, 2, 3, 4],
 'SourceName': ['cardiac myosin',
  'cardiac myosin',
  'nitric',
  'peptide magainin'],
 'SourceType': ['DISEASE', 'DISEASE', 'CHEMICAL', 'CHEMICAL'],
 'Edge': ['induce', 'induce', 'inhibit', 'exhibited'],
 'TargetName': ['myocarditis',
  'heart disease',
  'chrysin',
  'tumor'],
 'TargetType': ['DISEASE', 'DISEASE', 'CHEMICAL', 'DISEASE']}

我尝试使用在代码下方，但是某些源代码的类型错误，例如“肽杂志”应该是一种化学物质，但疾病不正确。

df1 = df.groupby(["id","SourceType","TargetType"])['SourceName', 'Edge', 'TargetName'].aggregate(lambda x: x).unstack().reset_index()
df1.columns=df1.columns.tolist()

不正确的样本输出，有人可以帮助我，谢谢。

预期输出：

Answer 1

我不完全了解您尝试与新结构实现的目标，但是可以通过“ SourceType”和“ TargetType”进行一次分组，然后合并产生的DataFrames：

source_df = pd.DataFrame()
target_df = pd.DataFrame()

for s, sub_df in df.groupby('SourceType'):
    source_sub_df = sub_df[['id', 'SourceName']]
    source_sub_df.columns = ['id', f'SourceType_{s}']
    source_df = pd.concat([source_df, source_sub_df])

for t, sub_df in df.groupby('TargetType'):
    target_sub_df = sub_df[['id', 'Edge', 'TargetName']]
    target_sub_df.columns = ['id', 'Edge', f'TargetType_{t}']
    target_df = pd.concat([target_df, target_sub_df])

df_out = source_df.merge(target_df, on='id').sort_values('id').reset_index(drop=True)

print(df_out)

输出：输出：

   id SourceType_CHEMICAL SourceType_DISEASE       Edge TargetType_CHEMICAL TargetType_DISEASE
0   1                 NaN     cardiac myosin     induce                 NaN        myocarditis
1   2                 NaN     cardiac myosin     induce                 NaN      heart disease
2   3              nitric                NaN    inhibit             chrysin                NaN
3   4    peptide magainin                NaN  exhibited                 NaN              tumor