python dataframe result based on values of 2 different columns

Question

Say I have the following dataframe.

import pandas as pd
df = pd.DataFrame()

df['close'] = (7980,7996,8855,8363,8283,8303,8266,8582,8586,8179,8206,7854,8145,8152,8240,8373,8319,8298,8048,8218,8188,8055,8432,8537,9682,10021,9985,10169,10272,10152,10196,10270,10306,10355,10969,10420,10154,10096,10307,10400,10484)

df['A'] = ('TDOWN','TDOWN', 'TDOWN', 'TOP', 'TOP', 'TOP', 'TOP', 'TOP','BUP','BUP','BUP', 'BUP', 'BUP', 'BOTTOM', 'BOTTOM', 'BOTTOM', 'BUP','BUP','BUP','BUP', 'BOTTOM', 'BOTTOM', 'BUP','BUP','BUP', 'BUP','BUP','BUP','BUP', 'BOTTOM', 'BOTTOM', 'BOTTOM', 'BOTTOM','TDOWN','TDOWN', 'TDOWN', 'TOP', 'TOP', 'TOP', 'TOP', 'TOP')

df['outcome1'] = ('-','-', '-', '-', '-', '-', '-', '8582','-','-','-', '-', '-', '8152', '-', '-', '-','-','-','-', '-', '8055', '-','-','-', '-','-','-','-', '10152', '-', '-', '-','-','-', '-', '-', '-', '-', '-', '10848')

print(df)

if a 'TDOWN' occurs on col 'A' and the corresponding price on col 'close' is higher than the price on a row below from col 'outcome1' than a 'SELL' occurs for col 'B' and vise versa, which is as follows.

if a 'BUP' occurs on col 'A' and the corresponding price on col 'close' is lower than the price on a row below from col 'outcome1' than a 'BUY' occurs for col 'B'.

Below is the desired outcome of I am trying to achieve

df['B'] = ('-','-', 'SELL', '-', '-', '-', '-', '-','-','-','-', 'BUY', 'BUY', '-', '-', '-', '-','-','BUY','-', '-', '-', 'BUY','BUY','BUY', 'BUY','BUY','-','-', '-', '-', '-', '-','-','SELL', '-', '-', '-', '-', '-', '-')

How do I code this so I can reflect the same results as column 'B'.

Thank you

Ben.T · Accepted Answer · 2020-07-08 11:14:08Z

2

you can use np.select after changing the '-' in outcome1 with the following available value by replace the - by nan and bfill.

s = df['outcome1'].replace('-', np.nan).bfill().astype(float)

conds = [df['A'].eq('TDOWN')&df['close'].gt(s),
         df['A'].eq('BUP')&df['close'].lt(s)]
choices = ['SELL','BUY']
df['B'] = np.select(conds, choices, '-')

print (df.head(20))
    close       A outcome1     B
0    7980   TDOWN        -     -
1    7996   TDOWN        -     -
2    8855   TDOWN        -  SELL
3    8363     TOP        -     -
4    8283     TOP        -     -
5    8303     TOP        -     -
6    8266     TOP        -     -
7    8582     TOP     8582     -
8    8586     BUP        -     -
9    8179     BUP        -     -
10   8206     BUP        -     -
11   7854     BUP        -   BUY
12   8145     BUP        -   BUY
13   8152  BOTTOM     8152     -
14   8240  BOTTOM        -     -
15   8373  BOTTOM        -     -
16   8319     BUP        -     -
17   8298     BUP        -     -
18   8048     BUP        -   BUY
19   8218     BUP        -     -

answered Jul 8, 2020 at 11:14

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

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

3 Comments

Daniel R Over a year ago

Don't you need to use .shift when creatiing s? OP asked to compare close to a "row below from col 'outcome1'". Your results seems to be the same as the expected output, so he probably didn't mean that, just the backward fill.

Ben.T Over a year ago

@DanielR indeed I would have used shift if the column outcome1 was filled with value, but because the available values are scattered in this column, I understood as the "the next available value" hence the bfill do the job after replace the '-' by nan :)

Daniel R Over a year ago

@Ben.T makes senses, thanks. I was too literal in my interpretation of the question.

Collectives™ on Stack Overflow

python dataframe result based on values of 2 different columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related