Pandas not coloring table contents

Question

I am trying to create a table that estimates profit from a DataFrame with buy and sell prices:

    import pandas as pd
    from df2img import df2img
    import matplotlib.pyplot as plt
    
    
    
    df = pd.DataFrame(
        {
            'Fruits': ['Apple', 'Apple', 'Apple', 'Orange', 'Banana', 'Orange'],
            'BuyPrice': [1000, 3000, 2400, 3000, 800, 1500],
            'SellPrice': [1200, 2800, 2500, 2500, 700, 1750]
        }
    )
    
    
    # Display DataFrame
    print('Original DataFrame:\n')
    print(df)
    
    # Add Profit percentage column
    df['Profit'] = (df['SellPrice']-df['BuyPrice'])*100/df['BuyPrice']
    df['Profit'] = df.apply(lambda x: "{:,.2f} %".format(x['Profit']), axis=1)
    
    # Rename column titles
    df = df.rename({'BuyPrice': 'Buy Price', 'SellPrice': 'Sell Price'}, axis=1)
    
    # Highlight positive and negative profits
    def highlight_cols(s):
        color = 'red' if type(s) != str and s < 0 else 'green'        
        return 'color: %s' % color
    
    df.style.applymap(highlight_cols, subset=['Profit'])


    print('\nFinal DataFrame:\n')
    print(df)
    
    
    # Now create an image file for the table
    df2img(
        df,
        file="table_fruits.png",
        header_color="white",
        header_bgcolor="orange",
        row_bgcolors=["lightgray", "white"],
        font_size=10.0,
        col_width=1.5,
        row_height=0.3
    )
    
    plt.show()

Here I want to color the positive profits with green and the negative ones with red. The df.style.applymap works only (though colors incorrectly) when I don't use df2img on a Jupyter notebook on the same cell. Thus the DataFrame Styler instruction does not pass to the final image file. Any not-so-complicated solution?

Output:

Answer 1

A slight setup fix is needed. We do not want a percentage represented as a string when determining positive or negative. We also do not what a percentage stored multiplied by 100 because that affects formatting abilities later on.

The new setup can look like:

import numpy as np
import pandas as pd

df = pd.DataFrame({
    'Fruits': ['Apple', 'Apple', 'Apple', 'Orange', 'Banana', 'Orange'],
    'BuyPrice': [1000, 3000, 2400, 3000, 800, 1500],
    'SellPrice': [1200, 2800, 2500, 2500, 700, 1750]
})

# Add Profit percentage column
df['Profit'] = (df['SellPrice'] - df['BuyPrice']) / df['BuyPrice']

# Rename column titles
df = df.rename({'BuyPrice': 'Buy Price', 'SellPrice': 'Sell Price'}, axis=1)

	Fruits	Buy Price	Sell Price	Profit
0	Apple	1000	1200	0.2
1	Apple	3000	2800	-0.0666667
2	Apple	2400	2500	0.0416667
3	Orange	3000	2500	-0.166667
4	Banana	800	700	-0.125
5	Orange	1500	1750	0.166667

*If (for whatever reason) we need the values multiplied by 100, remember to divide back down before plotting and exporting to image.

---

I highly recommend against using Plotly Tables to perform this specific task as they are not as flexible when it comes to conditional formatting. This is much easier with dataframe_image and the built-in Styler object:

# pip install dataframe_image
import dataframe_image as dfi

# Highlight positive and negative profits
def highlight_cols(s):
    return np.where(s < 0, 'color: red', 'color:green')


# CSS for col_headings
headers = {
    'selector': 'th.col_heading',
    'props': 'background-color: orange; color: white;'
}

# CSS for rows
rows = [
    {
        'selector': 'tbody tr:nth-child(even)',
        'props': 'background-color: lightgray'
    },
    {
        'selector': 'tbody tr:nth-child(odd)',
        'props': 'background-color: white'
    }
]

styler = (
    df.reset_index()  # make current index a column
        .style  # Create Styler
        .hide_index()  # Hide new index (since old index is a column
        .apply(
            # apply highlighter function to Profit Column
            highlight_cols, axis=0, subset=['Profit']
        )
        .format(
            # apply percentage formatting to Profit Column
            formatter='{:.2%}', subset=['Profit']
        )
        .set_table_styles([headers, *rows])  # add CSS
)
# Export Styled Table to PNG
dfi.export(styler, 'table_fruits.png')

---

With Plotly there is more overhead to allow for conditional styling, even though the static styling rules are more easily applied:

# pip install df2img
from df2img import df2img

# Define Format Strings for Columns
col_formats = {'Profit': '.2%'}

# Make index a column with label
plot_df = df.reset_index()

# Build dictionary of Font colours for _all_ cells
# Using default
font_colours_df = pd.DataFrame(
    'black',  # Set default font colour
    index=plot_df.index, columns=plot_df.columns
)
# Apply Colours to Profit Column
font_colours_df['Profit'] = np.where(
    plot_df['Profit'] < 0, 'red', 'green'
)
fig = df2img.plot_dataframe(
    plot_df,
    print_index=False,  # Hide new index (old index now column)
    row_fill_color=('white', 'lightgray'),
    tbl_header={
        'font': {'color': 'white'},
        'fill': {'color': 'orange'}
    },
    tbl_cells={
        'format': [
            # Conditionally build a complete list of foramt strings
            #  Based on col_formats dict and columns
            col_formats[c] if c in col_formats else None
            for c in plot_df.columns
        ],
        'font': {
            # Needs Transposed for colours to go to the correct cells
            'color': font_colours_df.T
        }
    },
    show_fig=False,  # Don't show in by default (since we're saving instead)
    fig_size=(500, 175)  # Set some reasonable Fig Size
)
df2img.save_dataframe(fig=fig, filename="table_fruits.png")

*Note Plotly has no notion of a subset. Styles for all cells must be explicitly declared. For tbl_cells.format that means that every column must be provided a format string (or None). For tbl_cells.font.color that means passing an entire DataFrame of colours to style a single column.

Nonetheless, the resulting Table is: