Error -> The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.-CodePudding

Could you please help me sort out the below if condition: (could be also possible with apply and lambda but don't know how to put so many conditions in single line)

test = pd.DataFrame({'index' : ['DS','VS','VB','FS','HB'],
   'bid' : [np.nan,102,103,104,np.NaN],
   'mid'  : [106,107,108,109,110],
    'ask' : [np.nan,112,113,114,115]})

print(test)

  index    bid  mid    ask
0    DS    NaN  106    NaN
1    VS  102.0  107  112.0
2    VB  103.0  108  113.0
3    FS  104.0  109  114.0
4    HB    NaN  110  115.0

What I tried :

if (test['index'].str.endswith('B') and test['bid'] > 0).all():
    test['fin'] == test['bid']
elif (test['index'].str.endswith('S')) and (test['ask'] > 0).all():
    test['fin'] == test['ask']
elif test['bid'] > 0:
    test['fin'] == test['bid']
elif test['mid'] > 0:
    test['fin'] == test['mid']
else:
    test['fin'] == test['ask']

Still getting the error message:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()

.Expected output:

  index    bid  mid    ask    fin
0    DS    NaN  106    NaN  106.0   
1    VS  102.0  107  112.0  112.0
2    VB  103.0  108  113.0  103.0
3    FS  104.0  109  114.0  114.0
4    HB    NaN  110  115.0  110.0    110 because no bid

explanation: I want to a new 'fin' columns based on the condition: if index ends with B and bid > 0 add bid value; if index end with S and ask > 0 add ask value; if those 2 conditions fail (ie end with a S but only bid available) if bid > 0 add bid value; if mid > 0 add mid value; if ask > 0 add ask value.

CodePudding user response：

When your condition is quite complicated, it might be easier to write the condition in afunction and apply it to every rows by using df.apply(func, axis=1)

import pandas as pd
import numpy as np
test = pd.DataFrame({'index' : ['DS','VS','VB','FS','HB'],
   'bid' : [np.nan,102,103,104,np.NaN],
   'mid'  : [106,107,108,109,110],
    'ask' : [np.nan,112,113,114,115]})


def extract_value(row):
    if row['index'].endswith('B') and row['bid'] > 0:
        return row['bid']
    elif row['index'].endswith('S') and row['ask'] > 0:
        return row['ask']
    elif row['bid'] > 0:
        return row['bid']
    elif row['mid'] > 0:
        return row['mid']
    else:
        return row['ask']


test['fin'] = test.apply(extract_value, axis=1)