MisterButter
MisterButter

Reputation: 797

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

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

Upvotes: 1

Views: 370

Answers (2)

mozway
mozway

Reputation: 260470

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

Upvotes: 1

user7864386
user7864386

Reputation:

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

Upvotes: 2

Related Questions