Custom modification of column values in Pandas

user_4257964 0
Viewed 24

I have a table containing unique ids and amounts associated with them for a few years.

ID Year Amount ($)
1 2016 100
1 2017 45
1 2017 65
2 2017 75
2 2018 75
2 2019 80

I want to add $100 to amount for the first year of each ID, but only if the first year is greater than or equal to 2017. In this case i want $100 added to ID '2' and only for year 2017. This $100 is the extra amount for every ID in their first year on or after 2017.

ID Year Amount ($)
1 2016 100
1 2017 45
1 2018 65
2 2017 175
2 2018 75
2 2019 80

Is there an easy way to do this in pandas?

Thanks in advance!

pandas dataframe python
1 Answers

You can use boolean masks for indexing:

# is it the first row of the group?
m1 = df.groupby('ID').cumcount().eq(0)
# is the Year ≥ 2017?
m2 = df['Year'].ge(2017)

# add 100 in place
df.loc[m1&m2, 'Amount ($)'] += 100

output:

   ID  Year  Amount ($)
0   1  2016         100
1   1  2017          45
2   1  2018          65
3   2  2017         175
4   2  2018          75
5   2  2019          80
user_163434640
Related