How to set values of succeeding rows based on dynamic condition pandas-CodePudding

I have the following column in a dataframe, what I would like to do is set order type to all succeeding rows based on if the row is under BUY IN AMOUNT, SELL IN AMOUNT or SELL IN UNITS. Meaning all orders succeeding for example BUY IN AMOUNT should get the value FUS. How can this be archieved?

Orders
BUY IN AMOUNT
X
X
SELL IN AMOUNT
X
X
SELL IN UNITS
X
X

Data:

df = pd.DataFrame({'Orders':['BUY IN AMOUNT','X','X','SELL IN AMOUNT','X','X','SELL IN UNITS','X','X']})

Desired output is the following:

Orders             TYPE
BUY IN AMOUNT
X                  FUS
X                  FUS
SELL IN AMOUNT
X                  FUD
X                  FUD
SELL IN UNITS
X                  FUD
X                  FUD

CodePudding user response：

One option is to use numpy.select ffill to fill in values depending on where BUY and SELL are; then mask the overfilled rows:

import numpy as np
cond = [df['Orders'].str.contains('BUY'), df['Orders'].str.contains('SELL')]
df['Type'] = np.select(cond, ['FUS','FUD'], pd.NA)
df['Type'] = df['Type'].ffill().mask(cond[0] | cond[1], '')

Output:

           Orders Type
0   BUY IN AMOUNT     
1               X  FUS
2               X  FUS
3  SELL IN AMOUNT     
4               X  FUD
5               X  FUD
6   SELL IN UNITS     
7               X  FUD
8               X  FUD

CodePudding user response：

You can use a mapping dictionary, and a mask:

d = {'BUY IN AMOUNT': 'FUS', 'SELL IN AMOUNT': 'FUD', 'SELL IN UNITS': 'FUD'}

s = df['Orders'].map(d)

df['TYPE'] = s.ffill().where(s.isna(), '')

Alternative:

d = {'BUY': 'FUS', 'SELL': 'FUD'}
s = df['Orders'].str.extract(f'({"|".join(d)})', expand=False).map(d)
df['TYPE'] = s.ffill().where(s.isna(), '')

Output:

           Orders TYPE
0   BUY IN AMOUNT     
1               X  FUS
2               X  FUS
3  SELL IN AMOUNT     
4               X  FUD
5               X  FUD
6   SELL IN UNITS     
7               X  FUD
8               X  FUD