Adding values in a new column conditionally in pandas dataframe

Question

I have a DataFrame like below:

ds = pd.DataFrame({'Name' : ['A','A','B','B','C', 'C', 'C', 'C'], 
    'Year': ['2021','2020','2020','2019','2021','2020','2020','2019' ]})

I want to add a new column 'Breached'. The value of "Breached" for column Name 'A' should be 1 if the year is less than the year of previous records of A and set to '0' otherwise. Similarly, the value of "Breached" for column Name 'B' should be 1 if the year is less than the year of previous records of B and set to '0' otherwise. Same goes for C.

So, my output should look like :

Here, the value of breached again is 0 only if the year was 2021 because that is the latest year for the name 'C'.

Any suggestion on how can I do that?

Can you clarify what you mean by "if the year is less than the year of previous records..." How many previous records? All records? Only records of the same name (A or B or C)? Why does (B, 2019) get a 0? Its year is less than everything before it... — ddejohn
– ddejohn, Commented Feb 11, 2022 at 22:59
Then why does (B, 2019) get a 0? It sounds like you want the opposite of what you said in your post. — ddejohn
– ddejohn, Commented Feb 11, 2022 at 23:01
SOrry for the confusion. It was a copy paste error. i have fixed it in the question now. — sats
– sats, Commented Feb 11, 2022 at 23:03

user7864386 · Accepted Answer · 2022-02-11 23:06:58Z

1

Seems like you could groupby + transform max + ne to get a boolean Series that is True if the year is the not latest year for each group, False otherwise. Then convert this Series to int dtype:

ds['Breached'] = ds.groupby('Name')['Year'].transform('max').ne(ds['Year']).astype(int)

Output:

  Name  Year  Breached
0    A  2021         0
1    A  2020         1
2    B  2020         0
3    B  2019         1
4    C  2021         0
5    C  2020         1
6    C  2020         1
7    C  2019         1

answered Feb 11, 2022 at 23:06

user7864386

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding values in a new column conditionally in pandas dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related