Format / Suppress Scientific Notation from Pandas Aggregation Results

Question

How can one modify the format for the output from a groupby operation in pandas that produces scientific notation for very large numbers?

I know how to do string formatting in python but I'm at a loss when it comes to applying it here.

df1.groupby('dept')['data1'].sum()

dept
value1       1.192433e+08
value2       1.293066e+08
value3       1.077142e+08

This suppresses the scientific notation if I convert to string but now I'm just wondering how to string format and add decimals.

sum_sales_dept.astype(str)

Solution 1

Granted, the answer I linked in the comments is not very helpful. You can specify your own string converter like so.

In [25]: pd.set_option('display.float_format', lambda x: '%.3f' % x)

In [28]: Series(np.random.randn(3))*1000000000
Out[28]: 
0    -757322420.605
1   -1436160588.997
2   -1235116117.064
dtype: float64

I'm not sure if that's the preferred way to do this, but it works.

Converting numbers to strings purely for aesthetic purposes seems like a bad idea, but if you have a good reason, this is one way:

In [6]: Series(np.random.randn(3)).apply(lambda x: '%.3f' % x)
Out[6]: 
0     0.026
1    -0.482
2    -0.694
dtype: object

Solution 2

Here is another way of doing it, similar to Dan Allan's answer but without the lambda function:

>>> pd.options.display.float_format = '{:.2f}'.format
>>> Series(np.random.randn(3))
0    0.41
1    0.99
2    0.10

or

>>> pd.set_option('display.float_format', '{:.2f}'.format)

Solution 3

You can use round function just to suppress scientific notation for specific dataframe:

df1.round(4)

or you can suppress is globally by:

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

Solution 4

If you want to style the output of a data frame in a jupyter notebook cell, you can set the display style on a per-dataframe basis:

df = pd.DataFrame({'A': np.random.randn(4)*1e7})
df.style.format("{:.1f}")

See the documentation here.

Solution 5

Setting a fixed number of decimal places globally is often a bad idea since it is unlikely that it will be an appropriate number of decimal places for all of your various data that you will display regardless of magnitude. Instead, try this which will give you scientific notation only for large and very small values (and adds a thousands separator unless you omit the ","):

pd.set_option('display.float_format', lambda x: '%,g' % x)

Or to almost completely suppress scientific notation without losing precision, try this:

pd.set_option('display.float_format', str)

View more solutions

Comments

[Dan Allan]

possible duplicate of Suppressing scientific notation in pandas?

[horatio1701d]

I saw that question but I'm not sure how that helps me. I'm just looking to preserve the current dtype which is float and simply show all decimals in the result instead of scientific notation.

[TomAugspurger]

That is probably just a display thing. But if you think there's something particular about your problem makes yours different from the one in Dan's link then you need to post more information about your problem, preferably with a small dataset that reproduces the problem. Also what are the dtypes on your result?

[Josh]

Thanks Dan. Do you know how to reset pandas options?

[muellermarkus]

@Josh To temporarily set options in pandas, you can use pandas.option_context (see pandas.pydata.org/pandas-docs/stable/generated/…).

[Steven C. Howell]

I think using a format string would be more approachable to team members that are less familiar with Python, and might not understand lambda functions.

[matanster]

It's oftentimes not for aesthetic purposes, but for quicker skimming of information via the visual cortex over large numeric dataframes.

[driven_spider]

pd.set_option('display.float_format', lambda x: '%.3f' % x) worked for me too

[576i]

This works and you can also use the newer f-string notation. Like pd.set_option('display.float_format', lambda x: f'{x:,.3f}') if you want a thousand separator as well.

[Michael]

by far one of the most undervoted answers.

[Ethan]

Thanks! The first option fails though under certain cases.