Color Cells in Pandas and Save as Excel in Python

In data analysis and reporting, presenting data in a visually appealing way can significantly enhance the understanding of the information. Pandas, a powerful data manipulation library in Python, provides capabilities to work with tabular data. However, when it comes to saving data to an Excel file with colored cells, we need to combine Pandas with other libraries like openpyxl. This blog post will guide you through the process of coloring cells in a Pandas DataFrame and then saving it as an Excel file.

Table of Contents#

  1. Core Concepts
  2. Typical Usage Method
  3. Common Practice
  4. Best Practices
  5. Code Examples
  6. Conclusion
  7. FAQ
  8. References

Core Concepts#

Pandas#

Pandas is a Python library used for data manipulation and analysis. It provides data structures like DataFrame and Series which are used to handle tabular data. A DataFrame is a two-dimensional labeled data structure with columns of potentially different types.

Openpyxl#

openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files. When we want to save a Pandas DataFrame as an Excel file with colored cells, we use openpyxl as the engine to interact with the Excel file.

Styling Cells#

Styling cells in Excel involves setting properties such as font color, background color, border, etc. In the context of saving a Pandas DataFrame to Excel, we can use conditional formatting rules or directly set cell styles based on certain conditions.

Typical Usage Method#

  1. Create a Pandas DataFrame: First, we need to have a DataFrame with our data.
  2. Define a Styling Function: Create a function that takes a cell value or a DataFrame and returns a string representing the CSS-style formatting.
  3. Apply the Styling Function: Use the style property of the DataFrame to apply the styling function.
  4. Save as Excel: Use the to_excel method of the styled DataFrame with openpyxl as the engine to save the DataFrame as an Excel file.

Common Practice#

Conditional Formatting#

Conditional formatting is a common practice where we color cells based on certain conditions. For example, we can color cells red if the value is below a certain threshold and green if it is above.

Highlighting Rows or Columns#

We can also highlight entire rows or columns based on specific criteria. For instance, highlighting rows where a particular column has a specific value.

Best Practices#

Use Functions for Styling#

Instead of hardcoding the styling rules in multiple places, use functions to define the styling. This makes the code more modular and easier to maintain.

Test the Styling#

Before saving the file, it's a good practice to preview the styled DataFrame in a Jupyter Notebook or print the HTML representation to ensure the styling is as expected.

Keep the Excel File Simple#

Avoid over - styling the Excel file as it can make the file large and difficult to open in some cases. Use a consistent color scheme for better readability.

Code Examples#

import pandas as pd
 
# Create a sample DataFrame
data = {
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Score': [85, 60, 92]
}
df = pd.DataFrame(data)
 
# Define a styling function for conditional formatting
def color_negative_red(value):
    if isinstance(value, (int, float)):
        if value < 70:
            return 'background-color: red'
        else:
            return 'background-color: green'
    return ''
 
# Apply the styling function to the DataFrame
styled_df = df.style.applymap(color_negative_red)
 
# Save the styled DataFrame as an Excel file
styled_df.to_excel('colored_cells.xlsx', engine='openpyxl', index=False)

In the above code:

  • We first create a sample DataFrame with Name and Score columns.
  • The color_negative_red function takes a cell value and returns a background color based on whether the value is below 70 or not.
  • We apply this function to the entire DataFrame using applymap.
  • Finally, we save the styled DataFrame as an Excel file named colored_cells.xlsx.

Conclusion#

Coloring cells in a Pandas DataFrame and saving it as an Excel file can be achieved by combining the power of Pandas and openpyxl. By using conditional formatting and following best practices, we can create visually appealing Excel reports. This technique is useful in data analysis, reporting, and presenting data to stakeholders.

FAQ#

Can I color cells based on multiple conditions?#

Yes, you can modify the styling function to include multiple conditions. For example, you can use if - elif - else statements in the function to handle different cases.

Can I apply different styles to different columns?#

Yes, you can use the apply method instead of applymap and specify the axis to apply the styling function to a particular column or row.

Does the styled DataFrame take up more memory?#

The styled DataFrame may take up slightly more memory as it stores the styling information in addition to the data. However, for most use cases, the memory overhead is negligible.

References#

2025-10