CODEWITHSUNDEEP
pythonpython-3.xpandasjupyter-notebooknumber-formatting
Sandy Tumma
Sandy Tumma

Reputation: 701

Format a number with commas to separate thousands

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'.

Upvotes: 70

Views: 171188

Answers (7)

ostrokach
ostrokach

Reputation: 20002

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

Upvotes: 8

1&#39;&#39;
1&#39;&#39;

Reputation: 27125

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

Upvotes: 5

Vritika Malhotra
Vritika Malhotra

Reputation: 369

Easiest method is

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

Upvotes: 21

jeffhale
jeffhale

Reputation: 4042

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.

Upvotes: 81

Ran Feldesh
Ran Feldesh

Reputation: 1179

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

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

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

Upvotes: 47

Sassaba
Sassaba

Reputation: 11

You can use apply or stack method

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

Upvotes: 0

flivan
flivan

Reputation: 471

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

Upvotes: 42

Related Questions