In pandas, How can I create a new column B based on a column A in df, such that:

• B=1 if A_(i 1)-A_(i) > 5 or A_(i) <= 10
• B=0 if A_(i 1)-A_(i) <= 5

However, the first B_i value is always one

Example:

A	B
5	1 (the first B_i)
12	1
14	0
22	1
20	0
33	1

CodePudding user response：

Use diff with a comparison to your value and convertion from boolean to int using le:

N = 5
df['B'] = (~df['A'].diff().le(N)).astype(int)

NB. using a le(5) comparison with inversion enables to have 1 for the first value output:

    A  B
0   5  1
1  12  1
2  14  0
3  22  1
4  20  0
5  33  1

updated answer, simply combine a second condition with OR (|):

df['B'] = (~df['A'].diff().le(5)|df['A'].lt(10)).astype(int)

output: same as above with the provided data

CodePudding user response：

I was little confused with your rows numeration bacause we should have missing value on last row instead of first if we calcule for B_i basing on condition A_(i 1)-A_(i) (first row should have both, A_(i) and A_(i 1) and last row should be missing A_(i 1) value.

Anyway,basing on your example i assumed that we calculate for B_(i 1).

import pandas as pd
df = pd.DataFrame(columns=["A"],data=[5,12,14,22,20,33])
df['shifted_A'] = df['A'].shift(1) #This row can be removed - it was added only show to how shift works on final dataframe
df['B']=''
df.loc[((df['A']-df['A'].shift(1))>5)   (df['A'].shift(1)<=10), 'B']=1 #Update rows that fulfill one of conditions with 1
df.loc[(df['A']-df['A'].shift(1))<=5, 'B']=0 #Update rows that fulfill condition with 0
df.loc[df.index==0, 'B']=1 #Update first row in B column
print(df)

That prints:

    A  shifted_A  B
0   5        NaN  1
1  12        5.0  1
2  14       12.0  0
3  22       14.0  1
4  20       22.0  0
5  33       20.0  1

I am not sure if it is fastest way, but i guess it should be one of easier to understand.

Little explanation:

df.loc[mask, columnname]=newvalue allows us to update value in given column if condition (mask) is fulfilled

(df['A']-df['A'].shift(1))>5) (df['A'].shift(1)<=10) Each condition here returns True or False. If we added them the result is True if any of that is True (which is simply OR). In case we need AND we can multiply the conditions

CodePudding user response：

Use Series.diff, replace first missing value for 1 after compare for greater or equal by Series.ge:

N = 5
df['B'] = (df.A.diff().fillna(N).ge(N) | df.A.lt(10)).astype(int)
print (df)
    A  B
0   5  1
1  12  1
2  14  0
3  22  1
4  20  0
5  33  1