Formatting thousand separator for numbers in a pandas dataframe

user_1027427 0
Viewed 10466

I am trying to write a dataframe to a csv and I would like the .csv to be formatted with commas. I don't see any way on the to_csv docs to use a format or anything like this.

Does anyone know a good way to be able to format my output?

My csv output looks like this:

12172083.89 1341.4078   -9568703.592    10323.7222
21661725.86 -1770.2725  12669066.38 14669.7118

I would like it to look like this:

12,172,083.89   1,341.4078  -9,568,703.592  10,323.7222
21,661,725.86   -1,770.2725 12,669,066.38   14,669.7118
pandas python-2.7
2 Answers

Comma is the default separator. If you want to choose your own separator you can do this by declaring the sep parameter of pandas to_csv() method.
df.to_csv(sep=',')

If you goal is to create thousand separators and export them back into a csv you can follow this example:

import pandas as pd
df = pd.DataFrame([[12172083.89, 1341.4078,   -9568703.592,    10323.7222],
[21661725.86, -1770.2725,  12669066.38, 14669.7118]],columns=['A','B','C','D'])
for c in df.columns:
    df[c] = df[c].apply(lambda x : '{0:,}'.format(x))
df.to_csv(sep='\t')

If you just want pandas to show separators when printed out:

pd.options.display.float_format = '{:,}'.format
print(df)
user_88861990

What you're looking to do has nothing to do with csv output but rather is related to the following:

print('{0:,}'.format(123456789000000.546776362))

produces

123,456,789,000,000.546776362

See format string syntax.

Also, you'd do well to pay heed to @Peter 's comment above about compromising the structure of a csv in the first place.

user_43741500
Related