How to format and color monetary numbers in Pandas dataframe?

Question

I'm trying to apply formatting to a pandas pivot table so that it has color formatting and number formatting with commas, no decimals, $ in front of the number and parenthesis for negatives.

import numpy as np
import pandas as pd


money = [10000000, 2200000000, 10241100000.4521, 20224400000.75895]
honey = [30.6427984591421, 9584.28792256921, 37008.6603, 211200.2304295]
ltrs = ["a", "b", "a", "c"]
YRMO = ["202211", "202210", "202211", "202210"]
df = pd.DataFrame(
    zip(money, honey, ltrs, YRMO), columns=["money", "honey", "ltrs", "YRMO"]
)

YRMOs = sorted(set(df["YRMO"]))

pivot = df.pivot_table(
    values=["money", "honey"],
    index="ltrs",
    columns=["YRMO"],
    aggfunc={"money": np.sum, "honey": np.sum},
    margins=True,
    margins_name="Total",
)
pivot = pivot.drop("Total", axis=1, level=1)
pivot = pivot.fillna(0)

pivot["Var 1"] = pivot["money"][max(YRMOs)] - pivot["money"][min(YRMOs)]
pivot["Var 2"] = pivot["honey"][max(YRMOs)] - pivot["honey"][min(YRMOs)]

def color(val):
    if val < -1000:
        color = "green"
    elif val > 1000:
        color = "red"
    return "color: %s" % color

def formatter(x):
    return f"$({abs(x):,.0f})"


html_pivot = pivot.style.applymap(color, subset=['Var 1', 'Var 2'])

How do I apply my BOTH my color function and formatter function to this pivot table/dataframe? The color formatting is for the variance columns and the formatter function is for all columns.

Additional context: making the pivot look good so I can email in an html email.

Answer 1

Here is one way to it using Python standard library's module locale to use US monetary conventions and Pandas replace with regex pattern:

import locale

locale.setlocale(locale.LC_ALL, "us_US.UTF-8")

pivot = pivot.applymap(
    lambda x: locale.currency(val=x, grouping=True, symbol=True)
).apply(lambda x: x.str.replace(pat=r"\.\d{2}", repl="", regex=True))

Then, refactor your function to deal with strings:

def color(val):
    if val[0] == "$" and float(val[1:].replace(",", "")) > 1000:
        color = "red"
    else:
        color = "green"
    return f"color: {color}"

Finally, running the following expression in a Jupyter notebook cell:

pivot.style.applymap(color, subset=["Var 1", "Var 2"])

Outputs: