17

As Python newbie I recently discovered that with Py 2.7 I can do something like:

print '{:20,.2f}'.format(123456789)

which will give the resulting output:

123,456,789.00

I'm now looking to have a similar outcome for a pandas df so my code was like:

import pandas as pd
import random
data = [[random.random()*10000 for i in range(1,4)] for j in range (1,8)]
df = pd.DataFrame (data)
print '{:20,.2f}'.format(df)

In this case I have the error:

 Unknown format code 'f' for object of type 'str'

Any suggestions to perform something like '{:20,.2f}'.format(df) ?

As now my idea is to index the dataframe (it's a small one), then format each individual float within it, might be assign astype(str), and rebuild the DF ... but looks so looks ugly :-( and I'm not even sure it'll work ..

What do you think ? I'm stuck ... and would like to have a better format for my dataframes when these are converted to reportlabs grids.

kynan
  • 13,235
  • 6
  • 79
  • 81
Fabio Pomi
  • 317
  • 1
  • 3
  • 9

1 Answers1

27
import pandas as pd
import numpy as np
data = np.random.random((8,3))*10000
df = pd.DataFrame (data)
pd.options.display.float_format = '{:20,.2f}'.format
print(df)

yields (random output similar to)

                     0                    1                    2
0             4,839.01             6,170.02               301.63
1             4,411.23             8,374.36             7,336.41
2             4,193.40             2,741.63             7,834.42
3             3,888.27             3,441.57             9,288.64
4               220.13             6,646.20             3,274.39
5             3,885.71             9,942.91             2,265.95
6             3,448.75             3,900.28             6,053.93

The docstring for pd.set_option or pd.describe_option explains:

display.float_format: [default: None] [currently: None] : callable
        The callable should accept a floating point number and return
        a string with the desired format of the number. This is used
        in some places like SeriesFormatter.
        See core.format.EngFormatter for an example.
unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • Thank you so much for this ! Looks perfect :-) for the dataframe. It's a great step forward .. now I'm facing the next topic which is about reportlab .. ha ha .. funny .. when DF is converted to grids then I'll apparently loose the format .. uffff.... but I'll handle in some way .. Great help from you !! Thank you so much !! – Fabio Pomi Aug 23 '13 at 15:46
  • 3
    Can also do this as `pd.options.display.float_format = '{:20,.2f}'.format` or `pd.set_option('float_format', '{:20,.2f}'.format)` :) http://pandas.pydata.org/pandas-docs/stable/basics.html#working-with-package-options – Andy Hayden Aug 30 '13 at 19:59