How to insert a comma as a thousands separator in a pandas dataframe column?

Question

I'm trying to format the Dollar Amount column to have a comma thousands separator for easier viewing, but I haven't been able to figure it out. Can someone please show me the way?

import pandas as pd
df = pd.read_excel('filename.xlsx') 
df['Dollar Amount'].head()

Index  Dollar Amount
0      5721.48
1      4000.00
2      4769.00
3       824.07
4       643.60
5       620.00

Name: Dollar Amount, dtype: float64

Does this answer your question? [Format a number with commas to separate thousands](https://stackoverflow.com/questions/43102734/format-a-number-with-commas-to-separate-thousands) — Josiah Yoder, Jul 20 '23 at 17:00

score 19 · Accepted Answer · answered Nov 21 '17 at 03:06

19

Notice it will convert your float type to object

df.DollarAmount.apply(lambda x : "{:,}".format(x))
Out[509]: 
0    5,721.48
1     4,000.0
2     4,769.0
3      824.07
4       643.6
5       620.0
Name: DollarAmount, dtype: object

answered Nov 21 '17 at 03:06

BENY

317,841
20
164
234

2

Thanks. I had to change one thing since it was a Pandas dataframe. `df['Dollar Amount'].apply(lambda x : "{:,}".format(x))` – ACH Nov 21 '17 at 03:30
@ACH aha, You got it :-) – BENY Nov 21 '17 at 03:32
1

how it is inserting (,) on the right position ? where are you giving the position? – Pyd Nov 21 '17 at 04:34
@pyd `:,` here adding the comma for 1k. – BENY Nov 21 '17 at 04:39
3

@pyd you can check the link :-) https://docs.python.org/3.4/library/string.html (By searching {:,} you will see the result ) – BENY Nov 21 '17 at 04:40
1

Up to date documentation link: https://docs.python.org/3/library/string.html – Abraham Sangha Jul 22 '20 at 20:46
Hi, how to apply across multiple columns? – user3423407 Aug 25 '20 at 13:21
1

@user3423407 df.apply(lambda x : "{:,}".format(x),1) – BENY Aug 25 '20 at 13:22
I strongly prefer to set this globally, as in [another answer to a closely-related question](https://stackoverflow.com/a/52405666/1048186), and especially in [this comment on that answer](https://stackoverflow.com/questions/43102734/format-a-number-with-commas-to-separate-thousands#comment111739550_52405666): `pd.options.display.float_format = '{:,.2f}'.format` For one thing, it works on all columns of any dataframes printed after you run this. – Josiah Yoder Jul 20 '23 at 16:59

score 9 · Answer 2 · answered Jun 16 '20 at 18:19

9

This is a more pandorable way to get the thousands separator.

df['Dollar Amount']=df['Dollar Amount'].apply('{:,}'.format)

answered Jun 16 '20 at 18:19

Niranjan

91
1
3

score 2 · Answer 3 · answered Nov 21 '17 at 03:30

Here's a solution using locale that might help, as long as you're okay with formatting your numbers as strings:

import pandas as pd
import locale as lc

# Get the list of all locale options
all_locales = lc.locale_alias
# I'll use US conventions since that's what you mentioned in your question
lc.setlocale(lc.LC_ALL,all_locales["en_us"])

df = pd.DataFrame({"Dollar Amount":[1000, 2000000, 2500.01]})
df["Dollars Formatted"] = df["Dollar Amount"].apply(lambda x: "$"+lc.format("%.2f",x,True))

The convenient thing about locale is that you can easily change between different number conventions if you need to, and it will continue to apply those conventions for the millions and billions separators.

RyanAbnavi · Answer 4 · 2020-06-16T22:13:37.060

2

using map:

df['Dollar Amount'] = df['Dollar Amount'].map("{:,}".format)

you can also use style which is nicer and let you do all your styling in one line:

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

edited Jun 16 '20 at 22:13

answered Jun 16 '20 at 22:05

RyanAbnavi

358
4
6

score 1 · Answer 5 · answered Feb 18 '20 at 13:13

If you need to insert thousands comma separators in a specific column and remove the decimal place:

import pandas as pd
df = pd.DataFrame([(0.21, 1000.0), (0.01, 2000000.0), (0.66, 1000.0), (0.21, 330000.0)], columns=['A', 'B'])

Before:

      A          B
0  0.21     1000.0
1  0.01  2000000.0
2  0.66     1000.0
3  0.21   330000.0

For "Col B" insert comma separators and remove decimal place: A slight adjustment to YOBEN_S's code above gives:

lst = list(df.columns)
lst.remove('A')
for c in lst:
    df[c] = df[c].astype(int).apply(lambda x: f'{x:,}')

After:

      A          B
0  0.21      1,000
1  0.01  2,000,000
2  0.66      1,000
3  0.21    330,000

score 0 · Answer 6 · answered Aug 05 '22 at 21:38

The f string version of @Benny's answer:

df = pd.DataFrame({'DollarAmount': [5012.82, 1203, 4000.0, 824.07, 625.0]})
df.DollarAmount.apply(lambda x: f"{x:,}")

0    5,012.82
1     1,203.0
2     4,000.0
3      824.07
4       625.0
Name: DollarAmount, dtype: object

How to insert a comma as a thousands separator in a pandas dataframe column?

6 Answers6

Linked