Pandas Dataframe Number Values Comma Formatted

Question

I have a Pandas dataframe like below.

    X        Y
0  12345    67890
1  54321    N/A
2  67890    123456

I need to make these numbers comma formatted. For example, 12345 => 12,345.

Please help.

Thanks.

See this [thread](https://stackoverflow.com/questions/20686629/how-to-convert-an-integer-to-a-comma-separated-string) — agastya, May 09 '20 at 12:37
I need all the numbers in the whole dataframe to be formatted like that, not one value. Also, note that, there is a string value there. It should not be changed. — Joydeep, May 09 '20 at 13:16
Use the apply function on the column. Filter out the ints using lambda — agastya, May 09 '20 at 13:28

Marco Cerliani · Answer 1 · 2020-05-09T14:35:20.973

1

nan = 'N/A'

df = pd.DataFrame({'A':np.random.randint(10000,20000, 6), 'B':np.random.randint(1000,2000, 6)})
df.loc[3,'B'] = nan

df = df.replace(nan, 0)
df['A'] = df['A'].apply(lambda x: '{:,}'.format(x))
df['B'] = df['B'].apply(lambda x: '{:,}'.format(x))

edited May 09 '20 at 14:35

answered May 09 '20 at 13:41

Marco Cerliani

21,233
3
49
54

It's giving error - Cannot specify ',' with 's'. Also, I am not talking about NaN. It's a string "N/A". – Joydeep May 09 '20 at 14:31
There could be a zero (0) in the column. – Joydeep May 09 '20 at 15:19
Do you mean a string like this '(0)' ? – Marco Cerliani May 09 '20 at 15:32
No, integer 0. That would give me trouble once I would need to replace back 0 to 'N/A' – Joydeep May 09 '20 at 15:36

score 1 · Answer 2 · answered May 09 '20 at 16:38

1

df['Y'] = df['Y'].apply(pd.to_numeric, errors='coerce')
df['Y'] = df['Y'].apply(lambda x: '{:,.0f}'.format(x))
df['Y'] = df['Y'].replace({'nan' : 'N/A'}, regex=True)

I guess there must be better ways. Thanks Marco Cerliani.

answered May 09 '20 at 16:38

Joydeep

147
14

Pandas Dataframe Number Values Comma Formatted

2 Answers2