40

I've seen this and this on formatting floating-point numbers for display in pandas, but I'm interested in doing the same thing for integers.

Right now, I have: 

pd.options.display.float_format = '{:,.2f}'.format

That works on the floats in my data, but will either leave annoying trailing zeroes on integers that are cast to floats, or I'll have plain integers that don't get formatted with commas.

The pandas docs mention a SeriesFormatter class about which I haven't been able to find any information.

Alternatively, if there's a way to write a single string formatter that will format floats as '{:,.2f}' and floats with zero trailing decimal as '{:,d}', that'd work too.

smci
  • 32,567
  • 20
  • 113
  • 146
Michael K
  • 2,196
  • 6
  • 34
  • 52

3 Answers3

21

You could monkey-patch pandas.io.formats.format.IntArrayFormatter:

import contextlib
import numpy as np
import pandas as pd
import pandas.io.formats.format as pf
np.random.seed(2015)

@contextlib.contextmanager
def custom_formatting():
    orig_float_format = pd.options.display.float_format
    orig_int_format = pf.IntArrayFormatter

    pd.options.display.float_format = '{:0,.2f}'.format
    class IntArrayFormatter(pf.GenericArrayFormatter):
        def _format_strings(self):
            formatter = self.formatter or '{:,d}'.format
            fmt_values = [formatter(x) for x in self.values]
            return fmt_values
    pf.IntArrayFormatter = IntArrayFormatter
    yield
    pd.options.display.float_format = orig_float_format
    pf.IntArrayFormatter = orig_int_format


df = pd.DataFrame(np.random.randint(10000, size=(5,3)), columns=list('ABC'))
df['D'] = np.random.random(df.shape[0])*10000

with custom_formatting():
    print(df)

yields

      A     B     C        D
0 2,658 2,828 4,540 8,961.77
1 9,506 2,734 9,805 2,221.86
2 3,765 4,152 4,583 2,011.82
3 5,244 5,395 7,485 8,656.08
4 9,107 6,033 5,998 2,942.53

while outside of the with-statement:

print(df)

yields

      A     B     C            D
0  2658  2828  4540  8961.765260
1  9506  2734  9805  2221.864779
2  3765  4152  4583  2011.823701
3  5244  5395  7485  8656.075610
4  9107  6033  5998  2942.530551
kynan
  • 13,235
  • 6
  • 79
  • 81
unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • Nice solution, is there a way to turn this off too? – leon yin Mar 16 '16 at 16:16
  • 3
    @leonyin: You could use a context manager. I've modified the post to show what I mean. – unutbu Mar 16 '16 at 19:46
  • can html formatting be maintained from calling df? print(df) is unformatted – leon yin Mar 16 '16 at 19:54
  • @leonyin: `print(df.to_html())` inside the `with-statement` produces custom-formatted HTML. – unutbu Mar 16 '16 at 23:38
  • Neat hack, you should submit this to the pandas cookbook, and as comment in any enhance request for a `pd.options.display.int_format`. I take it this doesn't survive pickling, worth noting. – smci Jan 28 '21 at 01:13
11

Another option for Jupyter notebooks is to use df.style.format('{:,}'), but it only works on a single dataframe as far as I know, so you would have to call this every time:

table.style.format('{:,}')
          col1       col2
0s   9,246,452  6,669,310
>0   2,513,002  5,090,144

table
       col1     col2
0s  9246452  6669310
>0  2513002  5090144

Styling — pandas 1.1.2 documentation

John
  • 1,335
  • 12
  • 17
  • It seems this doesn't work when the dataframe contains a `str` column, raising the following error: `ValueError: Cannot specify ',' with 's'.` Is there a way to overcome this problem using style functions? – Farid Nov 20 '20 at 03:30
  • 2
    @Farid You might have a couple options, the docs (linked above) say that `.format` can take a function, so you could have a function that checks the type and then formats strings differently than numerics. Another alternative is that `.format` can take a `dict` where the keys specify columns, and the values are different formats for different columns. – John Nov 22 '20 at 14:01
6

Starting with Pandas 1.3.0, you can specify df.style.format(thousands=',') to use commas to separate thousands in floats, complex numbers, and integers.

See docs: Styler.format

wjandrea
  • 28,235
  • 9
  • 60
  • 81
ostrokach
  • 17,993
  • 11
  • 78
  • 90