Changing the value of a column in a dataframe

Question

I have a data-frame, df

ID  year   Treat
1   2008     0
1   2009   2009
1   2010   2010
1   2011     0
2   2008     0
2   2009     0
2   2010     0
2   2011     0

I wanna generate a new variable new_treat:

ID  year   Treat  new_treat
1   2008     0       2009
1   2009   2009      2009
1   2010   2010      2009
1   2011     0       2009
2   2008     0        0
2   2009     0        0
2   2010     0        0
2   2011     0        0

I used the following code:

df['new_treat']= df.groupby(['id'])['treat'].shift(-1)

and

df['new_treat'] = df.groupby('id')['new_treat'].cummin()

It works with first two row but for the remaining I am getting zero.

For this data-frame I have only two non-zero but in my main data-frame there is number of nonzero values that I want to convert to beginning treatment year — Jui Sen
– Jui Sen, Commented Jun 4, 2021 at 22:36
The cummin() method works correctly. 0 is less than 2009. You may want to set the 0 values to NaN first. — Arne
– Arne, Commented Jun 5, 2021 at 0:03
Like @Arne suggests, keep blank entries as NaN rather than 0, then .cummin() will do what you want. — smci
– smci, Commented Jun 5, 2021 at 3:13

Utsav · Accepted Answer · 2021-06-05 02:45:13Z

1

Try first instead of cummin

cummin is not working from 3rd row because value of 3rd row is 0 which is min compared to 2009 hence 3rd and 4th row for id 1 is giving output as 0.

Code

df['new_treat']= df.groupby(['id'])['treat'].shift(-1)
df['new_treat'] = df.groupby('id')['new_treat'].transform('first').astype(int)
df

Output

    id  year    treat   new_treat
0   1   2008    0       2009
1   1   2009    2009    2009
2   1   2010    2010    2009
3   1   2011    0       2009
4   2   2008    0       0
5   2   2009    0       0
6   2   2010    0       0
7   2   2011    0       0

answered Jun 5, 2021 at 2:45

Utsav

6,0932 gold badges36 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

Changing the value of a column in a 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