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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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
Method 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
Method 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
Method 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)
Method 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.
Method 6
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)
Method 7
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.
Method 8
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
Method 9
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
Method 10
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
"""
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0