大 pandas 中的着色细胞 [英] Coloring Cells in Pandas

问题描述

我可以使用Pandas从excel文件导入数据：

  xl = read_excel（'path_to_file.xls '，'Sheet1'，index_col =无，na_values = ['NA']）
  

现在我将xl中的所有数据都作为DataFrame。我想根据另一个函数中定义的条件为该数据中的一些单元格着色，并将其导出（使用颜色编码）到Excel文件。

有人能告诉我该怎么办？

谢谢。

解决方案

Pandas有一个相对较新的 Styler 功能，您可以在其中对数据帧应用条件格式类型操作。

要利用 Styler 的全部功能，您应该熟悉 Styler .apply（）和 Styler.applymap（） API。这些允许您创建自定义函数并将它们应用于表的列，行或元素。例如，如果我想为+ ive单元格绿色和红色单元格着色，我会创建一个函数

  def _color_red_or_green（val）：
 color ='red'如果val< 0其他'绿色'
返回'颜色：％s'％颜色
  

并调用它在我的 Styler 对象上，即 df.style.applymap（_color_red_or_green）。

关于导出回Excel，据我所知， Styler 不支持，所以我可能会去xlsxwriter路由，如果您出于某种原因需要Excel。但是，根据我的经验，这是一个很好的纯Python替代方案，例如matplotlib图表和电子邮件/报告。

I am able to import data from an excel file using Pandas by using:

xl = read_excel('path_to_file.xls', 'Sheet1', index_col=None, na_values=['NA'])    

Now that I have all the data in xl as DataFrame. I would like to colour some cells in that data based on conditions defined in another function and export the same (with colour coding) to an Excel file.

Can someone tell me how should I go about this?

Thank you.

解决方案

Pandas has a relatively new Styler feature where you can apply conditional formatting type manipulations to dataframes. http://pandas.pydata.org/pandas-docs/stable/style.html

You can use some of their built-in functions like background_gradient or bar to replicate excel-like features like conditional formatting and data bars. You can also format cells to display percentages, floats, ints, etc. without changing the original dataframe.

Here's an example of the type of chart you can make using Styler (this is a nonsense chart but just meant to demonstrate features):

To harness the full functionality of Styler you should get comfortable with the Styler.apply() and Styler.applymap() APIs. These allow you to create custom functions and apply them to the table's columns, rows or elements. For example, if I wanted to color a +ive cell green and a -ive cell red, I'd create a function

def _color_red_or_green(val):
    color = 'red' if val < 0 else 'green'
    return 'color: %s' % color

and call it on my Styler object, i.e., df.style.applymap(_color_red_or_green).

With respect to exporting back to Excel, as far as I'm aware this is not supported in Styler yet so I'd probably go the xlsxwriter route if you NEED Excel for some reason. However, in my experience this is a great pure Python alternative, for example along with matplotlib charts and in emails/reports.

这篇关于大 pandas 中的着色细胞的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！