Javier Cárdenas Javier Cárdenas - 1 year ago 702
Python Question

Formatting thousand separator for integers in a pandas dataframe

I'm trying to use

like the example below to format a number in a pandas dataframe:

# This works for floats and integers
print '{:,}'.format(20000)
# 20,000
print '{:,}'.format(20000.0)
# 20,000.0

The problem is that with a dataframe that has integers does not work, and in a dataframe with float works ok. See the examples:

# Does not work. The format stays the same, does not show thousands separator
df_int = DataFrame({"A": [20000, 10000]})
print df_int.to_html(float_format=lambda x: '{:,}'.format(x))

# Example of result
# <tr>
# <th>0</th>
# <td> 20000</td>
# </tr

# Works OK
df_float = DataFrame({"A": [20000.0, 10000.0]})
print df_float.to_html(float_format=lambda x: '{:,}'.format(x))

# Example of result
# <tr>
# <th>0</th>
# <td>20,000.0</td>
# </tr>

What i'm doing wrong?

Answer Source

The formatters parameter in to_html will take a dictionary of column names mapped to a formatting function. Below has an example of a function to build a dict that maps the same function to both floats and ints.

In [250]: num_format = lambda x: '{:,}'.format(x)

In [246]: def build_formatters(df, format):
     ...:     return {column:format 
     ...:               for (column, dtype) in df.dtypes.iteritems()
     ...:               if dtype in [np.dtype('int64'), np.dtype('float64')]}

In [247]: formatters = build_formatters(df_int, num_format)

In [249]: print df_int.to_html(formatters=formatters)
<table border="1" class="dataframe">
    <tr style="text-align: right;">
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download