Format a number with commas to separate thousands

Question

I have a large dataframe, which has a column called Lead Rev. This column is a field of numbers such as (100000 or 5000 etc.) I want to know how to format these numbers to show commas as thousand separators. The dataset has over 200,000 rows.

Is it something like: '{:,}'.format('Lead Rev')

which gives this error:

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-182-5fe9c827d80b> in <module>()
----> 1 '{:,}'.format('Lead Rev')

ValueError: Cannot specify ',' or '_' with 's'.

When you say 'numbers', are they int, float or both? Also, the [format spec `'{:,}'` doesn't contain any type '%'/'f'/etc., so it will default to 's' and try to apply it to all types](https://docs.python.org/3/library/string.html#format-string-syntax). That's *supposed* to work, but if it doesn't, you'd have to fall back to column-specific, type-specific format specifiers/ custom formatters. It helps when reporting issues to say which version of pandas. — smci, Apr 30 '20 at 05:57
Does this answer your question? [Suppressing scientific notation in pandas?](https://stackoverflow.com/questions/17737300/suppressing-scientific-notation-in-pandas) — Josiah Yoder, Jul 20 '23 at 17:30
This question isn't *exactly* a duplicate of the question I just voted to close it towards, but it's a subset of the topic and fully answered by the top-voted question there. — Josiah Yoder, Jul 20 '23 at 17:30

jeffhale · Answer 1 · 2020-02-09T21:21:40.163

74

To make all your floats show comma separators by default in pandas versions 0.23 through 0.25 set the following:

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

https://pandas.pydata.org/pandas-docs/version/0.23.4/options.html

In pandas version 1.0 this leads to some strange formatting in some cases.

edited Feb 09 '20 at 21:21

answered Sep 19 '18 at 12:15

jeffhale

3,759
7
40
56

18

add ".2f" after comma will show only 2 digits other than some strange float-style digits. `pd.options.display.float_format = '{:,.2f}'.format` – Will Wu Jul 31 '20 at 10:18
2

Seems doesn't work for `int` . – Sean.H Feb 10 '23 at 06:28
Please include the first upvoted comment into your answer. The .2f is a very nice default when including thousands. – Josiah Yoder Jul 20 '23 at 16:54

Ran Feldesh · Answer 2 · 2019-12-20T14:26:11.060

44

df.head().style.format("{:,.0f}") (for all columns)

df.head().style.format({"col1": "{:,.0f}", "col2": "{:,.0f}"}) (per column)

https://pbpython.com/styling-pandas.html

edited Dec 20 '19 at 14:26

answered Jul 18 '19 at 15:42

Ran Feldesh

1,111
11
17

4

This answer won't change the values. It is strictly for presentation. Perfect. Thank you. - E – Edmund's Echo Oct 08 '19 at 21:05
2

FYI, this requires jinja2: `ImportError: Missing optional dependency 'jinja2'. DataFrame.style requires jinja2. Use pip or conda to install jinja2.` – Henry Henrinson Jan 16 '20 at 14:39
@Henry - which IDE/Notebook client are you using? For a standard Jupyter Lab or Jupyter Notebook installation and `pip install pandas` this works out of the box. – Ran Feldesh Jan 16 '20 at 18:04

score 43 · Answer 3 · answered May 12 '17 at 22:27

43

You can use apply() to get the desired result. This works with floating too

import pandas as pd

series1 = pd.Series({'Value': 353254})
series2 = pd.Series({'Value': 54464.43})
series3 = pd.Series({'Value': 6381763761})

df = pd.DataFrame([series1, series2, series3])
print(df.head())

         Value
0  3.532540e+05
1  5.446443e+04
2  6.381764e+09

df['Value'] = df.apply(lambda x: "{:,}".format(x['Value']), axis=1)
print(df.head())

             Value
0        353,254.0
1        54,464.43
2  6,381,763,761.0

answered May 12 '17 at 22:27

flivan

473
4
8

3

How is it different with `df['Value'] = df['Value'].apply(lambda x: "{:,}".format(x)`? – ytu Feb 03 '18 at 14:31
What if you want to have two decimals only? How do you incorporate the `%.2f` information? – FaCoffee Jun 19 '18 at 10:47
3

This answer is misleading because it results in a column that is a string instead of a float. – Nick Morgan Oct 11 '18 at 18:27
2

You can take the lambda out of it altogether, too.: `df['Value'] = df['Value'].apply('{:,}'.format)` since that str.format takes one argument. – Evan V Jan 10 '19 at 13:17
How to make it apply to `int` as well? – Ian CT Feb 16 '23 at 00:05

score 19 · Answer 4 · answered Sep 16 '20 at 21:29

19

Easiest method is

df = df.style.format('{:,}')

answered Sep 16 '20 at 21:29

Vritika Malhotra

349
2
4

score 6 · Answer 5 · answered Feb 04 '21 at 04:37

6

Possibly the most concise solution: df[column].map('{:,d}'.format).

answered Feb 04 '21 at 04:37

1''

26,823
32
143
200

score 5 · Answer 6 · answered Feb 17 '22 at 18:23

In Pandas version > 1.3.0, you can set the thousands separator either globally:

pd.set_option("styler.format.thousands", ",")

or for a specific dataframe:

df.style.format(thousands=",")

https://pandas.pydata.org/docs/reference/api/pandas.io.formats.style.Styler.format.html

score 0 · Answer 7 · answered Mar 04 '19 at 19:25

0

You can use apply or stack method

df.apply(lambda x: x.str.replace(',','.'))
df.stack().str.replace(',','.').unstack()

answered Mar 04 '19 at 19:25

Sassaba

11
1

Format a number with commas to separate thousands

7 Answers7

Linked

Related