如何为多索引数据框架中的特定单元格设置样式

Question

我正在迭代一个多索引数据框架，我试图将特定单元格的颜色设置为两个变量points_color和stat_color中的样式。

如何将样式应用于单元格?

for metric, new_df in df3.groupby(level=0):
idx = pd.IndexSlice
row = new_df.loc[(metric),:]
for geo in ['US', 'UK']:
points_color, stat_color = color(new_df.loc[metric,idx[:,:,['difference']]][geo]['']['difference'],
new_df.loc[metric,idx[:,:,['stat']]][geo]['']['stat'])

#####  SEE HERE  #######
df3.loc[metric,idx[:,:,['points']]][geo]['GM']['points'] = # apply points_color style to this value df3.loc[metric,idx[:,:,['points']]][geo]['GM']['points']
df3.loc[metric,idx[:,:,['stat']]][geo]['']['stat'] = # apply stat_color style to this value df3.loc[metric,idx[:,:,['stat']]][geo]['']['stat']
###########
df3

---

设置数据帧:

dic = {'US':{'Quality':{'points':"-2 n", 'difference':'equal', 'stat': 'same'}, 'Prices':{'points':"-7 n", 'difference':'negative', 'stat': 'below'}, 'Satisfaction':{'points':"3 n", 'difference':'positive', 'stat': 'below'}},
'UK': {'Quality':{'points':"3 n", 'difference':'equal', 'stat': 'above'}, 'Prices':{'points':"-13 n", 'difference':'negative', 'stat': 'below'}, 'Satisfaction':{'points':"2 n", 'difference':'negative', 'stat': 'same'}}}
d1 = defaultdict(dict)
for k, v in dic.items():
for k1, v1 in v.items():
for k2, v2 in v1.items():
d1[(k, k2)].update({k1: v2})
df = pd.DataFrame(d1)
df.columns = df.columns.rename("Skateboard", level=0)
df.columns = df.columns.rename("Metric", level=1)
df3 = pd.concat([df], keys=[''], names=['Q3'], axis=1).swaplevel(0, 1, axis=1)
df3.columns = df3.columns.map(lambda x: (x[0], 'GM', x[2]) if x[2] == 'points' else x)
df3.insert(loc=0, column=('','', 'Mode'), value="Website")
df3

设置颜色函数:它接受两个单元格值difference和stat，并确定单元格point和stats的样式是否在数据框中。

def color(difference, stat):
points_color, stat_color = '', ''

if stat in ('below', 'above'):
stat_color = 'background-color: #f2dcdb; color: red'

if difference == "negative":
points_color = 'color: red'
elif difference == "positive":
points_color = 'color: green' 

return points_color, stat_color

Answer 1

您可以通过列表选择geo列，比较stat和difference，并在切片中设置值:

def color(x):

idx = pd.IndexSlice
geo = ['US', 'UK']

m1 = x.loc[:, idx[geo, :, 'stat']].isin(('below', 'above'))
diff = x.loc[:, idx[geo, :, 'difference']]

df1 = pd.DataFrame('', index=x.index, columns=x.columns)

diff = (diff.rename(columns={'difference':'points'}, level=2)
.rename(columns={'':'GM'}, level=1))
df1.loc[:, idx[geo, 'GM', 'points']] = np.select([diff.eq('negative'), 
diff.eq('positive')], 
['color: red','color: green'], '')
df1.loc[:, idx[geo, :, 'stat']] = np.where(m1, 
'background-color: #f2dcdb; color: red', '')

return df1
df.style.apply(color, axis=None)