使用df.style.applymap为多个工作表Excel着色单元格的背景 [英] using df.style.applymap to color cell's background for multiple sheet excel

问题描述

MREPythonDataScienceHandbook/03.05-hierarchical-indexing.html ，关于分层索引的精彩摘要

MRE created with help from https://jakevdp.github.io/PythonDataScienceHandbook/03.05-hierarchical-indexing.html, amazing summary on Hierarchical indexing

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表格时，它似乎不起作用.

Using one df and coloring cell's background depending on condition works fine however when I try to apply it to multiple sheet excel it does not seems to work.

这是我的代码:

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方法中为子集参数添加索引?

Also, how to add index for subset parameter in applymap method?

推荐答案

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

It seems you need chain both rows, because df.style.applymap(coloring) is not assigned back:

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的字符串会将所有数据转换为对象:

for me working well, if values in list are integers, because if use np.array for mixed data - strings with numbers numpy convert all data to objects:

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 ，对我来说一切正常:

So if pass nested lists to DataFrame all working well for me:

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()

这篇关于使用df.style.applymap为多个工作表Excel着色单元格的背景的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！