Accounting Formatting In Pandas Df

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}

Solution 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 < 0else'{:,.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.