I got this format the floats to 1 decimal place.
data = {'A Prime': {0: 3.26, 1: 3.24, 2: 3.22, 3: 3.2, 4: 3.18, 5: 3.16,
6: 3.14, 7: 1.52, 8: 1.5, 9: 1.48, 10: 1.46, 11: 1.44, 12: 1.42},
'B': {0: 0.16608, 1: 0.16575, 2: 0.1654, 3: 0.16505999999999998, 4: 0.1647, 5: 0.16434, 6: 0.16398, 7: 0.10759, 8: 0.10687, 9: 0.10614000000000001,
10: 0.10540999999999999, 11: 0.10469, 12: 0.10396}, 'Proto Name': {0: 'Alpha',
1: 'Alpha', 2: 'Alpha', 3: 'Alpha', 4: 'Alpha', 5: 'Alpha', 6: 'Alpha', 7: 'Bravo', 8: 'Bravo', 9: 'Bravo', 10: 'Bravo', 11: 'Bravo', 12: 'Bravo'}}
import pandas as pd
df = pd.DataFrame(data)
A Prime B Proto Name
0 3.26 0.16608 Alpha
1 3.24 0.16575 Alpha
2 3.22 0.16540 Alpha
3 3.20 0.16506 Alpha
4 3.18 0.16470 Alpha
5 3.16 0.16434 Alpha
6 3.14 0.16398 Alpha
7 1.52 0.10759 Bravo
8 1.50 0.10687 Bravo
9 1.48 0.10614 Bravo
10 1.46 0.10541 Bravo
11 1.44 0.10469 Bravo
12 1.42 0.10396 Bravo
writer = pd.ExcelWriter(r'c:empoutput.xlsx')
# This method will truncate the data past the first decimal point
df.to_excel(writer,'Sheet1',float_format = "%0.1f")
writer.save()
but that alas is not perhaps all cases - no joy with say larger numbers and thousands separator
df.to_excel(writer,'Sheet1',float_format = ":,")
However the following seems to work
data = {'A Prime': {0: 326000, 1: 3240000}}
df = pd.DataFrame(data)
A Prime
0 326000
1 3240000
writer = pd.ExcelWriter(r'c:empoutput.xlsx')
df.to_excel(writer,'Sheet1')
workbook = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'num_format': '#,##0.00'})
worksheet.set_column('B', 18, format1)
#Alternatively, you could specify a range of columns with 'B:D' and 18 sets the column width
writer.save()
All taken from here: http://xlsxwriter.readthedocs.io/working_with_pandas.html