python - Python 表条件格式

Question

我编写了一个代码来创建一个数据表并将其保存为图像。这是代码-

df_avg = data_rtm_market.groupby('date').mean()

base = datetime.datetime.today()

date_list = [base - datetime.timedelta(days=x) for x in range(1,8)]
dtr = [x.strftime("%d-%m-%Y") for x in date_list]

df_avg.reset_index(inplace=True)
last_7_day = df_avg[df_avg['date'].isin(dtr)]

data_rtm_market.date = pd.to_datetime(data_rtm_market.date,format="%d-%m-%Y")

dam_market.date = pd.to_datetime(dam_market.date,format="%d-%m-%Y")

final_ = pd.merge(data_rtm_market,dam_market,how='inner', on=['date','block'])

df = final_

df[['total_purchase_bid','total_sell_bid','total_cleared_vol']]=df.groupby('date')['purchase_bid', 'sell_bid', 'cleared_volume'].transform('sum')
df[['max_mcp_rtm', 'max_mcp_dam']]=final_.groupby('date')['mcp_x','mcp_y'].transform('max')
df[['avg_mcp_rtm','avg_mcp_dam']]=final_.groupby('date')['mcp_x','mcp_y'].transform('mean')
df[['min_mcp_rtm','min_mcp_dam']]=final_.groupby('date')['mcp_x','mcp_y'].transform('min')

summary = df[['date','total_purchase_bid',
       'total_sell_bid', 'total_cleared_vol', 'max_mcp_rtm', 'max_mcp_dam',
       'avg_mcp_rtm', 'avg_mcp_dam', 'min_mcp_rtm', 'min_mcp_dam']]

table = summary.drop_duplicates(keep='first')

table1 = table.tail(8)
table1 = table1.iloc[:-1]

col3 = table1[['total_purchase_bid',
       'total_sell_bid', 'total_cleared_vol']].apply(lambda x:round((x/4000),2))

col4 = table1[['max_mcp_rtm', 'max_mcp_dam',
               'avg_mcp_rtm', 'avg_mcp_dam', 'min_mcp_rtm', 'min_mcp_dam']].apply(lambda x:round(x,2))

final_temp = pd.concat([table1['date'],col3, col4], axis=1, sort=False)

final_temp['date'] = final_temp['date'].dt.strftime('%d-%m-%Y')

final_temp = final_temp.set_index('date').T

final_temp.reset_index(inplace=True,drop=True)


final_temp.insert(0,'1', ["Volume(MUs)","",""," Price(Rs/kWh)","","Price(Rs/kWh)","","Price(Rs/kWh)",""])
final_temp.insert(1,'2', ["Buy    ","Sell    ","Cleared","Max RTM","Max DAM","Avg RTM","Avg DAM","Min RTM","Min DAM"])


def render_mpl_table(data, col_width=3.0, row_height=0.825, font_size=26,
                     header_color='#5DADE2', row_colors=['#f1f1f2', 'w'], edge_color='black',
                     bbox=[0, 0, 1, 1], header_columns=0,
                     ax=None, **kwargs):
    if ax is None:
        size = (np.array(data.shape[::-1]) + np.array([0, 1])) * np.array([col_width, row_height])
        fig, ax = plt.subplots(figsize=size)
        ax.axis('off')

    mpl_table = ax.table(cellText=data.values, bbox=bbox, colLabels=data.columns, **kwargs)

    mpl_table.auto_set_font_size(False)
    mpl_table.set_fontsize(font_size)

    for k, cell in six.iteritems(mpl_table._cells):
        cell.set_edgecolor(edge_color)
        if k[0] == 0 or k[1] < header_columns:
            cell.set_text_props(weight='bold', color='white')
            cell.set_facecolor(header_color)
        else:
            cell.set_facecolor(row_colors[k[0]%len(row_colors) ])
    return fig

final_temp.columns.values[0] = ""
final_temp.columns.values[1] = ""
fig = render_mpl_table(final_temp, header_columns=0, col_width=4)
fig.savefig("/content/assets/summary_table.jpeg",bbox_inches='tight')

这给了我如下数据表-

我想在单元格中使用条件格式，例如，如果 Max RTM 大于 Max DAM，则单元格背景或单元格文本变为绿色，如果 Max RTM 小于 Max DAM，则单元格背景或单元格文本变为红色的。有什么办法吗？另外，如何合并第一列中的文本？

标签： pythonpython-3.xpandas-groupby

Answer 1

如果您转置表格，您可以执行以下操作：

import pandas as pd

#Initialize random dataframe
df_test = pd.DataFrame(np.random.normal(size = (10,2)), 
columns=['A', 'B'])

#Style functions
def compare_columns(data, column1, column2, color1, color2):
    attr = 'background-color: {}'
    if data[column1] > data[column2]:
        return [attr.format(color1) for s in data]
    elif data[column1] <= data[column2]:
        return [attr.format(color2) for s in data]

df_test.style.apply(compare_columns, column1 = 'A', 
                    column2 = 'B', color1 = 'red', 
                    color2 = 'green', axis=1) 

输出：

有关更多详细信息，请参阅此处的答案（Pandas 中的着色单元）和 pandas 文档（https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html）。