Pandas- Finding first occurence in a row based on column values

user_13589474 0
Viewed 94

I have the following dataframe:

 Row    Bid_price   Bid_volume  Ask_price   Ask_volume
 2      2999.0      786.7      -500.0       1403.2
 3      3000.0      786.7      -499.9       1407.2
 4      2950.0      787.3      -250.1       1407.2
---------------------
 56     125.1       2691       36.9         3113.1
 57     125         2691.1     37           3133.1
---------------------
 117    41.4        3029.7     2999         3835.7
 118    40.05       3029.7     3000         3835.7
---------------------
 123    39.4        3129.7     NaN          NaN
 124    36.1        3129.7     NaN          NaN
 125    36          3134.7     NaN          NaN

I need to take the first pair of Bid_price and Bid_volume (2999.0 and 786.7) and compare with ALL pairs of Ask_price and Ask_volume. As long as Bid_volume < Ask_volume AND Bid_price > Ask_price I jump to the next pair of Bid_price and Bid_volume and compare again with ALL pairs of Ask_price and Ask_volume. Bid_Price is decreasing, Bid_Volume is increasing, Ask_Price is increasing, Ask_Volume is increasing. Bid_Price, Bid_Volume have the same length, but Ask_Price and Ask_Volume are shorter.

The output should be the first instance where Bid_volume is > Ask_volume AND Bid_price < Ask_price, so the condition is met. This is the case in line 124 for the pair Bid_Price and Bid_Volume, which matches with line 56 for the pair Ask_Price and Ask_Volume.

The desired output should be:

Row      Bid_price    Bid_volume  
124      36.1         3129.7

Row      Ask_price    Ask_volume
56       36.9         3113.1

My problem is that I can only evaluate conditions for each row. This returns nothing: 

BidAsk = BidAsk[(BidAsk["Bid_volume"] > BidAsk["Ask_volume"]) & (BidAsk["Bid_price"] < BidAsk["Ask_price"])]
BidAsk[["Bid_price","Bid_volume"]]

And this here give a traceback error: 

BidAsk = BidAsk.where((BidAsk["Bid_volume"] > BidAsk["Ask_volume"]) & (BidAsk["Bid_Price"] < BidAsk["Ask_Price"]))
BidAsk[["Bid_price", "Bid_volume"]]

Any help much appreciated. Thanks!

pandas dataframe python
2 Answers

The first method returns nothing because both the conditions are always false, you have to invert the comparison signs.

BidAsk = BidAsk[(BidAsk["Bid_volume"] < BidAsk["Ask_volume"]) & (BidAsk["Bid_price"] > BidAsk["Ask_price"])]
user_135550590

I hope I understand you correctly, this script will find Bid_price and Bid_volume that satisfies the condition Bid_volume is > Ask_volume AND Bid_price < Ask_price.

If I have this dataframe:

   Bid_price  Bid_volume  Ask_price  Ask_volume
0     2999.0       786.7     -500.0      1403.2
1     3000.0       786.7     -499.9      1407.2
2     2950.0       787.3     -250.1      1407.2
3     2500.0       792.8     -250.0      1593.2
4     2000.0       798.9     -200.1      1593.2
5     1400.0      2000.0     1200.0      1600.0
6       36.1      3129.7        NaN         NaN

Then:

import pandas as pd
from io import StringIO

txt = '''Bid_price   Bid_volume  Ask_price   Ask_volume
2999.0      786.7      -500.0       1403.2
3000.0      786.7      -499.9       1407.2
2950.0      787.3      -250.1       1407.2
2500.0      792.8      -250.0       1593.2
2000.0      798.9      -200.1       1593.2
1400.0     2000.0      1200.0       1600.0
  36.1     3129.7             '''

df = pd.read_fwf(StringIO(txt))

max_price = df.Ask_price.max()
max_volume = df.Ask_volume.max()

mask = pd.concat([df.Bid_price < max_price, df.Bid_volume > max_volume], axis=1).all(axis=1)

print( df.loc[mask, ['Bid_price', 'Bid_volume']].head(1) )

Prints:

   Bid_price  Bid_volume
6       36.1      3129.7

EDIT (according the updated question):

import pandas as pd
from io import StringIO

txt = ''' Row    Bid_price   Bid_volume  Ask_price   Ask_volume
 2      2999.0      786.7      -500.0       1403.2
 3      3000.0      786.7      -499.9       1407.2
 4      2950.0      787.3      -250.1       1407.2
 56     125.1       2691       36.9         3113.1
 57     125         2691.1     37           3133.1
 117    41.4        3029.7     2999         3835.7
 118    40.05       3029.7     3000         3835.7
 123    39.4        3129.7     NaN          NaN
 124    36.1        3129.7     NaN          NaN
 125    36          3134.7     NaN          NaN'''

df = pd.read_fwf(StringIO(txt))

def get_indexes(df):
    for idx1, bid_price, bid_volume in zip(df.index, df.Bid_price, df.Bid_volume):
        for idx2, ask_price, ask_volume in zip(df.index, df.Ask_price, df.Ask_volume):
            if bid_volume > ask_volume and bid_price < ask_price:
                return idx1, idx2, bid_price, bid_volume, ask_price, ask_volume

print(df)
print()

result = get_indexes(df)
if result:
    print('Bid Price   =', result[2])
    print('Bid Volume  =', result[3])
    print('Ask Price   =', result[4])
    print('Ask Volume  =', result[5])
    print('Index bid   =', result[0])
    print('Index ask   =', result[1])

Prints:

   Row  Bid_price  Bid_volume  Ask_price  Ask_volume
0    2    2999.00       786.7     -500.0      1403.2
1    3    3000.00       786.7     -499.9      1407.2
2    4    2950.00       787.3     -250.1      1407.2
3   56     125.10      2691.0       36.9      3113.1
4   57     125.00      2691.1       37.0      3133.1
5  117      41.40      3029.7     2999.0      3835.7
6  118      40.05      3029.7     3000.0      3835.7
7  123      39.40      3129.7        NaN         NaN
8  124      36.10      3129.7        NaN         NaN
9  125      36.00      3134.7        NaN         NaN

Bid Price   = 36.1
Bid Volume  = 3129.7
Ask Price   = 36.9
Ask Volume  = 3113.1
Index bid   = 8
Index ask   = 3
user_100359850
Related