Format certain floating dataframe columns into percentage in pandas

Question

I am trying to write a paper in IPython notebook, but encountered some issues with display format. Say I have following dataframe df, is there any way to format var1 and var2 into 2 digit decimals and var3 into percentages.

       var1        var2         var3    
id                                              
0    1.458315    1.500092   -0.005709   
1    1.576704    1.608445   -0.005122    
2    1.629253    1.652577   -0.004754    
3    1.669331    1.685456   -0.003525   
4    1.705139    1.712096   -0.003134   
5    1.740447    1.741961   -0.001223   
6    1.775980    1.770801   -0.001723    
7    1.812037    1.799327   -0.002013    
8    1.853130    1.822982   -0.001396    
9    1.943985    1.868401    0.005732

The numbers inside are not multiplied by 100, e.g. -0.0057=-0.57%.

In case if anyone is looking at this question after 2014, look at my answer for a concise answer. — BhishanPoudel, May 20 '19 at 21:35
The answers work for immediate formatting, but I was hoping to "attach" the format to the column so that I could continue doing other stuff with the dataframe and it would always print that column in that format (unless I reset the format to something else). Is this possible? — krubo, Jul 22 '19 at 15:48

linqu · Answer 1 · 2018-01-31T08:45:08.683

200

The accepted answer suggests to modify the raw data for presentation purposes, something you generally do not want. Imagine you need to make further analyses with these columns and you need the precision you lost with rounding.

You can modify the formatting of individual columns in data frames, in your case:

output = df.to_string(formatters={
    'var1': '{:,.2f}'.format,
    'var2': '{:,.2f}'.format,
    'var3': '{:,.2%}'.format
})
print(output)

For your information '{:,.2%}'.format(0.214) yields 21.40%, so no need for multiplying by 100.

You don't have a nice HTML table anymore but a text representation. If you need to stay with HTML use the to_html function instead.

from IPython.core.display import display, HTML
output = df.to_html(formatters={
    'var1': '{:,.2f}'.format,
    'var2': '{:,.2f}'.format,
    'var3': '{:,.2%}'.format
})
display(HTML(output))

Update

As of pandas 0.17.1, life got easier and we can get a beautiful html table right away:

df.style.format({
    'var1': '{:,.2f}'.format,
    'var2': '{:,.2f}'.format,
    'var3': '{:,.2%}'.format,
})

edited Jan 31 '18 at 08:45

answered Mar 23 '16 at 10:20

linqu

11,320
8
55
67

1

If you have n or a variable amount of columns in your dataframe and you want to apply the same formatting across all columns, but you may not know all the column headers in advance, you don't have to put the formatters in a dictionary, you can do a list and do it creatively like this: output = df.to_html(formatters=n * ['{:,.2%}'.format]) – Afflatus Aug 05 '16 at 20:47
A standard set of these in a dict with attr access would be great. – Wes Turner Jan 13 '19 at 06:52
3

The parts `.format` are not needed, you may omit them. – MarianD Feb 02 '20 at 11:33
7

`df.style.format({'var3': '{:,.2%}'})` - this is not working. Values remain unchanged i.e. without % – zwornik Apr 02 '20 at 11:38
3

@zwornik `%` needs to be outside the brackets in `'{:.2f}%'` – theFrok Jun 03 '20 at 16:13
@theFrok Note there's no `f` in `'{:,.2%}'`. @zwornik Apparently `df.style.format` doesn't change the DataFrame, but IronPython renders the result as HTML. You can use `df.style.format(...).render()` to get the HTML yourself. Or, use `df.apply` directly, which is what happens internally, per [documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html). – JuSTMOnIcAjUSTmONiCAJusTMoNICa Feb 10 '21 at 15:43
Is there a way to add this as the default representation of a data frame? I have tried changing `__str__` and `__repr__` but neither seems to alter the output when putting `df` at the end of my Jupyter/IPython cell. – Raisin Sep 11 '22 at 23:57
still doesn't work `df.style.format({'perc count': '{:,.2}%'})` Why does this answer have so many upvotes? '%' in or out of the curly bracket... – GenDemo May 29 '23 at 23:09
Ahhh, figured it out as per one of the comments below : very important, you need to assign a df to the formated df. It is not an option you can set on an existing df. eg: `df = df.style.format({'perc count': '{:,.2}%'})` – GenDemo May 29 '23 at 23:16

score 66 · Answer 2 · edited Jan 28 '21 at 19:46

66

You could also set the default format for float :

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

Use '{:.2%}' instead of '{:.2f}%' - The former converts 0.41 to 41.00% (correctly), the latter to 0.41% (incorrectly)

edited Jan 28 '21 at 19:46

Community

1
1

answered Jul 28 '15 at 09:10

Romain Jouin

4,448
3
49
79

2

Good to know and relevant to OP's question about outputting in an python notebook – Jim Oct 09 '15 at 18:04
3

And if the percentages are still given in decimals (e.g. when using `df.pct_change()`): `pd.options.display.float_format = '{:.2%}'.format` – Hugo Ideler Jun 23 '18 at 19:51
1

