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?
This question is related to
python
python-2.7
pandas
ipython
dataframe
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.
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)
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)
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
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
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
"""
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
Source: Stackoverflow.com