Change value of column based on specific id in pandas dataframe

user_11863394 0
Viewed 2438

I have the below sorted dataframe and I want to set the last value of each id in the id column to 0

id value
1   500
1   50
1   36
2   45
2   150
2   70
2   20
2   10

I am able to set the last value of the entire id column to 0 using df['value'].iloc[-1] = 0. How can I set the last value of both id : 1 and id : 2 to get the below output.

id value
1   500
1   50
1   0
2   45
2   150
2   70
2   20
2   0
pandas dataframe python
3 Answers

you can do drop_duplicates and keep last to get the last row of each id. Use the index of these rows and set the value to 0

df.loc[df['id'].drop_duplicates(keep='last').index, 'value'] = 0

print(df)
   id  value
0   1    500
1   1     50
2   1      0
3   2     45
4   2    150
5   2     70
6   2     20
7   2      0
user_92747320 
 df.loc[~df.id.duplicated('last'),'value']=0

Broken down

    m=df.id.duplicated('last')
    
    df.loc[~m,'value']=0




id  value
0   1    500
1   1     50
2   1      0
3   2     45
4   2    150
5   2     70
6   2     20
7   2      0

How it works

m=df.id.duplicated('last')# Selects the last duplicated in column id

~m reverses that and hence last duplicated becomes true

df.loc[~m,'value']# loc accessor allows us to reach the True value in the nominated column to write with 0
user_89869750

If you are willing to use numpy here is a fast solution:

import numpy as np

# Recreate example
df = pd.DataFrame({
    "id":[1,1,1,2,2,2,2,2],
    "value": [500,50,36,45,150,70,20,10]
})
# Solution
df["value"] = np.where(~df.id.duplicated(keep="last"),0,df["value"].to_numpy())
user_141662260
Related