Styling negative numbers in pandas

Question

I have a dataframe that I am exporting to Excel. I would also like to style it before the export.

I have this code which changes the background color and text color and works fine, but I would like to add to it:

df.style.set_properties(**{'background-color': 'black',
                           'color': 'lawngreen',
                           'border-color': 'white'}).to_excel(writer, sheet_name='Sheet1', startrow=rowPos, float_format = "%0.5f")

I need columns with strings and dates to have a white text color, and then positive numbers to be green and negative numbers to be red. I pulled these styles directly from pandas documentation on styling since I have never used it before, and am unsure how to achieve these results.

Lets say my dataframe looks like this:

StartDate   ExpiryDate  Commodity   Quantity  Price   Total
---------   ----------  ----------  -------   -----   -----
02/28/2024  12/28/2024  HO          10000     -3.89   -38900
02/28/2024  12/28/2024  WPI         10000      4.20    42000

how could I achieve what I am looking for?

Ean

Answer 1

I'd break it down into three steps (see the comments #) :

st = (
    df.style
    # 1-applying the default styles
    .set_properties(**default_css)
    # 2-formatting the numeric columns
    .apply(
        lambda df_: df_.select_dtypes("number")
        .lt(0).replace({True: tc(neg), False: tc(pos)}),
        axis=None,
    )
    .format(precision=2) # this one is optional
    # 3-formatting the string-like dates and strings
    .map(lambda v: tc(obj) if isinstance(v, str) else "")
)

# st.to_excel("output.xlsx", index=False) # uncomment to make an Excel

Output :

Used CSS :

default_css = {
    "background-color": "black",
    "border": "1px solid white",
}

tc = "color: {}".format # css text color
obj, pos, neg = "white", "lawngreen", "red"

Bead

this worked like a charm! Is it possible to select specific columns aside from using datatypes? for instance since quantity isn't really a value i want green or red but rather blue could that be handled? – Ean

Yes I'll be happy to update the answer but you need to be sure (at this point) and precise about the all styles you need to apply, in order to avoid moving targets. – Bead

Yes that would be the only other style that needs to be applied – Ean

IIUC, we could do it in different ways and here is one possible one, which gives this. – Bead

yup thats perfect, thank you! – Ean

Answer 2

This is written in plain text in the pandas documentation link you've given yourself !

https://pandas.pydata.org/pandas-docs/version/1.1/user_guide/style.html

TL/DR

Full solution (adapted pandas exemple) :

tested with :

python 3.10.11 (tags/v3.10.11:7d4cc5a, Apr  5 2023, 00:38:17)
ipython==8.22.1
numpy == 1.26.4
pandas == 2.2.1
openpyxl == 3.1.2
jinja2 == 3.1.3

import pandas as pd
import numpy as np
from pathlib import Path

np.random.seed(24)
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
df = pd.concat([df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))],
               axis=1)
df.iloc[3, 3] = np.nan
df.iloc[0, 2] = np.nan

# https://mcmap.net/q/273790/-generating-random-dates-within-a-given-range-in-pandas
def random_dates(start, end, n=10):
    start_u = start.value//10**9
    end_u = end.value//10**9
    return pd.to_datetime(np.random.randint(start_u, end_u, n), unit='s')

def color_rules(val):
    if isinstance(val, (float, int)):
        color = 'red' if val < 0 else 'green'
        return f"color: {color}" # to adapt. background color could be managed too
    elif isinstance(val, (pd.Timestamp, str)):
        return "color: orange" # to adapt. background color could be managed too
    else:
        return "color: grey" 


# 
start = pd.to_datetime('2015-01-01')
end = pd.to_datetime('2018-01-01')

df["dates"] = pd.to_datetime(random_dates(start, end))

df.style.applymap(color_rules).to_excel(Path().cwd() / "test.xlsx", sheet_name='Sheet1', float_format = "%0.5f")

For further tuning, you have to chain every logic needed

d.style.applymap(logic1).applymap(logic2). ... .to_excel( etc.)

Some context (quoting pandas doc)

Let’s write a simple style function that will color negative numbers red and positive numbers black.

import pandas as pd
import numpy as np

np.random.seed(24)
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
df = pd.concat([df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))],
               axis=1)
df.iloc[3, 3] = np.nan
df.iloc[0, 2] = np.nan

def color_negative_red(val):
    """
    Takes a scalar and returns a string with
    the css property `'color: red'` for negative
    strings, black otherwise.
    """
    color = 'red' if val < 0 else 'black'
    return 'color: %s' % color

s = df.style.applymap(color_negative_red)

Notice the similarity with the standard df.applymap, which operates on DataFrames elementwise. We want you to be able to reuse your existing knowledge of how to interact with DataFrames.

Notice also that our function returned a string containing the CSS attribute and value, separated by a colon just like in a tag. This will be a common theme.

also from https://pandas.pydata.org/pandas-docs/version/1.1/user_guide/style.html#Building-Styles-Summary

Style functions should return strings with one or more CSS attribute: value delimited by semicolons. Use

Styler.applymap(func) for elementwise styles

Styler.apply(func, axis=0) for columnwise styles

Styler.apply(func, axis=1) for rowwise styles

Styler.apply(func, axis=None) for tablewise styles

And crucially the input and output shapes of func must match. If x is the input then func(x).shape == x.shape.

Based on all the elements, it should be fairly simple to implement your own logic for positive<->green/ negative<->red color in a Jupyter notebook.

Excel-wise the doc states:

Export to Excel New in version 0.20.0

Experimental: This is a new feature and still under development. We’ll be adding features and possibly making breaking changes in future releases. We’d love to hear your feedback

Notice that the documentation also allows gradient coloring and more.

Deth

NB: I only used ipython console (no jupyter), and found out that jinja2 was necessary for proper usage of df.style – Deth

I saw that, but applying it to the code I have provided is where I am struggling to find a solution – Ean

added something to handle dates, see corrected answer – Deth

if i try to include background color like this: f"background-color: black, color: {color}" it does not work – Ean