3

I found answers on the question on how to load csv files with european formatting into pandas dataframes and display them in the US format (2,345.21). However how can I display floats saved in the US format in the european format (2.345,21) in pandas.

I tried to just change dot and commas here:

from

pd.options.display.float_format = '{:,.2f}'.format

to

pd.options.display.float_format = '{:.,2f}'.format

but this doesn't work. Another possibility could be to change the type to string and then replace dots with commas, but isn't there a more elegant way?

It seems it works with locale aware seperators. If your operating system has the locale Germany it is pretty easy:

import pandas as pd
import locale
locale.setlocale(locale.LC_ALL, '')
pd.options.display.float_format = '{:n}'.format
df = pd.DataFrame([2123.4567, 234.5678, 345.6789, 456.7890],
                  index=['foo','bar','baz','quux'],
                  columns=['cost'])
print(df)

results to:

         cost
foo  2.123,46
bar   234,568
baz   345,679
quux  456,789

However now I am dealing with the problem to format the precision and the format for high numbers. As the result of the following format command is somewhat surprising.

#precision
pd.options.display.float_format = '{:.2n}'.format
print(df)

        cost
foo  2,1e+03
bar  2,3e+02
baz  3,5e+02
quux 4,6e+02

#high numbers
pd.options.display.float_format = '{:n}'.format
df = pd.DataFrame([1222333, 234.5678, 345.6789, 456.7890],
                  index=['foo','bar','baz','quux'],
                  columns=['cost'])
print(df)

            cost
foo  1,22233e+06
bar      234,568
baz      345,679
quux     456,789
Stephan
  • 113
  • 14

2 Answers2

1

Before you begin:

conda install babel

Then try this:

from babel.numbers import decimal, format_decimal
format_decimal(22222345.22, format='#,##0.##;-#', locale='de')
pizza lover
  • 503
  • 4
  • 15
  • Thanks! However, I don't know if I overlook something, but I don't see how I can use this function on a pandas dataframe. – Stephan Dec 24 '18 at 10:24
  • 1
    I sticked to the answer. Of course I overlooked the `applymap()` function, which I used to apply the function to the whole dateframe. I don't think this is a very nice way (which would be setting formatting options, imho) but it works and is still better than transforming the entries to strings and then format them! – Stephan Dec 25 '18 at 11:07
  • Glad you figured it out! – pizza lover Dec 26 '18 at 14:18
0

You can control the display of floating numbers from a pandas dataframe more precisely in Continental European format using only Python's locale module:

import locale

First, you need to have Python set up for a Continental European country's preferences.

If your operating system is already set up for such a country such as Germany, then

locale.setlocale(locale.LC_ALL, '')

is enough as you mentioned. Otherwise, one needs to set up Python for such a country such as Germany (see "A Beginner’s Guide to Python’s locale Module" for more on this) :

locale.setlocale(locale.LC_ALL, 'de_DE')

Once the Python is set up as such, you can use a context manager along with locale.format_string() whenever you need to control the display of floating point numbers from a pandas dataframe:

df = pd.DataFrame([1222333, 234.5678, 345.6789, 456.7890],
              index=['foo','bar','baz','quux'],
              columns=['cost'])
with pd.option_context('display.float_format',
    lambda x:locale.format_string('%12.2f', x)):
    print(df)

             cost
foo    1222333.00
bar        234.57
baz        345.68
quux       456.79

locale.format_string() has a grouping= option for separation of thousands but it does not appear to work when comma replaces dot as the decimal point.

Alper
  • 299
  • 2
  • 12