How to display pandas DataFrame of floats using a format string for columns?

Question

I would like to display a pandas dataframe with a given format using print() and the IPython display(). For example:

df = pd.DataFrame([123.4567, 234.5678, 345.6789, 456.7890],
                  index=['foo','bar','baz','quux'],
                  columns=['cost'])
print df

         cost
foo   123.4567
bar   234.5678
baz   345.6789
quux  456.7890

I would like to somehow coerce this into printing

         cost
foo   $123.46
bar   $234.57
baz   $345.68
quux  $456.79

without having to modify the data itself or create a copy, just change the way it is displayed.

How can I do this?

Is `cost` the only float column, or are there other float columns that should not be formatted with `$`? — unutbu, Jan 05 '14 at 18:43
I'd like to do it for the cost column only (my real data has other columns) — Jason S, Jan 05 '14 at 19:00
i realize that once $ is attached, the data type automatically changes to object. — Nguai al, Jan 24 '19 at 16:00

unutbu · Accepted Answer · 2014-01-05T19:19:36.123

447

import pandas as pd
pd.options.display.float_format = '${:,.2f}'.format
df = pd.DataFrame([123.4567, 234.5678, 345.6789, 456.7890],
                  index=['foo','bar','baz','quux'],
                  columns=['cost'])
print(df)

yields

        cost
foo  $123.46
bar  $234.57
baz  $345.68
quux $456.79

but this only works if you want every float to be formatted with a dollar sign.

Otherwise, if you want dollar formatting for some floats only, then I think you'll have to pre-modify the dataframe (converting those floats to strings):

import pandas as pd
df = pd.DataFrame([123.4567, 234.5678, 345.6789, 456.7890],
                  index=['foo','bar','baz','quux'],
                  columns=['cost'])
df['foo'] = df['cost']
df['cost'] = df['cost'].map('${:,.2f}'.format)
print(df)

yields

         cost       foo
foo   $123.46  123.4567
bar   $234.57  234.5678
baz   $345.68  345.6789
quux  $456.79  456.7890

edited Jan 05 '14 at 19:19

answered Jan 05 '14 at 18:44

unutbu

842,883
184
1,785
1,677

4

This solution still works properly for me as of pandas 0.22. – Taylor D. Edmiston Feb 03 '18 at 20:31
50

