我想突出显示单元格,如果它们大于依赖于列标题的值。
我想“读取”列标题,如果它在字典(CEPA_FW)中,则返回相应的值。然后,如果该列中的任何单元格大于该值,则将它们填充为深橙色。我的努力在下面,但是我遇到了错误(ValueError:长度不匹配:预期轴有1个元素,新值有4个元素)。
df=pd.DataFrame(({'As':['0.001', 0, '0.001','0.06'], 'Zn': ['6','4','6','8'], 'Pb': ['0.006','0','0.005','0.005'], 'Yt': [1,0,0.002,6]}))
cols=df.columns
CEPA_FW= {'Ag':0.05,'As' :0.05 ,'Ba':1.0,'B':1.0,'Cd' :0.01 ,'Cr' :0.05 ,'Co':0.001,'Cu' :1.0 ,'K':5.0,'Pb' :0.005 ,'Hg' :0.0002 ,'Mn':0.5,'Ni' :1.0 ,'Se':0.01,'Sn':0.5,'SO4':400.0,'Zn' :5.0}
def fill_exceedances(val):
for header in cols:
if header in CEPA_FW:
for c in df[header]:
fill = 'darkorange' if c> CEPA_FW[header] else ''
return ['backgroundcolor: %s' % fill]
df.style.apply(fill_exceedances, axis = 1).to_excel('styled.xlsx', engine='openpyxl')
最佳答案
使用自定义函数创建按条件填充样式的DataFrame
:
def fill_exceedances(x):
color = 'orange'
#get columns which are in keys of dict
c = x.columns.intersection(CEPA_FW.keys())
#filter columns and rename by dict
df2 = x[c].rename(columns=CEPA_FW)
#create boolean mask only for matched columns and compare
mask = df2.astype(float).values > df2.columns[None,:].values
#new DataFrame filled by no color
df1 = pd.DataFrame('', index=x.index, columns=c)
#set color by mask and add missing non matched columns names by reindex
df1 = (df1.where(mask, 'background-color: {}'.format(color))
.reindex(columns=x.columns, fill_value=''))
return df1
df.style.apply(fill_exceedances, axis=None).to_excel('styled.xlsx', engine='openpyxl')
关于python - 基于列标题字符串的pandas DataFrame列的条件格式,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53848373/