I'm trying to format the Dollar Amount column to have a comma thousands separator for easier viewing, but I haven't been able to figure it out. Can someone please show me the way?
import pandas as pd
df = pd.read_excel('filename.xlsx')
df['Dollar Amount'].head()
Index Dollar Amount
0 5721.48
1 4000.00
2 4769.00
3 824.07
4 643.60
5 620.00
Name: Dollar Amount, dtype: float64
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
df['Dollar Amount'].apply(lambda x : "{:,}".format(x))
This is a more pandorable way to get the thousands separator.
df['Dollar Amount']=df['Dollar Amount'].apply('{:,}'.format)
Here's a solution using locale that might help, as long as you're okay with formatting your numbers as strings:
import pandas as pd
import locale as lc
# Get the list of all locale options
all_locales = lc.locale_alias
# I'll use US conventions since that's what you mentioned in your question
lc.setlocale(lc.LC_ALL,all_locales["en_us"])
df = pd.DataFrame({"Dollar Amount":[1000, 2000000, 2500.01]})
df["Dollars Formatted"] = df["Dollar Amount"].apply(lambda x: "$"+lc.format("%.2f",x,True))
The convenient thing about locale is that you can easily change between different number conventions if you need to, and it will continue to apply those conventions for the millions and billions separators.
using map:
df['Dollar Amount'] = df['Dollar Amount'].map("{:,}".format)
you can also use style which is nicer and let you do all your styling in one line:
df = df.style.format({'Dollar Amount': "{:,}"})
If you need to insert thousands comma separators in a specific column and remove the decimal place:
import pandas as pd
df = pd.DataFrame([(0.21, 1000.0), (0.01, 2000000.0), (0.66, 1000.0), (0.21, 330000.0)], columns=['A', 'B'])
Before:
A B
0 0.21 1000.0
1 0.01 2000000.0
2 0.66 1000.0
3 0.21 330000.0
For "Col B" insert comma separators and remove decimal place: A slight adjustment to YOBEN_S's code above gives:
lst = list(df.columns)
lst.remove('A')
for c in lst:
df[c] = df[c].astype(int).apply(lambda x: f'{x:,}')
After:
A B
0 0.21 1,000
1 0.01 2,000,000
2 0.66 1,000
3 0.21 330,000
The f string version of @Benny's answer:
df = pd.DataFrame({'DollarAmount': [5012.82, 1203, 4000.0, 824.07, 625.0]})
df.DollarAmount.apply(lambda x: f"{x:,}")
0 5,012.82
1 1,203.0
2 4,000.0
3 824.07
4 625.0
Name: DollarAmount, dtype: object
