Accounting formatting in Pandas df

Question

Imgur

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.

Imgur

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.

Please don't post code as an image, it makes it hard for people to cut & paste to replicate and test. — AChampion, Jul 13 '18 at 13:05
i posted code as text. i also included it as an image so that the output would be present. — loegare, Jul 13 '18 at 14:48

AChampion · Accepted Answer · 2018-07-14T22:51:52.830

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}')

Float format

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)))

Accounting Format

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.

Thanks! i had kind of given up on getting an answer here! Thank you for the bit on getting the whitespace to repeat, i had made it work by using '&nbsp' but this is a particularly less janky solution! i am going to fully write up what i did and edit it in, but it will absolutely use that piece! — loegare, Jul 16 '18 at 01:00

Accounting formatting in Pandas df

1 Answers1

Linked