python - Accounting formatting in Pandas df

Question

x=pd.DataFrame([[5.75,7.32],[1000000,-2]])

def money(val):
    """
    Takes a value and returns properly formatted money
    """
    if val < 0:
        return "$({:>,.0f})".format(abs((val)))
    else:
        return "${:>,.0f}".format(abs(val))

x.style.format({0: lambda x: money(x),
                1: lambda x: money(x)
                })

I am trying to get currency to format in the pandas jupyter display with excel accounting formatting. Which would look like the below.

I was most successful with the above code, but i also tried a myriad of css and html things, but i am not well versed in the languages so they didn't work really at all.

Answer 1

Your output looks like you are using the HTML display in the Jupyter notebook, so you will need to set pre for the white-space style, because HTML collapses multiple whitespace, and use a monospace font, e.g.:

styles = {
    'font-family': 'monospace',
    'white-space': 'pre'
}

x_style = x.style.set_properties(**styles)

Now to format the float, a simple right justified with $ could look like:

x_style.format('${:>10,.0f}')

This isn't quite right because you want to convert the negative number to (2), and you can do this with nested formats, separating out the number formatting from justification so you can add () if negative, e.g.:

x_style.format(lambda f: '${:>10}'.format(('({:,.0f})' if f < 0 else '{:,.0f}').format(f)))

Note: this is fragile in the sense it assumes 10 is sufficient width, vs. excel which dynamically left justifies $ to the maximum width of all the values in that column.

An alternative way to do this would be to extend string.StringFormatter to implement the accounting format logic.