of course this would affect all of your dataframes, so you'd have to unset it again to display non-percentage floats. – fantabolous May 18 '23 at 02:44
1

As per @fantabolous ' comment, this is not really useful. – GenDemo May 29 '23 at 23:10

score 49 · Accepted Answer · edited Mar 29 '18 at 07:31

49

replace the values using the round function, and format the string representation of the percentage numbers:

df['var2'] = pd.Series([round(val, 2) for val in df['var2']], index = df.index)
df['var3'] = pd.Series(["{0:.2f}%".format(val * 100) for val in df['var3']], index = df.index)

The round function rounds a floating point number to the number of decimal places provided as second argument to the function.

String formatting allows you to represent the numbers as you wish. You can change the number of decimal places shown by changing the number before the f.

p.s. I was not sure if your 'percentage' numbers had already been multiplied by 100. If they have then clearly you will want to change the number of decimals displayed, and remove the hundred multiplication.

edited Mar 29 '18 at 07:31

Michael

5,095
2
13
35

answered Jun 01 '14 at 16:22

Woody Pride

13,539
9
48
62

2

Thanks, will this change the actual values within each column? – user3576212 Jun 01 '14 at 17:02
Yes, if that is not desired, then just create new columns with those variables in. As far as I know, there is no way to specify how output appears beyond what the data actually are. – Woody Pride Jun 01 '14 at 17:03
1

To round the values in a series you can also just use `df['var2'].round(2)` – Ben Southgate Jun 17 '14 at 16:19
4

You could also set the default format for float : pd.options.display.float_format = '{:.2f}%'.format – Romain Jouin May 15 '15 at 21:03
1

@romain That's a great suggestion (for some use-cases) it should be its own answer (so I can upvote it) Though it does need tweak to multiply by 100. – Frames Catherine White Jul 28 '15 at 02:39
as suggested by @linqu, you generally do not want to modify data for display. – Tim May 14 '20 at 15:28
1

Is there a way to display a column as percentage without converting it to a string? – DISC-O Jun 09 '20 at 19:15
1

see below answer which is better – Woody Pride Jun 10 '20 at 02:08

BhishanPoudel · Answer 4 · 2021-04-04T15:42:10.590

Often times we are interested in calculating the full significant digits, but for the visual aesthetics, we may want to see only few decimal point when we display the dataframe.

In jupyter-notebook, pandas can utilize the html formatting taking advantage of the method called style.

For the case of just seeing two significant digits of some columns, we can use this code snippet:

Given dataframe

import numpy as np
import pandas as pd

df = pd.DataFrame({'var1': [1.458315, 1.576704, 1.629253, 1.6693310000000001, 1.705139, 1.740447, 1.77598, 1.812037, 1.85313, 1.9439849999999999],
          'var2': [1.500092, 1.6084450000000001, 1.652577, 1.685456, 1.7120959999999998, 1.741961, 1.7708009999999998, 1.7993270000000001, 1.8229819999999999, 1.8684009999999998],
          'var3': [-0.0057090000000000005, -0.005122, -0.0047539999999999995, -0.003525, -0.003134, -0.0012230000000000001, -0.0017230000000000001, -0.002013, -0.001396, 0.005732]})

print(df)
       var1      var2      var3
0  1.458315  1.500092 -0.005709
1  1.576704  1.608445 -0.005122
2  1.629253  1.652577 -0.004754
3  1.669331  1.685456 -0.003525
4  1.705139  1.712096 -0.003134
5  1.740447  1.741961 -0.001223
6  1.775980  1.770801 -0.001723
7  1.812037  1.799327 -0.002013
8  1.853130  1.822982 -0.001396
9  1.943985  1.868401  0.005732

Style to get required format

    df.style.format({'var1': "{:.2f}",'var2': "{:.2f}",'var3': "{:.2%}"})

Gives:

     var1   var2    var3
id          
0   1.46    1.50    -0.57%
1   1.58    1.61    -0.51%
2   1.63    1.65    -0.48%
3   1.67    1.69    -0.35%
4   1.71    1.71    -0.31%
5   1.74    1.74    -0.12%
6   1.78    1.77    -0.17%
7   1.81    1.80    -0.20%
8   1.85    1.82    -0.14%
9   1.94    1.87    0.57%

Update

If display command is not found try following:

from IPython.display import display

df_style = df.style.format({'var1': "{:.2f}",'var2': "{:.2f}",'var3': "{:.2%}"})

display(df_style)

Requirements

To use display command, you need to have installed Ipython in your machine.
The display command does not work in online python interpreter which do not have IPyton installed such as https://repl.it/languages/python3
The display command works in jupyter-notebook, jupyter-lab, Google-colab, kaggle-kernels, IBM-watson,Mode-Analytics and many other platforms out of the box, you do not even have to import display from IPython.display