as shown e.g. [here](https://stackoverflow.com/a/30691921/288875), you can modify the options only for the a given block by using `with pd.option_context('display.float_format', '${:,.2f}'.format'):` – Andre Holzner Aug 17 '18 at 16:18
8

Extra `'` before the closing parenthesis on the comment of @AndreHolzner; otherwise, it works like a charm! – dTanMan Mar 16 '20 at 04:23
This answer can be enchanced by the use of locales. For more information, look at: https://stackoverflow.com/a/320951/3288004 – Tiago Duque Jul 31 '20 at 13:27
Hey @unbunto. Kudos on your solution. Exactly what I was looking for. When I spool a df into an excel file (using openpyxl), I'm getting a "number stored as text" error. Any idea how can I avoid that? – goidelg Feb 10 '21 at 13:24
If you want to use f-strings instead, then list comprehension works nicely `df['cost'] = [f"${x:.2f}" for x in df['cost']]` – Colin Jun 27 '21 at 04:45
link to the format specs: https://docs.python.org/3/library/string.html#format-string-syntax – wisbucky Jul 13 '22 at 15:50

score 94 · Answer 2 · answered May 28 '14 at 21:24

94

If you don't want to modify the dataframe, you could use a custom formatter for that column.

import pandas as pd
pd.options.display.float_format = '${:,.2f}'.format
df = pd.DataFrame([123.4567, 234.5678, 345.6789, 456.7890],
                  index=['foo','bar','baz','quux'],
                  columns=['cost'])


print df.to_string(formatters={'cost':'${:,.2f}'.format})

yields

        cost
foo  $123.46
bar  $234.57
baz  $345.68
quux $456.79

answered May 28 '14 at 21:24

Chris Moore

1,057
7
2

3

Is it possible to get the formatter to work on a multilevel column? – user2579685 Sep 21 '15 at 14:05
4

AFAICT, this example works without the second line `pd.options.display.float_format = '${:,.2f}'.format` – pianoJames Jan 24 '18 at 15:21

score 82 · Answer 3 · edited Aug 08 '23 at 22:17

As of Pandas 0.17 there is now a styling system which essentially provides formatted views of a DataFrame using Python format strings:

import pandas as pd
import numpy as np

constants = pd.DataFrame(
    [('pi', np.pi), ('e', np.e)],
    columns=['name', 'value'])
C = constants.style.format({'name':'~~ {} ~~', 'value':'--> {:15.10f} <--'})
C

which displays

This is a view object; the DataFrame itself does not change formatting, but updates in the DataFrame are reflected in the view:

constants.name = ['pie', 'eek']
C

However it appears to have some limitations:

Adding new rows and/or columns in-place seems to cause inconsistency in the styled view (doesn't add row/column labels):
```
constants.loc[2] = dict(name='bogus', value=123.456)
constants['comment'] = ['fee', 'fie', 'fo']
constants
```
which looks ok but:
```
C
```

Formatting works only for values, not index entries:

constants = pd.DataFrame(
    [('pi', np.pi), ('e', np.e)],
    columns=['name', 'value']
    ).set_index('name')
C = constants.style.format({'name':'~~ {} ~~', 'value':'--> {:15.10f} <--'})
C

[`.format_index`](https://pandas.pydata.org/docs/reference/api/pandas.io.formats.style.Styler.format_index.html) was added in Pandas 1.4.0 — wjandrea, Aug 08 '23 at 22:19
As of Pandas 2.0.1, new row/column labels are added properly, but the values are still left unformatted. — wjandrea, Aug 08 '23 at 22:19

score 43 · Answer 4 · edited Jul 11 '17 at 14:17

43

Similar to unutbu above, you could also use applymap as follows:

import pandas as pd
df = pd.DataFrame([123.4567, 234.5678, 345.6789, 456.7890],
                  index=['foo','bar','baz','quux'],
                  columns=['cost'])

df = df.applymap("${0:.2f}".format)

edited Jul 11 '17 at 14:17

Andy Jones

4,723
2
19
24

answered Jun 24 '15 at 04:43

sedeh

7,083
6
48
65

I like using this approach before calling `df.to_csv()` to make sure all the columns in my `.csv` file have the same "digit width." Thanks! – jeschwar Oct 25 '18 at 15:44

data.dude · Answer 5 · 2020-12-14T09:15:56.477

If you do not want to change the display format permanently, and perhaps apply a new format later on, I personally favour the use of a resource manager (the with statement in Python). In your case you could do something like this:

with pd.option_context('display.float_format', '${:0.2f}'.format):
   print(df)

If you happen to need a different format further down in your code, you can change it by varying just the format in the snippet above.

score 11 · Answer 6 · answered Aug 17 '18 at 15:21

I like using pandas.apply() with python format().

import pandas as pd
s = pd.Series([1.357, 1.489, 2.333333])

make_float = lambda x: "${:,.2f}".format(x)
s.apply(make_float)

Also, it can be easily used with multiple columns...

df = pd.concat([s, s * 2], axis=1)

make_floats = lambda row: "${:,.2f}, ${:,.3f}".format(row[0], row[1])
df.apply(make_floats, axis=1)

score 11 · Answer 7 · answered Apr 10 '21 at 18:06

Instead of messing with pd.options and globally affecting the rendering of your data frames, you can use DataFrame.style.format and only style the rendering of one data frame.

df.style.format({
  'cost': lambda val: f'${val:,.2f}',
})

>>>
>>>            cost
>>> ---------------
>>> foo   $123.4567
>>> bar   $234.5678
>>> baz   $345.6789
>>> quux   $456.789

Explanation

The function df.style.format takes a dict whose keys map to the column names you want to style, and the value is a callable that receives each value for the specified column(s), and must return a string, representing the formatted value. This only affects the rendering of the data frame, and does not change the underlying data.

`df.style` creates a html-table which is not nice to look at when printed to a text-interface — user511, Nov 18 '21 at 10:29

score 5 · Answer 8 · answered Apr 20 '21 at 22:55

5

Nowadays, my preferred solution is to use a context manager just for displaying a dataframe:

with pd.option_context('display.float_format', '${:,.2f}'.format):
    display(df)

The format will be valid just for the display of this dataframe

answered Apr 20 '21 at 22:55

neves

33,186
27
159
192

score 4 · Answer 9 · answered Dec 16 '19 at 11:41

You can also set locale to your region and set float_format to use a currency format. This will automatically set $ sign for currency in USA.

import locale

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

pd.set_option("float_format", locale.currency)

df = pd.DataFrame(
    [123.4567, 234.5678, 345.6789, 456.7890],
    index=["foo", "bar", "baz", "quux"],
    columns=["cost"],
)
print(df)

        cost
foo  $123.46
bar  $234.57
baz  $345.68
quux $456.79

score 2 · Answer 10 · answered Feb 09 '19 at 07:25

summary:


    df = pd.DataFrame({'money': [100.456, 200.789], 'share': ['100,000', '200,000']})
    print(df)
    print(df.to_string(formatters={'money': '${:,.2f}'.format}))
    for col_name in ('share',):
        df[col_name] = df[col_name].map(lambda p: int(p.replace(',', '')))
    print(df)
    """
        money    share
    0  100.456  100,000
    1  200.789  200,000

        money    share
    0 $100.46  100,000
    1 $200.79  200,000

         money   share
    0  100.456  100000
    1  200.789  200000
    """

How to display pandas DataFrame of floats using a format string for columns?

10 Answers10

Explanation

Linked

Related