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 threadagastya– agastya2020-05-09 12:37:59 +00:00Commented May 9, 2020 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– Joydeep2020-05-09 13:16:40 +00:00Commented May 9, 2020 at 13:16
-
Use the apply function on the column. Filter out the ints using lambdaagastya– agastya2020-05-09 13:28:06 +00:00Commented May 9, 2020 at 13:28
Add a comment
|
2 Answers
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))
4 Comments
Joydeep
It's giving error - Cannot specify ',' with 's'. Also, I am not talking about NaN. It's a string "N/A".
Joydeep
There could be a zero (0) in the column.
Marco Cerliani
Do you mean a string like this '(0)' ?
Joydeep
No, integer 0. That would give me trouble once I would need to replace back 0 to 'N/A'
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.