How to display pandas DataFrame of floats using a format string for columns?
PythonPython 2.7PandasIpythonDataframePython Problem Overview
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?
Python Solutions
Solution 1 - Python
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 - Python
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 - Python
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 - Python
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 - Python
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.
Solution 6 - Python
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)
Solution 7 - Python
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.
Solution 8 - Python
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
Solution 9 - Python
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
Solution 10 - Python
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
"""