Java用sqlit拆分小数,更改小数点分隔符（Python，Sqlite，Pandas）

I have an Excel spreadsheet (which is an extract from SAP) which has data in it (text, numbers). I turn this data into a DataFrame, do some calculations and finally save it to a sqlite database.

Whereas the excel spreadsheet has a comma as the decimal separator. The sqlite database includes the numbers with a dot as the decimal separator.

Extract from the code:

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

(decimal=',' was a suggested solution which only works when you are working with csv)

After doing the calculations I use the following code to save the results to a 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 do need those numbers to have the same decimal separator as the input data and I cannot find a way to do this.

Therefore I am asking if someone of you has an idea how to achieve it?

I am using Python 3.5 with pandas (0.19.1) and numpy (1.11.2) on Mac OS X.

Thank you!

解决方案

You need to convert the float values before saving. Just loop through the column with values containing ., convert each value to string and then you can use replace method.

Here I converted all values in column x

df['x'] = [str(val).replace('.', ',') for val in df['x']]

df.to_sql('rawdata', conn, if_exists='replace')