python - 如何检查单元格中的值是否在范围内。基于此将不同的 'scores' 分配给新列

Question

我有这样的数据框:

orderID       Amount
   0          340.00
   1          200.00
   2           10.00
   3          500.00
   4          700.00

我想根据金额是否在指定范围内或是否“超出”范围有多远的条件对每个订单金额进行“评分”。

我的范围是:low = 300，high = 500 如果在这个范围内，我想分配 score = 100。如果 Amount 列中的值超出范围，我想应用这样的评分函数:

if Amount > high:
    df['score'] = 100 - (Amount - high)/100
else:
    df['score'] = 100 - (low - Amount)/100

我试过这样的代码片段，但它需要很长时间(数百万行):

 def pricing_function (df, column_name):
         for l in range(0,df.shape[0]):
             if (df[column_name].iloc[l] <= high and df[column_name].iloc[l] >= low):
                 df['score'][l] = 100
             elif df[column_name].iloc[l] > high:
                 df['score'][l] = 100 - (low - Amount)/100
             else:
                 df['score'][l] = 100 - (low - Amount)/100

然后将函数应用为:

df['score'] = df.apply(pricing_function(df= my_df, column_name = 'Amount'))

因此，我想要这样的数据框:

orderID      Amount    score
   0         340.00     100     
   1         200.00      99
   2          10.00      71
   3         500.00     100
   4         700.00      80

使用 for 循环迭代每个单元格需要很长时间，我在 StackOverflow 上找不到解决方案。我尝试使用:

df['score'] = my_df['Amount'].between(low, high, inclusive = True)

这会为范围内/范围外的值提供 True/False 值。我可以稍后在最后一步将 bool 值更改为 1/0 和 100/0，但我仍然无法有效地应用我的 scoring_function。

Answer 1

与 numpy.where 功能:

df['score'] = np.where(df.Amount.between(low, high), 100,
                       np.where(df.Amount > high, 100 - (df.Amount - high)/100,
                                                  100 - (low - df.Amount)/100))

交互方式:

In [46]: df['score'] = np.where(df.Amount.between(low, high), 100, np.where(df.Amount > high, 100 - (df.Amount - high)/100, 100 - (low - df.Amount)/100))

In [47]: df
Out[47]: 
   orderID  Amount  score
0        0   340.0  100.0
1        1   200.0   99.0
2        2    10.0   97.1
3        3   500.0  100.0
4        4   700.0   98.0