Pandas 数据帧转储以擅长颜色格式 [英] Pandas dataframe dump to excel with color formatting

问题描述

我有一个大熊猫数据框 df 为:

Sou ATC P25 P75 Avg11 9 15 10乙 6.63 15 15 25C 6.63 5 10 8

我想将此数据帧打印到 excel 文件，但我想对 excel 文件的每一行应用格式，以便将以下规则应用于 ATC 和 Avg 列:

• 如果值小于 P25，则显示为红色
• 如果值大于 P75，则显示为绿色
• 如果值在 P25 和 P75 之间，则显示为黄色

excel中的示例显示如下:

我不知道如何解决这个问题.

解决方案

您可以使用

I have a large pandas dataframe df as:

Sou ATC   P25   P75 Avg
A   11    9     15  10
B   6.63  15    15  25
C   6.63  5     10  8

I want to print this datamframe to excel file but I want to apply formatting to each row of the excel file such that following rules are applied to cells in ATC and Avg columns:

• colored in red if value is less than P25
• colored in green if value is greater than P75
• colored in yellow if value is between P25 and P75

Sample display in excel is as follows:

I am not sure how to approach this.

解决方案

You can use style.Styler.apply with DataFrame of styles with numpy.select for filling by masks created by DataFrame.lt and DataFrame.gt:

def color(x): 
   c1 = 'background-color: red'
   c2 = 'background-color: green'
   c3 = 'background-color: yellow'
   c = ''

   cols = ['ATC','Avg']
   m1 = x[cols].lt(x['P25'], axis=0)
   m2 = x[cols].gt(x['P75'], axis=0)
   arr = np.select([m1, m2], [c1, c2], default=c3)

   df1 = pd.DataFrame(arr, index=x.index, columns=cols)
   return df1.reindex(columns=x.columns, fill_value=c)

df.style.apply(color,axis=None).to_excel('format_file.xlsx', index=False, engine='openpyxl')

这篇关于Pandas 数据帧转储以擅长颜色格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！