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?

Solution 1

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

Solution 2

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

Solution 3

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'])
  constants.set_index('name',inplace=True)
  C = constants.style.format({'name': '~~ {} ~~', 'value':'--> {:15.10f} <--'})
  C
  

Solution 4

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)

Solution 5

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.

View more solutions

Comments

[unutbu]

Is cost the only float column, or are there other float columns that should not be formatted with $?

[Jason S]

I'd like to do it for the cost column only (my real data has other columns)

[Nguai al]

i realize that once $ is attached, the data type automatically changes to object.

[user2579685]

Is it possible to get the formatter to work on a multilevel column?

[pianoJames]

AFAICT, this example works without the second line pd.options.display.float_format = '${:,.2f}'.format

[Taylor D. Edmiston]

This solution still works properly for me as of pandas 0.22.

[Andre Holzner]

as shown e.g. here, you can modify the options only for the a given block by using with pd.option_context('display.float_format', '${:,.2f}'.format'):

[jeschwar]

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!

[Jms]

Can I use the DataFrame.style from inside the interpreter?

[dTanMan]

Extra ' before the closing parenthesis on the comment of @AndreHolzner; otherwise, it works like a charm!

[Tiago Duque]

This answer can be enchanced by the use of locales. For more information, look at: stackoverflow.com/a/320951/3288004

[goidelg]

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?

[Colin]

If you want to use f-strings instead, then list comprehension works nicely df['cost'] = [f"${x:.2f}" for x in df['cost']]

[user511]

df.style creates a html-table which is not nice to look at when printed to a text-interface