I have an Excel spreadsheet (an extract from SAP). I turn this into a DataFrame, do calculations and save it to an SQLite database. The Excel spreadsheet has comma as decimal separator. The SQLite database uses a dot as decimal separator.

Extract from the code:

df_mce3 = pd.read_excel('rawdata/filename.xlsx', header=0, converters={'supplier': str, 'month': str}, decimal=',')

decimal=',' only works with CSV files. I use following code to save the results to an SQLite database:

conn = sqlite.connect("database.db")
df_mce3.to_sql("rawdata", conn, if_exists="replace")
df_ka_ext.to_sql("costanalysis_external", conn, if_exists="replace")
[...]

Input:

month   ordqty  ordprice    ordervolume invoiceqty  invoiceprice    
08.2017 10,000  14,90       149,00      10,000      14,90

Output:

month   ordqty  ordprice    ordervolume invoiceqty  invoiceprice    
08.2017 10.000  14.90       149.00      10.000      14.90

I need those numbers to have the same decimal separator as the input data and I cannot find a way to do this. I am using Python 3.5 with Pandas 0.19.1 and NumPy 1.11.2 on Mac OS X.

After the comments I finally came up with a solution that works at least with sample data. I will have to check if it works with my actual data.

#!/usr/bin/env python3.5
import pandas as pd
import numpy as npdf = pd.DataFrame(np.random.randn(10, 4), columns=list('ABCD'))def formatnumbers(x):x = round(x, 4)x = str(x).replace('.', ',')return xfor col in df.columns:df[col] =  df[col].apply(formatnumbers)print(df.head(n=5))

Result is:

         A        B        C        D
0  -0,4065  -1,6113   0,2257   0,7424
1   0,8349   0,0316   -1,105  -1,6463
2  -0,2108   0,7356  -1,0823   0,5261
3   0,3382   0,6158   0,6117  -0,3896
4   -0,403  -1,3639   0,8691   0,5791