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
Answer from BENY on Stack OverflowNotice 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
This is a more pandorable way to get the thousands separator.
df['Dollar Amount']=df['Dollar Amount'].apply('{:,}'.format)
Two points here. I've 'pd.read_csv'ed a CSV file which has three columns.
I've used the following in order to extract the data and add headings to the columns (as currently the data is just naked)
Column 1 & 3 are text, and column 2 is a number.
How can I output the number with separators? EG 1,000,000 rather than 1000000
Also, what's the best way for formatting this dataframe to be included in an email body?
Thousands Separator in a Number Column of Data Editor
python - How to add thousand separator to numbers in pandas - Stack Overflow
Support parsing thousands separators in floating point data
Thousands separator for to_csv
Videos
When formatting a number with , you can just use '{:,}'.format:
n = 10000
print '{:,}'.format(n)
n = 1000.1
print '{:,}'.format(n)
In pandas, you can use the formatters parameter to to_html as discussed here.
num_format = lambda x: '{:,}'.format(x)
def build_formatters(df, format):
return {
column:format
for column, dtype in df.dtypes.items()
if dtype in [ np.dtype('int64'), np.dtype('float64') ]
}
formatters = build_formatters(data_frame, num_format)
data_frame.to_html(formatters=formatters)
Adding the thousands separator has actually been discussed quite a bit on stackoverflow. You can read here or here.
Use Series.map or Series.apply with this solutions:
df['col'] = df['col'].map('{:,}'.format)
df['col'] = df['col'].map(lambda x: f'{x:,}')
df['col'] = df['col'].apply('{:,}'.format)
df['col'] = df['col'].apply(lambda x: f'{x:,}')
/edit seems I found some outdated information online, read_excel() DOES support setting a thousands separator
dfa = pd.read_excel("file.xlsx", thousands=".")Case closed
I have a column I read from Excel via read_excel("file.xlsx") with:
2.699 2.507 2.716 3.229
Since I'm in the EU this are integers with values "2699, 2507, 2716, 3229" and the '.' is the thousand separator, not ','.
Panda uses the US standard so while converting it to int (there are trailing spaces so Pandas reads it as strings, then I strip them, then I convert to int) I get "2, 2, 2, 3".
read_csv() has the option to set the separator but read_excel() does not.
How can I change the thousand / comma separator to get the correct result?
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.
df.head().style.format("{:,.0f}") (for all columns)
df.head().style.format({"col1": "{:,.0f}", "col2": "{:,.0f}"}) (per column)
https://pbpython.com/styling-pandas.html
pandas (as of 0.20.1) does not allow overriding the default integer format in an easy way. It is hard coded in pandas.io.formats.format.IntArrayFormatter (the lambda function):
class IntArrayFormatter(GenericArrayFormatter):
def _format_strings(self):
formatter = self.formatter or (lambda x: '% d' % x)
fmt_values = [formatter(x) for x in self.values]
return fmt_values
I'm assuming what you're actually asking for is how you can override the format for all integers: modify (i.e. "monkey patch") the IntArrayFormatter to print integer values with thousands separated by comma as follows:
import pandas
class _IntArrayFormatter(pandas.io.formats.format.GenericArrayFormatter):
def _format_strings(self):
formatter = self.formatter or (lambda x: ' {:,}'.format(x))
fmt_values = [formatter(x) for x in self.values]
return fmt_values
pandas.io.formats.format.IntArrayFormatter = _IntArrayFormatter
Note:
- before 0.20.0, the formatters were in
pandas.formats.format. - before 0.18.1, the formatters were in
pandas.core.format.
Aside
For floats you do not need to jump through those hoops since there is a configuration option for it:
display.float_format: The callable should accept a floating point number and return a string with the desired format of the number. This is used in some places likeSeriesFormatter. Seecore.format.EngFormatterfor an example.
The formatters parameter in to_html will take a dictionary of column names mapped to a formatting function. Below has an example of a function to build a dict that maps the same function to both floats and ints.
In [250]: num_format = lambda x: '{:,}'.format(x)
In [246]: def build_formatters(df, format):
...: return {column:format
...: for (column, dtype) in df.dtypes.iteritems()
...: if dtype in [np.dtype('int64'), np.dtype('float64')]}
...:
In [247]: formatters = build_formatters(df_int, num_format)
In [249]: print df_int.to_html(formatters=formatters)
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>A</th>
</tr>
</thead>
<tbody>
<tr>
<th>0</th>
<td>20,000</td>
</tr>
<tr>
<th>1</th>
<td>10,000</td>
</tr>
</tbody>
</table>
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)
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.
Pass param thousands=',' to read_csv to read those values as thousands:
In [27]:
import pandas as pd
import io
t="""id;value
0;123,123
1;221,323,330
2;32,001"""
pd.read_csv(io.StringIO(t), thousands=r',', sep=';')
Out[27]:
id value
0 0 123123
1 1 221323330
2 2 32001
The answer to this question should be short:
df=pd.read_csv('filename.csv', thousands=',')