我有一个数据框,我想要为其某些样式设置为行的索引在字典中显示。在尝试使用apply,applymap,具有row / column / IndexSlice的子集的一堆东西之后,我仍然没有找到解决方案
注意:某些行将被设置为“样式”,而另一些则不会。
第一个问题:cat1出现在字典中,因此我需要使用条件样式。我需要将行中的每个单元格通过条件以应用某种颜色。经过大量尝试,下面的代码“最接近”了潜在的解决方案,但是保存为excel时出现错误。(当我仅将代码用于“绿色”时,它完全可以工作)。
“ DataFrame的真值不明确。请使用a.empty,a.bool(),a.item(),a.any()或a.all()。”
d_functions_colors={'cat1': catone_color,'cat5': catfive_color, 'cat40':catforty_color}
2020-12 2019-12 2018-12 2017-12 2016-12 ...
idx
cat1 100 NaN 50 35 5 ...
cat2 5 NaN 7 3 2 ...
cat3 6.25 NaN 6.25 4.93 5.21 ...
avg_cat3 5 4 3 2 1 ...
max_cat3 10 20 10 30 1 ...
cat4 ...
cat5 5 20 8 9.5 25 ...
avg_cat5 10 15 8 9.5 20 ...
...
(the results for avg, max are calculated and added for some 'cats' beforehand based on multiple (other) data sources)
def highlight_row_green(x):
c0 = ''
c1 = 'background-color: green'
c2 = 'background-color: yellow'
c3 = 'background-color: red'
df1 = pd.DataFrame('', index=x.index, columns=x.columns)
m1 = x.index.astype(str).str.contains('^cat1')
mask = (x[m1] >= 50).reindex(x.index, fill_value=False)
df1 = df1.mask(mask, c1)
return df1
def highlight_row_yellow(x):
c0 = ''
c1 = 'background-color: green'
c2 = 'background-color: yellow'
c3 = 'background-color: red'
df1 = pd.DataFrame('', index=x.index, columns=x.columns)
m1 = x.index.astype(str).str.contains('^cat1$')
mask = (20 <= x[m1] < 50)
df1 = df1.mask(mask, c2)
return df1
def highlight_row_red(x):
c0 = ''
c1 = 'background-color: green'
c2 = 'background-color: yellow'
c3 = 'background-color: red'
df1 = pd.DataFrame('', index=x.index, columns=x.columns)
m1 = x.index.astype(str).str.contains('^cat1$')
mask = ( x[m1] < 20)
df1 = df1.mask(mask, c3)
return df1
def pandastoExcel(path,filename,sheetname,my_dataframe):
outputfilepath=(path+'\\'+filename)
if len(sheetname)>=30:
sheetname='Default'
else:
pass
writer = pd.ExcelWriter(outputfilepath , engine='xlsxwriter')
try:
my_dataframe.to_excel(writer, sheet_name=sheetname,index=True)
except Exception as e:
print(str(e))
writer.save()
df_output=df.style.\
apply(highlight_row_green, axis=None).\
apply(highlight_row_yellow, axis=None).\
apply(highlight_row_red, axis=None)
第二个问题:我试图对它们进行分组,但出现相同的错误“ DataFrame的真值不明确。请使用a.empty,a.bool(),a.item(),a.any()或a.all ()。”
def catone_color(x):
c1 = 'background-color: green'
c2 = 'background-color: yellow'
c3 = 'background-color: red'
c0 = ''
df1 = pd.DataFrame('', index=x.index, columns=x.columns)
m1 = x.index.astype(str).str.contains('^cat1$')
mask = (x[m1] >= 50).reindex(x.index, fill_value=False)
masky = (20 <= x[m1] < 50)
maskr = (x[m1] < 20)
df1 = df1.mask(mask, c1)
df1 = df1.mask(masky, c2)
df1 = df1.mask(maskr, c3)
return df1
df_output=df.style.apply(catone_color, axis=None)
第三个问题:(例如cat5),样式基于cat5_avg中的值。因此,2020 cat5_value的颜色基于2020 cat5_avg,2019 cat5_value的颜色基于2019 cat5_value,依此类推。
通过将数据帧中的两行(例如row_values,row_avg)和其他内容组合在一起进行了尝试,但远没有实现任何目的。
2020-12 2019-12 2018-12 2017-12 2016-12 ...
idx
cat1 100 NaN 50 35 5 ...
cat2 5 NaN 7 3 2 ...
cat3 6.25 NaN 6.25 4.93 5.21 ...
avg_cat3 5 4 3 2 1 ...
max_cat3 10 20 10 30 1 ...
cat4 ...
cat5 5 20 8 9.5 25 ...
avg_cat5 10 15 8 9.5 20 ...
...
#Idea of what I'm trying to accomplish:
for cat5_value in row_cat_5:
If cat5_value > avg_cat5_same column:
color = 'green'
elif cat5_value == avg_cat5_same_column:
color = 'yellow'
elif cat5_value < avg_cat5_same_column:
color = 'red'
else:
color=''
return 'background-color: %s' % color
#(Background color row cat_5 would be red,green,yellow,yellow,green).
#What I've got left from trying:
g= 'green'
y = 'yellow'
r = 'red'
m1 = val.iloc[0, :] < val.iloc[1, :]
m2 = val.iloc[0, :] == val.iloc[1, :]
m3 = val.iloc[0, :] > val.iloc[1, :]
df1 = pd.DataFrame('background-color: ', index=val.index, columns=val.columns)
df1.iloc[:,0] = np.where(m1, 'background-color: {}'.format(g), df1.iloc[0, :])
df1.iloc[:,0] = np.where(m2, 'background-color: {}'.format(y), df1.iloc[0, :])
df1.iloc[:,0] = np.where(m3, 'background-color: {}'.format(r), df1.iloc[0, :])
return df1
先感谢您,
PS:衷心感谢所有贡献者,他们的回答是上述结果代码的基础。
用openpyxl解决
from openpyxl import Workbook,load_workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
from openpyxl.cell import Cell
d_functions_colors={'cat1': catone_color,
'cat5': catfive_color,'cat40':catforty_color}
wb = load_workbook(outputfilepath)
ws = wb.active
for row in ws.iter_rows(min_row=ws.min_row, max_row=ws.max_row):
if row[0] in d_functions_colors:
row=d_functions_colors[row[0].value](row)
def catone_color(row):
for cell in row:
try:
fl_cell=float(cell.value)
if fl_cell>=50:
#Green
cell.fill =
PatternFill(start_color='0000FF00',end_color='0000FF00',fill_type='solid')
elif 50>fl_cell>=20:
#yellow
cell.fill =
PatternFill(start_color='00FFFF00',end_color='00FFFF00',fill_type='solid')
elif 20 > fl_cell:
#red
cell.fill =
PatternFill(start_color='FFFF0000',end_color='FFFF0000',fill_type='solid')
else:
pass
except Exception as e:
print(str(e))
return row
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句