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
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
This is a more pandorable way to get the thousands separator.
df['Dollar Amount']=df['Dollar Amount'].apply('{:,}'.format)
Thousands separator for to_csv
python - How to add thousand separator to numbers in pandas - Stack Overflow
Support parsing thousands separators in floating point data
Thousands Separator in a Number Column of Data Editor
Videos
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?
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:,}')
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
/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?
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>