This is a way better answer than the accepted one. Changing the formatting is much preferable to actually changing the underlying values. — philippjfr, Sep 15 '19 at 19:30
@Poudel This is not working. I have used exacly the same code as yours and var3 is not formatted as percentage — zwornik, Apr 02 '20 at 11:25
@zwornik try `display(df.style.format({'var1': "{:.2f}",'var2': "{:.2f}",'var3': "{:.2%}"}))` — BhishanPoudel, Apr 03 '20 at 01:31
@Poudel It worked now. There is one superflous bracket at the end. It should be: `df_style = df.style.format({'var1': "{:.2f}",'var2': "{:.2f}",'var3': "{:.2%}"})` Thanks! — zwornik, Apr 03 '20 at 07:01
Had an issue with the index being non unique, so just had to df.reset_index(inplace= True) and then apply the .style.format. worked perfectly, thank you. — Fed, Dec 14 '20 at 04:16
As a fact, When I use this answer, I got the message : `AttributeError: 'Styler' object has no attribute 'head'` — fransua, Jul 01 '22 at 08:40
Ah, very important differentiator here, you need to assign a df to the formated df. It is not simply an option you can set. — GenDemo, May 29 '23 at 23:15

mdeff · Answer 5 · 2017-04-06T12:30:26.203

28

As suggested by @linqu you should not change your data for presentation. Since pandas 0.17.1, (conditional) formatting was made easier. Quoting the documentation:

You can apply conditional formatting, the visual styling of a DataFrame depending on the data within, by using the DataFrame.style property. This is a property that returns a pandas.Styler object, which has useful methods for formatting and displaying DataFrames.

For your example, that would be (the usual table will show up in Jupyter):

df.style.format({
    'var1': '{:,.2f}'.format,
    'var2': '{:,.2f}'.format,
    'var3': '{:,.2%}'.format,
})

edited Apr 06 '17 at 12:30

answered Mar 28 '17 at 00:45

mdeff

1,566
17
21

4

This is not working. I have used exacly the same code as yours – zwornik Apr 02 '20 at 11:32
this will give html output, and be useful for notebooks. for terminal output, printing to files etc, the `to_string` method is good. It needs pandas version 1.5 or higher. – LudvigH Aug 03 '23 at 10:27

score 18 · Answer 6 · answered Feb 14 '18 at 17:14

Just another way of doing it should you require to do it over a larger range of columns

using applymap

df[['var1','var2']] = df[['var1','var2']].applymap("{0:.2f}".format)
df['var3'] = df['var3'].applymap(lambda x: "{0:.2f}%".format(x*100))

applymap is useful if you need to apply the function over multiple columns; it's essentially an abbreviation of the below for this specific example:

df[['var1','var2']].apply(lambda x: map(lambda x:'{:.2f}%'.format(x),x),axis=1)

Great explanation below of apply, map applymap:

Difference between map, applymap and apply methods in Pandas

circld · Answer 7 · 2018-01-19T18:33:24.130

As a similar approach to the accepted answer that might be considered a bit more readable, elegant, and general (YMMV), you can leverage the map method:

# OP example
df['var3'].map(lambda n: '{:,.2%}'.format(n))

# also works on a series
series_example.map(lambda n: '{:,.2%}'.format(n))

Performance-wise, this is pretty close (marginally slower) than the OP solution.

As an aside, if you do choose to go the pd.options.display.float_format route, consider using a context manager to handle state per this parallel numpy example.

mirekphd · Answer 8 · 2023-08-09T09:20:32.550

4

style.format is vectorized, so we can simply apply it to the entire df (or just its numerical columns):

df[num_cols].style.format('{:,.3f}%')

Note that if df contains only 1 column and is in fact a Series, it will first require conversion to pandas DataFrame, e.g. with pd.DataFrame(df[num_col]).style.format, or as pointed below: df[num_col].to_frame().style.format).

edited Aug 09 '23 at 09:20

answered Dec 08 '20 at 18:49

mirekphd

4,799
3
38
59

2

The series should be converted to data frame first: df[num_cols].to_frame().style.format('{:,.3f}%') – Sahar Feb 12 '21 at 13:59

score 0 · Answer 9 · edited Sep 08 '22 at 01:40

0

The list comprehension has an assured result, I'm using it successfully I think you may use python list comprehension as follow:

df['var1'] = ["{:.2f}".format(i) for i in df['var1'] ]
df['var2'] = ["{:.2f}".format(i) for i in df['var2'] ]
df['var3'] = ["{:.2%}".format(i) for i in df['var3'] ]

Thanks

edited Sep 08 '22 at 01:40

HoangYell

4,100
37
31

answered Jun 21 '22 at 18:17

Sam Ahmed Alone

11
1

score 0 · Answer 10 · answered Feb 09 '23 at 08:06

Following from this answer I used the apply function on the given series. In my case, I was interested in showing value_counts for my Series with percentage formatting.

I did:

df['my_col'].value_counts(normalize=True).apply(lambda x: "{0:.2f}%".format(x*100))
# Incident             88.16%
# StreetWorks          3.29% 
# Accident             2.36%
# ...

Instead of just

df['my_col'].value_counts(normalize=True)
# Incident             0.881634
# StreetWorks          0.032856
# Accident             0.023589
# ...

Format certain floating dataframe columns into percentage in pandas

10 Answers10

Given dataframe

Style to get required format

Update

Requirements

Linked

Related