CODEWITHSUNDEEP
pythonpandas
darkpool
darkpool

Reputation: 14641

Update column for all rows in first group

I need to update the value of a column for all rows in the first group of a dataframe. For example I have the following df:

date          amount
2019-01-01    10
2019-01-01    14
2019-02-01    16
2019-02-01    34
2019-03-01    36
2019-04-01    25
2019-04-01    45
2019-05-01    23

I need to group by date and set all amount values in the first group to 0. The result is:

date          amount
2019-01-01    0
2019-01-01    0
2019-02-01    16
2019-02-01    34
2019-03-01    36
2019-04-01    25
2019-04-01    45
2019-05-01    23

Upvotes: 1

Views: 39

Answers (2)

sammywemmy
sammywemmy

Reputation: 28644

You could use groupby and ngroup :

df.loc[df.groupby("date").ngroup().eq(0), "amount"] = 0

Upvotes: 1

jezrael
jezrael

Reputation: 862661

You can create mask by compare first value of date by all values and set 0 by DataFrame.loc:

df.loc[df['date'].eq(df['date'].iat[0]), 'amount'] = 0
print (df)
         date  amount
0  2019-01-01       0
1  2019-01-01       0
2  2019-02-01      16
3  2019-02-01      34
4  2019-03-01      36
5  2019-04-01      25
6  2019-04-01      45
7  2019-05-01      23

Another idea with Series.rank:

df.loc[df['date'].rank(method='dense').eq(1), 'amount'] = 0

Upvotes: 4

Related Questions