数据框架上的条件if then else

Question

对于下面的df，我想应用以下规则，如果SPY或SCZ的值是正的，将具有最大值的列的标签添加到新列'ACTIVE'中，否则如果两者都不是正的，则将列'ACTIVE'的值设置为最大值的列标签，TLT或TIP。

# pseudo code rules
if df.SPY > 0 and df.SCZ > 0:
if df.SPY > df.SCZ:
df['ACTIVE'] = 'SPY'
else:
df['ACTIVE'] = 'SCZ'
else:
if df.TIP > df.TLT:
df['ACTIVE'] = 'TIP'
else:
df['ACTIVE'] = 'TLT'

#sample data
df3 = pd.DataFrame({'TLT':[0.077,0.064,0.034,0.028], 
'TIP':[0.021,0.014,0.005,0.005], 
'SPY':[0.055,-0.063,0.09,0.094],
'SCZ':[0.062,0.060,0.068,0.069]})
# condition is only referencing the last row, need it to apply to each row
if (df.SPY[-1:] < 0).bool() & (df.SCZ[-1:] < 0).bool():    
df['ACTIVE'] = df.iloc[:,0:2].apply(pd.Series.idxmax, axis=1)
else:
# this will set the last column to the highest performing asset
df['ACTIVE'] = df.iloc[:,2:4].apply(pd.Series.idxmax, axis=1) 

Answer 1

TLT    TIP     SPY     SCZ
0   0.077   0.021   0.055   0.062
1   0.064   0.014   -0.063  0.060
2   0.034   0.005   0.090   0.068
3   0.028   0.005   0.094   0.069

您可以使用np.where，并以矢量化的方式进行。

df3['Active'] = np.where((df3['SPY'] > 0) & (df3['SCZ'] > 0),
np.where(df3['SPY'] > df3['SCZ'], 'SPY', 'SCZ'),
np.where(df3['TIP'] > df3['TLT'], 'TIP', 'TLT'))
df3
TLT    TIP     SPY     SCZ     Active
0   0.077   0.021   0.055   0.062   SCZ
1   0.064   0.014   -0.063  0.060   TLT
2   0.034   0.005   0.090   0.068   SPY
3   0.028   0.005   0.094   0.069   SPY

Answer 2

您可以使用pandas.DataFrame.apply方法，它将对DataFrame的每一行应用提供的函数。

def active(row):
if row['SPY'] > 0 and row['SCZ'] > 0:
if row['SPY'] > row['SCZ']:
return 'SPY'
return 'SCZ'

if row['TIP'] > row['TLT']:
return 'TIP'
return 'TLT'
df3['ACTIVE'] = df3.apply(active, axis = 1)

Answer 3

我将使用Python内置的列表推导式。

首先，让我们提取用于确定新字段到其自身函数的逻辑:

def evaluate_row(row) -> str:
if row['SPY'] > 0 and row['SCZ'] > 0:
if row['SPY'] > row['SCZ']:
return 'SPY'
else:
return 'SCZ'
else:
if row['TIP'] > row['TLT']:
return 'TIP'
else:
return 'TLT'

现在我们只使用这个函数来生成一个列表，使用列表推导式来填充数据框中的新列'ACTIVE'。很简单:

df3['ACTIVE'] = [evaluate_row(r[1]) for r in df3.iterrows()]

注意:我们使用r[1]的原因是因为.iterrows()方法返回的元组。

结果是:

>>> df3
TLT    TIP    SPY    SCZ ACTIVE
0  0.077  0.021  0.055  0.062    SCZ
1  0.064  0.014 -0.063  0.060    TLT
2  0.034  0.005  0.090  0.068    SPY
3  0.028  0.005  0.094  0.069    SPY