基于其他列的值比较值和颜色单元格

2024-03-29 02:38:05 发布

您现在位置:Python中文网/ 问答频道 /正文

我正在从事一个项目,将最终值(在“金额”列中)与其他列中的值进行比较,以确定最终值是否超过预先确定的阈值。如果违反,则最终值将根据违反的阈值上色,如果未违反,则最终值将上色为绿色。样本:

 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

预期:

Color pandas cell

尝试使用以下代码:

 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)

然而,这并不像预期的那样有效。提前感谢您的帮助


Tags: formatdfnpredamountupperlowercolor
1条回答
网友
1楼 · 发布于 2024-03-29 02:38:05

我将函数写为:

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)

输出:

enter image description here

相关问题 更多 >