pandas在一列中找到相似的行，根据条件创建一个新列

Question

我有一个 df，查询主题 HPSame

0 WP_77.1 WP_706.1 HPS_1

1 WP_78.1 WP_46.1 HPS_2

2 WP_57.1 WP_26.1 HPS_3

3 WP_57.1 WP_627.1 HPS_4

4 WP_15.1 WP_16.1 HPS_5

5 WP_15 .1 WP_17.1 HPS_6

6 WP_15.1 WP_63.1 HPS_7 7 WP_15.1

WP_61.1 HPS_8

8 WP_15.1 WP_56.1 HPS_9

9 WP_40.1 WP_11.1 HPS_10

我试过，

df['query_s'] = df['query'].shift(-1)

df['HPSame_s'] = df['HPSame'].shift(-1)

condition = [(df['query'] == df['query_s'])]

ifTrue = df['HPSame']

ifFalse = df['HPSame_s']

df['match'] = np.where(condition, ifTrue, ifFalse)

这会引发 ValueError: 值的长度与索引的长度不匹配

我也尝试过，以下但没有给我我想要的结果。

df.loc[(df['query'] == df['query_s']), 'match'] = df['HPSame']

df.loc[(df['query'] != df['query_s']), 'match'] = df['HPSame_s']

我正在寻找结果，df = query subject HPSame match 0 WP_77.1 WP_706.1 HPS_1 HPS_1 1 WP_78.1 WP_46.1 HPS_2 HPS_2 2 WP_57.1 WP_26.1 HPS_3 HPS_3 3 WP_57.1 WP_627.1 HPS_4 HPS_3 4 WP_15.1 WP_16.1 HPS_5 HPS_5 5 WP_15.1 WP_17.1 HPS_6 HPS_5 6 WP_15.1 WP_63.1 HPS_7 HPS_5 7 WP_15.1 WP_61.1 HPS_8 HPS_5 8 WP_15.1 WP_56.1 HPS_9 HPS_5 9 WP _40.1 WP_11。 1 HPS_10 HPS_10

Answer 1

您可以使用ffill：df['match'] = df['HPSame'].where(df['query'] != df['query'].shift()).ffill()输出：     query   subject  HPSame   match0  WP_77.1  WP_706.1   HPS_1   HPS_11  WP_78.1   WP_46.1   HPS_2   HPS_22  WP_57.1   WP_26.1   HPS_3   HPS_33  WP_57.1  WP_627.1   HPS_4   HPS_34  WP_15.1   WP_16.1   HPS_5   HPS_55  WP_15.1   WP_17.1   HPS_6   HPS_56  WP_15.1   WP_63.1   HPS_7   HPS_57  WP_15.1   WP_61.1   HPS_8   HPS_58  WP_15.1   WP_56.1   HPS_9   HPS_59  WP_40.1   WP_11.1  HPS_10  HPS_10

Answer 2

您也可以使用groupby.transform('first')如df['match'] = (    df.groupby('query')['HPSame'].transform('first')    .reset_index(drop=True))输出     query   subject  HPSame   match0  WP_77.1  WP_706.1   HPS_1   HPS_11  WP_78.1   WP_46.1   HPS_2   HPS_22  WP_57.1   WP_26.1   HPS_3   HPS_33  WP_57.1  WP_627.1   HPS_4   HPS_34  WP_15.1   WP_16.1   HPS_5   HPS_55  WP_15.1   WP_17.1   HPS_6   HPS_56  WP_15.1   WP_63.1   HPS_7   HPS_57  WP_15.1   WP_61.1   HPS_8   HPS_58  WP_15.1   WP_56.1   HPS_9   HPS_59  WP_40.1   WP_11.1  HPS_10  HPS_10