根据其他列中的值比较值和彩色熊猫细胞

BoBoMann

我正在一个项目中，将最终值（在“金额”列中）与其他列中的值进行比较，以确定最终值是否违反了预定阈值。如果违反，则最终值将根据违反的阈值着色；如果未违反，则最终值将着色为绿色。样品：

 df

    Product     Components  Amount  Yellow  Orange  Red     Bound
       A             a1      61        50    55     60       Upper
       A             a2      28        60    30     20       Lower
       A             a3      37        10     5     0        Lower
       B             b1      89        90    100    110      Upper
       B             b2      91       100    95     90       Lower
       C             c1       5       10    15      20       Upper
       C             c2      29       30    20      10       Lower
       C             c3      15       100   200     300      Upper
       C             c4     201       500   400     300      Lower

预期：

尝试输入代码：

 def highlight(df):
    r = 'red'
    g = 'green'
    o = 'orange'
    y = 'yellow'

    yellow_up = (df['Amount'] > df['Yellow']) & (df['Amount'] < df['Orange']) & (df['Amount'] < df['Red']) & (df['Bound']=='Upper')
    orange_up = (df['Amount'] > df['Yellow']) & (df['Amount'] > df['Orange']) & (df['Amount'] < df['Red']) & (df['Bound']=='Upper')
    red_up = (df['Amount'] > df['Yellow']) & (df['Amount'] > df['Orange']) & (df['Amount'] > df['Red']) & (df['Bound']=='Upper')

    yellow_down = (df['Amount'] < df['Yellow']) & (df['Amount'] > df['Orange']) & (df['Amount'] > df['Red']) & (df['Bound']=='Lower')
    orange_down = (df['Amount'] < df['Yellow']) & (df['Amount'] < df['Orange']) & (df['Amount'] > df['Red']) & (df['Bound']=='Lower')
    red_down = (df['Amount'] < df['Yellow']) & (df['Amount'] < df['Orange']) & (df['Amount'] < df['Red']) & (df['Bound']=='Lower')

    df1 = pd.DataFrame('background-color: ', index = df.index, columns = df.columns)
    df1['Amount'] = np.where(yellow_up, 'background-color: {}'.format(y), 'background-color: {}'.format(g))
    df1['Amount'] = np.where(orange_up, 'background-color: {}'.format(o), 'background-color: {}'.format(g))
    df1['Amount'] = np.where(red_up, 'background-color: {}'.format(r), 'background-color: {}'.format(g))

    df1['Amount'] = np.where(yellow_down, 'background-color: {}'.format(y), 'background-color: {}'.format(g))
    df1['Amount'] = np.where(orange_down, 'background-color: {}'.format(y), 'background-color: {}'.format(g))
    df1['Amount'] = np.where(red_down, 'background-color: {}'.format(y), 'background-color: {}'.format(g))

    return df1

df.style.apply(highlight, axis = None)

但是，这不符合预期。预先感谢您的帮助！

广晃

我会这样写函数：

def highlight(row):
    color_map = {'Yellow':'yellow','Orange':'orange', 'Red':'red'}
    name = row.name

    thresh = df.loc[name, ['Yellow','Orange','Red']]
    direction = -1 if df.at[name, 'Bound']=='Upper' else 1

    value = row['Amount'] * direction
    thresh = (thresh * direction).sort_values()

    if (thresh > value).any():
        color = color_map[(thresh > value).idxmax()]
    else:
        color = 'green'

    return [f'background-color:{color}']

# apply
df.style.apply(highlight, subset=['Amount'], axis=1)

输出：