如何通过ID - Python更改以下重复行的值

Question

我试图找到一个解决方案，如果ID出现重复，则会转到另一列，并且只保留第一个值，并将其他值转换为0。

我所拥有的

test_2.query('FIX_VALUE != 0').head(10)

load_id_VR  ITEM_MEANING    LINE_COST   FIX_VALUE
672 19082022228668VR00500982    MILK RUN    452.46  -0.01
673 19082022228668VR00500982    STOP SUP MILK   34.57   -0.01
674 19082022228668VR00500982    SURTAXE GASOIL  60.90   -0.01
982 19082022280448VR00683048    EMPTIES LEG1    28.24   0.01
983 19082022280448VR00683048    MOBILITY PACKAGE    3.39    0.01
984 19082022280448VR00683048    SURTAXE GASOIL  2.81    0.01
991 19082022280448VR00683044    EMPTIES LEG1    13.18   0.01
992 19082022280448VR00683044    MOBILITY PACKAGE    1.58    0.01
993 19082022280448VR00683044    SURTAXE GASOIL  1.31    0.01
1033    1908202228144810027446  MATERIAL LEG2   62.99   -0.01

我想要达到的

load_id_VR  ITEM_MEANING    LINE_COST   FIX_VALUE
672 19082022228668VR00500982    MILK RUN    452.46  -0.01
673 19082022228668VR00500982    STOP SUP MILK   34.57   0.00
674 19082022228668VR00500982    SURTAXE GASOIL  60.90   0.00
982 19082022280448VR00683048    EMPTIES LEG1    28.24   0.01
983 19082022280448VR00683048    MOBILITY PACKAGE    3.39    0.00
984 19082022280448VR00683048    SURTAXE GASOIL  2.81    0.00
991 19082022280448VR00683044    EMPTIES LEG1    13.18   0.01
992 19082022280448VR00683044    MOBILITY PACKAGE    1.58    0.00
993 19082022280448VR00683044    SURTAXE GASOIL  1.31    0.00
1033    1908202228144810027446  MATERIAL LEG2   62.99   -0.01

它是否存在一种简单的方法来做到这一点？或者我需要这样做：

for i in range(0, len(df)):
    if (df['load_id_VR'].iloc[i] == df['load_id_VR'].iloc[i+1]):
        df['FIX_VALUE'].iloc[i+1] = 0.00

没有测试循环，希望在这里避免一个循环，如果它存在于Pandas --我们的Numpy中--可以直接执行这个命令。

Answer 1

这里有一种方法

# using mask, assign the value of zero where load_id_vr is duplicated
df['FIX_VALUE']=df['FIX_VALUE'].mask(df.duplicated(subset='load_id_VR'),0)
df

或使用LOC

df.loc[df.duplicated(subset='load_id_VR'), 'FIX_VALUE'] = 0
df

load_id_VR  ITEM_MEANING    LINE_COST   FIX_VALUE
672     19082022228668VR00500982    MILK RUN    452.46  -0.01
673     19082022228668VR00500982    STOP SUP MILK   34.57   0.00
674     19082022228668VR00500982    SURTAXE GASOIL  60.90   0.00
982     19082022280448VR00683048    EMPTIES LEG1    28.24   0.01
983     19082022280448VR00683048    MOBILITY PACKAGE    3.39    0.00
984     19082022280448VR00683048    SURTAXE GASOIL  2.81    0.00
991     19082022280448VR00683044    EMPTIES LEG1    13.18   0.01
992     19082022280448VR00683044    MOBILITY PACKAGE    1.58    0.00
993     19082022280448VR00683044    SURTAXE GASOIL  1.31    0.00
1033    1908202228144810027446  MATERIAL LEG2   62.99   -0.01