使用 df.style.applymap 为多个工作表 Excel 的单元格背景着色

Question

MRE在的帮助下创建https://jakevdp.github.io/PythonDataScienceHandbook/03.05-hierarchical-indexing.html，关于分层索引的惊人总结

MRE:

index = pd.MultiIndex.from_product([[2013, 2014,2015, 2016]],
names=['year'])
columns = pd.MultiIndex.from_product([['Bob', 'Guido', 'Sue'], ['HR', 'group']])
data = np.array([[1,2,3,4,5,"g1"],
[3,6,1,3,2,"g2"],
[3,6,1,2,3,"g1"],
[6,7,8,11,23,"g2"]])
all_df = pd.DataFrame(data, index=index, columns=columns)

使用一个df并根据条件对单元格的背景进行着色可以很好地工作，但当我试图将其应用于多张excel时，它似乎不起作用。

这是我的代码：

def coloring(val):
color = '#EDFFE7' if val in lst else 'white'
return f"background-color: {color}"

groups = ["g1", "g2"]
writer = pd.ExcelWriter("test.xlsx", engine="xlsxwriter")
for g in groups:
df = all_df.loc[all_df[("Sue","group")] == g].copy()
df.style.applymap(coloring).to_excel(writer, sheet_name=g)
writer.save()

这个

另外，如何在applymap方法中为子集参数添加索引？

Answer 1

似乎需要将两行都链接起来，因为df.style.applymap(coloring)没有分配回：

df.style.applymap(coloring).to_excel(writer, sheet_name=g)

相反：

df.style.applymap(coloring)
df.to_excel(writer, sheet_name=g)

或分配回：

df = df.style.applymap(coloring)
df.to_excel(writer, sheet_name=g)

编辑：

对我来说，如果列表中的值是整数，因为如果对混合数据使用np.array——数字为numpy的字符串将所有数据转换为对象：

index = pd.MultiIndex.from_product([[2013, 2014,2015, 2016]],
names=['year'])
columns = pd.MultiIndex.from_product([['Bob', 'Guido', 'Sue'], ['HR', 'group']])
data = np.array([[1,2,3,4,5,"g1"],
[3,6,1,3,2,"g2"],
[3,6,1,2,3,"g1"],
[6,7,8,11,23,"g2"]])
all_df = pd.DataFrame(data, index=index, columns=columns)
print (all_df.dtypes)
Bob    HR       object
group    object
Guido  HR       object
group    object
Sue    HR       object
group    object
dtype: object

因此，如果将嵌套列表传递给DataFrame，对我来说一切都很好：

index = pd.MultiIndex.from_product([[2013, 2014,2015, 2016]],
names=['year'])
columns = pd.MultiIndex.from_product([['Bob', 'Guido', 'Sue'], ['HR', 'group']])
data = [[1,2,3,4,5,"g1"],
[3,6,1,3,2,"g2"],
[3,6,1,2,3,"g1"],
[6,7,8,11,23,"g2"]]
all_df = pd.DataFrame(data, index=index, columns=columns)
print (all_df.dtypes)
Bob    HR        int64
group     int64
Guido  HR        int64
group     int64
Sue    HR        int64
group    object
dtype: object

---

def coloring(val):
color = '#EDFFE7' if val in lst else 'white'
return f"background-color: {color}"
writer = pd.ExcelWriter("test.xlsx", engine="xlsxwriter")
groups = ["g1", "g2"]
lst = [1,2,3]

for g in groups:
df = all_df.loc[all_df[("Sue","group")] == g].copy()
#print (df)
df.style.applymap(coloring).to_excel(writer, sheet_name=g)
writer.save()