CODEWITHSUNDEEP
pythonpandas
Stacey
Stacey

Reputation: 5097

Create a dataframe column based on values in another column on the same row and a row back

I have a dataframe df, where the head looks looks like:

        BB       DATE         PX
0  1333 JP 2014-03-04   471300.0       
1  1333 JP 2014-04-04   132900.0       
2  1333 JP 2014-04-07   150400.0       
3  1333 JP 2014-04-13   191900.0       
4  2345 JP 2014-04-14   218900.0

I would like to create a new column called say FLAG which is set using 2 conditions 1.Looking at the column named BB and for each row take the current value and look back at the day before and compare the values. If the values are the same

and

  1. If the Absolute difference between the current DATE and the date yesterday is greater than 3

Then set FLAG = 1 otherwise set FLAG =0

Therefore in this example the new FLAG column would look like

        BB       DATE         PX  FLAG
0  1333 JP 2014-03-04   471300.0     0  
1  1333 JP 2014-04-04   132900.0     1  
2  1333 JP 2014-04-07   150400.0     0  
3  1333 JP 2014-04-13   191900.0     1  
4  2345 JP 2014-04-14   218900.0     0

Where index equals 1 you can see the BB value on that row and for the row before are the same but the absolute difference between the DATE for the two rows is greater than 3. The same is true where index = 3.

I've tried:

 if df.BB.shift(1) = df.BB.shift(0) & abs(df.DATE.shift(1) - df.DATE.shift(0)) >3 :
    df['FLAG'] = 1 
    else:
    df['FLAG'] = 0

But can't get it to work.

Upvotes: 1

Views: 105

Answers (1)

Kevin Fang
Kevin Fang

Reputation: 2012

Assume your DATE column is pandas datetime object, one liner:

df['FLAG'] = ((df['BB'].shift(1)==df['BB']) & (df['DATE'].diff()>pd.Timedelta(days=3))).astype(int)

Output:

       BB       DATE        PX  FLAG
0  1333JP 2014-03-04  471300.0     0
1  1333JP 2014-04-04  132900.0     1
2  1333JP 2014-04-07  150400.0     0
3  1333JP 2014-04-13  191900.0     1
4  2345JP 2014-04-14  218900.0     0

Upvotes: 1

Related Questions