Try this example:

df = pd.DataFrame({
        'date':['1/15/2016','2/1/2016','2/15/2016','3/15/2016'],
        'numA':[1000,2000,3000,4000.3],
        'numB':[10000,20000.2,30000,40000]
    })

writer = pd.ExcelWriter('c:/.../pandas_excel_test.xlsx', engine = 'xlsxwriter')
df.to_excel(writer, index=False, sheet_name='Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'num_format': '0.00'})
worksheet.set_column('C:C', None, format1)  # Adds formatting to column C
writer.save()

You can change the format 0.00 in the .add_format({'num_format': '0.00'}) on your desired format, for example, 0.0000 for four decimal places. Note this only applies to column C.

If you want to change the formatting of all columns, modify the worksheet.set_column('C:C', None, format1), for example

worksheet.set_column(0, 3, None, format1) 

where 0 the first column and 3 the final column.

Check more formatting here: https://xlsxwriter.readthedocs.io/worksheet.html

Actually, 0.0050000000000000001 is a valid representation of the number. Excel only guarantees precision to 15 significant digits (according to the IEEE 754 specification).

That means, what happens after that is anyone's guess (and choice). Internally Excel represents the number as a double precision float (64 bits), which would actually be 0.005000000000000000104083408558608425664715468883514404296875 but when showing the number a trick is employed, cutting the number after 15 significant digits and then pad with zeros if the formatting of the cell requests more decimals.

Therefore, when showing the number 0.005 with up to 30 decimals in Excel, you will see 0.005000000000000000000000000000 which looks like a better representation of the number, but this is not actually the case, which becomes apparent if further arithmetic operations (including the least significant digits) are performed on the number.

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:\temp\output.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:\temp\output.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:B', 18, format1)
#Alternatively, you could specify a range of columns with 'B:D' and 18 sets the column width
writer.close()

All taken from here: http://xlsxwriter.readthedocs.io/working_with_pandas.html

I think my question is obsolete. Excel recognizes that these are decimal characters and automatically converts them to the specified format. I close the question with that. The code in the question already solves the "problem" by itself.

As far as I can tell, if the right separators are entered, all numeric column types are inferred correctly; otherwise some columns will be strings. So to tell if you have the right separator arguments, you can use something like this:

all(df[col].dtype in ['int64', 'float64'] for col in numeric_columns)

At this point if you determine it's not right, your only option would be to try it again with switched separators. Here's kind of a contrived way to do this I came up with while testing, but it reads from a csv and doesn't capture the excel/html determination logic:

import pandas as pd

def read_data(data_file):
    numeric_columns = ['B', 'C']
    separators = {'thousands': ".", 'decimal': ","}
    df = pd.read_csv(data_file, **separators)
    if not parsed_correctly(df, numeric_columns):
        print('retry!')
        separators['thousands'], separators['decimal'] = separators['decimal'], separators['thousands']
        df = pd.read_csv(data_file, **separators)
        print(parsed_correctly(df, numeric_columns))

def parsed_correctly(df, numeric_columns):
    print('parsed_correctly', all(df[col].dtype in ['int64', 'float64'] for col in numeric_columns))
    return all(df[col].dtype in ['int64', 'float64'] for col in numeric_columns)

if __name__ == '__main__':
    files = ['csv_sep.csv', 'csv_sep2.csv']
    for f in files:
        read_data(f)
        print('\n')

In addition to the other solutions where the string data is converted to numbers when creating or using the dataframe it is also possible to do it using options to the xlsxwriter engine:

# Versions of Pandas >= 1.3.0:
writer = pd.ExcelWriter('output.xlsx',
                        engine='xlsxwriter',
                        engine_kwargs={'options': {'strings_to_numbers': True}})

# Versions of Pandas < 1.3.0:
writer = pd.ExcelWriter('output.xlsx',
                        engine='xlsxwriter',
                        options={'strings_to_numbers': True})

From the docs:

strings_to_numbers: Enable the worksheet.write() method to convert strings to numbers, where possible, using float() in order to avoid an Excel warning about "Numbers Stored as Text".

Let us try

out = (s//1).astype(int).astype(str)+','+(s%1*100).astype(int).astype(str).str.zfill(2)
0    105,00
1     99,20
dtype: object

Input data

s=pd.Series([105,99.2])