为什么使用“to_excel"保存时 pandas 数据框样式丢失? [英] why pandas dataframe style lost when saved with "to_excel"?

问题描述

根据

解决方案

我自己偶然发现了这个问题，据我所知，目前尚不支持像这样导出到 excel.我已将您的代码调整为与文档中的输出相匹配.

这是excel方法的文档输出.

df.style.\应用地图(color_negative_red).\应用(highlight_max).\to_excel('styled.xlsx', engine='openpyxl')

这是您调整后的代码:

将熊猫导入为 pddict = {'A': [1, 1, 1, 1, 1], 'B':[2,1,2,1,2], 'C':[1,2,1,2,1]}df = pd.DataFrame(dict)def突出显示(df，颜色=黄色"):attr = '背景色:{}'.format(color)df_bool = pd.DataFrame(df.apply(lambda x: [True if x.iloc[0] < v else False for v in x],axis=1).apply(pd.Series),索引=df.index)df_bool.columns = df.columns返回 pd.DataFrame(np.where(df_bool, attr, ""),索引= df.index，列= df.columns)df.style.\应用(突出显示，轴=无).\to_excel("styled.xlsx", engine="openpyxl")

在 highlight 函数中，我根据上面列表理解中应用的条件创建了一个布尔数据框.然后，我根据此数据框的结果分配样式.

Per this example the to_excel method should save the Excel file with background color. However, my saved Excel file does not have any color in it. I tried to write using both openpyxl and xlsxwriter engines. In both cases, the Excel file was saved, but the cell color/style was lost.

I can read the file back and reformat with openpyxl, but if this to_excel method is supposed to work, why doesn't it?

Here is the sample code.

 import pandas as pd # version 0.24.2
 dict = {'A': [1, 1, 1, 1, 1], 'B':[2, 1, 2, 1, 2], 'C':[1, 2, 1, 2, 1]}
 df = pd.DataFrame(dict)
 df_styled = df.style.apply(lambda x: ["background: #ffa31a" if x.iloc[0] < v else " " for v in x], axis=1)

 df_styled 
 ''' in my jupyter notebook, this displayed my dataframe with background color when condition is met, (all the 2s highlighted)'''

 '''Save the styled data frame to excel using to_excel'''
 df_styled.to_excel('example_file_openpyxl.xlsx', engine='openpyxl')
 df_styled.to_excel('example_file_xlsxwriter.xlsx', engine='xlsxwriter')

解决方案

I stumbled across this myself and as far as I'm aware there isn't support for exporting to excel like this yet. I've adjusted your code to match the output to excel in the documentation.

This is the documentation output to excel method.

df.style.\
    applymap(color_negative_red).\
    apply(highlight_max).\
    to_excel('styled.xlsx', engine='openpyxl')

This is your code adjusted:

import pandas as pd
dict = {'A': [1, 1, 1, 1, 1], 'B':[2,1,2,1,2], 'C':[1,2,1,2,1]}
df = pd.DataFrame(dict)

def highlight(df, color = "yellow"):

    attr = 'background-color: {}'.format(color)
    df_bool = pd.DataFrame(df.apply(lambda x: [True if x.iloc[0] < v else False for v in x],axis=1).apply(pd.Series),
                      index=df.index)
    df_bool.columns =df.columns
    return pd.DataFrame(np.where(df_bool, attr, ""),
                       index= df.index, columns=df.columns)
df.style. \
    apply(highlight, axis=None).\
    to_excel("styled.xlsx", engine="openpyxl")

Inside the highlight function, I create a boolean dataframe based on the conditions applied in the list comprehension above. Then, I assign styling based on the result of this dataframe.

这篇关于为什么使用“to_excel"保存时 pandas 数据框样式丢失?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！